极客时间MySQL专栏总结

索引的类型

索引的出现是为了提高数据查询的效率，就像书的目录一样。实现索引的方式也有很多种，MySQL有如下几种类型的索引。

哈希索引

我们都知道哈希表这种数据结构，哈希索引就是基于这种数据结构的索引。

• 优点：新增、精确查询都很快，O(1)的时间复杂度；
• 缺点：

1. 无法用于排序和分组；
2. 区间查询很慢。

• 适用场景：等值查询。

全文索引

MyISAM存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。
查找条件使用MATCH AGAINST，而不是普通的WHERE。
全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。
InnoDB存储引擎在MySQL 5.6.4 版本中也开始支持全文索引。

B+树索引

不同数据结构的出现，主要是为了解决不同场景，不同操作的效率问题。B+树是适合存在磁盘的数据结构，它有以下特点：

1. B+树的每个节点都是一页；

从磁盘读取数据的时候，并不是一条一条读的，而是读“一块”（好几条）数据到内存。在MySQL中，这“一块”的单位就是页，默认大小为16k。

2. B+树是N叉树，N取决于字段的大小和页的大小；

假设一棵二叉树高度为5，目标值在树的底部，那么在进行深度优先搜索过程中，就需要经过5个节点。对于存储在磁盘的B+树来说，经过5个节点就意味着要查询5次磁盘，是比较耗时的操作。如果这棵树的高度低一些，就意味着可以少进行几次查询磁盘的操作。

同样一棵树，每个节点的子节点（扇出）越多，那么它的树高也越低。所以B+树是N叉树的结构，是更适合磁盘的数据结构。

以整数（bigint）字段索引为例，一个key为8B，一个引用约为6B，所以一条数据的索引占据空间约为14B，一页可以存：16K/14B ≈ 1170 条数据的索引，也就是说，此时N = 1170，是1170叉树。

3. B+树的非叶子节点存key（索引字段的值）和其它页的引用，叶子节点存全部数据；

假想如果在非叶子节点也存全部数据（一般来说大小远超6B）的话，那么每页可以存的索引条数就会变少，也就是说每个节点的子节点会变少，进而导致树的高度变大。

4. B+树的每个节点中的key都是按照顺序排列的。

在每个页查找的时候，可以使用二分法查找。

在InnoDB中，表都是根据主键顺序，以索引（B+树）的形式存放的，这种存储方式称为索引组织表。
根据索引类型，分为两种：

1. 主键索引：叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）；
2. 非主键索引：叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

二者区别：

1. 主键查询方式，则只需要搜索 ID 这棵 B+ 树；
2. 普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。
3. 基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

图片来自极客时间MySQL学习专栏

索引的维护

B+树为了维护索引的有序性，在插入新值的时候需要做必要的维护。

根据插入的类型，可以分为几种：

1. 直接在后面插入；
2. 插在中间，挪动后面的数据；
3. 插入时数据满了，会造成页分裂，性能受影响，空间利用率降低；
4. 合并：当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并，合并的过程，可以认为是分裂的逆过程。

对于以上步骤，我们可以想到：

• 为什么建表语句中最好使用自增主键？

在自增主键的模式下，每次都是递增的值，可以让索引的维护，每次都是追加操作，不会挪动其他记录，避免了页分裂的情况。
如果使用业务逻辑字段做主键，往往不容易保证有序插入，写数据成本会变高。除了性能方面，从存储空间的角度来看，如果业务字段的长度比较大（例如身份证号），使用业务字段作为主键，普通索引的叶子节点会变大，会使普通索引占用更多空间。

• 哪些场景适合业务字段作为主键？

1. 只有一个索引；
2. 该索引必须是唯一索引。

典型的KV场景。

• 重建索引

索引可能因为页分裂、删除的原因，导致数据页有空洞，重建索引可以把数据按顺序插入，让索引更紧凑更省空间。

• 重建主键索引

 alter table T engine=InnoDB

索引的使用优化

create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

回表

select ID from T where k between 3 and 5

查询步骤：

1. 在索引k上查询到k = 3的记录，得到ID = 300；
2. 在主键索引上查询到ID = 300对应的记录；
3. 在索引k上查询到k = 5的记录，得到ID = 500；
4. 在主键索引上查询到ID = 500对应的记录；
5. 在索引k上未查询到k = 6的记录，不满足条件，循环结束。

在这个过程中，回到主键索引查询的过程，称为回表。

覆盖索引

select ID from T where k between 3 and 5

我们知道在普通索引中，叶子节点存的是主键（ID），而这条SQL语句只查询ID，因此不需要再进行回表操作，索引已经覆盖了我们的查询需求，这种情况叫做覆盖索引。

可以想到，对于一些高频的，请求部分字段的查询，我们可以对这些字段创建一个联合索引，提升查询效率。

最左前缀原则

最左前缀可以是联合作引的最左N个字段，也可以是字符串索引的最左M个字符。

如何安排联合索引字段顺序：

1. 如果通过调整顺序，可以少维护一个索引，这个顺序可以优先考虑；
2. 如果既有联合查询（a,b），又有单个查询(a) ，(b)，这时候不得不维护另外一个索引，此时考虑的原则是空间，选择字段小的作为单独索引。

a小的话索引就是：(b,a)、(a)；反之则是：(a,b)、(b)。

索引下推

(name, age)为联合索引

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

没有索引下推优化时，会按照顺序把name第一个字为张的记录，一条条去回表；有了索引下推优化之后，会再根据age的值判断，如果不为10，则直接跳过，否则再去回表。
MySQL5.6之前还没有索引下推的优化。

索引的使用问题

普通索引和唯一索引的区别

• 查询；

