【JAVA核心知识】31：创建高性能的索引 ---《高性能MySQL》读书笔记

索引是存储引擎用于快速找到记录的一种数据结构。通过索引找到对应的数据行。好的索引对良好的性能非常关键，索引优化应该是查询性能优化最有效的手段了。
不好的索引会严重影响性能，即使是正提升的索引，‘’最优‘’和‘’好的‘’的索引带来的提升也可能不是一个数量级别。数据量越大，索引越重要。
避免使用ORM工具产生索引，索引是一个十分复杂的东西，再复杂的ORM工作，在精妙和复杂的索引面前都是浮云。事实上我个人认为ORM工具除了开发上较为便捷，在性能处理(无论是表设计还是索引设计)上的表现极差。而绝大多数拥有大量级数据处理的企业应该都愿意用更多的开发时间换更高的性能。

1 索引的类型

索引的实现与存储引擎有关，并不是所有的存储引擎都支持所有类型的索引。即使是支持同一类型索引，不同的存储引擎在实现上也可能不同。

1.1 B-Tree（B+Tree）索引

需要主要了解的索引。如果谈论索引时没有特别指明类型，那么多半就是说的B-Tree索引。本文也是，除了此节索引类型会涉及部分其他类型索引，其他各节除特别指出均针对B-Tree索引。
MyISAM引擎，InnoDB引擎都支持B-Tree索引。MyISAM使用压缩技术使索引更小，通过数据的物理地址引用被索引的行；InnoDB按照原格式存储，根据主键引用被索引的行。
B+Tree是B-Tree的进化版（B+Tree是B-Tree）,B+树的非叶结点仅具有索引作用，使得一个数据页可以承载更多的索引值，B+的叶子节点作为有序链表，在遍历时也更高效。但是从数据结构上来讲并不说B+树就优于B树，B树的优点在于，由于B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。 Innodb使用B+树也仅仅是因为B+树更加契合 Innodb的设计理念。

因为我们主要要了解的存储引擎InnoDB使用的是B+Tree的数据结构实现的这种类型的索引。所以说起索引时，如果没指定存储引擎，那么实现方式是就是指InnoDB的B+Tree，所以我们下面主要看B+Tree索引：
B+Tree意味着所有的值都是按顺序存储，并且每一个叶子页到根的距离相同。下图是InnoDB的B+Tree的抽象展示，val可能是数据行（InnoDB的聚簇索引），也可能是聚簇索引键值（InnoDB的非聚簇索引），也可能是指向数据行的指针（MyISAM的非聚簇索引）。索引树会有多层，一般的B-Tree在叶子页节点之间没有指向下一个叶子页的指针这特性。

B+Tree索引使得存储引擎无需全表扫描，仅需从索引的根节点向下层搜索到叶子节点就能获取到目标数据或者判断出数据不存在。而由于B+Tree索引的有序性，所以在范围数据的查询和order by排序查找上有很高的效率。例如查出所有姓名列以I到K开头的数据或按照某列排序。
要特别注意的是,如果是多列索引，那么索引的定义顺序对于B+Tree来说十分重要。这是由于实现方式导致的，一个(A,B)定义的索引和一个(B,A)定义的索引是完全不同的两个索引，所以也许为了应对不同的场景，可能会对相同的多列建立多个索引。

多列索引的排序是依据索引创建时列的顺序排列，例如定义索引有三列(name,birthday,sex)，那么索引会先按name排序，name一样的数据再按birthday排序，name,birthday都一样的再按sex排序。

1.1.1 B-Tree索引规则

基于这些特性，B+Tree适用于全键值，键值范围或最左列键前缀查找：

• 全值匹配：和索引中的所有列进行匹配，如查询name是张三，birthday是2020-11-09，sex是男的列
• 最左列匹配：对索引的最左列匹配。如查询name是张三的列。
• 最左列列前缀：匹配最左列某一列值的开头部分，如查询name中姓张的列。
• 匹配范围值：根据最左列的范围，如查询name在张三3与张三6之间的列
• 精准匹配某一列并范围匹配另一列：如查询所有叫张三的，生日前缀为2020-11或生日在2020-11与2021-11之间的列。
• 只访问索引的查询：即只访问索引列而不访问数据行的查询 如select name,birthday,sex from person where name = ‘张三’，因为索引中包含要查询的列，所以只会访问索引，而不会去查询数据行.

1.1.2 B-Tree索引限制

由于B+Tree索引有序的特性，B+Tree索引也会有相应的限制，通过这些限制也能知道为什么B+Tree索引的顺序如此重要：

• 不是从索引的最左列开始查询，则无法使用索引，如值查询sex是男，birthday是2020-11-09的列，而name不作为查询条件，则不适用索引
• 无法匹配后缀：如可以查询姓张的（like ‘张%’），但是却无法查询名字是三的列（like ‘%三’）。
• 不能跳过左列索引:如查询name是张三，sex是男的列。此时索引只有name列生效，sex列无效
• 范围查询（包括like和<>）的右边条件都不适用索引：如select * from person where name = ‘张三’ and birthday like ‘2020-11%’ and sex = ‘男’，则只有name与birthday有效，sex无效。

