共计 7514 个字符，预计需要花费 19 分钟才能阅读完成。
我纯熟利用 ctrl c 和 ctrl v 开发 curd 代码好多年了。
mysql 查问为什么会慢，对于这个问题，在理论开发常常会遇到，而面试中，也是个高频题。
遇到这种问题，咱们个别也会想到是因为索引。
那除开索引之外，还有哪些因素会导致数据库查问变慢呢？
有哪些操作，能够晋升 mysql 的查问能力呢？
明天这篇文章，咱们就来聊聊会导致数据库查问变慢的场景有哪些，并给出起因和解决方案。
数据库查问流程
咱们先来看下，一条查问语句下来，会经验哪些流程。
比方咱们有一张数据库表
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 真正获取一行行数据并返回数据的中央，存储引擎是能够替换更改的，既能够用不反对事务的 MyISAM，也能够替换成反对事务的 Innodb。这个能够在建表的时候指定。比方
CREATE TABLE `user` (...) ENGINE=InnoDB;当初最罕用的是InnoDB。
咱们就重点说这个。
InnoDB 中，因为间接操作磁盘会比较慢，所以加了一层内存提提速，叫buffer pool，这外面，放了很多内存页，每一页 16KB，有些内存页放的是数据库表里看到的那种一行行的数据，有些则是放的索引信息。
查问 SQL 到了 InnoDB 中。会依据后面优化器里计算失去的索引，去 查问相应的索引页 ，如果不在 buffer pool 里则从磁盘里加载索引页。 再通过索引页减速查问，失去数据页 的具体位置。如果这些数据页不在 buffer pool 中，则从磁盘里加载进来。
这样咱们就失去了咱们想要的一行行数据。
最初将失去的数据后果返回给客户端。
慢查问剖析
如果下面的流程比较慢的话，咱们能够通过开启 profiling 看到流程慢在哪。
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 阶段，而这一阶段里如果慢的话，最容易想到的还是索引相干的起因。
索引相干起因
索引相干的问题，个别能用 explain 命令帮忙剖析。通过它能看到 用了哪些索引 ，大略会 扫描多少行 之类的信息。
mysql 会在 优化器阶段 里看下抉择哪个索引，查问速度会更快。
个别次要思考几个因素，比方：
- 抉择这个索引大略要扫描 多少行（rows）
- 为了把这些行取出来，须要读 多少个 16kb 的页
- 走一般索引须要回表，主键索引则不须要，回表老本 大不大？
回到 show profile 中提到的 sql 语句，咱们应用explain select * from user where age>=60 剖析一下。
下面的这条语句，应用的 type 为 ALL，意味着是 全表扫描 ，possible_keys 是指 可能用失去的索引 ，这里可能应用到的索引是为 age 建的一般索引，但实际上数据库应用的索引是在key 那一列，是 NULL。也就是说 这句 sql 不走索引，全表扫描。
这个是因为数据表里，符合条件的数据行数（rows）太多，如果应用 age 索引，那么须要将它们从 age 索引中读出来，并且 age 索引是 一般索引 ，还须要 回表 找到对应的 主键 能力找到对应的 数据页。算下来还不如间接走主键划算。于是最终抉择了全表扫描。
当然下面只是举了个例子，实际上，mysql 执行 sql 时，不必索引或者用的索引不合乎咱们预期 这件事常常产生，索引生效的场景有很多，比方用了 不等号，隐式转换 等，这个置信大家背八股文的时候也背过不少了，我也不再赘述。
聊两个生产中容易遇到的问题吧。
索引不合乎预期
理论开发中有些状况比拟非凡，比方有些数据库表一开始数据量小，索引少，执行 sql 时，的确应用了合乎你预期的索引。但随时工夫边长，开发的人变多了，数据量也变大了，甚至还可能会退出一些其余反复多余的索引，就有可能呈现用着用着，用到了不合乎你预期的其余索引了。从而导致查问忽然变慢。
这种问题，也好解决，能够通过 force index 指定索引。比方
通过 explain 能够看出，加了 force index 之后，sql 就选用了 idx_age 这个索引了。
走了索引还是很慢
有些 sql，用 explain 命令看，明明是走索引的，但还是很慢。个别是两种状况：
第一种是索引区分度太低，比方网页全门路的 url 链接，这拿来做索引，一眼看过来全都是同一个域名，如果 前缀索引 的长度建得不够长，那这走索引跟走 全表扫描 似的，正确姿态是尽量让索引的 区分度 更高，比方域名去掉，只拿前面 URI 局部去做索引。
第二种是索引中匹配到的数据太大，这时候须要关注的是 explain 里的 rows 字段了。
它是用于 预估 这个查问语句须要查的行数的，它不肯定齐全精确，但能够体现个大略量级。
当它很大时，个别常见的是上面几种状况。
- 如果这个字段具备 惟一 的属性，比方电话号码等，个别是不应该有大量反复的，那可能是你代码逻辑呈现了 大量反复插入 的操作，你须要查看下代码逻辑，或者须要加个 惟一索引 限度下。
- 如果这个字段下的数据就是会很大，是否须要全副拿？如果不须要，加个
limit限度下。如果的确要拿全副，那也不能一次性全拿，明天你数据量小，可能一次取一两万都没啥压力，万一哪天涨到了十万级别，那一次性取就有点吃不消了。你可能须要 分批次取 ，具体操作是先用order by id排序一下，拿到一批数据后取最大 id作为下次取数据的起始地位。
连接数过小
索引相干的起因咱们聊完了，咱们来聊聊，除了索引之外，还有哪些因素会限度咱们的查问速度的。
咱们能够看到，mysql 的 server 层里有个 连贯治理，它的作用是治理客户端和 mysql 之间的长连贯。
失常状况下，客户端与 server 层如果只有 一条 连贯，那么在执行 sql 查问之后，只能阻塞期待后果返回，如果有大量查问同时并发申请，那么 前面的申请都须要期待后面的申请执行实现 后，能力开始执行。
因而很多时候咱们的应用程序，比方 go 或 java 这些，会打印出 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 的缓存命中率。
通过 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 里曾经将这个性能干掉了。
