- 查询慢的原因:
- 优化数据访问:
- 执行过程的优化:
- 优化特定类型的查询:
文件排序:查询的数据量多
mysql> explain select rental_id,staff_id from rental where rental_date > '2005-05-25' order by rental_date,inventory_id;
索引排序:查询的数据量少
mysql> explain select rental_id,staff_id from rental where rental_date > '2006-05-25' order by rental_date,inventory_id;
- 向数据库请求不需要的数据(这些都是不可取的)
查询不需要的数据
多表关联的时候返回全部列
总是取出全部数据
重复查询相同的数据
- 执行过程的优化
查询缓存:
查询优化处理:语法解析器和预处理
mysql查询完缓存之后,会经过以下几个步骤,解析sql、预处理、SQL优化执行计划,这几个步骤出现任何错误,都会终止查询;
语法解析器:mysql通过关键字将sql进行解析,并生成一棵解析树SAB TREE,mysql解析器将使用mysql语法规则进行验证和解析查询,例如验证使用了错误的关键字或者顺序是否正确,预处理会进一步验证解析树是否合法,例如:表名和列名是否存在,是否有歧义、还会验证权限等等;
查询优化器:当语法树没有问题之后,由优化器将其转化成执行计划,一条查询语句可以使用非常多的执行方式,最后都可以得到相应的结果,但是不同的执行计划带来的执行效率是不同的,优化器最主要的目的是选择最有效的执行方式。mysql使用的是基于成本(CBU)的优化器,在优化的时候,会尝试预测一个查询使用查询计划的成本,并选择其中一个查询最小的成本。
- 优化器的优化策略
静态优化(只优化一次):直接对解析树进行分析,并完成优化
动态优化(每次执行都需要重新评估):跟查询的上下文有关,也可能跟取值、索引对应的条数有关
- 优化器的优化类型:
- 重新定义关联表的优化顺序
- 将外连接转化为内连接,内连接的效率要高于内连接
- 使用等价变换规则,mysql可以使用一些等价变换规则来简化并优化表达式
- 优化聚合函数 count(),min(),max(),
- 预估并转化为常数表达式,当mysql检测到一个表达式可以转化为常数的时候,就会把该表达式作为常数处理
- 索引覆盖扫描,当索引中的列包含所有查询中需要使用列的时候,可以使用覆盖索引
- 子查询优化
- 等值传播:explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500 and film_actor.film_id > 500;
- 排序优化
两次传输排序:第一次数据读取是将需要排序的字段取出来,然后进行排序,第二次是将排序好的结果,按照需要去取读取数据行,这种方式效率比较低,原因是第二次读取数据行的时候因为是已经排好序,要去读取所有记录而此时更多的是随机IO,读取成本会比较高,两次传输的优势:在排序的时候,存储尽可能少的数据,让排序缓冲区有尽可能多的容量来容纳排序的行数。
单次传输排序:先读取查询所需要的所有行,然后再根据给定列进行排序,最后直接返回排序结果,此方式只需要一次顺序io读取所有数据,无需任何随机的io,问题在于查询的列特别多的时候,会占用大量的存储空间,无法存储大量的数据。
当需要排序的列的总大小加上order by的列大小超过max_length_for_sort_data定义的字符,mysql会选择两次传输排序,反之使用单次传输排序,用户也可以设置此参数的值来选择排序的方式。
查询缓冲区空间:mysql> show variables like '%max_length_for_sort_data%';
查询所需要的时间:mysql> show status like 'last_query_cost';
- 优化count()查询:
- 总有人认为myisam的count查询比较快,这是有前提条件的,只有where后面没有查询条件的时候才会快;
- 使用近似值;
- 更复杂的优化;直接从缓存取数据
- 优化关联查询:
- 确保on或者using子句上的列有索引,在创建索引的时候,就要考虑到关联的顺序;
- 确保任何的groupby或者orderby的表达式只关联到一个表中的列;
- 优化子查询:
- 使用关联查询代替子查询(子查询查出来的数据放在临时表里,临时表也是io)
- 优化group by 和 distinct
如果对关联查询做分组,并且是对查询表中的某个列进行分组,那么可以使用查询表中的列进行分组效率会高些
mysql> select a.first_name,a.last_name,count(*) from film_actor b inner join actor a using(actor_id) group by a.first_name,a.last_name;
mysql> select a.first_name,a.last_name,count(*) from film_actor b inner join actor a using(actor_id) group by a.actor_id;
- limit分页优化
直接limit 效率比较低,全表检索所有数据再取5条数据
mysql> select rental_id from rental order by rental_id limit 10000,5;
使用关联查询效率比较高,先用rental_id 查出5条数据,再用rental_id取匹配所有行数据
mysql> select * from rental a join (select rental_id from rental order by rental_id limit 10000,5) b on a.rental_id = b.rental_id;
- union优化:
除非确定不需要 重复的行,否则一定要使用union all,因为没有all关键子,会在零时表上添加关键子 distinct的关键字,这个操作代价很高。