MySQL 索引与索引优化

索引

索引是用来（对数据排序以及）快速查询的数据结构

数据库中的索引一般作为索引文件被存放在磁盘中，表中的数据被修改之后索引也需要更改

重要名词

聚集索引：数据文件就是索引文件，数据和索引一起放。在 innodb 中的聚集索引其实是联合索引，它的最后一个索引位必须是主键，这么做是为了保证插入时一定找得到所在的页

非聚集索引：索引与数据不放在一起，索引存放数据的地址

主键索引：将主键排序，按照主键大小查询的索引是主键索引，主键不能为 null

辅助索引：辅助索引的叶子节点存储的数据是主键，找到主键后对主键索引进行查找，又称二级索引

唯一索引：限定每个记录都不一样的辅助索引，它和辅助索引有一定区别，就是它不可以使用 innodb 的插入缓存的特性，因为使用唯一索引需要校验每个字段都唯一（当插入的时候），因此无法避免查询磁盘中的索引

唯一索引是可以包含 null 值的，包含 NULL 单列索引的查询，也是可以用上索引的。但是不建议这么做，因为使用 is null 的索引查询很可能比直接全表扫描更慢。mysql 对 null 值特殊处理，会放在唯一索引的最左侧。存储大量的 NULL 值，除了计算更复杂之外，数据扫描的代价也会更高一些。因此，对唯一索引的默认值不建议设置为 NULL

并且 NULL 不会受到唯一索引限制。举个例子：当 Mysql 中建立的联合索引， 只要索引中的某一列的值为空时(NULL)，即便其他的字段完全相同，也不会引起唯一索引冲突

官方是这么说的：

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL.

唯一约束对 NULL 值不适用。原因可以这样解释： 比如我们有一个单列的唯一索引，既然实际会有空置的情况，那么这列一定不是 NOT NULL 的，如果唯一约束对空值也有起作用，就会导致仅有一行数据可以为空，这可能会和实际的业务需求想冲突的，所以通常 Mysql 的存储引擎的唯一索引对 NULL 值是不适用的。 这也就倒是联合唯一索引的情况下，只要某一列为空，就不会报唯一索引冲突

覆盖索引：辅助索引里根据索引数据找主键，如果我们需要的数据都已经找到了，不需要再回表，提高了效率

种类

哈希索引

使用 hash 算法来完成数据的存储，与 hashmap 原理相同。在数据库中一般不常用，因为 hash 索引只支持等号匹配，在范围匹配用的比较多的情况，哈希索引失去了它的功能

B 树索引

B 树索引又称多路平衡搜索树，因此是有序的，节点中存放多个关键字和多个指针

关键字集合分布在整颗树中，不管是叶节点还是其他节点，都有关键字，任何一个关键字出现且只出现在一个结点中

搜索有可能在非叶子结点结束

插入与删除时自动调节以满足 B 树性质

B+ 树索引

在存储相同数据的情况下，B 树比 B+ 树高

B+ 数每个磁盘块中存放了参考值与指向子节点的指针，参考值右边的指针指向大于等于这个参考值的数据，左边存放小于这个参考值的数据

所有数据都存放在叶子结点，并且叶节点中有指针指向下一个叶节点

B 和 B+ 会对索引列进行排序，因此如果 order by 命中也后提高效率

倒排索引

通用倒排索引简称 gin 索引

倒排表以字或词为关键字进行索引，表中关键字所对应的记录表项记录了出现这个字或词的所有文档，一个表项就是一个字表段，它记录该文档的 ID 和字符在该文档中出现的位置情况

由于每个字或词对应的文档数量在动态变化，所以倒排表的建立和维护都较为复杂，但是在查询的时候由于可以一次得到查询关键字所对应的所有文档，所以效率高于正排表。在全文检索中，检索的快速响应是一个最为关键的性能，而索引建立由于在后台进行，尽管效率相对低一些，但不会影响整个搜索引擎的效率

