mysql 索引级别排序 const ref mysql 索引顺序

转载

mob64ca1417b0c6 2024-07-23 15:51:26

文章标签 mysql 学习 b树字段主键 文章分类 MySQL 数据库

MySQL学习总结--索引 & 排序

索引模型、索引类型、索引覆盖、索引下推
唯一索引 VS 普通索引
change buffer
重建索引/表
索引选择异常
字符串字段如何加索引
order by 排序字段要加索引
索引字段不要应用函数，其传参可以应用函数

索引模型、索引类型、索引覆盖、索引下推

索引的作用：提高数据查询效率，避免全表扫描 O(n)，扫描时间随数据量变大线性增长。
常见索引模型

哈希表：使用哈希函数提高查询效率，只适合等值查询。
有序数组：使用二分法提高查询效率 O(log(N))，但更新效率低，适合静态历史数据。
搜索树：使用N叉树（二叉树树高过高）提高查询&更新效率 O(log(N))，适合关系型数据库。

InnoDB中的索引模型：B+树

InnoDB 的 B+树叶子节点是一个数据页（page），每个数据页默认16K，一个页里面可以存多个记录，数据页内部有个有序数组，可以理解为通过二分法再去定位行数据，数据页之间通过双向链表相互连接。
页分裂：一个数据页满了，需要新增加一个数据页，会导致空间利用率降低大概50%。
页合并：当相邻的两个数据页利用率很低的时候会做数据页合并，即页分裂的逆过程。
读写数据是也是按数据页为单位来读写的。当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。

索引类型

主键索引(聚簇索引)：B+树叶子节点存的是整行的数据，无需回表。
非主键索引(二级索引)：B+树叶子节点存的是主键的值，需要回表（回到主键索引上再查一次，从而得到整行数据）。

索引组织表结构

每一张表其实就是一个主键索引 B+树，叶子结点的key是主键，value是行数据。没有设主键的表，InnoDB会默认创建一个 rowid（6 字节）做主键。
二级索引就是新增一个B+树，key是索引字段，value是主键。查询如果未命中索引，则遍历主键B+树。

索引覆盖

是指索引上的信息足够满足查询请求，不需要再回表到主键索引上去取数据，高频请求使用索引覆盖，是一个常用的性能优化手段。
如果查询条件使用的是二级索引（或是联合索引的最左原则字段），查询目标字段是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写读取整行数据。

最左前缀匹配原则

最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。
B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。
查询条件where name like ‘张 %’ 能够命中单个 name 索引，或 name 位于最左的联合索引。

联合索引字段顺序

最左前缀匹配原则：如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
空间：字段长度大的放最左，字段长度小的可以单独建索引。

索引下推

虽然本次查询需要回表，但在回表前，充分利用索引中的字段进行了where过滤，减少了回表次数。
MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
查询条件like 'hello%’ and age >10，MySQL5.6版本之前，会对所有匹配like 'hello%’ 的数据进行回表查询。5.6版本后，会先过滤掉age <= 10的数据，再进行回表查询。

自增主键的应用场景

由于 InnoDB 是 B+ 索引组织表，从性能和存储空间方面考量，一般选择自增主键，并尽量使用主键查询，因为：

自增主键每次插入数据时都是追加操作，不涉及到挪动其他记录，不会触发叶子节点的页分裂。
自增主键长度较小，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

业务逻辑字段做主键的应用场景

典型的 KV 场景：只有一个索引，且该索引必须是唯一索引。
所有的查询都是where k=N，并没有>= 或者<= 这种操作（没有range操作），在这种场景里，hash索引的效率就比N叉树高。
由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

“N叉树”的N值在MySQL中是否可以人工调整

通过改变页的大小能间接影响 N 值

页的大小默认为 16KB，5.6 以后可以通过 innodb_page_size 参数控制，页越大，一页能存放的索引数量就越多，N值就越大。
注意，页太小会导致树高过高；页太大，则加载到内存的时间和单个数据页查询时间会提高，需要达到平衡才行。

通过改变索引key的大小能间接影响 N 值

N叉树中非叶子节点存放的是索引信息，索引包含Key和Point指针。 Point指针的大小是固定为的，因此Key越小，一个页（16K）中能存放的索引数量就越多，N值就越大。

update /delete 条件一定要加索引

