有些时候我们需要了解MySQL的服务器状态信息,譬如当前MySQL启动后的运行时间,当前MySQL的客户端会话连接数,当前MySQL服务器执行的慢查询数,当前MySQL执行了多少SELECT语句、执行了多少UPDATE/DELETE/INSERT语句等统计信息,从而便于我们根据当前MySQL服务器的运行状态进行对应的调整或优化工作。
在MySQL中,我们可以使用SHOW STATUS指令语句来查看MySQL服务器的状态信息。
####mysql 性能监控
show status;
-- 1、查询连接mysql服务器次数
show status like 'Connections';
-- 2、查询当前MySQL本次启动后的运行统计时间
show STATUS like 'Uptime';
-- 3、查询慢查询次数
show status like 'slow_queries'
-- 4、 查询自当前MySQL启动后所有连接执行的SELECT语句总数
show global status like 'com_select';
-- 5、查看MySQL服务器的线程信息
show status like 'Thread_%';
-- 6、--查看insert语句的执行数
show status like 'com_insert';
show GLOBAL status like 'com_insert';
-- 7、查询本次MySQL启动后执行的SELECT语句的次数
show status like 'com_select';
-- 8、查询本次MySQL启动后执行的SELECT语句的次数
show status like 'com_update';
show status like 'com_delete';
-- 10、innodb操作次数
show status like 'innodb_rows_%';
通过上面信息可以修改mysql服务器的配置参数,或是否考虑读写分类等
使用show profiles分析SQL性能
开启profile
mysql> set profiling=1;
show processlist 数据运行状态分析
按客户端 IP 分组,看哪个客户端的链接数最多
select client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip from information_schema.processlist ) as connect_info group by client_ip order by client_num desc;
查看正在执行的线程,并按 Time 倒排序,看看有没有执行时间特别长的线程
select * from information_schema.processlist where Command != 'Sleep' order by Time desc;
找出所有执行时间超过 5 分钟的线程,拼凑出 kill 语句,方便后面查杀
select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;
3.查看慢查询日志
使用pt-query-digest分析慢查询日志
explain的语法如下:
explain [extended] select … from … where …
列的说明:
id: SELECT的识别符,这是SELECT的查询序列号。
select_type: SELECT类型,有以下几种不同的类型
- (1).SIMPLE: 简单的SELECT(不使用UNION或子查询)
- (2).PRIMARY: 最外面的SELECT,如果我们使用UNION或子查询,第一个查询将会是这个类型
- (3).UNION: 使用UNION查询时,除第一个语句外的所有语句会返回这个类型
- (4).DEPENDENT UNION: UNION中的第二个或后面的SELECT语句,取决于外面的查询。
- (5).UNION RESULT: UNION的结果。
- (6).SUBQUERY: 子查询中的第一个SELECT。
- (7).DEPENDENT SUBQUERY: 子查询中的第一个SELECT,取决于外面的查询。
- (8).DERIVED : 衍生表会返回这个类型。如:select * from (select * from jos_content) as A;。
table 表示是哪个表的数据。
type : 表示表的连接类型,性能由好到差的类型类型为
- System(表中仅有一行,即常量表),
- const(单表中最多有一个匹配行),
- eq_ref(对于前面的每一行,在此表中只查询一条记录),
- ref(使用普通的索引),
- ref_or_null(和ref类似,但是条件中包含对于NULL查询),
- index_merge(索引合并优化),
- unique_subquery(in的后面是一个查询主键字段的子查询),
- index_subquery(类似unique_subquery,主要是in的后面是查询非唯一索引字段的子查询),
- range(单表中的范围查询),
- index(对于当前的每一行,都通过查询索引来得到数据),
- all(对于当前的每一行,都通过全表扫描来得到数据)
type比较重要。表示链接的类型。链接类型由好到坏的,依次是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般情况,至少要达到 range 级别,最好是 ref 级别。否则可能会有性能问题。
possible_keys 是指可能用到该表的索引,如果为NULL则没有。
key 是指用到的索引。
key_len 是索引的长度,在不影响查询精度的情况下,值越小越好 。
ref 是指索引的那一列被使用了。一般会是个常数。
rows MYSQL认为必须检查的用来返回请求数据的行数 。
extra 是指额外的信息。也是比较重要的 。坏的例子是Using temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢。参数的信息非常多有几十种,常用的有
- A:distinct:在select部分使用了distinc关键字
- B:no tables used:不带from字句的查询或者From dual查询
- C:使用not in()形式子查询或not exists运算符的连接查询,这种叫做反连接。即,一般连接查询是先查询内表,再查询外表,反连接就是先查询外表,再查询内表。
- D:using filesort:排序时无法使用到索引时,就会出现这个。常见于order by和group by语句中
- E:using index:查询时不需要回表查询,直接通过索引就可以获取查询的数据。
- F:using join buffer(block nested loop),using join buffer(batched key accss):5.6.x之后的版本优化关联查询的BNL,BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。
- G:using sort_union,using_union,using intersect,using sort_intersection:
- using intersect:表示使用and的各个索引的条件时,该信息表示是从处理结果获取交集
- using union:表示使用or连接各个使用索引的条件时,该信息表示从处理结果获取并集
- using sort_union和using sort_intersection:与前面两个对应的类似,只是他们是出现在用and和or查询信息量大时,先查询主键,然后进行排序合并后,才能读取记录并返回。
- H:using temporary:表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表,执行计划中看不出来,需要查看status变量,used_tmp_table,used_tmp_disk_table才能看出来。
- I:using where:表示存储引擎返回的记录并不是所有的都满足查询条件,需要在server层进行过滤。查询条件中分为限制条件和检查条件,5.6之前,存储引擎只能根据限制条件扫描数据并返回,然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性,可以把检查条件也下推到存储引擎层,不符合检查条件和限制条件的数据,直接不读取,这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition
- J:firstmatch(tb_name):5.6.x开始引入的优化子查询的新特性之一,常见于where字句含有in()类型的子查询。如果内表的数据量比较大,就可能出现这个
- K:loosescan(m..n):5.6.x之后引入的优化子查询的新特性之一,在in()类型的子查询中,子查询返回的可能有重复记录时,就可能出现这个
5.定期清理表 delete删除数据后,mysql表文件大小保持不变
optimize table:可以去除删除操作后留下的数据文件碎片,减小文件尺寸,加快未来的读写操作. OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用