mysql索引十连问｜ 剑指offer - mysql

• 大白话 mysql 之深入浅出索引原理 - 上
• 大白话 mysql 之深入浅出索引原理 - 下

什么是索引？

索引类似书本的目录，查询书中的指定内容时，先在目录上查找，之后可快速定位到内容位置。在数据库中通常通过 B 树 / B + 树数据结构实现。

主键索引和非主键索引有什么区别？

主键索引树中叶子节点存储的是整行数据，而非主键索引叶子节点上保存的是主键的值。使用非主键索引时，先从非主键索引获取到行对应主键 ID，之后再根据 id 在主键索引树上搜索对应行数据，这个过程也被称为回表。

一般使用什么字段作为主键，为什么？

一般使用 innodb 的自增整数类型作为主键：

• 因为自增，容易保证主键索引的有序性，同时还能避免新数据中间位置插入时导致的页分裂；
• 二级索引叶子节点上保存的是主键值，整数类型主键长度较小，二级索引树占用的空间较小。

索引使用场景

where

为查询条件字段创建索引，以达到快速过滤指定条件数据的目的。

order by

当使用 order by 将查询结果按某个字段排序时，可考虑为该字段创建索引。没有索引时，会先将查询结果放到内存中进行排序（若内存空间不足，会利用磁盘辅助排序），比较影响查询效率。索引本身是有序的，可以直接按索引的顺序逐条回表取出数据即可。如果是分页查询，效果更好，这时候只需要取出某个范围的索引对应的数据，而不需要取出所有满足条件的数据排序后再截取返回分页数据。

join

使用 join 时，为被驱动表的关联字段创建索引，可以有效提高查询效率。比如 select * from t1 straight_join t2 on (t1.a=t2.a) where t1.b = 'xxxx'; t2 的字段 a 上有索引，查询过程会是先从表 1 中依次取出满足条件的行数据，之后用行数据中的 a 字段去 t2 上匹配后将两表字段拼接返回，此时能使用到 t2.a 的索引，避免了 t2 全表扫描。

索引覆盖

如果 select 字段 + where 字段字段列数不太多且查询频繁时，可以考虑为 select 和 where 字段创建联合索引，避免查询时回表，提高查询效率。比如 select a from t where b = ‘xx’, 创建联合索引 (b, a)， 此时扫描索引树后，就已经得到需要查询的字段 a 了，不需要再回表。需要注意的是联合索引字段的顺序，这个语句无法使用到索引 (a, b)。

创建索引需要注意的地方

• 最左前缀匹配原则，联合索引需要注意索引字段的顺序，mysql 会一直向右匹配直到遇到范围查询 (>、<、between、like) 就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 4 , 如果建立 (a,b,c,d) 顺序的索引，d 是用不到索引的。字段是否用到索引的意思是字段是否能利用字段在索引中的有序性进行快速过滤。索引 (a,b,c,d), 在索引树上是先按 a 进行排序，再按 b 进行排序，以此类推，排序规则类似 order by a,b,c,d。上面查询条件中，a 定值，b 是有序的；b 定值，c 是有序的；c 范围查询，剩下的 d 是无序的。所以 d 无法使用到该索引。
• 基数小，区分度低的不适合创建索引。比如性别，最多基数最多总共就 3 个，此时索引过滤性能不高，查完索引后还需回表，可能比直接全表扫描效率更低。
• 更新频繁的字段创建索引时要权衡索引维护成本。
• 尽量扩展索引，比如已经有 a 索引，现在要加 (a,b) 的索引，那么只需要修改原来的索引即可。
• 避免对 text 大字段创建索引，会导致索引树太大，查询效率不高。如果大字段前 n 个字符区分度较高，可以考虑创建前缀索引，只索引开始的部分字符，这样可以节约索引空间，提高索引效率。其缺点是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于覆盖索引 (因为前缀索引树上只有字段的部分内容，需要进行回表)。

什么时候索引会失效？

• 模糊查询时查询条件以”%” 开头无法使用到索引
• 使用 or 查询时，只有当所有的查询条件字段都有索引才能使用到，比如 a=1 or b = 2, 只有当 a 和 b 都有索引才能使用到索引。
• 数据类型出现隐式转换，如 varchar 不加单引号的时候可能会自动转换为 int 类型，这个时候索引失效。
• 在索引列上使用 IS NULL 或者 IS NOT NULL 时候，索引失效，因为索引不会索引空值。
• 在索引字段上使用”NOT、 <>、!=、NOT IN “时是不会使用索引的，这时只会进行全表扫描。
• 对索引字段进行计算操作，函数操作时不会使用索引。
• 当优化器觉得全表扫描速度比索引速度快的时候不会使用索引。一般出现在全表数据比较少的情况下，这时全表扫描比在非主键索引上查找后再回表速度可能更快。
• 联合索引时，查找不满足最左匹配规则，无法使用到联合索引。

innodb 使用 b + 树作为索引模型的原因

Mysql 设计的使用场景比较广泛，需要对遍历查询、单条查询、数据更新都需要较好的性能支持。B + 树的特性是只在叶子节点上存储数据。可以从数据读写方面与哈希表、有序数组、b 树其他几种索引模型进行比较：

• 哈希表：哈希表只能进行等值查询，在处理范围查询和排序查询时，需要全表扫描哈希表。
• 有序数组：有序数组在进行数据更新时成本较大。往数组中间位置添加数据时，需要移动后面的数据位置。
• B 树：b 树在非叶子节点上也存储数据，在遍历数据时，需要对不同层级的节点上的数据进行拼接和排序，这会导致多次磁盘 io。查询效率较低。

如何删除百万级别或以上的数据？

可以考虑先删掉表的索引，等删除数据后再重建索引。当我们在进行数据修改时，需要同时修改索引，这些额外的索引维护成本较低数据修改的效率；同时，大量的数据删除会导致索引数据页产生大量的碎片空间，此时删除数据后重建索引可以使索引树更 “紧凑”，提高磁盘空间利用率。

Innodb 中的 B + 树模型中，N 叉树的 N 能否被修改？

1. 通过调整索引字段大小来修改 N 叉树中非叶子节点存放的是索引信息，索引包含 Key 和 Point 指针。Point 指针固定为 6 个字节，假如 Key 为 10 个字节，那么单个索引就是 16 个字节。如果 B + 树中页大小为 16 K，那么一个页就可以存储 1024 个索引，此时 N 就等于 1024。我们通过改变 Key 的大小，就可以改变 N 的值。
2. 通过修改页大小间接修改，页越大，每页存放的索引数量就越多，N 就越大。

数据页调整后，如果数据页太小层数会太深，数据页太大，加载到内存的时间和单个数据页查询时间会提高，需要达到平衡才行。

如何知道语句有没有走索引查询？

可以利用 explain 查看 sql 语句的执行计划，通过执行计划来分析索引使用情况。