索引的选择
索引(Index)是帮助MySQL高效获取数据的数据结构，可以理解为快速查找排好序的一种数据结构。Mysql索引主要三种结构：Hash索引、有序数据索引、B+Tree索引,本篇文章针对InnoDB 引擎中B+Tree的索引展开
InnoDB 使用了 B+ 树索引模型，在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。所以数据都是存储在 B+ 树中的。所以每一个索引在 InnoDB 里面对应一棵 B+ 树。
通过test表说明
create table test(id int primary key, age int, name varchar(16),index (age));
表中 R1~R5 的 (ID,age) 值分别为 (10,1)、(20,2)、(30,3)、(50,5) 和 (60,6)两棵树的示例示意图如下。
图一 主键索引的结构树
图二 非主键索引的结构树
非叶子节点存储索引key，叶子节点存储key+data，每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。这样就提高了区间访问性能：如果要查询key为从2到20所有数据记录，当找到2后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，提到了区间查询效率(无需返回上层父节点重复遍历查找减少IO操作)。
主键索引叶子节点存储的是key+整行的数据；非主键索引叶子节点存储的是key+主键的值。
如果是select * from T where ID=50，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
如果是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。
也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，在应用中应该尽量使用主键查询。
B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。以图一为例，如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400，则需要逻辑上挪动后面的数据，空出位置，比较麻烦。而如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。页分裂操作还影响数据页的利用率。
指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。正符合递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。
首先不容易保证有序插入，这样写数据性能成本相对较高。从存储空间的角度来看。由于每个非主键索引的叶子节点上都是主键的值。如果用比较长的字段做主键，那么每个二级索引的叶子节点占用的字节也会更长。
总结:在业务场景只需要一个索引，且要保证唯一性的情况下，用业务字段做主键是比较合适；反之如果不适合上述条件，从性能和存储的角度考虑，使用自增主键更加合理。
以图二为例，select id from test where age > 3;
因为id的值已经在age的索引树上了，所以可以直接提供查询结果，不用回表，对于这种索引已覆盖了查询需求的，成为覆盖索引，由于覆盖索引可以减少树的搜索次数，提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
用以下用户表举例
CREATE TABLE `user` ( `id` int(11), `id_card` varchar(16), `name` varchar(12), `age` int(6), `sex` varchar(4), PRIMARY KEY (`id`), KEY `phone_name` (`phone`,`name`), KEY `name_age_sex` (`name`,`age`,`sex`))
如果业务场景经常性的需要根据手机号查询用户的姓名，这种情况下，可以建立phone和name的联合索引，phone_name联合索引也是覆盖索引，包含了该经常查询的需求，不需要消耗额外的性能去回表查询。
最左优先，索引项按照索引定义里面出现的字段顺序排序。
如上诉索引name_age_sex 可以看做是name单独索引name_age联合索引和name_age_sex联合索引，应以最高频优先进行字段顺序排序。
因此，联合索引的选择应根据业务场景需求，综合考虑性能存储来进行定义。
select id from test where age=3
对于普通索引，查询到（3,30）时，需要继续向下查询，直到碰到age不等于3为止
对于唯一索引，查询到（3,30）时，可以直接结束查询
从这里看，唯一索引查询更快，但是此处的性能差距很小
因为InnoDB 的数据是按数据页为单位来读写的。当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。因为引擎是按页读写的，所以说，当找到 age=3 的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。
所以从查询来看，二者的差别不大
change buffer概念
当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性，显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。
对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束，要将数据页读入内存判断。而这时直接更新内存会更快，就没必要使用 change buffer 了。所以change buffer不适用唯一索引，适用普通索引
用以下测试表说明
create table test1(id int primary key,name VARCHAR(8) );
对于要插入一条数据（7，’hh’）来说，有两种场景
第一种场景:记录更新的目标页在内存中
对于唯一索引，找到6和8之间的位置，判断是否符合唯一性，插入结束
对于普通索引，找到6和8之间的位置，插入结束
此处两种索引的区别是一个判断，差距不大
第二种场景: 记录更新的目标页不在内存中
对于唯一索引，找到并将数据页读到内存，找到6和8之间的位置，判断是否符合唯一性，插入结束
对于普通索引，则是将更新记录进change buffer，插入就结束了
将数据页读入内存是比较消耗性能的，可以看出，change buffe减少了对磁盘的访问，性能提升可观，在change buffer更新进原始数据页前，change buffer记录的变更越多，效果就越明显。
因此，对于写多读少的业务来说，此时选择普通索引结合change buffer的使用效果明显，反之对于写后立即读的场景且要求唯一性，因为写后马上要访问数据页，此时change buffer 反而多此一举，唯一索引更加适合
原文：https://www.cnblogs.com/baih/p/14758295.html