MySQL慢查询(中):正确的处理姿势,你get到了吗?

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 继上篇:MySQL慢查询(上):你知道为啥会慢么?

上篇回顾


继上篇:MySQL慢查询(上):你知道为啥会慢么?


在上一篇内容中,我们一起探索了这些内容:


  • SQL执行过程


  • 查询SQL为什么会慢


通过梳理 MySQL中的 SQL执行过程我们发现,任何流程的执行都存在其执行环境和规则,主要导致慢查询最根本的问题就是需要访问的数据太多,导致查询不可避免的需要筛选大量的数据。


如果将MySQL慢查询作为一个问题来拆解分析的话,上一篇算是问题分析,那今天来跟大家聊聊问题定位和问题解决。


微信图片_20220608100135.png


本文主要内容包括:


1、如何定位慢查询问题


2、几种实用解决方案


废话不多说,直接开干~


1、如何定位问题呢?


发现了慢查询之后,关于如何定位问题发生原因,最常用的方法就是利用EXPLAIN关键字模拟查询优化器执行查询SQL,从而知道MySQL是如何处理你的查询SQL,通过执行计划来分析性能瓶颈。


通常我们使用EXPLAIN,会得到如下下的执行计划信息:


微信图片_20220608100139.png

关于各字段含义,大家可以通过检索自行了解,在此就不再过多赘述。


关于定位分析问题,关键看如下几点:


1)select_type


表示查询类型,用于区别普通查询、联合查询、子查询等复杂查询。


2)type


显示查询使用类型,从好到差依次为:system > const > eq_ref > ref > range > index > all


3)possible_keys 和 key


分别指可能应用的索引和实际应用的索引。


注意:查询中若使用了覆盖索引(select 后要查询的字段刚好和创建的索引字段完全相同),则该索引仅出现在key列表中。


4)rows


大致估算出找到所需记录所需要读取的行数(从效率上来讲,数值越小越好)


5)Extra


重要的额外信息。包含MySQL解决查询的详细信息,也是关键参考项之一。


2、几种实用解决方案


我们通过EXPLAIN关键字模拟查询优化器执行查询SQL,发现了慢查询问题原因,那看看如何才能有效解决呢?


推进几种较为实用的解决方案给大家。


2.1 优化数据结构


2.1.1 选择索引的数据类型


MySQL支持很多数据类型,选择合适的数据类型存储数据对性能有很大的影响。

通常来说,可以遵循以下一些指导原则:


(1)越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快。


(2)简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂。在MySQL中,应该用内置的日期和时间数据类型,而不是用字符串来存储时间;以及用整型数据类型存储IP地址。


(3)尽量避免NULL:应该指定列为NOT NULL,除非你想存储NULL。在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。


2.1.2 范式与反范式


  • 范式化


范式化模型要求满足下面三大范式:


1)数据库表中每个字段只包含最小的信息属性,不能再进行细化分解;


2)(在满足1的基础上)模型含有主键,非主键字段依赖主键;


比如用户这个模型,它的主键是用户ID,那么用户模型其它字段都应该依赖于用户ID


如商品ID和用户没有直接关系,则这个属性不应该放到用户模型而应该放到用户-商品”中间表。


3)(在满足2的基础上)模型非主键字段不能相互依赖。


订单表(订单编号,购日期,顾客编号,顾客姓名,……)


初看该表没有问题,满足第二范式,每列都和主键列”订单编号”相关。


再细看你会发现“顾客姓名”和顾客编号”相关,顾客编号”和订单编号”又相关,最后经过传递依赖,顾客姓名”也和订单编号”相关。


为了满足第三范式,应去掉顾客姓名”列,放入客户表中。


  • 反范式化


反范式化模型即不满足范式化的模型。主要是为了性能和效率的考虑适当的违反范式化设计要求,允许存在少量的数据冗余,即以空间换时间。


小结


可见一个良好而实用的数据模型往往是依赖于具体的需求场景的,在设计数据模型之前,仔细分析需求场景,不仅能提高效率,也能有效规避后期可能遇到的一些意外麻烦。


范式化设计和反范式化设计的优劣对比如下:


1、范式化可以尽量的减少数据冗余


2、范式化的更新操作比反范式化更快


3、范式化的表通常比反范式化的表要小


4、反范式化减少表的关联


5、反范式化相比范式化可以更好的对索引进行优化,例如使用覆盖索引。


关于数据库范式与反范式设计,详情可参考我之前的一篇文章:数据库范式与反范式设计,是一门艺术


2.2 应用索引策略


微信图片_20220608100142.jpg


索引(MySQL中也被称为“键Key”),是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键,尤其当表中的数据量越来越大时,索引对性能的影响愈发重要(不恰当的索引对会随数据量增大时,性能急剧下降)。



举例如下情况:


假设数据库中一个表有10^6条记录,DBMS的页面大小为4K(约可存储100条记录)。