RR 隔离级别下，对于无索引字段进行 update、delete、select .. for update 操作，代价极高，会锁住主键索引上所有的行和间隙，容易出现死锁。而有索引字段只有符合条件的记录本身和附近的间隔会加锁。
事前预防：设置参数 sql_safe_updates = on。如果 delete 或者 update 语句中没有 where 条件，或者 where 条件里面没有包含索引字段，则执行会报错。

唯一索引 VS 普通索引

【普通索引】写多读少场景，且业务上能确保字段值唯一：账单类、日志类的系统

写操作性能：普通索引能利用内存中的 Change Buffer，比唯一索引更快。唯一索引必须将数据页从磁盘读到内存中，然后进行判断和插入。特别是，出于成本考虑使用机械硬盘的“历史数据”的归档库，尽量使用普通索引，把 change buffer 尽量开大，能显著提升写入速度。遇到大量插入数据慢、内存命中率低的时候（Hit rate），也可以从索引上进行排查。
读操作性能：两者几乎没有差异。因为InnoDB 读写数据的单位是数据页，整页读到内存中（60KB），普通索引要读 2 行，读到不符合条件的行返回，唯一索引读到 1 行符合条件即返回。

【唯一索引】写后立马会做查询场景

会触发 change buffer 立即 merge 到磁盘，这样的场景磁盘 io 次数不会减少，反而会增加 change buffer 的维护代价。

change buffer

change buffer 是一种缓存，如果写操作涉及的数据页不在内存中，则由 change buffer 先缓存这次的操作，从而避免了先读后写的【随机读】数据页的磁盘 IO，下次对这个数据页进行读操作时会自动触发 merge（将 change buffer 中的操作应用到原数据页得到最新结果），系统也有后台线程会定期 merge。

在内存中有一份：占用 Innodb buffer pool，参数innodb_change_buffer_max_size设置其占比。
也会通过 redo log 持久化到磁盘，从而 crash-safe：用到了change buffer机制时，在 redo log 中记录的本次变更，是记录new change buffer item相关的信息，而不是直接的记录物理页的变更。

merge 的流程

把老版本的数据页从磁盘读入到内存。
从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版本的数据页。
写 redo log，包含了数据页的变更和 change buffer 的变更。
此时，数据页和内存中 change buffer 对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据，就是刷脏页的过程了，何时刷取决于刷脏页的策略。

change buffer 只对二级普通索引有用

减少读磁盘：仅仅是减少的是对二级普通索引页的读磁盘操作，而对于其他类型的页(唯一索引，主键索引)还是要读磁盘的。
减少内存占用：change buffer 虽然还是需要内存占用(记录数据更新动作)，但相比于数据页来说(默认16K)，所占的内存还是小了很多的。

insert 语句优化效果更明显：把多个唯一索引改为普通索引，从而利用 change buffer 减少随机读。

写操作（插入，更新，删除）会同时更新主键索引树、所有二级索引树。
insert：主键索引树和唯一索引树的肯定都要更新，无法用到 change buffer；但是普通索引树的更新，可以使用 change buffer。
update：只要涉及到相关字段更新，就要同时更新相应的索引树，道理同上。

redo log 与 change buffer 分别优化了先读后写操作流程的不同阶段

先把要变更的数据页从磁盘读到内存中。Change buffer 优化此步骤的随机【读】，仅当所需数据页不在内存中，需要先读磁盘时，才有用武之地。
对内存数据页执行变更。
将变更后的数据页，写入至磁盘中。 Redo log 优化此步骤的随机【写】，转化为顺序写，这是确保crash-safe的必须步骤，一直都会用到。

重建索引/表

为什么要重建索引/表 – 为了收缩空间

索引可能因为删除，或者页分裂等原因，导致数据页有空洞。
重建索引的过程会创建一个新的索引，把数据递增插入，这样页面的利用率最高，索引更紧凑、更省空间。

空洞：被标记为可复用，而没有被使用的空间，看起来就像是“空洞”。

删除行，被删除的行只是被标记为可复用，但磁盘文件的大小是不会变的，即 delete 命令不能回收表空间。
插入行，索引递增插入时紧凑，随机插入时可能造成数据页分裂，形成空洞。
更新索引上的值，实际上是把旧值标记为删除，然后插入一个新值，也会形成空洞。

