MySQL基础知识——MySQL索引

深入浅出索引

索引的意义

索引的意义：在大量数据中，加速访问少量特定数据；

使用索引的前提条件：

1）索引块数量小于数据块数量；

2）索引键有序，故可以使用二分查找等高效的查找方式；（提升查找效率）

3）索引文件足够小，能够长期存放在内存缓冲区中；（减少磁盘IO）

注：当查询数据量超过一定比例时，全表扫描可能比走索引更高效。

索引的分类

稠密索引&稀疏索引：数据文件中的记录能否在索引文件中找到一一映射；

主索引&辅助索引：索引能否决定记录在数据文件中的位置；

多级索引：索引的索引，索引之上再建索引；

倒排索引：（词条，文档）的关系索引；

索引数据结构

索引的出现是为了提高查询效率，实现索引的数据结构有很多，这里主要介绍三种常见、也比较简单的数据结构，分别是哈希表、有序数组和搜索树。

哈希表

哈希表是一种以键-值（key-value）存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。

不可避免地，多个key值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。

假设，你现在维护着一个身份证信息和姓名的表，需要根据身份证号查找对应的名字，这时对应的哈希索引的示意图如下所示：

图中， User2和User4根据身份证号算出来的值都是N，但没关系，后面还跟了一个链表。假设，这时候你要查ID_card_n2对应的名字是什么，处理步骤就是：首先，将ID_card_n2通过哈希函数算出N；然后，按顺序遍历，找到User2。

注：哈希表适合等值查询场景，不适合范围查询1。

有序数组

有序数组在等值查询和范围查询场景中的性能就都非常优秀。

还是上面这个根据身份证号查名字的例子，如果我们使用有序数组来实现的话，示意图如下所示：

这里我们假设身份证号没有重复，这个数组就是按照身份证号递增的顺序保存的。这时候如果你要查ID_card_n2对应的名字，用二分法就可以快速得到，这个时间复杂度是O(log(N))。

同时很显然，这个索引结构支持范围查询。你要查身份证号在[ID_card_X, ID_card_Y]区间的User，可以先用二分法找到ID_card_X（如果不存在ID_card_X，就找到大于ID_card_X的第一个User），然后向右遍历，直到查到第一个大于ID_card_Y的身份证号，退出循环

注：在等值和范围查询都很优秀，但更新成本高，因而仅适合静态存储引擎（数据量不会动态改变的场景，无insert和delete）；

二叉搜索树

还是上面根据身份证号查名字的例子，如果我们用二叉搜索树来实现的话，示意图如下所示：

二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。这样如果你要查ID_card_n2的话，按照图中的搜索顺序就是按照UserA -> UserC -> UserF -> User2这个路径得到。这个时间复杂度是O(log(N))。

注：为了维持O(log(N))的查询复杂度，需要保持这棵树是平衡二叉树（如果不保持这棵树是平衡二叉树，则二叉树有可能退化成链表，时间复杂度就变成了O(N)）。为了做这个保证，更新的时间复杂度也是O(log(N))。

问：既然二叉树是搜索效率最高的，为什么大多数的数据库存储却并不使用二叉树？

你可以想象一下一棵100万节点的平衡二叉树，树高20。一次查询可能需要访问20个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要10 ms左右的寻址时间。也就是说，对于一个100万行的表，如果使用二叉树来存储，单独访问一个行可能需要20个10 ms的时间，这个查询可真够慢的。

注：InnoDB将数据划分为若干个页，页是磁盘与内存交互的基本单位。在InnoDB存储引擎中，B+数的每个节点通常对应一个页。因此，一个高度为20的二叉树，查询一条数据可能需要20次IO。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N叉”树。（N叉数可以降低树的高度，以减少单次查询的磁盘IO次数）

InnoDB的索引模型

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的， InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。

每一个索引在InnoDB里面对应一棵B+树。

假设，我们有一个主键列为ID的表，表中有字段k，并且在k上有索引。这个表的建表语句是：

create table T( 
    id int primary key, 
    k int not null, 
    name varchar(16), 
    index (k)
) engine=InnoDB;

create table T( id int primary key, k int not null, name varchar(16), index (k) ) engine=InnoDB;

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

问1：什么是主键索引？什么是非主键索引？

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

2）非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondaryindex）。

问2：基于主键索引和普通索引的查询有什么区别？

1）如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树。

2）如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

问3：什么是回表？

通过二级索引进行查询和计算后，需要重新到表中查询完整数据，以便满足投影字段的需求，这个过程叫做回表。

页分裂和页合并

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，如果插入新的行ID值为700，则只需要在R5的记录后面插入一个新记录。如果新插入的ID值为400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。

页分裂：如果R5所在数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为分裂。（在这种情况下，性能自然会受到影响。除了性能外，也分裂还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%）

页合并：当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。

思考：结合上面的例子，我们来分析下哪些场景下应该使用自增主键，使用自增主键有什么好处？

1）有了自增主键，在插入新记录的时候可以不指定ID的值，系统会获取当前ID最大值加1作为下一条记录的ID值。

2）自增主键的插入数据模式，每插入一条新记录，都是追加操作，都不涉及到挪动其它记录，也不会触发叶子节点的分裂。（提升性能）

