我熟练应用ctrl c和ctrl v 开发curd代码好多年了。mysql查询为什么会慢关于这个问题在实际开发经常会遇到而面试中也是个高频题。遇到这种问题我们一般也会想到是因为索引。那除开索引之外还有哪些因素会导致数据库查询变慢呢有哪些操作可以提升mysql的查询能力呢今天这篇文章我们就来聊聊会导致数据库查询变慢的场景有哪些并给出原因和解决方案。数据库查询流程我们先来看下一条查询语句下来会经历哪些流程。比如我们有一张数据库表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) ) ENGINEInnoDB DEFAULT CHARSETutf8;我们平常写的应用代码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 ( ... ) ENGINEInnoDB;现在最常用的是InnoDB。我们就重点说这个。InnoDB中因为直接操作磁盘会比较慢所以加了一层内存提提速叫buffer pool这里面放了很多内存页每一页16KB有些内存页放的是数据库表里看到的那种一行行的数据有些则是放的索引信息。bufferPool与磁盘查询SQL到了InnoDB中。会根据前面优化器里计算得到的索引去查询相应的索引页如果不在buffer pool里则从磁盘里加载索引页。再通过索引页加速查询得到数据页的具体位置。如果这些数据页不在buffer pool中则从磁盘里加载进来。这样我们就得到了我们想要的一行行数据。索引页与磁盘页的关系最后将得到的数据结果返回给客户端。慢查询分析如果上面的流程比较慢的话我们可以通过开启profiling看到流程慢在哪。mysql set profilingON; 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 age60 | | 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 age60对应的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 age60分析一下。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执行达到了几分钟甚至超时那有可能是因为你应用侧gojava写的应用也就是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里已经将这个功能干掉了。
