索引策略，性能爆炸！！！

前言

上一篇说了MySQL有哪几种索引类型，今天就来记录一下具体的索引策略。

相信大家在面试时候也会遇到如何进行查询优化的问题，其中索引相关的策略就是重点考察项，比如怎么设置索引列等。

下面就详细聊一下有哪些索引策略，内容来自于《高性能MySQL》一书。

独立的列

如果查询中的列不是独立的列，则MySQL就不会使用索引。

「独立的列」指的是索引列不能是表达式中的一部分，也不能是函数的参数。

比如student这张表，有一个索引idx_age;

需要对age列进行查询。

select * from student where age=20;

此时会使用idx_age这个索引

但如果是如下查询

select * from student where age+1=20;

此时就不会使用到idx_age这个索引

前缀索引和索引选择性

「索引的选择性」是指不重复的索引值和数据表记录总数的比值，范围从1/N到1之间。

比如十个人，职业有5种，那么这个选择性就是5/10，也就是1/2.

索引的选择性越高，查询效率越高，可过滤掉更多的行数。

索引开始的部分字符，可以节约索引空间，提高索引效率，但也会降低索引的选择性。

「如何计算完整列的选择性？」

比如student表中的age列的选择性

select count(distinct age)/count(*) from student;

《高性能MySQL》书中说是选择性趋近于0.031，基本就可用。

「如何创建前缀索引？」

比如对student表中的name列做前缀索引，数字n是指用前n个字符用来做索引

alter table student add key(name(n));

对于BLOB、TEXT或者很长的Varchar类型的列，必须使用前缀索引，MySQL不支持索引这些列的完整长度。

计算合适的前缀长度的即是计算完整列的选择性，并使前缀列的选择性接近于完整列的选择性。

「如何确定n？」

select count(1) as count,left(remark,2) pre from student group by pre;

select count(distinct left(remark,2))/count(*) from student;

可选择几个参数，直至算出合适的前缀长度为止，例如：

select 
count(distinct left(remark,2))/count(*) as pre2,
count(distinct left(remark,3))/count(*) as pre3,
count(distinct left(remark,4))/count(*) as pre4,
count(distinct left(remark,5))/count(*) as pre5,
from student;

多列索引

索引合并策略有时候是一种优化的结果，但实际上更多时候说明表上的索引建的很糟糕：

1、当出现对多个索引做相交操作时候（AND），意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。

2、当需要对多个索引做联合操作（OR）时候。

比如对student表的age和name列做一个联合索引

alter table student add key idx_age_remark(age,name);

此时使用age列查询会使用索引

但此时使用name列查询却不会使用索引

这就是索引的最左前缀原则，只有使用索引的最左前缀列进行检索才会使用索引。

「何为最左前缀？」

比如索引是这样（a,b,c），那么a，ab，abc就是最左前缀，此时会使用到索引，但是如果用b，bc，ac进行检索，就不会使用到索引

「选择合适的索引列顺序」

正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要。

索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。索引可以按照升序和降序进行扫描，以满足精确符合列顺序的GROUP BY、ORDER BY和DISTINCT等子句的查询需求。

经验法则：

将选择性最高的列放在索引最前列

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，InnoDB上的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

当表有聚簇索引时，数据行实际存放在索引的叶子页中，表示数据行和相邻的键值紧凑的存储在一起。同时无法将数据行存储在两个不同的地方，所以一个表只能由一个聚簇索引。

叶子页包含了行的全部数据，节点页只包含了索引列。

如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，会隐式定义一个主键来作为聚簇索引

优点

（1）可把相关数据保存在一起

（2）数据访问更快。将索引和数据保存在同一个B-Tree中，不用回表查询

（3）使用覆盖索引扫描的查询可以直接使用页节点的主键值。

缺点

（1）最大限度提高了IO密集型应用的性能，但如果数据全部存放在内存中，那么访问的顺序就没那么重要了。

（2）插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。如果不是按照主键顺序插入，那么加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。

（3）更新聚簇索引代价很高，会把行数据移到一个新的位置。