1.1.3 B-Tree索引生效

一定要注意上述索引规则和限制只是在表示MySql能多大程度的使用索引。他针对的是索引顺序，不是针对你的Where条件顺序而言的。
如下：

select * from person where noIndexCol = 1 and name = ‘张三’

此时name 左包有一个条件是noIndexCol,那是不是意味着name就会无效呢？答案是否定的
再如下：

select * from person where birthday = '2022-02-02' and name = ‘张三’

此时where顺序和索引顺序不一致，是不是索引也不生效呢？答案也是否定的。name和birthday都会生效。

所以要分清索引的顺序和where的顺序。 where的顺序并没有什么强制的要求，MySQL的优化器会识别你的where，获取各种排列下所有可以使用的索引并选择"最优"的。这一点从explain的possible keys就能体现出来。

1.2 hash索引

通过索引计算出一个hash值，再通过Hash值找到指向数据行的指针，使用拉链法处理Hash冲突，从而进行精确查询， 和HashMap的原理类似。Mysql只有Memory引擎支持。
hash索引有着紧凑的索引结构以及极快的匹配速度，缺点是仅能进行全值精确匹配，无法模糊查询，范围查询，进行排序，也无法通过索引值避免读取行。
为了利用hash索引高效优势，InnoDB引擎有一个特殊的功能：自适应哈希索引(adaptive hash index)。Innodb发现某些索引值使用的非常频繁时，就会再B+Tree索引之上再创建一个hash索引，这样B+Tree就有了hash索引的优点。这个是InnoDB的内部行为，用户只能控制开启或关闭。
自定义hash索引：为了利用hash的优势，对于一些值很长列。比如url列，由于url较长，对url创建索引会使得索引特别大，且查询效率较低，此时可以对url进行hash计算。如用CRC32算出一个整数值，然后对这个整数值创建索引。不使用SHA1和MD5是因为这两种强加密函数为了尽可能的消除hash冲突会产生一段比较长的字符串（长会计算慢，字符串也比整数慢），这样的话自定义hash索引就失去意义了。同时在where语句中要加上原值：where url_hash=CRC32(‘…’) and url = '…'以应对hash冲突。对于hash冲突一定要考虑到：hash冲突轻微时无所谓，但hash冲突较严重时，索引维护的代价会很高。如删除一个处于hash冲突严重的行，需要遍历该hash值下面的所有行以确定要删除的行（注意生日悖论，Hash冲突的增长速率可能比你想象中的更加严重）。出现严重的hash冲突时可以换一个hash方法(自己写或者使用现有的比如FNV64)结合扩长hash值长度减轻。

1.3 其他索引

除了上面两个常解接触到索引外，MySQL还有地理位置的空间索引，根据文本关键字查找，类似搜索引擎的全文索引，以及第三方引擎的使用。不详细深入。

2 索引的优点

索引可以让数据库能够快速的定位到指定数据行。但是这并不是索引的唯一作用。如常见的B-Tree索引，按照顺序存储数据，这就使得MySQL可以用来做ORDER BY和GROUP BY操作，又因为B-Tree索引存储了实际的列值，所以有时仅仅使用索引就能完成全部查询而不用去查询实际的数据行，总结下来就是：

• 索引大大减少了服务器需要扫描的数据量
• 索引可以帮助服务器避免排序和临时表
• 索引可以将随机I/O变为顺序I/O

《Relational Database Index Design and the Optimizers》定义了一种评价索引是否适合某个查询的方式：三星系统（three-star system）

• 索引能将相关的记录放到一起则获得一星
• 索引中的数据顺序和查找中的排列顺序一致则获得二星
• 索引中的列包含查询中需要的全部列则获得三星

索引具有诸多好处，但是并不意味着索引总是最好的方案。维护索引是需要额外的工作，索引带来的好处大于维护索引所需要的额外工作时，索引才是有效的。对于非常小的表，大多数情况下无需创建索引，简单的全表扫描会更高效。对于中大型的表，索引就非常有效。对于超大型的表，仅仅使用索引就不能满足要求了，因为此时建立维护和使用索引要付出更多的代价，此时就要结合分区(分库分表)的设计了，将大的数据块分区，然后建立一个元数据表，用来记录表定位。这对于大型系统是一个常用的技巧。这个记录也不一定要对应单条记录，如果数量量级极大，定位单条记录的意义已经不大了，此时就需要使用块级别元数据技术代替索引。另外还有建立算法取模定位坐标的方式，此种方式适用于业务较为稳定或非超大型数据量上，在业务扩展期间的超大型数据的使用上要慎重，因为取模算法扩展性较差，一旦扩展需要迁移大量的数据，这对于超大型数据来说是非常麻烦的。

