mysql sql中类型转换引起的索引无法使用的问题

来源：互联网发布：腾讯大数据编辑：程序博客网时间：2024/06/06 17:15

今天开发说有个SQL特别慢，让优化下，我看了下是因为条件字段检索时的类型转换引起的索引不可用。

其中clpp字段和clnk字段都是字符串类型，他们当做数字类型使用。

原SQL:

 SELECT jlbh,xzqh,kkbh,cdbh,hphm,hpzl,hpys,jgsj,clsd,wzlx,cllx,csys,clpp,clzpp,clnk,facenum_,sunvisornum_,sjly FROM sjkk_gcjl s WHERE 1=1 AND jgsj>='2015-01-28 00:00:00.0' AND jgsj<='2015-09-28 16:12:01.0' AND clzpp IN ( '50002' ) AND clpp = 5 AND clnk = 16384 ORDER BY jgsj DESC  LIMIT 100;

下面实验可以看到这个查询根本用不到索引了(即使建议数据库使用index15)，

mysql> explain SELECT jlbh,xzqh,kkbh,cdbh,hphm,hpzl,hpys,jgsj,clsd,wzlx,cllx,csys,clpp,clzpp,clnk,facenum_,sunvisornum_,sjly     ->  FROM sjkk_gcjl s use index(index15) WHERE 1=1 AND jgsj>='2015-01-28 00:00:00.0' AND jgsj<='2015-09-28 16:12:01.0' AND clzpp = '50002' AND clpp = 5 AND clnk = 16384 ORDER BY jgsj DESC  LIMIT 100;+----+-------------+-------+------+---------------+------+---------+------+-----------+-----------------------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows      | Extra                       |+----+-------------+-------+------+---------------+------+---------+------+-----------+-----------------------------+|  1 | SIMPLE      | s     | ALL  | index15       | NULL | NULL    | NULL | 794092921 | Using where; Using filesort |+----+-------------+-------+------+---------------+------+---------+------+-----------+-----------------------------+mysql>SELECT jlbh,xzqh,kkbh,cdbh,hphm,hpzl,hpys,jgsj,clsd,wzlx,cllx,csys,clpp,clzpp,clnk,facenum_,sunvisornum_,sjly     ->  FROM sjkk_gcjl s use index(index15) WHERE 1=1 AND jgsj>='2015-01-28 00:00:00.0' AND jgsj<='2015-09-28 16:12:01.0' AND clzpp = '50002' AND clpp = 5 AND clnk = 16384 ORDER BY jgsj DESC  LIMIT 100;....十几分钟都出不来结果

下面实验将clpp和clnk的条件改为字符串就可以使用索引了。这时可以使用到index15索引的（`clpp`,`clzpp`,`clnk`,`jgsj`）这些部分

mysql> explain SELECT jlbh,xzqh,kkbh,cdbh,hphm,hpzl,hpys,jgsj,clsd,wzlx,cllx,csys,clpp,clzpp,clnk,facenum_,sunvisornum_,sjly  FROM sjkk_gcjl s use index(index15) WHERE 1=1 AND jgsj>='2015-01-28 00:00:00.0' AND jgsj<='2015-09-28 16:12:01.0' AND clzpp = '50002' AND clpp = '5' AND clnk = '16384' ORDER BY jgsj DESC  LIMIT 100;+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+|  1 | SIMPLE      | s     | range | index15       | index15 | 76      | NULL |    3 | Using where |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+mysql> SELECT jlbh,xzqh,kkbh,cdbh,hphm,hpzl,hpys,jgsj,clsd,wzlx,cllx,csys,clpp,clzpp,clnk,facenum_,sunvisornum_,sjly  FROM sjkk_gcjl s use index(index15) WHERE 1=1 AND jgsj>='2015-01-28 00:00:00.0' AND jgsj<='2015-09-28 16:12:01.0' AND clzpp = '50002' AND clpp = '5' AND clnk = '16384' ORDER BY jgsj DESC  LIMIT 100;.....30 rows in set (0.01 sec)

如果只将clpp改为字符串，clnk条件该类型还是整数（即与数据库中的clnk不匹配），虽然有可以使用index15，但只能使用到索引的（`clpp`,`clzpp`）部分

mysql> explain SELECT jlbh,xzqh,kkbh,cdbh,hphm,hpzl,hpys,jgsj,clsd,wzlx,cllx,csys,clpp,clzpp,clnk,facenum_,sunvisornum_,sjly  FROM sjkk_gcjl s use index(index15) WHERE 1=1 AND jgsj>='2015-01-28 00:00:00.0' AND jgsj<='2015-09-28 16:12:01.0' AND clzpp = '50002' AND clpp = '5' AND clnk = 16384 ORDER BY jgsj DESC  LIMIT 100;+----+-------------+-------+------+---------------+---------+---------+-------------+--------+-----------------------------+| id | select_type | table | type | possible_keys | key     | key_len | ref         | rows   | Extra                       |+----+-------------+-------+------+---------------+---------+---------+-------------+--------+-----------------------------+|  1 | SIMPLE      | s     | ref  | index15       | index15 | 35      | const,const | 809186 | Using where; Using filesort |+----+-------------+-------+------+---------------+---------+---------+-------------+--------+-----------------------------+mysql> explain SELECT jlbh,xzqh,kkbh,cdbh,hphm,hpzl,hpys,jgsj,clsd,wzlx,cllx,csys,clpp,clzpp,clnk,facenum_,sunvisornum_,sjly  FROM sjkk_gcjl s use index(index15) WHERE 1=1 AND jgsj>='2015-01-28 00:00:00.0' AND jgsj<='2015-09-28 16:12:01.0' AND clzpp = '50002' AND clpp = '5' AND clnk = 16384 ORDER BY jgsj DESC  LIMIT 100;...十几分钟都出不来结果

总结
所以在写sql的时候，条件中的数据类型一定要与数据库中的类型一致，否则数据类型转换会引起用不到索引。

0 0