实战：万字小结MySQL慢原因分析

01Byte空间

已于 2024-07-30 10:28:16 修改

阅读量992

点赞数 23

分类专栏：软件架构 MySQL java 文章标签： mysql adb 数据库慢sql 慢sql分析慢sql优化

于 2024-07-30 10:24:35 首次发布

本文链接：https://blog.csdn.net/Rookie_CEO/article/details/140789696

版权

软件架构同时被 3 个专栏收录

1017 篇文章

订阅专栏

java

294 篇文章

订阅专栏

MySQL

176 篇文章

订阅专栏

mysql查询为什么会慢，关于这个问题，在实际开发经常会遇到，而面试中，也是个高频题。

遇到这种问题，我们一般也会想到是因为索引。

那除开索引之外，还有哪些因素会导致数据库查询变慢呢？

有哪些操作，可以提升mysql的查询能力呢？

今天这篇文章，我们就来聊聊会导致数据库查询变慢的场景有哪些，并给出原因和解决方案。

数据库查询流程

我们先来看下，一条查询语句下来，会经历哪些流程。

实战：搞懂SQL执行流程、SQL执行计划解读和SQL优化_如何看sql执行计划,如何判断优或略?-CSDN博客

比如我们有一张数据库表

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '名字',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `gender` int(8) NOT NULL DEFAULT '0' COMMENT '性别',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`),
  KEY `idx_gender` (`gender`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我们平常写的应用代码（go或C++之类的），这时候就叫客户端了。

客户端底层会带着账号密码，尝试向mysql建立一条TCP长链接。

mysql的连接管理模块会对这条连接进行管理。

建立连接后，客户端执行一条查询sql语句。比如：

select * from user where gender = 1 and age = 100;

客户端会将sql语句通过网络连接给mysql。

mysql收到sql语句后，会在分析器中先判断下SQL语句有没有语法错误，比如select，如果少打一个l，写成slect，则会报错You have an error in your SQL syntax;。这个报错对于我这样的手残党来说可以说是很熟悉了。

接下来是优化器，在这里会根据一定的规则选择该用什么索引。

之后，才是通过执行器去调用存储引擎的接口函数。

Mysql架构

存储引擎类似于一个个组件，它们才是mysql真正获取一行行数据并返回数据的地方，存储引擎是可以替换更改的，既可以用不支持事务的MyISAM，也可以替换成支持事务的Innodb。这个可以在建表的时候指定。比如

CREATE TABLE `user` (
  ...
) ENGINE=InnoDB;

现在最常用的是InnoDB。

我们就重点说这个。

InnoDB中，因为直接操作磁盘会比较慢，所以加了一层内存提提速，叫buffer pool，这里面，放了很多内存页，每一页16KB，有些内存页放的是数据库表里看到的那种一行行的数据，有些则是放的索引信息。

bufferPool与磁盘

查询SQL到了InnoDB中。会根据前面优化器里计算得到的索引，去查询相应的索引页，如果不在buffer pool里则从磁盘里加载索引页。再通过索引页加速查询，得到数据页的具体位置。如果这些数据页不在buffer pool中，则从磁盘里加载进来。

这样我们就得到了我们想要的一行行数据。

索引页与磁盘页的关系

最后将得到的数据结果返回给客户端。

`show profile`慢查询分析

如果上面的流程比较慢的话，我们可以通过开启profiling看到流程慢在哪。

show profile语句已经弃用，并将在以后版本中移除，建议使用 Performance Schema
Performance Schema文档：MySQL :: MySQL 5.7 Reference Manual :: 25.19.1 Query Profiling Using Performance Schema

但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗费的时间等。

`show profile`基本语法：

查看是否开启：show variables like "%pro%";
开启：set profiling = 1
查看SQL执行时间：show profiles;
查看记录中第一条SQL详细时间：show profile for query 1;

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type: {
    ALL                显示所有信息
  | BLOCK IO           显示块输入和输出操作的数量
  | CONTEXT SWITCHES   显示自愿上下文切换和非自愿上下文切换的数量
  | CPU                显示用户和系统的CPU使用时间
  | IPC                显示已发送和已接收消息（messages）的数量
  | MEMORY             -- 尚未生效
  | PAGE FAULTS        显示主要和次要页面错误的数量
  | SOURCE             显示源代码中函数名称以及该函数所在文件的名称和行号
  | SWAPS              显示SWAP数量
}

mysql> set profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

然后正常执行sql语句。

这些SQL语句的执行时间都会被记录下来，此时你想查看有哪些语句被记录下来了，可以执行 show profiles;

mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.06811025 | select * from user where age>=60                  |
|        2 | 0.00151375 | select * from user where gender = 2 and age = 80  |
|        3 | 0.00230425 | select * from user where gender = 2 and age = 60  |
|        4 | 0.00070400 | select * from user where gender = 2 and age = 100 |
|        5 | 0.07797650 | select * from user where age!=60                  |
+----------+------------+---------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)

关注下上面的query_id，比如select * from user where age>=60对应的query_id是1，如果你想查看这条SQL语句的具体耗时，那么可以执行以下的命令。

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000074 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000034 |
| init                 | 0.000032 |
| System lock          | 0.000027 |
| optimizing           | 0.000020 |
| statistics           | 0.000058 |
| preparing            | 0.000018 |
| executing            | 0.000013 |
| Sending data         | 0.067701 |
| end                  | 0.000021 |
| query end            | 0.000015 |
| closing tables       | 0.000014 |
| freeing items        | 0.000047 |
| cleaning up          | 0.000027 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

通过上面的各个项，大家就可以看到具体耗时在哪。比如从上面可以看出Sending data的耗时最大，这个是指执行器开始查询数据并将数据发送给客户端的耗时，因为我的这张表符合条件的数据有好几万条，所以这块耗时最大，也符合预期。

一般情况下，我们开发过程中，耗时大部分时候都在Sending data阶段，而这一阶段里如果慢的话，最容易想到的还是索引相关的原因。

show profile返回的行和列

执行一个show profile all for query 3;命令显示的结果集。我们根据行和列分别来分析一个每个字段的含义。

列字段的含义：

+---------------------+--------------+------+-----+----------+-------+
| Field               | Type         | Null | Key | Default  | Extra |
+---------------------+--------------+------+-----+----------+-------+
| QUERY_ID            | int(20)      | NO   |     | 0        |       | # 语句ID
| STATE               | varchar(30)  | NO   |     |          |       | # 状态
| DURATION            | decimal(9,6) | NO   |     | 0.000000 |       | # 持续时间，单位s
| CPU_USER            | decimal(9,6) | YES  |     | NULL     |       | # 用户态CPU时间，单位s
| CPU_SYSTEM          | decimal(9,6) | YES  |     | NULL     |       | # 系统态CPU时间，单位s
| CONTEXT_VOLUNTARY   | int(20)      | YES  |     | NULL     |       | # 自愿上下文切换次数
| CONTEXT_INVOLUNTARY | int(20)      | YES  |     | NULL     |       | # 非自愿上下文切换次数
| BLOCK_OPS_IN        | int(20)      | YES  |     | NULL     |       | # 块输入次数
| BLOCK_OPS_OUT       | int(20)      | YES  |     | NULL     |       | # 块输出次数
| MESSAGES_SENT       | int(20)      | YES  |     | NULL     |       | # 发送的消息数量
| MESSAGES_RECEIVED   | int(20)      | YES  |     | NULL     |       | # 接收的消息数量
| PAGE_FAULTS_MAJOR   | int(20)      | YES  |     | NULL     |       | # 主要页面错误数量
| PAGE_FAULTS_MINOR   | int(20)      | YES  |     | NULL     |       | # 次要页面错误数量
| SWAPS               | int(20)      | YES  |     | NULL     |       | # 交换次数
| SOURCE_FUNCTION     | varchar(30)  | YES  |     | NULL     |       | # 源代码函数
| SOURCE_FILE         | varchar(20)  | YES  |     | NULL     |       | # 源代码文件
| SOURCE_LINE         | int(20)      | YES  |     | NULL     |       | # 源代码行数
+---------------------+--------------+------+-----+----------+-------+

行字段含义


* Sending data (最重要的一个过程★★★★★)
  线程正在读取和处理一条SELECT语句的行，并且将数据发送至客户端。由于在此期间会执行大量   的磁盘访问（读操作），
  这个状态在一个指定查询的生命周期中经常是耗时最长的。
  这个字段才是SQL真正运行采集+相应数据的时间，而非executing；
  
  --以下按照首字母顺序依次排序
  
* After create
  这个状态当线程创建一个表（包括内部临时表）时，在这个建表功能结束时出现。即使某些错误导致建表失败，也会使用这个状态。

* Analyzing
  当计算MyISAM表索引分布时。（比如进行ANALYZE TABLE时）

* checking permissions
  这个线程检查服务器是否有具有执行该语句的所需权限。

* Checking table
  线程正在执行表检查操作。

* cleaning up
  线程处理一个命令，并正准备释放内存和重置某些状态变量。

* closing tables
  线程正在将变更的表中的数据刷新到磁盘上并正在关闭使用过的表。这应该是一个快速的操作。如果不是这样的话
  则应该检查硬盘空间是否已满或者硬盘IO是否达到瓶颈。 

* converting HEAP to MyISAM
  线程将一个内部临时表转换为磁盘上的MyISAM表。

* copy to tmp table 
  线程正在处理一个ALTER TABLE语句。这个状态发生在新的表结构已经创建之后，但是在数据被复制进入之前。

* Copying to group table
  如果一个语句有不同的ORDER BY和GROUP BY条件，数据会被复制到一个临时表中并且按组排序。

* Copying to tmp table
  线程将数据写入内存中的临时表。  正在创建临时表以存放部分查询结果

* Copying to tmp table on disk
  线程正在将数据写入磁盘中的临时表。临时表的结果集过大。所以线程将临时表由基于内存模式改为基于磁盘模式，以节省内存。
  但是这个过程会异常的缓慢！！

* Creating index
  线程正在对一个MyISAM表执行ALTER TABLE ... ENABLE KEYS语句。

* Creating sort index
  线程正在使用内部临时表处理一个SELECT 操作。

* creating table
  线程正在创建一个表，包括创建临时表。

* Creating tmp table
  线程正在创建一个临时表在内存或者磁盘上。
  如果这个表创建在内存上但是之后被转换到磁盘上，这个状态在运行Copying to tmp table on disk 的时候保持。

* deleting from main table
  线程正在执行多表删除的第一部分，只从第一个表中删除。并且保存列和偏移量用来从其他（参考）表删除。

* deleting from reference tables
  线程正在执行多表删除的第二部分，并从其他表中删除匹配的行。

* discard_or_import_tablespace
  线程正在执行ALTER TABLE ... DISCARD TABLESPACE 或 ALTER TABLE ... IMPORT TABLESPACE语句。

* end
  这个状态出现在结束时，但是在对ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT,   或者 UPDATE 语句进行清理之前。

* executing
  该线程已开始执行一条语句。

* Execution of init_command
  线程正在执行处于init_command系统变量的值中的语句。

* freeing items
* 线程已经执行了命令。在这个状态中涉及的查询缓存可以得到一些释放。这个状态通常后面跟随cleaning up状态。

* Flushing tables
  线程正在执行FLUSH TABLES 并且等待所有线程关闭他们的表。

* FULLTEXT initialization
  服务器正在准备进行自然语言全文检索。

* init
  这个状态出现在线程初始化ALTER TABLE, DELETE, INSERT, SELECT, 或 UPDATE语句之前。
  服务器在这种状态下进行的操作，包括：刷新全日志、Innodb日志，和一些查询缓存清理操作。

* Killed
  程序对线程发送了KILL语句，并且它应该放弃下一次对KILL标记的检查。
  这个标记在每一个MySQL的主要循环中被检查，但在某些情况下，它可能需要令线程在很短的时间内死亡。
  如果这个线程被其他线程锁住了，这个KILL操作会在其他线程释放锁的瞬时执行。


* logging slow query
  这个线程正在将语句写入慢查询日志。

* NULL
  没有操作的状态。

* login
  线程连接的初始状态。直到客户端已经成功验证。

* manage keys
  服务器启用或禁用表索引。

* Opening tables, Opening table
  线程正试图打开一张表

* optimizing
  服务器执行查询的初步优化。

* preparing
 在查询优化过程中出现这个状态。

* Purging old relay logs
  线程正在移除不必要的中继日志文件。

* query end
  这个状态出现在处理一个查询之后，但是在freeing items状态之前。

* Reading from net
  服务器正在从网络阅读数据包。

* Removing duplicates
  查询正在使用SELECT DISTINCT，这种情况下MySQL不能在早期阶段优化掉一些distinct操作。
  因此，MySQL需要一个额外的阶段，在将结果发送到客户端之前删除所有重复的行。

* removing tmp table
  线程正在移除一个内置临时表，在执行一条SELECT语句之后。 如果没有临时表产生，那么这个状态不被使用。

* rename
* 线程正在重命名一张表。

* rename result table
  线程正在处理ALTER TABLE语句，创建新的表，并且重命名它来代替原有的表。

* Reopen tables
  线程获得了表锁，但是在取得表锁之后才发现该表的底层结构已经发生了变化。线程释放这个锁，关闭表，并试图重新打开该表。

* Repair by sorting
  修复代码正在使用一个分类来创建索引。

* Repair done
  线程完成一个多线程的MyISAM表的修复。

* Repair with keycache
  修复代码正在通过索引缓存一个接一个地使用创建索引。这比通过分类修复要慢很多。

* Rolling back
  线程正在回滚一个事务

* Searching rows for update
  线程正在进行第一阶段，在更新前寻找所有匹配的行。如果update正在更改用于查找相关行的索引，则必须这么做。

* setup
  线程正开始进行一个ALTER TABLE操作。

* Sorting for group
  线程正在执行一个由GROUP BY指定的排序。

* Sorting for order
  线程正在执行一个由ORDER BY指定的排序。

* Sorting index
  线程正在对索引页进行排序，为了对MyISAM表进行操作时获得更优的性能。

* Sorting result
 对于一个SELECT语句，这与创建排序索引相似，但是是对非临时表。

* statistics
  服务器计算统计去规划一个查询。如果一个线程长时间处于这个状态，这个服务器的磁盘可能在执行其他工作。

* System lock
 这个线程正在请求或者等待一个内部的或外部的系统表锁。如果这个状态是由于外部锁的请求产生的，并且你没有使用多个正在访问相同的表的mysql服务器
 
* Waiting for table level lock
  系统锁定后的下一个线程状态。线程已获得外部锁并且将请求内部表锁。

* Updating
  线程寻找更新匹配的行并进行更新。

* updating main table
  线程正在执行多表更新的第一部分，只从第一个表中更新。并且保存列和偏移量用来从其他（参考）表更新。

* updating reference tables
  线程正在执行多表更新的第二部分，并从其他表中更新匹配的行。

* User lock
  线程正在请求或等待一个GET_LOCK()调用所要求的咨询锁。对于SHOW PROFILE，这个状态意味这线程正在请求锁。（而非等待）

* User sleep
  线程调用了一个SLEEP()。

* Waiting for commit lock
  一个显式或隐式语句在提交时等待释放读锁

* Waiting for global read lock
  等待全局读锁。

* Waiting for release of readlock
  等待释放读锁。

* Waiting for tables, Waiting for table, Waiting for table flush
  线程获得一个通知，底层表结构已经发生变化，它需要重新打开表来获取新的结构。然而，重新打开表，它必须等到所有其他线程关闭这个有问题的表。
  这个通知产生通常因为另一个线程对问题表执行了FLUSH TABLES或者以下语句之一：
  FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

* Waiting for lock_type lock
  等待各个种类的表锁。

* Waiting on cond
 一个普通的状态，线程正在等待一个条件为真。没有特定的状态信息可用。

* Writing to net
  服务器正在写一个网络数据包。

SQL执行过程中可能导致时间慢的原因


1. Sending data (最重要的一个过程★★★★★)
  线程正在读取和处理一条SELECT语句的行，并且将数据发送至客户端。由于在此期间会执行大量
  的磁盘访问（读操作），这个状态在一个指定查询的生命周期中经常是耗时最长的。
  
  对于一个普通查询来说，这个参数过大可分为两种情况
  1. 第一种是SQL本身，比如没有建立正确的索引，索引失效等等情况，这种数据体现在CPU_user 和CPU_sysyem字段 时间过长；
  2. 第二种是相应数据量过大，导致CPU调度时上下文频繁切换。这种数据体现在CONTEXT_INVOLUNTARY和CONTEXT_VOLUNTARY字段 时间过长;
     像：外网使用Navicat连接到远程数据库中。查询一个普通的SQL，在本地MySQL执行速度很快，但是使用远程服务器的MySQL就异常的缓慢。
  这时若查询profile详情，就会发现大量相应数据传输IO导致频繁的上下文切换消耗了大量的时间。
 
2. converting HEAP to MyISAM
   原译指的是：线程将一个内部临时表转换为磁盘上的MyISAM表。
   我们实际操作中可能出现的问题就是查询结果太大了导致内存不够，往磁盘上搬。

3.Creating tmp table
  创建了临时表

4.Coping to tmp table on disk
  把内存中临时表复制到磁盘

5.locked
  加锁

------------------------------------------------ 

2，4 可以修改一下tmp_table_size和max_heap_table_size两个参数来调整

索引相关原因

索引相关的问题，一般能用explain命令帮助分析。通过它能看到用了哪些索引，大概会扫描多少行之类的信息。

mysql会在优化器阶段里看下选择哪个索引，查询速度会更快。

一般主要考虑几个因素，比如：

选择这个索引大概要扫描多少行（rows）
为了把这些行取出来，需要读多少个16kb的页
走普通索引需要回表，主键索引则不需要，回表成本大不大？

回到show profile中提到的sql语句，我们使用explain select * from user where age>=60 分析一下。

explain sql

上面的这条语句，使用的type为ALL，意味着是全表扫描，possible_keys是指可能用得到的索引，这里可能使用到的索引是为age建的普通索引，但实际上数据库使用的索引是在key那一列，是NULL。也就是说这句sql不走索引，全表扫描。

这个是因为数据表里，符合条件的数据行数（rows）太多，如果使用age索引，那么需要将它们从age索引中读出来，并且age索引是普通索引，还需要回表找到对应的主键才能找到对应的数据页。算下来还不如直接走主键划算。于是最终选择了全表扫描。

当然上面只是举了个例子，实际上，mysql执行sql时，不用索引或者用的索引不符合我们预期这件事经常发生，索引失效的场景有很多，比如用了不等号，隐式转换等，这个相信大家背八股文的时候也背过不少了，我也不再赘述。

聊两个生产中容易遇到的问题吧。

索引不符合预期

实际开发中有些情况比较特殊，比如有些数据库表一开始数据量小，索引少，执行sql时，确实使用了符合你预期的索引。但随时时间边长，开发的人变多了，数据量也变大了，甚至还可能会加入一些其他重复多余的索引，就有可能出现用着用着，用到了不符合你预期的其他索引了。从而导致查询突然变慢。

这种问题，也好解决，可以通过force index指定索引。比如

force index指定索引

通过explain可以看出，加了force index之后，sql就选用了idx_age这个索引了。

走了索引还是很慢

有些sql，用explain命令看，明明是走索引的，但还是很慢。一般是两种情况：

第一种是索引区分度太低，比如网页全路径的url链接，这拿来做索引，一眼看过去全都是同一个域名，如果前缀索引的长度建得不够长，那这走索引跟走全表扫描似的，正确姿势是尽量让索引的区分度更高，比如域名去掉，只拿后面URI部分去做索引。

索引前缀区分度太低

第二种是索引中匹配到的数据太大，这时候需要关注的是explain里的rows字段了。

它是用于预估这个查询语句需要查的行数的，它不一定完全准确，但可以体现个大概量级。

当它很大时，一般常见的是下面几种情况。

如果这个字段具有唯一的属性，比如电话号码等，一般是不应该有大量重复的，那可能是你代码逻辑出现了大量重复插入的操作，你需要检查下代码逻辑，或者需要加个唯一索引限制下。
如果这个字段下的数据就是会很大，是否需要全部拿？如果不需要，加个limit限制下。如果确实要拿全部，那也不能一次性全拿，今天你数据量小，可能一次取一两万都没啥压力，万一哪天涨到了十万级别，那一次性取就有点吃不消了。你可能需要分批次取，具体操作是先用order by id排序一下，拿到一批数据后取最大id作为下次取数据的起始位置。

连接数过小

索引相关的原因我们聊完了，我们来聊聊，除了索引之外，还有哪些因素会限制我们的查询速度的。

我们可以看到，mysql的server层里有个连接管理，它的作用是管理客户端和mysql之间的长连接。

正常情况下，客户端与server层如果只有一条连接，那么在执行sql查询之后，只能阻塞等待结果返回，如果有大量查询同时并发请求，那么后面的请求都需要等待前面的请求执行完成后，才能开始执行。

连接过少会导致sql阻塞

因此很多时候我们的应用程序，比如go或java这些，会打印出sql执行了几分钟的日志，但实际上你把这条语句单独拎出来执行，却又是毫秒级别的。这都是因为这些sql语句在等待前面的sql执行完成。

怎么解决呢？

如果我们能多建几条连接，那么请求就可以并发执行，后面的连接就不用等那么久了。

增加连接可以加快执行sql

而连接数过小的问题，受数据库和客户端两侧同时限制。

数据库连接数过小

Mysql的最大连接数默认是100, 最大可以达到16384。

可以通过设置mysql的max_connections参数，更改数据库的最大连接数。

mysql> set global max_connections= 500;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+
1 row in set (0.00 sec)

上面的操作，就把最大连接数改成了500。

应用侧连接数过小

数据库连接大小是调整过了，但貌似问题还是没有变化？还是有很多sql执行达到了几分钟，甚至超时？

那有可能是因为你应用侧（go，java写的应用，也就是mysql的客户端）的连接数也过小。

应用侧与mysql底层的连接，是基于TCP协议的长链接，而TCP协议，需要经过三次握手和四次挥手来实现建连和释放。如果我每次执行sql都重新建立一个新的连接的话，那就要不断握手和挥手，这很耗时。所以一般会建立一个长连接池，连接用完之后，塞到连接池里，下次要执行sql的时候，再从里面捞一条连接出来用，非常环保。

连接池原理

我们一般写代码的时候，都会通过第三方的orm库来对数据库进行操作，而成熟的orm库，百分之一千万都会有个连接池。

而这个连接池，一般会有个大小。这个大小就控制了你的连接数最大值，如果说你的连接池太小，都还没有数据库的大，那调了数据库的最大连接数也没啥作用。

一般情况下，可以翻下你使用的orm库的文档，看下怎么设置这个连接池的大小，就几行代码的事情，改改就好。比如go语言里的gorm里是这么设置的

func Init() {
  db, err := gorm.Open(mysql.Open(conn), config)
    sqlDB, err := db.DB()
    // SetMaxIdleConns 设置空闲连接池中连接的最大数量
    sqlDB.SetMaxIdleConns(200)
    // SetMaxOpenConns 设置打开数据库连接的最大数量
    sqlDB.SetMaxOpenConns(1000)
}

buffer pool太小

连接数是上去了，速度也提升了。

曾经遇到过面试官会追问，有没有其他办法可以让速度更快呢？

那必须要眉头紧锁，假装思考，然后说：有的。

我们在前面的数据库查询流程里，提到了进了innodb之后，会有一层内存buffer pool，用于将磁盘数据页加载到内存页中，只要查询到buffer pool里有，就可以直接返回，否则就要走磁盘IO，那就慢了。

也就是说，如果我的buffer pool 越大，那我们能放的数据页就越多，相应的，sql查询时就更可能命中buffer pool，那查询速度自然就更快了。

可以通过下面的命令查询到buffer pool的大小，单位是Byte。

mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.01 sec)

也就是128Mb。

如果想要调大一点。可以执行

mysql> set global innodb_buffer_pool_size = 536870912;
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 536870912 |
+-------------------------+-----------+
1 row in set (0.01 sec)

这样就把buffer pool增大到512Mb了。

但是吧，如果buffer pool大小正常，只是别的原因导致的查询变慢，那改buffer pool毫无意义。

但问题又来了。

怎么知道buffer pool是不是太小了？

这个我们可以看buffer pool的缓存命中率。

查看buffer pool命中率

通过 show status like 'Innodb_buffer_pool_%';可以看到跟buffer pool有关的一些信息。

Innodb_buffer_pool_read_requests表示读请求的次数。

Innodb_buffer_pool_reads 表示从物理磁盘中读取数据的请求次数。

所以buffer pool的命中率就可以这样得到：

buffer pool 命中率 = 1 - (Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%

比如我上面截图里的就是，1 - (405/2278354) = 99.98%。可以说命中率非常高了。

一般情况下buffer pool命中率都在99%以上，如果低于这个值，才需要考虑加大innodb buffer pool的大小。

当然，还可以把这个命中率做到监控里，这样半夜sql变慢了，早上上班还能定位到原因，就很舒服。

还有哪些骚操作？

前面提到的是在存储引擎层里加入了buffer pool用于缓存内存页，这样可以加速查询。

那同样的道理，server层也可以加个缓存，直接将第一次查询的结果缓存下来，这样下次查询就能立刻返回，听着挺美的。

按道理，如果命中缓存的话，确实是能为查询加速的。但这个功能限制很大，其中最大的问题是只要数据库表被更新过，表里面的所有缓存都会失效，数据表频繁的更新，就会带来频繁的缓存失效。所以这个功能只适合用于那些不怎么更新的数据表。

另外，这个功能在8.0版本之后，就被干掉了。所以这功能用来聊聊天可以，没必要真的在生产中使用啊。

查询缓存被删除

总结

数据查询过慢一般是索引问题，可能是因为选错索引，也可能是因为查询的行数太多。
客户端和数据库连接数过小，会限制sql的查询并发数，增大连接数可以提升速度。
innodb里会有一层内存buffer pool用于提升查询速度，命中率一般>99%，如果低于这个值，可以考虑增大buffer pool的大小，这样也可以提升速度。
查询缓存（query cache）确实能为查询提速，但一般不建议打开，因为限制比较大，并且8.0以后的Mysql里已经将这个功能干掉了。