GIN（Generalized Inverted Index）是一个存储对 key-posting list 集合的索引结构，其中 key 是一个键值，而 posting list 是一组出现过 key 的位置。如 ‘hello’-’14:2 23:4’ 中，表示 hello 在14:2 和 23:4 这两个位置出现过，在 PG 中这些位置实际上就是元组的 tid

此索引比较适合用于对文档、数组等字段建立

高效使用索引

sql 在每次执行的时候都会生成扫描区间，我们可以用扫描区间来大致判断会执行什么索引，以及被操作的数据多少

避免索引失效

我们可以使用 EXPLAIN 来查看 sql 语句执行情况，进而优化索引

基于阿里编程规范，通常的做法有以下几点：

1，select 语句：使用覆盖索引以避免回表操作；避免使用*

2，where 语句：

• 无过滤不索引
• 联合索引遵从最左前缀原则
• 使用 or（需要分别在索引中查询所有被 or 引用的列，回表代价较大）、null、不等于、字符串不加单引号都会使索引失效（字符串不加单引号是 index 全索引扫描，加了之后普通索引为 ref，因为发生了隐式转化，内部调用了 cast 函数，然后通过比较规则进行比较），like 以通配符开头无法使用索引
• 索引列上有计算或者使用函数，因此需要让索引列以列名形式在搜索条件中单独出现（比如2*a > 4，MySQL 并不会去优化查询表达式，而且全表扫描的时候将 a 的值代入看看满不满足条件）。同时，强转不会造成性能影响，是可以使用到索引的
• 索引的基数较低，并且数据量较小的情况下，建立索引还不如不建。因为全表扫描的消耗可能比走索引还低。但是如果数据量比较大的话，基数低还是可以走到索引的，就算查询基数只有2，如果数据量有1000w，那么索引还是省去了500w的查询，而且如果数据分布不均匀的话，我们想要命中那个较少的部分，也很有可能命中索引的

3，limit 语句：需要配合 where 优化，否则在寻找偏移量 offset 的时候，会从第0条数据开始全表扫描

4，order by 语句：匹配最左前缀原则，并且顺序需要正确才能用到索引（联合索引后面的自动如果逆序查是不会用到索引，因为索引是有顺序的，一般查出来就不用排了，如果逆序查还是需要在内存中创建 sort buffer 做排序。但是如果全逆序也还是也可以使用到索引的，将查出来的数据倒过来就行了），尽量避免 filesort

此外尽可能不要将多个查询条件（尤其是带 in 的）和 order by limit 混在一起用，大概率走不到索引，这是 mysql 的底层问题 bug# 78993，具体 case 和解释：

【MySQL】全索引扫描的bug

大致意思就是，mysql 中的优化器在遇到 where 条件的时候查询到了合适的索引，但是遇到 order by limit 的时候继续优化 sql 时清除了如何访问索引（比如原来是 range 访问，此时 mysql 将它清除了）。最后发现扫全表的代价比使用索引的代价要小。因此出现了这个问题

因此在实际生产中如果不得不用 order by limit 可以使用子查询做优化：

select * from (
select id
 from lol
where seller_id= 1501204 and service_id= 1
and sale_type in(3, 4) and use_status in(3, 4, 5, 6)
) order by id ASC limit 100

如何创建索引

在创建索引之前想想以下几个问题：

• 哪些列经常作为 where 条件、分组以及 order by？（根据 where 条件进行索引查找，因为索引会自动排序所以符合了 order by，而且不管是正序还是逆序都可以查）
• 哪些列会和其他表进行关联？（主表会进行全表扫描，从表根据判断条件进行查找，此时从表就可能会使用到索引）
• 准备创建索引的列的基数是否较高？（基数较低，比如性别，创建索引纯属浪费，因为除了空间时间的消耗，在查找时走该索引的话几乎每一条数据都需要回表，这时候 MySQL 会自动选择全表扫描，该索引建了和没建一样）
• 避免重复索引

