MySQL优化

概述

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

MySQL的优化方式有很多，大致我们可以从以下几点来优化MySQL：从设计上优化、从查询上优化、从索引上优化、从存储上优化。

SQL执行频率

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

--下面的命令显示了当前session中所有统计参数的值
show session status like 'Com_______';   --查看当前会话统计结果(Com后是7个_)

show global status like 'Com_______';    -- 查看自数据库上次启动至今统计结果(Com后是7个_)

show status like 'Innodb_rows_%';      -- 查看针对Innodb引擎的统计结果

参数	含义
Com_select	执行select操作的次数，一次查询只累加1。
Com_insert	执行INSERT操作的次数，对于批量插入的INSERT操作，只累加一次。
Com_update	执行UPDATE操作的次数。
Com_delete	执行DELETE操作的次数。
Innodb_rows_read	select查询返回的行数。
Innodb_rows_inserted	执行INSERT操作插入的行数。
Innodb_rows_updated	执行UPDATE操作更新的行数。
Innodb_rows_deleted	执行DELETE操作删除的行数。
Connections	试图连接MySQL服务器的次数。
Uptime	服务器工作时间。
Slow_queries	慢查询的次数。

定位低效率执行SQL

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

慢查询日志：通过慢查询日志定位那些执行效率较低的SQL语句。

show processlist：该命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。

慢查询日志

--查看慢日志配置信息

show variables like '%slow_query_1og%';

开启慢日志查询

set global slow_query_log=1;

查看慢日志记录SQL的最低阈值时间

show variables like 'long_query_time%';

修改慢日志记录sQL的最低阈值时间

set global long_query_time = 数字 ;

set global long_query_time = 4 ;

show processlist

show processlist;

通过这个指令可以看到客户端与服务端建立的线程信息以及之间执行过的SQL信息。可以看到正在发生的查询执行状态。

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

explain分析执行计划

基本使用

通过以上步骤查询到效率低的SQL语句后，可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

操作

explain SQL语句；

可以查询SQL语句在内部执行的状态信息

字段	含义
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	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
extra	执行情况的说明和描述

id

id字段是select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id情况有三种。

id 相同表示加载表的顺序是从上到下。
id不同id值越大，优先级越高，越先被执行。
id有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行：在所有的组中，id的值越大，优先级越高，越先执行。

select_type

表示SELECT的类型，常见的取值，如下表所示:

select_type	含义
SIMPLE	简单的select查询，查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY	在SELECT或WHERE列表中包含了子查询
DERIVED	在FROM列表中包含的子查询，被标记为DERIVED (衍生) MYSQL会递归执行这些子查询，把结果放在临时表中
UNION	若第二个SELECT出现在UNION之后,则标记为UNION ;若UNION包含在FROM子句的子查询中，外层SELECT将被标记为: DERIVED
UNION RESULT	从UNION表获取结果的SELECT

type

type显示的是访问类型，是较为重要的一个指标。

type	含义
NULL	MySQL不访问任何表，索引，直接返回结果
system	系统表，少量数据，往往不需要进行磁盘IO；如果是5.7及以上版本的话就不是system了，而是all，即使只有一条记录
const	命中主键(primary key)或者唯一(unique)索引；被连接的部分是一个常量(const)值;
eq_ref	对于前表的每一行，后表只有一行被扫描。(1) join查询；(2) 命中主键(primary key)或者非空唯一(unique not nl)索引；(3) 等值连接；
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。对于前表的每一行(row)，后表可能有多于一行的数据被扫描。
range	只检索给定返回的行，使用一个索引来选择行。where之后出现between，<，> , in等操作。
index	需要扫描索引上的全部数据。
all	全表扫描，此时id上无索引

结果值从最好到最坏以此是：system > const > eq_ref > ref > range > index > ALL

其它字符

table

显示这一步所访问数据库中表名称有时不是真实的表名字，可能是简称，

rows

扫描行的数量。

key

possible_keys：显示可能应用在这张表的索引，一个或多个。

key：实际使用的索引，如果为NULL，则没有使用索引。

key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

show profile分析

MysqI从5.0.37版本开始增加了对show profiles和show profile语句的支持。show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过have_profiling 参数，能够看到当前MySQL是否支持profile：

select @@have profiling;
set profil ing=1; -- 开启profiling开关

执行show profiles指令查看SQL语句执行的耗时

show profiles;

通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间

show profile for query 8;

在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、 block io、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间:

字段	含义
Status	sql语句执行的状态.
Duration	sql执行过程中每一个步骤的耗时
CPU_user	当前用户占有的cpu
CPU_system	系统占有的cpu

