索引策略，性能爆炸！！！
前言
上一篇说了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 descselect * from T where A > '1' order by A desc,B desc 「不能使用索引排序的情况：」
不同的排序方向：
select * from T where A = '1' order by A desc,B ascorder 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,Cselect * from T where A=1 and B in ('a','b') order by C descEXPLAIN的Extra列出现“Using Where”，表示服务器将存储引擎返回行以后再应用where条件。
不管是范围条件查询还是In条件查询，EXPLAIN的type都是range，对于范围条件查询，MySQL无法再使用范围列后面的其他索引列了，但是IN查询则没有这个限制。
冗余和重复索引
重复索引是指相同的列上按照相同的顺序创建相同类型的索引。
冗余索引指已有一个多列索引，又创建了这个多列索引的最左前缀索引，比如又一个（A，B，C）的索引，同时建立一个（A）或者（A，B），这就时冗余索引。
未使用的索引
未使用的索引应当删除
索引和锁
索引可以让查询锁定更少的行。
锁定行的时候会带来额外开销，锁定超过需要的行会增加锁争用并减少并发性。
往期推荐
下期见
Liusy01
一个分享Java后端学习日志和健身的公众号
欢迎大家点个在看
分享至朋友圈
好文！点个在看