前言

在进行优化讲解之前，先请大家记住不要听信你看到的关于优化的“绝对真理”，而应该是在实际的业务场景下通过测试来验证你关于执行计划以及响应时间的假设。 实际开发中的Mysql性能优化只能是立足于具体业务场景，选择合适的MySQL服务版本，制定切实可行的优化方案。需要我们格外注意的是，在做性能优化的同时，也是伴随着新的风险产生，因此我们在做Mysql性能优化的时候是需要格外小心谨慎的！

一、优化要考虑的问题

优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统！
优化手段有很大的风险，一定要意识到和预见到！
任何的技术可以解决一个问题，但必然存在带来一个问题的风险！
对于优化来说调优而带来的问题,控制在可接受的范围内才是有成果。
保持现状或出现更差的情况都是失败！

二、优化的需求

稳定性和业务可持续性,通常比性能更重要！
优化不可避免涉及到变更，变更就有风险！
优化使性能变好，维持和变差是等概率事件！
优化应该是各部门协同，共同参与的工作，任何单一部门都不能对数据库进行优化！

所以优化工作,是由业务需要驱使的！！！

三、开发中Mysql优化由谁参与

在进行数据库优化时，应由数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等，业务相关人员共同参与。

四、优化的思路

4.1 优化的方向

在数据库优化上有两个主要方向：即安全与性能。

安全 ---> 数据安全性
性能 ---> 数据的高性能访问下面我们主要从性能优化方向进行介绍

4.2优化的维度

网络异常，图片无法展示

从上图中可以看出，我们把数据库优化分为四个纬度：硬件，系统配置，数据库表结构，SQL及索引

硬件： CPU、内存、存储、网络设备等

系统配置： 服务器系统、数据库服务参数等

数据库表结构： 高可用、分库分表、读写分离、存储引擎、表设计等

Sql及索引： sql语句、索引使用等

从优化成本进行考虑：硬件>系统配置>数据库表结构>SQL及索引
从优化效果进行考虑：硬件<系统配置<数据库表结构<SQL及索引

4.3 优化的工具

检查问题常用工具

mysqladmin #mysql客户端，可进行管理操作
mysqlshow #功能强大的查看shell命令
show [SESSION | GLOBAL] variables #查看数据库参数信息
SHOW [SESSION | GLOBAL] STATUS #查看数据库的状态信息
SHOW ENGINE INNODB STATUS Innodb #引擎的所有状态
information_schema #获取元数据的方法
SHOW PROCESSLIST #查看当前所有连接session状态
explain #获取查询语句的执行计划
show index #查看表的索引信息
slow-log #记录慢查询语句
mysqldumpslow #分析slowlog文件的

不常用但好用的工具

zabbix #监控主机、系统、数据库（部署zabbix监控平台）
mysqlslap #分析慢日志
sysbench #压力测试工具
workbench #管理、备份、监控、分析、优化工具（比较费资源）
pt-query-digest #分析慢日志
mysql profiling #统计数据库整体状态工具
Performance Schema mysql #性能状态统计的数据

4.4 数据库使用优化思路

应急调优的思路：

针对突然的业务办理卡顿，无法进行正常的业务处理！需要立马解决的场景！

show processlist（查看链接session状态）
explain(分析查询计划)，show index from table（分析索引）
通过执行计划判断，索引问题（有没有、合不合理）或者语句本身问题
show status like '%lock%'; # 查询锁状态
SESSION_ID; # 杀掉有问题的session

慢日志查询分析SQL中的慢查询

常规调优的思路：

针对业务周期性的卡顿，例如在每天10-11点业务特别慢，但是还能够使用，过了这段时间就好了。

查看slowlog，分析slowlog，分析出查询慢的语句。
按照一定优先级，进行一个一个的排查所有慢语句。
分析top sql，进行explain调试，查看语句执行时间。
调整索引或语句本身。

五、查询优化

5.1 MySQL查询流程

我们该如何进行sql优化呢，首先我们需要知道，sql优化其实主要是解决查询的优化问题，所以我们先从数据库的查询开始入手，下面这幅图显示了查询的执行路径：

