索引基础
MySQL的索引通常我们可以理解为类似于书籍的索引，如果我们想要在书中找到某个主题的内容，那么最简单的方法就是去找这本书的“索引”，通过索引找到对应的页码。在MySQL数据库中想要获得更好的性能，索引是至关重要的，这个重要性往往随着表的数据量增大而越发明显。
索引的类型
索引有很多类型，可以为不同的场景提供更好的性能。索引是在存储引擎层实现的而不是服务层，所以并没有统一的标准，不同存储引擎的索引工作方式并不一样
B-Tree索引
B-Tree（B+ tree）是MySQL（InnoDB存储引擎）中使用最频繁的一种索引结构。
B-Tree（B+ tree）索引意味着所有的值都是顺序存储的，并且每一个叶子节点到根节点的距离都是相同的， 非叶子节点存放的了指向子节点的指针，而叶子节点存储了被索引的数据。树的深度和表的大小有直接关系。如图所示：
自适应hash索引
InnoDB存储引擎有一个被称为自适应hash索引的特性，当存储引擎发现某些索引值被频繁的访问时，他们会在原来的B-Tree索引的基础上在内存中在构建一个hash索引，让B-Tree索引具有了一些hash索引的特性。这个特性可以通过变量进行关闭。
B-Tree索引的使用场景
- 全值匹配: 指和索引中的所有列进行匹配。
- 匹配最左前缀: 指只使用多列索引的第一列。
- 匹配列前缀: 值可以只匹配某一列的值开头的部分
- 匹配范围值: 索引可以排序也就意味着可以匹配索引列某一范围的数据。
- 精准匹配某一列而范围匹配另外一列: 指精准匹配第一列，而第二列使用范围匹配。
- 只访问索引的查询（覆盖索引）: 指“只访问索引的查询”，查询只需要访问索引包含的列，而不需要访问完整额数据行。
B-Tree索引的限制
索引的顺序是是否重要的，在优化性能的时候，可能需要是同相同的列但顺序不同的索引来优化查询。
- 如果不是按照索引的最左列开始查找，则无法使用索引
- 不能跳过索引中的列。如索引（A,B,C）无法跳过B，而直接通过A，C进行匹配，这样只会使用索引的第一列A。
- 如果查询中有范围查询，则右边的列无法使用索引优化查询，如2中的B如果进行范围查询，则无法使用C进行查询优化。
全文索引
FULLTEXT是一种特殊类型的索引，它查找的是文本中的关键字，而不是比较索引中的值。适用于MATCH AGAINST的操作，而不是普通的WHERE操作。
索引的优点和高性能的索引策略
总结使用索引的优点大致有三条：
- 索引大大减少了服务器需要扫描的数据量。
- 索引可以帮助服务器避免排序和临时表。
- 索引可以将随机IO变为顺序IO。
高效的索引策略
- 选择性
索引的选择性是指：不重复的索引值（基数）和数据表的记录总数的比值，所有的选择性越高，则查询的效率越高。特别的：当使用前缀索引时前缀的“基数”应该尽量接近完整列的“基数”
- 多列索引
为多个列创建一个组合索引相比于在多个列上创建单个索引来说，往往可以提升MySQL的查询性能，虽然多个单个索引可以使用索引合并的特性，但可能存在几个方面的问题：
- 索引列需要多个索引相交（通常是AND条件下），这种情况下往往需要使用组合索引。
- 当优化器需要对多个索引做联合操作（通常是有多个OR条件），通常需要在算法的缓存，排序，合并上耗费大量的CPU和内存资源。
- 优化器不会将上面的成本计算到”查询成本“中，这会导致查询的成本被低估，导致该执行计划还不如进行全表扫描。
- 合适的索引顺序
正确的索引顺序依赖使用该索引的查询语句，同时需要考虑如何更好的满足排序和分组的操作要求。其中一个重要的经验法则是：将选择性最高的列放在索引的最前列。
聚簇索引
聚簇索引不是一种单独的索引类型，而是一种数据存储的方式，其实现依赖于存储引擎。在InnoDB中，聚簇索引在统一结构（一个数据文件）中保存了B-Tree索引和数据行，通过叶子节点保存行数据。每个表只能有一个聚簇索引，这是因为无法将数据同时存放在两个地方。
如果没有主动为表定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式的顶一个主键来作为表的聚簇索引。但是这样存在一个缺点，即所有需要这样隐藏主键的表都依赖一个单点”自增值“，会导致非常严重的锁竞争。
聚簇索引的优点
- 可以把相互关联的数据保存在一起。
- 数据的访问更快。
- 使用覆盖索引扫描的查询可以直接使用叶子节点中的主键值。
聚簇索引的缺点
- 如果数据全部存放在内存中，那么聚簇索引对于IO密集型应用的提示就没那么重要了。
- 插入速度严重依赖于插入数据主键的有序性。
- 更新聚簇索引的代价很高。
- 基于聚簇索引的表插入新行，或者主键被更新导致需要移动的时候可能面临页分裂的问题。
- 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏。
- 非聚簇索引可能比想象中的大。
- 非聚簇索引查询数据可能需要两次索引查找（回表）。
聚簇索引的数据结构
聚簇索引的非叶子节点只保留主键， 叶子节点存储主键、事务ID、用于事务和MVCC的回滚指针以及剩余列。而InnoDB的非聚簇索引叶子节点中保存的为主键值，并以此作为指向行的”指针“。在减少非聚簇索引的空间占用的同时，减少了当出现行移动或者数据页分裂时的索引维护工作
如果使用InnoDB的表并没有什么数据需要聚集，那么可以定义个自增的代理键作为主键，这样可以保证数据的顺序插入。同时最好避免随机的聚簇索引，并且避免使用UUID作为聚簇索引（UUID本身没有聚集性）。UUID不仅占用的空间更大，也同时会导致页分裂和碎片的产生。
为什么聚簇索引要保证有序性？
由于主键是有序的，所以InnoDB会将每一条数据存储在上一条记录的后面，当页打到最大填充因子时（InnoDB的默认最大填充因子是页大小的15/16， 而单页大小为16K），留出的部分用于后续修改，下一条记录就会默认被写入新的页中。这是期望的结果。
向聚簇索引中插入无序的数据会发生什么？
由于被插入的数据不一定比之前的大，所以InnoDB无法简单的将数据插入到索引的最后，而是需要为索引找到合适的位置，并分配空间，其中可能会增加很多额外的工作。如下：
- 写入的目标页坑没有被加载到内存中，或者已从内存中移除，那么InnoDB不得不将该页加载到内存中，这将导致大量的随机IO。
- 由于写入是乱序的，所以InnoDB不得不频繁的做页分裂操作，以为新的记录分配空间。
- 由于页分裂，所有数据页变得稀疏不规则，最终数据会有碎片。
为什么有时候按主键顺序插入也会存在性能问题？
对于高并发的工作负载，在InnoDB中按主键顺序插入可能造成明显的写入竞争，主键的上界会成为”热点“。
另外由于主键采用AUTO_INCREMENT锁机制，可能导致频繁的锁竞争。
非聚簇索引的数据结构
覆盖索引
通常我们会根据WHERE条件来设计索引，但优秀的索引设计应该考虑整个查询。比如：如果能通过一个索引直接获取需要的数据，那就没有必要直接获取列的数据。这种场景称为”覆盖索引“。
覆盖索引的优势
- 索引的条目通常远小于数据行的大小，如果只需要读取索引，那么将极大的减少数据访问量。
- 因为索引是有序的，所以对于范围查询，会比从磁盘读取每一行数据IO要小得多。
- 由于InnoDB聚簇索引的特性，覆盖索引对InnoDB引擎的表特别有用。
使用索引扫描来做排序
MySQL有两种方式来生成有序的结果，通过排序操作，或者通过索引顺序扫描。通过使用EXPLAIN的输出结果，type值为”index“则表明MySQL通过索引来进行排序。
索引排序的条件
- 只有索引顺序与
ORDER BY子句的顺序完全一致，并且所有列的排序顺序一样时，才会生效。 - 如果查询关联多张表只有当
ORDER BY子句引用的字段都在第一张表时，才会生效。 - 索引排序同样需要满足最左前缀匹配原则。
冗余和重复的索引
重复索引
MySQL支持在相同的列上创建多个相同的索引，所以当相同的列上出现相同顺序、相同类型的索引时，这种索引就是重复索引
冗余索引
冗余索引直接表现为包含关系，如（A，B）列上创建的索引与（A）列上创建的索引相比，后者就是冗余索引。大多数时间我们不需要冗余索引。创建多个冗余索引不仅会带来维护成本，同时也会降低插入性能。
未使用的索引
除了冗余索引和重复索引，还存在一些服务器永远用不到的索引。我们可以通过performance_schema和sys chema来查询那些没有被使用的索引。比如在table_io_waits_summary_by_usage视图中就可以知道哪些索引没有被使用过。
维护索引和表
更新索引的信息
MySQL的优化器是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描的行数。如果表没有统计信息，或者统计信息不准确，那么优化器可能会做出错误的判断。此时我们可以通过查询INFOMATION_SCHEMA.STATISTAICS表来查询统计信息，并通过ANALYZE TABLE <table> ENGINE=<engine>命令来重新统计表的信息。
减少索引和数据的碎片
B-Tree索引可能出现碎片化，这将会降低查询效率。其原因是由于B-Tree索引需要随机的磁盘访问才能定位到叶子页，这是不可避免的，如果叶子页在物理磁盘上分布是有序且紧密的那么性能将会更好。
表的数据存储也可能出现碎片化，有以下三种类型的数据碎片：
- 行碎片： 指数据行被存储在多个地方的片段中。
- 行间碎片： 指在逻辑上有序的页或行，在磁盘上不是有序存储的。
- 剩余空间碎片： 指数据页中有大量的空余空间。
