如何优化MySQL数据库性能？

如何优化MySQL数据库性能？

数据库设计思想深究----Mysql_kevinmeanscool的博客-CSDN博客

在上文中，作者详细地解析了数据库的核心原理。

对于数据库而言，影响数据库的性能因子比较多，但都不离开最基础的原理，本文将从开发尝试访问数据库到响应结果这一过程来详细探讨如何使得数据库性能更高效。

首先，从整个B/S架构中，请求的快速响应，是我们追求的高性能。对于数据库，其中最直接的感受就是一条SQL的请求后响应的速度。

我们与数据库打交道的方式通常有：CRUD。

一、如何书写出高效的SQL

追求高效的过程，就是追求最小系统开销实现功能的过程。系统中存在的低效的SQL将会使得数据库的负担加重。因此，写出高效的SQL是提升数据库性能的第一步。

以下，我们将对一个存储量在千万的表进行分析。

1.1 Create 增加

阅读MySQL官方文档，文档中说：增加记录的方式有三种:

 简而言之可以表示为：

//way1
insert Test(column_1) values (1),(2);
insert Test(column_1) value (1),(2);
//way2
insert Test(column_1) select 5 from dual
                       union all
                      select 6 from dual;
//way3
insert Test(column_1) values (3),(4)
on duplicate key update
column_1 = (9);

其中values是官方推荐，它与其他方式都可以进行单行插入或多行插入：

 可以发现，在插入等量数据时，values最快，

虽然官方文档对此作出了解释，但根据实验，请优先使用values去插入记录，values性能更好。

其中way3，代表的是，当插入的记录存在唯一约束时，会判断是否发生唯一冲突，如果没有发生则直接插入。如果发生，则更新为指定的值。假如一次insert插入多行记录，在冲突的情况下，会串行处理记录，中途发生无法解决唯一冲突的情况时，则会回滚整个事务。

对于插入语句，强烈建议读者，插入时，要将本次业务需要插入的字段全部列出，并一一对应：

 这样的好处在于，未来对表进行修改时，采用默认所有字段插入时，将造成无法预估的错误：比如新加字段不能为NULL。

同时，根据官方文档介绍，插入的数据类型与字段本身类型之间存在着隐式转换：

因此，在插入时，请避免出现隐式转换的情况，这将增加性能开销，同时当字符中出现字母时，将会返回数据库类型转换异常：

此类情况因为使用IDEA的插件mybatis-generator自动生成类型映射已经大大减少。

还有一种情况是批量插入，这里推荐使用values(record1)(record2) 的形式，而不是拼接完整SQL，因为这样会造成额外的解析与优化开销。这与JDBC中，batch statement的做法类似，不过，batch也是完整SQL拼接。使用Mybatis的标签拼接values后的record的是最高效的做法，因为这与values(record1)(record2) 的形式相同。

insert高性能SQL小结：

1.优先使用values去插入记录，values性能更好；

2.在插入时，请避免出现隐式转换的情况，这将增加性能开销；

3.使用values(record1)(record2) 的形式，而不是拼接完整SQL，因为这样会造成额外的解析与优化开销；

1.2 Retrieve 检索

MySQL官方文档为我们提供了检索表记录的select方式。

通常，我们检索表有2种方式：线性扫描，索引扫描

线性扫描的过程并不是发生在磁盘上，而是MySQL存储引擎通过随机磁盘页读取(1次I/O)，缓存到内存中，再根据内存中的table进行条件过滤。如果直接进行全表线性扫描，将是日益增加的性能低下。

因此数据库为我们提供了利用缓存空间换取检索速度的方式：索引 

1.2.1 检索性能提升的法宝：索引

索引的原理在开头的文章有详细介绍，这里不展开。

索引通常分为两种：

聚集索引(clustered index)

 此类索引的特点就是叶子结点会引用完整的record。

普通索引(secondary index)

而普通索引的特点就是叶子结点引用的主键值。

读者可能会想，如果我没有主键，光给一个字段加索引可以吗？

可以的，但是这样只会对单个列的查询有提升，因此建议每个表都设置主键。这样对于查询的性能提升的十分可观的每次I/O检索到的数据量为 (MySQL一次磁盘页I/O大小)^I/O次数，一次磁盘页I/O大小默认为16KB，也就是说对于海量数据，几次磁盘I/O便可检索到，通常遍历整个主键索引的B+树，并且需要读叶子节点数据，称之为全索引扫描。

其次，对于非主键列，如果存在高频访问的需求，建议设置普通索引。提升检索性能。

