MySQL之索引

前言

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

InnoDB 的索引模型

每一个索引在 InnoDB 里面对应一棵 B+ 树。所以一张表会有多棵B+树。
B+树：

特点

1. 树的叶子节点是双向链表
2. 一个节点的数据对应操作系统一页数据，大概4kb。页内是一个单向链表
3. n叉树的n会比较大，所以b+树的整体高度不高

根据叶子节点的不同我们可以分为两类：主键索引和非主键索引
根据字段数分为两类：普通索引和联合索引

主键索引

主键索引对应的B+树的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引

非主键索引

非主键索引对应的B+树的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引

1. 二级索引叶子节点中并没有存放MySQL表中的一行数据，而是存放的是聚簇索引，所以通过二级索引差需要回表
2. 另外由于二级索引中存放的是聚簇索引，所以聚簇索引的字节数要越少越好，这样能节约每个索引的空间，增加一页存放的行数

联合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀原则

索引实践

覆盖索引

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

最左前缀原则

如果以a,b两个字段建联合索引，那么就不需要为a单独建索引了，使用a查询会默认使用联合索引。可以通过改变联合索引的顺序少建索引

索引下推

可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

举例

一张表我们以（name, age）作联合索引，SQL语句如下：

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

如果无索引下推，则需要回表4次：

有索引下推，则仅仅需要回表2次：

尽量使用普通索引而不是唯一索引

1. 唯一索引不会使用chage buffer，因为每次都要去比较是否是唯一的键，所以iops次数会增多。而普通索引会使用change buffer
2. 唯一索引更容易触发死锁

不使用索引的情况

字段使用函数操作

如t_modified字段有普通索引，但是执行如下SQL语句仍然不会索引：

select count(*) from tradelog where month(t_modified)=7;

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能

字段使用函数操作（隐式）

select * from tradelog where tradeid=110717;

在这里，tradeid字段是字符串，在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。所以上面的查询语句相当于

select * from tradelog where  CAST(tradid AS signed int) = 110717;

注意还有可能因为表的字符集不同而出现隐式转换

优化器选错索引

优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。
优化器根据扫描行数，临时表、是否是主键索引和是否排序等因素来综合排序选择索引。
在频繁的插入和删除数据时，可能出现MySQL估计扫描的行数出现偏差的问题。

set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/

Q1是普通查询，不强制使用索引，耗时如下：

Q1 扫描了 10 万行，显然是走了全表扫描，执行时间是 40 毫秒。Q2 扫描了 10001 行，执行了 21 毫秒。也就是说在没有使用 force index 的时候，MySQL 用错了索引，导致了更长的执行时间。选错索引的原因：

强制使用索引a时扫描的行数(rows)应该为10001行而不是37116，这里MySQL内部出现了bug，导致最后总体估算出现问题，最后选错索引。