索引逆序查找：索引有槽的概念，虽然底层是单向链表链接行，都是可以正向找到下一组数据的第一个元素，然后向上找一个槽，来找到数据的上一个元素

同时，还要注意：

• 能用普通索引不用唯一索引（唯一索引需要判断是否唯一）
• 能用单列索引不用联合索引，联合索引要考虑最左前缀原则

隐式转换规则

当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。某些转换是隐式发生的。例如，MySQL 会根据需要自动将字符串转换为数字，反之亦然。以下规则描述了比较操作的转换方式：

• 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，特殊的情况是使用<=>对两个 NULL 做比较时会返回1，这两种情况都不需要做类型转换
• 两个参数都是字符串，会按照字符串来比较，不做类型转换
• 两个参数都是整数，按照整数来比较，不做类型转换
• 十六进制的值和非数字做比较时，会被当做二进制串
• 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
• 所有其他情况下，两个参数都会被转换为浮点数再进行比较
• 不以数字开头的字符串都将转换为0。如 ‘abc’、‘a123bc’、‘abc123’ 都会转化为0
• 以数字开头的字符串转换时会进行截取，从第一个字符截取到第一个非数字内容为止。比如 ‘123abc’ 会转换为123，‘012abc’ 会转换为012也就是12，‘5.3a66b78c’ 会转换为5.3，其他同理

前缀索引以及索引长度

在 SQL 执行计划中，key_len 表示索引长度，经常用于判断复合索引是否被完全使用

在没有指定索引长度的情况下，如果索引字段不为空且长度不可变，索引长度等于该字段的长度。它有以下规则：

• 如果可以为空（未定义NOT NULL），mysql会多加1个字节标识
• 如果长度可变（比如varchar），MySQL会使用2个字节标识
• 索引长度有最大限制，每个引擎都不一样

前缀索引就是在创建索引的字段后面加入（数字），比如：

CREATE INDEX idx_author_id ON book (author_id(20));

以上的例子代表该索引会使用20个字节，如果可以把这个字段都包含进去，就是一般的索引。如果没有全部包进去，就叫前缀索引，一般用于text等比较多内容的字段，在存放空间上以及操作速度上都有提升

比如有一个 char 属性的内容叫 ‘dsafsadfasfdsaf’
我们创建该属性的索引使用5个字节，索引只会存放 ‘dsafs’

为什么要避免使用 select *

以前就听过不要使用 select *，但是为什么呢？（事实上阿里给出的答案无法说服我）

1，数据库要查询所有数据，查询多余的字段会占用内存，同时由于查找的大多都是辅助索引，多出来必须要根据主键再找一次的步骤，也就是使用 * 的话大概率要回表查询

2，这个 Join Buffer 也给我们了不要使用 * 的理由，只有查询条件以及过滤条件的列才会被放到这个缓冲区中，因此如果放 * 的话缓冲区会很快被占满

最左前缀原则

将多个属性放在一个索引中叫联合索引，如 (name,city) 就是 name 加 city。如果想要命中这种索引需要遵守最左前缀原则

搜索的属性由左向右匹配，创建 (name,city) 搜 name 可以命中索引，但是搜 city 不能

如果多个属性都在联合索引中但是顺序不同，数据库会通过查询优化器自动优化来命中联合索引

根据最左前缀原则，搜索次数的较多的创建索引时应该放在前面

limit 优化

limit 默认从头开始找数据，全表扫描，所以数据越多找的速度越慢

比如 select * from student limit 10000，10需要先过一遍10000才可以找到数据，因为 MySQL 无法知道数据排在第几位，因此使用这种 offset 偏移量是非常影响性能的

我们可以结合索引来解决这个问题，先使用 where 语句查询有索引的区间来代替 offset 的功能，然后返回后面 size 数量的数据即可

