优化步骤:下图示例中为了对比时间,临时使用 LIMIT 6000,10
实际使用的时候应该是: LIMIT 0,10
-- 直接LIMIT
SELECT * FROM biz_target_user LIMIT 230000,10;
-- 主键为自增字段:只查询id > 230000后的数据
SELECT * FROM biz_target_user where id > 230000 LIMIT 0,10;
-- 主键为自增字段:先查询符合条件的主键,使用RIGHT JOIN查询其他字段
SELECT mainTable.* FROM biz_target_user mainTable RIGHT JOIN (SELECT id FROM biz_target_user where id > 230000 LIMIT 0,10) tmpTable on mainTable.id = tmpTable.id ;
一、where条件中 使用带索引的字段:
将条件查询中使用的字段,建立索引。
二、查询到主键后 再查询其他字段、善用自增主键(即where条件筛选数据)
参考
https://mp.weixin.qq.com/s/LSNO-B30B3eaaru0emNkVA
https://mp.weixin.qq.com/s/FObWr9j7uNjhzDoluEXJAQ
-- 优化前SQL
SELECT 各种字段
FROM `table_name`
WHERE 各种条件
LIMIT 0,10;
先查询到主键,减少其他字段的查询 节省查询资源, 再根据主键查询其他字段
-- 优化后SQL
SELECT 各种字段
FROM `table_name` main_tale
RIGHT JOIN
(
SELECT 子查询只查主键
FROM `table_name`
WHERE 各种条件
LIMIT 0,10
) temp_table ON temp_table.主键 = main_table.主键
测试limit 查询500w数据
直接查询 耗时66秒
优化后 耗时24秒
如果主键ID是自增主键,则可以进一步优化:锁定查询范围 预估查询的数据ID在某个范围内,使用主键将范围限定后查询
-- 只查询主键值大于75000000的数据
select e.* from xp_task_execute e
RIGHT JOIN
(
SELECT execute_id
FROM xp_task_execute
WHERE execute_id > 75000000 and client_id is not null and finish_time is not null order by finish_time desc
LIMIT 0,200
) t ON t.execute_id = e.execute_id;