MySQL性能优化

1 优化思路

​ 说到性能调优，大部分时候想要实现的目标是让查询更快。一个查询的流程又是由很多个环节组成的，每个环节都会消耗时间。要减少查询所消耗的时间，就要从每一个环节入手。

确定环境：

select version();
show variables like '%engine%';

2 连接-配置优化

​ 第一个环节是客户端连接到服务端，连接这一块有可能出现的问题是服务端连接数不够导致应用程序获取不到连接。比如Mysql：error 1040：Too many connections的错误。这个是超过了服务端设置的最大并发连接数。

​ 可以从两个方面来解决连接数不够的问题：

​ 1.从服务端来说，可以增加服务端的可连接数。如果有多个应用或者很多请求同时访问数据库，连接数不够的时候，可以:

​ （1）增加可用连接数，修改max_connections的大小：

show varibales like 'max_connections'; --修改最大连接数，当有多个应用连接的时候

​ （2）及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间都是28800秒，8小时，可以把这个值调小。

show global variables like 'wait_timeout'; --及时释放不活动的连接，注意不要释放连接池还在使用的连接

​ 2.从客户端来说，可以减少从服务端获取的连接数。如果想要不是每一次执行SQL都要创建一个新的连接，这时可以引入连接池，可以实现连接的重用。常见的连接池有老牌的DBCP和C3P0，阿里的Druid，Hikari（Spring Boot2.X版本默认的连接池）。连接池不是越大越好，只要维护一定数量大小的连接池，其他的客户端排队等待获取连接就可以了。有的时候连接池越大，效率反而越低。

​ Druid默认最大连接池大小是8。Hikari默认最大连接池大小是10。在Hikari的githuhb文档中，给出了一个PostgreSQL数据库建议的设置连接池小大的公式。它的建议是机器核数乘以2加1.也就是说，4核的机器，连接池维护9个连接就够了。这个公式从一定程度上来说对其他数据库也是适用的。每一个连接，服务端都需要创建一个线程去处理它。连接数越多，服务端创建的线程数就会越多。问题：CPU是怎么同时执行远远超过它的核数大小的任务的？分配时间片。上下文切换。而CPU的核数是有限的，频繁的上下文切换回造成比较大的性能开销。

​ 这里说到了从数据库配置的层面去优化数据库。不管是数据库本身的配置，还是安装这个数据库服务的操作系统的配置，对于配置进行优化，最终的目标都是为了更好的发挥硬件本身的性能，包括CPU，内存，磁盘，网络。在不同的硬件环境下，操作系统和MySQL的参数的配置是不同的，没有标准的配置。 一般修改配置的工作是由专业的DBA完成，也有一些工具可以给出推荐值。

3 架构优化

3.1 缓存

​ 在系统中有一些很慢的查询，要么数据量大，要么关联的表多，要么是计算逻辑非常复杂，这样的查询每次会占用连接很长时间。所以为了减轻数据库的压力，和提升查询效率，把数据放到内存中缓存起来，比如使用redis。

​ 缓存适用于实时性不是很高的业务，比如报表数据，一次查询要2分钟，但是一天只需要更新一次。

​ 独立的缓存服务，属于架构层面的优化。

3.2 集群，主从复制

​ 在分布式中，有一种提升可用性的手段叫做冗余，也就是创建集群。

​ 集群的话必然会面临一个问题，就是不同的节点之间数据一致性的问题。如果同时读写多台数据库节点，怎么让所有的节点数据保持一致？这个时候需要用到复制技术（replication），被复制的节点称为master，复制的节点称为slave。Slave本身也可以作为其他节点的数据来源，这个叫做级联复制。之前说过，MySQL所有的更新语句都会记录到Server层binlog中。从服务器会不断获取主服务器的binlog文件，然后解析里面的SQL语句，在从服务器上面执行一遍，保持主从的数据一致。