3 聚簇索引和非聚簇索引

3.1 聚簇索引是什么

聚簇索引并不是一种索引类型，而是一种数据存储的方式。InnoDB的聚簇索引就是在同一个结构中保存了B+Tree索引，数据行和相关行信息，数据行就放在聚簇索引的叶子页（leaf page）中。聚簇索引的每一个叶子节点都包含了聚簇索引列，事务ID,用于事务和MVCC的回滚指针以及所有的剩余列信息，如果聚簇索引列是前缀索引，那么还包含完整的聚簇索引列。 可以说在InnoDB中，聚簇索引“就是”表。此时再看B+Tree索引结构图（就是上面索引类型一章的图）：

如果索引是一个聚簇索引，那么val里面存的就是数据行以及相关行信息。 但是要注意并不是所有的引擎都支持聚簇索引，比如MyISAM的索引就属于非聚簇索引。
“聚簇”就表示数据行和相邻的键值紧凑的存储在一起。因为无法把数据行同时存放在两个不同的地方，所以一个表只能有一个聚簇索引。
InnoDB默认主键索引就是聚簇索引，如果没有定义主键，那么InnoDB会选择一个唯一的非空索引代替，如果同样没有定义这样的索引，那么InnoDB会隐式的定生成一个不可见的列row_id作为聚簇索引。
聚簇索引的特性对性能很有帮助，但是也可能导致严重的性能问题，所以表设计时要仔细考虑聚簇索引。
但是在了解聚簇索引的优缺点之前，最好先了解一下数据页的相关知识，这有助于理解为什么聚簇索引会有这样的优缺点。

3.2 数据页与页分裂,页合并

数据页
InnoDB存储数据的物理文件是.ibd文件，每个表都有自己的.ibd文件。idb文件由多个段（segments）组成，段的下一级单位是区，区的下一级单位是页，数据行就在数据页里面。数据页默认16KB。InnoDB操作的最小粒度是数据页，而不是数据行，InnoDB获取行记录是将行所在的页数据全部读取到内存中，然后再获取行记录。因此如果多行数据聚集在一个页内，那么仅需一次磁盘IO就能完成获取，如果分布在多个页内就需要多次磁盘IO。而磁盘IO是比较慢的。B+Tree索引的叶子节点就是一个个数据页，而不是数据行。
数据页的存储：
假设使用AUTO_INCREMENT作为聚簇索引，顺序插入聚簇索引列值为1,2,3的三行数据,数据页就是这样：

随着数据的顺序插入，数据页会慢慢变满，当达到页的最大填充因子时（InnoDB默认为最大页大小的15/16，流出空间用于以后修改），下一条记录就会写入新的页中。每个页中都会存着指向前一页和后一页的指针。
页分裂：
假设聚簇索引列不是顺序插入的，而是随机的，这里假设顺序是：6,2,4,7,1,8…5…3:
插到8的过程是下图这样的，假设此时已达到最大填充因子，8到5之间的数据会新开页并插入到后续的页中：

然后插入5，因为装载因子的预留空间，所以5还能插入，该页变成这样：

彻底填满了数据页，此时插入3，但是该页已经没有足够的空间了，为了保证聚簇索引的顺序性，就需要进行页分裂，也就是生成一个新页来承载该页的数据，新页也不再是仅仅迁移一个8了，会迁移更多数据保证均衡，整个过程会耗费更多的资源：

页合并：
了解了页分裂，页合并也就更好理解的，当删除一行数据时，实际上记录并没有被物理删除，而是仅仅打上一个标记声明可以被其他记录使用，当页中剩余的记录不足MERGE_THRESHOLD（默认为页体积的50%）时，InnoDB会探查这个页前面或者后面的页是否有足够的空间，如果有，就会把这个页的数据迁移过去，完成页合并，这个动作同样相当耗费资源。
这里额外说一下：因为删除数据时，记录并没有被物理记录，这就会导致数据库占有空间比实际空间更大的情况，让数据库处于高水位，此时可以通过alter table tableName engine = innodb;或者optimize table tableName（只对MyISAM, BDB和InnoDB表起作用） 来进行表重组，但是重组操作会锁表，这是需要重组时需要考虑到的。
了解数据页相关的知识，再看聚簇索引的一些优缺点就会更清晰了。

3.3 聚簇索引的优缺点

聚簇索引有以下重要优点：

• 可以把相关数据保存在一起。例如电子邮箱，可以通过用户ID来聚集数据，这样获取某个用户的全部邮件时仅需从磁盘读取少数的数据页，如果没有使用聚簇索引，那么可能每封邮件都可能导致一次磁盘IO。（PS：聚集索引必须具有唯一性，那怎么通过用户ID聚集数据呢？答案就是可以将聚集索引列的值定义为用户ID+随机值的方式，这样就能达成目的）
• 数据访问更快，聚簇索引的叶子节点直接就存储着数据行，因此从聚簇索引获取数据比非聚簇索引要快。
• 使用覆盖索引扫描的查询可以直接使用页节点的主键值 。

