【七】MySQL-性能分析与优化实战

Mysql性能分析与优化

 

一、查询执行过程

 

 

二、优化思路

数据查询慢，不代表sql语句写法有问题，用一张流程图展示MySQL优化思路

 

主要包括：
1、适当添加索引（四种：普通索引、主键索引、唯一索引、全文索引）
2、分表技术（水平分割、垂直分割）
3、读写（写：update/delete/add）分离
4、对mysql配置优化（my.conf）
5、mysql服务器硬件升级

mysql查询执行顺序

示例SQL：

SELECT *

FROM user

LEFT JOIN order ON user.id = order.uid

WHERE order.price > 1000

GROUP BY user.name

HAVING count(1) > 5

ORDER BY user.name

LIMIT 0,10

1、FROM（将最近的两张表，进行笛卡尔积）—VT1
2、ON（将VT1按照它的条件进行过滤）—VT2
3、LEFT JOIN（保留左表的记录）—VT3
4、WHERE（过滤VT3中的记录）–VT4…VTn
5、GROUP BY（对VT4的记录进行分组）—VT5
6、HAVING（对VT5中的记录进行过滤）—VT6
7、SELECT（对VT6中的记录，选取指定的列）–VT7
8、ORDER BY（对VT7的记录进行排序）–游标
9、LIMIT（对排序之后的值进行分页）
WHERE条件执行顺序（影响性能）
1、MYSQL：从左往右去执行WHERE条件的。
2、Oracle：从右往左去执行WHERE条件的。
结论：写WHERE条件的时候，优先级高的部分要去编写过滤力度最大的条件语句。

 

 

三、查看MySQL服务器运行的状态值/服务器配置信息

1、查询MySQL 服务器运行的状态值

如果系统的并发请求数不高，且查询速度慢，可以忽略该步骤直接进行 SQL 语句调优步骤。
执行命令：show status/show global status 可以查看所有的性能会话参数/全局参数；或 show status like '参数名称' 可以查看指定参数名称的性能参数，一般某一类参数都有相同的前缀。
返回结果太多，我们主要关注“Queries”、“Threadsconnected” 和 “Threadsrunning” 的值，即查询次数、线程连接数和线程运行数。

2、show status

 

下面是参考官方文档及网上资料整理出来的中文详细解释，不管你是初学mysql还是你是mysql专业级的dba，这都是值得看的：

3、查看INSERT、UPDATE、DELETE、SELECT的执行频率

show global status like 'Com_______';

4、查询MySQL服务器配置信息

执行命令：show variables/show global variables

5、核心参数解析

5.1、慢查询

mysql> show variables like '%slow%';

+——————+——-+

| Variable_name　　　     　 | Value |

+——————+——-+

| log_slow_queries          | ON |

| slow_launch_time          | 2 |

+——————+——-+

mysql> show global status like '%slow%';

+———————+——-+

| Variable_name | Value |

+———————+——-+

| Slow_launch_threads        | 0 |

| Slow_queries | 4148 |

+———————+——-+

 

如何开启mysql慢查询日志？

1、查看mysql的慢查询日志是否开启

show variables like ‘%query%’;

2、如何启用慢查询日志呢？

set global slow_query_log=‘ON’;

这样就启用了。

3、指定日志的输出格式

MySQL支持TABLE和FILE两种输出格式，可以用下面的命令来查看当前的输出格式：

show variables like ‘%log_output%’;

设置输出格式：

 

set global log_output=‘FILE’;

set global log_output=‘TABLE’;

set global log_output=‘FILE,TABLE’;

 

1）查看TABLE记录的慢日志：

select * from mysql.slow_log;

这里面记录了查询时间、发起查询的客户端、扫描行数、执行的sql语句等信息。

2）查看FILE记录的慢日志：

先找到日志文件

 

修改阀值：

执行如下sql可以查看当前设置的阀值：

show variables like 'long_query_time';

show global variables like '%long_query_time%';

set global long_query_time=5;

 

5.2、连接数

经常会遇见”MySQL: ERROR 1040: Too many connections”的情况，一种是访问量确实很高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力，另外一种情况是MySQL配置文件中max_connections值过小：

mysql> show variables like 'max_connections';

+—————–+——-+

| Variable_name | Value |

+—————–+——-+

| max_connections    | 256 |

+—————–+——-+

 

