MySQL-18：SQL逻辑相同，性能却差距巨大

最新推荐文章于 2022-06-08 17:52:30 发布

oldGarlic

最新推荐文章于 2022-06-08 17:52:30 发布

阅读量149

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/qq_40008535/article/details/110790187

版权

MySQL 专栏收录该内容

43 篇文章 2 订阅

订阅专栏

18、SQL逻辑相同，性能却差距巨大

先来将重点：

对索引字段做函数操作会导致优化器放弃走树搜索功能
不同类型的字段比较，会先转换为同类型再比较
不同字符集的字段比较，会将较小的字符集转型为较大的字符集

18.1 字段函数操作

现在，有一张，如下图，有100000行数据，同时在a，b字段上建了不同索引。

在这里插入图片描述

现在，我们要查询a = 1的数据行，以下有两种写法，当结果却不一样

EXPLAIN SELECT a,b from t WHERE a+999 = 1000
EXPLAIN SELECT a,b from t WHERE a = 1000-999

看下两条的预执行结果，我们会发现第一条语句走了全表扫描，第二条语句走了索引a。

在这里插入图片描述

造成这种现象的原因在于MySQL在使用索引时，会拿“=”右边的值与索引中存储的字段值比对，同时“=”左边的变量也需要和索引字段相同。

也就是说，第一条sql将1000去比对值，a+999会作为索引字段，所以当对索引字段做函数操作，优化器会放弃走树搜索功能。

18.2 隐形类型转换

这是一个谁转换为谁的问题，先说结论，在MySQL中，字符串和整形做比较，会统一转换为数字。

还是上面那张表，我们将a的数据类型由int(11)修改为varchar(64)，我们来执行如下sql语句：

SELECT * from t where a = 500

我们会发现如下，走的是全表扫描：

在这里插入图片描述

看看上面说过的结论，字符串和整形做比较，会统一转换为数字，在执行过程中它需要把每个索引值都转化数字再进行比较，也就是说它是下面这样子的。

SELECT * from t where cast(a as SIGNED) = 500

我们将它做点改变即可：

在这里插入图片描述

18.3 隐式字符集编码转换

先上结论，字符集是有范围大小之分的，就像是编程语言中的数据类型一样，两者比较，为了避免数据截断问题，总是会统一转换为更大的那一个类型再进行比较，例如，int和long比较，会统一转换为long类型再比较。

先建表，两张表中字符设置的不一样，一张为utf8，一张为utf8mb4，utf8mb4为utf8的超集。


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;

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');

先查询id =2的所有操作步骤信息，sql如下，并查看sql的预估情况：

在这里插入图片描述

我们会发现查询a.id =2使用了主键索引，查询a.tradeid = b.tradeid使用了全表扫描。

这条sql的执行流程如下：

在log表中找到id=2的行，拿到tradeid
去detail表中查找数据，当这一步的时候却没有使用tradeid索引树

本质上类比过来进行这样： 本质原来还是索引字段做了函数操作。

 SELECT * from trade_detail WHERE tradeid = CONVERT("aaaaaaab" USING utf8mb4);
 SELECT * from trade_detail WHERE CONVERT(tradeid USING utf8mb4)  = CONVERT("aaaaaaab" USING utf8mb4);