重建二级索引

# 正确示例
 alter table T drop index k;
 alter table T add index(k);
 ```

重建主键索引/表

# 错误示例：
 # 直接删掉主键索引是不好的，它会使得所有的二级索引都失效，并且会用ROWID来作主键索引；
 # 不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。
 alter table T drop primary key;
 alter table T add primary key(id);
 
 # 正确示例：
 alter table T engine=InnoDB;
 ```

alter table t engine=InnoDB 反而让表空间变大

在 DDL 期间，如果刚好有外部的 DML 在执行，这期间可能会引入一些新的空洞。
在重建表的时候，InnoDB 不会把整张表占满，每个页留了 1/16 给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。

alter / analyze / optimize table

alter table 重建表，减少页空洞，回收空间。
analyze table 重新统计索引信息，解决优化器索引选择异常（预估扫描行数不准确）。
optimize table 重建表+重新统计索引信息。

online / inplace / copy

# alter table 重建表默认使用 inplace 方式，即在引擎层中创建临时文件，server 层无感
 alter table t engine=innodb,ALGORITHM=inplace;
 # 也可手动指定使用 copy 方式，即在 server 层创建临时表
 alter table t engine=innodb,ALGORITHM=copy;

online：在执行 DDL 时不阻塞对原表的写操作。online 一定 inplace，反过来不一定成立，例如：全文索引（FULLTEXT index）、空间索引 (SPATIAL index)。
inplace ：在引擎层中创建临时文件，server 层无感。如果你有一个 1TB 的表，现在磁盘间是 1.2TB，能不能做一个 inplace 的 DDL 呢？答案是不能。因为，tmp_file 也是要占用临时空间的。
copy ：在 server 层创建临时表。

DDL 过程

DDL 过程：新建一个表、递增插入旧表的数据、交换表名、删除旧表。
Online DDL 过程：在转存数据过程中，对旧表的写操作记录日志，最后对新表重放此日志。MySQL 5.6 版本开始引入。
建议只对小表在业务低峰期使用 Online DDL，线上大表建议用 GitHub 开源的 gh-ost 来做。

索引选择异常

如何定位索引选择异常这样的问题?

set long_query_time=0在慢查询日志中记录所有语句，对比force index(a)强制指定索引、优化器自动选择索引两个语句的执行时间。
explain查看优化器如何选择索引，同时使用show warnings可以看到MySQL 实际执行的语句，包括字符集的转换。
show index table_name查看索引基数。
analyze table table_name重新统计索引信息。
force index(a)强制指定索引。
修改语句引导优化器，或增加、删除索引。

优化器选错索引的情况

由于不断地在一张表上删除、插入，导致索引统计信息不准确，导致判断扫描行数不准确，可用 analyze table table_name来重新统计索引信息。
由于临时表，排序字段，导致优化器误判，可用force index强制指定索引，或修改语句引导优化器，或增加或者删除索引来绕过这个问题。

慢查询日志是真正判断SQL效率的依据

# 将慢查询日志的阈值设置为 0，这个线程接下来的所有语句都会被记录入慢查询日志中；
 set long_query_time=0;
 # 普通查询语句，由优化器选择索引
 select * from t where a between 10000 and 20000; /*Q1*/
 # force index(a) 手动指定使用索引 a
 select * from t force index(a) where a between 10000 and 20000;/*Q2*/

explain 查看优化器如何选择索引

影响优化器选择索引的因素

扫描行数（索引行数+回表行数）
是否使用临时表
是否需要排序

explain 输出

key：选择的索引
rows：预估的扫描行数
Extra

Using filesort：排序字段不在本次使用的索引中，需要额外排序。
Using index：使用了覆盖索引，无需回表。
Using index condition：使用了索引下推，虽然本次查询需要回表，但在回表前，充分利用索引中的字段进行了where过滤，减少了回表次数。
Using where：表示本次查询要进行筛选过滤。
Using temporary：表示需要使用临时表。
Block Nested Loop、join_buffer：join 语句使用了 BNL 算法，驱动表数据暂存到了 join_buffer。
Using MRR：使用了 MRR 多范围读优化，Multi-Range Read，二级索引的多值查询，在回表前先对主键 id 排序（借助 read_rnd_buffer），从而将随机读转化为顺序读，提高查询效率。
Using join buffer (Batched Key Access)：join 语句使用了 BKA 算法，先把驱动表的数据先放入 join_buffer，把 join 字段作为 key 批量提交给引擎层的 MRR 接口，而 MRR 会先在被驱动表 join 字段二级索引上进行多值查询，在回表前对主键 id 排序，从而将随机读转化为顺序读。
Using join buffer (hash join)：join 语句使用了哈希 join，在 join_buffer 里维护一个哈希表放入驱动表的数据，扫描被驱动表时用 join 字段做 hash 查找。8.0.20 版本开始全面用 hash join 替代了 BNL，能显著提升无索引 join 语句的性能。