客户端将查询发送到服务器；
服务器检查查询缓存，如果找到了，就从缓存中返回结果，否则进行下一步。
服务器解析，预处理。
查询优化器优化查询
生成执行计划，执行引擎调用存储引擎API执行查询
服务器将结果发送回客户端。

网络异常，图片无法展示

查询缓存 在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据，如果命中缓存直接从缓存中拿到结果并返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行。

语法解析和预处理器 MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。

查询优化器 语法树被校验合法后由优化器转成查询计划，一条语句可以有很多种执行方式，最后返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

查询执行引擎 在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。最常使用的也是比较最多的引擎是MyISAM引擎和InnoDB引擎。mysql5.5开始的默认存储引擎已经变更为innodb了。

5.2 查询优化

前面的查询流程分析，我们大概了解了MySQL是如何执行的，其中涉及到的部分我们在后面会一一道来。现在我们先从查询优化部分开始。

sql是我们和数据库交流最重要的部分，所以我们在调优的时候，需要花费的大量时间就在sql调优上面。常见的分析手段有慢查询日志，EXPLAIN 分析查询，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

创建测试用表，写入1000W数据

创建以下表：

CREATE TABLE `tb_table` (
  `id` int(11) NOT NULL,
  `username` varchar(20) NOT NULL COMMENT '姓名',
  `password` varchar(32) NOT NULL COMMENT '密码',
  `email` varchar(50) NOT NULL COMMENT '邮箱',
  `code` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

执行以下sql，批量添加1000w条数据：

drop procedure if exists tb_insert; 
DELIMITER；
CREATE PROCEDURE tb_insert()
BEGIN
DECLARE i INT;
SET i = 0;
START TRANSACTION;
WHILE i < 10000000 DO
    INSERT INTO tb_table VALUES (i,CONCAT("张三",i),MD5(i),CONCAT('张三',i,'@itcast.cn'),i);
    SET i = i+1;
END WHILE;
COMMIT; 
END; 
DELIMITER ; 
call tb_insert();

5.2.1 慢查询

SHOW VARIABLES LIKE '%slow%';
SET GLOBAL slow_query_log=TRUE; #临时开启，mysql重启后失效
SHOW VARIABLES LIKE '%long_query_time%';
SET long_query_time=2; #设置慢日志触发条件,查询时间
//在windows下使用
set global slow_query_log_file='D:\mysqlslow.log';

慢查询日志开启

在配置文件my.cnf或my.ini中在[mysqld]一行下面加入两个配置参数

log-slow-queries=/data/mysqldata/slow-query.log
long_query_time=5

log-slow-queries参数为慢查询日志存放的位置，一般这个目录要有mysql的运行帐号的可写权限，一般都将这个目录设置为mysql的数据存放目录；

long_query_time=5中的5表示查询超过5秒才记录；

还可以在my.cnf或者my.ini中添加log-queries-not-using-indexes参数，表示记录下没有使用索引的查询。

慢查询分析

我们可以通过打开log文件查看得知哪些SQL执行效率低下，从日志中，可以发现查询时间超过5 秒的SQL，而小于5秒的没有出现在此日志中。

进入mysql容器

docker exec -it mysql /bin/bash

慢查询日志如图：

网络异常，图片无法展示

如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带，mysql的安装目录）来对慢查询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总，显示汇总后摘要结果。

在docker中的目录为（个别可能不一样）：

网络异常，图片无法展示

可以使用命令查看帮助

mysqldumpslow -help

进入log的存放目录，运行 mysqldumpslow 慢日志文件名：

[root@mysql_data]# mysqldumpslow slow-query.log
Reading mysql slow query log fromslow-query.log
Count: 2 Time=11.00s (22s) Lock=0.00s (0s)Rows=1.0 (2), root[root]@mysql
select count(N) from t_user;

如图：

网络异常，图片无法展示

mysqldumpslow命令

/path/mysqldumpslow -s c -t 10/database/mysql/slow-query.log

这会输出记录次数最多的10条SQL语句，其中：

-s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的平均数
-t, 是top n的意思，即为返回前面多少条的数据；
-g, 后边可以写一个正则匹配模式，大小写不敏感的；

例如：

/path/mysqldumpslow -s r -t 10/database/mysql/slow-log

得到返回记录集最多的10个查询。

/path/mysqldumpslow -s t -t 10 -g “leftjoin” /database/mysql/slow-log

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

使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后，由于日志记录操作，在一定程度上会占用CPU资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈。

5.2.2 EXPLAIN

EXPLAIN可以帮助开发人员分析SQL问题，EXPLAIN显示了MySQL如何使用使用SQL执行计划，可以帮助开发人员写出更优化的查询语句。使用方法，在select语句前加上Explain就可以了：

EXPLAIN SELECT ap_article_content .* from ap_article,ap_article_content where 
ap_article.id = ap_article_content.article_id and ap_article.id = 1436583924280201218

结果的列的说明如下：

网络异常，图片无法展示

1) id