比如我需要读取表中信息做处理，全表读取一定是错误的方式，太影响性能，同时也不能用 offset 做分页，因此我需要这么写

select * from public.b2c_product_summary where id >= 332            and id < 332 + 10000 * 1 ;
 
sleep 1s
 
select * from public.b2c_product_summary where id >= 332 + 10000 *1 and id < 332 + 10000 * 2 ;
 
sleep 1s
 
......
......
......
 
select * from public.b2c_product_summary where id >= 332 + 10000 *145 and id < 332 + 10000 * 146 ;
 
sleep 1s
 
select * from public.b2c_product_summary where id >= 332 + 10000 *146 and id < 332 + 10000 * 147 ;

每次读取10000条，强烈建议 sleep 一段时间，比如 1s，这样可以给 DB 集群主从同步一个喘息之机，避免主从延迟过大，导致 DB 实例上的其他应用取不到最新数据，甚至导致主从无法切换或主从切换后丢数据

如果 order by 的列有相同的值时，MySQL 会随机选取这些行，因此为了保证每次都返回的顺序一致可以额外增加一个主键作为排序字段

SQL 执行计划

就是之前说的使用 EXPLAIN 来了解一条 sql 语句是如何执行的，但是到底应该如何看执行计划呢，出现的每一个属性到底是什么意思呢？

EXPLAIN 可以看 SELECT，也适用于 DELETE、INSERT、REPLACE 和 UPDATE 语句

基本字段

table：被操作对象的名字，通常是表名

partitions：匹配分区信息（非分区表为 NULL），一般用不到

possible_keys：可能被使用的索引，可能被使用到的索引越多，计算最优解时的时间就越长。索引不是建的越多越好

key：真实使用的索引，优化器根据成本优化选择出的索引

key_len：索引键长度，使用字节为单位。可以衡量索引的好坏，key_len 越小，判断的成本越低，索引效果越好。计算规则为建表时候定义的长度，比如 varchar(30) 在 utf-8mb3 下最多存放90个字节，变长需要加2字节，为 null 加1字节，因此一共为93字节，这与之前学的底层数据存储方式有关

ref：表示本行被操作对象的参考对象，即与索引列进行等值匹配的数据是什么，该值可能为 const（匹配的是常数），可能为 eq_ref（非唯一的某个数），可能是一个函数等等

rows：估计该查询需要查多少行数据，注意，是估计

id

表示被操作的顺序；id 值大，先被执行，若 id 值相同，执行顺序从上到下

如果为连接查询，两个查询的 id 值一般相同，如果为子查询或者嵌套查询，id 就有了大小之分

在相同的 id 值中，靠前的代表先执行，靠后的代表后执行，即前面的是驱动表，后边的被驱动表

id 有可能为 null，这种情况发生在查询时生成临时表存储数据的时候

select_type

查询中每个 select 子句的类型，一般都为简单查询

SIMPLE：简单查询，不包含 UNION 或者子查询
PRIMARY：查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY
SUBQUERY：子查询中的第一个 SELECT
UNION：在 UNION 语句中，UNION 之后出现的 SELECT
DERIVED：在 FROM 中出现的子查询将被标记为 DERIVED
UNION RESULT：UNION 查询的结果

在出现子查询、嵌套查询等复杂查询的时候，使用 select_type 来确定该查询到底在整个查询的什么位置

type（重要）

查询执行的类型，描述了查询是如何执行的，MySQL 会自动在所有索引之中，选择一个消耗最低的扫描方式

单表扫描方式

• system：这些数据已经加载到内存里，不需要进行磁盘 IO，这类扫描是速度最快的

• const：表中最多只有一行匹配的记录，通常使用主键或唯一索引的所有字段作为查询条件会出现 const，比如 where a = 1；这个条件

此时在底层会生成一个 [1, 1] 的单点扫描区域，由于是唯一索引因此直接确定然后回表，速度相当快

