针对索引原理的介绍，可以看看我的上一篇介绍 MySQL索引原理，这里主要讲讲聚集索引、非聚集索引、回表、索引覆盖、最左匹配、索引下推和前缀索引的概念。
聚集索引
聚集索引指的是索引树叶子节点存储的是索引ID和对应的行数据，也就是说某个key在索引树中查询到的节点中，直接就包含了整个数据行(整行数据)，可以直接返回。最常见的聚簇索引就是主键ID，如果表设计中没有设置主键ID，则会按照以下规则生成聚簇索引。
如果定义了主键ID，则主键ID就是聚簇索引
如果没有定义主键ID，则以第一个非空唯一索引(NOT NULL Unique)作为聚簇索引
如果都没有，则InnoDB自动给你生成一个隐藏的6位的RowID作为聚簇索引
非聚集索引
非聚簇索引指的是索引树叶子节点存储索引列和主键ID，也就是说通过非聚簇索引只能知道查询key所在行的key值和主键ID值，并不包含行记录其他列的值，如果想要其他列的值则需要回表。
回表
InnoDB引擎在建立索引时，不管是聚集索引还是普通索引都是使用的B+树来创建的。如下建表T(id,age,name)，我们设置了主键ID和普通索引index(name)
create table T(
id int primary key,
name varchar(16),
age int not null,
index (name)
)engine = InnoDB;
在上述的建表中，InnoDB会为我们创建两个索引树：ID聚簇索引树、age非聚簇索引树，他们都是B+树。
这时我们可以有以下操作：
# 示例一：
select * from T where id = 1;
# 解析：通过主键id=1的方式查询，直接走的聚簇索引，索引到的叶子节点拿到所在的数据，是最快的，不用回表。
# 示例二：
select * from T where name = "aa";
#解析：通过索引列name="aa"查询，走的是非聚簇索引，索引到的叶子节点拿到的时id值，需要再通过id走聚簇索引，找到聚簇索引叶子节点的数据，这就是回表。
索引覆盖
覆盖索引指的是被查的数据（select后面的数据）建立在联合索引上时，这个现象就是索引覆盖。就是说我们在select后需要具体查询的数据，都建立在一个联合索引上面，sql语句在执行时通过InnoDB引擎直接在这个联合索引树中直接索引到需要的数据，直接返回。（这个过程没有回表）
# 示例一：
select id,name from T where name = "aa";
# 解析：能够命中name索引，走非聚簇索引name列，索引到叶子节点拿到id值，以及索引name列值，都是select需要查询的数据，此时无需回表，直接返回。
# 示例二：
select id,name,age from T where name = "aa";
# 解析：能够命中name索引，走非聚簇索引name列，索引到叶子节点拿到id值，以及索引name列值，但是age值需要通过id走聚簇索引回表查询，才能拿到，所以不是覆盖索引。此时如果建立联合索引：(name, age)就可以了，能够避免回表，能够提升查询效率。
最左匹配
最左匹配原则指的是在联合索引中，当InnoDB引擎执行某条sql时，where条件出现了联合索引中的列值，此时将会按照最左匹配原则去匹配数据。假设上面table T(id,age,name)建立了联合索引index(age,name)
T(id,age,name)建立了联合索引index(name,age)
# 示例一：
select id,name from T where age = 11;
# 解析：此时不会触发索引，将全表扫描，因为不符合最左匹配原则：先按照age匹配数据，在按照name匹配数据。
# 示例二：
select id,name,age from T where name = "aa";
# 解析：能够命中联和索引，按照最左匹配原则，将首先按照age去匹配了数据。
索引下推
指的是在使用索引查询时，会按照最左匹配原则，MySQL5.6引入的索引下推原则（IPC优化）会先按照最左侧索引查询数据，然后再按照右侧索引做优化查询，减少回表次数。
如上表T(id,age,name)建立了联合索引idnex(name, age)，需要查询：name=张、age=10的数据，可以：
mysql> select * from T where name like '张%' and age=10;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | 张1 | 11 |
+----+------+-----+
| 2 | 张2 | 22 |
+----+------+-----+
| 3 | 张3 | 33 |
+----+------+-----+
| 4 | 张4 | 44 |
+----+------+-----+
解析：按照最左匹配原则，先按照name走非聚簇索引找到姓张的数据，假设有4条数据，那一般做法是按照四条数据ID值挨个回表，做四次回表操作，匹配到符合的数据。如果是引入了索引下推的IPC优化原则，则在按照name列找到数据后，直接继续按照age过滤age=10的数据，拿到叶子节点的id时，再次做回表。此时的回表次数是大大降低的。
前缀索引
有时候需要索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指不重复的索引值（也称为基数，cardinality)和数据表的记录总数的比值，范围从0到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。
比如一个全球城市表City(city)，city值为全球城市的名称，我们当然可以为他创建索引index(city)，但是我们观察这些数据时就会发现：很多城市名称是相同的，甚至很多city数据的前几位数据时一样的。针对这种情况我们可以换种思路：是不是可以创建索引index(city(x))（其中x表示city的前x位），用来保证索引较短的同时，保证和建立索引index(city)一样的查询性能？答案是肯定的！我们可以通过一下手段，通过不停的查询city列前X位的选择性(前10名城市的频率)，来确定一个稳定的选择性。
mysql> select count(*) as cnt,left(city,5) as pref from City group by pref order by cnt desc limit 10;
+-----+-------+
| cnt | pref |
+-----+-------+
| 10 | South |
| 8 | Garde |
| 7 | Emeis |
| 7 | Escob |
| 6 | Amroh |
| 6 | Yingk |
| 6 | Moncl |
| 6 | Lanca |
| 6 | Jelet |
| 6 | Tegal |
+-----+-------+
10 rows in set (0.01 sec)
mysql> select count(*) as cnt,left(city,6) as pref from City group by pref order by cnt desc limit 10;
+-----+--------+
| cnt | pref |
+-----+--------+
| 8 | Garden |
| 7 | Emeish |
| 7 | Escoba |
| 6 | Amroha |
| 6 | Yingko |
| 6 | Lancas |
| 6 | Jelets |
| 6 | Tegal |
| 6 | Monclo |
| 6 | Ambatt |
+-----+--------+
rows in set (0.00 sec)
通过上面改变不同前缀长度发现，当前缀长度为6时，这个前缀的选择性就接近完整列的选择性了。甚至是一样的。
当然还有另外更方便的方法，那就是计算完整列的选择性，并使其前缀的选择性接近于完整列的选择性。下面显示如何计算完整列的选择性：
mysql> select count(distinct city) count(*) from City;
+---------------------------------+
| count(distinct city) count(*) |
+---------------------------------+
| 0.4283 |
+---------------------------------+
row in set (0.05 sec)
可以在一个查询中针对不同前缀长度的选择性进行计算，这对于大表非常有用，下面给出如何在同一个查询中计算不同前缀长度的选择性：
mysql> select count(distinct left(city,3))/count(*) as sel3,
-> count(distinct left(city,4))/count(*) as sel4,
-> count(distinct left(city,5))/count(*) as sel5,
-> count(distinct left(city,6))/count(*) as sel6
-> from City;
+--------+--------+--------+--------+
| sel3 | sel4 | sel5 | sel6 |
+--------+--------+--------+--------+
| 0.3367 | 0.4075 | 0.4208 | 0.4267 |
+--------+--------+--------+--------+
1 row in set (0.01 sec)
可以看见当索引前缀为6时的基数是0.4267，已经接近完整列选择性0.4283。在上面的示例中，已经找到了合适的前缀长度，下面创建前缀索引：
mysql> alter table City add key (city(6));
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
索引的选择
可以遵循下面几个规则：
针对很长的字符串做索引，此时需要考虑前缀索引就是说当我们需要建立的索引的列可能很长，如果完全按照该字符串建立索引代价会很大，索引会很大。所以需要按照一定规则选取字符串前几位作为索引，这个规则就是：比率P = 不重复的索引值 / 总记录数。可想而知，比率P越大则查询效率越高，当P=1时是最高的，但是很多时候并不如我们意，需要选取前缀索引，选择该列数据的前面几位作为索引。(此方法创建的前缀索引有弊端：mysql无法根据其做order by，group by，无法用前缀索引做覆盖扫描。) 创建前缀索引：
alter table user add key(name(6));尽量少的建立单列索引，因为这样只能使用到一星索引，还不如直接创建全覆盖索引
选择合适的索引顺序：创建联合索引时，根据最左侧匹配原则，需要按照从左到右，以最具有识别度的列放最左侧。或者根据业务来选择。
考虑覆盖索引：在一些能覆盖到的业务查询时，建立覆盖索引，这样可以减少回表，减少IO操作，减少系统调用和拷贝时间
使用索引扫描来排序：在需要得到有序结果集时，是很占用CPU资源的，而排序最快的就是使用index
索引压缩：针对一些不必要的索引、对冗余的索引可以去除，针对重复索引如创建了单列的索引也存在于联合索引中的，可以优化去除某个。
减少重复、冗余、以及未使用的索引MySQL的主键索引和唯一索引都是通过B+树实现的，所以在创建了主键索引和唯一索引后，就不要再创建普通索引了；比如创建了索引(A,B)，此时再去创建(A)则属于重复索引，这是索引的最左侧匹配原则，在创建了(A,B)后，是可以使用索引(A)的；并不是说索引越多越好，因为索引是占用磁盘空间的，只是在查询的时候MySQL优先获取索引的数据，优先过滤索引之后再去检索数据的。这会增加insert、update的时间；删除无用的索引，当发现某一些索引使用频率很低，可以考虑删除的话，可以使用：INFORMATION_SCHEMA.INDEX_STATISTICS 看索引的使用频率
减少MySQL索引的数据碎片
MySQL有些时候是会产生碎片的，比如在某一时间大批量的删除数据，这一段空间是会被保留的，当插入新数据时，InnoDB会尝试重新使用这些内存。但是因为内存的大小不一。可能会占用不等的内存，这样就会产生大小不一的内存碎片。当产生内存碎片后，就会降低查询性能，因为查询时是随机磁盘访问了，这时可以采取：OPTIMIZE TABLE 或者重新导入数据表来整理数据
参考
https://www.cnblogs.com/balfish/p/9003794.html
