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

女爷i 2022-12-27 01:27 95阅读 0赞

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

**先来将重点：**

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

#### 18.1 字段函数操作 ####

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]

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

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

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

![在这里插入图片描述][20201207004121160.jpg_pic_center]

![在这里插入图片描述][20201207004130557.jpg_pic_center]

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

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

#### 18.2 隐形类型转换 ####

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

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

SELECT * from t where a = 500

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

![在这里插入图片描述][20201207004140246.jpg_pic_center]

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

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

我们将它做点改变即可：

![在这里插入图片描述][202012070041498.jpg_pic_center]

#### 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的预估情况：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]

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

**这条sql的执行流程如下：**

1.  在log表中找到id=2的行，拿到tradeid
2.  去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);

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/6457e014be774b9babf4be190d2cea28.png
[20201207004121160.jpg_pic_center]: /images/20221120/4f410dfbee3d49c092c4c606475aff87.png
[20201207004130557.jpg_pic_center]: /images/20221120/106ec16ffd9141fd92ecac33a24b1f6e.png
[20201207004140246.jpg_pic_center]: /images/20221120/a61e2b3c99b44b5b9822dd8882996b31.png
[202012070041498.jpg_pic_center]: /images/20221120/9a33b5fcd21f4a4f86e03f542e7dc6c9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/f72868e73af447d49155d13acbc685da.png