mysql 上亿条数据sql慢查询优化，关联查询优化

单表分库数量上亿，慢查询优化

1.大数据量单表排序查询

select a.id,a.name,... 
		from table a 
		where a.itemNo ='1234332' 
		order by a.createTime desc
		limit 0,10

数据库测试，扫描512.22k行，竟然13秒

这是由于大数据量排序导致的

在不改变表机构的情况下优化，优化查询字段看看效果时候明显

测试单查主键

select a.id	from table a 
		where a.itemNo ='1234332' 
		order by a.createTime desc
		limit 0,10

优化查询字段效果明显，优化后sql为

select a.id,a.name,... 
		from table a 
		right join (select a.id	from table where a.itemNo ='1234332' 
		order by a.createTime desc
		limit 0,10) b on a.id = b.id

测试执行

和查询id一样，完美解决！

注意：这里是right join ，使用inner join 会直接卡住

2. 单表分页优化

#查询时间 0.024s
select a.id,a.name... from table a limit 0,10;

#查询数据页码较大时
select a.id,a.name... from table a limit 500000, 10
#扫描行数较多，查询时间较长 6.897s

#优化后
select a.id,a.name... from table a 
	where a.id > (select id from table a limit 500000,1) 
	limit 10
#查询时间 0.321s

3.业务实现：未转发，为分享，未查看

#使用not exist
select a.id,a.name... from table a 
		where  NOT EXISTS (select b.tableId from userTable b 
		where b.userId = 'weasd' and b.tableId = a.id)
		limit 10

当上述问题重a表数据很大时，not Exist 转left join 优化

#使用left join
select a.id,a.name... from table a left join  userTable b 
		on b.userId = 'weasd' and b.tableId = a.id
		where b.id is null 
		limit 10

4.关联查询优化

小表驱动大表，是关联查询常见的使用，当数据量达到几十万的时候也会慢

SELECT b.image, b.video,...
FROM t_small_table a
	JOIN t_big_table b ON a.userId = b.userId AND a.bigId = b.bigId
WHERE a.createTime < 1615292025398
	AND a.userId IN ('1234', '4567', '891011')
ORDER BY a.createTime DESC
LIMIT 0, 10

上述sql 扫描行647.25K，执行时间为2.982s，虽然只返回了10行

优化后

SELECT b.image, b.video,...
	(SELECT userId,bigId FROM t_small_table 
	WHERE createTime < 1615292025398 AND userId IN ('1234', '4567', '891011')
		ORDER BY a.createTime DESC) a
	JOIN t_big_table b ON a.userId = b.userId  AND a.bigId = b.bigId
ORDER BY a.createTime DESC
LIMIT 0, 10

优化后查询时间为0.046s