几种常用索引以及使用策略
（本篇内容均出自《高性能MySQL》一书，是一篇粗略的笔记，有幸遇见建议直接观看书中第五章)
一、概述
索引是存储引擎用于快速查找记录的一种数据结构，它对性能的影响非常关键，尤其是当表中数据量越来越大的时候。因此索引优化是提高查询性能的有效手段，查询和索引也需要配合使用。本文将介绍索引的种类，索引策略，以及一些注意事项。
二、索引类型
索引类型很多，在MYSQL中，索引是在存储引擎层而不是服务层实现，所以并没有统一的索引标准：不同存储引擎的索引的工作方式并不一样；不是所有的存储引擎都支持所有类型的索引；即使多个存储引擎支持同一种类型到的索引，底层实现也可能不同。
2.1 B+Tree索引
多数存储引擎都使用B+Tree(Balance Tree)索引，B+Tre结构e是B-Tree的变种，因此有必要先介绍两种数据结构。
2.1.1 选用B-Tree的目的
树结构很多，如二叉树、平衡二叉树、红黑树，为什么要选用B-Tree呢？
内存和硬盘的速度相差太多了，并且硬盘是将所有信息分割成相等大小的页面，每次硬盘读写都是一个或多个完整的页面。因此为提高时间效率，应尽量减少I/O次数，并且每次从硬盘中读取到更多且合适点的数据量。
像二叉树类的结构，每个结点只存储了一个元素，如果要存储大量数据，就会形成一棵很“高”的树，每次查找就会进行大量I/O，效率极低。而B-Tree对此进行了调整，使得每个结点可以存储多个元素，与硬盘存储的页面大小相匹配。
通过上图(图来源于网络)这种方式，每次I/O都能获得最大数量的数据，减少了必须访问结点和数据块的数量，可以说B-Tree是专门为内外存的数据交互准备的。
2.1.2 B-Tree与B+Tree的差异
对于树结构来说，都可以通过中序遍历来顺序查找树中的元素，但这种遍历对于B-Tree意味着要在硬盘的不同页面之间进行多次访问，并且在分支结点上同时存储键值和数据，就减少了一个页面内存放的键值的数量。在这些问题基础上就产生了B+Tree(图来源于网络)。
在B-Tree中，每个元素在树中只出现了一次，有可能在叶子结点，也可能在分支结点上。但B+Tree中，出现在分支结点上的元素会在叶子结点中再次出现，叶子结点本身依据键值大小顺序链接。并且分支结点上的元素仅含键值，要对实际数据进行访问需要到达该键值的终端结点。
2.1.3 可用B+Tree索引的查询类型
B+Tree索引对全键值、键值范围和建前缀查找有效：
- 全值匹配：和索引中所有列进行匹配
- 匹配最左前缀：只使用索引的第一列
- 匹配列前缀：使用索引到的第一列，匹配这一列值的开头部分
- 匹配范围值：使用索引到的第一列进行范围匹配
- 精确匹配某一列并范围匹配另外一列：对第一列使用全匹配，并对第二列进行范围匹配
- 只访问索引的查询：查询时只访问索引内容，不需要访问数据行
同时由于B+Tree是顺序组织存储的，该索引还适用于查询中的排序操作，一般order by子句满足上面列出的这几种查询类型，则这个索引也可以满足对应的排序需求。
2.1.4 B+Tree索引的限制
假设表中存在索引key(name,age,gender)：
- 若不是按照索引的最左列开始查找，则无法使用索引。如key索引无法用于查找某个年龄或某个性别的人，同时也无法查找name以某个字结尾的人
- 不能跳过索引中的列。如查询用到name，若不使用age，则索引不能继续用gender进行匹配，就只能使用name进行索引
- 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。如查询语句name='Aidan' and age>10 and gender = 'm'，由于age是一个范围查询，则这个查询只能用到key索引的前面两列。
2.2 哈希索引
MySQL中只有Memory引擎支持哈希索引(也支持B+Tree索引)，是其默认的索引类型。Memory引擎支持非唯一哈希索引，采用链接法消除哈希冲突。
InnoDB引擎中有一个功能叫“自适应哈希索引”，当InnoDB注意到某些索引值被使用得很频繁时，它会在内存中基于B+Tree索引之上再创建一个哈希索引，让B+Tree也具有一些哈希索引的优点。但这是一个完全自动的行为，不受用户控制或配置，不过可以关闭该功能。
哈希索引基于哈希表实现，哈希表中保存着指向每个数据行的指针。对于每一行数据，存储引擎会对所有索引的列计算一个哈希码。
2.2.1 哈希索引的限制
哈希索引的检索效率非常高，不需要想B+Tree索引需要从根结点到支结点，但哈希索引的特殊性也带来了很多限制：
- 哈希索引是使用索引列的全部内容来计算哈希值的，所以哈希索引不支持部分索引列匹配查找
- 哈希索引数据并不是按照索引值顺序存储的，所以无法用于排序
- 哈希索引只支持等值比较查询，包括=，IN( )，<=>。也不支持任何范围查询
- 哈希索引只包括哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行
- 如果哈希冲突很多，则维护索引的代价也会很高
2.3 全文索引
全文索引作用是查找文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引匹配方式完全不一样，它更类似于搜索引擎做的事，而不单单只是进行where的条件匹配。
三、索引策略
3.1 独立的列
索引列必须是独立的，不能是表达式的一部分或者是函数的参数，否则MySQL就不会使用索引。
- select age from person where age+1 = 10;
MySQL不会自动解析age+1这个表达式，因此我们使用时需要对where进行简化
- select …… where to_days(current_date) - to_days(date_col) <= 10;
3.2 前缀索引和索引选择性
对BLOB、TEXT或很长的VARCHAR类型进行索引，必须使用前缀索引，MySQL不允许索引这些列的完整长度。那么现在的关键在于选择多长的前缀。
这里需要提到一个概念，索引的选择性。它是指不重复的索引值和数据表的记录总数(#T)的比值，范围在1/#T到1之间。根据公式，选择性越高则查询效率越高，因此要保证前缀索引的选择性接近索引的整个列，但长度又不能太长。但在具体表中，不能仅看平均选择性，还要注意数据分布的平均性。
3.3 多列索引
多列索引不是为每个列创建独立的索引，事实上在多个列上创建单列索引在大多数情况下并不能提高查询性能。在早期版本，这种情况只会使用其中某一个单列索引或者根本不会使用索引。在MySQL5.0以及之后的版本，产生了索引合并策略，即可能会使用这些单列索引进行扫描，并对结果进行合并，但这并不能称之为行之有效。若在explain中看到索引合并，应检查表结构。也可通过optimizer_switch参数关闭索引合并功能，也可使用IGNORE INDEX提示让优化器忽略掉某些索引。
除了以上错误理解多列索引，还要注意索引列顺序问题。这里是指B+Tree索引，因为B+Tree索引是顺序组织存储的，适合排序，所以需要考虑如何更好地满足排序和分组的需要。
3.4 聚簇索引
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构。InnoDB的聚簇索引实际是按照每张表的主键构造一棵B+树，同时在叶子节点中存放整张表到的行记录数据。
这个特性使得索引组织表中的数据也是索引的一部分，每张表只能拥有一个聚簇索引。InnoDB通过主键聚集数据，如果没有定义主键，InnoDB会选择非空唯一索引替代，如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录，包含相邻键值的页面可能会相距很远。
聚簇索引的优点：
- 数据访问更快。因为索引和数据保存在同一棵B+树，所以获取数据时，比非聚簇索引更快
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值，即对主键的排序查找和范围查找速度非常快
聚簇索引的缺点：
- 插入速度严重依赖插入顺序。按照主键的顺序插入是加在数据到InnoDB表中速度最快的方式，但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表
- 更新聚簇索引列代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置
- 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间(与B+Tree结构的插入删除更新有关)
- 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者页分裂导致数据存储不连续的时候
- 二级索引(辅助索引)可能比想象的要更大，因为二级索引的叶子节点包含了引用行的主键列
- 二级索引访问需要两次索引查找，第一次记现在辅助索引的B+Tree中检索，到其叶子节点获取对应到的主键；第二次使用主键在主索引B+Tree中再执行一次检索，最终到叶子节点获取到整行数据
辅助索引：
上面提到了辅助索引，也叫非聚簇索引，是在聚簇索引纸上创建的索引。辅助索引存储的叶子节点不再是行的物理位置，而是行的主键值，因此利用辅助索引访问数据总是需要二次查找。
3.4.1 InnoDB和MyISAM数据分布对比
这里我们实际探究聚簇索引和非聚簇索引的数据分布，以及主键索引和二级索引的数据分布的区别。通过这样一张表进行对比：
create table test(
col1 int not null,
col2 int not null,
primary key(col1),
key(col2)
);
MyISAM数据分布：MyISAM是按照是数据插入的顺序存储在磁盘上。行的旁边显示了行号，从0递增。因为行是定长的，所以MyISAM可以从表的开头跳过所需的字节找到需要的行。
MyISAM支持B+Tree索引，但并不是聚簇，它的数据存储和主键索引是分开的。它的主键索引中，叶子结点仅存储着键值和行号，按索引列排序。
对于col2，非主键的索引，和主键索引一样
InnoDB的数据分布：
InnoDB支持聚簇索引，整个索引实际包含了整个表，所以不需要像MyISAM那样独立的行存储。
(如果主键是一个前缀索引，InnoDB也会包含完整的主键列和剩下的其他列)
与MyISAM的二级索引不同，InnoDB二级索引的叶子结点存储的不是“行指针”，而是主键值。这样做减少了当出现行移动或者数据页分裂时二级索引的维护工作，不过使用主键值会让二级索引占用更多空间。
整体对比：
3.4.2 在InnoDB表中按主键顺序插入
这里主要解释为什么尽量按顺序写入数据行。主键值是顺序的，则InnoDB把每一条记录都存储在上一条记录的后面，当达到页的最大填充因子时，下一条记录就会写入新页。但是对于非顺序插入，插入就会变得很随机，每次插入都需要为新的值找合适的位置，因此需要把原来的值重新更改位置，分配空间。这样做就会有：
- 写入的目标也可能已经刷到磁盘上并从缓存中移除，或者还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中，这就会产生大量随机I/O
- 写入的乱序使得InnoDB不得不频繁进行页分裂操作，以分配新的行空间。页分裂会移动大量数据，影响效率，并且页会变得稀疏，造成空间上的浪费
但对于高并发工作负载，InnoDB中按主键顺序插入可能会造成明显的争用。
3.5 覆盖索引
MySQL可以使用索引来直接获取列的数据，这样就避免了再去读取数据行。如果一个索引包含所有需要查询的字段的值，那么就称之为“覆盖索引”。
这样做的优点是：
- 索引条目通常远小于数据行的大小，只读取索引，就大大减少了数据访问量。而且索引比数据更小，对于I/O密集型应用，每次就能放更多到内存(MyISAM能压缩索引，这点对其很有帮助)
- 因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查找会比随机读取每一行数据的I/O要少的多
- 对MyISAM来说，内存中只缓存索引，数据则依赖操作系统缓存，因此每一次访问数据就需要一次系统调用；对InnoDB来说，如果能够在二级索引上覆盖查询，则可以避免对聚簇索引的二次查询
覆盖索引的条件是必须要存储索引列的值，因此哈希索引、全文索引和空间索引都不能成为覆盖索引。同时也不是所有引擎都支持覆盖索引。
当发起一个被索引覆盖的查询时，在explain的extra列中可以看到“Using index”的信息，使用索引覆盖查询必须要注意：
- 索引一定是覆盖了查询的所有列
- 存储引擎在索引中执行的操作有限制。在MySQL5.5以及之前版本中，只允许在索引中做等于、不等于、大于等简单操作。MySQL能在索引中做最左前缀匹配的like匹配，但以通配符开头的like查询，就无法在索引中匹配，只能取数据行进行比较
如果查询中有部分可以被索引覆盖，则可以尝试改变sql语句，先进行在索引中查询，缩小范围，再提取数据行。
3.6 使用索引扫描来排序
MySQL有两种方式可以生成有序的结果：排序操作和按索引顺序扫描。如果explain出来的type列为“index”，则说明MySQL使用了索引扫描来排序。利用索引进行排序需要符合：
- 只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才能使用索引来对结果做排序
- 如果查询需要关联多张表，则只有当order by 子句引用的字段全部为第一个表时，才能使用索引做排序
- order by子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则MySQL都需要执行排序操作，而无法利用排序操作
以上最后一点，当前导列为常量的时候，order by子句可以不满足索引的最左前缀的要求。
例如有索引index(name,age)，若使用select name,age from person order by age，则不能用索引进行排序，但如果select name,age from person where name='Aidan' order by age，通过where条件指定了索引前面的列，形成了最左前缀条件，就能够使用。
一些不能使用索引做排序的查询(假设有index(rental_date,inventory_id,customer_id)索引)：
1、查询使用了两种不同的排序方向，但是索引列都是正序排序
……where rental_date='2020-09-01' order by inventory_id DESC, customer_id ASC;
2、查询的order by子句中引用了一个不在索引中的列
……where rental_date='2020-09-01' order by inventory_id, staff_id;
3、查询的where和order by中的列无法组合成索引的最左前缀
……where rental_date='2020-09-01' order by customer_id;
4、查询在索引列的第一列上是范围条件，所以MySQL无法使用索引的其余列
……where rental_date>'2020-09-01' order by inventory_id, customer_id;
5、查询在某一列行有多个等于条件
……where rental_date='2020-09-01' and inventory_id in(1,2) order by customer_id;
3.7 前缀压缩索引
MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中。默认只压缩字符串，但通过参数设置也可以对整数做压缩。
MyISAM压缩每个所以块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来。例如，第一个值是perform，第二个值是performance，则第二个值通过前缀压缩后就是类似于“7，ance”的形式。MyISAM对行指针也采用类似的前缀压缩方式。
压缩在某些情况下会提高性能，但某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值，所以MyISAM查找是无法在索引块使用二分查找而只能从头开始扫描。如果倒序扫描就更糟糕了。因此对I/O密集型应用比较适用，而CPU密集型就比较慢了。可以在create table语句中指定PACK_KEYS参数控制索引压缩方式。
3.8 索引和锁
索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行，那么就会锁定更少的行。虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外开销；锁定超过需要的行会增加锁争用并减少并发性。
InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后，MySQL服务器才能应用WHERE子句。这时已经无法避免锁定行了：InnoDB已经锁住了这些行，到适当的时候才释放。在MySQL 5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但是在早期的MySQL版本中，InnoDB只有在事务提交后才能释放锁。
例：有索引index(id)，在执行……where id<5 and id<>1时，最后会返回2~4行，但实际执行时会锁住1~4行。因为底层存储引擎的操作是“从索引的开头开始获取满足条件actor_id <5的记录”，服务器并没有告诉InnoDB可以过滤第1行的WHERE条件。