这台MySQL服务器最大连接数是256，然后查询一下服务器响应的最大连接数：

mysql> show global status like 'Max_used_connections';

 

MySQL服务器过去的最大连接数是245，没有达到服务器连接数上限256，应该没有出现1040错误，比较理想的设置是
Max_used_connections / max_connections * 100% ≈ 85%
最大连接数占上限连接数的85％左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。

 

5.3、进程使用情况

mysql> show global status like 'Thread%';

+——————-+——-+

| Variable_name | Value |

+——————-+——-+

| Threads_cached | 46 |

| Threads_connected      | 2 |

| Threads_created　　　   | 570　  |

| Threads_running　　　   | 1    |

+——————-+——-+

 

如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器thread_cache_size配置：

mysql> show variables like 'thread_cache_size';

+——————-+——-+

| Variable_name　　　　　 | Value |

+——————-+——-+

| thread_cache_size      | 64　　 |

+——————-+——-+

 

四、获取需要优化的SQL语句

 

方式一 查看运行的线程

执行命令：show processlist / show full processlist

返回结果：

 

 

从返回结果中我们可以了解该线程执行了什么命令/SQL 语句以及执行的时间。实际应用中，查询的返回结果会有 N 条记录。
其中，返回的 State 的值是我们判断性能好坏的关键，其值出现如下内容，则该行记录的 SQL 语句需要优化：

• Converting HEAP to MyISAM # 查询结果太大时，把结果放到磁盘，严重 
• Create tmp table #创建临时表，严重 
• Copying to tmp table on disk #把内存临时表复制到磁盘，严重 
• locked #被其他查询锁住，严重 
• loggin slow query #记录慢查询
• Sorting result #排序

方式二开启慢查询日志（推荐）

在配置文件 my.cnf 中的 [mysqld] 一行下边添加几个参数：

slow_query_log = 1

slow_query_log_file=/var/lib/mysql/slow.log

long_query_time = 2

log_queries_not_using_indexes = 1

 

其中，slowquerylog = 1 表示开启慢查询；slowquerylogfile 表示慢查询日志存放的位置；longquerytime = 2 表示查询 >=2 秒才记录日志；logqueriesnotusing_indexes = 1 记录没有使用索引的 SQL 语句。
注意：slowquerylog_file 的路径不能随便写，否则 MySQL 服务器可能没有权限将日志文件写到指定的目录中。
修改保存文件后，重启 MySQL 服务。在 /var/lib/mysql/ 目录下会创建 slow-query.log 日志文件。连接 MySQL 服务端执行如下命令可以查看配置情况。

 

show variables like 'slow_query%'; 

show variables like 'long_query_time';

 

MySQL 提供 mysqldumpslow 工具对日志进行分析。我们可以使用 mysqldumpslow --help 查看命令相关用法

常用参数如下：

 

 

案例：  

获取返回记录集最多的10个sql

mysqldumpslow -s r -t 10 /var/lib/mysql/slow-query.log

获取访问次数最多的10个sql

mysqldumpslow -s c -t 10 /var/lib/mysql/slow-query.log

获取按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow-query.log

 

五、分析SQL语句

方式一：explain

筛选出有问题的 SQL，我们可以使用 MySQL 提供的 explain 查看 SQL 执行计划情况（关联表，表查询顺序、索引使用情况等）。
用法：explain select * from user;
返回结果：

 +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+

 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

 +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+

 | 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

 

字段解释：

 

 

 

方式二：profiling

 

show profiles

默认是关闭的

show variables like 'profiling'

 

 

打开

set profiling=on;

 

Show Profile的常用查询参数

①ALL：显示所有的开销信息。

②BLOCK IO：显示块IO开销。

③CONTEXT SWITCHES：上下文切换开销。

④CPU：显示CPU开销信息。

⑤IPC：显示发送和接收开销信息。

⑥MEMORY：显示内存开销信息。

⑦PAGE FAULTS：显示页面错误开销信息。

⑧SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。

⑨SWAPS：显示交换次数开销信息。

 

 

六、常用工具

1、mysqlshow

 

mysqlshow  -uroot --count -p -S /mnt/mysqldata/mysql/mysql.sock

 

mysqlshow  -uroot  forecast_cloud_new_2 --count -p -S /mnt/mysqldata/mysql/mysql.sock