​ 这里面涉及到三个线程，连接到master获取binlog，并且解析binlog写入中继日志，这个线程叫做IO线程。Master节点上有一个log dump线程，是用来发送binlog给slave的。从库的SQl线程，是用来读取relay log，把数据写入到数据库的。

​ 做了主从复制配置之后，我们只把数据写入master节点，而读的请求可以分担到slave节点。我们把这种方案叫做读写分离。

​ 对于读多写少的项目来说，读写分离对于减轻主服务器的访问压力很有用。

3.3 分库分表

​ 在集群架构中，所有的节点存储的都是相同的数据。如果单张表存储的数据过大的时候，比如一张表有上亿的数据，每天以百万的量级增加，单表的查询性能还是会大幅下降。这时就要用到第二手段，叫做分片。把单个节点的数据分散到多个节点存储，减少存储和访问压力，这个就是分库分表。

​ 分库分表总体上可以分为两类：垂直分库，减少并发压力；水平分表，解决存储瓶颈。

1.垂直分库：把一个数据库按照业务拆分成不同的数据库

2.水平分库分表：把单张表的数据按照一定的规则分布到多个数据库

4 优化器-SQL优化

​ 优化器的作用是对SQL语句进行优化分析，生成执行计划。首先，要用到服务端的慢查询日志。

4.1 慢查询日志slow query log

4.1.1 打开慢查询开关

因为开启慢查询日志是有代价的（跟binlog，optimizer-trace一样），所以它默认是关闭的.

show variables like 'slow_query%';

除了这个开关，还有一个参数，控制执行超过多长时间的SQL才记录到慢日志，默认是10秒。如果改成0秒的话就是记录所有的SQL。

show variables like '%long_query%';

参数的两种修改方式：

​ 1.set动态修改参数（重启后失效）

set @@global.slow_query_log=1; --1开启，0关闭，重启后失效
set @@global.long_query_time=3; --默认10秒，另开一个窗口后才会查到最新值

​ 2.修改配置文件my.cnf，以下配置定义了慢查询日志的开关，慢查询的时间，日志文件的存放路径。

show_query_log=ON
long_query_time=2
show_query_log_file=/var/lib/mysql/localhost-slow.log

4.1.2 慢日志分析

1.日志内容

2.mysqldumpslow

MySQL提供了mysqldumpslow的工具，在Mysql的bin目录下。例如：查询用时最多的10条慢SQL：

mysqldumpslow -s t -t 10 -g 'select' /var/lib/mysql/localhost-slow.log

​ Count代表这个SQL执行了多少次；Time代表执行的时间，括号中是累计时间；Lock表示锁定的时间，括号是累计；Rows表示返回的记录数，括号是累计。当然，有的时候查询慢，不一定是SQL语句的问题，也有可能是服务器状态的问题。所以也要掌握一些查看服务器和存储引擎状态的命令。

4.1.3 其他系统命令

1.Show processlist 运行线程

show full processlist;

这是一个很重要的命令，用于显示用户运行线程。可以根据id号kill线程。也可以查表，效果一样：

select * from information.processlist

列	含义
id	线程的唯一标识，可以根据它kill线程
User	启动这个线程的用户，普通用户只能看到自己的线程
Jpst	哪个端口发起的连接
db	操作的数据库
Command	线程的命令 https://dev.mysql.com/doc/refman/5.7/en/thread-commands.html
Time	操作持续时间，单位秒
State	线程状态，比如查询所有可能有copying to tmp table, Sorting result, Sending data https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html
Info	SQL语句的前100个字符。如果要查看完整的SQL语句，用show full processlist

2.Show status 服务器运行状态

Show status用于查看mysql服务器运行状态（重启后会清空）

show global status;

有session和global两种作用域，格式：参数-值。可以通过like带通配符过滤：

showo global status like 'com_select'; --查看select次数

3.show engine 存储引擎运行信息

