MYSQL查询性能优化
MYSQL查询性能优化
慢查询的基础
是否想数据库请求了不需要的数据
有些查询会请求超过实际需要的数据,然后这些多余的数据会被应用程序丢弃,这会给MySql服务器带来额外的负担,并增加网络开销,另外也会消耗应用服务器的CPU和内存资源
- 查询不需要的数据(没有带分页,把所有的数据查询出来,然后只取出自己想要的前几条)
- 多表关联的时候返回全部列
- 总是取出全部列 (
select * from
) - 多次查询相同的数据
MYSQL 是否在扫面额外的记录
在确定查询只返回需要的数据以后,接下来就应该看看查询为了返回这些结果是否扫描了过多的数据,最简单的衡量查询开销的三个指标
- 响应时间
- 扫描的行数
- 返回的行数
响应时间
由两部分组成:服务时间和排毒时间。服务时间是指处理这个查询真正话费的时间,排队时间是指服务器因为等待某些资源而没有真正执行查询的时间,可能是在等待I/O操作,也可能是在等待行锁等等,当然实际情况可能会更复杂,响应时间并没有一致的规律或者公式。诸如存储引擎的锁(表锁、行锁)、高并发资源竞争、硬件响应等著作因素都会影响响应时间
扫描的行数和访问类型
在平谷查询开销的时候,需要考虑一下从表中找到某一行数据的成本,在EXPLAIN
语句中的type列反应了访问类型,访问类型有很多,比如:全表扫描,索引扫描,范围扫描,唯一索引扫描,常数引用等等
例如查询SQL
EXPLAIN SELECT * FROM `city` WHERE country_id=87
结果如图
我们可以看到 这个select 这个sql 将返回5行数据 索引名为idx_fk_country_id
type为ref 那如果我们不查索引值而是查其他字段的话,会看到type为all(全表扫描) 预估需要查询数据的条数也更多 当然可能消耗的时间就会更多
重构查询的方式
一个复杂查询还是多个简单查询
分而治之
有时候对于一个大的查询我们需要分而治之,将大查询切分成小查询,每个查询功能完全一样,只完成一小部分,每次只返回一小部分数据
删除旧数据就是一个很好的例子,定期清除大量数据时,如果用一个大的语句一次性完成的话,则可能需要一次性锁住很多数据,占满整个事务日志,耗尽系统资源,阻塞很多小的,但是很重要的查询,所以将一个大的DELETE语句接分成多个较小的del