【MYSQL】所有查询表中有2千万数据--sql如何优化

mumu_wangwei

已于 2022-06-30 04:44:46 修改

阅读量326

点赞数

分类专栏： PHP 工作笔记

于 2022-06-28 05:14:17 首次发布

本文链接：https://blog.csdn.net/weixin_43224306/article/details/125494984

版权

mysql sql 数据库

工作笔记同时被 2 个专栏收录

86 篇文章 4 订阅

订阅专栏

PHP

28 篇文章 0 订阅

订阅专栏

这条sql如何优化
所有查询表中有2千万数据

SELECT
	* 
FROM
	(
		( SELECT * FROM q_user_01 WHERE id < 1162922 ORDER BY create_time DESC LIMIT 10 ) UNION ALL
		( SELECT * FROM q_user_02 WHERE id < 1162922 ORDER BY create_time DESC LIMIT 10 ) UNION ALL
		( SELECT * FROM q_user_03 WHERE id < 1162922 ORDER BY create_time DESC LIMIT 10 ) UNION ALL
		( SELECT * FROM q_user_04 WHERE id < 1162922 ORDER BY create_time DESC LIMIT 10 ) UNION ALL
		( SELECT * FROM q_user_05 WHERE id < 1162922 ORDER BY create_time DESC LIMIT 10 ) UNION ALL
		( SELECT * FROM q_user_06 WHERE id < 1162922 ORDER BY create_time DESC LIMIT 10 ) UNION ALL
		( SELECT * FROM q_user_07 WHERE id < 1162922 ORDER BY create_time DESC LIMIT 10 ) UNION ALL
		( SELECT * FROM q_user_08 WHERE id < 1162922 ORDER BY create_time DESC LIMIT 10 ) UNION ALL
		( SELECT * FROM q_user_09 WHERE id < 1162922 ORDER BY create_time DESC LIMIT 10 ) UNION ALL
		( SELECT * FROM q_user_10 WHERE id < 1162922 ORDER BY create_time DESC LIMIT 10 ) 
	) AS u 
WHERE
	u.id < 1162922 
ORDER BY
	u.create_time DESC 
	LIMIT 10

执行时间:2.913s

SELECT
	* 
FROM
	(
		( SELECT * FROM q_user_01 WHERE id < 1162922 ORDER BY id DESC LIMIT 10 ) UNION ALL
		( SELECT * FROM q_user_02 WHERE id < 1162922 ORDER BY id DESC LIMIT 10 ) UNION ALL
		( SELECT * FROM q_user_03 WHERE id < 1162922 ORDER BY id DESC LIMIT 10 ) UNION ALL
		( SELECT * FROM q_user_04 WHERE id < 1162922 ORDER BY id DESC LIMIT 10 ) UNION ALL
		( SELECT * FROM q_user_05 WHERE id < 1162922 ORDER BY id DESC LIMIT 10 ) UNION ALL
		( SELECT * FROM q_user_06 WHERE id < 1162922 ORDER BY id DESC LIMIT 10 ) UNION ALL
		( SELECT * FROM q_user_07 WHERE id < 1162922 ORDER BY id DESC LIMIT 10 ) UNION ALL
		( SELECT * FROM q_user_08 WHERE id < 1162922 ORDER BY id DESC LIMIT 10 ) UNION ALL
		( SELECT * FROM q_user_09 WHERE id < 1162922 ORDER BY id DESC LIMIT 10 ) UNION ALL
	( SELECT * FROM q_user_10 WHERE id < 1162922 ORDER BY id DESC LIMIT 10 )) AS u 
WHERE
	u.id < 1162922 
ORDER BY
	u.create_time DESC 
	LIMIT 10

执行时间: 0.005s

where 字段加索引，order by 字段加索引.
where 字段索引优先于order by索引字段
index （name,pwd,id）符合索引，where字段在前