那么，有的读者在想，能不能每个字段都加索引，这样岂不是快的飞起，先不说缓存的压力，索引通俗来讲，就是字典的偏旁部首检索目录，一本字典，全是目录，那不还是一本没有目录的字典。

1.2.2 优化器如何看待索引？

为什么有的读者明明对字段加了索引，但是查询时还是会全盘扫描呢？

让我们通过一个简单的例子理解：

我们有这样一个表，id为主键（该表以id为key构造了一个聚集索引），age上有一个普通索引。 

执行SQL：

explain select a.age from vis a;

 发现执行计划选择使用vis_index_age普通索引，优化器为什么这样选择？我们可以通过optimizer查看：

# 调大trace的容量，防止被截断
set global optimizer_trace_max_mem_size = 1048576;

# 开启optimizer_trace
set optimizer_trace="enabled=on";

再执行一次SQL（注意，optimizer只能追踪一次会话，因此要选中一起执行）

踩坑提醒：IDEA自带的NavigationBar,会在执行后，自动执行一个:

 SET SQL_SELECt_LIMIT=501

导致一直无法获取到我们的SQL的优化结果。

 关掉了，还不行···于是我选择下载了一个MySQLWorkbeach,

关掉

select a.age from VisMan_mysql_db.vis a;
select a.* from information_schema.OPTIMIZER_TRACE a;

获取到执行计划(执行计划如何阅读，官网有文档：MySQL :: MySQL Internals Manual :: 8.14 Example)：

在TRACE的JSON中有三个步骤构成：

join_preparation（准备阶段）、join_optimization（优化阶段）、join_execution（执行阶段）。

 我们重点关注优化过程：

 我们一般重点关注“considered_execution_plans”：considered_execution_plans的部分负责对比各可行计划的代价，选择相对最优的执行计划。

前两个步骤：对于查询中的每个表，预估了表扫描的范围访问的成本和返回的记录数量。

 通过阅读发现，执行计划认为结果数量与表大小相同，于是选取了全表扫描的策略（不是整个table，上文提到过，整个索引树扫描也叫全表扫描）

如果，我们需要进行的是范围查找（这是最最最常见的业务场景，几乎没有SQL敢做全表查询的，实际项目表记录都是十分庞大的）：

explain select a.age from vis a where a.age>5;

 再次查看优化器是如何作出这个结果的：

 可以看到优化器通过贪婪算法，选择了最小cost的方式，used_index:"vis_index_age"来实现。上文提到了InnoDB引擎对于非主键索引使用的是B树数据结构，与B+树不同之处在于每个节点上都存储了索引列的value，还因为查找树的特性可以只构造一个大小为5的索引树即可返回结果集合。

再通过一个例子，来解释最初提出的问题：为什么索引会无效？

explain select a.age,a.name from vis a where a.age>5;

继续查看一下优化器信息（本次目的也很明确：为什么不走vis_index_age索引？为什么索引树大小有10？两倍？）：

 首先，我们可以看到vis_index_age索引是有被考虑的，并且可以与主键索引构成索引组合。

 可以看到优化器计算出了vis_index_age索引的cost：7.01>>全表扫描，因此优化器将执行计划定为了全表扫描。

Q：为什么走索引代价还这么大？

A：因为发生了回表。

 原因很简单，name并没有索引，已有的索引只有age与id，InnoDB如果想返回结果的话，只能先利用age的普通索引，再索引回聚集索引，再查找到对应的记录，这样系统的开销就变大了，甚至大过了直接的全盘扫描。

因此，对于非索引字短与索引字段的结果组合，即使条件中使用了索引字段，也会导致索引失效，请避免这种行为，对于千万级的表，一次全盘扫描开销将十分巨大，很可能导致数据库服务挂掉。

如果一定有这样的需求（例如很多业务绑定的数据：username+password等），可以考虑组合索引。

 

 

我们利用了联合索引，使得性能至少提升了3.5倍，当然这会牺牲一部分内存。

你可能会想了，我给name也加个索引，name索引与age索引的组合也算组合吧。我们直接看结果：

 

 我们可以发现，vis_index_name是不可用的。这是因为，在SQL中name的确定只能依赖id，条件并没有给出。

实际上联合索引index(age,name)是{index(age),index(name),index(age,name)}的集合：

explain select a.id from vis a where a.age>5;

 可以看到index(age,name)，依旧可以发挥index(id)的作用，这是因为联合索引实际上就是一个B+树，其节点为：

 当然，如果id比id与name更频繁，你也可以单独维护index(id)，优化器会根据cost优先选取index(id)。

上述的例子，不单单是为了介绍索引的使用。最主要还是让我们明白，优化的核心目的。不论哪个例子，都出现了一个explain关键字，它会返回这样一个结果： 