优化器怎么判断要扫描的行数

根据索引统计信息/ 索引基数 / 区分度（cardinality）来估算要扫描的行数。一个索引上不同的值越多，这个索引的区分度就越好，即索引基数越大。
show index 查看索引基数
索引基数如何计算? 通过哪个参数可以设置索引统计的存储方式?

索引基数本身是通过采样统计估算出来的，不是准确结果。
索引基数 = 统计 N 个数据页上不同值的平均个数 * 这个索引的页面数
当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。
参数innodb_stats_persistent ：on 表示统计信息会持久化存储，默认 N 是 20，M 是 10。 off 表示统计信息只存储在内存中，默认 N 是 8，M 是 16。

字符串字段如何加索引

直接创建完整索引，可能比较占用空间，优点是简单，能利用覆盖索引。
创建前缀索引，节省空间，缺点是会增加查询扫描次数，不能使用覆盖索引。需要判断出前缀的长度为多少时，其损失的区分度能接受。

# 计算不同前缀长度下，此列上有多少不重复的值
 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;

使用reverse函数倒序存储，再创建前缀索引，适于前缀区分度不高，而后缀区分度高的场景，目的还是要提高索引的区分度，缺点是不支持范围扫描。

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

使用crc32函数创建 hash 整数字段，再创建索引，查询性能稳定，有额外的存储和计算消耗，缺点是不支持范围扫描。

# 在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。
 mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);
 # crc32() 函数得到校验码
 mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

order by 排序字段要加索引

为什么排序字段要加索引

因为 B+ 树索引本身是有序的，MySQL 只需按索引顺序直接取出数据，无需放入 sort_buffer 进行额外排序，即 explain语句Extra字段中没有 Using filesort。
如果排序字有索引，则无需回表取排序字段，实现索引覆盖，即 explain语句Extra字段有 Using index。
需要多个字段一起排序时，可以按照最左前缀匹配原则建立组合索引。
分页 limit 过大时会导致大量排序，可以记录上一页最后的 id，下一页查询条件带上where id > last_id limit 100。

优化器如何选择排序策略

sort_buffer 是 MySQL 给每个线程分配的一块用于排序的内存。
sort_buffer_size ：如果数据总体大小不超过此上限，则在在内存中排序（快排 or 堆选择），超出就利用磁盘临时文件辅助（归并），进行外部排序。
max_length_for_sort_data ：决定使用哪种排序策略，默认策略为全字段排序，如果单行的长度超过此值，就使用 rowid 排序。即，如果 MySQL 认为内存足够大，会优先选择全字段排序，否则使用 rowid 排序（多一次回表）。因为如果 select 的字段很多，sort_buffer 里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。
rowid：对于有主键的 innodb，rowid就是主键。对于没有主键的innodb，rowid由系统生成（6个字节）。对于Memory引擎，rowid就是数组下标。
对于 InnoDB 磁盘临时表，执行全字段排序会减少磁盘访问，因此会被优先选择。
对于内存临时表，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘，此时排序的行越小越好，因此优化器会优先选择 rowid 排序。
内存临时表，使用 Memory 引擎创建的临时表。tmp_table_size 参数限制了内存临时表的大小，默认值是 16M，超过则只能使用磁盘临时表。
磁盘临时表，使用引擎 InnoDB（默认）或 MyISAM 创建的临时表。参数 internal_tmp_disk_storage_engine指定磁盘临时表的默认引擎。

全字段排序流程

在 where 普通索引上查找，回表取【排序字段 + 所有 select 的字段】，放入 sort_buffer 中。
对 sort_buffer 中的数据按照排序字段进行排序，取 limit 行。