如果设计表和查询时能充分利用这些优点，那就能极大的提升性能。同时聚簇索引也有一些缺点需要注意：
聚簇索引的缺点：

• 聚簇数据通过减少磁盘I/O提高I/O密集型应用的性能，如果数据在内存中，访问顺序就没那么重要了，聚簇索引也就没有优势了
• 插入速度严重依赖于插入顺序，按照主键顺序插入到InnoDB表中是速度最快的方式。如果不是按照主键顺序插入数据，那么插入完成后最好使用OPTIMIZE TABLE命令重新组织一下表
• 聚簇索引的更新代价很高，因为要强制将每个更新的行移动到新的位置
• 插入新行或者聚簇索引列更新导致行需要移动时，可能面临“页分裂(page split)”的问题。当行的聚簇索引列要求这一行必须插入某个已满的页中，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致占用更多的磁盘空间。过多的页分裂还会导致数据稀疏，存储在更多的页上，使得全表扫描变慢。
• 二级索引(非聚簇索引)可能比想象中的要大，因为二级索引的叶子节点包含了引用行的主键列。
• 二级索引需要两次索引查找，才能找到目标数据。这是因为二级索引(非聚簇索引)叶子节点存储的是行的聚簇索引列值，而不是行的物理地址，这就要求通过二级索引查询时，存储引擎先找到二级索引的叶子节点获得对应的聚簇索引列值，然后再用这个值去聚簇索引中查找对应的行。这就是两次查找。自适应hash索引的出现就是为了减少这样的重复工作。

3.4 非聚簇索引(二级索引)

对于InnoDB来说，一个表只允许有一个聚簇索引，那么除了聚簇索引外的其他索引都属于非聚簇索引。非聚簇索引的叶子节点存储的不是数据，也不是行指针，而是聚簇索引列值，因此如果通过非聚簇索引查询时，需要进行二次索引查找。这种策略减少了出现行移动或者数据页分裂时二级索引的维护工作。
MyISAM引擎不支持聚簇索引，其所有的索引都属于非聚簇索引，且MyISAM引擎的索引叶子页存放的则是指向数据行的指针。

下图很好的展示了聚簇索引和非聚簇索引在InnoDB和MyISAM中的对比。

3.5 聚簇索引(主键列)选择

诚然，如果我们没有定义主键或者唯一索引列，MySQL会隐式的定生成一个不可见的列row_id作为聚簇索引。但是很糟糕的是这个列即不能被任何查询访问，也不能被内部(如基于行的复制)使用。这会使得这表的查询以及复制都变的很差。因此主键是一个高性能MySQL表必须的。关键就在于如何设计一个好的主键。
基于上面页分裂的知识，使用AUTO_INCREMENT自增列是最简单的方法。整数比字符串更好，因为拥有更快的对比效率，检索时也就更快。一定要尽量避免使用“随机”的字符串作为主键，比如MD5(),SHA1()。这些函数生产的值会任意分布在很大的空间，这会导致INSERT和一些SELECT语句都很慢。主要原因是：

• 乱序写入意味着频繁导致页分裂，而页分裂需要移动大量数据。频繁的页分裂还会导致空间碎片化。
• 上文也写了，删除数据时，记录并没有被物理记录，而是仅仅打上一个标记声明可以被其他记录使用，当页中剩余的记录不足MERGE_THRESHOLD（默认为页体积的50%）时，而随机的数据结构会使得删除分布在不同的页上，让数据页更难达到页合并阈值，空间碎片化严重，导致数据库实际占有空间比预想空间大的多情况，让数据库处于高数位。
• 导致磁盘随机访问，InnoDB操作的最小粒度是数据页，而不是数据行。因此Insert时，如果目标页已经刷入磁盘，且内容已经从缓存中移除，那么这个Insert操作就需要先读出数据页到内存，修改后在写入磁盘。在select时还会因为数据分布广泛，导致需要多次I/O才能完成呼叫获取。随机值也使得缓存赖以工作的访问局部原理失效。

使用AUTO_INCREMENT自增列并不是没有缺点，对于高并发的引用，主键的上界会成为热点导致竞争。此时可以考虑分表或者更改innodb_autoinc_lock_mode配置。
另外关于UUID()，UUID()虽然分别也不均匀，但是还是有一定顺序的，尽管如此，还是不如整数好用，如果要使用UUID,应该消除‘-’符号，更好的做法是使用UNHEX()函数把UUID值转换成一个16字节的字符串(因为UUID其实就是一个十六进制值的字符串形式)，然后存储在BINARY(16)列中，然后查询出来时再用HEX()函数转变回去。

