3.4 选择合适的索引列顺序
我们常用的B+树索引中，索引列的顺序是索引首先按照最左列进行排序、然后是第二列…因此，索引可以按照升序或降序进行扫描，以满足order by、group by、distinct等子句的查询需求。
选择索引的列顺序有一个经验法则：将索引选择性最高的列放在索引最前列。
3.5 聚簇索引
聚簇索引是一种索引数据的组织方式。我们常用的InnoDB中，主键其实就是聚簇索引。当表有聚簇索引时，表的数据行是存储在索引的叶子节点中的。
因为无法同时把数据行存储在两个不同的地方，所以一个表只能有一个聚簇索引
《高性能MySQL（第三版）》中的图5-3展示来聚簇索引中行记录的存放，索引叶子节点包含了数据行，索引的中间节点只是包含索引键。
InnoDB使用主键作为聚簇索引，如果没有定义主键，InnoDB则会选择一个唯一的非空索引来代替；若还是没有唯一的非空索引，InnoDB会隐式地将行ID作为主键。所以无论怎样，InnoDB都会有一个聚簇索引。
聚簇索引将数据进行聚合，有以下的优点：
- 把相关数据保存在一起。例如实现Email功能，可以根据用户ID来聚集数据，这样只需要从磁盘读取少量的数据页就可以获取某个用户的全部Email
- 数据访问更快。聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据通常比非聚簇索引需要回表查询数据要更快
- 使用覆盖索引扫描的查询可以直接使用聚簇索引中的主键值
同时，也存在一些缺点：
- 聚簇索引最大限度地提高了IO密集型应用的性能，但如果数据全部存放在内存中，那么访问的顺序就不再那么重要，聚簇索引的优势不明显
- 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么加载完成后最好使用optimize table命令重新组织表
- 更新聚簇索引代价高。因为会强制InnoDB将每个被更新的数据行移动到新的位置
- 基于聚簇索引的表在插入新行，或主键被更新导致要移动数据行的时候，可能会导致“页分裂”的问题。当数据行的主键值要求必须将这一行插入到某个已经满了的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这种页分裂会导致表占用更多的磁盘空间
- 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
- 二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点中包含了引用行的主键列
- 二级索引访问需要两次的索引查找，因为二级索引叶子节点中存储的是数据行的主键值；当使用二级索引查找时，首先先在二级索引中查找到数据行的主键值，然后再根据这个主键值去聚簇索引中找到对应的数据行，这就经过了两次B+树的查找。InnoDB的自适应哈希索引可以减少这样的重复工作
3.5.1 MyISAM与InnoDB的聚簇索引/非聚簇索引
现在通过《高性能MySQL（第三版）》中的一个例子，来理解下聚簇索引和非聚簇索引的区别。对于这个表：
MySQL的InnoDB存储引擎和MyISAM存储引擎存储的方式是不同的。假设此表：
主键col1取值为1～10000，并按照随机顺序插入，并使用了optimize table命令重新组织了表
非聚簇索引col2取值为1～100之间的随机值，有重复
满足上述这两个条件，使得该表的数据在磁盘上的存储方式已经是最优的，但数据行的顺序是随机的。
MyISAM的数据分布：
MyISAM的主键col1分布：
MyISAM的二级索引col2分布：
由图对比可见，MyISAM中主键索引和二级索引在存储结构上没有区别，仅有的区别就是主键索引是一个非空的唯一索引。
InnoDB的数据分布：
InnoDB聚簇索引的叶子节点包含了主键值、事务ID、用于事务和MVCC的回滚指针以及剩余的列（本例中是col2）。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。
可以看到，InnoDB的数据分布中聚簇索引与数据行是聚集在一起的，不会像MyISAM那样将主键索引与数据分开进行存储。
再来看看InnoDB的二级索引col2分布：
InnoDB二级索引的每一个叶子节点中都包含了索引列col2，然后是主键索引值col1。所以可以归纳出MyISAM与InnoDB的又一个不同点：InnoDB二级索引的叶子节点存储的不是行指针，而是主键值，并以主键值作为数据行指针，这种方式减少了当出现行移动或者数据页分裂时二级索引的维护工作。虽然使用主键值作为行指针会让二级索引占用更多的空间，但好处是InnoDB在移动行时无需更新二级索引的这个指针。
最后用一张抽象图总结InnoDB和MyISAM是如何存放数据和索引的：
3.5.2 在InnoDB表中按主键顺序插入行
一般而言，建立InnoDB存储引擎表的时候，最好设置一个自增的id列，作为表的聚簇索引，这样可以保证数据行是按顺序插入的，对于根据主键做关联操作的性能页更好。
避免使用随机的聚簇索引，特别是对于IO密集型应用。使用UUID来作为聚簇索引，会使得聚簇索引的插入变得非常随机，性能非常低；此外UUID占用更多的空间，也容易导致页分裂和碎片。
向聚簇索引插入顺序的索引值：
因为主键的值是顺序的，故InnoDB把每一条记录都存储在上一条记录的后面，当达到页大小的15/16时，下一条记录会写入到新的页中。
向聚集索引插入随机的UUID索引值：
由于新行的主键值不一定比之前插入的大，故InnoDB需要为新的行寻找合适的位置来分配插入的空间，缺点如下：
- 写入的目标页可能已经刷到磁盘并从缓存中移除，或者是还没被加载到缓存中，InnoDB在插入之前不得不先找到目标页并从磁盘读取到内存中，这会导致大量的随机IO
- 写入的乱序导致InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间。而页分裂又会导致要移动大量数据，所以一次插入修改的页不止一个页
- 由于频繁的页分裂，页会变得稀疏并被不规则地填充，故最终数据会有碎片
因此，主键最好设置成顺序自增的。那顺序的主键会造成什么坏结果？
- 高并发情况下，在InnoDB中按主键顺序并发插入时，可能会导致间隙锁竞争严重
- AUTO-INC锁是当向使用含有AUTO_INCREMENT列的表中插入数据时需要获取的一种特殊的表级锁。在最简单的情况下，如果一个事务正在向表中插入值，则任何其他事务必须等待对该表执行自己的插入操作，以便第一个事务插入的行的值是连续的。 innodb_autoinc_lock_mode配置选项控制用于自动增量锁定的算法。 它允许您选择如何在可预测的自动递增值序列和插入操作的最大并发性之间进行权衡。可以通过改innodb_autoinc_lock_mode配置来解决。
3.6 覆盖索引
如果一个索引包含（覆盖）了所有需要查询的字段的值，就称这种索引为“覆盖索引”。覆盖索引的叶子节点中已经包含了要查询的数据，因此可以避免回表查询。使用覆盖索引可以极高地提升查询性能：
索引条目通常远小于数据行大小，故只需要读取索引，MySQL就能极大地减少数据访问量
索引在单个页内按照列值顺序进行存储，故对于IO密集型地范围查询会比随机从磁盘读取数据的IO要少很多
由于InnoDB的主键是聚簇索引，覆盖索引对于InnoDB表更能提升查询效率。InnoDB的二级索引在叶子节点中保存了行的主键值，故如果二级索引能够覆盖查询，则可以避免对主键索引的二次查询
一些存储引擎在内存中只缓存索引，数据则依赖操作系统来缓存，故访问数据需要一次系统调用。如果需要回表查询的话，会占用一定的系统开销
当发起了一个被索引覆盖的查询时，在explain的extra列可以看到“Using index”的信息
使用覆盖索引查询可以避免回表查询，但是如果要查询的字段并没有包含在覆盖索引中，还是会去回表查询的。比如 t 表中有 3 个列 a、b、c，覆盖索引是key（a，b），如果执行select a,b from t where a=? and b=?，完全用到了覆盖索引，无需回表查询。但执行了select * from t where a=? and b=?，虽然走了覆盖索引key（a，b），但它并没有覆盖c列，因此需要进行回表查询。
3.7 使用索引扫描进行排序
MySQL有两种方式可以生成有序的结果
- 通过排序操作
- 按索引顺序扫描
当explain出来的type列的值为“index”，则说明MySQL使用索引扫描来做了排序。MySQL可以使用同一个索引既满足排序，又用于查找数据行。
使用索引扫描进行排序条件：
- 只有当索引的列顺序与order by子句的顺序完全一致，并且所有列的排序方向都一致时，MySQL才能使用索引来对结果做排序
- 查询关联多张表时，则只有当order by子句引用的字段全部为第一个表时，才能使用索引做排序
- 满足索引的最左前缀要求，即索引不会失效，有可能使用索引做排序
- order by子句可以不满足索引的最左前缀要求，但where子句或join子句对前面的量指定为了常量，就也可能使用索引做排序
具体的例子就不举了，大家在工作中可以使用explain语句分析分析就知道有没有用到索引了，然后好好总结。
3.8 压缩（前缀压缩）索引
MyISAM使用前缀索引来压缩索引的大小，从而上更多的索引可以加载进内存中，有利于提高性能。MyISAM默认只压缩字符串，但也可以通过参数设置也可以对整数做压缩。压缩方法是：先完全保存索引块中对第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，然后存储起来即可。例如，索引块的第一个值是“perform”，第二个值是“performance”，那么第二个值的前缀压缩后存储的是类似“7，ance”的形式。MyISAM对行指针也采用类似的前缀压缩方式。
压缩块使用更少的空间，但某些操作也会变慢。由于每个索引值的压缩前缀都依赖前面的值，故MyISAM查找时无法在索引块中使用二分查找，只能从头开始遍历。正序扫描速度还行，但倒序扫描就很慢了。
测试表面，对于CPU密集型应用，因为扫描需要随机查找，压缩索引使得MyISAM在索引查找上要慢很多。而对于IO密集型应用，压缩索引需要的磁盘空间很小，故对查询又会带来更多的好处。
3.9 冗余和重复索引
MySQL允许在相同列上创建多个索引，MySQL需要单独维护重复的索引，并且优化器在优化查询时也需要逐个进行考虑，这会一定程度影响性能。
重复索引是值相同的列上按照相同的顺序创建的相同类型的索引，应该避免这样创建，发现后要立即移除。例如在主键id上又建了一个unique index唯一索引，但实际上主键id就已经是唯一的索引了，这就是重复索引了。
冗余索引与重复索引有所不同。若创建了索引（a，b），那再创建索引（a）这就是冗余索引了。但再创建了索引（b）或者索引（b，a），它们都不是冗余索引，所以区别是不是冗余索引就是看新建的索引列是不是最左前缀列。还有一种情况，对于二级索引，因为它的叶子节点已经包含了主键id，如果建这种（a，id）索引，这也是一种冗余。
冗余索引和重复索引都应该避免，发现的话就删除它们，以免影响性能。
3.10 未使用的索引
有些服务器永远使用不到的索引其实是一种累赘，最好将其删除免得占用磁盘空间。Google或百度可以查找到使用什么工具去定位使用不到的索引，定位到这样的索引后，就把它们删除就可以了。
3.11 索引和锁
InnoDB有行锁和表锁。InnoDB只有在访问行的时候才会对其加锁，而索引能减少InnoDB访问的行数，从而减少锁的数量，但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引没有办法过滤掉无效行，那在InnoDB检索到数据并返回给服务器层后，MySQL服务器才应用where子句，此时InnoDB已经锁住了这些行，到适当的时候才释放。（MySQL5.1及以上，服务器端过滤掉行之后就释放锁）
结合下面例子，有这条SQL：
查询结果：
该查询虽然返回了2～4的数据，但它实际上获取了1～4之间的行的排他锁。InnoDB会锁第一行，因为MySQL为该查询选择的执行计划是索引范围扫描：
底层存储引擎的操作实际上是：从索引的开头开始获取满足条件actor_id<5的记录，服务器并没有告诉InnoDB可以过滤第一行的where条件。extra列中出现了“using where”，这表明mysql服务器将存储引擎返回行以后再应用where过滤条件。
另外，再提一嘴，InnoDB在二级索引上使用共享锁，在主键索引上使用排他锁，这消除了使用覆盖索引的可能性，并且使select for update比lock in share mode或非锁定查询要慢很多。
4 最后
由于MySQL索引展开来细讲的话可能要写成一本书了，限于篇幅和本人精力，故本文只是大致地梳理了索引的一些重点，可以加深对MySQL索引的理解，也为日后工作实践等提供资料参考。我是Zhongger，一个在互联网公司摸鱼写代码的打工人，我们下期见～
