1.索引监控
show status like 'Handler_read%';
2.参数解释
Handler_read_first:读取索引第一个条目的次数(所用查询一共使用了多少索引)
Handler_read_key:通过index获取数据的次数
Handler_read_last:读取索引最后一个条目的次数(读取根节点的次数)
Handler_read_next:通过索引读取下一条数据的次数
Handler_read_prev:通过索引读取上一条数据的次数
Handler_read_rnd:从固定位置读取数据的次数
Handler_read_rnd_next:从数据节点读取下一条数据的次数
3.查询慢的原因
查询性能低下的主要原因是访问的数据太多,某些查询不可避免的需要筛选大量的数据,我们可以通过减少访问数据量的方式进行优化:
确认应用程序是否在检索大量超过需要的数据
确认mysql服务器层是否在分析大量超过需要的数据行
网络
CPU
IO
上下文切换
系统调用
生成统计信息
锁等待时间
是否向数据库请求了不需要的数据:
查询不需要的记录:
我们常常会误以为mysql会只返回需要的数据,实际上mysql却是先返回全部结果再进行计算,在日常的开发习惯中,经常是先用select语句查询大量的结果,然后获取前面的N行后关闭结果集。
优化方式是在查询后面添加limit
多表关联时返回全部列:
2.执行过程的优化
查询缓存
在解析一个查询语句之前,如果查询缓存是打开的,那么mysql会优先检查这个查询是否命中查询缓存中的数据,如果查询恰好命中了查询缓存,那么会在返回结果之前会检查用户权限,如果权限没有问题,那么mysql会跳过所有的阶段,就直接从缓存中拿到结果并返回给客户端
查询优化处理
mysql查询完缓存之后会经过以下几个步骤:解析SQL、预处理、优化SQL执行计划,这几个步骤出现任何的错误,都可能会终止查询
语法解析器和预处理:
mysql通过关键字将SQL语句进行解析,并生成一颗解析树,mysql解析器将使用mysql语法规则验证和解析查询,例如验证使用使用了错误的关键字或者顺序是否正确等等,预处理器会进一步检查解析树是否合法,例如表名和列名是否存在,是否有歧义,还会验证权限等等
3.查询优化器
当语法树没有问题之后,相应的要由优化器将其转成执行计划,一条查询语句可以使用非常多的执行方式,最后都可以得到对应的结果,但是不同的执行方式带来的效率是不同的,优化器的最主要目的就是要选择最有效的执行计划
mysql使用的是基于成本的优化器,在优化的时候会尝试预测一个查询使用某种查询计划时候的成本,并选择其中成本最小的一个
3.1
select count(*) from film_actor;
show status like 'last_query_cost';
last_query_cost:每个表或者索引的页面个数
索引的基数
索引和数据行的长度
索引的分布情况
是通过以上值进行统计计算得出来的
可以看到这条查询语句大概需要做1195个数据页才能找到对应的数据,这是经过一系列的统计信息计算来的
4.在很多情况下mysql会选择错误的执行计划,原因如下:
统计信息不准确
InnoDB因为其mvcc的架构,并不能维护一个数据表的行数的精确统计信息
执行计划的成本估算不等同于实际执行的成本
有时候某个执行计划虽然需要读取更多的页面,但是他的成本却更小,因为如果这些页面都是顺序读或者这些页面都已经在内存中的话,那么它的访问成本将很小,mysql层面并不知道哪些页面在内存中,哪些在磁盘,所以查询之际执行过程中到底需要多少次IO是无法得知的
mysql的最优可能跟你想的不一样
mysql的优化是基于成本模型的优化,但是有可能不是最快的优化
如:
explain select film.film_id,film.title,film.release_year,actor.actor_id,actor.first_name,actor.last_name from film inner join film_actor using(film_id) inner join actor using(actor_id);
explain select straight_join film.film_id,film.title,film.release_year,actor.actor_id,actor.first_name,actor.last_name from film inner join film_actor using(film_id) inner join actor using(actor_id);
查看执行的成本:
show status like 'last_query_cost';
straight_join可以指定先查左表
mysql不考虑其他并发执行的查询
mysql不会考虑不受其控制的操作成本
执行存储过程或者用户自定义函数的成本
5.优化器的优化策略
静态优化
直接对解析树进行分析,并完成优化
动态优化
动态优化与查询的上下文有关,也可能跟取值、索引对应的行数有关
mysql对查询的静态优化只需要一次,但对动态优化在每次执行时都需要重新评估
6.优化器的优化类型
重新定义关联表的顺序
数据表的关联并不总是按照在查询中指定的顺序进行,决定关联顺序时优化器很重要的功能
将外连接转化成内连接,内连接的效率要高于外连接
使用等价变换规则,mysql可以使用一些等价变化来简化并规划表达式
如:需要查询a>4且a<4的值,就可以用a!=4代替
优化count(),min(),max()
索引和列是否可以为空通常可以帮助mysql优化这类表达式:例如,要找到某一列的最小值,只需要查询索引的最左端的记录即可,不需要全文扫描比较
预估并转化为常数表达式,当mysql检测到一个表达式可以转化为常数的时候,就会一直把该表达式作为常数进行处理
explain select film.film_id,film_actor.actor_id from film inner join film_actor using(film_id) where film.film_id = 1
索引覆盖扫描,当索引中的列包含所有查询中需要使用的列的时候,可以使用覆盖索引
当数据量特别大的时候也会导致索引失效,预计是30%
子查询优化 :
mysql在某些情况下可以将子查询转换一种效率更高的形式,从而减少多个查询多次对数据进行访问,例如将经常查询的数据放入到缓存中
等值传播:
如果两个列的值通过等式关联,那么mysql能够把其中一个列的where条件传递到另一个上:
explain select film.film_id from film inner join film_actor using(film_id
) where film.film_id > 500;
这里使用film_id字段进行等值关联,film_id这个列不仅适用于film表而且适用于film_actor表
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;
7.关联查询
join的实现方式原理
有索引的执行方式
没有索引的执行方式
使用缓存的执行方式
(1)Join Buffer会缓存所有参与查询的列而不是只有Join的列。
(2)可以通过调整join_buffer_size缓存大小
(3)join_buffer_size的默认值是256K,join_buffer_size的最大值在MySQL 5.1.22版本前是4G-1,而之后的版本才能在64位操作系统下申请大于4G的Join Buffer空间。
(4)使用Block Nested-Loop Join算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on,默认为开启。
show variables like '%optimizer_switch%'