如果使用了随机值主键，那么也许需要OPTIMIZE TABLE来重建表并优化数据页的填充。

3.6 二级索引(回表)存储引擎和Server层的交互

通过聚簇索引查询时，直接直接通过聚簇索引获得符合的数据行集(可单条和范围)。
但是如果通过二级索引，那么需要先用二级索引去获得聚簇索引集，然后再用聚簇索引去二次查询获得数据行。在MySQL架构基础中提到过，存储引擎只是提供诸如‘开始一个事务’或者‘根据主键提取一行记录’等操作。对于存储引擎来说，没有所谓的IN操作，因此Server层拿到聚簇索引集后，需要再利用聚簇索引一条条的去存储引擎拿到对应的数据行。如果有额外的where过滤条件，那么也是Server层根据聚簇索引拿到数据行后在Server层判定，保留符合条件的，丢弃不符合条件的，不是说直接在存储引擎就比对数据行完成过滤了，这需要注意。
虽然二次查询是根据索引的单条获取，但是把数据放到一起依然是重要的了，数据越聚集，所跨的数据页越少，那么为了读取数据页可能进行磁盘IO的次数就越少(因为数据页会被缓存)。

3.7 索引下推

索引下推(ICP)是MySQL5.6发布后针对二级索引的一项优化改进，目的是把索引过滤条件下推到存储引擎，来减少回表此时。ICP适用于MYISAM和InnoDB。
通过上面章节的描述，可以了解到对于联合的二级索引，如果出现例如like，大于小于这种范围查询，会导致后续索引失效。对于后续条件，MySQL此时的方案是Server层拿到聚簇索引集，然后再用聚簇索引一条条的回表拿到对应的数据行进行比对，保留符合条件的，丢弃不符合条件的，即使后续条件也属于索引列。
例如：

select * from testTable where name like 'zq%' and age = 1;

如果建立了name和age的联合索引，因为name的范围查询，按照索引的规则限制，此时age列就无法使用索引，只能回表了。但是这不是多此一举吗，二级索引完全可以进行age的判定的，可以有效避免脏数据。索引下推就是对此的优化，意思为将过滤条件下推到存储引擎层。二级索引检索时，会将age的限制也传入，存储引擎也承担着过滤，只返回age=1的索引，减少了返回的聚簇索引数目，从而减少回表次数。如果一个SQL使用了索引下推，那么在EXPLAIN时，Extra的列就会显示Using index condition。索引下推默认是开启状态，可以通过set optimizer_switch='index_condition_pushdown=off';关闭。
ICP的优化只针对二级索引的范围查询，毕竟聚簇索引直接获取数据行。

3.8 索引合并

多列索引是在多列建立一个索引，而不是为每一列建立单独的索引，在多个列建立独立索引并进行多列组合查询大多数情况下并不能提高性能。MySQL5.0之前最好的情况下也只能使用最左的一个索引。MySQL5.0引入“索引合并(index merge)”策略，理念是通过取交集，并集，先交再并的方式将同一个表多个索引的范围查询合并成一个索引。索引合并可以通过参数optimizer_switch关闭。
索引合并机制有4个开关选项：

• index_merge 索引合并机制的总开关
• index_merge_intersection 索引合并-取交集 （索引AND）
• index_merge_union 索引合并-取并集（索引OR）
• index_merge_sort_union 索引合并-排序并集 (索引范围查询)
  默认情况下，这些优化开关的值都是on，即打开状态。

索引合并一定程度上可以使用多个单列索引定位指定的行，然而这种策略弊端很大,所带来的优化有时并不符合预期，因此尽量不要用这个机制来作为设计方案，如果EXPLAIN中的type列为index_merge，Extra列为:Using intersect/union/sort_union(index1,index2,index3...),说明这个查询使用了索引合并策略，此时要好好审查这个查询的索引设计合理性。
在官方声明中也强调应该避免在update语句中出现索引合并，因为这可能导致死锁。因为索引合并的场景下，加锁顺序为：二级索引A->A对应的聚簇索引->二级索引B->B对应的聚簇索引。
那么就有以下场景：

解决方案很简单，建立name和age的联合索引就可以解决。

4 高性能索引策略

这一节如何有效且高效的选择和使用索引，包括一些索引设计和选择的小技巧。

4.1 独立的列

独立的列是指索引列不能是表达式的一部分，也不能是函数的参数，如以下SQL:

select * from testTable where testCol + 1 = 5;

通过肉眼很容易看出来要查询testCol =4的列，但是MySQL确无法执行这样的表达式，即使testCol列上有索引也无法使用，要想使得索引有效，需要改成这样(只是举个例子，表达要把索引列独立，事实上可以直接写=4)：

select * from testTable where testCol = 5-1;

无论是简单的基础运算还是复杂的函数，都需要遵循这样的原则：始终将索引列单纯放在比较符号的一侧。 注意一点MySql本身并不存在对这方面的优化，即使像例子上这样的testCol + 1 = 5这样的极简运算，mysql依然不会走索引。

