大家好，我是君哥，为什么mysql查询慢？关于这个问题，在实际开发中经常遇到，也是面试中经常被问到的问题。遇到这种问题，我们一般认为是索引的问题。那么除了索引之外，还有哪些因素会导致数据库查询变慢呢？哪些操作可以提高mysql的查询能力？在今天的文章中，我们来谈谈导致数据库查询变慢的场景，并给出原因和解决方案。我们先来看一下数据库查询的过程，一条查询语句会经过怎样的过程。例如，我们有一个数据库表CREATETABLE`user`(`id`int(10)unsignedNOTNULLAUTO_INCREMENTCOMMENT'primarykey',`name`varchar(100)NOTNULLDEFAULT''COMMENT'name',`age`int(11)NOTNULLDEFAULT'0'COMMENT'age',`gender`int(8)NOTNULLDEFAULT'0'COMMENT'gender',PRIMARYKEY(`id`),KEY`idx_age`(`age`),KEY`idx_gender`(`gender`))ENGINE=InnoDBDEFAULTCHARSET=utf8;我们平时写的应用程序代码（go或者C++）这时候就叫做client。客户端底层会拿着账号密码，尝试与mysql建立TCP长连接。mysql的连接管理模块会管理这个连接。连接建立后，客户端执行查询sql语句。例如：select*fromuserwheregender=1andage=100;客户端将sql语句通过网络连接到mysql。mysql收到sql语句后，首先会在分析器中判断sql语句是否有语法错误，比如select，如果少了一个l写成slect，就会报错youhaveanerrorin你的SQL语法；这个报错对于我这种手残党来说再熟悉不过了。接下来是优化器，它会根据一定的规则选择使用什么索引。之后通过执行器调用存储引擎的接口函数。Mysql架构的存储引擎与各个组件类似。它们是mysql真正获取一行数据并返回数据的地方。存储引擎可以更换和改变。可以换成不支持事务的MyISAM，或者支持事务的Innodb。.这可以在创建表时指定。例如，CREATETABLE`user`(...)ENGINE=InnoDB;现在最常用的是InnoDB。让我们专注于此。在InnoDB中，因为直接操作磁盘比较慢，所以加了一层内存来提速，叫做bufferpool。内存页有很多，每页16KB，数据库表中存放了一些内存页。那种一行一行的数据，有的是索引信息。bufferPool和磁盘查询SQL到InnoDB。根据前面优化器中计算出的索引，查询对应的索引页。如果不在缓冲池中，索引页将从磁盘加载。然后利用索引页加速查询，得到数据页的具体位置。如果这些数据页不在缓冲池中，则从磁盘加载它们。这样，我们就得到了我们想要的那一行数据。索引页与磁盘页的关系最终将获取的数据结果返回给客户端。慢查询分析如果上面的过程比较慢，我们可以通过开启profiling来查看是哪里的过程慢了。mysql>setprofiling=ON;QueryOK,0rowsaffected,1warning(0.00sec)mysql>showvariableslike'profiling';+----------------+-------+|变量名|值|+----------------+------+|剖析|ON|+-----------------+--------+1rowinset(0.00sec)并正常执行sql语句。这些SQL语句的执行时间都会被记录下来。这时候如果想查看记录了哪些语句，可以执行showprofiles;mysql>showprofiles;+--------+-----------+----------------------------------------------------+|查询ID|持续时间|查询|+------------+------------+---------------------------------------------------+|1|0.06811025|从年龄>=60的用户中选择*||2|0.00151375|从性别=2和年龄=80的用户中选择*||3|0.00230425|从性别=2和年龄=60的用户中选择*|4|0.00070400|从性别=2和年龄=100的用户中选择*||5|0.07797650|select*fromuserwhereage!=60|+------------+------------+------------------------------------------------+5rowsinset,1warning(0.00sec)注意以上面的query_id为例，select*fromuserwhereage>=60对应query_id1，如果想查看这条SQL语句的具体耗时，可以执行如下命令。mysql>显示查询1的配置文件;+--------------------+----------+|状态|持续时间|+---------------------+---------+|开始|0.000074||检查权限|0.000010||打开表格|0.000034||初始化|0.000032||系统锁|0.000027||优化|0.000020||统计|0.000058||准备|表|0.000014||释放物品|0.000047||清理|0.000027|+--------------------+------------+15行在集合中，1个警告（0.00秒）通过以上项目，你可以看到具体的时间都花在了哪里。比如从上面可以看出Sendingdata耗时最多。这是指执行器开始查询数据并将数据发送给客户端所花费的时间。因为我的表有几万条符合条件的数据，所以这个是最耗时的，也符合预期。一般情况下，在我们的开发过程中，大部分时间都花在发送数据阶段，如果这个阶段比较慢，很有可能是索引相关的原因。索引相关的原因索引相关的问题一般可以借助explain命令来分析。通过它，你可以看到使用了哪些索引，以及将扫描多少行。MySQL在优化器阶段会看选择哪个索引，查询速度会更快。一般会考虑几个因素，比如：应该扫描多少行（rows）来选择这个索引？为了fetch这些行，普通索引需要读多少个16kb的page回表，而主键索引不回表，回表的代价大吗？回到showprofile中提到的SQL语句，我们使用explainselect*fromuserwhereage>=60来分析。上面explainsql语句中，使用的类型是ALL，即全表扫描。possible_keys是指可能使用的索引。这里可能用到的索引是为age建立的普通索引，但实际上数据库使用的索引是在key列上的，也就是NULL。也就是说这个sql没有使用索引，而是扫描全表。这是因为数据表中符合条件的数据行（行）过多。如果使用age索引，需要从age索引中读取，age索引是普通索引，需要回表查找对应的主键。找到对应的数据页。毕竟还是直接上主键比较好。所以最终还是选择了全表扫描。当然，以上只是一个例子。实际上，MySQL在执行SQL的时候，经常会出现没有使用索引或者使用的索引不符合我们预期的情况。索引失效的场景有很多，比如使用不等号，隐式转换等，这个相信大家已经背了很多八股文，不再赘述。下面说说生产中容易遇到的两个问题。指标不符合预期实际开发中有些情况比较特殊。比如一些数据库表，数据量小，一开始索引很少。在执行SQL时，确实使用了符合你预期的索引。但是随着时间的推移，开发的人越来越多，数据量越来越大，甚至可能会增加一些其他的冗余索引，这可能会导致使用其他不符合你预期的索引。.这会导致查询突然变慢。这种问题也很好解决，可以通过强制索引来指定索引。比如forceindex指定的索引可以从explain中看到。加入强制索引后，SQL选择索引idx_age。索引还是很慢有些sql，使用explain命令，明明用了索引，但是还是很慢。一般有两种情况：第一种是索引辨别度太低，比如网页全路径的url链接，用来做索引。一看，都是同一个域名。如果前缀索引的长度不够长，那么这种索引方式类似于全表扫描。正确的姿势是让索引更具差异化，比如去掉域名，只使用URI部分做索引。索引前缀区分度太低二是索引中匹配的数据太大。这时候我们需要注意explain中的rows字段。它用于估计查询语句需要检查的行数。它可能不完全准确，但它可以反映一个大致的数量级。当它很大时，一般有以下几种情况。如果这个字段有唯一属性，比如电话号码等，一般应该不会有大量重复，可能是你的代码逻辑有大量重复的插入操作，需要检查代码逻辑，或者你需要添加唯一索引限制Down。如果这个字段的数据会很大，是否需要全部取出来？如果不需要，请添加限制限制。如果你真的想得到所有的东西，你不可能一下子得到所有的东西。今天，你的数据量很小，一次提取10000到20000可能没有压力。要是哪天涨到十万级别，那就有点吃不消了。您可能需要分批获取。具体操作是先用orderbyid排序。拿到一批数据后，取最大的id作为下一次取数据的起始位置。连接数太少索引相关的原因我们说完了。下面说说除了索引还有哪些因素会限制我们的查询速度。我们可以看到在mysql的server层有一个connectionmanagement，它的作用就是管理client和mysql的长连接。一般情况下，如果client和server层只有一个连接，执行sql查询后，只能阻塞等待结果返回。如果同时有大量的查询和并发请求，那么后面的请求需要等待前面的请求执行完成。开始执行。连接太少会导致sql阻塞，所以很多时候我们的应用，比如go或者java，会打印几分钟sql执行的log，但实际上你把这条语句拿出来单独执行，但是在毫秒级别。这都是因为这些sql语句都在等待前面的sql执行完成。如何解决？如果我们能多建立几个连接，那么请求就可以并发执行，后面的连接就不用等那么久了。增加连接数可以加快sql的执行速度，但是连接数太少，受数据库和客户端的限制。数据库连接数太少。Mysql默认的最大连接数是100，最大可以达到16384，可以通过设置mysql的max_connections参数来改变数据库的最大连接数。mysql>setglobalmax_connections=500;QueryOK,0rowsaffected(0.00sec)mysql>showvariableslike'max_connections';+----------------+-------+|变量名|值|+----------------+------+|最大连接|500|+----------------+--------+1rowinset(0.00sec)以上操作将最大连接数更改为500。应用程序端的连接数太小。数据库连接的大小已经调整了，但是问题好像没有变？还是有很多sql执行需要几分钟，甚至超时？那可能是因为你的应用端（go，java写的应用，也就是mysql客户端）的连接数也太少了。应用端与mysql底层的连接是基于TCP协议的长连接，TCP协议需要三次握手四次挥手来实现连接的建立和释放。如果我每次执行sql都重新建立一个新的连接，不停地握手和挥手很费时间。所以一般会建立一个长连接池。连接用完后，塞入连接池。下次要执行该SQL时，从中取出一个连接，非常环保。连接池的原理我们一般在写代码的时候，都会通过第三方的orm库来操作数据库，成熟的orm库都会有1000万的连接池。而这个连接池一般是有大小的。此大小控制您拥有的最大连接数。如果你的连接池太小，不能和数据库一样大，那么调整数据库的最大连接数是没有效果的。一般情况下，你可以阅读你使用的orm库的文档，看看如何设置连接池的大小，改几行代码就可以了。比如go语言的gorm是这样设置的funcInit(){db,err:=gorm.Open(mysql.Open(conn),config)sqlDB,err:=db.DB()//SetMaxIdleConns设置最大值空闲连接池中的连接数sqlDB.SetMaxIdleConns(200)//SetMaxOpenConns设置最大打开数据库连接数sqlDB.SetMaxOpenConns(1000)}缓冲池太小，连接数上来了，速度也快了改善。我遇到过面试官会问，有没有其他办法可以让它更快？然后你必须皱眉，假装思考，然后说：是的。在前面的数据库查询过程中，我们提到进入InnoDB后，会有一层内存缓冲池，用于将磁盘数据页加载到内存页中。只要在bufferpool中找到query，就可以直接返回，否则会去磁盘IO，会很慢。也就是说，如果我的bufferpool越大，我们能存放的数据页就越多，相应的，sql查询就更容易命中bufferpool，查询速度自然会更快。缓冲池的大小可以通过以下命令查询，单位为Byte。mysql>showglobalvariableslike'innodb_buffer_pool_size';+------------------------+------------+|变量名|价值|+------------------------+------------+|innodb_buffer_pool_size|134217728|+-----------------------+------------+1行集合（0.01秒）是128MB。如果你想让它变大。你可以执行mysql>setglobalinnodb_buffer_pool_size=536870912;QueryOK,0rowsaffected(0.01sec)mysql>showglobalvariableslike'innodb_buffer_pool_size';+---------------------------+------------+|变量名|值|+--------------------+----------+|innodb_buffer_pool_size|536870912|+------------------------+-----------+1行在集合中（0.01秒）这会将缓冲池增加到512Mb。但是，如果缓冲池的大小是正常的，但是由于其他原因导致查询变慢，那么改变缓冲池是没有意义的。但是问题又来了。你怎么知道缓冲池是否太小？我们可以看一下缓冲池的缓存命中率。通过showstatuslike'Innodb_buffer_pool_%'检查缓冲池命中率；你可以看到一些与缓冲池相关的信息。Innodb_buffer_pool_read_requests表示读请求的数量。Innodb_buffer_pool_reads表示从物理磁盘读取数据的请求数。所以bufferpool的命中率可以这样得到：bufferpoolhitrate=1-(Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100%比如上面的截图，1-(405/2278354)=99.98%。可以说命中率是非常高的。一般缓冲池命中率在99%以上。如果低于这个值，就要考虑增加innodb缓冲池的大小。当然这个命中率也可以包含在监控中，让半夜sql变慢，早上上班的时候就可以定位到原因，很舒服。还有哪些技巧？前面提到，在存储引擎层增加了缓冲池来缓存内存页，可以加快查询速度。同样的道理，server层也可以加一个缓存，直接缓存第一次查询的结果，这样下次查询就可以马上返回，听上去很美。按理说命中缓存的话，确实可以加快查询速度。但是这个功能非常有限。最大的问题是只要更新数据库表，表中的所有缓存都会失效。频繁更新数据表会带来频繁的缓存失效。所以这个功能只适用于那些更新不多的数据表。另外，这个功能在8.0版本之后被移除了。所以这个功能可以用来聊天，生产中没必要用。查询缓存被删除。总结数据查询速度太慢。一般是索引问题。可能是因为选择了错误的索引，也可能是因为查询的行数太多。如果客户端和数据库的连接数太少，SQL查询的并发数会受到限制，增加连接数可以提高速度。innodb中会有一层内存缓冲池来提高查询速度。命中率一般>99%。如果低于这个值，可以考虑增加缓冲池的大小，这样也可以提高速度。查询缓存（querycache）确实可以加快查询速度，但是一般不建议开启，因为限制比较大，8.0以后的Mysql已经杀掉了这个功能。
