优化

概念

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

查看SQL执行的频率

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

命令

查看当前会话统计结果（本次客户端启动到现在的统计结果）

show session status like 'Com_______'；(模糊匹配，七个下划线)

查看自服务器上次启动到现在的统计结果

show global status like 'Com_______'；(模糊匹配，七个下划线)

查看针对Innodb引擎的统计结果

show status like 'Innodb_rows_%';

参数含义

定位低效率SQL

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

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

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

show processlist

show processlist;

explain分析执行计划

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

查看某个SQL语句执行的信息：

explain 执行计划(即一个SQL语句);

字段解释：

id

id字段是select查询的序列号，是一组数字，表示的是查询中操作表的顺序

有三种情况：

1.id相同表示加载表的顺序是从上到下

2.id不同。id值越大，优先级越高，越先被执行

3.id有相同也有不同。id相同的可以认为是一组，从上到下顺序执行；在所有组中，id越大的越先执行

select_type

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

select_type	含义
SIMPLE	简单的select查询，不包含子查询或UNION
PRIMARY	查询中包含子查询时，最外层的查询即为该标识
SUBQUERY	在select和where中包含子查询
DERIVED	在FROM中包含子查询，被标记为衍生表
UNION	若SELECT语句出现在UNION后，则该语句被标记为UNION
UNION RESULT	从UNION表获取结果的SELECT语句

type

type显示的是访问类型，是较为重要的一个指标，可取值为：

type	含义
NULL	MySQL不访问任何表，索引，直接返回结果（查函数等）
syste	系统表，表示直接从内存读取数据，不从磁盘读取，但从5.7以上的版本不再显示system，直接显示All
const	查询条件(where后)的字段为主键索引或唯一索引，且判断条件为常量
eq_ref	左表有主键，且左表的每一行和右表的每一行刚好匹配
ref	左表有普通索引，和右表匹配时可能会匹配多行
range	范围查询，where后出现between,<,>,in等操作
index	需要扫描索引上的全部数据
all	全表扫描

该表从上到下效率一次降低

table

显示这一步所访问的表名，可以是简称

rows

扫描行的数量

key

possible_keys:显示哪一列可能使用索引

key:实际哪一列使用的索引，若为NULL，则没有使用索引
key_len:表示索引中使用的字节数，该值为索引字段最大可能长度，再不损失精确性的前提下，长度越短越好，计算如下

extra

其他的额外的执行计划信息

show profile分析SQL

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

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

select @@have_profiling;

set profiling=1; -- 开启profiling

查看之前SQL语句执行的耗时

在执行一些SQL后，可以通过show profiles指令来查看SQL语句执行的耗时

show profiles;

查看某个SQL语句每个线程的耗时

也可以查看单个SQL语句每个线程的状态和消耗的时间

show profile for query Query_ID;

-- Query_ID即为用show profiles查看后的ID，在经过新的SQL语句后会发生变化

可以进一步选择all、cpu、block io、context switch、page faults等明细类型类查看MySQL在使用什么资源上消耗了过高的时间。

例：选择查看CPU耗费的时间

show profile cpu for query 133;

trace分析优化器执行计划

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

步骤：

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

然后再执行一条SQL语句，最后检查information_schema.optimizer_trace就可以直到MySQL是如何执行SQL的

select * from information_schema.optimizer_trace \G;

information_schema是MySQL自带的数据库

optimizer_trace是其中的一张表

该命令需要在cmd中使用才能查看，不能直接在工具上查看

索引优化

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

避免索引失效

全职匹配

当一张表的某个索引是组合索引，包含多个字段时，搜索时where条件同时包含这三个字段（顺序随意），则索引生效

最左前缀法则

如果某个索引包含多个字段，要遵循最左前缀法则。即查询从索引的最左前列开始，并且不跳过索引中的列。

例：某个索引创建时包含a,b,c三个字段，且创建该索引时的字段顺序也为a,b,c

1.where条件只出现a,索引生效

2.where条件出现a,b，索引生效

3.where条件出现a,c，只有索引a生效（b字段被跳过了）

4.where条件出现b,c，索引不生效(最左列的索引未出现)

5.where条件出现b,a，索引生效（MySQL会自动优化排序，因此只要该条件出现即可，不需要排序）

满足该法则时，索引生效

其他匹配原则

注：以下例子中，索引字段为name，status，address，顺序从左到右

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

例：

其中，name和status可以使用索引，但address不能

2.索引列上进行运行操作，索引会失效

例：

其中，索引列name进行了substring截取运算，索引不生效

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

其中，status字段的类型是varchar，在执行时，MySQL会进行优化，将数字1转化为字符串，因此不影响查询，但是无法使用索引

4.尽量使用覆盖索引，避免select *

5.用or分割开的条件，需要or前后的列都用到索引，索引才能生效（且or前后也需要遵循最左前缀法则）

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

解决方式：不用*，使用索引列

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

评估准则：多数里找少数，索引生效，少数找多数，索引失效（包括is NULL和is NOT NULL，in和not in等）

注：当使用的索引为主键索引时，都会使用索引（不会回表）

8.单列索引和复合索引，尽量使用复合索引

如果一张表有多个单列索引，即使where中都使用了这些索引列，也只有一个最优索引生效，而组合索引匹配的概率会更高

最优索引：满足条件(仅单个字段)后得到的数据在该字段中的重复度最低的字段的索引，即筛选后得到的数据尽量少，以便进行后续的匹配筛选

SQL优化

大批量插入数据

当我们使用load命令将文件中的数据导入时，适当的设置可以提高导入的效率

从文件向表中导入数据

1.首先要检查一个全局系统变量'local_infile'的状态，如果为OFF，则说明不可用

show global variables like 'local_infile';

2.修改local_infile值为ON，开启local_infile

set global local_infile=1;

3.加载数据

格式：

load data local infile 文件地址 into table 表名 fields terminated by 文件中字段之间的分隔符 lines terminated by 换行符

注：分隔符和换行符都要用单引号

InnoDB类型的表提高导入效率的方式

主键顺序插入

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

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

关闭唯一性校验

当表中包含唯一索引，且可以保证数据不重复时，可以先关闭唯一性校验，导入数据后在恢复唯一性校验

set UNIQUE_CHECKS=0; -- 关闭唯一性校验

导入数据

set UNIQUE_CHECKS=1; -- 开启唯一性校验

优化insert语句

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

一次插入多个数据

在执行insert操作时，客户端需要和数据库建立一次连接，并在操作后断开，因此需要插入多行数据时，同时插入多个数据的效率比分开插入多个数据的效率更高

在事务中进行数据插入

数据有序插入

优化order by语句

两种排序方式

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

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

1.使用排序时搜索的数据尽量为索引数据

2.order by后边的多个排序字段要求尽量排序方式相同

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

Filesort的优化

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

对于Filesort，MySQL有两种排序算法

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

2.一次是扫描算法：一次性取出满足条件的所有字段，然后再排序去sort buffer中排序后直接输出结果集。排序时内存消耗较多，但是排序效率更高

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

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

优化子查询

多表查询的效率比子查询更高

原因：多表查询时，MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作

因此在编写SQL语句时，应尽量使用多表查询来代替子查询

优化limit查询

一般分页查询时，通过创建覆盖索引能够比较好的提高性能

例：

limit 900000,10，此时需要MySQL排序前900010条记录，仅仅返回900000 - 900010的记录，其他记录丢弃，查询排序的代价非常大。

优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

例：

先使用分页查询得到在900000 - 900010中有索引的字段的数据，在关联原表得到其他内容

优化思路二

该方案适用于主键自增的表，可以把limit查询转换成某个位置的查询