数据库-mysql-小林coding-索引篇

本系列笔记为作者在跟随小林coding学习的时候做的笔记。感谢小林大大。

索引常见面试题

什么是索引

索引是数据的目录，位于存储引擎中

索引的分类

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

按数据结构分类

创建的聚簇索引和二级索引默认使用的是 B+Tree 索引：

• 即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。
• 支持范围查询

通过二级索引查询到主键后使用查到的主键查询聚簇索引的过程叫「回表」，也就是说要查两个 B+Tree 才能查到数据

覆盖索引：当查询的数据是能在二级索引的 B+Tree 的叶子节点里查询到时就不用再查主键索引，也就是只需要查一个 B+Tree 就能找到数据。

按物理存储分类

• 聚簇索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
• 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

按字段特性分类

1. 主键索引

建立在主键字段上的索引，一张表最多只有一个主键索引，索引列的值不允许有空值。

1. 唯一索引

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

3. 普通索引

建立在普通字段上的索引

1. 前缀索引

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

按字段个数分类

• 建立在单列上的索引称为单列索引，比如主键索引；
• 建立在多列上的索引称为联合索引；

使用联合索引时，存在最左匹配原则，不一定每个索引字段都会用到

范围查询（如 >、<）会停止匹配，>=、<=、BETWEEN、like 可以继续匹配，即得有等值判断才能继续匹配

索引下推：innodb默认只判断一个条件，如果有联合索引且另一个条件为联合索引最左匹配的下一个字段的等值条件，总结一下就是server层的判断下推到db引擎对联合索引的判断。

当查询语句的执行计划里，出现了 Extra 为 Using index condition，那么说明使用了索引下推的优化。

索引区分度：区分度就是某个字段 column 不同值的个数「除以」表的总行数。建立联合索引把区分度大的字段排在前面。

对于排序order by语句可以将排序字段和条件字段创建一个联合索引

什么时候需要 / 不需要创建索引

索引的缺点

• 占用空间；
• 增删改操作维护索引时间开销大；

什么时候适用索引

• 唯一性限制的字段；
• 经常用于 WHERE 查询条件的字段，多个字段用联合索引。
• 经常用于 GROUP BY 和 ORDER BY 的字段，多个字段用联合索引。

什么时候不需要创建索引

• WHERE 条件，GROUP BY，ORDER BY 里用不到的字段。
• 字段中存在大量重复数据，比如性别字段。查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
• 表数据太少的时候；
• 经常更新的字段。

有什么优化索引的方法

• 前缀索引优化；字段中字符串的前几个字符建立索引
• 覆盖索引优化；将查询和查到的字段都建立为联合索引
• 主键索引最好是自增的
• 索引设置为 NOT NULL
• 防止索引失效：索引失效见下文，执行计划详见基础篇

从数据页的角度看 B+ 树

InnoDB 是如何存储数据的

• 第一个分组中的记录只能有 1 条记录；
• 最后一个分组中的记录条数范围只能在 1-8 条之间；
• 剩下的分组中记录条数范围只能在 4-8 条之间。

B+ 树是如何进行查询的

和搜索树一样

聚簇索引和二级索引

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

MySQL 单表不要超过 2000W 行

见原文

x为一页能放下的页号数（最大是x叉树），y为一页能放下的记录数，z为树高，total为全部记录数量

total=x^(z-1)*y

页大小为16kb，算15kb存数据，页号大小为12byte（主键8byte，页号4byte（地址）），那么x=15*1024/12≈1280

记录大小算1kb，那么y为15，z=2时total=19200，z=3时total=2.45kw

所以主要原因就是超过2000W行（小林大大算出来是2.45kw）后，B+树会变成4层，增加的查询的时间算小头，大头是由于索引树太庞大，内存无法一次全部装入会缺页中断导致的磁盘IO的开销。

索引失效有哪些

• 对索引使用左或者左右模糊匹配(%)
• 对索引进行了运算或使用了函数
• 联合索引非最左匹配
• 索引条件 OR 其他条件

MySQL 使用 like “%x“，索引一定会失效吗？

对于可以使用覆盖索引的情况，会直接查二级索引树，由于叶子节点更小遍历速度比直接查询主键索引更快

count(*) 和 count(1) 有什么区别？哪个性能最好

如何优化 count(*)

第一种，近似值

执行 explain 命令效率是很高的，因为它并不会真正的去查询，下图中的 rows 字段值就是 explain 命令对表 t_order 记录的估算值。

第二种，额外表保存计数值

当我们在数据表插入一条记录的同时，将计数表中的计数字段 + 1。也就是说，在新增和删除操作时，我们需要额外维护这个计数表。

MySQL 自增主键不连续情况

微信文章原文

1. auto_increment_offset自增初始值和auto_increment_increment自增步长设置不为 1
2. 唯一键冲突，+1后不减回
3. 事务回滚，不回滚自增值
4. 批量插入（如 insert…select 语句），多轮插入，第一轮使用1个id，后面每一轮id数是前一轮的两倍，多用了不减回