如果没有索引,查询将对整个表进行扫描,最坏的情况下,如果所有数据页都不在内存,需要读取10^4个页面,如果这10^4个页面在磁盘上随机分布,需要进行10^4次I/O,假设磁盘每次I/O时间为10ms(忽略数据传输时间),则总共需要100s(但实际上要好很多很多)。


如果对之建立B-Tree索引,则只需要进行log100(10^6)=3次页面读取,最坏情况下耗时30ms。这就是索引带来的效果。


了解了索引的优点之后,其实正确的创建和使用索引是实现高性能查询的基础。

可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询,当然,如果想使用索引,必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。


2.2.1 最左边前缀主要规则


  • 匹配全值(Match the full value):对索引中的所有列都指定具体的值。例如,上图中索引可以帮助你查找出生于1960-01-01的Cuba Allen。


  • 匹配最左前缀(Match a leftmost prefix):你可以利用索引查找last name为Allen的人,仅仅使用索引中的第1列。


  • 匹配列前缀(Match a column prefix):例如,你可以利用索引查找last name以J开始的人,这仅仅使用索引中的第1列。


  • 匹配值的范围查询(Match a range of values):可以利用索引查找last name在Allen和Barrymore之间的人,仅仅使用索引中第1列。


  • 匹配部分精确而其它部分进行范围匹配(Match one part exactly and match a range on another part):可以利用索引查找last name为Allen,而first name以字母K开始的人。


  • 仅对索引进行查询(Index-only queries):如果查询的列都位于索引中,则不需要读取元组的值。


由于B-树中的节点都是顺序存储的,所以可以利用索引进行查找(找某些值),也可以对查询结果进行ORDER BY。


当然,使用B-tree索引有以下一些限制:


  • 查询必须从索引的最左边的列开始。关于这点已经提了很多遍了。例如你不能利用索引查找在某一天出生的人。


  • 不能跳过某一索引列。例如,你不能利用索引查找last name为Smith且出生于某一天的人。


  • 存储引擎不能使用索引中范围条件右边的列。例如,如果你的查询语句为WHERE lastname="Smith" AND firstname LIKE 'J%' AND dob='1976-12-23',则该查询只会使用索引中的前两列,因为LIKE是范围查询。


2.2.2 聚簇索引


聚簇索引保证关键字的值相近的元组存储的物理位置也相同(所以字符串类型不宜建立聚簇索引,特别是随机字符串,会使得系统进行大量的移动操作),且一个表只能有一个聚簇索引。因为由存储引擎实现索引,所以,并不是所有的引擎都支持聚簇索引。目前,只有solidDB和InnoDB支持。


InnoDB对主键建立聚簇索引。如果你不指定主键,InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引,InnoDB会定义一个隐藏的主键,然后对其建立聚簇索引。


2.3 查询缓存


MySQL查询缓存会保存查询返回的完整结果。当查询命中缓存,MySQL会立刻返回结果,而跳过了后续解析、优化以及执行阶段,会有效提升查询性能。


但是查询缓存不是银弹,它也会存在一些问题。


2.3.1 查询缓存注意事项


1)缓存情况严格


存在一些不确定函数情况无法使用查询缓存,如:NOW()、CURRENT_DATE() 等类似的函数;


超过 query_cache_size (设置查询缓存空间大小)的查询结果无法被缓存;

同时大小写敏感,只有字符串相等情况下查询SQL才使用相同缓存。


-- 不会使用同一个缓存
select name from users where id = 1;
SELECT name FROM users WHERE id = 1;


2)缓存易失效


假如缓存过查询结果,但是由于查询缓存设置内存不足,新缓存加入时MySQL会将某些缓存逐出,导致后续查询未命中。同时数据结构及数据修改,内存不足,缓存碎片都会导致缓存失效。


2.3.2 小结


查询缓存对应用程序完全透明,应用程序无需关心MySQL是通过查询缓存返回的还是实际执行返回的结果。但随着目前服务器性能越来越强,查询缓存被发现是一个影响服务器扩展性的因素,它很可能成为整个服务器的资源竞争点,大家采用生产环境开启应用时候一定要慎重考量


2.4 重构查询方式


优化慢查询时候,我们可以转换下思路,我们的目标是找到一个更优的方法获取时间需要的结果,而不是一定从MySQL获取一模一样的结果集。重构查询的技巧很有必要。


2.4.1 复杂查询拆分


将一个复杂查询拆分多个简单查询,考虑是否需要将一个复杂查询拆分为多个简单查询。


实际开发过程中,大家往往会强调数据库层完成尽可能多的工作,这样做的初衷是认为网络通信、查询解析和优化是一件代价很高的事情,其实MySQL从设计上让连接和断开都很轻量级,同时在返回一个小查询结果方面很高效。况且目前网络速度也比之前快很多,无论是带宽还是延迟。


对于大查询我们要“分而治之”,将大查询切分多个小查询。不过在一次查询能够胜任的情况下还拆成多个独立查询就不明智了。


