Mysql优化策略

1.定位优化策略整体思路

定位策略

通过图中的步骤我们可以总结以上几个方法去定位问题出在哪里，然后根据不同的问题对症下药，妙手回春

2.Show Status(观察服务器状态)

利用 show status 分析当前mysql连接情况，运行状态
Queries：当前时间请求个数（可以根据两个时间范围求每秒请求个数）
Threads_connected：当前线程连接个数
Threads_running： 当前进程运行个数(连接后不一定running工作)
Threads_cached：已经被线程缓存池缓存的线程个数
Threads_created：表示创建过的线程数，如果发现Threads_created值过大的
话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值

利用AWK进行分析当前时间的连接数 
mysqladmin -uroot -p ext //效果等同于===>show status()
定时脚本
#!bin/bash
while true
do
mysqladmin -uroot -p ext |awk '/Queries/{a=$4}/Threads_connected/{b=$4}/Threads_running/{c=$4}END{printf("%d %d %d\n",a,b,c)}'  >>log.txt
sleep 1
done

AWK用法：
awk '/匹配正则/{printf("%d\n",$1)}' file_name.txt //匹配txt文件里的第一列($1)的信息
awk '正则/{处理方式}/正则/{处理方式}'

3.Show Processlist（查看当前线程处理情况）

show processlist

展示列解释：
id - 线程ID，可以用：kill id; 杀死一个线程，很有用
-----------------------------------------------------
db - 数据库
-----------------------------------------------------
user - 用户
-----------------------------------------------------
host - 连库的主机IP
-----------------------------------------------------
command - 当前执行的命令，比如最常见的：Sleep，Query，Connect 等
connect：从复制已经连接到主库
connect out：从复制正在连接到主库
create db：正在执行一个创建库的操作。
execute：正在执行一个准备好的语句。
field list：提取表列信息线程
kill：当前线程被其他线程杀掉。
query：正在整型一条语句。
quit：线程被终止。
refresh：刷新表、日志、缓存、重置状态变量值或从服务信息线程。
shutdown：关闭服务线程
sleep：等待客户端发送一条新语句线程。
table dump：发送表内容到从服务器
statics：获取当前服务状态信息。
如果一个线程耗时比较久，需要重点关注造成该线程。
-----------------------------------------------------
time - 消耗时间，单位秒，很有用
-----------------------------------------------------
State - 执行状态，比如：Sending data，Sorting for group，Creating tmp 
table，Locked等

creating tmp table：在内存或磁盘创建一个临时表。如果一个表刚开始再内存创建，之后转到磁盘，该状态会变为：Copying to tmp table on disk（可以考虑文件排序是否用到索引或者Group By是否用到索引）
logging slow query：写语句到slow-query日志

Locked 被其他查询锁住了。

Sending data 正在处理SELECT查询的记录，同时正在把结果发送给客户端(返回结果数据太多可以考虑分页)
-----------------------------------------------------
info - 执行的SQL语句，很有用

什么时候会产生临时表

1、UNION查询；
2、用到TEMPTABLE算法或者是UNION查询中的视图；
3、ORDER BY和GROUP BY的子句不一样时；
4、表连接中，ORDER BY的列不是驱动表中的；
5、DISTINCT查询并且加上ORDER BY时；
6、SQL中用到SQL_SMALL_RESULT选项时；
7、FROM中的子查询；
8、子查询或者semi-join时创建的表；

什么情况下临时表写到磁盘上

1、取出的列含有text/blob类型时，内存中存不了text/blob
2、group by 或者distinct列中存在>512字节的string列
3、select列中存在>512字节的string列，同时又使用了unoin/union all语句

利用 Show Processlist 获取State定时脚本

#!/bin/bash
while true
do 
mysql -uroot -e 'show processlist \G' | grep State |uniq | sort -rn >> state.txt
unsleep 100000 //10毫秒执行一次
done

4.show profile (分析当前会话中sql执行的资源消耗情况)

set profiling =1
开启，服务器上执行的所有的语句会检测消耗的时间，存到临时表中
show profiles

image.png

show profile for query 临时表ID

image.png

具体参数分析

starting：开始
checking permissions：检查权限
Opening tables：打开表
init ： 初始化
System lock ：系统锁
optimizing ： 优化
statistics ： 统计
preparing ：准备
executing ：执行
Sending data ：发送数据
Sorting result ：排序
end ：结束
query end ：查询 结束
closing tables ： 关闭表 ／去除TMP 表
freeing items ： 释放物品
cleaning up ：清理

5.Explain分析SQL

mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      |anheqiaobei | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)

从Explain返回的结果，我们重点关注以下几个参数：
possible_keys：
可能使用的索引
-----------------------------------------------------
key：
key列显示MySQL实际决定使用的键（索引）
-----------------------------------------------------
type：
常用的类型有： ALL, index,  range, ref, eq_ref, const, system, NULL(从左到右，性能从差到好)

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，
就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于
where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况
下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过
单独索引查找完成。

-----------------------------------------------------
Extra：
Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的
请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。
如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：这个值强调了where语句会导致没有符合条件的行。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

Show ProcessList分析
Explain分析