mysql数据库查询好慢，除了索引，还能因为什么？

我正在参加技能社区创作者签约计划招募活动，点击链接报名投稿。

我熟练运用ctrl c和ctrl v 开发curd代码好多年了。

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

遇到这种问题，咱三次握手们一般也会想到是由于索引。

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

有哪些操作，能够提高mysql的查询能力呢？

今日这Java篇文章，咱们就来聊聊接口测试用例设计会导致数据库查询变慢的三次握手详细过程场景有哪些，并给出原因和解决方案。

数据库查询流程

咱们先来看下，一条查询句子下来，会阅历哪些流程。

比方咱们有一张数据库表

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;

咱们往常写的运用代码（g监控安装流程o或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;

现在最常用的是javascriptInnoDB。

咱们就要点说这个。

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

查询SQL到了InnoDjava培训B中。会依据前面优化器里核算得到的索引，去查询相应的索引页，假如不在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句子的履行时刻都会被记录下来，此刻你想查看有哪java怎么读些句子被记录下来了，能够履行 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 whjava是什么意思ere age&三次握手和四次挥手gt;=60对应的query_监控app下载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会在优化器阶段里看下挑选哪个索引，查询速度会更java面试题快。

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

• 挑选这个索引大约要扫描多少行（rows）
• 为了把这些行取出来，需求读多少个16kb的页
• 走一般索引需求回表，主键索引则不需求，回表本钱大不大？

回到show profile中提到的sq监控系统l句子，咱们运用explain索引失效 select * from user where age>=60 分析一下。

上面的这条句子，运用的ty监控家用远程手机pe为ALL，意味着是全表扫描，possible_keys是指或许用得到的索引，这儿或许运用到的索引是为age建的一般索引，但实践上数据库运用的索引是在key那一列，接口文档是NULL。也便是说这句sql三次握手方法用于不走索引，全表扫描。

这个是由于数据表里，契合条件的数据行数（rows）太多，假索引是什么意思如运用age索引，那么需求将它们从age索引中读出来监控家用远程手机，而且age索引是一般索引，还需求回表找到对应的主键才能找到对应的数据页。算下来还不如直接走主键划算。所以终究挑选了全表扫描。

当然上面只是举了个比方，实践上，mysql履行sql时，不必索引或者用的索引不契合咱们预期这件事常常产生，索引失效的场景有许多，比方用了不等号，隐式转换等，这个信任咱们背八股文的监控摄像头品牌排行时分也背过不少了，我也不再赘述。

聊两个生产中简单遇到的问题吧。

索引不契合预期

实践开发中有些状况比较特殊，比方有些数据库表一开端数据量小，索引少，履行sql时，的确运用了契合你预期的索引。但随时时刻边长，开发的人变多了，数据量也变大了，乃至还或许会参加一些其他重复多余的索引，就有或许呈现用着用着，用到了不契合你预期的其他索引了。从而导致查询突然监控拍下东航客机坠落瞬间变索引失效慢。

这种问题，也好解决，能够经过force index指定索引。比方

经过explain能够看出，加了fo监控拍下东航客机坠落瞬间rce index之后，sq索引l就选用了idx_age这个索引了。

走了索引仍是很慢

有些sql，用explain指令看，分明是Java走接口索引的，但仍是很慢。一般是两种状况：

第接口卡一种是索引区分度太低，比方网页全接口路径的url链接，这拿来做索引，一眼看过去全都是同一个域名，假如前缀索引接口测试用例设计的长度建得不够长，那这走索引跟走全表扫描似的，正确姿态是尽量让索引的区分度更高，比方java编译器域名去掉，只拿后边URI部分去做索引。

第二种是索引中匹配到的数据太大，这时分需三次握手方法用于求重视的是explain里的rows字段了。

它是用于预估这个查询句子需求查的行数的，它不必定完全精确，但能够表现个大约量级。

当它很大时，一般常见的是下面几种状况三次握手过程。

