인덱스 외에 mysql 쿼리를 느리게 만드는 다른 요인은 무엇입니까?
- 青灯夜游앞으로
- 2022-07-19 20:22:491906검색
저는 수년 동안 ctrl c와 ctrl v를 사용하여 커드 코드를 개발하는 데 능숙했습니다.
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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
우리가 일반적으로 작성하는 애플리케이션 코드(go 또는 C++ 등)는 현재 client라고 합니다.
클라이언트의 최하위 계층은 계정 비밀번호를 가져와 mysql에 대한 TCP 긴 링크 설정을 시도합니다.
mysql의 연결 관리 모듈이 이 연결을 관리합니다.
연결이 설정된 후 클라이언트는 쿼리 SQL 문을 실행합니다. 예:
select * from user where gender = 1 and age = 100;
클라이언트는 네트워크를 통해 sql 문을 mysql에 연결합니다.
mysql은 sql 문을 받은 후 먼저 analyzer의 SQL 문에 문법 오류가 있는지 확인합니다. 예를 들어 l이 하나 적으면 작성합니다. 선택으로 SQL 구문에 오류가 있습니다. 오류가 보고됩니다. 이 오류 보고서는 나 같은 장애인에게는 매우 친숙하다고 할 수 있습니다. 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
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)🎜현재 가장 일반적으로 사용되는 것은 🎜InnoDB🎜입니다. 🎜🎜이것에 집중합시다. 🎜🎜InnoDB에서는 디스크를 직접 작동하는 것이 느리기 때문에 속도를 높이기 위해 🎜버퍼 풀🎜이라는 메모리 계층이 추가됩니다. 그 안에는 많은 메모리 페이지가 있으며, 각 페이지에는 16KB의 메모리 페이지가 있습니다. 에서 볼 수 있는 행별 데이터의 종류이며, 그 중 일부에는 인덱스 정보가 포함되어 있습니다. 🎜🎜
🎜느린 쿼리 분석🎜🎜🎜위 프로세스가 느린 경우 프로파일링을 켜면 프로세스가 느린 부분을 확인할 수 있습니다. 🎜func Init() {
db, err := gorm.Open(mysql.Open(conn), config)
sqlDB, err := db.DB()
// SetMaxIdleConns 设置空闲连接池中连接的最大数量
sqlDB.SetMaxIdleConns(200)
// SetMaxOpenConns 设置打开数据库连接的最大数量
sqlDB.SetMaxOpenConns(1000)
}🎜그럼 정상적으로 sql 문을 실행해 보세요. 🎜🎜이러한 SQL 문의 실행 시간이 기록됩니다. 이때 어떤 명령문이 기록되었는지 확인하려면 showprofiles;를 실행하면 됩니다.🎜mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.01 sec)
🎜위의 query_id, 예를 들어 select * from user where age>=60가 1인 것에 해당하는 query_id입니다. 이 SQL 문의 구체적인 시간 소비를 확인하려면 다음을 수행하세요. 다음 명령을 실행하십시오. 🎜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)
🎜위 항목을 통해 특정 시간이 어디서 소비되는지 알 수 있습니다. 예를 들어, 데이터를 보내는 데 가장 많은 시간이 걸린다는 것을 위에서 볼 수 있습니다. 이는 내 테이블에 수만 개의 한정된 데이터가 있기 때문에 Executor가 데이터 쿼리를 시작하고 데이터를 클라이언트에 보내는 데 걸리는 시간을 의미합니다. data.이므로 이 부분이 가장 시간이 많이 걸리고 기대에 부합합니다. 🎜🎜일반적으로 개발 과정에서 대부분의 시간은 데이터 전송 단계에서 소요됩니다. 이 단계에서 속도가 느리다면 가장 많이 생각할 수 있는 이유는 인덱스 관련 이유입니다. 🎜Index 관련 이유
Index 관련 문제는 일반적으로 explain 명령을 사용하여 분석할 수 있습니다. 이를 통해 어떤 인덱스가 사용되는지, 그리고 아마도 몇 행이 스캔되는지 및 기타 정보를 확인할 수 있습니다.
mysql은 최적화 단계에서 선택할 인덱스를 살펴보고 쿼리 속도가 더 빨라집니다.
일반적으로 다음과 같은 여러 요소가 주로 고려됩니다.
- 이 인덱스를 선택하려면 몇 개의 행(행)을 스캔해야 합니까? 이러한 행을 추출하려면 몇 개의 16kb 페이지를 읽어야 합니까?
- 사용 일반 인덱스 테이블 반환이 필요하지만 기본 키 인덱스는 필요하지 않습니다. 테이블 반환 비용은 얼마입니까?
- 쇼 프로필에 언급된 SQL 문으로 돌아가서
explain select * from user where age>=60을 사용하여 분석합니다.
위 문에서는 type을 ALL로 사용합니다. 이는 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，用explainfull table Scan
possible_keys는 사용 가능성이 있는 인덱스를 참조합니다. 여기서 사용할 수 있는 인덱스는 연령에 맞춰 작성된 일반 인덱스이지만 실제로는 데이터베이스에서 사용하는 인덱스가 keycode> 열에 있습니다. NULL입니다. 즉, 이 SQL은 인덱스를 사용하지 않고 테이블 전체를 스캔합니다. 데이터 테이블에 정규화된 데이터 행(rows)이 너무 많기 때문입니다. 연령 인덱스를 사용하는 경우 이를 연령 인덱스에서 읽어내야 하는데 연령 인덱스는 일반 인덱스
데이터 페이지를 찾으려면
해당 기본 키를 찾아야 합니다. 결국 기본 키를 직접 사용하는 것만큼 비용 효율적이지는 않습니다. 그래서 결국 전체 테이블 스캔을 선택했습니다.
물론 위의 내용은 단지 예시일 뿐입니다. 실제로 mysql이 sql을 실행할 때 인덱스가 사용되지 않거나 사용된 인덱스가 기대에 미치지 못하는 경우가 있습니다.이런 경우는 자주 발생합니다.
부등호 사용, 수식변환숨기기 등. 8부작 에세이를 외울 때 많이 외우셨을 거라 생각해서 자세한 내용은 생략하겠습니다.
🎜제작 과정에서 쉽게 접할 수 있는 두 가지 문제에 대해 이야기해보겠습니다. 🎜🎜인덱스가 기대에 미치지 못합니다🎜
🎜실제 개발에서는 상황이 특별합니다. 예를 들어 일부 데이터베이스 테이블에는 데이터 양이 적고 인덱스가 거의 없습니다. 실제로 SQL을 실행할 때 예상한 인덱스를 사용하십시오. 하지만 시간이 지날수록 더 많은 사람들이 발전하고, 데이터의 양도 늘어나고, 일부 반복적이고 중복되는 인덱스가 추가될 수도 있으며, 기대에 미치지 못하는 다른 인덱스가 사용될 수도 있습니다. 이로 인해 쿼리가 갑자기 느려집니다. 🎜🎜이러한 문제는force index🎜를 통해 인덱스를 지정하면 쉽게 해결할 수 있습니다. 예를 들어 🎜🎜explain을 통해 force index를 추가하면 SQL이 use 인덱스 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里已经将这个功能干掉了。
最后
最近原创更文的阅读量稳步下跌，思前想后，夜里辗转反侧。
我有个不成熟的请求。
离开广东好长时间了，好久没人叫我靓仔了。
大家可以在评论区里，叫我一靓仔吗？
我这么善良质朴的愿望，能被满足吗？
如果实在叫不出口的话，能帮我点下右下角的点赞和在看吗？
【相关推荐：mysql视频教程】
위 내용은 인덱스 외에 mysql 쿼리를 느리게 만드는 다른 요인은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!