​ Show engine用来显示存储引擎的当前运行信息，包括事务持有的表锁，行锁信息；事务锁等待情况；线程信号量等待；文件IO请求；buffer pool统计信息。 例如：show engine innodb status；开启InnoDB监控：

set global innodb_status_output=ON;
set global innodb_status_output_locks=ON;

​ 很多开源的MySQL监控工具，其实他们的原理也都是读取的服务器，操作系统，MYSQL服务的状态和变量。之后，MySQL提供了一个执行计划的工具。通过EXPLAIN可以模拟服务端执行SQL查询语句的过程。通过这种方式可以分析语句或者表的性能瓶颈。

​ MYSQL5.6.3以前只能分析select，之后可以分析update，delete，insert。

4.2 EXPLAIN执行计划

先创建三张表。课程表，老师表，老师联系方式表（没有任何索引）。

drop table if_exists course;
create table 'course' (
	'cid' int(3) default null,
    'cname' varchar(20) default null,
    'tid' int(3) default null
) engine=InnoDB default charrest=utf8mb4;

drop table if_exists teacher;
create table 'teacher' (
	'tid' int(3) default null,
    'tname' varchar(20) default null,
    'tcid' int(3) default null
) engine=InnoDB default charrest=utf8mb4;

drop table if_exists teacher_contact;
create table 'teacher_contact' (
	'tcid' int(3) default null,
    'phone' varchar(200) default null,
) engine=InnoDB default charrest=utf8mb4;

insert into 'course' values(1, 'mysql', 1);
insert into 'course' values(2, 'jvm', 1);
insert into 'course' values(3, 'juc', 2);
insert into 'course' values(4, 'spring', 3);
insert into 'teacher' values(1, 'qs', 1);
insert into 'teacher' values(2, 'hh', 2);
insert into 'teacher' values(3, 'mic', 3);
insert into 'teacher_contact' values(1, '13688888888');
insert into 'teacher_contact' values(2, '13688888889');
insert into 'teacher_contact' values(3, '13688888810');

Explain的结果字段有很多，详细分析一下：

4.2.1 Id

Id是查询序列编号，每张表都是单独访问的，一个select就会有一个序号。

（1）Id值不同的时候，先查询id值大的（先大后小）

explain select tc.phone 
	from teacher_contact tc
	where tcid = (
    	select tcid from teacher t where t.tid = (
        	select c.tid from course c where c.cname='mysql'
        )
    )

查询顺序：course c——teacher t——teacher_contact tc

先查课程表，再查老师表，最后查老师联系表。子查询只能以这种方式进行，只有拿到内层的结果之后才能进行外层的查询。

（2）id值相同

explain select t.tname, c.name, tc.phone
	from teacher t, course c, teacher_contact tc
	where t.tid = c.tid and t.tcid = tc.tcid and (c.cid = 2 or tc.cid = 3)

Id值相同时，表的查询顺序是从上往下顺序执行。例如这次查询的id都是1（说明子查询被优化器转换成了连接查询），查询的顺序是teacher t（3条）——course c（4条）——teacher_contact tc（3条）。

​ （3）如果id有相同也有不同，就是id不同的先大后小，相同的从上往下。

​ 在连接查询中，先查询的叫做驱动表，后查询的叫做被驱动表。应该先查小表（得到的结果少），小表驱动大表。

4.2.2 Select type 查询类型

​ 这里并没有列出全部（其他：dependent union，dependent subquery，materialized，uncacheable subquery，uncacheable union）。下面列举了一些常见的查询类型：

​ 1.simple：简单查询，不包含子查询，不包含关联查询union。

explain select * from teacher;

再看一个包含子查询的案例：

--查询mysql课程老师的手机号
explain select tc.phone from tescher_contact tc
	where tcid = (
    	select tcid from teacher t where t.tid = (
        	select c.tid from course c where c.cname='mysql'
        )
    )

​ 2.primary：子查询sql语句中的主查询，也就是最外层的查询

