1、聚集索引和非聚集索引

聚集索引（聚簇索引）： 使用主键值的大小进行记录和页的排序，叶子节点存放完整数据记录的B+树。
非聚集索引（非聚簇索引或二级索引或辅助索引）：以非主键列大小为排序规则，叶子节点存放记录主键的B+树。

2、索引的代价

每个表需要建立的最大索引个数为16

  • 空间上的代价

每建立一个索引都要为其建立一棵B+树，每棵B+树的每个节点都是一个数据页，每个数据页都默认占用16KB的存储空间。

  • 时间上的代价

每当对表中数据进行增删改操作时，都需要修改各个B+树索引。

3、创建索引

  • 只为用户搜索排序和分组的字段创建索引
  • 考虑索引选择性（不重复的索引值占总记录总数的比值），索引选择性太低，说明列包含太多重复值，可能需要执行大量回表操作
  • 索引列类型尽量小，索引列占用大小越小，数据页存放索引目录项越多，读写效率越高
  • 如果需要建立索引字段长度很长，可以考虑建立前缀索引
  • 表主键最好选择自增，避免页分裂带来的损耗

4、索引的使用

4.1、回表的代价及覆盖索引

使用二级索引记录时，需要先查出对应的主键，再用主键回到聚集索引搜索对应记录，该过程称为回表。

由于查询结果所需要的数据只在主键索引上有，所以不得不回表，但是如果查询的结果已经在二级索引树上，就不需要回表，称为覆盖索引

例：比如一张学生信息表，主键为学号，通过身份证和姓名建立了联合索引 idx_id_name,那么使用身份证查询学生姓名时就无需回表查主键索引，通过联合索引 idx_id_name即可查出姓名信息

4.2、最左前缀原则

如果对字段A,B建立联合索引，那么数据记录是先按照A排序，字段A值相同时按照B排序，此时如果查询时只传入B字段的值是无法使用索引的，需要满足最左前缀原则，即sql语句条件字段必须按照联合索引字段顺序的最左开始匹配。

在建立联合索引的时候，如何安排索引内的字段顺序？
如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的

4.3、索引下推

以联合索引（name, age）为例，如下sql由于对name字段使用了右模糊，所以只能用上索引name，无法使用age

  1. mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

在 MySQL 5.6 之前，只能用 “张”找到第一个满足条件的记录 ID，然后用ID开始一个个回表，到主键索引上找出数据行，再对比字段值。
而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

4.4、索引合并

MySQL5.0之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描。但是从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。

索引合并即使用多个索引完成一次查询的执行过程，where 中可能有多个条件(或者join)涉及到多个字段，多个条件使用and或者or连接，那么久有可能使用到索引合并，即对多个索引分别进行条件扫描，然后将它们各自的结果进行合并。

（1）Intersection(交集)索引合并

SELECT * FROM TABLE WHERE key1 = 'a' and key2 = 'b'

使用索引idx_key1扫描key1值在[‘a’,’a’]区间的二级索引记录，使用索引idx_key2扫描key2值在[‘b’,’b’]区间的二级索引记录，然后从两者的操作结果中找出id值相同的记录，根据这些id进行回表操作，交集索引合并即对不同索引中扫描到的记录的id值取交集，只为这些id执行回表操作。

使用交集索引合并的要求：如果使用的都是二级索引，则要求从每个索引中取到的二级索引记录都是按照主键值排序的（实际只有单点扫描区间对应的二级索引记录是按照主键值排序的）
原因：
从两个有序集合中取交集比从两个无序集合中取交集要容易的多
如果获取的id值是有序的，那么根据这些id值执行回表操作时就不再是进行单纯的随机io，从而提供效率

不能使用交集合并索引的情况：

使用索引idx_key1扫描key1值在[‘a’,+00]区间中的二级索引记录不是按照主键值排序的
SELECT * FROM TABLE WHERE key1 > 'a' and key2 = 'b'

key_part1 和 key_part2,,共同组成联合索引，使用key_part1 扫描[‘b’,’b’]区间得到的二级索引记录也不是按照主键排序的
SELECT * FROM TABLE WHERE key1 > 'a' and key_part1 = 'b'

（2）Union(并集)索引合并

SELECT * FROM TABLE WHERE key1 = 'a' or key2 = 'b'

和交集索引合并类似，并集索引合并即对不同索引中扫描到的记录的id值取并集，只为这些id执行回表操作。

使用交集索引合并的要求也和交集索引合并相同：如果使用的都是二级索引，则要求从每个索引中取到的二级索引记录都是按照主键值排序的（实际只有单点扫描区间对应的二级索引记录是按照主键值排序的）

（3）Sort Union(排序并集)索引合并

并集索引合并的条件太苛刻，必须保证从各个索引扫描到的记录的主键值是有序的。

排序并集索引合并是对从不同索引中扫描到的记录的id值进行排序，然后按照并集索引合并的方式，取并集，然后回表。

为什么没有Sort Intersection(排序交集)索引合并？

Sort Union针对的是单独根据搜索条件从某个二级索引中获取的记录数较少的场景，即使对二级索引记录按照主键值进行排序，成本也不会太高，而
Intersection索引合并针对的是单独从某个二级索引获取的记录数太多，导致回表成本太大的使用场景，使用Intersection索引合并后可以明显降低回表成本，但是如果加入Sort Intersection索引合并，就需要为大量的二级索引记录按照主键值进行排序，这个成本可能比使用单个二级索引的查询成本更高，所以mysql没有引入Sort Intersection

4.5、如何给字符串加前缀索引

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

如何解决前缀索引区分度不高的问题？
1、使用倒序存储，如存储身份证号的时候把它倒过来存，后6位的区分度要高于前面部分
2、使用 hash 字段 在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引

4.6、普通索引和唯一索引怎么选？

两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响，建议尽量选择普通索引

（1）查询过程

以查询语句 select id from T where a=5为例

  • 对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录
  • 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索

但是两者带来的性能差距其实微乎其微，InnoDB 的数据是按数据页为单位来读写的，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。当找到 k=5 的记录的时候，它所在的数据页绝大可能就都在内存里了，如果 k=5 这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页。
对于整型字段，一个数据页可以放近千个 key，因此出现这种情况的概率会很低，所以我们可以认为两者查询从性能上来说几乎相同。

（2）更新过程

changBuffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
change buffer实际上是可以持久化的数据，change buffer 在内存中有拷贝，也会被写入到磁盘上。将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。
显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束，所以唯一索引的更新就不能使用 change buffer，而普通索引可以使用 change buffer

对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束
对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束

普通索引的所有场景，使用 change buffer 都可以起到加速作用吗？
对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。
假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价