索引数据模型
提高查询效率的数据结构有很多，常见的有：
- 哈希表：哈希表适用于只有等值查询的场景，区间查询很慢
- 有序数组：有序数组做等值查询和区间查询都很快，但是插入记录的成本很高，适用于静态存储
- 搜索树：使用N叉树而不是简单地使用二叉树是为了减少读写磁盘的次数。以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了
InnoDB索引
MySQL索引是在存储引擎层实现的。
InnoDB的表都是根据主键以索引的形式存放的，这种存储方式称为索引组织表。
InnoDB使用B+树维护索引，每一个索引在InnoDB中对应一颗B+树。
根据叶子节点的内容，可以分为：
- 主键索引：主键索引叶子节点存储的是整行数据，主键索引也称为聚簇索引，根据主键索引查询只需要搜索一颗B+树
- 非主键索引：非主键索引叶子节点存储的是主键值，非主键索引也称为二级索引，根据非主键索引查询需要搜索两颗B+树，即需要回表
由于主键索引树的叶子结点是数据，普通索引树的叶子结点是主键值，所以普通索引树比主键索引树小很多。
插入数据时需要做一些操作来维护B+树，可能涉及页分裂和页合并。
什么情况下需要重建索引？索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会把数据按顺序插入，这样页面的利用率更高，也就是索引更紧凑、更省空间。
自增主键
某些建表规范要求使用自增主键，为什么？
- 使用自增主键的好处是每次都是追加操作，不会触发叶子节点的分裂，如果使用业务字段做主键，不容易保证有序插入，写数据的成本就会大一些
- 非主键索引的叶子节点存储的是主键值，使用自增主键占用的空间小
- 因此，从时间和空间两方面的效率来看，使用自增主键一般都是更好的选择
有没有场景适合用业务字段做主键，而不使用自增主键呢？有的，如果你的业务场景只有一个索引，并且是唯一索引，那么也可以使用业务字段做主键。
为什么自增主键会不连续？主要原因在于获取自增主键id时加的锁不是一个事务锁，每次申请完就马上释放，为了保证性能，InnoDB在插入失败或回滚时不会回退自增主键id。另外，批量插入语句会采用批量申请自增id的策略，可能有部分id被浪费。总之，自增主键保证递增，但不保证连续。
覆盖索引
如果根据某个索引查询时不需要回表，这个索引就称为覆盖索引，覆盖索引是一种常用的优化手段。
比如，公民信息表，以身份证号做了索引，还有必要以身份证号+名字做联合索引吗？如果根据身份证号查询名字是高频的，那么以身份证号+名字做联合索引是合适的，这样就用到了覆盖索引，而无需回表。
最左前缀原则
B+树结构的索引，可以利用索引的“最左前缀”来定位记录。只要满足最左前缀，就可以用到索引来加速查询，这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左N个字符。
在建立联合索引的时候，如何安排索引内的字段顺序呢？
- 一个原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往是需要优先考虑采用的。比如，已经有了(a,b)的联合索引，一般就不需要单独在a上建立索引了
- 那么如果需要单独查b呢？查询条件里只有b的语句，是无法使用(a,b)这个联合索引的，此时就不得不为b单独再建一个索引
- 如果既有a和b的联合查询，又有分别查询a或b，那么应当考虑将占用空间更小的建立单独索引
前缀索引
ALTER TABLE user ADD INDEX index1(email);
ALTER TABLE user ADD INDEX index2(email(6));
即我们可以定义字符串的一部分作为索引，这种称为前缀索引。
前缀索引的好处是节省空间，代价是可能增加额外的记录扫描次数，降低了查询效率。关键点在于前缀长度，选择合适的长度，既可以节省空间，又不会额外增加太多查询成本。
前缀索引还有一个不利影响是使用了前缀索引就用不上覆盖索引对查询性能的优化了，因为前缀索引总是要回表的。
如果要建立索引的字段的前n个字符区分度很差怎么办？使用它们建立前缀索引就不合适了。如果末尾的子串具备合理的区分度，那么可以考虑倒序存储+前缀索引。
除了前缀索引，还有没有其他方式优化字符串字段的索引问题呢？有的，可以考虑使用hash字段，即对字符串字段做hash运算，记录hash值，然后在hash值上建立索引。
倒序存储+前缀索引、hash字段这两种优化方式最大的限制就是不支持范围查询，只能做等值查询。
索引下推
MySQL 5.6引入，可以在索引遍历过程中，对索引中包含的字段先做判断，不满足查询条件的直接过滤，减少回表次数。
唯一索引 vs 普通索引
唯一索引和普通索引在读的效率上差别微乎其微，因为InnoDB的数据是按数据页为单位来读写的，当需要读一条记录的时候，并不是将这个记录从磁盘读出来，而是以页为单位，整体读入内存。
唯一索引和普通索引在写的效率上差别如何？这当中涉及到change buffer这个机制。
当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。
唯一索引不会使用change buffer，因为需要判断是否违反唯一性原则，需要将数据页读入内存，将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。已经读入到内存了，直接更新内存就可以了。
普通索引可以使用change buffer，change buffer减少了随机磁盘访问，所以对更新性能的提升是会很明显的。
那么是所有普通索引的场景，都适合使用change buffer吗？对于写多读少的业务来说，change buffer可以大大提高效率，但是如果写完以后马上就读，change buffer反而可能会起反作用。
那么唯一索引和普通索引到底怎么选呢？一般的，建议尽量使用普通索引，如果写操作后马上要读，可以关闭change buffer.
change buffer vs redo log
通过下面这张图来理解change buffer和redo log的关系：
change buffer主要节省的是随机读磁盘的IO消耗，redo log主要节省的是随机写磁盘的IO消耗。
为什么MySQL可能选错索引？
选择索引是优化器的工作，优化器会依据扫描行数、是否使用临时表、是否排序等多种因素决定使用哪一个索引。
扫描行数是怎么计算的呢？MySQL并不能精确计算扫描行数，只能根据统计信息来预估扫描行数。这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。可以通过如下语句查看索引的信息：
show index from xxxtable;
这个基数是怎么得到的？通过采样统计，采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。由于是采样统计，这个基数是很容易不准的。如果你发现explain的结果和实际差别较大，可以通过如下命令来重新统计索引信息：
analyze table xxxtable;
MySQL选择了错误的索引时怎么办？
- 如果是索引统计信息不准，可以通过analyze table t 命令，重新统计索引信息
- 使用force index强制使用某个索引，这种方法不灵活
- 修改查询语句，引导MySQL使用合适的索引
- 新增更合适的索引或者删除误用的索引
为什么看起来使用了索引但是效率很差？
SELECT COUNT(*) FROM xxx WHERE MONTH(created_at) = 7;
对索引字段做函数操作，可能会破坏索引值的有序性，所以就不能使用树搜索功能来快速定位，而不得不使用全索引扫描来遍历索引树。
SELECT * FROM xxx WHERE xxxstr = 123456;
在MySQL中，字符串和整数做比较，是将字符串转换成整数。也就是说，上面的语句等效于：
SELECT * FROM xxx WHERE CAST(xxxstr AS SIGNED INT) = 123456;
所以这也是对索引字段做函数操作，效率差是因为没有用到树搜索功能。
还有一种看起来使用了索引但是效率很差的例子是隐式字符编码转换，比如uft8转换utf8mb4，也等效于对索引字段做函数操作，导致用不到树搜索功能。