mysql执行代码段_优化这段MYSQL代码以获得更好的执行时间

在上述帖子中提出的优化背后的基本思想是仅查询索引页面而不触及数据页面。如果查看非优化查询的查询计划：

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 | | 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列，该列显示最内层语句使用PRIMARY索引。我略微修改了您的查询，因此值类型兼容：

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

+----+-------------+------------+--------+-------------------------------+-----------------------+---------+------+-------+---------------------------------+我们再次只扫描索引页面。