例如:做数据库做10次查询,每次返回一行记录。


2.4.2 分解关联查询


将关联查询进行分解,对每一个表进行一次单表查询,然后将结果在应用程序中进行关联。


例如:


SELECT *
FROM users
LEFT JOIN orders ON orders.user_id = users.user_id
LEFT JOIN goods ON goods.good_id = orders.good_id
WHERE users.name = 'zhangsan';


以上查询可以分解成下面的查询来代替:


SELECT * FROM users WHERE users.name = 'zhangsan';
SELECT * FROM orders WHERE orders.user_id = 103;
SELECT * FROM goods WHERE goods.good_id IN  (123, 456, 789);


为什么要这样做呢?看起来好像没有什么好处,而且返回数据结果也是一致的。实际上利用分解查询的方式来重构查询有很大的优势,主要表现为:


  • 将查询分解后,执行单个查询可减少锁的竞争;


  • 应用层做关联,更容易对数据库进行拆分,更易于做到高性能和可扩展;


  • 减少冗余记录的查询(在应用层做关联,表示对某条记录应用只需要查询一次,而在数据库中做关联查询,则可能需要重复访问一部分数据。


小结


MySQL慢查询问题细数起来,林林总总太多了,但行之有效的无外乎这几种:


  • 优化数据结构


  • 应用索引策略


  • 查询缓存


  • 重构查询方式


实践出真知,如果大家有任何其他好的解决方法可以留言与我交流,希望对你的工作有所帮助,谢谢!

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
缓存 关系型数据库 MySQL
MySQL慢查询优化策略
MySQL慢查询优化是一个复杂的过程,需要根据具体的应用场景和数据特点进行。以上策略是提升数据库查询性能的有效途径,但最关键的是对系统进行持续的监控和分析,及时发现并解决性能瓶颈。通过实践这些策略,你可以显著提高MySQL数据库的性能,为用户提供更快的响应时间和更好的体验。
350 10
|
3月前
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
4月前
|
SQL 缓存 关系型数据库
MySQL 慢查询是怎样优化的
本文深入解析了MySQL查询速度变慢的原因及优化策略,涵盖查询缓存、执行流程、SQL优化、执行计划分析(如EXPLAIN)、查询状态查看等内容,帮助开发者快速定位并解决慢查询问题。
163 0
|
4月前
|
SQL 监控 关系型数据库
MySQL慢查询攻略
本文详细介绍了MySQL慢查询优化的全流程,从定位性能瓶颈到具体优化策略,再到高级调优与预防监控。首先通过开启慢查询日志和分析工具(如pt-query-digest)找到问题SQL,接着从索引优化(如最左前缀原则、覆盖索引)、SQL语句重构(如避免全表扫描)及EXPLAIN执行计划解析等方面进行核心优化。随后深入参数调优和架构升级,如调整innodb_buffer_pool_size、实施分库分表等。最后,通过实时监控工具(如PMM、Prometheus+Grafana)建立长效机制,并以电商订单查询为例,展示优化前后性能大幅提升的实战效果。
429 0
|
11月前
|
SQL 关系型数据库 MySQL
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
MySQL慢查询优化、索引优化,是必知必备,大厂面试高频,本文深入详解,建议收藏。关注【mikechen的互联网架构】,10年+BAT架构经验分享。
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
|
9月前
|
关系型数据库 MySQL 数据库
mysql慢查询每日汇报与分析
通过启用慢查询日志、提取和分析慢查询日志,可以有效识别和优化数据库中的性能瓶颈。结合适当的自动化工具和优化措施,可以显著提高MySQL数据库的性能和稳定性。希望本文的详解和示例能够为数据库管理人员提供有价值的参考,帮助实现高效的数据库管理。
218 11
|
9月前
|
关系型数据库 MySQL Docker
docker pull mysql:8.0.26提示Error response from daemon: Get “https://registry-1.docker.io/v2/“: EOF错误
docker pull mysql:8.0.26提示Error response from daemon: Get “https://registry-1.docker.io/v2/“: EOF错误
|
10月前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
258 18
|
11月前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案,包括索引优化、SQL慢查询优化和数据库表优化,帮助提升数据库性能。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
MySQL慢查询优化、索引优化、以及表等优化详解
|
10月前
|
SQL 关系型数据库 MySQL
数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog
《数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog》介绍了如何利用MySQL的二进制日志(Binlog)恢复误删除的数据。主要内容包括: 1. **启用二进制日志**:在`my.cnf`中配置`log-bin`并重启MySQL服务。 2. **查看二进制日志文件**:使用`SHOW VARIABLES LIKE 'log_%';`和`SHOW MASTER STATUS;`命令获取当前日志文件及位置。 3. **创建数据备份**:确保在恢复前已有备份,以防意外。 4. **导出二进制日志为SQL语句**:使用`mysqlbinlog`
573 2

推荐镜像

更多