彻底搞懂数据库索引

一、索引基础

1.索引定义

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

2.索引类型

用于提高读写效率的数据结构有很多，这里先介绍常见的 3 种，分别是：

• 哈希表
• 有序数组
• 搜索树（主要）

3.搜索树索引——B+树

根据叶子结点的内容，索引类型分为主键索引和非主键索引。

• 主键索引的叶子结点存的是整条记录，主键索引也被称为聚簇索引（clustered index）。
• 非主键索引的叶子结点存的是主键的值，非主键索引也被称为二级索引（secondary index）/ 普通索引 / 辅助索引。

那么，基于主键索引和非主键索引的查询有什么区别？

• 如果语句是 select * from T where ID=500，即主键查询，则只需要搜索 ID 这棵树。
• 如果语句是 select * from T where k=5，即非主键索引查询，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。从非主键索引回到主键索引的过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。而从存储空间的角度讲，因为非主键索引树的叶结点存放的是主键的值，那么，应该考虑让主键的字段尽量短，这样非主键索引的叶子结点就越小，非主键索引占用的空间也就越小。一般情况下，建议创建一个自增主键，这样非主键索引占用的空间最小。

4. 联合索引

联合索引是指对表上的多个列进行索引。

联合索引 (a, b) 是根据 a, b 进行排序（先根据 a 排序，如果 a 相同则根据 b 排序）。因此，下列语句可以直接使用联合索引得到结果（事实上，也就是用到了最左前缀原则）：

• select … from xxx where a=xxx;
• select … from xxx where a=xxx order by b;

而下列语句则不能使用联合查询：

• select … from xxx where b=xxx;

对于联合索引 (a, b, c)，下列语句同样可以直接通过联合索引得到结果：

• select … from xxx where a=xxx order by b;
• select … from xxx where a=xxx and b=xxx order by c;

而下列语句则不行，需要执行一次 filesort 排序操作。

• select … from xxx where a=xxx order by c;

5.最左前缀原则

不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。利用索引的 “最左前缀” 原则来定位记录，避免重复定义索引。

因此，基于最左前缀原则，我们在定义联合索引的时候，考虑如何安排索引内的字段顺序就至关重要了！评估的标准就是索引的复用能力，比如，当已经有了 (a,b) 字段的索引，一般就不需要再单独在 a 上建立索引了。

6.回表和覆盖索引

如果where子句中的一个条件是非主键索引，那么查询的时候，先通过非主键索引定位到主键索引（主键位于非主键索引搜索树的叶子节点）；然后通过主键索引定位到查询的内容。在这个过程中，回到主键索引树的过程，称为回表。

但是当我们的查询内容是主键值，那么可以直接提供查询结果，不需要回表。也就是说，在这个查询里，非主键索引 已经 “覆盖了” 我们的查询需求，故称为覆盖索引。

总结：覆盖索引就是从辅助索引中就能直接得到查询结果，而不需要回表到聚簇索引中进行再次查询，所以可以减少搜索次数（不需要从辅助索引树回表到聚簇索引树），或者说减少 IO 操作（通过辅助索引树可以一次性从磁盘载入更多节点），从而提升性能。

7.索引下推

MySQL 5.6 引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。

---

二、前缀索引：

当索引是很长的字符序列时，这个索引将会很占内存，而且会很慢，这时候就会用到前缀索引了。所谓的前缀索引就是去索引的前面几个字母作为索引，但是要降低索引的重复率，索引我们还必须要判断前缀索引的重复率。

mysql> select 1.0*count (distinct name)/count (*) from test 

这是比较整个 name 的重复率，当时这是最好的情况。然后分别截取 name 字符的前几个字母，最后选取的计算值要接近整个取整个 name 时得出的计算值，然后再选中占用空间小的。由上面执行的结果可知应选中 name 的前 4 个字母作为索引最为适合。

创建索引：

mysql> alter table test add key(name(4));

---

三、text和blob类型对比

blob 可变长二进制数据，最多存2¹⁶-1个字节
text 最多存2¹⁶-1个字节

---

四、使用索引

explain的type字段：

• ALL: 全表扫描
• index: 索引全扫描
• range: 索引范围扫描
• ref: 使用非唯一索引扫描
• eq_ref: 使用唯一索引扫描

增加普通的 BTREE 索引

 ALTER TABLE dept ADD INDEX index_remark (`remark`); 

增加联合 BTREE 索引

 ALTER TABLE dept ADD INDEX index_all (`deptName`,`remark`,`createDate`);  

上面两个 SQL 索引相同 Type 不同是因为 BTREE 索引中匹配最左前缀 比如给 col1+col2+col3 字段上加联合索引能够被包含

col1+col2 、col1+col2+col3、col1+col3 使用 (顺序没有影响 比如 where col1=? and col2=? 和 where col2=? and col1=? 结果一样)

使用联合索引 相当于一下子创建了如上的三个索引，这就是联合索引的好处

---

五、存在索引但不能使用索引的经典场景

1. 以 % 开头的 LIKE 查询不能使用 BTREE 索引

explain select deptName,remark,createDate from dept where deptName like'%2' and remark = 'test'and createDate ='2018-07-22'; type index

2. 复合索引时 不符合最左匹配原则 (上面已经提到)

explain select deptName,remark,createDate from dept where remark = 'test'and createDate ='2018-07-22'; type index

复合索引最左匹配原则的成因：

Mysql 创建复合索引的规则是首先会对复合索引最左边，也就是第一个字段的索引进行排序，

在第一个字段排序的基础上，在对第二个字段进行排序，所以直接使用第二个字段是没有顺序的

3. 用 or 分隔开的条件，如果 or 前的条件中的列有索引，后面的列中没有索引，那么涉及到的索引都不会使用到

explain select deptName,remark,createDate from dept where deptName =‘2’ and remark = 'test’and createDate =‘2018-07-22’ or salary =200; type ALL

---

六、Show Profile

1.Show Profile 是 mysql 提供的可以用来分析当前会话中 sql 语句执行的资源消耗情况的工具，可用于 sql 调优的测量。默认情况下处于关闭状态，并保存最近 15 次的运行结果。

2.通过 set profiling = on 开启show profile；通过 show profiles 查看 sql 语句的耗时时间，然后通过 show profile 命令对耗时时间长的 sql 语句进行诊断。

3.show profile 的常用查询参数。

①ALL：显示所有的开销信息。

②BLOCK IO：显示块 IO 开销。

③CONTEXT SWITCHES：上下文切换开销。

④CPU：显示 CPU 开销信息。

⑤IPC：显示发送和接收开销信息。

⑥MEMORY：显示内存开销信息。

⑦PAGE FAULTS：显示页面错误开销信息。

⑧SOURCE：显示和 Source_function，Source_file，Source_line 相关的开销信息。

⑨SWAPS：显示交换次数开销信息。