select id from T where k=5

1. 普通索引：找到第一个满足条件的记录会继续查询，直到遇到第一个不满足条件的记录；
2. 唯一索引：找到第一个满足条件的记录后会停止检索；
3. 对比来说，性能几乎没有差别。

1. InnoDB每次都是读一页的数据，相同条件的数据大概率在同一页内，找到下一条记录的代价，只是一次指针寻找和一次计算；
2. 如果恰好不在同一页，但从平均性能差异来看，可以忽略不计。

• 更新，记录所在的页在内存中：

insert into T values (4, 400)

1. 普通索引：找到3和5之间的位置，直接插入；
2. 唯一索引：找到3和5之间的位置，判断没有冲突后插入。

相比较来说，唯一索引会耗费微小的CPU时间。

• 更新，记录所在的页在不内存中：

1. 普通索引：将更新记录在change buffer就可以了；
2. 唯一索引：需要将数据页读入内存，判断到没有冲突后插入这个值。

将数据从磁盘读入内存涉及到IO随机访问，是数据库成本最高的操作之一。使用change buffer没有这个操作，对更新性能提升很多。

• change buffer；

1. 如果数据在内存， 就更新内存；
2. 如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了；
3. 在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。

change buffer是可持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge：

1. 访问这个数据页会触发merge；
2. 系统后台线程会定期merge；
3. 数据库正常关闭的过程中，也会merge。

为什么使用change buffer：

1. 减少读磁盘，语句的执行速度得到明显的提升；
2. 数据读入内存会占用buffer pool，使用change buffer避免占用内存，提高内存使用率。

使用change buffer的限制：

1. 普通索引可以使用，因为唯一索引需要判断是否违反唯一性约束；
2. 数据页不在内存可以使用，因为数据页在内存的话，直接更新内存会更快；
3. change buffer用的是buffer pool的内存，可以通过参数innodb_change_buffer_max_size调整。

change buffer适合写多读少的系统，例如账单类、日志类系统。

merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

如果一个系统更新后马上查询，会导致频繁触发merge，随机IO的次数不会得到减少，同时增加了change buffer维护的代价，得不偿失。

• change buffer 和 redo log：

1. change buffer 也会写到redo log中；
2. 两者比较：

change buffer节省的是随机读磁盘的IO消耗；redo log节省了随机写磁盘的IO消耗。

• 如何选择。

1. 尽量使用普通索引；
2. 如果更新后面会经常伴随查询，最好关闭change buffer。

在使用机械硬盘时，change buffer 这个机制的收效是非常显著的。所以，当你有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

如何给字符串加索引

两种方式：

1. 不指定前缀长度，索引会包含真个字符串；
2. 定义字符串的一部分作为索引。

• 前缀索引优点：节省空间。
• 前缀索引缺点：

1. 需要额外关注字段的区分度；
2. 可能损失区分度；
3. 无法使用覆盖索引。

• 如何确定前缀索引的长度：关注区分度，区分度越高越好。使用如下方法确认长度：

mysql> select count(distinct email) as L from SUser;

mysql> select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如 5%。然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6。

• 字段前面区分度不高，如何使用前缀索引：

1. 倒序存储：使用最后几位作为索引；

查询时需要使用reverse函数。
mysql> select field_list from t where id_card = reverse('input_id_cartring');

2. 使用hash字段，增加一个整数字段作为hash值字段，同时在这个字段添加索引；

查询时也要加上本字段条件，因为hash值可能因为哈希冲突而一致。
mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

3. 两种方式比较：

1. 占用额外空间：倒序存储不会占用额外空间；hash字段方法会增加一个字段，从而要额外多需要4个字节的空间。然而一般来说，倒序存储时4个字节长度是不够的，所以两者在此方面差不多；
2. CPU消耗：倒序需要调用reverse函数；hash字段需要调用crc32函数。reverse函数相比于crc32函数来说消耗CPU资源会更小一些；
3. 查询效率：倒序使用的还是前缀索引，因此可能扫描多行；hash字段虽然可能有冲突，但概率较小，hash的效率更稳定一些。

为什么会选错索引

• 统计信息错误导致选错索引；

采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

innodb_stats_persistent 参数：
设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10；
设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

analyze table t 命令，可以用来重新统计索引信息。

• 索引选择异常如何处理：

1. force index；
2. 修改SQL语句，引导MySQL使用我们期望的索引；
3. 新建一个更合适的索引。

为什么有时候不走索引

• 条件字段函数操作；

如果对字段做了函数计算，就用不上索引了，这是MySQL的规定。
对字段进行函数操作，可能会破坏索引值的有序性，因此优化器放弃走索引树搜索。

• 隐式转换类型；

在MySQL的转换规则：如果字符串和数字比较，会将字符串转成数字。

mysql> select * from tradelog where tradeid=110717;

tradeid字段类型为varchar，因此，上面的SQL语句相当于：

mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;

对条件字段进行了函数操作，因此优化器弃走索引树搜索。

• 隐式字符编码转换。

mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /*语句Q1*/

会先从tradelog查询id = 2的数据，再根据id = 2的这行数据中的tradeid字段，查询trade_detail 表。因此tradelog 是驱动表，trade_detail 是被驱动表。

假设tradelog的字符集是utf8mb4，trade_detail的字符集是utf-8，那么在查询trade_detail时不会使用索引。
因为此时查询trade_detail的语句等价于：

select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value; 

此时条件字段使用了函数。

除了字符集问题，在连接过程中，只要被驱动表的索引字段加了函数操作，都会导致对被驱动表全表扫描。

解决：

1. 把trade_detail的字符集改为utf8mb4；
2. 换一种写法：

mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;