​ 3.subquery：子查询中所有的内层查询都是该类型。

​ 4.derived：衍生查询，表示在得到最终查询结果之前会用到的临时表，例如：

explain select cr.cname from (
	select * from course where tid=1 union
    select * from course where tid=2
) cr;

​ 对于关联查询，先执行有便的table（union），再执行左边的table，类型是derived

​ 5.union：同上例

​ 6.union result：主要是显示哪些表之间存在union查询。<union2,3>代表id=2和id=3的查询存在union，同上例。

4.2.3 Type 连接类型

​ 所有连接类型中，上面的最好，越往下越差。在常用的连接类型中：system>const>eq_ref>ref>range>all。这里并没有列举全部（其他: fulltext, ref_or_null, index_merger, unique_subquery, index_subquery）。以上访问类型除了all，都能用到索引。

​ 1.const：主键索引或者唯一索引，只能查到一条数据的SQL

drop talbe if_exists single_data;
create table single_data(
	id int(3) primary key,
    content varchar(20)
);
insert into single_data values(1,'a');
explain select * from single_data a where id=1';

2.system：system是const的一种特例，只有一行满足条件，对于NyISAM，Memory的表，只查询到一条记录，也是system。比如系统库的这张表（8.0的版本中系统表全部变成InnoDB存储引擎了）。

explain select * from mysql.proxies_priv;

3.eq_ref：通常出现在多表的join查询，被驱动表通过唯一性索引（unique或primary key）进行访问，此时被驱动表的访问方式就是eq_ref。eq_ref是出const之外最好的访问类型。先删除teacher表中多余的数据，teacher_contact有3条数据，teacher表有三条数据。teacher_contact表的tcid（第一个字段）创建主键索引。

-- alert table teacher_contact drop primary key
alert table teacher_contact add primary key(tcid);

explain select t.tcid from teacher t, teacher_contact tc where t.tcid=tc.tcid

此时的执行计划（先大后小，从上往下，tc是被驱动表。Tc表是eq_ref）：被驱动表用主键进行访问

小结：以上三种system，const，eq_ref，都是可遇不可求的，基本上很难优化到这个状态。

​ 4.ref：查询用到了非唯一性索引，或者关联操作只使用了索引的最左前缀

​ 为teacher表的tcid（第三个字段）创建普通索引。

-- alert table teacher drop primary key
alert table teacher add primary key(tcid);

explain select * from teacher where tcid=3;

5.range：索引范围扫描。如果where后面是between and 或 < 或 > 或 >= 或 <= 或 in这些，type类型就为range。不走索引一定是全表扫描（ALL），所以先加上普通索引。

-- alert table teacher drop index idx_tid
alert table teacher add index idx_tid(tid)

explain select * from teacher where tid<3;
-- 或
explain select * from teacher where tid between 1 and 2;

IN查询也是range（字段上有主键索引）

explain select * from teacher_contact where tcid in(1,2,3)

6.index：Full Index Scan，查询全部索引中的数据（比不走索引要快）

explain select tid from teacher;

7.all：Full Table Scan，如果没有索引或者没有用到索引，type就是ALL。代表全表扫描。

8.Null：不用访问表或者索引就能得到结果：

explain select 1 from dual where 1=1

总结：一般来说，需要保证查询的type至少达到range级别，最好能达到ref。all（全表扫描）和index（查询全部索引）都是需要优化的

4.2.4 Possible_key, key

​ 可能用到的索引和实际用到的索引。如果是null就代表没有用到索引。Possible_key可以有一个个或者多个，可能用到索引不代表一定用到索引。反过来，possible_key为空，key可能有值吗？表上创建联合索引：

alert table user_innodb add index name_phone(name, phone);
explain select phone from user_innodb where phone = '126';

结论：是有可能的（这里是覆盖索引的情况）。如果通过分析发现没有用到索引，就要检查SQL或者创建索引。

