PnJgHT的博客

在之前的文章中有介绍order by语句的几种执行模式。考虑如下场景：有一个APP，有一个随机显示英语单词的功能，也就是根据每个用户的级别有一个单词表，然后这个用户每次访问首页的时候，都会随机滚动显示三个单词。会发现随着单词表变大，选单词这个逻辑变得越来越慢，甚至影响到了首页的打开速度。如果要我们来设计这个SQL语句要怎么设计呢？对这个例子进行了简化：去掉每个级别的用户都有一个对应的单词表这个逻辑，直接就是从一个单词表中随机选出三个单词。在这个表里面插入了 10000 行记录。

在 MySQL 中，有很多看上去逻辑相同，但性能却差异巨大的 SQL 语句。对这些语句使用不当的话，就会不经意间导致整个数据库的压力变大。

假设目前有这么一个表业务要求是要查询城市是“杭州”的所有人名字，并且按照姓名排序返回前 1000 个人的姓名、年龄，那么SQL语句可以这么写这个语句用到了order by 这个关键词，那么接下里看看这个语句是如何执行的，以及有什么参数会影响执行的行为。

这个语句会命中 d=5 的这一行，对应的主键 id=5，因此在 select 语句执行完成后，id=5 这一行会加一个写锁，而且由于两阶段锁协议，这个写锁会在执行 commit 语句的时候释放。由于字段 d 上没有索引，因此这条查询语句会做全表扫描。那么，其他被扫描到的，但是不满足条件的 5 行记录上，会不会被加锁呢？

binlog 可以用来归档，也可以用来做主备同步，备库执行了 binlog 就可以跟主库保持一致。

在开发系统的时候，可能需要需要计算一个表的行数这时候你可能会想，一条 select count(*) from t 语句不就解决了吗？但是，会发现随着系统中记录数越来越多，这条语句执行得也会越来越慢。然后可能就想了，MySQL 怎么这么笨啊，记个总数，每次要查的时候直接读出来，不就好了吗。

本篇文章针对 InnoDB 引擎展开讨论。一个 InnoDB 表包含两部分，即：表结构定义和数据。在 MySQL 8.0 版本以前，是存在以。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小，所以今天主要讨论的是表数据。

有时候会出现这么一种情况：一条 SQL 语句，正常执行的时候特别快，但是有时也不知道怎么回事，它就会变得特别慢，并且这样的场景很难复现，它不只随机，而且持续时间还很短。

字符串字段创建索引的场景可以使用的方式有：直接创建完整索引，这样可能比较占用空间；创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。。

已知MySQL中一张表是可以支持多个索引的，但是我们写SQL语句的时候并没有主动指定使用哪个索引，也就是说使用哪个索引是由MySQL来确定的。所以可能会出现这种情况：本来一句可以执行的很快的SQL语句却由于MySQL选错了索引导致执行的很慢。现在有如下的例子：先建一个简单的表，表里有 a、b 两个字段，并分别建上索引：这个语句很简单，a上有索引，使用索引a，而实质上优化器选择的确实也是索引a。扫描的行数是10001行再做如下操作：session A 开启了一个事务。

唯一索引、普通索引，怎么选？

在MySQL中可重复读隔离级别下保证事务较高的隔离性，同样的sql查询语句在一个事务里多次执行查询结果相同，就算其它事务对数据有修改也不会影响当前事务sql语句的查询结果。这个就是靠机制来保证的。MySQL在读已提交和可重复读的隔离级别下实现了MVCC。

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈。

InnoDB索引非主键索引存储的是主键ID，这样可以保证数据一致性和节省存储空间，可以这么理解：商城系统订单表会存储一个用户ID作为关联外键，而不推荐存储完整的用户信息，因为当我们用户表中的信息（真实名称、手机号、收货地址···）修改后，不需要再次维护订单表的用户数据，同时也节省了存储空间。B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题，而B+树只需要遍历叶子节点就可以解决对全部关键字信息的扫描，所以对于数据库中频繁使用的range query，B+树有着更高的性。

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

简单来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在MySQL中，事务支持是在引擎层实现的。

查询语句的那一套流程（连接器-分析器-优化器-执行器）更新语句也是同样会走一遍。执行语句前要先连接数据库，这是的工作。在一个表上有更新的时候，跟这个表所有有关的查询缓存都会失效，所以这一条更新语句就会把表 T 上所有缓存结果都清空。这也就是我们一般不建议使用查询缓存的原因。接下来，会通过词法和语法解析知道这是一条更新语句。优化器决定要使用 ID 这个索引。然后，负责具体执行，找到这一行，然后更新。...

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。