SQL优化
约 828 字大约 3 分钟
2025-03-16
优化性能
- 批量插入、更新数据
- *避免使用select 语句,第一,实际业务场景中,可能我们真正使用的只有其中一两列;第二,select *语句不会走覆盖索引,会出现大量的回表查询,sql性能低
小表驱动大表
小表驱动大表,即使用小表的数据集驱动大表的数据集
假如有order和user两张表,其中order表有10000条数据,而user表有100条数据。需求:查所有有效的用户下过的订单列表
# in 关键字:优先执行in里面的子查询语句
select * from order
where user_id in (select id from user where status=1)
# exists 关键字
select * from order
where exists (select 1 from user where order.user_id = user.id and status=1)
in
适用于左边大表,右边小表exists
适用于左边小表,右边大表
连接查询代替子查询
子查询语句的优缺点:
- 优点:简单,结构化,适用表数据不多
- 缺点:需要创建临时表,查询完毕之后,再删除这些临时表
# 子查询
select * from order
where user_id in (select id from user where status=1)
# 连接查询
select o.* from order o
inner join user u on o.user_id = u.id
where u.status=1
优化索引
使用explain语句判断sql语句有没有走索引,有 type 、key 、key_len 三个属性需要关注
id:表示SQL的执行顺序,从大到小开始执行
- id相同时,执行顺序由上至下;
- 如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行;
- id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行;
type:数据的访问类型,表示 MySQL 在表中找到所需行的方式,从好到坏依次是:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
- system:const连接类型的一种特例,仅有一行满足条件
- const:当确定最多只会有一行匹配的时候,MySQL优化器会在查询前读取它而且只读取一次,因此非常快。当主键放入where子句时,mysql把这个查询转为一个常量(高效)
- eq_ref:最多只返回一条符合条件的记录,使用唯一性索引和主键查找时发生
- ref:一种索引访问,返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引、或者唯一性索引才会发生
- range:范围扫描,一个有限制的索引扫描,key列显示使用了哪个索引
- index:和全表扫描一样,只是扫描表的时候按照索引次序进行而不是行,避免排序,但是开销还是大
- all:最坏的情况,全表扫描
key:显示 MySQL 实际决定使用的键(索引)。如果没有选择索引,键是 NULL
key_len:显示 MySQL 决定使用的键长度。如果键是 NULL,则长度为 NULL。使用的索引的长度。在不损失精确性的情况下,长度越短越好
rows:预计要检查的行数
Extra: 包含MySQL解决查询的详细信息,也是关键参考项之一。