『MySQL 实战 45 讲』18 - 为什么这些SQL语句逻辑相同,性能却差异巨大

2024-05-03 00:04

本文主要是介绍『MySQL 实战 45 讲』18 - 为什么这些SQL语句逻辑相同,性能却差异巨大,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

为什么这些SQL语句逻辑相同,性能却差异巨大

条件字段函数操作

  1. 创建一个 sql 表。该表为交易记录表,包含交易流水号(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;
  1. 假设要求的是统计发生在所有年份中 7 月份的交易记录总数,可能会写出下面样式的 sql
select count(*) from tradelog where month(t_modified)=7;
  1. 由于函数计算,不会走索引,t_modified 的索引如图

在这里插入图片描述
4. 对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能
5. 优化器可以选择遍历主键索引,也可以选择遍历索引 t_modified,优化器对比索引大小后发现,索引 t_modified 更小,遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引 t_modified。但是这里是全索引扫描
在这里插入图片描述
6. 即使对于对于不改变有序性的函数,优化器也不会考虑使用索引(除非计算是在函数入参),例如

select * from tradelog where id + 1 = 10000
# 函数入参计算,这样才能用索引
# where id = 10000 -1

隐式类型转换

  1. 下面语句需要进行隐式转换,因为 tradeid 的字段类型是 varchar(32),而输入的参数却是整型,需要全表扫描
select * from tradelog where tradeid=110717;
  1. 对于优化器来说,这个语句相当于
select * from tradelog where  CAST(tradid AS signed int) = 110717;
  1. 这就说明了,这条语句触发了上面的规则:对索引字段做函数操作,优化器会放弃走树搜索功能

隐式字符编码转换

  1. 创建另外一个表,用于记录交易的操作细节,同时插入一些数据
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;insert into tradelog values(1, 'aaaaaaaa', 1000, now());
insert into tradelog values(2, 'aaaaaaab', 1000, now());
insert into tradelog values(3, 'aaaaaaac', 1000, now());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');
  1. 查询 id=2 的交易的所有操作步骤信息
  • 第一行显示优化器会先在交易记录表 tradelog 上查到 id=2 的行,这个步骤用上了主键索引,rows=1 表示只扫描一行
  • 第二行 key=NULL,表示没有用上交易详情表 trade_detail 上的 tradeid 索引,进行了全表扫描
select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /*语句Q1*/

在这里插入图片描述
3. Q1 语句的执行过程
在这里插入图片描述

  • (1)是根据 id 在 tradelog 表里找到 L2 这一行
  • (2)是从 L2 中取出 tradeid 字段的值
  • (3)是根据 tradeid 值到 trade_detail 表中查找条件匹配的行。explain 的结果里面第二行的 key=NULL 表示的就是,这个过程是通过遍历主键索引的方式,一个一个地判断 tradeid 的值是否匹配
  1. 问题就出在第 3 步,单独把这一步变成 SQL 语句的话,如下
select * from trade_detail where tradeid=$L2.tradeid.value; 
  1. 其中 $L2.tradeid.value 的字符集是 utf8mb4,字符集 utf8mb4 是 utf8 的超集,MySQL 内部的操作是,先把 utf8 字符串转成 utf8mb4 字符集,再做比较
  2. 也就是说,实际上这个语句等同于下面这个写法
select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value; 
  1. 对索引字段做函数操作,优化器会放弃走树搜索功能。字符集不同只是条件之一,连接过程中要求在被驱动表的索引字段上加函数操作,是直接导致对被驱动表做全表扫描的原因
  2. 换个需求,如果是“查找 trade_detail 表里 id=4 的操作,对应的操作者是谁”
select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

在这里插入图片描述
9. 这次的第 3 步语句相当于

select operator from tradelog  where traideid =$R4.tradeid.value; 
  1. 按照转换规则,实际是在输入参数上使用函数,所以能使用索引
select operator from tradelog  where traideid =CONVERT($R4.tradeid.value USING utf8mb4); 
  1. 所以,如果要使查 detail 表可以使用上索引,要不就修改字段的编码,要不就强制转换 tradeid 为 uft8
# 修改字段为 utf8mb4 
alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;
# 强制转换 tradeid 为 uft8
select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 
  1. 总结:对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能

这篇关于『MySQL 实战 45 讲』18 - 为什么这些SQL语句逻辑相同,性能却差异巨大的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/955425

相关文章

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中,BETWEEN是一个操作符,用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分

MySql match against工具详细用法

《MySqlmatchagainst工具详细用法》在MySQL中,MATCH……AGAINST是全文索引(Full-Textindex)的查询语法,它允许你对文本进行高效的全文搜素,支持自然语言搜... 目录一、全文索引的基本概念二、创建全文索引三、自然语言搜索四、布尔搜索五、相关性排序六、全文索引的限制七

C++使用printf语句实现进制转换的示例代码

《C++使用printf语句实现进制转换的示例代码》在C语言中,printf函数可以直接实现部分进制转换功能,通过格式说明符(formatspecifier)快速输出不同进制的数值,下面给大家分享C+... 目录一、printf 原生支持的进制转换1. 十进制、八进制、十六进制转换2. 显示进制前缀3. 指

数据库面试必备之MySQL中的乐观锁与悲观锁

《数据库面试必备之MySQL中的乐观锁与悲观锁》:本文主要介绍数据库面试必备之MySQL中乐观锁与悲观锁的相关资料,乐观锁适用于读多写少的场景,通过版本号检查避免冲突,而悲观锁适用于写多读少且对数... 目录一、引言二、乐观锁(一)原理(二)应用场景(三)示例代码三、悲观锁(一)原理(二)应用场景(三)示例

Python列表去重的4种核心方法与实战指南详解

《Python列表去重的4种核心方法与实战指南详解》在Python开发中,处理列表数据时经常需要去除重复元素,本文将详细介绍4种最实用的列表去重方法,有需要的小伙伴可以根据自己的需要进行选择... 目录方法1:集合(set)去重法(最快速)方法2:顺序遍历法(保持顺序)方法3:副本删除法(原地修改)方法4:

在Spring Boot中浅尝内存泄漏的实战记录

《在SpringBoot中浅尝内存泄漏的实战记录》本文给大家分享在SpringBoot中浅尝内存泄漏的实战记录,结合实例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录使用静态集合持有对象引用,阻止GC回收关键点:可执行代码:验证:1,运行程序(启动时添加JVM参数限制堆大小):2,访问 htt

SQL表间关联查询实例详解

《SQL表间关联查询实例详解》本文主要讲解SQL语句中常用的表间关联查询方式,包括:左连接(leftjoin)、右连接(rightjoin)、全连接(fulljoin)、内连接(innerjoin)、... 目录简介样例准备左外连接右外连接全外连接内连接交叉连接自然连接简介本文主要讲解SQL语句中常用的表

SQL server配置管理器找不到如何打开它

《SQLserver配置管理器找不到如何打开它》最近遇到了SQLserver配置管理器打不开的问题,尝试在开始菜单栏搜SQLServerManager无果,于是将自己找到的方法总结分享给大家,对SQ... 目录方法一:桌面图标进入方法二:运行窗口进入方法三:查找文件路径方法四:检查 SQL Server 安

MySQL 中的 LIMIT 语句及基本用法

《MySQL中的LIMIT语句及基本用法》LIMIT语句用于限制查询返回的行数,常用于分页查询或取部分数据,提高查询效率,:本文主要介绍MySQL中的LIMIT语句,需要的朋友可以参考下... 目录mysql 中的 LIMIT 语句1. LIMIT 语法2. LIMIT 基本用法(1) 获取前 N 行数据(