SELECT识别符。这是SELECT查询序列号。这个不重要

2) select_type

表示SELECT语句的类型。

simple:简单select（不使用union或子查询）。
primary: 子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY。
union: union中的第二个或后面的select语句。
dependent union:union中的第二个或后面的select语句，取决于外面的查询。
union result:UNION的结果，union语句中第二个select开始后面所有select
subquery:子查询中的第一个SELECT，结果不依赖于外部查询。
dependent subquery:子查询中的第一个select，取决于外面的查询。
derived:派生表的SELECT, FROM子句的子查询
UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

3) table

显示这查询的数据是关于哪张表的。

4) type

区间索引，这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为：

system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别,最好能达到ref。

system：表仅有一行，这是const类型的特列，平时不会出现，这个也可以忽略不计。
const：数据表最多只有一个匹配行，因为只匹配一行数据，所以很快
eq_ref：mysql手册是这样说的:"对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY"。eq_ref可以用于使用=比较带索引的列。
ref：查询条件索引既不是UNIQUE也不是PRIMARY KEY的情况。ref可用于=或<或>操作符的带索引的列。
ref_or_null：该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
index_merge：该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
unique_subquery：该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
range：只检索给定范围的行,使用一个索引来选择行。
index：该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
ALL：对于每个来自于先前的表的行组合,进行完整的表扫描。（性能最差）

5) possible_keys

指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。

6) key

实际使用到的索引。如果为NULL，则没有使用索引。如果为primary的话，表示使用了主键。

7) key_len

最长的索引宽度。如果键是NULL，长度就是NULL。在不损失精确性的情况下，长度越短越好。

8) ref

显示使用哪个列或常数与key一起从表中选择行。

9) rows

显示MySQL认为它执行查询时必须检查的行数。

10) Extra

执行状态说明，该列包含MySQL解决查询的详细信息

Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

六、索引优化

6.1 索引的介绍

在表没有添加索引和添加索引的时候，都执行以下查询：

SELECT * FROM tb_table WHERE code = 500000

然后再添加数据库的数据，插入100万条，再次测试有索引和没有索引的查询语句。

通过上面的对比测试可以看出，索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。对于少量的数据，没有合适的索引影响不是很大，但是，当随着数据量的增加，性能会急剧下降。

索引的目的在于提高查询效率，大家可以回忆之前学习的全文检索技术。类似使用字典，如果没有目录（索引），那么我们要从字典的第一个字开始查询到最后一个字才能有结果，可能要把字典中所有的字看一遍才能找到要结果，而目录（索引）则能够让我们快速的定位到这个字的位置，从而找到我们要的结果。

6.2 索引的类型

主键索引 PRIMARY KEY它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。

PRIMARY KEY (`id`)

唯一索引 UNIQUE唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。可以在创建表的时候指定，也可以修改表结构。

UNIQUE KEY `un_code` (`code`) USING BTREE

普通索引 INDEX这是最基本的索引，它没有任何限制。可以在创建表的时候指定，也可以修改表结构

KEY `index_code` (`code`) USING BTREE