3）因为非主键索引的叶子节点上都是主键的值，所以主键长度越小，非主键索引的叶子节点就越小，非主键索引占用空间也就越小。如，自增主键为整型，相比字符串等类型，节省更过空间。（节约存储空间）

问：有没有什么场景适合用业务字段直接做主键呢？

有，如只有一个索引，且该索引必须是唯一索引。（由于没有其它索引，索引也就不用考虑其它索引的叶子节点大小的问题）

思考：对于上面例子中的InnoDB表T，如果要重建索引K，你的两个SQL语句可以这么写：

alter table T drop index k; 
alter table T add index(k);

如果你要重建主键索引，也可以这么写：

alter table T drop primary key; 
alter table T add primary key(id);

对于上面两个索引重建做法是否合适？

答：重建索引k的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，可用这个语句代替：

alter table T engine=InnoDB;

注：在做查询时，尽量使用主键索引，以减少回表，提升性能。

二级索引搜索过程

在介绍二级索引搜索过程前，先来了解两个概念：

1）回表：通过二级索引检索数据过程中，回到主键索引树搜索，获取所需字段的过程，我们称为回表；因为所需字段只在主键索引上有；

2）覆盖索引：查询的字段均为索引字段，则不需要回表操作；通过冗余的索引字段，减少回表操作，是常用的优化手段；

普通二级索引

在下面这个表T中，如果我执行 select * from Twhere k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？

-- 创建表T，写入6行数据
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’);

现在，我们一起来看看这条SQL查询语句的执行流程：

在k索引树上找到k=3的记录，取得 ID = 300；
再到ID索引树查到ID=300对应的R3；（回表）
在k索引树取下一个值k=5，取得ID=500；
再回到ID索引树查到ID=500对应的R4；（回表）
在k索引树取下一个值k=6，不满足条件，循环结束。

由上述流程可知，这个查询过程进行了3次搜索，2次回表。

覆盖索引

如果执行的语句是 select ID from Twhere k between 3 and 5，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引k已经“覆盖了”我们的查询需求，我们称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

现在，我们一起来看看这条SQL查询语句的执行流程：

在k索引树上找到k=3的记录，取得 ID = 300；
在k索引树取下一个值k=5，取得ID=500；
在k索引树取下一个值k=6，不满足条件，循环结束。

由上述流程可知，这个查询过程进行了3次搜索，0次回表。

注：在引擎内部使用覆盖索引在索引k上其实读了三个记录， R3~R5（对应的索引k上的记录项），但是对于MySQL的Server层来说，它就是找引擎拿到了两条记录，因此MySQL认为扫描行数是2。

联合索引&最左匹配原则

思考：在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？

假设这个市民表的定义是这样的：

