显示的执行计划与实际不一致，并且速度奇慢

今天遇到一个问题，explain看到执行计划是没问题，但是执行起来速度奇慢，先看下问题吧。

说明下环境，centos 6.5   32G内存  表数据量 8亿多

mysql> explain select * from sjkk_gcjl where jgsj>='2010-01-20 00:00:00' AND  jgsj<='2015-05-20 00:00:00'  and hpys = '2' and csys='A' ORDER BY jgsj DESC,jlbh DESc;
+----+-------------+-----------+------+---------------------------------+---------+---------+-------------+---------+-------------+
| id | select_type | table     | type | possible_keys                   | key     | key_len | ref         | rows    | Extra       |
+----+-------------+-----------+------+---------------------------------+---------+---------+-------------+---------+-------------+
|  1 | SIMPLE      | sjkk_gcjl | ref  | index08,index09,index10,index11 | index09 | 22      | const,const | 4875345 | Using where |
+----+-------------+-----------+------+---------------------------------+---------+---------+-------------+---------+-------------+

 select * from sjkk_gcjl where jgsj>='2010-01-20 00:00:00' AND  jgsj<='2015-05-20 00:00:00'  and hpys = '2' and csys='A' ORDER BY jgsj DESC,jlbh DESc;
 ......
 100 rows in set (6min 36sec)

KEY `index09` (`csys`,`hpys`,`jgsj`,`jlbh`)；
从执行计划看是没有问题的，index09已经避免了排序，并且特别适合这个sql，理论不应该这么慢的,怀疑数据库真正执行时走的不是index09.

强制使用index09,发现在毫秒级返结果,这可以确定上面的猜想，数据库实际没有使用index09.

select * from sjkk_gcjl force index(index09) where jgsj>='2010-01-20 00:00:00' AND  jgsj<='2015-05-20 00:00:00'  and hpys = '2' and csys='A' 
ORDER BY jgsj DESC,jlbh DESC;
.....
<pre name="code" class="html">100 rows in set (0.00 sec)

看看索引的统计信息,发现Cardinality全是NULl。

mysql> show index from sjkk_gcjl;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sjkk_gcjl |          1 | index09  |            1 | csys        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| sjkk_gcjl |          1 | index09  |            2 | hpys        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| sjkk_gcjl |          1 | index09  |            3 | jgsj        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| sjkk_gcjl |          1 | index09  |            4 | jlbh        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
.....

收集下统计信息。

analyze table sjkk_gcjl;
+-------------------------+---------+----------+----------+
| Table                   | Op      | Msg_type | Msg_text |
+-------------------------+---------+----------+----------+
| changzhou_9yi.sjkk_gcjl | analyze | status   | OK       |
+-------------------------+---------+----------+----------+
1 row in set (1 hour 7 min 55.56 sec)

再次查看索引的统计信息。

mysql> show index from sjkk_gcjl;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sjkk_gcjl |          1 | index09  |            1 | csys        | A         |          56 |     NULL | NULL   |      | BTREE      |         |               |
| sjkk_gcjl |          1 | index09  |            2 | hpys        | A         |         224 |     NULL | NULL   |      | BTREE      |         |               |
| sjkk_gcjl |          1 | index09  |            3 | jgsj        | A         |   199409376 |     NULL | NULL   |      | BTREE      |         |               |
| sjkk_gcjl |          1 | index09  |            4 | jlbh        | A         |   797637506 |     NULL | NULL   |      | BTREE      |         |               |
......

再次执行，毫秒级返回结果。

select * from sjkk_gcjl  where jgsj>='2010-01-20 00:00:00' AND  jgsj<='2015-05-20 00:00:00'  and hpys='2' and csys='A' ORDER BY jgsj DESC,jlbh DESC LIMIT 100;
....
100 rows in set (0.00 sec)