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

最新推荐文章于 2025-04-13 14:49:58 发布

kf_panda

最新推荐文章于 2025-04-13 14:49:58 发布

阅读量513

点赞数

分类专栏： MYSQL索引&SQL优化&服务器优化

本文链接：https://blog.youkuaiyun.com/gua___gua/article/details/50819629

版权

MYSQL索引&SQL优化&服务器优化专栏收录该内容

26 篇文章

订阅专栏

本文探讨了MySQL查询优化的实际案例，通过对比不同SQL语句及其执行计划，揭示了如何利用索引提升查询性能，并分析了查询时间范围对执行计划选择的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、环境介绍

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 |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+