MySQL-索引

MySQL-索引

本文主要参考丁奇的mysql实战四十五讲

什么是索引？

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。一本 500 页的书，如果你想快速找到其中的某一个知识点，在不借助目录的情况下，那我估计你可得找一会儿。同样，对于数据库的表而言，索引其实就是它的“目录”

InnoDB的索引模型

众所周知的InnoDb索引的底层是由B+树实现的，每一个索引对应法这一课b+树

建表语句

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)

这张表中一共有主键索引（ID），辅助索引（k）
主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

基于主键索引和普通索引的查询有什么区别？

如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；

如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

索引维护

因为B+树的叶子结点存在的是数据页，而其所有的叶子结点的数据都是按规则有序的双向链表，当插入新的数据时，如果主键不是按规则递增的话，就可能会出现新的数据插入已经满的数据页从而导致当前数据页的分裂，导致整个空间利用率下降。这也是为什么最好用自增ID作为索引字段

当然有分亦有和，当两个数据页由于数据删除利用率很低后，相邻的会合并

主键的选择

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。

由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。

显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小，从性能和存储空间方面考量，自增主键往往是更合理的选择。

回表

现在，我们一起来看看这条 SQL 查询语句的执行流程：

1. 在 k 索引树上找到 k=3 的记录，取得 ID = 300；
2. 再到 ID 索引树查到 ID=300 对应的 R3；
3. 在 k 索引树取下一个值 k=5，取得 ID=500；
4. 再回到 ID 索引树查到 ID=500 对应的 R4；
5. 在 k 索引树取下一个值 k=6，不满足条件，循环结束。

**回到主键索引树搜索的过程，我们称为回表，**可以看到，这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。认为是扫描了两行

覆盖索引（覆盖主键索引，不回表）

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为***覆盖索引***。

由于覆盖索引可以***减少树的搜索次数***，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

最左前缀原则

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录

首先我们建立一个联合索引（name，age）
如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张 %’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

在建立联合索引的时候，如何安排索引内的字段顺序？

• 第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。
• 空间原则：基于第一原则，假如b字段很长，而又需要b单独索引，可以将索引设置为（a,b）

思考 select * from T where a =x and b>y and c = z, 走那种索引

这个就要牵扯到索引失效的情况了

1.有or必全有索引;
2.复合索引未用左列字段;
3.like以%开头;
4.需要类型转换;
5.where中索引列有运算;
6.where中索引列使用了函数;
7.如果mysql觉得全表扫描更快时（数据少）;

那么可以看到，当对索引列进行运算时，就会出现索引失效的问题

(1) select * from mytable where a=3 and b=5 and c=4;
abc 三列都使用索引，而且都有效
(2) select * from mytable where c=4 and b=6 and a=3;
mysql没有那么笨，不会因为书写顺序而无法识辨索引。
where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样。
(3) select * from mytable where a=3 and c=7;
a 用到索引，sql中没有使用 b列，b列中断，c没有用到索引
(4) select * from mytable where a=3 and b>7 and c=3;
a 用到索引，b也用到索引，c没有用到。
因为 b是范围索引，所以b处断点，复合索引中后序的列即使出现，索引也是无效的。
(5) select * from mytable where b=3 and c=4;
sql中没有使用a列， 所以b，c 就无法使用到索引
(6) select * from mytable where a>4 and b=7 and c=9;
a 用到索引， a是范围索引，索引在a处中断， b、c没有使用索引
(7)select * from mytable where a=3 order by b;
a用到了索引，b在结果排序中也用到了索引的效果。前面说过，a下面任意一段的b是排好序的
(8) select * from mytable where a=3 order by c;
a 用到了索引，sql中没有使用 b列，索引中断，c处没有使用索引，在 Extra列 可以看到 filesort
(9) select * from mytable where b=3 order by a;
此sql中，先b，后a，导致 b=3 索引无效，排序a也索引无效。

索引与%

索引下推（减少回表次数）

联合索引（name, age）

mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;

这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录 ID3

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接***过滤掉不满足条件的记录***，减少回表次数。
主键索引（a，b）,辅助索引（c）,(c,a),(c,b),请问是否所有索引都是必要的？