CREATE TABLE `tuser` ( 
    `id` int(11) NOT NULL,
    `id_card` varchar(32) DEFAULT NULL, 
    `name` varchar(32) DEFAULT NULL, 
    `age` int(11) DEFAULT NULL, 
    `ismale` tinyint(1) DEFAULT NULL, 
    PRIMARY KEY (`id`),
    KEY `id_card` (`id_card`), 
    KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB;

如果有一个高频请求，要根据市民的身份证号查询他的姓名，则可使用该联合索引。可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

问：如果现在要按照市民的身份证号去查他的家庭地址呢？虽然这个查询需求在业务中出现的概率不高，但总不能让它走全表扫描吧？反过来说，单独为一个不频繁的请求创建一个（身份证号，地址）的索引又感觉有点浪费。应该怎么做呢？

答：B+树索引结构，可以利用索引的“最左前缀”，来定位记录。

为了直观地说明这个概念，我们用（name， age）这个联合索引来分析。

从上图可以看到，索引项是按照索引定义里面出现的字段顺序排序的。

当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到ID4，然后向后遍历得到所有需要的结果。

可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

注：对于(a,b,c)3个字段组成的联合索引，仅有(a,b,c),(a,b),(a)会走索引，即最左前缀匹配。

问：在建立联合索引的时候，如何安排索引内的字段顺序？

1）减少索引维护：如果调整顺序，可以少维护一个索引，则优先考虑该顺序；

2）占用空间考量：如果既有联合查询，又有基于a、b各自的查询呢？查询条件里面只有b的语句，是无法使用(a,b)这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护(a,b)、(b) 这两个索引。这时候，我们要考虑的原则就是空间了。若a比b字段占用空间大，则可以建立(a,b)，(b)；（此时也能减少单独使用b索引需要检索的数据量，因为联合索引(a,b)也分担了一部分）

3）减少前缀匹配数据量：若根据a,b建立联合索引，若a字段对于数据的区分度较大，则使用(a,b)顺序；

索引下推优化

索引下推优化（ICP）：在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数；（MySQL >= 5.6）

还是以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是10岁的所有男孩”。那么， SQL语句是这么写的：

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

上述语句执行流程分以下两种情况：

1）无索引下推执行流程：从ID3开始一个个回表。到主键索引上找出数据行，再对比字段值。（MySQL 5.6之前）

注：无索引下推执行过程中，回表4次。

2）索引下推执行流程：遍历所有以’张’开头的记录，在(name,age)索引内部就判断了age是否等于10，对于不等于10的记录，直接过滤掉，以减少回表次数。（MySQL 5.6之后）

注：索引下推执行过程中，回表2次。

思考

DBA小吕在入职新公司的时候，就发现自己接手维护的库里面，有这么一个表，表结构定义类似这样的：

CREATE TABLE `geek` ( 
    `a` int(11) NOT NULL, 
    `b` int(11) NOT NULL, 
    `c` int(11) NOT NULL, 
    `d` int(11) NOT NULL, 
    PRIMARY KEY (`a`,`b`), 
    KEY `c` (`c`),
    KEY `ca` (`c`,`a`),
    KEY `cb` (`c`,`b`) 
) ENGINE=InnoDB;

公司的同事告诉他说，由于历史原因，这个表需要a、 b做联合主键，这个小吕理解了。但是，学过本章内容的小吕又纳闷了，既然主键包含了a、 b这两个字段，那意味着单独在字段c上创建一个索引，就已经包含了三个字段了呀，为什么要创建“ca”“cb”这两个索引？

同事告诉他，是因为他们的业务里面有这样的两种语句：

select * from geek where c=N order by a limit 1; 
select * from geek where c=N order by b limit 1;

问：这位同事的解释对吗，为了这两个查询模式，这两个索引是否都是必须的？为什么呢？

答：ca不需要，cb需要；

索引组织表是根据索引字段排序好的。

比如主键a，b的聚簇索引组织顺序相当于 order by a,b ，也就是先按a排序，再按b排序，c无序；

上面第一条SQL，实际是先按c排序，再按a排序；可以走索引c，然后走主键索引ab，因而ca不是必须的；

参考：MySQL45讲：04.深入浅出索引（上）、05.深入浅出索引（下）

普通索引和唯一索引应该怎么选择

在不同的业务场景下，应该选择普通索引，还是唯一索引？下面对其进行分析...

Change Buffer

InnoDB的数据是按页为单位读写的。当读一条记录时，并不是将这个记录本身从磁盘读出来，而是以页为单位，将记录所在的页整体读入内存。页大小默认是16KB，存放页的内存区域称为InnoDB Buffer Pool；

Change Buffer是InnoDB Buffer Pool中的一块内存区域，因此不能无限增大。change buffer的大小，可以通过 innodb_change_buffer_max_size 参数来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

问1：Change Buffer有什么用处？

可以提高更新性能。

当需要更新一个数据页时，如果数据页在内存中就直接更新；反之，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了；在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作，以保证数据逻辑的正确性。

显然，如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率。

问2：Change Buffer数据会不会被持久化？

change buffer在内存中有拷贝，也会被写入到磁盘上，放在系统表空间文件中。

问3：什么是Change Buffer的merge操作？

将change buffer中的操作应用到旧数据页，得到新数据页的过程称为merge。

Merge的触发条件包括：

1）访问这个数据页。

2）后台线程会定期merge。

3）正常关闭（shutdown）数据库。

问4：使用Change Buffer有哪些意义？

1）减少磁盘IO：将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。

2）节约内存：减少读入内存的数据页，仅记录变更，提高内存利用率。

问5：Change Buffer有哪些使用约束？

仅普通索引可以使用。唯一索引所有的更新操作都要先判断这个操作是否违反唯一性约束，因而需要将数据页读入内存。

问6：Change Buffer的使用场景有哪些？

只有普通索引可以使用。

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入(4,400)这个记录，就要先判断现在表中是否已经存在k=4的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。

因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。

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

问7：普通索引的所有场景，使用change buffer都可以起到加速作用么？

仅适用于读多写少的场景。

对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。所以，对于这种业务模式来说， change buffer反而起到了副作用。

Change Buffer补充：

1）change buffer和数据页一样，也是物理页的一个组成部分，数据结构也是一颗B+树，这棵B+树放在系统表空间中，默认ibdata1中；

2）change buffer写入系统表空间机制和普通表的脏页刷新到磁盘是相同的机制——Checkpoint机制；

3）之所以change buffer要写入系统表空间，是为了保证数据的一致性，change buffer做修改时需要写redo，在做恢复时需要根据redo来恢复change buffer，若是不把change buffer写入系统表空间，也就是不进行持久化，那么在change buffer写入内存后掉电，则无法进行数据恢复。这样也会导致索引中的数据和相应表的相应列中的数据不一致；

4）change buffer 写入到了系统表空间，purge的时候会先查询change buffer里对应的记录（即检查change buffer中的记录更新操作是否done），然后进行purge，因为change buffer B+树的key是表空间ID，所以查询根据表空间ID查询change buffer会很快；

5）merge的执行过程：

从磁盘读入数据页到内存（老版本的数据页）；
从change buffer里找出这个数据页的change buffer 记录（可能有多个），依次应用，得到新版数据页；
写redo log，这个redo log包含了数据的变更和change buffer的变更；
merge流程（新版本数据页）并不会直接写磁盘，而是等待变更后的内存页通过刷脏后落盘；

注1：change buffer和merge后的新版本数据页都会被写到redo log。change buffer写入redo log主要是为了防止change buffer写入内存后掉电；merge后的新版本数据页写入redo log主要是为了保证数据的一致性。

注2：change buffer节省的是随机读磁盘的IO次数，写redo log（孔乙己粉板）节省的是随机写IO次数。

普通索引和唯一索引读写性能分析

假设有如下表结构：

-- 表T结构
create table T(
   id int primary key, 
   k int not null, 
   name varchar(16),
   index (k)
) engine=InnoDB;

查询操作

SQL语句：select id from T where k=5;

若k为普通索引，则查找到满足条件的第一个记录(5,500)后，需要继续查找下一个记录，直到碰到第一个不满足k=5条件的记录；
若k为唯一索引，由于索引定义了唯一性，则查找到第一个满足条件的记录后，就会停止继续检索；

注：对于非唯一索引来说，会多做一次查找和计算；但性能影响很小；

问：为什么非唯一索引多做一次查找和计算，性能影响很小？

因为引擎是按页读写的，所以说，当找到k=5的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。

当然，如果k=5这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。

但是，对于整型字段，一个数据页可以放近千个key，因此出现这种情况的概率会很低。所以，计算平均性能差异时，仍可以认为这个操作成本对于现在的CPU来说可以忽略不计。

更新操作

SQL语句：insert into T values( 4, 400, xxx );

场景1：假设更新的目标页在内存中；

若k为普通索引，找到3和5之间的位置，插入这个值，语句执行结束；
若k为唯一索引，找到3和5之间的位置，若没有冲突，则插入这个值，语句执行结束；（唯一索引的所有更新操作都需要把数据页更新到内存中，以判断当前操作是否违反唯一性约束）

场景2：更新的目标也不在内存中；

若k为普通索引，将更新记录在change buffer，语句执行结束；
若k为唯一索引，将数据页读入内存，若没有冲突，则插入这个值，语句执行结束；

唯一索引更新过程中，需要保证更新操作的唯一性，因此需要把数据页读入内存；而对于普通索引而言，无需把数据页读入内存，所以普通索引的更新性能要好于唯一索引；

注：基于普通索引和唯一索引的特性，在使用二者时需注意一下两点：

对于不需要DB保证唯一性约束的二级索引，尽可能使用普通索引；
对于更新数据后，要马上读取数据的场景，要关闭change buffer。而在其它情况下change buffer都能提升性能。

Change Buffer与Redo Log（WAL）的区别

Change Buffer与Redo Log（主要是指WAL机制）的区别主要表现在两个方面：更新、读取，下面分别对其展开介绍。

更新

更新操作：insert into t(id,k) values(id1,k1),(id2,k2);

假设查找到位置后，k1所在的数据页在内存(InnoDB buffer pool)中，k2所在的数据页不在内存中；下图是带change buffer的更新状态图。

分析这条更新语句，会发现它涉及4个部分：

内存
redo log（ib_log_fileX）
数据表空间（t.ibd）
系统表空间（ibdata1）

更新过程如下：

1）Page 1在内存中，直接更新内存；（写内存）

2）Page 2不在内存中，则在change buffer中记录操作；（写内存）

3）将上述两个动作记入redo log中；（两次操作合在一起写了一次磁盘，而且是顺序写）

同时，图中的两个虚线箭头，是后台操作，不影响更新的响应时间。

问1：更新过程步骤2操作，change buffer一开始是写内存的，如果此时掉电重启，会不会导致change buffer丢失呢？

答：虽然是只更新内存，但是在事务提交的时候，把change buffer的操作也记录到redo log里了，所以崩溃恢复的时候， change buffer也能找回来。（change buffer的落盘机制与数据页相似，也是 No Stale + No Force，因而未提交的事务，对应的change buffer回滚；已提交的事务对应的change buffer通过binlog + redo log恢复。）

问2：No Stale + No Force是什么？

No Steal + No Force是一种数据库管理策略的组合，具体涉及事务处理时数据更新到磁盘的时机和方式。在这种策略组合下：

No Steal：指的是不允许一个未提交（uncommitted）的事务将修改更新到磁盘。这意味着，在事务提交之前，其所做的任何修改都不会被持久化到磁盘上。这种策略有助于确保数据的一致性，因为如果在事务提交之前系统崩溃，未提交的修改不会影响到磁盘上的数据。
No Force：表示事务在提交（committed）之后可以不立即将更新持久化到磁盘。这种策略允许系统缓存多个更新，然后批量地将它们持久化到磁盘，从而减少了磁盘操作的次数，提高了写操作的效率。然而，这也带来了风险，因为如果在批量持久化之前系统崩溃，已经提交的事务数据可能会丢失。

结合这两种策略，“No Steal + No Force”提供了一种权衡数据一致性和系统性能的方案。它确保了未提交的事务修改不会被写入磁盘，同时允许已提交的事务修改在稍后的时间点进行批量持久化。然而，这种策略也增加了数据丢失的风险，特别是在系统崩溃或故障的情况下。因此，在采用这种策略时，通常需要配合其他机制（如日志记录）来确保数据的完整性和可恢复性。

注：No Steal + No Force更多关注于数据更新的时机和方式，而两阶段提交则关注于如何在分布式系统中保证事务的一致性。

读取

读取操作：select * from t where k in (k1,k2);

假设读语句发生在更新语句后不久，内存中的数据都还在，未刷脏；

查询过程如下：

1）读Page 1的时候，直接从内存返回。因为WAL之后如果读数据，不一定要读盘，可能直接从内存返回结果；

2）读Page 2的时候，需要把Page 2从磁盘读入内存中，然后应用change buffer里面的操作日志，生成一个正确的版本并返回结果；

上述过程即为page页的Merge过程；

注： merge后的内存页并不会直接写磁盘，而是等待后续的刷脏落盘；

问：Change Buffer和Redo Log在提升更新性能上的收益区别是什么？

redo log（WAL）主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的是随机读磁盘的IO消耗。

参考：MySQL45讲：09.普通索引和唯一索引，应该怎么选择？

MySQL为什么有时候会选错索引

优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。

思考

假设有表结构：

-- T表结构：
CREATE TABLE `t` (
    `id` int(11) NOT NULL,
    `a` int(11) DEFAULT NULL,
    `b` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `a` (`a`),
    KEY `b` (`b`)
) ENGINE=InnoDB;

往表t中插入10万行记录，取值按整数递增，即：(1,1,1)， (2,2,2)， (3,3,3) 直到 (100000,100000,100000)。

插入数据的存储过程如下：

-- 插入数据存储过程：
delimiter ;;
create procedure idata()
begin
    declare i int;
    set i=1;
    while(i<=100000)do
        insert into t values(i, i, i);
        set i=i+1;
    end while;
end;;
delimiter ;

-- 调用存储过程
call idata();

接下来，我们分析一条SQL语句：

mysql> select * from t where a between 10000 and 20000;

单独执行上述语句时，查询执行计划如下：

从执行计划来看，走了索引a，符合预期。

接着我们再做如下操作：

这里， session A的操作你已经很熟悉了，它就是开启了一个事务。随后， session B把数据都删除后，又调用了 idata这个存储过程，插入了10万行数据。

这时候， session B的查询语句select * from t where a between 10000 and 20000就不会再选择索引a了。可以通过慢查询日志（slow log）来查看一下具体的执行情况。

为了说明优化器选择的结果是否正确，我增加了一个对照，即：使用force index(a)来让优化器强制使用索引a。

在Session B中执行如下三条SQL语句：

set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/

第一句，是将慢查询日志的阈值设置为0，表示这个线程接下来的语句都会被记录入慢查询日志中；
第二句， Q1是session B原来的查询；
第三句， Q2是加了force index(a)来和session B原来的查询语句执行情况对比。

这三条SQL语句执行完成后的慢查询日志结果如下：

可以看到， Q1扫描了10万行，显然是走了全表扫描，执行时间是40毫秒。 Q2扫描了10001行，执行了21毫秒。也就是说，我们在没有使用force index的时候， MySQL用错了索引，导致了更长的执行时间。

这个例子对应的是我们平常不断地删除历史数据和新增数据的场景。这时， MySQL竟然会选错索引，是不是有点奇怪呢？下面我们就来看一下优化器是如何选择索引的。

问：begin 和 start transaction with consistent snapshot 的区别是什么？

创建一致性视图的时机不同。

1）begin 是在第一次执行 SELECT 时创建一致性视图。

2）start transaction with consistent snapshot 是在该语句执行后，立即创建一致性视图。

优化器的逻辑

优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。

问：扫描行数在优化器选择索引时发挥着怎样作用？

在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

问：扫描行数是怎么判断的？

MySQL在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。

这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

可以使用 show index from xxx 方法，看到一个索引的基数。如下图所示，就是表t的show index的结果。虽然这个表的每一行的三个字段都是一样的，但是在统计信息中，这三个索引的基数值并不同，而且其实都不准确。

MySQL是怎样得到索引的基数的呢？

下面简单介绍一下MySQL采样统计的方法。

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

在MySQL中，存储索引统计的方式（N、M值的选择）是如何设置的？

可以通过设置参数innodb_stats_persistent的值来选择：

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

由于是采样统计，所以不管N是20还是8，这个基数都是很容易不准的。从上图可知，索引统计值（cardinality列，即扫描行数估计值）虽然不够准确，但大体上还是差不多的，那就说明选错索引还有别的原因。

注1：扫描行数不同于select count，是一个统计值，因而并不是非常精确的；

主2：主键索引的扫描行数直接通过show table status中表的行数（Rows）来估计，并不是通过上述采样方式获取的；

问：为什么要采样统计呢？

因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择“采样统计”。

接下来，我们再一起看看，在Session B中优化器预估的这两个语句的扫描行数是多少。

注：rows这个字段表示的是预计扫描行数。

其中， Q1的结果还是符合预期的， rows的值是104620（和采样统计的扫描行数接近）；但是Q2的rows值是37116，偏差就大了。而上图中我们用explain命令和慢日志记录看到的实际扫描rows是只有10001行，是这个偏差误导了优化器的判断。

问：上述优化器预估的两个语句的扫描行数分别为104620、37116，优化器为什么放着扫描37000行的执行计划不用，却选择了扫描行数是100000的执行计划呢？

这是因为，如果使用索引a，每次从索引a上拿到一个值，都要回到主键索引上查出整行数据（即考虑了回表的代价），这个代价优化器也要算进去的。而如果选择扫描10万行，是直接在主键索引上扫描的，没有额外的代价。

优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。当然，从执行时间看来，这个选择并不是最优的。

问：对于Q2的执行计划，查询1w行数据，查询计划得出的扫描行数为什么是3.7w?

session B中先delete 10w行数据，再通过call idata()插入了10万行数据；由于session A开启了一致性读，且事务并未提交，则之前插入的10万行数据不能删除。所以之前的10w行数据每一行都有两个版本，旧版本是delete之前的数据，新版本是标记为deleted的数据。一共要扫描3w行数据；再加上到主键索引回表的数据，接近4w行；

索引选择异常和处理

其实大多数时候优化器都能找到正确的索引，但偶尔你还是会碰到我们上面举例的这两种情况：原本可以执行得很快的SQL语句，执行速度却比你预期的慢很多，你应该怎么办呢？

如果索引选择异常，可通过如下几种策略更正：

1）采用 force index 强行选择一个索引。该策略有如下缺点：

格式不优美；
如果索引改了名字，这个语句也得改比较麻烦；
如果以后迁移到别的数据库，这个语法还可能不兼容；

2）修改SQL语句，引导MySQL使用我们期望的索引。一般不建议使用，使用条件比较苛刻。

3）新建一个更合适的索引，来提供给优化器做选择或删掉误用的索引。

4）执行analyze table xxx，更新统计值，重新分析表，使得统计值更加准确。（由于索引统计信息不准确导致选错索引，可以使用该方法解决）

参考：MySQL45讲：10.MySQL为什么有时候会选错索引？

怎么给字符串字段加索引

前缀索引

MySQL是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

使用前缀索引的优缺点：

1）优点：能够节省索引占用空间。

2）缺点：

对于前缀索引区分度不高的场景，会增加扫描次数，降低索引性能。
使用前缀索引（即便使用前缀索引截断了完整信息），则不能使用覆盖索引。因为，系统并不确定前缀索引的定义是否阶段了完整信息。

下面举例说明，假设存在如下表结构：

-- SUser表结构
create table SUser(
   ID bigint unsigned primary key,
   email varchar(64), 
   ... 
)engine=innodb;

比如，这两个在email字段上创建索引的语句：

mysql> alter table SUser add index index1(email);
或 
my sql> alter table SUser add index index2(email(6));

第一个语句创建的index1索引里面，包含了每个记录的整个字符串；而第二个语句创建的index2索引里面，对于每个记录都是只取前6个字节。

问：这两种不同的定义在数据结构和存储上有什么区别呢？

index1索引和index2索引示意图如下：

从图中你可以看到，由于email(6)这个索引结构中每个邮箱字段都只取前6个字节（即： zhangs），所以占用的空间会更小，这就是使用前缀索引的优势。

但，这同时带来的损失是，可能会增加额外的记录扫描次数。

接下来，我们再看看下面这个语句，在这两个索引定义下分别是怎么执行的。

select id,name,email from SUser where email='zhangssxyz@xxx.com';

对于普通索引index1执行流程如下：

从index1索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得ID2的值；
到主键上查到主键值是ID2的行，判断email的值是正确的，将这行记录加入结果集；
取index1索引树上刚刚查到的位置的下一条记录，发现已经不满足email='zhangssxyz@xxx.com’的条件了，循环结束；

只需要回主键索引取一次数据，所以系统认为只扫描了一行。

对于前缀索引index2执行流程如下：

从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是ID1；
到主键上查到主键值是ID1的行，判断出email的值不是’zhangssxyz@xxx.com’，这行记录丢弃；
取index2上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出ID2，再到ID索引上取整行然后判断，这次值对了，将这行记录加入结果集；
重复上一步，直到在idxe2上取到的值不是’zhangs’时，循环结束。

在这个过程中，要回主键索引取4次数据，也就是扫描了4行。

但是，对于这个查询语句来说，如果你定义的index2不是email(6)而是email(7），也就是说取email字段的前7个字节来构建索引的话，即满足前缀’zhangss’的记录只有一个，也能够直接查到ID2，只扫描一行就结束了。

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

问：当要给字符串创建前缀索引时，有什么方法能够确定我应该使用多长的前缀呢？

答：在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

在建立索引前，可以使用下面的语句，计算这个列上有多少个不同的值：

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

然后，依次选取不同长度的前缀来看这个值，比如我们要看一下4~7个字节的前缀索引，可以用这个语句：

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）直接创建完整索引。该方式比较占用空间。

2）前缀索引。节省空间，但会增加查询扫描次数，并且不能使用覆盖索引。（但如果建立前缀索引的对象是身份证号时（一般同一地区身份证号的前几位相同），则需要创建长度较长的前缀索引，才能满足区分度的要求。但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。）

3）倒叙索引。如果字段尾部的值区分度比较高，可考虑采用倒叙索引，如身份证号。倒叙索引查询语句如下所示：

mysql> select field_list from t where id_card = reverse('input_id_card_string');

注：不要忘记使用count(distint)方法验证。

4）使用hash字段。可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

# 创建身份证号的效验码字段
mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

然后每次插入新记录的时候，都同时用crc32()这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过crc32()函数得到的结果可能是相同的，所以你的查询语句where部分要判断id_card的值是否精确相同。

mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string’;

如此一来，索引的长度变成了4个字节，比原来小多了。

问：倒叙索引和使用hash字段有哪些异同点？

1）相同点：都不支持范围查询。

2）区别：

占用空间：倒序存储方式在主键索引上，不会消耗额外的存储空间，而hash字段方法需要增加一个字段。当然，倒序存储方式使用4个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个hash字段也差不多抵消了。
CPU消耗：倒序方式每次写和读的时候，都需要额外调用一次reverse函数，而hash字段的方式需要额外调用一次crc32()函数。如果只从这两个函数的计算复杂度来看的话，reverse函数额外消耗的CPU资源会更小些。
查询效率：使用hash字段方式的查询性能相对更稳定一些。因为crc32算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数；

参考：MySQL45讲：11.怎么给字符串字段加索引？

为什么逻辑相同的SQL语句，性能却差异巨大

条件字段函数操作

假设你现在维护了一个交易系统，其中交易记录表tradelog包含交易流水号（tradeid）、交易员id（operator）、交易时间（t_modified）等字段。为了便于描述，我们先忽略其他字段。这个表的建表语句如下：

CREATE TABLE tradelog
    `id` int(11) NOT NULL,
    tradeid` varchar(32) DEFAULT NULL,
    operator' int(11) DEFAULT NULL,
    `t_modified` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `tradeid` (`tradeid`),
    KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

假设，现在已经记录了从2016年初到2018年底的所有数据，运营部门有一个需求是，要统计发生在所有年份中7月份的交易记录总数。这个逻辑看上去并不复杂，你的SQL语句可能会这么写：

select count(*) from tradelog where month(t_modified)=7;

由于t_modified字段上有索引，于是你就很放心地在生产库中执行了这条语句，但却发现执行了特别久，才返回了结果。

MySQL规定：如果对字段做了函数计算，就无法使用索引进行快速定位了。（但仍可能走全索引扫描）

问1：为什么条件是where t_modified='2018-7-1’的时候可以用上索引，而改成where month(t_modified)=7的时候就不行了？

答：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

下面是这个t_modified索引的示意图。方框上面的数字就是month()函数对应的值。

如果你的SQL语句条件用的是where t_modified='2018-7-1’的话，引擎就会按照上面绿色箭头的路线，快速定位到 t_modified='2018-7-1’需要的结果。

实际上， B+树提供的这个快速定位能力，来源于同一层兄弟节点的有序性。

但是，如果计算month()函数的话，你会看到传入7的时候，在树的第一层就不知道该怎么办了。

在这个例子中，优化器并不是要放弃使用这个索引，而是放弃了树搜索功能。优化器可以选择遍历主键索引，也可以选择遍历索引t_modified，优化器对比索引大小后发现，索引t_modified更小，遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引t_modified。

使用explain命令，查看一下这条SQL语句的执行结果：

key="t_modified"表示的是，使用了t_modified这个索引；我在测试表数据中插入了10万行数据， rows=100335，说明这条语句扫描了整个索引的所有值； Extra字段的Using index，表示的是使用了覆盖索引。

也就是说，由于在t_modified字段加了month()函数操作，导致了全索引扫描。

为了能够用上索引的快速定位能力，我们就要把SQL语句改成基于字段本身的范围查询。按照下面这个写法，优化器就能按照我们预期的，用上t_modified索引的快速定位能力了。

select count(*) from tradelog where (t_modified >= '2016-7-1' and t_modified <'2016-8-1') or (t_modified >= '2017-7-1' and t_modified < '2017-8-1') or (t_modified >= '2018-7-1' and t_modified < '2018-8-1');

问2：前面提到如果函数改变了索引值的有序性，MySQL无法再使用索引快速定位功能。那么，如果函数不改变索引值的有序性，MySQL是否能够使用索引快速定位功能？

答：不能。如，对于select * from tradelog where id + 1 = 10000这个SQL语句，这个加1操作并不会改变有序性，但是MySQL优化器还是不能用id索引快速定位到9999这一行。所以，需要你在写SQL语句的时候，手动改写成 where id = 10000 -1才可以。（说明MySQL并没有对id + 1 = 10000进行RBO优化）

由此得出，上述所说的条件字段函数操作，此处的函数是广义函数，即where条件中的字段不能参与任何计算。

隐式类型转换

一起看一下这条SQL语句：

select * from tradelog where tradeid=110717;

交易编号tradeid这个字段上，本来就有索引，但是explain的结果却显示，这条语句需要走全表扫描。你可能也发现了， tradeid的字段类型是varchar(32)，而输入的参数却是整型，所以需要做类型转换。

问1：数据类型转换规则是什么？数据类型转换规则那么多，记不住，该怎么办？

这里有一个简单的方法，看 select “10” > 9的结果：

1）如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是1。

2）如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是0。（MySQL中两个字符串比大小是比较第一次字符）

验证结果：

从图中可知， select “10” > 9返回的是1，所以你就能确认MySQL里的转换规则了：在MySQL中，字符串和数字做比较的话，是将字符串转换成数字。

问2：为什么有数据类型转换，就需要走全索引扫描？

答：因为有些数据类型转换会导致条件字段有函数操作，所以需要走全索引扫描。

示例如下：

这时，你再看这个全表扫描的语句：

select * from tradelog where tradeid=110717;

就知道对于优化器来说，这个语句相当于：

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

也就是说，这条语句触发了我们上面说到的规则：对索引字段做函数操作，优化器会放弃走树搜索功能。

问3：id的类型是int，如果执行下面这个语句，是否会导致全表扫描呢？

select * from tradelog where id = "83126";

答：不会，如上述验证，字符串"83126"会转化为83126，所以走索引扫描。

隐式字符编码转换

假设系统里还有另外一个表trade_detail，用于记录交易的操作细节。为了便于量化分析和复现，我往交易日志表tradelog和交易详情表trade_detail这两个表里插入一些数据。

-- tradelog表结构
CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid`varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified`datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid`(`tradeid`),
  KEY `t_modified`(`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- tradelog表插入数据
insert into tradelog values(1, 'aaaaaaaa', 1000, now());
insert into tradelog values(2, 'aaaaaaab', 1000, now());
insert into tradelog values(3, 'aaaaaaac', 1000, now());

-- trade_detail表结构
CREATE TABLE `trade_detail`(
  `id` int(11) NOT NULL,
  `tradeid`varchar(32) DEFAULT NULL,
  `trade_step`int(11) DEFAULT NULL, /*操作步骤*/
  `step_info`varchar(32) DEFAULT NULL, /*步骤信息*/
  PRIMARY KEY (`id`),
  KEY `tradeid`(`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- trade_detail表插入数据
insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');
insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');

这时候，如果要查询id=2的交易的所有操作步骤信息， SQL语句可以这么写：

select d.* from tradelog l, trade_detail d where d.tradeid = l.tradeid and l.id = 2;

一起来看下这个结果：

1）第一行显示优化器会先在交易记录表tradelog上查到id=2的行，这个步骤用上了主键索引， rows=1表示只扫描一行。

2）第二行key=NULL，表示没有用上交易详情表trade_detail上的tradeid索引，进行了全表扫描。

在这个执行计划里，是从tradelog表中取tradeid字段，再去trade_detail表里查询匹配字段。因此，我们把tradelog称为驱动表，把trade_detail称为被驱动表，把tradeid称为关联字段。

explain结果表示的执行流程：

上图中：

第1步，是根据id在tradelog表里找到L2这一行。
第2步，是从L2中取出tradeid字段的值。
第3步，是根据tradeid值到trade_detail表中查找条件匹配的行。

explain的结果里面第二行的key=NULL表示的就是，这个过程是通过遍历主键索引的方式，一个一个地判断tradeid的值是否匹配。

问1：表trade_detail里tradeid字段上有索引，但是上述联合查询时，为什么走了全表扫描，而不是走tradeid索引？

答：因为这两个表的字符集不同，一个是utf8，一个是utf8mb4，所以做表连接查询的时候用不上关联字段的索引。

问2：为什么字符集不同就无法使用索引呢？

答：因为字符集utf8mb4是utf8的超集，意味着utf8能够表示的字符，utf8mb4都可以表示，而且utf8mb4还可以表示一些utf8不能表示的字符。所以当这两个类型的字符串在做比较的时候， MySQL内部的操作是，先把utf8字符串转成utf8mb4字符集，再做比较。

如果单独把这一步改成SQL语句的话，那就是：

// 驱动表的查询结果作为值（utf8mb4），被驱动表的tradeid（utf8）作为筛选字段
select * from trade_detail where tradeid = tradelog.tradeid;

// 也就是说，实际上这个语句等同于下面这个写法，即条件字段有函数操作，所以不能走索引
select * from trade_detail where CONVERT(traideid USING utf8mb4) = tradelog.tradeid;

这就再次触发了我们上面说到的原则：对索引字段做函数操作，优化器会放弃走树搜索功能。

所以，字符集不同只是条件之一，连接过程中要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。

注：在程序设计语言里面，做自动类型转换的时候，为了避免数据在转换过程中由于截断导致数据错误，也都是“按数据长度增加的方向”进行转换的。

假设有如下SQL语句：

select l.operator from tradelog l, trade_detail d where d.tradeid = l.tradeid and d.id=2;

查找trade_detail表里id=4的操作，对应的操作者是谁”？查询计划如下：

这个语句里trade_detail表成了驱动表，但是explain结果的第二行显示，这次的查询操作用上了被驱动表tradelog里的索引(tradeid)，扫描行数是1。

问3：这也是两个tradeid字段的join操作，为什么这次能用上被驱动表的tradeid索引呢？

因为驱动表和被驱动表关系调换了后，上述SQL语句类似：

// 此时驱动表trade_detail的tradeid（utf8）作为值
select operator from tradelog where traideid = trade_detail.tradeid;

// 也就是说，实际上这个语句等同于下面这个写法
// 此时函数是加在输入参数上，所以被驱动表可以使用traideid索引
select operator from tradelog where traideid = CONVERT(trade_detail.tradeid USING utf8mb4);

注：如果函数加在字段上，会破坏索引值的有序性，因此优化器就决定放弃走索引搜索。而如果函数加在值上，则不影响优化器选择走索引（字段“=”值）

问4：了解了上述原理后，针对下面SQL语句，该如何优化呢？

select d.* from tradelog l, trade_detail d where d.tradeid = l.tradeid and l.id = 2;

方案一：比较常见的优化方法是，把trade_detail表上的tradeid字段的字符集也改成utf8mb4，这样就没有字符集转换的问题了。

alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;

方案二：如果能够修改字段的字符集的话，是最好不过了。但如果数据量比较大，或者业务上暂时不能做这个DDL的话，那就只能采用修改SQL语句的方法了。

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

参考：MySQL45讲：18.为什么这些SQL语句逻辑相同，性能却差异巨大？