4.2.5 key_len

​ 索引的长度（使用的字节数）。跟索引字段的类型，长度有关。

​ 表上有联合索引：KEY ‘comidx_name_phone’(‘name’, ‘phone’)

explain select * from user_innodb where name='qs';

Key_len = 1023，为什么不是255+11=266呢？这里的索引只用到了name字段，定义长度255.utf8mb4编码一个字符4个字节。所以是255*4=1020.使用变长字段varchar需要额外增加2个字节，允许null需要额外增加1个字节。所以一共1023.

4.2.6 Rows

MySQL认为扫描多少行才能返回请求的数据，是一个预估值。一般来说行数越少越好。

4.2.7 Filtered

​ 这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，它是一个百分比。如果比例很低，说明存储引擎层返回的数据经过大量过滤，这个是会消耗性能的，需要关注。

4.2.8 Ref

使用哪个列或者常数和索引一起从表中筛选数据。

4.2.9 Extra

执行计划给出额外的信息说明。

​ 1.using index：用到了覆盖索引，不需要回表

​ 2.using where：使用了where过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤（跟是否使用索引没有关系）

explain select * from user_innodb where phone = '1345353545';

​ 3.using index condition：索引条件下推

​ 4.using filesort：不能使用索引来排序，用到了额外的排序（跟磁盘或文件没有关系）。需要优化。（复合索引的前提）

alert table user_innodb drop index comidx_name_phone;
alert table user_innodb add index comidx_name_phone(name,phone);
explain select * from user_innodb where name = 'qs' order by id;

​ 5.using temporary：用到了临时表，例如（以下不是全部情况）

​ （1）distinct非索引列（确定tid字段是否有索引）

explain select distinct(tid) from teacher t;

​ （2）group by非索引列

explain select tname from teacher group by tname;

​ （3）使用join的时候，group任意列（t表的结果）

explain select t.tid from teacher t join course c on t.tid - c.tid group by t.tid;

需要优化，比如创建复合索引。

如果需要具体的cost信息，可以用：EXPLAIN FORMAT = JSON。如果觉得explain还不够细，可以开启optimizer trace。

show variables like 'optimizer_trace';
set optimizer_trace='enabled=on';
select * from information_schema.optimizer_trace\G

总结一下：模拟优化器执行SQL查询语句的过程，来知道MySQL是怎么处理一条SQL语句的，通过这种方式我们可以分析语句或者表的性能瓶颈。分析出问题之后，就是对SQL语句的具体优化。

4.3 SQL与索引优化

​ 当我们的SQL语句比较复杂，有多个关联和子查询的时候，就要分析SQL语句有没有改写的方法。举个简单的例子，一摸一样的数据：

-- 大偏移量的limit
select * from user_innodb limit 900000,10;
-- 改成先过滤ID，再limit
select * from user_innodb where id > 900000 limit 10;

一个sum语句性能提升3倍的优化案例： https://gper.club/articles/7e7e7f7ff4g5egc2g63 这一步是SQL语句的优化，目的是让SQL语句的cost更小。

5 存储引擎与表结构优化

1.存储引擎的选择

​ 为不同的业务表选择不同的存储引擎，例如：查询插入多的业务表，用MyISAM。临时数据用memory。 常规并发多更新的表用InnoDB。

​ 2.分表或者分区

​ 交易历史表：在年底为下一年度建立12个分区，每个月一个分区

​ 渠道交易表：分成：当日表，当月表，历史表，历史表再做分区

​ 3.字段定义：

​ 原则：使用可以正确存储数据的最小数据类型。为每列选择合适的字段类型：

​ （1）整数类型：

Int有6中类型，不同的类型的最大存储范围是不一样的，占用的存储空间也是不一样的。举 例：存储性别字段？用tinyint，因为enum也是整数存储。