rowid 排序流程

在 where 普通索引上查找，回表取【排序字段 + rowid】，放入 sort_buffer 中。
对 sort_buffer 中的数据按照排序字段进行排序，取 limit 行。
再次回表取所有 select 的字段。

如何确定一个排序语句是否使用了临时文件

/* 打开optimizer_trace，只对本线程有效 */
 SET optimizer_trace='enabled=on'; 
 /* @a保存Innodb_rows_read的初始值 */
 select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';
 /* 执行语句 */
 select city, name,age from t where city='杭州' order by name limit 1000; 
 /* 查看 OPTIMIZER_TRACE 输出 */
 SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
 /* @b保存Innodb_rows_read的当前值 */
 select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
 /* 计算Innodb_rows_read差值 */
 select @b-@a;
 - set tmp_table_size=1024;
 set sort_buffer_size=32768;
 set max_length_for_sort_data=16;
 /* 打开 optimizer_trace，只对本线程有效 */
 SET optimizer_trace='enabled=on';
 - /* 执行语句 */
 select word from words order by rand() limit 3;
 - /* 查看 OPTIMIZER_TRACE 输出 */
 SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

OPTIMIZER_TRACE 输出

number_of_tmp_files ：使用的临时文件数量。sort_buffer_size 越小，需要分成的份数越多，此值就越大。
examined_rows：参与排序的行数。
sort_mode：packed_additional_fields 表示排序过程对字符串做了“紧凑”处理。即使 name 字段的定义是varchar(16)，在排序过程中还是按照实际长度来分配空间的。rowid 表示使用 rowid 排序策略，没有表示使用默认的全字段排序策略。
select @b-@a ：表示整个执行过程扫描了多少行，如果采用 rowid 排序，此值还包含第二次回表扫描的行数，例如 4000 + limit 1000 = 5000。
filesort_priority_queue_optimization ：chosen=true，表示使用了优先队列排序算法（堆选择排序），这个过程不需要临时文件，因此对应的 number_of_tmp_files 是 0。 limit 行数 * (排序字段 + rowid) 长度 + 维护堆结构代价 < sort_buffer_size 会使用堆选择排序，超出则使用归并排序。

order by rand() 随机排序对大表性能很差
bash mysql> select word from words order by rand() limit 3;

order by rand() 会创建随机值临时表，再额外排序。
创建一个没有索引的内存临时表 Using temporary，放入 select 字段、rand() 函数生成的随机数，此过程为全表扫描。
借助 sort_buffer 对内存临时表排序 Using filesort，根据 MySQL 版本，可能又对内存临时表全表扫描一次。

随机排序方法

只随机取一个 word 值

# 取得整个表的行数，并记为 C。
 select count(*) into @C from t;
 # 取得 Y = floor(C * rand())。 floor 函数在这里的作用，就是取整数部分。
 set @Y = floor(@C * rand());
 # 再用 limit Y,1 取得一行。
 set @sql = concat("select * from t limit ", @Y, ",1");
 # 由于 limit 后面的参数不能直接跟变量，使用了 prepare+execute 的方法，也可以改为拼接 SQL。 
 prepare stmt from @sql;
 execute stmt;
 DEALLOCATE prepare stmt;

MySQL 处理limit Y,1的做法就是按顺序一个一个地读出来，丢掉前 Y 个，然后把下一个记录作为返回结果，因此这一步需要扫描 Y+1 行。再加上，第一步扫描的 C 行，总共需要扫描 C+Y+1 行。但是也比 order by rand() 代价小的多，因为select * from t limit @Y1，1是直接在主键索引上按序取 limit 行数据，利用了索引本身的有序性。远比 order by rand() 组成随机值临时表，再额外排序的代价小。
随机取 3 个 word 值

# 执行三个 limit Y, 1 语句得到三行数据。
 select count(*) into @C from t;
 set @Y1 = floor(@C * rand());
 set @Y2 = floor(@C * rand());
 set @Y3 = floor(@C * rand());
 select * from t limit @Y1，1； //在应用代码里面取Y1、Y2、Y3值，拼出SQL后执行
 select * from t limit @Y2，1；
 select * from t limit @Y3，1；
 # 总扫描行数是 C+(Y1+1)+(Y2+1)+(Y3+1)，实际上它还是可以继续优化，来进一步减少扫描行数的。
 # 取 Y1、Y2 和 Y3 里面最大的一个数，记为 M，最小的一个数记为 N，总共只需要 C+M+1 行
 select * from t limit N, M-N+1;

