查询优化,索引优化,库表机构优化需要齐头并进,一个不落!
查询的生命周期:
- 客户端发送一条查询给服务器
- 服务器先检查缓存,命中缓存则立即返回,否则进入下一阶段
- 服务器进入sql解析,预处理,再由优化器生成对应的执行计划
- mysql根据执行计划,调用存储引擎的api来执行查询
- 将结果返回给客户
查询为什么慢?
- 很多原因:
- 在查询的生命周期中,如网络,CPU计算,生成统计信息和执行计划,锁等待都会导致变慢
- 尤其是向存储引擎检索数据的调用操作
- 还有一些不必要操作,如重复相同的操作。
- 最基本的原因:访问的数据太多!分析低效查询的2个有效步骤!
- 客户端请求了太多没必要的数据行,或者列,增加网络开销,和cpu与内存的损耗
- 常见错误select *,只取自己需要的列!
- mysql为了返回结果,扫描了太多没必要的行
- 扫描的行和返回的行,最好的情况当然是扫描行数和返回的行数一样,一般在1:1和10:1之间会比较好。
- 扫描的行和使用的方式(访问的类型),即访问数据的成本,不同的访问类型成本也不一样
- 通过explain查看type列,sys—>const—>eq_ref—>ref—>range—>index—>all,无非是没用索引,或者没用到高效的索引
- where的三种使用方式
- 在索引中使用where条件过滤不匹配的记录,在存储引擎层实现,效率最好
- 覆盖索引(useing index),直接从索引中取数据,在服务器层完成,但不需要回表查询
- 从数据表中返回数据,然后在服务器层过滤不满足条件的数据(using where)
- 客户端请求了太多没必要的数据行,或者列,增加网络开销,和cpu与内存的损耗
优化特定类型的查询
- 优化max和min
- 使用索引的max和min,可以直接找到索引的头和尾,将整个表达式当成一个常数对待
- count()的优化
- 统计某一列值的数量(不统计null)
- 统计结果集的行数,count(*)
- myisam的神话:只有不加任何where条件的count()才非常快,如果知道某一列不能为null,count(列值)会优化成count()
- 简单优化:适用于myisam表的这个count(*)特别快的特性
- select count() from city where id>5;优化后:select(select count() from city )-count(* ) from city where id<=5;
- 查询某列不同值 的数量:select count(color=‘blue’ or null),count(color=‘red’ or null) from items;
- 优化关联查询
- 保证on连接的列上有索引,一般来说只需要在关键书序的第二个表的相应列上创建索引。如A和B在列C上关联,如果优化器关联顺序是B,A,则在A的列C上创建索引即可。
- 避免join太多表,join会产生临时表,建议不超过5个。
- 优化子查询
- 尽量不适用子查询,尤其是in+子查询。
- 子查询的结果集无法使用索引,通常子查询会产生临时表,不管是内存中还是存盘上的都无法使用索引,导致CPU和I/O损耗较高
- 尽量使用关联查询代替子查询
- 尽量不适用子查询,尤其是in+子查询。
- 优化group by和distinct
- 标识列(自增列)作为分组条件,往往别其他列效率更好
- group by结果集会隐式排序。按照指定按照分组字段排序,如果不关系排序,可以使用order by null。8.0之后就不在支持隐式排序了。
- 优化order by
- 如果进行关联查询+order by,尽量使用第一个表的列做排序的条件。这样在关联处理第一个的时候就进行文件排序,explain的extra字段会有using filesort
- 除此之外的情况,mysql都会先把关联结果放在一个临时表中,然后在所有关联结束后在进行文件排序。extra会有using temporay,using filesort。
- 优化limit
- 尽量使用覆盖索引,使用延迟关联,让mysql扫描尽量少的页面。
- 把limit转换为已知位置的范围查询
- 避免使用offset。记录上一次读取数据的位置,直接从该位置扫描。
- 优化union
- Mysql总是创建并填充临时表的方式来执行union
- 通常把where,limit,order by的条件下沉到子句中。
- 尽量使用union all,除非明确需要消除重复的行。
- 使用用户自定义变量