跳到主要内容

MySQL索引优化总结

参考：https://github.com/xiaolincoder/CS-Base

索引数据结构​

存储在 B+Tree 索引时是长什么样子的？

B+Tree 是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。叶子之间双向链表

B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足，查询千万级的目标数据最多需要 3-4 次磁盘 I/O，所以B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。

为什么要用B+树做索引数据结构？

磁盘比内存速度慢几万倍，磁盘读写的最小单位是扇区(512B),操作系统读写单位是块(4KB)，一次磁盘IO读8个扇区，我们希望磁盘IO少，可以支持范围查询。

  • 索引最好可以排序这样就可以二分，时间复杂度降低到log(n)，于是就有了二分查找树，但是二分查找树有可能退化为一条链，又回到了O(n)
  • 于是有了平衡二叉树(AVL树)，左右子树高度差不大于1。但是由于都是二叉树，高度还是高
  • 于是 有了B树，也就是多叉树，用来降低高度，但是B树每个节点都是索引+记录，导致内存浪费，而且范围查询不够好
  • 于是 有了B+树，非叶子结点存指针，叶子存数据，叶子之间双向链表，支持范围查询，高度3-4层
  • 相对于hash，hash查询速度快O(1),但是不适合范围查询

InnoDB如何存储数据？

InnoDB 的数据是按「数据页」为单位来读写的，而不是行，以页为单位读取存入内存，默认16kb。InnoDB 里的 B+ 树中的每个节点都是一个数据页

索引类型​

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。 按「字段个数」分类：单列索引、联合索引。

主键索引(聚簇索引)​

主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；

规则：

  • 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
  • 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
  • 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

二级索引(辅助索引)​

二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

唯一索引​

唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

前缀索引​

前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。

联合索引​

例如：联合索引(product_no, name)

CREATE INDEX index_product_no_name ON product(product_no, name);

当在联合索引查询数据时，先按 product_no 字段比较，在 product_no 相同的情况下再按 name 字段比较。

遵循最左匹配原则

索引优化​

回表查询​

例如语句select * from product where product_no = '0002';会先根据二级索引product_no找到对应的主键索引，然后再通过主键索引中的 B+Tree 树查询到对应的叶子节点，然后获取整行数据。这个过程叫回表，也就是说要查两个 B+Tree 才能查到数据

索引覆盖​

在二级索引的 B+Tree 就能查询到结果的过程就叫作覆盖索引，也就是只需要查一个 B+Tree 就能找到数据。

select id from product where product_no = '0002';

最左匹配原则​

按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循最左匹配原则，联合索引会失效。

(a, b, c) 联合索引,可以匹配：

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；

不可以匹配：

where b=2；
where c=3；
where b=2 and c=3；

是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到索引的。

联合索引范围查询​

联合索引的最左匹配原则会一直向右匹配直到遇到范围查询停止匹配。也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。

  1. 例子select * from t_table where a > 1 and b = 2

    在符合 a > 1 条件的二级索引记录的范围里，b 字段的值是无序的

    结论：这条查询语句只有 a 字段用到了联合索引进行索引查询，而 b 字段并没有使用到联合索引。

  2. select * from t_table where a >= 1 and b = 2

    对于符合 a = 1 的二级索引记录的范围里，b 字段的值是「有序」的

    结论：这条查询语句 a 和 b 字段都用到了联合索引进行索引查询

  3. SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2

    BETWEEN等价于 \>= and =<

    结论：这条查询语句 a 和 b 字段都用到了联合索引进行索引查询

  4. SELECT * FROM t_user WHERE name like 'j%' and age = 22

    在符合前缀为 ‘j’ 的 name 字段的二级索引记录的范围里，age 字段的值是「无序」的

    但是对于符合 name = j 的二级索引记录的范围里，age字段的值是「有序」的 结论：这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

索引下推​

对于联合索引（a, b），在执行 select * from table where a > 1 and b = 2 a 字段能用到索引,b 是否等于 2的判断是在联合索引里判断？还是回主键索引去判断呢？

  • 在 MySQL 5.6 之前，只能从 ID2 （主键值）开始一个个回表，到「主键索引」上找出数据行，再对比 b 字段值。
  • 而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

联合索引排序​

select * from order where status = 1 order by create_time asc

给 status 和 create_time 列建立一个联合索引,这样根据 status 筛选后的数据就是按照 create_time 排好序的，避免在文件排序，提高了查询效率

索引失效​

  1. 左或者左右模糊匹配： like %xx 或者 like %xx% 两种方式，因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。
  2. 对函数使用索引：因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。
  3. 对索引进行表达式计算：因为索引保存的是索引字段的原始值
  4. 对索引隐式类型转换：MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。
  5. 非最左匹配原则：不按照最左优先的方式进行索引的匹配。
  6. WHERE 子句中的 OR：OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。因为只有一个条件列是索引列是没有意义

索引优化​

前缀索引优化：使用某个字段中字符串的前几个字符建立索引，减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小（order by 就无法使用前缀索引，无法把前缀索引用作覆盖索引）

覆盖索引优化：不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作。

主键索引自增：插入一条新记录，都是追加操作，不需要重新移动数据（非自增，页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。）

索引设置不为空：空为让索引难以优化，占用空间

防止索引失效：%不出现在左边，不用函数计算，类型转换，注意最左匹配原则，or前后都要是索引

常见问题总结​

索引区分度问题？​

区分度=列去重后的个数/总个数，如果低于30%，可能会走全表扫描

什么时候需要索引？​

字段唯一，经常where查询，经常排序

不需要的时候：重复数据，数据少，经常更新

执行计划？​

对于执行计划，参数有：

  • possible_keys 字段表示可能用到的索引；
  • key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
  • key_len 表示索引的长度；
  • rows 表示扫描的数据行数。
  • type 表示数据扫描类型，我们需要重点看这个。

type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

  • All（全表扫描）；
  • index（全索引扫描）；
  • range（索引范围扫描）；
  • ref（非唯一索引扫描）；
  • eq_ref（唯一索引扫描）；
  • const（结果只有一条的主键或唯一索引扫描）。

在这些情况里，all 是最坏的情况，因为采用了全表扫描的方式。index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。所以，要尽量避免全表扫描和全索引扫描。

range 表示采用了索引范围扫描，一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。

ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。

const 类型表示使用了主键或者唯一索引与常量值进行比较，比如 select name from product where id=1。

需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。

0%