​ （2）字符类型：变长情况下，varchar更节省空间，但是对于varchar字段，需要一个字节来记录长 度，比如：联系地址。固定长度的用char，不要用varchar，比如：行政区编码。

​ （3）非空：非空字段尽量定义成not null，提供默认值，或者使用特殊值，空串代替null。NULL类 型的存储，优化，使用都会存在问题。

​ （4）不要使用外键，触发器，视图：降低了可读性；影响数据库性能，应该把计算的事情交给程序， 数据库专心做存储；数据的完整性应该在程序中检查。

​ （5）大文件存储：图片和音频，视频怎么存储？不要用数据库村粗图片（比如base64编码）或者 大文件。把文件放在NAS上，数据库只存储URI（相对路径），在应用中配置NAS服务器地址。

​ （6）表拆分或者字段冗余：

​ 表拆分：将不常用的字段拆分出去，避免列数过多和数据量过大。淘宝的商品表。商户信息表。 比如在业务系统中，要记录所有接收和发送的消息，这个消息是XML格式的，用blob或者text存 储，用来追踪和判断重复，可以建立一张表专门用来存储报文。

​ 字段冗余：合同表的客户姓名。

6 总结：优化体系

除了对于代码，SQL语句，表定义，架构，配置优化之外，业务层面的优化也不能忽视，举几个例子：

​ （1）在某一年的双十一，为什么会做一个充值到余额宝和余额有奖金的活动？现在会推荐大家用花呗支付，而不是银行卡支付？因为使用余额或者余额宝付款是记录本地或者内部数据库中，而使用银行卡付款，需要调用接口，操作内部数据库肯定更快。

​ （2）在某一年的双十一，为什么在凌晨禁止查询今天之外的账单？为什么小鸡的饲料发放延迟了？这时一种降级措施，用来保证当前最核心的业务。

​ （3）某银行的叫记录，只能按月份查询。

​ （4）最近几年的双十一，为什么11月1日就开始了？变成了各种定金红包模式？预售分流。

​ 在应用层面同样有很多其他的方案来优化，达到尽量减轻数据库的压力的目的，比如限流，或者引入MQ削锋，等等。用数据库慢，不代表数据库本身慢，有的时候还要往上层去优化。当然，如果关系型数据库解决不了的问题，我们可能需要用到搜索引擎或者大数据的方案了，并不是所有的数据都要放到关系型数据库存储。

7 优化案例

​ 服务端状态分析：

​ 如果出现连接变慢，查询被阻塞，无法获取连接的情况。

​ 1.重启！

​ 2.show processlist查看线程状态，连接数数量，连接时间，状态

​ 3.查看锁的状态

​ 4.kill有问题的线程

​ 对于具体的慢SQL：

​ 一，分析查询基本情况

​ 涉及到的表的表结构，字段的索引情况，每张表的数据量，查询的业务含义。这个非常重要，因为 有时候你会发现SQL根本没必要这么写，或者表设计是有问题的。

​ 二，找出慢的原因

​ 1.查看执行计划，分析SQL的执行情况，了解表访问顺序，访问类型，索引，扫描行数等信息

​ 2.如果总体的时间很长，不确定哪一个因素影响最大，通过条件的增减，顺序的调整，找出引起查 询慢的主要原因，不断的尝试验证。找到原因：比如是没有走索引引起的，还是关联查询引起的，还order by引起的。找到原因后：

​ 3.对症下药，

​ （1）创建索引或者联合索引

​ （2）改写SQL，这里需要平时积累经验，例如：a）使用小表驱动大表，b）用join代替子查询，c） not exist转换为left join is null，d）or改成union，e）如果结果集允许重复的话，使用union all代替union，f）大偏移的limit，先过滤再排序。

​ 如果sql本身解决不了，就要上升到表结构和架构了。

​ （3）表结构（冗余，拆分，not all等），架构优化（缓存，读写分离，分表分库）

​ （4）业务层的优化，必须条件是否必要