组合索引 INDEX索引分单列索引和组合索引(联合索引)。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列，最多可以包含16个列。

KEY `num` (`code`,`username`) USING BTREE

注意，组合索引前面索引必须要先使用，后面的索引才能使用。即最左原则
全文索引 FULLTEXT全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

6.3 索引的存储结构

6.3.1 BTree索引

在前面的例子中我们看见有USING BTREE，这个是什么呢？这个就是MySQL所使用的索引方案，MySQL中普遍使用B+Tree做索引，也就是BTREE。特点：

BTREE索引以B+树的结构存储数据
BTREE索引能够加快数据的查询速度
BTREE索引更适合进行行范围查找使用的场景：

全值匹配的查询，例如根据订单号查询 order_sn='98764322119900'
联合索引时会遵循最左前缀匹配的原则,即最左优先
匹配列前缀查询，例如：order_sn like '9876%'
匹配范围值的查找，例如：order_sn > '98764322119900'
只访问索引的查询B-Tree和B+TreeB-Tree每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度.

B+Tree相对于B-Tree有几点不同：

非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中

6.3.2 哈希索引

Hash索引在MySQL中使用的并不是很多，目前主要是Memory存储引擎使用，在Memory存储引擎中将Hash索引作为默认的索引类型。所谓Hash索引，实际上就是通过一定的Hash算法，将需要索引的键值进行Hash运算，然后将得到的Hash值存入一个Hash表中。然后每次需要检索的时候，都会将检索条件进行相同算法的Hash运算，然后再和Hash表中的Hash值进行比较并得出相应的信息。

特点：

Hash索引仅仅只能满足“=”,“IN”和“<=>”查询，不能使用范围查询；
Hash索引无法被利用来避免数据的排序操作；
Hash索引不能利用部分索引键查询；(比如联合查询后，必须全列输入查询)
Hash索引在任何时候都不能避免表扫描；
Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高；

6.3.2 Full-text全文索引

Full-text索引也就是我们常说的全文索引，MySQL中仅有MyISAM和InnoDB存储引擎支持。

对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成Full-text索引时，会为文本生成一份单词的清单，在索引时根据这个单词的清单来索引。

注意：

对于较大的数据集，把数据添加到一个没有Full-text索引的表，然后添加Full-text索引的速度比把数据添加到一个已经有Full-text索引的表快。
针对较大的数据，生成全文索引非常的消耗时间和空间。
5.6版本前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那么全文索引不会生效。5.6版本和之后InnoDB存储引擎开始支持全文索引。
在MySQL中，全文索引支队英文有用，目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。
在MySQL中，如果检索的字符串太短则无法检索得到预期的结果，检索的字符串长度至少为4字节。

6.4 索引的使用

虽然索引能够为查找带来速度上的提升，但是也会对性能有一些损失。

索引会增加写操作的成本
太多的索引会增加查询优化器的选择时间当创建索引带来的好处多过于消耗的时候，才是最优的选择~

使用索引的场景

主键自动建立唯一索引；
经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
作为排序的列要建立索引；
查询中与其他表关联的字段，外键关系建立索引
高并发条件下倾向建立组合索引；
用于聚合函数的列可以建立索引，例如使用count(number)时，number列就要建立索引

不使用索引的场景

有大量重复的列不单独建立索引
表记录太少不要建立索引，因为没有太大作用。
不会作为查询的列不要建立索引

Mysql数据库高级篇 - Mysql性能优化（1）

前言

一、优化要考虑的问题

二、优化的需求

三、开发中Mysql优化由谁参与

四、优化的思路

4.1 优化的方向

4.2优化的维度

4.3 优化的工具

4.4 数据库使用优化思路

五、查询优化

5.1 MySQL查询流程

5.2 查询优化

5.2.1 慢查询

5.2.2 EXPLAIN

六、索引优化

6.1 索引的介绍

6.2 索引的类型

6.3 索引的存储结构

6.3.1 BTree索引

6.3.2 哈希索引

6.3.2 Full-text全文索引

6.4 索引的使用

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像