===============================================================================
001-案例：占用CPU的优化
一、总体思路
1、先用操作系统命令 top 观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。
2、如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。
找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。
SHOW PROFILES\G查看queryid,sqltext，接着show profile cpu,block io for query 2;查看消耗情况。
3、一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。
4、也有可能是每个 sql 消耗并不多，突然大量的 session 连进来使 cpu 飙升，需要跟应用一起分析为何连接数会激增，再做相应调整，比如说限制连接数等。
5、补充：SQL语句分析三板斧explain+show processlist+show profile for query query_id
二、使用 show processlist
1、查看当前哪些线程正在运行。查看下来一共有160多个
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
state只是sql执行中的某一个状态，以查询sql为例，
可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成。
由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时长的状态。
2、从show processlist命令输出的结果看到有一条sql语句重复出现，
但是info列显示的不全只有select a.col1,a.col2,a.col3 from table1 a这样的信息。
那就先从这个表入手查，select count(*) from table1;查出这张表有60W+的数据。
select count(*) 使用了6秒。但是现在不确定这个语句执行的时候有没有where条件。
3、再查询information_schema.processlist表时发现info信息是完整的，
在这里找到上边怀疑的sql的完整版为select a.col1,a.col2,a.col3 from table1 a where a.col4='123' and a.col5='abc';
查看这个语句的执行计划(类似下面这种)
mysql> explain select ename,hiredate,sal from table1 where sal=1000 \G;
type: ALL全表扫描，没有使用索引
4、哪种type性能好
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
ALL——全表扫描
index——索引全扫描
range——索引范围扫描，常用语<,<=,>=,between等操作
ref——使用非索引扫描或索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref——类似ref，区别在于使用的是索引，使用主键的关联查询
const/system——单条记录，系统会把匹配行中的其他列作为常数处理，如主键或索引查询
null——MySQL不访问任何表或索引，直接返回结果
三、索引评估及添加
1、经查看发现col4列上没有索引。
mysql> show index from table1 \G;
2、表的存储引擎为 InnoDB，于是在col4列上创建索引
mysql> show table status from table1 like 'table1'\G;
Engine: InnoDB存储引擎
3、加索引
mysql> create index idx_sal on table1(col4);
4、再次查看执行计划，发现语句使用索引扫描。
mysql> explain select * from table1 where col4=1000 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE # 连接类型
table: emp
type: ref # 建索引之前是：type: ALL全表扫描
possible_keys: idx_sal
key: idx_sal
key_len: 6
ref: const 主键或索引查询
四、sql语句的执行效率立马提升。CPU的使用率也降下来了。
五、高CPU占用总结
cpu消耗过大有慢sql造成,慢sql包括全表扫描，扫描数据量太大，内存排序，磁盘排序，锁争用等；表的现象：
sql执行状态为:sending data,copying to tmp table,copying to tmp table on disk,sorting result,using filesort,locked;
1、sending data
sql正从表中查询数据，如果查询条件没有适当索引，会导致sql执行时间过长
2、copying to tmp table on disk:
因临时结果集太大，超过数据库规定的临时内存大小,需要拷贝临时结果集到磁盘上
3、sorting result,using filesort:
sql正在执行排序操作，排序操作会引起较多的cpu消耗，可以通过添加索引，或减小排序结果集。
所以如果用户的数据量很大，内存很小，因iops的限制，一条慢sql就有可能消耗掉所有io资源，而影响其他sql查询，
对于数据库就是所有的sql需要执行很长时间才返回结果集，对于应用会造成整体响应变慢。
==========================================================================
002-案例：大量的sleep进程的优化
一、系统运行一段时间后通过show processlist发现有大量的sleep状态的连接进程。
二、临时性的批量杀sql
通过information_schema.processlist表中的连接信息生成需要处理掉的MySQL连接的语句临时文件，然后执行临时文件中生成的指令
mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root' and where command = 'Sleep' ;
+------------------------+
| concat('KILL ',id,';')
+------------------------+
| KILL 3101;
| KILL 2946;
+------------------------+
mysql>select concat('KILL ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/a.txt';
mysql>source /tmp/a.txt;
三、通过参数优化彻底解决
1、#没进行过优化，还是默认值28800（8小时）
Mysql> show variables like “%timeout%”;
+--------------------+---------+
| connect_timeout | 10 |
| wait_timeout | 28800 |
+--------------------+---------+
大量闲置连接，会白白消耗内存，可能会造成'too many connections'的错误。
2、新增 timeout参数，设置为120秒
vi /etc/my.cnf
[mysqld]
wait_timeout=120
interactive_timeout=120
注意：要同时设置interactive_timeout和wait_timeout才会生效。
3、重启mysql
==========================================================================
003-案例：高IO的优化
一、查看mysql线程的IO使用情况
1、安装iotop,yum install iotop
2、iotop查看mysql进程里的哪个线程读写速率比较大
iotop -u mysql ##-u mysql查询用户mysql运行的对应程序
3、查看对应操作系统线程号与mysql哪个功能线程对应，看一下是哪个processlist_id占用较大的io
select name,type,thread_id,thread_os_id,processlist_id from threads;
type字段中BACKGROUND表示后台线程，FOREGROUND表示前端线程，如连接线程等
补充方法：
SHOW PROFILES\G查看queryid,sqltext，接着show profile block io for query 2;查看消耗情况。
4、processlist_id对应show processlist
5、杀processlist_id
二、InnoDB引擎从大表中删除大量行
1、有什么办法可以快速删除？
1亿6000万的表，有一个自增ID，4要删除大于250万以后的数据。
2、三种方案
方法1:重建表（拷贝待保留数据到新表、删旧表、新表改为旧表名）--时间较长
方法2:拆分SQL执行--时间较长
方法3:切换主从--推荐
三、利用硬链接和truncate降低drop table对线上环境的影响
在建立硬链接之后，mysql会认为rm了硬链接文件之后就算操作完毕，不会真正去删除物理文件。
如果手动rm物理文件会产生很多的io影响。
使用coreutils 工具集中的truncate会根据指定的size大小进行逐步删除，会将对IO造成的影响降到低。
1、建立硬链接。
ln table.ibd table.idb.hdlk
2、mysql执行drop table操作。
drop table if exists tablename；
3、使用truncate删除物理文件。
truncate -s 1024*1024*4 filename
==========================================================================
004-案例：高内存占用的优化
一、内存消耗过高使系统卡住
1、近期是否升级过kernel。
2、innodb内部统计的内存使用量。
3、NUMA开关导致swap。
4、临时表、memory引擎表。
5、连接所消耗内存。
6、table cache相关的内存。
7、真正的mysqld内存泄漏。
8、异常sql
二、紧急处理
1、查杀异常sql
netstat -natpl检查数据库端口连接数
show processlist紧急定位明显异常的sql
2、增加swap交换空间
3、增加内存
三、优化参数限制内存占用
1、查看全局内存使用情况
select event_name,SUM_NUMBER_OF_BYTES_ALLOC
from memory_summary_global_by_event_name
order by SUM_NUMBER_OF_BYTES_ALLOC
desc LIMIT 10;
2、查看进程内存使用情况
(1)select event_name, SUM_NUMBER_OF_BYTES_ALLOC
from memory_summary_by_thread_by_event_name
order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 20;
(2)show profiles
show profile memory for query 1;
3、调整全局缓存、线程缓存、等
innodb_buffer_pool_size=576M ->256M (InnoDB引擎缓冲区占了大头)
performance_schema_max_table_instances=600 （5.6默认的设置12500）
table_definition_cache=400 （5.6默认的设置1400）
table_open_cache=256 （5.6默认的设置2000）
query_cache_size=0(高并发系统建议禁用)
tmp_table_size=64M(临时表数据量大于tmp_table_size会将临时表存储到磁盘)
4、MySQL内存占用计算
##MySQL 大可使用内存（ M ）：
SELECT
(
@@key_buffer_size + @@innodb_buffer_pool_size + @@query_cache_size + @@tmp_table_size + @@max_connections * (
@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack
)
) / 1024 / 1024 AS result
##MySQL 单个连接大可使用内存（ M ）：
SELECT
(
@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack
) / 1024 / 1024 AS result
##MySQL 大可使用内存（不包含连接占用内存）（ M ）：
SELECT
(
@@key_buffer_size + @@innodb_buffer_pool_size + @@query_cache_size + @@tmp_table_size
) / 1024 / 1024 AS result
四、进一步定位
1、由于是怀疑线程, 所以用CURRENT_NUMBER_OF_BYTES_USED倒序, 查询Thread相关的表; 结果类似下图:
select thread_id,event_name,CURRENT_NUMBER_OF_BYTES_USED
from memory_summary_by_thread_by_event_name
order by CURRENT_NUMBER_OF_BYTES_USED desc
limit 3 \G
2、select name,type,thread_id,thread_os_id,processlist_id from threads;
3、processlist和events_statements_current一一对应起来，可以通过performance_schema.threads表来关联，给出一个可行的sql
select a.SQL_TEXT,c.id,d.trx_started
from events_statements_current a
join threads b on a.THREAD_ID=b.THREAD_ID
join information_schema.processlist c on b.PROCESSLIST_ID=c.id
join information_schema.innodb_trx d on c.id=d.trx_mysql_thread_id
order by d.trx_started \G
4、一般占用私有内存多的是tmp_table，sort以及join等操作，是否某个session占用过多的私有内存？
explain SQL_TEXT;
5、show processlist;
6、kill id
==========================================================================
005-参考文章
https://blog.51cto.com/11819159/2128099
https://www.cnblogs.com/xzlive/p/9881507.html
https://www.linuxidc.com/Linux/2016-12/137852.htm
http://blog.itpub.net/29510932/viewspace-2129312/
https://blog.csdn.net/dennis211/article/details/78170079
https://www.fashengba.com/post/mysql-delete-big-table.html
https://www.cnblogs.com/D666/p/9165044.html
动力小刚于2019年4月 个人邮箱：zcs0237#163.com
