mysql 性能优化

1.CentOS系统参数优化-提升MySQL服务器性能

2.mysql参数优化

有些时候我们需要了解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分析慢查询日志

4.根据慢查询日志，explain/desc sql是用来分析sql语句，帮助优化的一个命令。

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()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个

SHOW STATUS LIKE  "%Handler_read_rnd_next%";

5.定期清理表 delete删除数据后，mysql表文件大小保持不变
optimize table:可以去除删除操作后留下的数据文件碎片,减小文件尺寸,加快未来的读写操作.OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用

6.分库，分表，分区，读写分离