MySQL —— 优化

概念

在应用的开发过程中，由于初期数据量小，开发人员写SQL语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多SQL语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的SQL语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化
MySQL优化方式有很多，大致可以从以下几点来优化MySQL：

• 从设计上优化
• 从查询上优化
• 从索引上优化
• 从存储上优化

查看SQL执行频率

MySQL客户端连接成功后，通过show [session|global] status命令可以查看服务器状态，通过查看状态信息可以查看对当前数据库的主要操作类型。

--下面的命令显示了当前session中所有统计参数的值
show session status like 'Com_______'; -- 查看当前会话统计结果
show global status like 'Com_______'; -- 查看数据库上次启动至今统计结果
show status like 'Innodb_rows_%'; --查看针对InnoDB引擎的统计结果

定位低效率执行SQL

可以通过两种方式定位执行效率较低的SQL语句：

• 慢查询日志：通过慢查询日志定位那些执行效率较低的SQL语句
• show processlist：该命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看SQL得执行情况，同时对一些锁表操作进行优化

慢查询日志：

--查看慢日志配置信息
show variables like '%slow_query_log%';
--开启慢日志查询
set global slow_query_log = 1;
--查看慢日志记录SQL的最低阈值时间
show variables like 'long_query_time%';
--修改慢日志记录SQL的最低阈值时间
set global long_query_time = 4;

show processlist：

• id：用户登录MySQL时，系统分配的‘connection_id’，可以通过函数connection_id()查看
• user：显示当前用户，如果不是root，这个命令就只显示用户权限范围的sql语句
• host：显示这个语句时从哪个ip端口发出的，可以用来追踪出现问题语句的用户
• db：显示这个进程目前连接的是哪个数据库
• command：显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等
• time：显示这个状态持续的时间，单位是秒
• state：显示当前连接的sql语句的状态，很重要的列，state描述的是语句执行中的某一个状态，一个sql语句，以查询为例，可能需要经过copying to tem table、sorting result、sending data等状态才可以完成
• info：显示这个SQL语句，是判断问题语句的一个重要依据

show processlist;

Explain分析执行计划

通过定位低效率执行SQL后，可以通过explain命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序

字段	含义
id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序
select_type	表示select的类型，常见的取值有SIMPLE（简单表，即不适用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个select）等
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为（system —> const —> eq_ref —> ref —> ref_or_null —> index_merge —> index_subquery —> range —> index —> all）
possible_keys	表示查询时，可能使用的索引，一个或多个
key	表示实际使用的索引，如果为null，则没有使用索引
key_len	索引字段的长度，该值为索引字段最大长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
rows	扫描行的数量
extra	执行情况的说明和描述

Explain-id:
id字段是select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序，i