• 假如这个字段具有唯一的特点，比方电话号码等，一般是不应该有很多重复的，那或许是你代码逻辑呈现了很多重复插入的操作，你需求接口卡查看下代码逻辑，或者需求加个唯一索引约束下。
• 假如这个字段下的数据便是会很大，是否需求悉数拿？假如java面试题不需求，加个limit约束下。假如的确要拿悉数，那也不能一三次握手次性全拿，今日你数据量小，或java是什么意思许一次取一两万都没啥压力，假如哪天涨到了Java十万等级，那一次性取就有点吃不消了。你或许需求分接口自动化批次取，详细操作是先用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)

上面的操作，就把最大衔接数改成了5监控可以保存多少天00。

运用侧衔接数过小

数据库衔接巨细是调整过了，但形似问题仍是没有改变？仍是有许多sql履监控摄像头行达到了几分钟，乃至超时？

那有或许是由于你运用侧（go，java写的运用，也便是mysql的客户端）的衔接接口是什么数也过小。

运用侧与myjava模拟器sql底层的监控摄像头衔接，是基于TCP协议的长链接，而TCP协javaee议，需求经过三次握手和四次挥手来实现建连和开释。假如我每次履行sql都重新树立一个新的衔接的话，那就要不断握手和java语言挥手，这很耗时。所以一般会树立一个长衔接池，衔接用完之后，塞到衔接池里，下非必须履行sql的监控拍下东航客机坠落瞬间时分，再从里边捞一条衔接出来用，十分环保。

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

而这个衔接池，一般会有个巨细。这个巨细就操控了你的衔接数最大值，假如说你的衔接池太小，都还没有数据库的大，那调java语言了数据库的最大衔接数也没啥效果。

一般状况下，能够翻下你运用的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的巨细java模拟器，单位是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增大到51Java2Mb了。

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

但问题又来了。

怎样知道buffer pool是不接口类型是太小了？

这索引是什么意思个咱们能够看buffer pool的缓存接口是什么射中率。

经过 show status like 'Innodb_buffer_pool_%';java环境变量配置能够看到跟b索引是什么意思uffer pool有关的一些信息。

Innodb_接口buffer_pool_read_requests表明读恳求的次数。

Innodb_接口测试buffer_pool_reads 表明从物理磁盘中读取数据的恳求次数。

所以buf接口fer pool的射中率就能够这样得到：

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

比方我上面截图里的便是，1 – (405/2278354) = 99.98%。能够说射中率十分高javaee了。

一般状况下buffer pool射中率都在99%以上，假如低于这个值，才需求考虑加大innodb索引超出了数组界限什么意思 buffer接口卡 p三次握手四次挥手过程ool的巨细。

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

还有哪些骚操作？

前面提到的是在存储接口类型引擎层里参加了buffer poojava面试题l用于缓存内存页，这样能够加速查询。

那相同的道理，server层也能够加个缓存，直java怎么读接将第一次查询的成果缓存下来，这样下次查询就能立刻回来，听着挺美的。

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

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

总结

• 数据查询过慢一般是索引问题，索引页是哪一页或许是由于选错索引，也或许是由于查询的行数太多。
• 客户端和数据库衔接数过小，会约束sql的查询并发数，增大衔接数能够提高索引是什么意思速度。
• innodb里会监控眼有一层内存buffer pool用于提高查询速度，射中率一般>99%，假如低于java培训这个值，能够考虑增大buffer pool的巨细，这样也能够提高速度。
• 查询缓java环境变量配置存（query cache）的确能为查询提速，但一般不建议翻开，由于约束比较大，而且8.0今后的mysql里已经将这个功用干掉了。

最后

最近原创更文的三次握手四次挥手过程阅览量稳步下跌，左思右想，夜里翻来覆去。

我有个不成熟的恳求。接口

脱离广东好长时刻了，好久没人叫我靓仔了。

咱java编译器们能够在评论区里，叫我一靓仔吗？

我这么善良质朴的愿索引页是哪一页望，能被满意吗？

假如真实叫不出口的话，能帮我点下右下角的点赞和在看吗？

别说了，一起在常识的海洋里呛水吧

我正在参加技能社区创作者签约计划招募活动，点击链接报名投稿。