（4）基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面领“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中，存储引擎会将该页面分裂成两个页面来容纳该行，导致表占用更多的存储空间。

（5）可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。

（6）二级索引（非聚簇索引）可能比想象的更要大，因为在二级索引的叶子节点中包含了引用行的主键列。

（7）二级索引访问需要两次索引查找。因为二级索引叶子节点中保存的不是指向行的物理位置的指针，而是行的主键值。获取主键之后再去聚簇索引中查找到对应的行

「主键索引」

「二级索引」

覆盖索引

如果一个索引包含或覆盖所有需要查询的字段的值，我们就称之为覆盖索引。

查询只需要扫描索引无需回表的好处：

（1）索引条目通常小于数据行大小，如果只读取索引，那么会极大的减少数据访问量。

（2）索引按照列值顺序存储，对于IO密集型的范围查询会比随机从磁盘读取的每一行数据的IO要少得多。

（3）部分存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此需要一次系统调用，造成性能问题。

（4）由于InnoDB的聚簇索引，覆盖索引对InnoDB表非常有益。InnoDB的二级索引在叶子节点保存了行的主键值，所以如果二级索引能够覆盖查询，则可以避免对主键索引的二次查询。所以InnoDB的二级索引查询时也可以用到覆盖索引。

当发起一个覆盖索引查询时，在EXPLAIN的Extra列可以看到“Using index”的信息。

比如我上面创建了一个age和name的联合索引，那么我只查询这两列的数据。

使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：

通过操作排序、或者按索引顺序排序，如果EXPLAIN出来的type值是index，则说明MySQL使用了索引扫描来做排序。

如果索引不能覆盖查询所需的全部列，那就不得不没扫描一条索引记录就回表查询一次对应的行。这基本上都是随机IO。

MySQL可以使用同一个索引既满足排序、又满足查找行。

只有当索引的列顺序和Order by子句的顺序完全一致，并且所有列的排序方向（desc或asc）都一致时，才能使用索引对结果进行排序。

如果查询需要关联多张表，只有当Order by子句引用的字段全部为第一个表时，才能使用索引进行排序。

Order by子句和查找型查询的限制是一样的，需要满足索引最左前缀的要求。否则，都需要执行排序操作。

有一种情况下可以不满足最左前缀的要求，就是前导列为常量的时候。如果Where或Join子句中对这些列指定了常量，就可以弥补索引的不足。

例如：表T中有多列索引（A，B，C）

select * from T where A='1' order by B,C

最左前缀：

select * from T where A='1' order by B desc

select * from T where A > '1' order by A desc,B desc 

「不能使用索引排序的情况：」

不同的排序方向：

select * from T where A = '1' order by A desc,B asc

order by子句存在不在索引中的列：

select * from T order by A desc,D desc

非最左前缀：

select * from T where A=1 order by C desc

范围查询：

select * from T where A>1 order by B,C

select * from T where A=1 and B in ('a','b') order by C desc

EXPLAIN的Extra列出现“Using Where”，表示服务器将存储引擎返回行以后再应用where条件。

不管是范围条件查询还是In条件查询，EXPLAIN的type都是range，对于范围条件查询，MySQL无法再使用范围列后面的其他索引列了，但是IN查询则没有这个限制。

冗余和重复索引

重复索引是指相同的列上按照相同的顺序创建相同类型的索引。

冗余索引指已有一个多列索引，又创建了这个多列索引的最左前缀索引，比如又一个（A，B，C）的索引，同时建立一个（A）或者（A，B），这就时冗余索引。

未使用的索引

未使用的索引应当删除

索引和锁

索引可以让查询锁定更少的行。

锁定行的时候会带来额外开销，锁定超过需要的行会增加锁争用并减少并发性。

往期推荐

• k8s全系列文章汇总
• 数据库事务
• Innodb存储引擎详解
• 23个常用设计模式

下期见

Liusy01

一个分享Java后端学习日志和健身的公众号

欢迎大家点个在看

分享至朋友圈

好文！点个在看