limit n order by 分页查询时有重复数据

limit n, a 获取 a 条记录，会把 n + a 行都读出来，n 很大时性能会很差。
limit n+a, a 获取第二组 a 条记录。
这两组 a 条记录出现了重复数据，是因为limit 有可能出现两种算法，比如直接排序和优先队列排序，就是不同的结果。而 limit 后面的参数，是会影响算法的。

索引字段不要应用函数，其传参可以应用函数

因为对索引字段做函数操作，可能会破坏索引值的有序性，从而导致优化器放弃树搜索，使用全索引扫描。

索引字段应用函数：显式函数、隐式的字段类型转换 CAST、隐式的字符集转换 CONVERT。
联合索引对左侧字段做 in 查询。
联合索引 order by 一个字段升序，另一个字段降序。

显式的函数操作

# 【错误】索引字段应用了函数，导致全索引扫描
 select count(*) from tradelog where month(t_modified)=7;
 # 【正确】利用索引
 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');
 # 【错误】
 select * from tradelog where id + 1 = 10000;
 # 【正确】
 select * from tradelog where id = 10000 - 1;

隐式的字段类型转换：字符串 -> 数字

# 【错误】tradeid 是字符串类型，隐式转换相当于 where CAST(tradid AS signed int) = 110717
 select * from tradelog where tradeid=110717;
 # 【正确】id 是整数类型，隐式转换相当于 where id = CAST("83126" AS signed int)
 select * from tradelog where id="83126";

隐式的字符集转换：子集（utf8） -> 超集（utf8mb4）

join 的两个表字符集不同时，可能对被驱动表的索引字段应用CONVERT函数，导致优化器放弃树搜索，使用全索引扫描。
解决思路：选字符集和排序集时，字符串都选超集 utf8mb4；或者改 sql 语句。
执行explain语句后，使用show warnings; 可以看到MySQL 实际执行的SQL，包括字符集的转换。

# 【错误】条件 l.id=2 确定驱动表是 l ，将 l.tradeid 作为传参值（超集），在被驱动表 d 中查找（子集） where d.tradeid="val"，相当于 where CONVERT(d.traideid USING utf8mb4)="val"，导致不能对索引 d.traideid 做树搜索，只能全索引扫描。
 select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
 
 # 【正确】条件 l.id=2 确定驱动表是 l ，将 l.tradeid 作为传参值（超集），对传参值应用转换函数（子集），在被驱动表 d 中查找（子集）时就能利用索引 d.traideid 做树搜索。
 select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 
 
 # 【正确】条件 d.id=4 确定驱动表是 d ，将 d.tradeid 作为传参值（子集），在被驱动表 l 中查找（超集） where l.tradeid="val"，相当于 where l.tradeid=CONVERT("val" USING utf8mb4)，是对传参应用函数，能利用索引 l.traideid 做树搜索。
 select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

查看数据类型转换规则简单方法

select “10” > 9;

如果转换规则是“将字符串转成数字”，那么就是做数字比较，结果应该是 1。
如果转换规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是 0。字符串比较大小是逐位从高位到低位逐个比较（按ascii码）那么“10”的“1”的ascii比“9”小，所以结果为0。

select 'a' = 0;

如果结果是1，说明无法转换成数字的字符串都被转换成0来处理了。

字符集转换规则是子集（utf8） -> 超集（utf8mb4）

传参超出字符串长度被截断造成慢查询

# 字段 b 定义的是 varchar(10)，where 传参长度超出10
 select * from table_a where b='1234567890abcd';
 # 最理想的情况是 MySQL 看到参数长度超了，直接返回空，然而并没有
 # 再不济，将参数拿到索引里面去做匹配，肯定也没能够快速判断出索引树 b 上并没有这个值，也很快就能返回空，然而也没有
 # 实际执行很慢，原因是：引擎层做了字符截断，用截断的参数在索引 b 上查找到 10 万行，回表 10 万次；但是每次回表以后查出整行，到 server 层一判断，b 的值都不是’1234567890abcd’;返回结果是空。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。