索引问题是 SQL 问题中出现频率最高的，常见的索引问题包括：无索引，隐式转换。当数据库中出现访问表的 SQL 无索引导致全表扫描，如果表的数据量很大，扫描大量的数据，应用请求变慢占用数据库连接，连接堆积很快达到数据库的最大连接数设置，新的应用请求将会被拒绝导致故障发生。
一、什么是隐式转换？
当我们对不同类型的值进行比较的时候，为了使得这些数值「可比较」（也可以称为类型的兼容性)，MySQL 会做一些隐式转化（Implicit type conversion）。比如下面的例子：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> select 1+'1'; +-------+ | 1+'1' | +-------+ | 2 | +-------+ 1 row in set (0.00 sec) mysql> select CONCAT(2,' test'); +-------------------+ | CONCAT(2,' test') | +-------------------+ | 2 test | +-------------------+ 1 row in set (0.01 sec) |
很明显，上面的SQL语句的执行过程中就出现了隐式转化。并且从结果们可以判断出，第一条SQL中，将字符串的“1”转换为数字1，而在第二条的SQL中，将数字2转换为字符串“2”。MySQL也提供了CAST()函数，我们可以使用它明确的把数值转换为字符串。
当隐式转换出现在查询中，指SQL查询条件中的传入值与对应字段的数据定义不一致，此时MySQL在执行条件判断时，若参数类型与字段类型不匹配，则会做类型的隐式转换，符合转换规则的，转换完成后可以利用索引，如果无法转换则会导致索引无法使用，进而出现上述慢SQL堆积数据库连接数跑满的情况。常见的隐式转换如字段的表结构定义为字符类型，但SQL传入值为数字；或者是字段定义collation为区分大小写，在多表关联的场景下，其表的关联字段大小写敏感定义各不相同。
隐式类型转换有无法命中索引的风险，在高并发、大数据量的情况下，命不中索引带来的后果非常严重。将数据库拖死，继而整个系统崩溃，对于大规模系统损失惨重。
官方文档中关于隐式转化的规则是如下描述的：
- 当两个参数至少有一个是NULL时，比较的结果也是NULL；另外是使用<=>（安全的等于）对两个NULL做比较时会返回1，这两种情况都不需要做类型转换。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> select null = null; +-------------+ | null = null | +-------------+ | NULL | +-------------+ 1 row in set (0.00 sec) mysql> select null = 1; +----------+ | null = 1 | +----------+ | NULL | +----------+ 1 row in set (0.00 sec) mysql> select null <=> null; +---------------+ | null <=> null | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec) |
- 当两个参数都是字符串，会按照字符串来比较，不做类型转换。
|
1 2 3 4 5 6 7 |
mysql> select 'a' = 'a'; +-----------+ | 'a' = 'a' | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec) |
- 同样当两个参数都是整数，按照整数来比较，不做类型转换。
|
1 2 3 4 5 6 7 |
mysql> select 1 = 2; +-------+ | 1 = 2 | +-------+ | 0 | +-------+ 1 row in set (0.00 sec) |
- 十六进制的值和非数字做比较时，会被当做二进制串，和数字做比较时会按下面的规则处理。
有一个参数是TIMESTAMP或DATETIME，并且另外一个参数是常量，常量会被转换为timestamp。
有一个参数是decimal类型，如果另外一个参数是decimal或者整数，会将整数转换为decimal后进行比较；如果另外一个参数是浮点数，则会把decimal转换为浮点数进行比较；所有其他情况下，两个参数都会被转换为浮点数再进行比较。
由于MySQL隐式类型转换规则比较复杂，依赖MySQL隐式转换很容易出现各种想想不到的问题，而且MySQL隐式类型转换本身也是非常耗费MySQL服务器性能的，所以非常不推荐这样使用。
二、隐式转换分析
为了便于理解直接从隐式转换的注入例子开始来往下分析。首先创建表，加入俩个用户。
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE `users` ( `userid` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(45) NOT NULL, `password` varchar(45) NOT NULL, PRIMARY KEY (`userid`) ); INSERT INTO `users` (`username`,`password`) VALUES ('admin','MySuperS3cretPass!'); INSERT INTO `users` (`username`,`password`) VALUES ('666admin','MySuperS3cretPass!'); |
看如下查询
|
1 2 3 4 5 6 7 |
mysql> SELECT * FROM users WHERE username = 'a'+'b' AND password = 'a'+'b'; +--------+----------+--------------------+ | userid | username | password | +--------+----------+--------------------+ | 1 | admin | MySuperS3cretPass! | +--------+----------+--------------------+ 1 row in set, 7 warnings (0.00 sec) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> SHOW WARNINGS; +---------+------+--------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'admin' | | Warning | 1292 | Truncated incorrect DOUBLE value: 'a' | | Warning | 1292 | Truncated incorrect DOUBLE value: 'b' | | Warning | 1292 | Truncated incorrect DOUBLE value: 'MySuperS3cretPass!' | | Warning | 1292 | Truncated incorrect DOUBLE value: 'a' | | Warning | 1292 | Truncated incorrect DOUBLE value: 'b' | | Warning | 1292 | Truncated incorrect DOUBLE value: '666admin' | +---------+------+--------------------------------------------------------+ 7 rows in set (0.00 sec) |
好奇这个结果怎么会是这样的，没错，这里利用了隐式转换注入，回过头来分析一下原理。
|
1 2 3 4 5 6 7 |
mysql> SELECT 1+1; +-----+ | 1+1 | +-----+ | 2 | +-----+ 1 row in set (0.00 sec) |
如上查询没什么问题…这里不说了，接着下面。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> SELECT 'foo'+1; +---------+ | 'foo'+1 | +---------+ | 1 | +---------+ 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+-----------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' | +---------+------+-----------------------------------------+ 1 row in set (0.00 sec) |
这里发生了隐式转换，结果为查询结果为1，这里’foo’被转换成double类型，但是显然他不是数字，所以它将转换成0。
MySQL手册说明如下：当操作符与不同类型的操作数一起使用时，会发生类型转换，使操作数兼容。
那么添加俩个字符串呢？不需要转换吗？看下面SQL执行。
|
1 2 3 4 5 6 7 |
mysql> SELECT 'a'+'b'; +---------+ | 'a'+'b' | +---------+ | 0 | +---------+ 1 row in set, 2 warnings (0.00 sec) |
这里“+”是个算术运算符，俩个字符串转化为数值0+0，结果自然就是0了。
现在知道俩个字符串的总合是0，通过查询我们知道SELECT ‘a’ + ‘b’ = 0;的结果是true（数值就是1），那么比较下俩个字符串总和和另外一个字符串。
|
1 2 3 4 5 6 7 |
mysql> SELECT 'a'+'b'='c'; +-------------+ | 'a'+'b'='c' | +-------------+ | 1 | +-------------+ 1 row in set, 3 warnings (0.00 sec) |
从warnings我们可以看出都进行了隐式转换，查询执行应该是select 0=0…结果就是1。
MySQL手册说明如下：在所有其他情况下，将参数作为浮点数（实数）进行比较。
好了，基础了解到这里，来理解下文章开头那个案例。
|
1 |
mysql> SELECT * FROM users WHERE username = 'a'+'b' AND password = 'a'+'b'; |
在查询的时候数值进行隐式转换和比较，比如第一条记录的username过程应该是这样的，select ‘admin’=’a’+’b’结果自然为1，password以此类推。到第二条记录的时候，select ‘666admin’=’a’+’b’结果自然为0（字符串666admin即不等1也不等于0），进一步分析select 666=’a’+’b’，结果为假，数值就是0，所以开头那个案例的查询结果就是那样的了。
如果需要查询满足第二个条件select ‘666admin’=666这个可以满足，利用如下:
|
1 2 3 4 5 6 7 |
mysql> SELECT * FROM users WHERE username = 'a'+'666' AND password = 'a'+'b'; +--------+----------+--------------------+ | userid | username | password | +--------+----------+--------------------+ | 3 | 666admin | MySuperS3cretPass! | +--------+----------+--------------------+ 1 row in set, 6 warnings (0.00 sec) |
还有更多运算符可以在此利用。
三、隐式转换案例
案例一：int转string
表结构
|
1 2 3 4 5 6 7 8 |
CREATE TABLE `user` ( uid int, mo char(11) NOT NULL DEFAULT '', KEY idx_mo (mo) ) ENGINE=InnoDB; insert into user select 2,123456; insert into user select 2,234567; |
执行计划
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> explain select uid from `user` where mo=123456 limit 0,1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ALL | idx_mo | NULL | NULL | NULL | 4 | 25.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 4 warnings (0.00 sec) mysql> show warnings; +---------+------+---------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------------------------------------------+ | Warning | 1739 | Cannot use ref access on index 'idx_mo' due to type or collation conversion on field 'mo' | | Warning | 1739 | Cannot use range access on index 'idx_mo' due to type or collation conversion on field 'mo' | | Note | 1003 | /* select#1 */ select `test`.`user`.`uid` AS `uid` from `test`.`user` where (`test`.`user`.`mo` = 123456) limit 0,1 | +---------+------+---------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) |
可以看见没有转换成功，所以也无法使用索引，那么解决办法如下：
|
1 2 3 4 5 6 7 |
mysql> explain SELECT uid FROM `user` WHERE mo='123456' LIMIT 0,1; +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+ | 1 | SIMPLE | user | NULL | ref | idx_mo | idx_mo | 11 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) |
上述案例中由于表结构定义mo字段后字符串数据类型，而应用传入的则是数字，进而导致了隐式转换，索引无法使用，所以有两种方案：
第一，将表结构mo修改为数字数据类型。
第二，修改应用将应用中传入的字符类型改为数据类型。
案例二：datetime转string
表结构
|
1 2 3 4 5 6 7 8 |
CREATE TABLE `test_date` ( `id` int(11) DEFAULT NULL, `gmt_create` varchar(100) DEFAULT NULL, KEY `idx_gmt_create` (`gmt_create`) ) ENGINE=InnoDB; insert into test_date select 1,'2017-05-15 17:32:23'; insert into test_date select 2,'2017-05-16 17:32:23'; |
执行计划
|
1 2 3 4 5 6 7 |
mysql> explain select * from test_date where gmt_create BETWEEN DATE_ADD(NOW(),INTERVAL - 1 MINUTE) AND DATE_ADD(NOW(),INTERVAL 15 MINUTE); +----+-------------+-----------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test_date | NULL | ALL | idx_gmt_create | NULL | NULL | NULL | 2 | 50.00 | Using where | +----+-------------+-----------+------------+------+----------------+------+---------+------+------+----------+-------------+ 1 row in set, 2 warnings (0.01 sec) |
解决办法
|
1 2 3 |
mysql> alter table test_date change gmt_create gmt_create datetime; Query OK, 2 rows affected (0.05 sec) Records: 2 Duplicates: 0 Warnings: 0 |
|
1 2 3 4 5 6 7 |
mysql> explain select * from test_date where gmt_create BETWEEN DATE_ADD(NOW(),INTERVAL - 1 MINUTE) AND DATE_ADD(NOW(),INTERVAL 15 MINUTE); +----+-------------+-----------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | test_date | NULL | range | idx_gmt_create | idx_gmt_create | 6 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-----------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) |
数据类型定义错误隐式转换，导致数据库cpu压力100%，所以我们在定义时间字段的时候一定要采用时间类型的数据类型。
案例三：字符集转换
表结构
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE `t1` ( `c1` varchar(100) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `c2` varchar(100) DEFAULT NULL, KEY `idx_c1` (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `t2` ( `c1` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `c2` varchar(100) DEFAULT NULL, KEY `idx_c2` (`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
执行计划
|
1 2 3 4 5 6 7 8 |
mysql> explain select t1.* from t2 left join t1 on t1.c1=t2.c1 where t2.c2='b'; +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | t2 | NULL | ref | idx_c2 | idx_c2 | 303 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+----------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec) |
修改COLLATE
|
1 2 3 |
mysql> alter table t1 modify column c1 varchar(100) COLLATE utf8_bin; Query OK, 401920 rows affected (2.79 sec) Records: 401920 Duplicates: 0 Warnings: 0 |
执行计划
|
1 2 3 4 5 6 7 8 |
mysql> explain select t1.* from t2 left join t1 on t1.c1=t2.c1 where t2.c2='b'; +----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+-------+ | 1 | SIMPLE | t2 | NULL | ref | idx_c2 | idx_c2 | 303 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | t1 | NULL | ref | idx_c1 | idx_c1 | 303 | test.t2.c1 | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) |
可以看到修改了字段的COLLATE后执行计划使用到了索引，所以一定要注意表字段的collate属性的定义保持一致。
案例四：string转int
上面三个案例都没有转换成功，但是也并不是所有的都无法转换，比如string转int在某些情况下就没有问题。
|
1 2 3 4 5 6 7 8 |
CREATE TABLE `user` ( uid int, mo char(11) NOT NULL DEFAULT '', KEY idx_uid (uid) ) ENGINE=InnoDB; insert into user select 2,123456; insert into user select 2,234567; |
执行计划
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> explain select uid from `user` where uid='1' limit 0,1; +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ref | idx_uid | idx_uid | 5 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +-------+------+-------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `test`.`user`.`uid` AS `uid` from `test`.`user` where (`test`.`user`.`uid` = '1') limit 0,1 | +-------+------+-------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
从结果上看已经转换成功了，并且正常使用了索引。那么如果查询条件是where uid in (‘1’,2)呢？还能转换成功吗？可以看看淘宝月报这篇文章的分析：MySQL · 最佳实践 · 一个“异常”的索引选择
四、索引常见误区
- 误区一：对查询条件的每个字段建立单列索引，例如查询条件为：A=？and B=？and C=？。
在表上创建了3个单列查询条件的索引idx_A(A)，idx_B(B)，idx_C(C)，应该根据条件的过滤性，创建适当的单列索引或者组合索引。
- 误区二：对查询的所有字段建立组合索引，例如查询条件为select A,B,C,D,E,F from T where G=？。
在表上创建了idx_A_B_C_D_E_F_G(A,B,C,D,E,F,G)。
五、索引最佳实践
- 在使用索引时，我们可以通过explain+extended查看SQL的执行计划，判断是否使用了索引以及发生了隐式转换。
- 由于常见的隐式转换是由字段数据类型以及collation定义不当导致，因此我们在设计开发阶段，要避免数据库字段定义，避免出现隐式转换。
- 由于MySQL不支持函数索引，在开发时要避免在查询条件加入函数，例如date(gmt_create)。
- 所有上线的SQL都要经过严格的审核，创建合适的索引。
<参考>
如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。