ColumnJSON NameMeaningidselect_idThe SELECT identifierselect_typeNoneThe SELECT typetabletable_nameThe table for the output rowpartitionspartitionsThe matching partitionstypeaccess_typeThe join typepossible_keyspossible_keysThe possible indexes to choosekeykeyThe index actually chosenkey_lenkey_lengthThe length of the chosen keyrefrefThe columns compared to the indexrowsrowsEstimate of rows to be examinedfilteredfilteredPercentage of rows filtered by table conditionExtraNoneAdditional information

通过对这些项的综合评估，确定我们的优化方案。其最终目的就是为了使得optimizer中best-path的cost降到最低。

小结：

检索优化的最终目的是optimizer中best-path的cost降到最低，这个大目标由可以分为多个步骤：降低回表次数（联合索引）、降低扫描等级（全表扫描->全索引扫描->范围扫描）、减少索引行数（exists与in）等。

通常，我们首先关心索引失效的问题：

1.建议每个表都设置主键。建立聚集索引与依附索引模型；

2.对于非主键列，如果存在高频访问的需求，建议设置普通索引；

3.对于非索引字短与索引字段的结果组合，即使条件中使用了索引字段，也会导致索引失效，请避免这种行为，对于千万级的表，一次全盘扫描开销将十分巨大，很可能导致数据库服务挂掉（有2种解决方案：1.组合索引；2.子查询后进行等值查找，等值查找默认Hash连接,Hash连接时间复杂度永远是O(1)，MySQL8.0后新特性，会使用更多内存）;

4.避免出现隐式转换的情况，增加转换开销的同时，使索引失效;

5.避免出现索引字段被函数嵌套或表达式的情况，会使索引失效;（解决方案：将需要函数处理的条件放在外查询，先子查询查询其他条件作为外查询的行组）

6.避免出现索引字段前置模糊查询的情况，会使索引失效;

7.聚集函数的变量优先使用索引、最好是主键索引;(对于count()，不为主键时优先使用count(1));

8.尽量使用数字型字段，在数字型字段上建立索引；（因为数字型字段索引开销更少，比较速度更快，可使用HashCode:CRC32()）

9.exists/in的正确使用：

exists是对外表做loop循环，每次loop循环再对内表（子查询）进行查询，因为内表查询可以利用索引，因此外小内大的情况下，使用exists性能更好；

in是将内外表做hash join，先查询内表，再把内表行组与外表匹配，因此外表匹配可以利用索引，因此外大内小的情况下，使用in性能更好；

如果两表差不多大，二者性能近似，不过，要根据数据膨胀率提前进行优化。

10.减少使用 or ，多用union代替 。因为一旦or的条件中出现非索引字段，将使索引失效，而union则不会导致这个情况，因此减少使用or；

11.减少字符集的隐式转换。关联查询时，字符集不同会自动在字段上加入ConVERT()函数，使得索引被函数嵌套，导致索引失效。

12.联合索引中将选择性最高的放在最前面。因为最左匹配原则。

/待更新

1.3 Update 更新

更新 *** 作的前提是检索记录，因此检索记录的性能优化适用于更新 *** 作。

1.在更新时，结果的子查询一定使用索引检索，否则性能会很差。

2.对于索引字段更新，会导致字段的索引树重新组织，因此，可以通过分库分表，降低只有一个表时QPS过高的问题。

1.4 Delete 删除

删除 *** 作的前提是检索记录，因此检索记录的性能优化适用于更新 *** 作。

1.在删除时，结果的子查询一定使用索引检索，否则性能会很差。

2.对于索引字段更新，会导致字段的索引树重新组织，因此，可以通过分库分表，降低只有一个表时QPS过高的问题。

可以发现，CRUD中，除了C，其他三种 *** 作都依赖检索的性能，因此优化检索性能往往会成为高性能SQL的首要任务。

二、 数据库整体优化思路 2.1 查询慢SQL的方法 ：慢查询日志

MySQL中提供了一个记录耗时特别长的SQL语句的功能，这个功能就是慢查询日志(slow_query_log)。

这个日志功能平时不会开启，因此，很多人都不太了解，实际使用原理并不难。

我们先查看一下慢查询日志的状态：

show variables like 'slow_%';

可以看到slow_query_log默认为：OFF 未开启。

MySQL提供了两种开启慢查询日志的方法：1.配置文件设置；2.terminal动态开启

[mysqld]
# 开启慢查询日志，1表示开启，0表示关闭。
slow_query_log = 1

 不过在B/S模式下，设置配置文件后重启是很不可能的事情。因此我们使用动态开启的情况比较多：