4.2 前缀索引和索引的选择性

如果索引列存储的是很长的字符串，就会导致索引变的大且慢。一个策略是上面提到过的模拟hash索引。但是有时这样还不够，此时可以截取字符串起始的部分字符作为索引，这样可以大大节约索引空间，从而提高索引效率，但是这样会降低索引的选择性。选择性是指：不重复的索引值(也称为基数 cardinality) 和数据表的记录总数(T)的比值,范围从1/T到1之间，索引的选择性越高则查询效率越高，因为选择性高的索引可以在查询时过滤到更多的行。唯一索引的选择性是1，这是最好的，性能最高的索引。
一般情况可以根据场景决定是否使用前缀索引，但是对于BLOB,TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。
前缀索引的使用要点在于选择多长的前缀既能保证较高的选择性，又不能太长。首先要找到索引目标列，计算一下不做截断以及选择不同长度时的选择性，根据选择性定义，一个SQL就能搞定：

SELECT 
COUNT(DISTINCT LEFT(testColumn,2))/COUNT(*) AS sel2,
COUNT(DISTINCT LEFT(testColumn,3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(testColumn,4))/COUNT(*) AS sel4,
... ...
COUNT(DISTINCT testColumn)/COUNT(*) AS sel 
FROM testTable;

此时就会发现，当前缀长度达到一个数字X时，继续增加长度，选择性的提升幅度就很小了。但是N并许并不能作为最佳长度，因为上面计算的是平均选择度，如果数据分布很不均匀，那么那些权重较高的数据的选择性就会非常糟糕，因此接下来要进行验证:

SELECT COUNT(*) AS cnt,LEFT(testColumn,X) AS pref FROM testTable GROUP BY pref ORDER BY cnt DESC LIMIT 5;

通过上面这个SQL，就可以得到截断长度为X时权重较高的前5(可以根据数据量自由调整，这里示例5个，也可以看权重较低的，甚至全部)前缀以及他们的出现频次，通过这个就能看到选择性分布是否均匀，如果分布悬殊，就计算X+1,X+2…总能得到一个分布相对均匀的场景，此时得到的数字N就是我们的目标前缀长度。找到了这个长度就可以建立一个前缀索引：

ALTER TABLE testTable ADD INDEX  myPrefTest(testColumn(N));

此时就可以使用testColumn列的前缀索引了，前缀索引的使用和正常索引的使用方式一样，无需用户自己进行截断，MySQL会帮你做：

SELECT * FROM testTable WHERE testColumn = 'qwer1234';

前缀索引是一种能使索引更小更快的又快方法，但是其也有缺点：MySQL无法使用前缀索引进行ORDER BY 或者GROUP BY，也无法使用前缀索引做覆盖扫描（查询索引列时只查询索引就能得到数据）。
如何做后缀索引 : 一些场景，如邮箱地址匹配可能要进行后缀匹配，而上面也提到了MySQL无法做到后缀匹配，因此一个巧妙的方式是将字符串反转，变后缀为前缀，此时就能利用MySQL的索引特性提高匹配效率。

4.3 多列索引与索引的顺序

一个很重要的一点：多列索引是指按照合适的顺序为多个列建立一个索引。但是一些专家或博客诸如“把WHERE条件里面的列都建上索引”的这种模糊说法使得很多人错误的理解为：为每个列创建独立的索引或者按照任意的顺序创建索引。
在多个列建立独立索引并进行多列组合查询大多数情况下并不能提高性能。虽然有上文提到的索引合并优化，但是这种策略弊端很大,所带来的优化有时并不符合预期，还具有死锁的风险，因此尽量不要用这个机制来作为设计方案。
一定要谨记多列索引是在多列建立一个索引，而不是为每一列建立单独的索引！另外
对于多列索引，索引的列顺序至关重要。 这是因为对于B-Tree索引，索引首先按照最左列排序，其次是第二列，索引排序本身是有顺序的，所以索引可以按照升序或降序扫描，以满足ORDER BY, GROUP BY和DISTINCT等要求。
有这样一个经验法则：在不涉及排序和分组的情况下，将选择性最高且数据分布相对均匀的列放到索引的最前列。为什么这么说呢：

• 首先目标查询不涉及排序和分组，如果涉及排序和分组，那么首先要保证排序或分组字段能利用上索引。因为避免随机IO以及排序大多数情况下都比一点选择性提升更为重要。收益也更大。
• 其次选择性高，能快速排除更多的不匹配数据。
• 最后目标列数据要相对均匀，在上一节前缀索引中，已经描述了平均选择性在数据不均匀时的特殊性，前缀索引需要考虑数据分布，索引列顺序的设计同样如此：如果A列有30个值，B列有100个值，简单的看是A列更适合放在前面，因为其，但是如果A列分布极不均匀，90%的列都是30个值中的同一个，此时将A置为最前列明显不是一个好的选择了，因此数据分布也是设置索引顺序时的一个重要指标。（典型案例：未登陆用户设置userID一个固定值，且占据大多数，然后以userID为最前列，此时查询未登录用户的相关信息索引就相当糟糕了，但是userID查询登陆用户确实又很高效，此时可以分别建立[A,B],[B,A]两个索引，针对不同的场景使用不同的SQL）

4.4 覆盖索引

InnoDB的B-Tree索引是存储有索引列的值的。如果要查询的列不属于索引列，那么就需要获得数据行才能得到。但是如果索引的叶子节点已经包含要查询的数据，还有什么必要再回表查询数据行呢？直接就可以在索引中得到。如果一个索引完全包含（或者说覆盖）所有需要查询的字段的值，我们就称为覆盖索引。
合理使用覆盖索引能有效降低数据访问量，降低I/O，毕竟不用读取数据行了。还可以将简单的顺序查询变成完全的顺序访问。如果是二级索引，还能避免二次查询。
当发起一个被索引覆盖的查询时，在EXPLAIN的Extra列可以看到Using Index的信息。

4.5 使用索引扫描来做排序

MySQL有两种排序方法：通过排序操作；或者按照索引扫描。如果EXPLAIN出来的type列的值是“index”,则说明MySQL使用了索引来做排序。
扫描索引本身很快，顺序访问即可。但是如果索引不能覆盖查询所需的列(覆盖索引)，那么就不得不每扫描一个索引就进行一次回表查询获得行。这基本是随机I/O。因此使用索引排序时，尽量满足这个索引还是一个覆盖索引。这样能有效的提高性能。
只有索引的列顺序与ORDER BY子句顺序完全一致并且所有列的排序方向一致(升序或降序)都一样时，才能使用索引对结果排序。关联多表查询时，只有ORDER BY的字段都是第一个表时，才能使用索引对结果排序。ORDER BY的使用依然要遵循最左前缀的要求才能进行索引排序。
如果有索引(A,B)，order by A，B可以使用，where A = “XXX” order by B也是可以使用索引的。
或者有时，我们可以将数据检索出来，然后通过应用排序。

4.6 避免冗余索引，重复索引与未使用的索引

索引的维护需要付出代价。增加索引会使的INSERT,UPDATE,DELETE速度变慢。
MySQL允许在相同的列上创建多个索引，因此要避免重复索引的出现。重复索引有可能不是有意的，而是认知不足导致的，比如设置字段A为主键，在对A设计唯一限制，再对A建立索引。此时就会产生3个索引。
冗余索引不同于重复索引，如果有索引(A，B)，此时再建立索引（A）,那么这种就属于冗余索引，需要尽量避免。索引的修改尽量选择扩展而不是创建新索引。
有些索引索引可能建立之后用不到，或者之前用到，后期业务变更导致不在使用的索引也要及时删除。

4.7 关联查询的索引建立

在创建索引时需要考虑关联的顺序。如果表A和表B用列C关联。那么需要在次表建立索引，主表无需建立索引。如A left join B,以A表为主，此时可以理解为在A表拿一条数据，然后去B表找对应的数据，因此应该在次表B上建立索引，A表全表扫描，无需创建索引。相应的如果A right join B,那么此时A为次表，那么就应该在A表建立索引。如果是A inner join B，那么B表为主表，A表为次表。关联查询时不需要在主表创建索引，因为不会用到，额外的索引只会带来额外的负担

4.8 其他小诀窍

• 使用频率高的列放在前面，可以有效的降低索引数目。对于如性别或者开关这种值域较少的列放在前面。如果部分查询又无需这个列，此时可以通过在where中加sex in (‘男’,‘女’)来绕过最左前缀原则，重新让索引生效，而不用再建一个索引或者调整索引顺序。但是这个诀窍只适合值域较少的列。这种使用IN的方法要注意，因为每额外增加一个IN条件，组合都会以指数形式增加（3*3=9）
• 需要范围查询的列放后面，因为一旦范围查询，后续索引列就失效了。虽然可以通过IN来绕过，但是并不是所有的列都适合的。
• 延迟关联：如果需要翻页，当翻到较后面的页是要花费大量时间扫描需要丢弃的数据，此时一个好的策略是使用延迟关联，即先用二级索引查询LIMIT出符合条件的聚簇索引，再使用IN或者JOIN来完成数据获取。
• 可以通过一个不做任何操作的ALTER操作还重建表。如修改表的存储引擎为当前引擎：ALTER TABLE totalTable ENGINE = INNODB;

4.9 小心隐式转换

查询过程中如果参数类和列类型不一致，那么MySQL就会进行隐式的类型转换，这种转换很可能会让索引失效，因此在编写SQL时，一定要保证参数类型与列类型一致，避免意料之外的情况发生。
先看一下官方定义的隐式类型转换规则:

1. 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
2. 两个参数都是字符串，会按照字符串来比较，不做类型转换
3. 两个参数都是整数，按照整数来比较，不做类型转换
4. 十六进制的值和非数字做比较时，会被当做二进制串
5. 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
6. 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
7. 所有其他情况下，两个参数都会被转换为浮点数再进行比较

结合实际示例来看为什么隐式转换可能会让索引失效：
假设phone列定义为char，执行以下sql:

select * from UserInfo where phone = 123456789;

此时就会发生隐式类型转换，结合以上转换规则，符合条件7，那么实际上以上SQL的执行是这样的：

select * from UserInfo where cast(phone as double)= cast(123456789 as double);

根据索引策略 phone列并非单独的列，索引并不会生效，因此会进行全表扫描，性能自然会极差。

5 索引与锁

InnoDB的行锁效率很高，但是锁定操作依然会带来额外的开销并影响并发性。InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问访问的行数，从而减少锁的数量。但是前提是在存储引擎层就过滤掉不需要的行，而不是获取数据之后。简单的说就是要在去数据页读数据的之前就过滤掉无用的行，那些索引没有过滤掉，需要获取数据行之后再在服务器层用where条件过滤的行依然会被锁定。
例如：

select * from testTable where id < 5 and id <> 1;

id为聚簇索引，这个查询只会返回id在 2-4 的数据行，但是实际上 1-4 的数据行都会加上排他锁。因为id<5 通过索引筛选掉了大于5的数据行，但是id <> 1因为索引的使用规则，无法生效，也就无法在获取数据之前筛选，最后的结果是将1-4的数据行获取，然后再匹配筛选，这就导致 数据行1 也会被行锁锁定。
这个例子证明即使使用了索引也可能导致锁定一些不需要的数据。不使用索引会更糟糕，MySQL会扫描整个表并锁住所有的行（注意是所有的行都上行锁，而不是对表上表锁，虽然都是锁住所有数据，但是概念还是不一样的），即使那些数据并不是查询所需要的。(以上机制应用在REPEATABLE_READ级别下。而READ_COMMITTED级别下则不会如此，而是锁定实际的行-这句话对的，已验证，回表范围加锁只在REPEATABLE_READ级别)。
值得注意的是select ******* for update操作。此操作虽然是一个读操作，但是for update的锁定意味此操作无论如何都需要回表。因此如果采用select ******* for update进行查询，那么就无需费心思去尝试利用覆盖索引避免回表查询了。因为没有意义，即使满足条件，MySQL依然会回表以进行独占锁的加锁。
关于事务与锁，有以下几个结论：

1. 通过聚簇索引更新时，会在聚簇索引上加锁。
2. 通过二级索引进行更新时，会先对二级索引加锁，然后对聚簇索引加锁。
3. 使用聚簇索引更新二级索引时，会先对聚簇加锁，再对二级索引加锁。此结论的前提条件为结论4。
4. 更新二级索引时，只有二级索引所在的列产生实际变化的更新，才会对二级索引加锁，否则仅会对聚簇索引加锁。
5. 在REPEATABLE_READ级别下，对索引的加锁范围是索引所确定的范围，而不是最终结果集范围。也就是说需要回表查询才能剔除的行的聚簇索引依然会被加锁。而READ_COMMITTED级别下则不会。

具体的验证过程见:一次关于InnoDB在UPDATE时对聚簇索引及二级索引加锁情况的试验
另外关于死锁：MySQL造成更新死锁及插入死锁的几种常见原因

6 总结

了解索引的工作原理是很有必要的，可以根据这些理解创建最合适的索引。而不是根据一些诸如“让多列索引中选择性高的列放在第一列”之类的经验法则及推论。
在选择和编写利用索引时，有三个原则要始终记住：

• 单行访问很慢。因为MySQL最小的操作单元是数据页，因此如果读取一个数据页仅仅是为了获取其中的一行数据，那会造成很大的浪费。因此读取时尽量包含数据页中尽可能多的需要的行。
• 按顺序访问范围数据是很快的。一是因为顺序I/O不需要多次磁盘寻道，比随机I/O快的多 二是按顺序读取数据，就不需要额外的排序操作，GROUP BY也会因此受益。尽可能的使用原生顺序。
• 索引覆盖查询很快！如果一个索引能包含所有需要的列，那么就不需要再回表查询。这能避免大量的单行访问。

这三个原则对应的就是“三星系统”。但是并不是说每个查询都要一个这样完美的索引。因为这样就代表着需要建立大量的索引。索引的维护也是要付出代价的，此时就需要我们根据实际的场景来做成取舍。

参考资料：《高性能MySQL》

PS：
【JAVA核心知识】系列导航 [持续更新中…]
关联导航：MySQL架构基础
关联导航：MySQL数据类型选择与设计
关联导航：查询性能优化
关联导航：EXPLAIN的使用
欢迎关注…