mysql 执行计划不对_mysql tokudb执行计划走的不准确案例

一、环境介绍

mysql版本：5.5.41-tokudb-7.5.5

表相关字段：

`jlbh` varchar(31) NOT NULL,

`hpys` varchar(1) NOT NULL,

`jgsj` datetime NOT NULL,

表相关索引：

KEY `index10` (`hpys`,`jgsj`,`jlbh`,`hphm`,`kkbh`,`clpp`,`clzpp`,`clsd`)

表引擎：TokuDB

统计信息已收集

二、SQL1

SELECT

jlbh,

kkbh,

hphm,

jgsj,

clsd

FROM

sjkk_gcjl s

WHERE

1 = 1

AND hphm LIKE '%77'

AND jgsj >= '2015-11-10 19:59:59'

AND jgsj <= '2015-11-10 23:59:59'

AND hpys = '2'

ORDER BY

jgsj DESC,

jlbh DESC

LIMIT 100;

5min以上还没有出结果

执行计划：

+----+-------------+-------+------+-------------------------+---------+---------+-------+---------+--------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+-------------------------+---------+---------+-------+---------+--------------------------+

| 1 | SIMPLE | s | ref | index04,index10,index14 | index10 | 5 | const | 2394655 | Using where; Using index |

+----+-------------+-------+------+-------------------------+---------+---------+-------+---------+--------------------------+

三、SQL2

SELECT

jlbh,

kkbh,

hphm,

jgsj,

clsd

FROM

sjkk_gcjl s FORCE INDEX(INDEX10)

WHERE

1 = 1

AND hphm LIKE '%77'

AND jgsj >= '2015-11-10 19:59:59'

AND jgsj <= '2015-11-10 23:59:59'

AND hpys = '2'

ORDER BY

jgsj DESC,

jlbh DESC

LIMIT 100;

100 rows in set (0.10 sec)

执行计划：

+----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+

| 1 | SIMPLE | s | range | index10 | index10 | 13 | NULL | 2396575 | Using where; Using index |

+----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+

疑问：

为啥使用了强制索引后，才key_len才会是13。有什么方法在不使用强制索引来让数据库自己走key_len为13？

四、做测试发现增大SQL中的时间范围，数据库会自己走SQL2中的执行计划。

下面第一个sql数据库默认走的和上面SQL1中的一样，但是只增大了查询时间范围，数据库就会走上面SQL2中的执行计划。

SELECT

jlbh,

kkbh,

hphm,

jgsj,

clsd

FROM

sjkk_gcjl s

WHERE

1 = 1

AND hphm LIKE '%77'

AND jgsj >= '2015-11-10 10:05:35'

AND jgsj <= '2015-11-10 14:05:35'

AND hpys = '2'

ORDER BY

jgsj DESC,

jlbh DESC

LIMIT 100;

5分钟不出结果...

+----+-------------+-------+------+-------------------------+---------+---------+-------+---------+--------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+-------------------------+---------+---------+-------+---------+--------------------------+

| 1 | SIMPLE | s | ref | index04,index10,index14 | index10 | 5 | const | 2397854 | Using where; Using index |

+----+-------------+-------+------+-------------------------+---------+---------+-------+---------+--------------------------+

SELECT

jlbh,

kkbh,

hphm,

jgsj,

clsd

FROM

sjkk_gcjl s

WHERE

1 = 1

AND hphm LIKE '%77'

AND jgsj >= '2015-10-01 14:05:35'

AND jgsj <= '2015-11-10 14:05:35'

AND hpys = '2'

ORDER BY

jgsj DESC,

jlbh DESC

LIMIT 100;

100 rows in set (0.10 sec)

+----+-------------+-------+-------+-------------------------+---------+---------+------+-----------+--------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+-------------------------+---------+---------+------+-----------+--------------------------+

| 1 | SIMPLE | s | range | index04,index10,index14 | index10 | 13 | NULL | 254032046 | Using where; Using index |

+----+-------------+-------+-------+-------------------------+---------+---------+------+-----------+--------------------------+

对上面第一个sql使用强制索引让其走第二个执行计划，也会很快。

SELECT

jlbh,

kkbh,

hphm,

jgsj,

clsd

FROM

sjkk_gcjl s force index(index10)

WHERE

1 = 1

AND hphm LIKE '%77'

AND jgsj >= '2015-11-10 10:05:35'

AND jgsj <= '2015-11-10 14:05:35'

AND hpys = '2'

ORDER BY

jgsj DESC,

jlbh DESC

LIMIT 100;

100 rows in set (0.11 sec)

+----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+

| 1 | SIMPLE | s | range | index10 | index10 | 13 | NULL | 2397854 | Using where; Using index |

+----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+