MySQL 索引

MySQL 索引使用的数据结构主要有 BTree索引 和 哈希索引 。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择 BTree 索引。

MySQL 的 BTree 索引使用的是 B 树中的 B+Tree。

InnoDB 索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为 InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。

每一个索引在 InnoDB 里面对应一棵 B+ 树。

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

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

单列索引

联合索引

联合索引基于一个表中的多个字段的索引。联合索引中即使字段一样，但顺序不同，也属于不同的联合索引，查询速度也不同。

以联合索引(a,b,c)为例，建立这样的索引相当于建立了索引a、ab、abc三个索引。一个索引顶三个索引当然是好事，毕竟每多一个索引，都会增加写操作的开销和磁盘空间的开销。

覆盖(动词)索引

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

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

同样的有联合索引（a,b,c），如果有如下的sql：

select a,b,c from table where a = xxx and b = xxx

那么 MySQL 可以直接通过遍历索引取得数据，而无需读表，这减少了很多的随机 io 操作。减少 io 操作，特别的随机 io 其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
索引列越多，通过索引筛选出的数据越少。

有1000W条数据的表，有如下sql：

select * from table where a = 1 and b = 2 and c = 3

假设每个条件可以筛选出 10% 的数据，如果只有单值索引，那么通过该索引能筛选出 1000W * 10% = 100w 条数据，然后再回表从 100w 条数据中找到符合 b = 2 and c = 3 的数据，然后再排序，再分页。

如果是复合索引，通过索引筛选出 1000w * 10% * 10% * 10% = 1w，然后再排序、分页，更高效。

注意：

1. 创建联合索引时，要考虑列的顺序，如果使用前几列查询，联合索引就会有效，后几列查询，联合索引无效。
2. 联合索引使用最左前缀原则，例如 A, B 两个字段都会在查询中用到，但 A 使用的频率更高，就将 A 作为联合索引的第一个字段，放在最左边。
3. 当存在多个单列索引可以用时，mysql会根据查询优化策略选择其中一个单列索引，并不是每个单列索引都生效。
4. 当同时存在单列索引和联合索引，mysql会根据查询优化策略选择其中一个索引。
5. 如果where中的关系是or，索引不生效。

最左前缀原则

索引的最左前缀原则，可以加速检索。这个最左原则可以是联合索引的最左的N个字段，也可以是索引的最左M个字符。

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

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

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

聚簇索引与非聚簇索引

Innodb 的次索引指向对主键的引用 (聚簇索引)。

MyISAM 的次索引和主索引都指向物理行 (非聚簇索引)。

InnoDB 的主索引的节点与数据放在一起，次索引的节点存放的是主键的位置。

MyISAM 的主索引和次索引都指向该数据在磁盘的位置。

聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引（理由：数据一旦存储，顺序只能有一种）。

《数据库原理》书中写道：聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

InnoDB 的的二级索引的叶子节点存放的是 KEY 字段和主键值。因此，通过二级索引查询首先查到是主键值，然后 InnoDB 再根据查到的主键值通过主键索引找到相应的数据块。

MyISAM 的二级索引叶子节点存放的还是列值与行号的组合，叶子节点中保存的是数据的物理地址。所以可以看出 MYISAM 的主键索引和二级索引没有任何区别，主键索引仅仅只是一个叫做 PRIMARY 的唯一、非空的索引，且MYISAM引擎中可以不设主键。

对 Innodb 来说：

1. 主键索引既存储索引值,又在叶子中存储行的数据。

2. 如果没有主键, 则会 Unique key 做主键。

3. 如果没有 Unique key ,则系统生成一个内部的 rowid 做主键。

唯一索引与普通索引

结论：如果业务代码已经保证了不会写入重复，从性能的角度考虑，选择普通索引而不是唯一索引。

唯一索引的更新不能使用 change buffer，实际上也只有普通索引可以使用。

两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，建议尽量选择普通索引。

在表中插入一个新记录包含有 k = 4

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下：

• 对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。

这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。

但，这不是我们关注的重点。

第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下：

• 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

索引失效

1）or 语句前后没有同时使用索引。当 or 左右查询字段只有一个是索引，该索引失效，只有当 or 左右查询字段均为索引时，才会生效。这也是为什么尽量少用 or 的原因。

注意：要想使用 or，又想让索引生效，只能将 or 条件中的每个列都加上索引。

2）like 查询是以 % 开头。只有 % 不在第一个位置，索引才会起作用。

问题：like’%字符串%’ 索引不被使用的解决方法？
a）使用覆盖索引，查询字段必须是建立覆盖索引字段

EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';

b）当覆盖索引指向的字段是 varchar(380) 及 380 以上的字段时，覆盖索引会失效

**3）**类型转换，隐式转换导致索引失效。如果列类型是字符串，那一定要在条件中将数据使用引号包起来，否则不使用索引。

**4）**如果 mysql 估计使用全表扫描要比使用索引快,则不使用索引。

5）查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)。

错误的例子：

select * from test where id-1=9; 

注：sql 中相等判断是用 1 个等号。

正确的例子：

select * from test where id=10; 

**6）**使用不等于 != 或者 <> 时。

**7）**is null, is not null

**8）**not in, not exist

**9）**没有查询条件，或者查询条件没有建立索引。

**10）**查询的数量是大表的大部分，应该是 30％ 以上。

另：

索引失效分析工具：

将 explain 命令加在要分析的 sql 语句前面，在执行结果中查看 key 这一列的值，如果为 NULL，说明没有使用索引。

查看索引的使用情况：

show status like ‘Handler_read%';

handler_read_key: 这个值越高越好，越高表示使用索引查询到的次数。
handler_read_rnd_next: 这个值越高，说明查询低效。