• ref：使用普通索引作为查询条件，如果 a 是非唯一索引的话查询结果可能找到多个符合条件的行，此时，扫描方式不再是 const，而且 ref（引用）。总之这种情况是单点扫范围

定位到索引开头部分然后向后扫描直到 a 不为1，因此需要范围扫，如果还设置了where a is null and a = 1，则是ref扫描的特例ref_or_null，因为null可能不止一个

• range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了，比如where a > 10 and a < 14，此时会生成扫描区域(10, 14)。但是如果是(-∞, +∞) 的话则不是这种情况

• index：全索引扫描，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，并且比聚集索引小很多，速度更快。（在联合索引中，查找非第一个字段可能会使用index，这种情况出现在不需要回表的时候）

同时，如果在扫全表的时候添加了 order by 主键语句，则会被人为的认定为 index 扫描

• all：全表扫描，性能最差的扫描，一般不可以在 sql 中出现这种情况

多表扫描方式

大部分的链接查询都没有多次单表查询效率高，因为比较复杂，大部分人都无法驾驭多表扫描的优化。链接查询从本质上来说，就是把一个表通过最优的策略查询出来的结果，一个个传递给下一张表作为条件进行查询

而且我们都知道为了防止笛卡儿积问题，我们会使用 on 来写删除条件，如果条件不对应则该数据会被剔除。这就有了表的先后扫描问题，左连接和右链接都是前面一张表做为驱动表，驱动表先扫描，后一张表作为被驱动表后扫描。而内链接则是驱动表与被驱动表可以互相转化

• eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件

所有值的顺序从最优到最差排序为：system > const > eq_ref > ref > fulltext > ref_or_null > unique_subquery > index_subquery > range > index > ALL

一般来说不要用多表联合查询，单表查询的效率比多表快的多

filtered

一般配合 rows 这个列一起使用，该列表示满足搜索条件的记录有大概多少条。比如用 a 索引来算出 rows 有300 行，filtered 为百分之十表示预测在这300行中满足条件的记录占百分之十，即30条

这个条件过滤在成本优化的时候被用作判断标准，MySQL 在计算扇出的时候采用的就是条件过滤这个策略

Extra

重要的补充信息，一般的查询语句会在 extra 下给出提示，一行数据可能有多个 extra 信息，这里的信息是我们了解底层执行了某种优化最好的信息来源，比如索引下推、临时表等等。同时，如果查询语句写的有问题在这里也可以找到哪里出问题了，MySQL 的官方文档中可以查看更多的信息：

• Using filesort ：使用文件完成排序，简称文件排序。说明在查询的过程中我们在内存中做了数据排序（sort buffer），如果显示了这个，说明效率是比较慢的并且没有用到索引。但是，就算它的效率比较慢，mysql 也使用了双路排序和单路排序来提升效率（双路指先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段。单路排序是一次性取出满足条件行的所有字段，然后在 sort buffer 中进行排序。因此单路排序效率更快，但是会使用更多的空间）
• Using index：可直接在索引中获取需要的信息。若同时出现 Using where 表明索引还被用来过滤筛选；没有出现，表明只是用来了读取数据
• Using index condition：尝试只使用索引来获取数据，即能用索引就用
• Using index for group-by：使用索引优化GROUPBY或者DISTINCT操作，避免外的磁盘操作
• Using temporary：用临时表存储中间结果，常用于 GROUP BY或者 ORDER BY操作
• Using where：使用了 where 条件
• Using join buffer (Block Nested Loop)：连表查询的方式，表示当被驱动表没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询

当 Extra 列包含 Using filesort 或 Using temporary 时，MySQL 的性能可能会存在问题，需要尽可能避免

查看生成执行计划的处理过程

MySQL 提供了查看优化器如何生成并且选择执行计划的功能，只需要开启然后再执行查询之后查看系统表 OPTIMIZER_TRACE 即可

set optimizer_trace = "enabled=on"

select user_id, sex
from user;