trace

MySQL5.6提供了对SQL的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A计划，而不是选择B计划

打开trace，设置格式为JSON,并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

SET optimizer_trace = "enabled = on" ,end_markers_in_j_son = son;
set optimizer_trace_max_mem_size = 1000000;

检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的

select * from information_schema.optimizer_trace\G;

索引优化

索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。

全值匹配

避免索引失效应用全值匹配。

该情况下，索引生效，执行效率高。

全值匹配和字段匹配成功即可，和字段顺序无关

最左前缀法则

该情况下，索引生效，执行效率高。

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

如果符合最左法则，但是出现跳跃某一列，只有跳过的这一列之前的索引生效

其它匹配原则

范围查询右边的列，不能使用索引。

不要在索引列上进行运算操作，索引将失效。

字符串不加单引号，造成索引失效。

尽量使用覆盖索引(只访问索引的查询(索引列完全包含查询列) )，减少select *

如果查询列，超出索引列，也会降低性能。

Extra：

usingindex：使用覆盖索引的时候就会出现

usingwhere：在查找使用索引的情况下，需要回表去查询所需的数据

using index condition：查找使用了索引，但是需要回表查询数据

using index using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

用or分割开的条件，即使or前的条件中的列有索引，后面的列中没有索引，涉及的索引都不会被用到。

以%开头的Like模糊查询，索引失效。

如果MySQL评估使用索引比全表更慢，则不使用索引。

is NULL ，is NOT NULL <font color='red'>有时</font>索引失效。

in走索引，not in索引失效。

单列索引和复合索引。

如果MySQL评估使用索引比全表更慢，则不使用索引。

is NUNL，is NOT NULL有时有效，有时索引失效。

in走索引，not in索引失效。

单列索引和复合索引，尽量使用符合索引(如果一张表有多个单列索引，即使where中都使用了这些索引列，则只有一个最优索引生效)

SQL优化

大批量插入数据

当使用load命令导入数据的时候，适当的设置可以提高导入的效率。对于InnoDB类型的表，有以下几种方式可以提高导入的效率：

主键顺序插入

因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一一个主键，将可以利用这点，来提高导入数据的效率。

关闭唯一性校验

在导入数掘前执行SET UNIQUE_CHECKS=0; 关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1, 恢复唯一性校验，可以提高导入的效率。

导入磁盘中的内容

当通过load向表加载数据时，尽量保证文件中的主键有序，这样可以提高执行效率

1、检查一个全局系统变量'local_infile' 的状态，如果得到如下显示Value = OFF，则说明这是不可用的

show global variables like 'local_infile';

2、修改local_infile值为on，开启local_infile

set global local_infile=1;

load data local infile '文件路径' into table 表名 fields terminated by '字段分隔符' lines terminated by '换行符';

insert

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案

1、如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

-- 原始方式为:
insert into tb_test values(1, 'Tom');
insert into tb_test values(2, 'Cat');
insert into tb_test values(3, 'Jerry');

--优化后的方案为:
insert into tb_test values(1, 'Tom')，(2, 'Cat')，(3, 'Jerry') ;

2、在事务中进行数据插入。

begin;
insert into tb_test values(1, 'Tom');
insert into tb_test values(2, 'Cat');
insert into tb_test values(3, 'Jer');
commit;

3、数据有序插入

insert into tb_test values(4, 'Tim');
insert into tb_test values(1，'Tom');
insert into tb_test values(3, 'Jer');
insert into tb_test values(2, 'Cat');

-- 优化后：
insert into tb_test values(1, 'Tom');
insert into tb_test values(2, 'Cat');
insert into tb_test values(3, 'Jer');
insert into tb_test values(4, 'Tim');

order by

两种排序方式：

第一种是通过对返回数据进行排序，也就是通常说的filesort排序，所有不是通过索引直接返回排序结果的排序都叫FileSort排序

第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

orderby后边的多个排序字段要求尽量排序方式相同

order by后边的多个排序字段顺序尽量和组合索引字段顺序一致

Filesort 的优化

通过创建合适的索引，能够减少Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快Filesort的排序操作。对于Filesort，MySQL 有两种排序算法:

1)两次扫描算法 : MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区sort buffer中排序，如果sort buffer不够，则在临时表temporary table中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。

2)一次扫描算法：一次性取出满足条件的所有字段，然后在排序区sortbuffer中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

MySQL通过比较系统变量max_length_for_sort_data的大小和Query语句取出的字段总大小，来判定是否那种排序算法，如果max_length_for_sort_data更大，那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高sort_buffer_size 和max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。