飙血推荐
  • HTML教程
  • MySQL教程
  • JavaScript基础教程
  • php入门教程
  • JavaScript正则表达式运用
  • Excel函数教程
  • UEditor使用文档
  • AngularJS教程
  • ThinkPHP5.0教程

MySQL索引失效之隐式转换

时间:2022-01-09  作者:geaozhang  

常见索引失效:

1. 条件索引字段"不干净":函数操作、运算操作

2. 隐式类型转换:字符串转数值;其他类型转换

3. 隐式字符编码转换:按字符编码数据长度大的方向转换,避免数据截取

一、常见索引失效场景

root@test 10:50 > show create table t_num\G
*************************** 1. row ***************************
       Table: t_num
Create Table: CREATE TABLE `t_num` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL,
  `c2` varchar(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4
​
root@test 10:51 > select * from t_num;
+----+----+----+
| id | c1 | c2 |
+----+----+----+
|  1 | -2 | -2 |
|  2 | -1 | -1 |
|  3 |  0 |  0 |
|  4 |  1 |  1 |
|  5 |  2 |  2 |
+----+----+----+
​
# 在c1字段上加上索引
root@test 10:52 > alter table t_num add index ix_c1(c1);
​
# 标准使用情况下,索引有效
root@test 10:55 > explain select * from t_num where c1 = -1;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_num | NULL       | ref  | ix_c1         | ix_c1 | 4       | const |    1 |   域名 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+

1、条件字段函数操作

# 在where中c1上加上abs()绝对值函数,可以看到type=ALL,全表扫描,在Server层进行绝对值处理后进行比较
root@test 10:58 > explain select * from t_num where abs(c1) = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_num | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   域名 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

如上,对索引字段做函数操作,即where条件列上不干净时,可能会破坏索引值的有序性(按照c1的值有序组织索引树),因此优化器就决定放弃走索引树搜索功能。

但是,条件字段函数操作下,也并非完全的走全表扫描,优化器并非完全的放弃该字段索引。

# 选择查询的数据,只有id和c1字段,可以看到type=index,使用到了ix_c1索引
root@test 10:59 > explain select id,c1 from t_num where abs(c1) = 1;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t_num | NULL       | index | NULL          | ix_c1 | 4       | NULL |    5 |   域名 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+

如上,由于ix_c1索引树是根节点c1和叶子节点id构造的,虽然因为c1上的函数操作导致放弃索引定位,但优化器可以选择遍历该索引树,使用覆盖索引(Using index),无需回表,将所需的id和c1数据返回Server层后进行后续的abs()和where过滤。

2、条件字段运算操作

# where条件里,对c1进行运算操作
root@test 11:03 > explain select * from t_num where c1 + 1 = 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_num | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   域名 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

如上,虽然“+1”的操作并没有破坏c1索引的有序性,但优化器仍然没有使用该索引快速定位。因此,等号左边,注意优化掉索引字段上的运算操作。

3、隐式类型转换

# 在c2字段上加上索引
root@test 12:30 > alter table t_num add index ix_c2(c2);
​
# 标准使用情况下(注:c2是varchar类型的),索引有效
root@test 12:30 > explain select * from t_num where c2 = "2";
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_num | NULL       | ref  | ix_c2         | ix_c2 | 42      | const |    1 |   域名 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
​
# 去掉等号右边值的引号,即字符串和数值进行比较,索引失效
root@test 12:30 > explain select * from t_num where c2 = 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_num | NULL       | ALL  | ix_c2         | NULL | NULL    | NULL |    5 |    域名 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

如上,c2字段是varchar类型,是字符串和数值的比较,此时,MySQL是将字符串转换成数字,即此处的c2被CAST(c2 AS signed int),这就相当于对条件字段做了函数操作,优化器放弃走树索引定位。

4、隐式字符编码转换

# 创建一个t_cou表,表结构基本和前面的t_num相同,唯一不同的设置是表字符集CHARSET=utf8
root@test 14:02 > show create table t_cou\G
*************************** 1. row ***************************
       Table: t_cou
Create Table: CREATE TABLE `t_cou` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL,
  `c2` varchar(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_c1` (`c1`),
  KEY `ix_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
​
root@test 14:02 > insert into t_cou select * from t_num;
​
# join表,t_num和t_cou通过c2字段进行关联查询
root@test 14:03 > select n.* from t_num n
    -> join t_cou c
    -> on 域名 = 域名
    -> where 域名 = 1;
+----+----+----+
| id | c1 | c2 |
+----+----+----+
|  4 |  1 | 1  |
+----+----+----+
​
root@test 14:23 > explain select n.* from t_num n join t_cou c  on 域名 = 域名 where 域名 = 1;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | c     | NULL       | ref  | ix_c1         | ix_c1 | 4       | const |    1 |   域名 | NULL                  |
|  1 | SIMPLE      | n     | NULL       | ref  | ix_c2         | ix_c2 | 42      | func  |    1 |   域名 | Using index condition |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
# 执行计划分析:
# 1.操作的c表,使用了ix_c1定位到一行数据
# 2.从c表定位到的行数据,拿到c2字段去操作n表,t_cou称为驱动表,t_num称为被驱动表
# 域名=func说明使用了函数操作,指的是域名=CONVERT(域名 USING utf8mb4)
# 4.同时Using index condition,ix_c2读取查询时,使用被下推的条件过滤,满足条件的才回表
​
root@test 14:23 > explain select n.* from t_num n join t_cou c  on 域名 = 域名 where 域名 = 1;
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-----------------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref   | rows | filtered | Extra                                                           |
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-----------------------------------------------------------------+
|  1 | SIMPLE      | n     | NULL       | ref   | ix_c1,ix_c2   | ix_c1 | 4       | const |    1 |   域名 | NULL                                                            |
|  1 | SIMPLE      | c     | NULL       | index | NULL          | ix_c2 | 32      | NULL  |    5 |   域名 | Using where; Using index; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-----------------------------------------------------------------+
# 执行计划分析:
# 1.操作的n表,使用了ix_c1定位到一行数据
# 2.从n表定位到的行数据,拿到c2字段去操作c表,t_num称为驱动表,t_cou称为被驱动表
# 3.同样的域名=域名,会将域名的字符集进行转换,即被驱动表的索引字段上加函数操作,索引失效
# 域名,表join时,驱动表数据读入join buffer,被驱动表连接字段无索引则全表扫,每取一行和join buffer数据对比判断,作为结果集返回

如上,分别对t_num、 t_cou作为驱动表和被驱动表的执行计划分析,总结:

  1. utf8mb4和utf8两种不同字符集(编码)类型的字符串在做比较时,MySQL会先把 utf8 字符串转成 utf8mb4 字符集,再做比较。为什么?字符集 utf8mb4 是 utf8 的超集,再做隐式自动类型转换时,为了避免数据在转换过程中由于截断导致数据错误,会“按数据长度增加的方向”进行转换。

  2. 表连接过程中,被驱动表的索引字段上加函数操作,会导致对被驱动表做全表扫描。

优化手法:

  1. 修改统一join字段的字符集

  2. 对驱动表下手,将连接字段的字符集转换成被驱动表连接字段的字符集

root@test 18:09 > explain select n.* from t_num n join t_cou c  on convert(域名 using utf8) = 域名 where 域名 = 1;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | n     | NULL       | ref  | ix_c1         | ix_c1 | 4       | const |    1 |   域名 | NULL                     |
|  1 | SIMPLE      | c     | NULL       | ref  | ix_c2         | ix_c2 | 32      | func  |    1 |   域名 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+--------------------------+

 

二、类型转换

1、字符串转整型

# 字符开头的一律为0
root@test 18:44 > select convert("abc", unsigned integer);
+----------------------------------+
| convert("abc", unsigned integer) |
+----------------------------------+
|                                0 |
+----------------------------------+
# \'abc\' = 0是成立的,因此查询时等号右边使用对应的类型很重要,0匹配出字段字符开头数据,\'0\'只匹配0
root@test 18:44 > select \'abc\' = 0;
+-----------+
| \'abc\' = 0 |
+-----------+
|         1 |
+-----------+
​
# 数字开头的,直接截取到第一个不是字符的位置
root@test 18:45 > select convert("123abc", unsigned integer);
+-------------------------------------+
| convert("123abc", unsigned integer) |
+-------------------------------------+
|                                 123 |
+-------------------------------------+

2、时间类型转换

root@test 19:11 > show create table time_demo\G
*************************** 1. row ***************************
       Table: time_demo
Create Table: CREATE TABLE `time_demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` datetime DEFAULT NULL,
  `c2` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
​
root@test 19:15 > select count(*) from time_demo;
+----------+
| count(*) |
+----------+
|       11 |
+----------+
​
root@test 19:16 > select * from time_demo limit 4;
+----+---------------------+------------+
| id | c1                  | c2         |
+----+---------------------+------------+
|  1 | 2022-01-08 00:01:01 | 2022-01-08 |
|  2 | 2022-01-06 23:01:01 | 2022-01-06 |
|  3 | 2022-01-06 00:00:00 | 2022-01-06 |
|  4 | 2022-01-08 00:00:00 | 2022-01-08 |
+----+---------------------+------------+
​
# 域名转datetime:末尾追加 00:00:00
root@test 19:11 > select * from time_demo where c1 between "2022-01-06" and "2022-01-08";
+----+---------------------+------------+
| id | c1                  | c2         |
+----+---------------------+------------+
|  2 | 2022-01-06 23:01:01 | 2022-01-06 |
|  3 | 2022-01-06 00:00:00 | 2022-01-06 |
|  4 | 2022-01-08 00:00:00 | 2022-01-08 |
+----+---------------------+------------+
# 结果分析:c1是datetime类型,进行比较时,between and中的date类型会转换成datetime
# 即 where c1 between "2022-01-06 00:00:00" and "2022-01-08 00:00:00";
# 同 where c1 >= "2022-01-06 00:00:00" and c1 <= "2022-01-08 00:00:00";
root@test 19:42 > explain select * from time_demo where c1 between "2022-01-06" and "2022-01-08";
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | time_demo | NULL       | range | ix_c1         | ix_c1 | 6       | NULL |    3 |   域名 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
# 格式化date转datetime
root@test 19:23 > select date_format("2022-01-08","%Y-%m-%d %H:%i:%s");
+-----------------------------------------------+
| date_format("2022-01-08","%Y-%m-%d %H:%i:%s") |
+-----------------------------------------------+
| 2022-01-06 00:00:00                           |
+-----------------------------------------------+
​
# 域名time转date:直接截取date部分
root@test 19:47 > select date(c1) from time_demo limit 1;
+------------+
| date(c1)   |
+------------+
| 2022-01-06 |
+------------+
​
# 域名转time,没有意义,直接变成 00:00:00
 

标签:MySQL编程
湘ICP备14001474号-3  投诉建议:234161800@qq.com   部分内容来源于网络,如有侵权,请联系删除。