今天开发说有个SQL特别慢,让优化下,我看了下是因为条件字段检索时的类型转换引起的索引不可用。
下面实验可以看到这个查询根本用不到索引了(即使建议数据库使用index15),
下面实验将clpp和clnk的条件改为字符串就可以使用索引了。这时可以使用到index15索引的 (`clpp`,`clzpp`,`clnk`,`jgsj`)这些部分
其中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的时候,条件中的数据类型一定要与数据库中的类型一致,否则数据类型转换会引起用不到索引。