select * from information_schema.OPTIMIZER_TRACE

set optimizer_trace = "enabled=off"

表中的列分别表示：

QUERY：输入的查询语句
TRACE：优化过程的 JSON 形式
MISSING_BYTES_BEYOND_MAX_MEM_SIZE：上一列中会输出很多内容，有些内容因为太多了，不会打入到表中，该列表示被忽略的文本字节数
INSUFFICIENT_PRIVILEGES：表示是否有权限查看执行计划的生成过程，0 表示有权限

JSON 格式的执行计划

使用下面语句，让返回的执行计划转化为 JSON 格式的。比起表格式的，最大的优点就是多了几个属性

explain format = json 查询语句

read_cost：总 IO 成本加上检测 rows 乘 （1 - filter）条记录的成本
eval_cost：单纯检测 rows 乘 filter 条记录的成本，即查出来的最终数据的检测成本
perfix_cost：查询该表的总成本，即 read_cost 加 eval_cost
data_read_per_join：此次查询内存总共读取的数据量

MySQL 对索引底层的优化

多表查询成本

在MySQL中，一条查询语句的执行成本主要有两个方面组成：

1、I/O成本：经常使用的MyISAM和InnoDB存储引擎都是将数据和索引存放在磁盘中，当查询表中的记录时，需要先将数据或索引加载到内存中然后再操作。从磁盘加载到内存的过程中消耗的时间称为I/O成本。

2、CPU成本：读取以及检索记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。

对于InnoDB存储引擎来说，页是磁盘和内存交互的基本单位。MySQL规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。

1.0、0.2这些数字称之为成本常数，这两个是用的最多的，还有其他成本常数。MySQL 根据计算多个可能用到的索引的查询成本，选择最最小的执行方式来执行 sql

统计数据

MySQL 为了快速的计算出使用索引的成本、记录表中的数据属性等等原因，维护了两张表来分别记录关于索引以及数据库的相关信息，这两张表都在 mysql 系统数据库下

同时，MySQL 提供了两种方式存放统计数据，一种是基于磁盘的永久存储，另一种是基于内存的暂时存储（现在一般都存在磁盘中），用户可以自己选择

MySQL 的大多数成本计算以及统计都不是遍历整张表来算出需要的值的，做法都是取一定数目的页算出需要的数据一共有多少，取平均值，然后乘以总页数

其中关于索引的表记录如下：

