优化MYSQL代码以获得更好的执行时间
优化MYSQL代码以获得更好的执行时间
提问于 2012-09-26 15:23:28
这段mysql代码
SELECT id, value, LENGTH(stuffing)
FROM t_limit ORDER BY id LIMIT 150000, 10可以通过这样重写来优化以获得更好的性能。
注:表中有Id索引
SELECT l.id, value, LENGTH(stuffing)
FROM (
SELECT id
FROM t_limit
ORDER BY
id
LIMIT 150000, 10
) o
JOIN t_limit l
ON l.id = o.id
ORDER BY
l.idRef:http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
现在，如何以类似的方式优化这段代码
SELECT id, value, LENGTH(stuffing)
FROM t_limit where value>100 ORDER BY id LIMIT 150000, 10回答 2
Stack Overflow用户
回答已采纳
发布于 2012-09-27 15:00:22
上述文章中提出的优化的基本思想是只查询索引页而不触及数据页。如果您查看非优化查询的查询计划：
SELECT id, value, LENGTH(stuffing) AS len
FROM t_limit
ORDER BY
id
LIMIT 150000, 10它将是：
+----+-------------+---------+------+---------------+------+---------+------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+---------+------+---------------+------+---------+------+--------+----------------+
| 1 | SIMPLE | t_limit | ALL | NULL | NULL | NULL | NULL | 200000 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+--------+----------------+所以这是个简单的桌子扫描。通过子查询优化，我们得到：
+----+-------------+------------+--------+-------------------------------+---------+---------+------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+-------------------------------+---------+---------+------+--------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 10 | Using temporary; Using filesort |
| 1 | PRIMARY | l | eq_ref | PRIMARY | PRIMARY | 4 | o.id | 1 | |
| 2 | DERIVED | t_limit | index | NULL | PRIMARY | 4 | NULL | 150010 | Using index |
+----+-------------+------------+--------+-------------------------------+---------+---------+------+--------+---------------------------------+查看key列，它显示index语句使用了主索引。我稍微修改了您的查询，因此值类型是兼容的：
SELECT l.id, value, LENGTH(stuffing) AS len
FROM (
SELECT id
FROM t_limit
where value like 'Value 1%'
ORDER BY
id
LIMIT 30000, 10
) o
JOIN t_limit l
ON l.id = o.id
ORDER BY
l.id您需要考虑where条件的作用。如果将它放置在外部查询中，您将只过滤从内部查询返回的10行--我想这不是您所要求的。现在，在提供的情况下(内部语句中的where条件)，您将得到一个表扫描，因为没有索引可以完成您的查询：
+----+-------------+------------+--------+---------------+---------+---------+------+--------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------+--------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 10 | Using temporary; Using filesort |
| 1 | PRIMARY | l | eq_ref | PRIMARY | PRIMARY | 4 | o.id | 1 | |
| 2 | DERIVED | t_limit | ALL | NULL | NULL | NULL | NULL | 200000 | Using filesort |
+----+-------------+------------+--------+---------------+---------+---------+------+--------+---------------------------------+为了从博客文章中显示的同样的优化中获利，您需要一个额外的非聚集索引，例如。
create index NCIX_t_limit_id_value on t_limit(id, value)现在，当您运行上述查询时，计划如下：
+----+-------------+------------+--------+-------------------------------+-----------------------+---------+------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+-------------------------------+-----------------------+---------+------+-------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 10 | Using temporary; Using filesort |
| 1 | PRIMARY | l | eq_ref | PRIMARY,NCIX_t_limit_id_value | PRIMARY | 4 | o.id | 1 | |
| 2 | DERIVED | t_limit | index | NULL | NCIX_t_limit_id_value | 66 | NULL | 30010 | Using where; Using index |
+----+-------------+------------+--------+-------------------------------+-----------------------+---------+------+-------+---------------------------------+再说一遍，我们只是扫描索引页。
Stack Overflow用户
发布于 2012-09-27 14:04:32
您可以将查询编写为：
SELECT l.id, value, LENGTH(stuffing)
FROM (SELECT id
FROM t_limit
WHERE value > 100
ORDER BY id
LIMIT 150000, 10
) o JOIN
t_limit l
ON l.id = o.id
ORDER BY l.id然而，这不会提高性能。MySQL必须读取数据页才能获得具有正确值的行。
还有其他索引可以放在上面：(value)、(value、id)和(id，value)。这将对性能产生不同的影响。
第一个将使用索引来满足WHERE子句，然后基本上忽略索引的"id“部分。如果value > 100具有很高的选择性(例如，只有不到1%的记录符合这一标准)，这将提高性能。
第二种可能会有帮助。老实说，我不知道MySQL是否会从索引中读取ids，然后进行排序。或者，如果它会读取原始数据。如果是第一个，这会有帮助的。
第三种可能是最好的选择。我认为MySQL将读取索引，查找匹配的值，然后使用索引进行排序和限制。我的意思是，它应该，但我不是百分之百肯定引擎将真正做到这一点。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/12605137
复制相关文章
相似问题
腾讯云开发者
