sql 语句优化实战

sql 语句优化实战

order by

# 为表employees创建联合索引
create index idx_name_age_position on employees(name,age,position)
# using filesort
Explain select * from employees where name='zs' order by position; 
优化后：select * from employees where name='zs' order by age, position
# 满足最左前缀法则，使用了索引树
Explain select * from employees where name='zs' order by age, position;
# 不满足最左前缀法则，使用了文件排序
Explain select * from employees where name='zs' order by position, age;
# 满足最左前缀法则，使用了索引树
Explain select * from employees where name='zs' and age=20 order by position age;
# 和索引树排序方向不同，使用了文件排序
Explain select * from employees where name='zs' and age=20 order by age, position desc;
# 范围查询（让索引name失效了），使用了文件排序
Explain select * from employees where name in ('zs','lise') order by age, position;
# 范围查询，使用了文件排序
Explain select * from employees where name > 'zs' order by name; 
# 该表共有3百万条数据，name > 'zs'的共有250万条，所以就没有必要再去走索引树了，直接遍历即可

总结：

1. 如果排序的字段创建了联合索引，那么尽量在业务不冲突的情况下，遵循最左前缀法则来写排序语句

例如这条语句：

# using filesort
Explain select * from employees where name='zs' order by position; 
优化后使用了索引树：select * from employees where name='zs' order by age, position

2. 如果文件排序没办法避免，那么尽量想办法使用覆盖索引

所谓的覆盖索引，指的是当前查询的所有数据字段都是索引列，这就意味着可以直接从索引列中获取数据，而不需要进行查表

文件排序原理

mysql在执行文件排序的前，会把查询的数据的大小与系统变量（max_length_for_sort_data，默认是1024字节）的大小进行比较,如果比系统变量小，那么执行单路排序，反之则执行双路排序

• 单路排序

把所有的数据扔到sort_buffer内存缓冲区中，进行排序

• 双路排序

取数据的排序字段和主键字段，在内存缓冲区中排序完成后，将主键字段做一次回表查询，获取完整数据

group by优化

group by 的原理是先排序后分组，因此对 group by 的优化参考 order by

limit 分页优化

# 以下这条sql，会扫描1234567+10条数据，然后去掉不符合条件的1234567数据，所以性能很差，需要优化
Explain select * from employees order by name limit 1234567,10
-- 优化：通过先进行覆盖索引的查找，然后在使用join做连接查询获取所有数据，这样比全表扫描要快
explain select * from employees a inner join (select id from employees order by name limit 1234567,10)  b on a.id = b.id

join优化

在join中会涉及到大表和小表的概念，MySQL内部优化器会根据关联字段是否创建了索引来使用不同的算法：

• Nlj(嵌套循环算法)：如果关联字段使用了索引（大小表的关联字段都得有索引），mysql会对小表做全表扫描，用小表的数据去和大表的数据去做索引字段的关联查询（type：ref）

• bnlj（块嵌套循环算法）：如果关联字段没有使用索引（大小表的关联字段没有索引），mysql会提供一个 join buffer缓冲区，先把小表放到缓冲区中，然后全表扫描大表，把大表的数据和缓冲区中的小表数据在内存中进行匹配

结论：使用join查询时，一定要建立关联字段的索引（大小表的关联字段都得建索引），且两张表的关联字段在设计之初就要做到字段类型和长度是一致的，否则索引失效

in和exists优化

在sql中如果A表是大表，B表是小表，那么使用in会更加合适，反之应该使用exists

原因是：sql语句中包含了in关键字,则它会优先执行in右边的子查询,然后再执行in左面的查询，这样做的原因是in左面的数据量很少,作为条件查询速度更快，而exists刚好相反，它优先执行exists左边的语句，因为exists左边的是小表

• in: B的数据量<A的数据量

select * from A where id in (select id from B) 

• exists: B的数据量>A的数据量

select * from A where exists (select 1 from B where B.id = A.id)  true / false
# 先去查询a表的数据，但是需要满足exists后面的条件，条件为：a表的id=b表的id

count优化

对于count的优化应该是架构层面的优化，因为count的统计出现的频率异常之高，所以对于访问频率过高的数据建议维护在缓存中，例如Redis中

用union all代替union

union会去重，union all不会去重，但是union会去重过程需要遍历、排序和比较,会更消耗cpu资源

批量插入

增量查询

有时候，我们需要通过远程接口查询数据，然后同步到另外一个数据库

错误做法：

select * from user;

如果直接获取所有的数据,然后同步过去，这样虽说非常方便,但是带来了一个非常大的问题，就是如果数据很多的话,查询性能会非常差

正确做法：

按id和时间升序，每次只同步一批数据，同步完成之后，保存这这批条数据中最大的id和时间，然后给同步下一批数据时使用

select * from user
where id > #{lastId} and create_time >= #{lastcreateTime} limit 100;