CREATE TABLE innodb_index_stats (

database_name varchar(64) COLLATE utf8_bin NOT NULL,// 数据库名称

table_name varchar(64) COLLATE utf8_bin NOT NULL,// 表名、分区表名、子分区表名称

index_name varchar(64) COLLATE utf8_bin NOT NULL,// 索引名称

last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,// 表示InnoDB上次更新此统计信息行的时间戳

stat_name varchar(64) COLLATE utf8_bin NOT NULL,// 统计信息名称，其对应的统计信息值保存在stat_value列

stat_value bigint(20) unsigned NOT NULL,// 保存统计信息名称stat_name列对应的统计信息值

sample_size bigint(20) unsigned DEFAULT NULL,// 提供了统计信息估计值的采样页数（在记录索引有多少个不重复值时，需要提供采样了多少页的数据）

stat_description varchar(1024) COLLATE utf8_bin NOT NULL,// 统计信息名称stat_name列中指定的统计信息的说明信息

PRIMARY KEY (database_name,table_name,index_name,stat_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

sample_size 该值非常重要，通过它可以算出索引列中一个值重复了多少次，然后计算出走某个索引需要的成本，就算是说该值解决了非唯一索引的统计问题

但是如果该索引中可以包含空值，那又如何处理呢？有些人觉得 null 就是一个特殊的重复值，有些人觉得某个 null 的取值不一样，因此每个 null 都有不同含义。根据想法的不同， 统计 null 值的方法也不同，MySQL 为了处理这个问题，提供了 innodb_stats_method 配置，让用户可以自己选择如何对待 null 值

innodb_table_stats 结构也差不多，记录了表名、数据库名、最后更新时间等数据，这里就不列出来了

那这些值应该在一定的时间点自动更新才行，不然一些增删改的操作随时可能造成这些数据的不准确。MySQL 设定了没改变表数据的百分之十就自动更新这些数据。在我看来这是应该针对数据库的优化操作，如果想要记录数据使用 AOP 每次更新的时候记录一下即可，但是这么做太费时间，MySQL 采用算法牺牲了数据的精准度以降低时间复杂度

单表底层优化

1，MySQL 对需要回表的数据进行了优化，当有多个条件需要回表的时候，会自动将主键从小到大的排序，然后再统一执行回表操作，这样可以节省随机IO次数。这项技术简称MRR，但是 MRR 的使用条件非常严格，不怎么用的到

2，交集索引合并：在where a = 1 and b = 1的时候，索引会怎么查询呢？MySQL并不会用a = 1的主键回表然后判断b是否为1.它会直接走两个索引找到a与b的主键，然后取交集得到最终的主键后回表

但是这有一个条件，就是两组主键必须都是按从小到大排序的。学过算法的同学都知道如果排完序的两组数据找交集只需要O(n)的时间复杂度。因此如果是where a = 1 and b > 1的话就不会有交集索引优化了

此时MySQL应该会走a索引，因为底层的优化判断出单点扫描比范围扫描要快

3，并集索引合并：在where a = 1 or b = 1的时候，会类似交集索引合并一样，按主键值排序去除不符合的条件，然后根据这些选择出来的条件回表

索引合并一般都在有排序的情况下在可以使用到，因此在 sql 中尽可能使用 ref 吧

4，Sort-Union 索引合并：在使用or条件搜索的情况下，查出来的主键是有可能进行排序优化的，比如 where a < 1 or b > 1 时，MySQL 会先将各个索引中扫描到的记录的主键值进行排序，再按照执行 Union 并集索引合并的方式进行查询

同时 MySQL 没有交集索引合并

多表底层优化

嵌套循环连接：就是普通的连接方式，把一个表通过最优的策略查询出来的结果，一个个传递给下一张表作为条件进行查询。因此我们一般会使用索引优化，将需要连接的字段加上索引减少全表扫描的次数

基于块的嵌套循环连接：我们从表中查出来的数据一般都在内存中，如果数据量太大，有可能导致内存不足以存放整张被驱动表。此时可能就需要把被扫描过的表记录从内存释放掉给正在扫描的记录腾地方。这样效率是非常低的，为了优化效率，设计者提出了 Join Buffer 的概念，也就是连接缓冲区

该 buffer 存放了多态驱动表中扫描出来的记录，这样在扫描被驱动表的时候就可以将多条记录一起作为条件进去扫描了

这个 Join Buffer 也给我们了不要使用 * 的理由，只有查询条件以及过滤条件的列才会被放到这个缓冲区中，因此如果放 * 的话缓冲区会很快被占满

索引下推

在 mysql5.6 以及以后的版本中这个优化默认开启，该优化针对二级索引

众所周知 MySQL 可以分层的，其中的服务层负责 SQL 语法解析、生成执行计划等，而存储引擎层则去执行数据的存储和检索。索引下推就是把服务层要做的事情交给了存储引擎层
![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/eb211faa4fc14b75b2357574842a1f1a.png

当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL 服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合 MySQL 服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器。这么做减少了回表次数，并且 IO 传递的数据也减少了

只有在创建联合索引的时候才会使用到这个优化，存储引擎通过在查询的时候尽可能的（如果索引中没有需要查找的字段还是要回表）去筛选满足条件的数据，这样就不用服务层来进行判断，返回了更少的数据，减少了回表次数，因此提高了性能

比如我们在一张表里创建了联合索引（name, age），没有使用索引下推时：

使用了索引下推时：