set global slow_query_log  = 1;
//0就是关闭

这时慢查询日志便开启了，当然，这会消耗微不足道的内存资源，对系统几乎没有影响。

还有两个全局变量也十分重要：

long_query_time：决定一个SQL语句是否是慢查询的阈值。如果一个SQL的执行时间超过这个阈值，则认定这个SQL为慢查询的SQL语句，将其记录在慢查询日志中，否则不记录。默认值为10s，如果设置为0s，则表示记录所有的SQL语句。

slow_query_log_file：慢查询日志存放目录，对应到MySQL数据库服务器上面的一个日志文件。默认的日志文件名称为服务器主机名称-slow.log。路径为show variables like 'datadir'。

上面已经开启了慢查询日志的功能，下面来验证开启后的效果。

select sleep(11);

然后查看日志（如果没有权限，则termimal：sudo chmod -R a+rwx /usr/local/mysql/data ）：

 通过上面的查询结果，可以确认开启已经生效了。但是Time的format没有设置：

show global variables like 'log_timestamps';
set global log_timestamps = system;

默认为UTC，设置后即可与系统（服务器）保持一致。

相关的参数还有很多：

############### 慢查询日志配置相关 #######################################
slow_query_log=1
# 慢查询日志记录在mysql.slow_log表中
log_output=table,file
slow_query_log_file=/var/lib/mysql/test-slow.log
# 慢查询日志的SQL耗时的阈值
long_query_time=10
# 对没有使用索引的SQL，即便是查询耗时低于10s，也记录日志
#log_queries_not_using_indexes=1
# 对没有使用索引的SQL，即便是查询耗时低于10s，也记录日志，但是一分钟内置记录1次。
#log_throttle_queries_not_using_indexes=1
# 没有使用索引的SQL，如果扫描的行数低于1000，则不记录到慢查询日志中
#min_examined_row_limit=1000
# alter，analyze语句，如果超过慢查询的阈值，也记录在慢查询日志中
#log_slow_admin_statements=1
############### 慢查询日志配置相关 #######################################

暂时没有找到：官方文档。

接下来，我们将读懂慢查询日志：

/usr/local/mysql/bin/mysqld, Version: 5.7.22 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2022-01-23T13:19:35.992520Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id: 12850
# Query_time: 11.011741  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use visman_mysql_db;
SET timestamp=1642943975;
select sleep(11);

Time：表示这个慢查询的SQL发生的时间。User@Host：表示这个SQL是由哪个用户通过哪个IP地址访问的。Query_time：表示这个SQL语句执行所花费的时间，单位是秒。Lock_time：表示这个SQL语句在执行的过程中，锁定表或行的时间。Rows_sent：表示最后查询的结果发送给客户端的行数。Rows_examined：表示这个SQL语句在执行过程中，实际扫描的行数。SET timestamp=1642943975：记录日志的时间最后就是SQL语句的具体内容。

MySQL自带的mysqldumpslow，可以帮助我们分析慢SQL的客观原因：

mysqldumpslow经常使用的参数：
-s，是order的顺序
----- al 平均锁定时间
-----ar 平均返回记录时间
-----at 平均查询时间（默认）
-----c 计数
-----l 锁定时间
-----r 返回记录
-----t 查询时间
-t，是top n的意思，即为返回前面多少条的数据
-g，后边可以写一个正则匹配模式，大小写不敏感的
例子：
mysqldumpslow -t 10 -s t -g “left join” host-slow.log

2.2 整体的SQL性能优化流程

首先，利用mysqldumpslow，设置日志的内容，然后

set global slow_query_log  = 1;

开启慢查询日志，等待一个业务周期后。

set global slow_query_log  = 0;

关闭慢查询日志。

通过分析slow_query_log.log，获取具体的SQL。

在服务环境/本机环境中，初步分析这个SQL: explain

初步分析后，如果还不能解决则需要 Optimizer 优化器缓存的执行计划来帮忙：

# 调大trace的容量，防止被截断
set global optimizer_trace_max_mem_size = 1048576;

# 开启optimizer_trace
set optimizer_trace='enabled=on';

开启后，手工事务的情况下，执行SQL。

然后 

select * from information_schema.OPTIMIZER_TRACE;

查看信息，尤其关注trace，是JSON格式的字符串，导入 JSON阅读器 中，

开始分析potential indexes和 consider_execution_plan 根据经验基本上就可以判断出性能瓶颈在哪里，然后根据上文CRUD优化小结，修改SQL。

自此，就结束了对SQL优化的介绍