# MySQL的SQL优化

数据库到达百万条数据后,查询语句不能随意编写,要注意合理利用索引,防止出现慢查询

# 1. Explain

待补充

# 2. 优化方式

  • 排序字段必须使用索引,多表关联要以主表字段排序
  • 多表关联 Left Join 其他表的时候,如果以其他表的字段作为查询条件都会产生临时表

# 3. 联合索引

两个或更多个列上的索引被称作联合索引,联合索引又叫复合索引。对于复合索引:Mysql 从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分,按最左匹配

例如索引是 key index (a,b,c),可以支持[a]、[a,b]、[a,b,c] 3种组合进行查找,但不支 [b,c] 进行查找。当最左侧字段是常量引用时,索引就十分有效

MySql 8.0 跳跃式索引(Skip Scan Index),联合索引左边的索引列的值可能性只有几种,不是很多,这样会触发跳跃式索引,就算 SQL 没有加上当前索引条件,也会命中后面的列,所以推荐把出现值少的列放左边,当然也要依据查询条件字段情况考虑,例如 key index (a,b,c),当 a 字段的值可能性只有几种,类似性别,查找 [b,c] 时,索引也会生效

# 4. Limit越大越慢

SQL与执行时间

select * from user where sex = 1 limit 100, 10;	OK, Time: 0.005000s
select * from user where sex = 1 limit 1000, 10;	OK, Time: 0.007000s
select * from user where sex = 1 limit 10000, 10;	OK, Time: 0.016000s
select * from user where sex = 1 limit 100000, 10;	OK, Time: 0.169000s
select * from user where sex = 1 limit 1000000, 10;	OK, Time: 5.892000s
select * from user where sex = 1 limit 10000000, 10;	OK, Time: 33.465000s

优化后的SQL执行时间

select * from user a join (select id from user where sex = 1 limit 100, 10) b on a.id=b.id;	OK, Time: 0.000000s
select * from user a join (select id from user where sex = 1 limit 1000, 10) b on a.id=b.id;	OK, Time: 0.00000s
select * from user a join (select id from user where sex = 1 limit 10000, 10) b on a.id=b.id;	OK, Time: 0.002000s
select * from user a join (select id from user where sex = 1 limit 100000, 10) b on a.id=b.id;	OK, Time: 0.015000s
select * from user a join (select id from user where sex = 1 limit 1000000, 10) b on a.id=b.id;	OK, Time: 0.151000s
select * from user a join (select id from user where sex = 1 limit 10000000, 10) b on a.id=b.id;	OK, Time: 1.161000s

参考

上次更新时间: 2023-12-15 03:14:55