MySQL 数据访问与查询优化:提升性能的实战策略和解耦优化技巧(一)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
全局流量管理 GTM,标准版 1个月
简介: MySQL 数据访问与查询优化:提升性能的实战策略和解耦优化技巧

前言

博文:构建优化之城:MySQL 数据建模、数据类型优化与索引常识全面解析

博文:MySQL 数据结构优化与索引细节解析:打造高效数据库的优化秘笈

简要说明了一些索引的基本知识及分类、技术名词,索引的数据结构、生产如何调优、调优的细节如何处理、如何避免生产慢 SQL,该篇博文会继续从以下几点来对 MySQL 调优部分进行分析:

  1. 为什么会查询慢?
  2. 优化数据访问、执行过程
  3. 大数据量查询优化
  4. 海量数据解耦优化处理

为什么会查询慢?

查询慢的原因有很多种,不一定是数据库设计不合理或索引使用不正当或 SQL 编写有问题等等

一般情况下,表里数据量不是特别多时,其实改变 SQL 语句的差别不大,但是当表中的数据量形成了一定规模数以后,查询慢的情况就会经常发生,以下是查询慢可能会发生的原因

  1. 网络:网络对 MySQL 影响非常大,在进行数据访问时,很多情况下都是在数据中心进行存放的;当需要读取跨异地的数据时,网络将会成为一个至关重要的影响点,特别是在分布式环境中,影响更为突出,因此要尽量减少网络对于数据访问的影响;比如:数据库服务节点与部署服务的节点尽量保持在同一个地域内,不要一个在海外,一个在国内等
  2. CPU:在执行不同的操作时,通过 CPU 轮转来完成各个任务的执行,所以时间片的分配会在一定程度下影响数据库的查询效率,尽量减少在业务代码中循环获取数据库连接读取数据
  3. IO:在进行 SQL 调优,最关键的点其实就是要优化 IO 成本量
  4. 上下文切换:N 多个线程在执行,某个进程的时间片用完以后,就会切换到另外一个进程去执行,切换时会比较浪费时间
  5. 系统调用:操作系统内核中的核心概念,涉及到 IO 模型,一般是由具体的 IO 框架来控制的,无法进行优化
  6. 生成统计信息:MySQL 中 > show profiles、performance schema 这些统计信息的生成,都需要占用一定的资源,此时也会影响数据的查询
  7. 锁等待时间:在并发场景中,锁是非常麻烦的一个问题;在 MySQL 有表锁、行锁,锁机制是与存储引擎相关的;经常用的存储引擎是 MyISAM、InnoDB,MyISAM 里面有两种锁:共享读锁、独占写锁,锁名称不同,内部实现也不一样;MyISAM 在加锁时只会锁别; InnoDB 可以锁表也可以锁行,但是需要注意的是,InnoDB 锁的是索引,若没有对应的索引可以加锁的话,那么就会由行锁退化为表锁

优化数据访问

查询数据低效原因

从数据方面看,查询性能低下的主要原因:访问的数据太多,某些查询不可避免的需要筛选大量的数据,也就是说,这是 IO 问题,因为我们知道大部分的数据都是持久化到磁盘中的,有时候就算加了索引也不一定可以用到索引

确认应用程序是否在检索大量超过需要的数据(数据量超过 30% 会触发 filesort)或者说你通过索引已经过滤了一部分数据,但过滤后的这部分数据你又因为排序的原因导致这部分数据又造成了 filesort,可以通过观察执行计划得知,如下索引:

mysql> select count(*) from rental;
+----------+
| count(*) |
+----------+
|    16044 |
+----------+
1 row in set (0.00 sec)
mysql> explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by rental_date,inventory_id;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | rental | NULL       | ALL  | rental_date   | NULL | NULL    | NULL | 16008 |    50.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+

如上,可以得出检索了数据行 16008 记录,过滤的行数太多了,完全可以通过改变条件来保证检索的数据行减少,如下:

mysql> explain select rental_id,staff_id from rental where rental_date>'2006-05-25' order by rental_date,inventory_id;\
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | rental | NULL       | range | rental_date   | rental_date | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+

如上可以看到只是改变了简单的查询条件,查询的行数就改变了很多,所以有时候就可以通过适当的调整来减少数据访问的行数;比如:当用户表数据量比较多时,默认我只展示最近一个月的用户出来

是否请求了不需要的数据

查询不需要的记录

在工作中经常会误以为 MySQL 只会返回需要的数据,实际上 MySQL 会先返回所有的结果再进行计算,在日常的开发习惯中,经常会先用 select 语句查询大量的结果集,然后获取前面的 N 行数据后关闭结果集,优化方式其实在查询后面添加 LIMIT > 限制结果集数量

多表关联时返回全部列字段

查询时尽量不要使用 SELECT *,用到什么列就查什么列;多表关联尤其不要用 *,建议表民后追加别名,也就是说你有两张以上的表进行关联时,不要写 *

重复查询相同的字段值

若需要不断重复执行相同的查询,且每次返回相同的数据,基于这样的应用场景,可以将这部分数据缓存起来,能够提高查询效率(Redis、Spring Session,当然本地缓存不是很推荐,数据量大或占用的空间大小多时对 JVM 年轻代、老年代会带来负担

优化执行过程

查询缓存

在解析查询语句之前,若查询缓存是开启的,那么 MySQL 会优先检查这个查询是否命中查询缓存中的数据,若查询恰好命中了查询缓存,那么在返回结果之前会先检查用户权限,若权限没有问题,MySQL 会跳过所有的阶段,就会直接从缓存中拿到结果后就返回给客户端;虽然查询缓存在 MySQL 8 里面给去掉了,但是在 5.x 还是有的,对于某些不经常改变的字典表数据完全可以使用查询缓存来加快查询的访问效率

查询优化处理

MySQL 查询完缓存之后会经过以下步骤 > 解析 SQL、预处理、优化 SQL 执行计划,在这其中某个步骤出现了问题,都可能会终止查询操作,两块:语法解析器、查询优化器

语法解析器

所谓语法解析器 > MySQL 通过关键字将 SQL 语句进行解析,生成一颗解析树,解析器会将使用 MySQL 语法规则验证后解析查询,例如:验证使用了错误的关键字或者顺序是否正确等等,预处理器会进一步检查解析树是否合法;例如:表名、列名是否存在,是否存在歧义,还会验证权限等等;AST(抽象语法树)没必要自己解析的,可以使用 Apache Calcite 开源项目组件进行解析,Calcite > 一款开源 SQL 解析工具,可以将各种 SQL 语句解析成抽象语法树,之后再通过 AST 就可以把 SQL 中所需要的表达式算法和关系体现在具体的代码中

当语法没有问题后,相应的是由优化器将其转换为执行计划,一条查询语句可以使用非常多的执行方式,最后都可以得到对应的结果,但是不同的执行方式带来的效率是不同的,优化器最主要的目的就是要选择最有效的执行计划

查询优化器

MySQL 使用的是基于成本的优化器

在优化时会尝试预测一个查询使用某种查询计划时的成本,并选择其中成本最小的一个(使用不到正确的索引与成本也有很大关系),如下:

mysql> select count(*) from film_actor;
+----------+
| count(*) |
+----------+
|     5462 |
+----------+
1 row in set (0.00 sec)
mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 1104.399000 |
+-----------------+-------------+

通过以上查询可以看出,执行了一个 COUNT 操作,一共查询出了 5462 条记录,耗费的成本是 1104.3990,show status like 'last_query_cost' 这是在当前会话中返回最后一条 SQL 语句执行的耗费时间成本,若换另外一条语句来执行,如下:

mysql> select count(*) from film;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.01 sec)
mysql> show status like 'last_query_cost';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| Last_query_cost | 211.999000 |
+-----------------+------------+

不同的 SQL 语句在执行时所耗费的时间成本是不同的,当得到这个结果值之后,可以适当调整我们的 SQL 语句,以达到最优的方式

在实际业务开发中,COUNT(1) 计数的方式尽量少用,除非是我们那些需要逻辑分页的查询场景下,其他的业务下,我们可以将 O(N) 计数的方式变为 O(1),也就是说我们提前把这个数计算好,而不是每次要查询时在去单独统计这个数出来

以上时间成本的参数值即 > IO_COST、CPU_COST 开销总和,它通常也是评价 SQL 查询执行效率的一个常用指标

  1. 它作为比较各个查询之间开销依据
  2. 它只能检测比较简单的查询开销,对于包含子查询、union 查询是测试不出来的
  3. 当我们执行查询时,MySQL 会自动生成一个执行计划,也就是 Query Plan,通常会有很多种不同实现方式,它会选择最低成本的那个,而这个 COST 值就是开销最低的那一个

在很多情况下,MySQL 会选择错误的执行计划,产生此问题的根本原因在于以下几点:

  1. 统计信息不准确,InnoDB 因为其 MVCC(多版本并发控制) 架构,并不能维护一个数据表行数的精确统计信息,在某些情况下进行了大量的增删改查操作之后,可能会导致统计的信息不精确
mysql> show index from film_actor;
+------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| film_actor |          0 | PRIMARY        |            1 | actor_id    | A         |         200 |     NULL | NULL   |      | BTREE      |         |               |
| film_actor |          0 | PRIMARY        |            2 | film_id     | A         |        5462 |     NULL | NULL   |      | BTREE      |         |               |
| film_actor |          1 | idx_fk_film_id |            1 | film_id     | A         |         997 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

如上表示的 film_actor 表中的索引统计信息,当进行大量的查询之后,可能会造成基数 Cardinality 统计不精确,那么就会有可能产生错误的选择


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
vnjohn
+关注
目录
打赏
0
0
0
0
241
分享
相关文章
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
MySQL底层概述—8.JOIN排序索引优化
MySQL底层概述—7.优化原则及慢查询
本文主要介绍了:Explain概述、Explain详解、索引优化数据准备、索引优化原则详解、慢查询设置与测试、慢查询SQL优化思路
MySQL底层概述—7.优化原则及慢查询
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化,旨在提升数据库性能。主要内容包括: 1. 内存相关参数优化:缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化:日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化: 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
MySQL底层概述—5.InnoDB参数优化
MySQL底层概述—4.InnoDB数据文件
本文介绍了InnoDB表空间文件结构及其组成部分,包括表空间、段、区、页和行。表空间是最高逻辑层,包含多个段;段由若干个区组成,每个区包含64个连续的页,页用于存储多条行记录。文章还详细解析了Page结构,分为通用部分(文件头与文件尾)、数据记录部分和页目录部分。此外,文中探讨了行记录格式,包括四种行格式(Redundant、Compact、Dynamic和Compressed),重点介绍了Compact行记录格式及其溢出机制。最后,文章解释了不同行格式的特点及应用场景,帮助理解InnoDB存储引擎的工作原理。
MySQL底层概述—4.InnoDB数据文件
MySQL和SQLSugar百万条数据查询分页优化
在面对百万条数据的查询时,优化MySQL和SQLSugar的分页性能是非常重要的。通过合理使用索引、调整查询语句、使用缓存以及采用高效的分页策略,可以显著提高查询效率。本文介绍的技巧和方法,可以为开发人员在数据处理和查询优化中提供有效的指导,提升系统的性能和用户体验。掌握这些技巧后,您可以在处理海量数据时更加游刃有余。
24 9
MySQL进阶突击系列(09)数据磁盘存储模型 | 一行数据怎么存?
文中详细介绍了MySQL数据库中一行数据在磁盘上的存储机制,包括表空间、段、区、页和行的具体结构,以及如何设计和优化行数据存储以提高性能。
【YashanDB 知识库】MySQL 迁移至崖山 char 类型数据自动补空格问题
问题分类】功能使用 【关键字】char,char(1) 【问题描述】MySQL 迁移至崖山环境,字段类型源端和目标端都为 char(2),但应用存储的数据为'0'、'1',此时崖山查询该表字段时会自动补充空格 【问题原因分析】mysql 有 sql_mode 控制,检查是否启用了 PAD_CHAR_TO_FULL_LENGTH SQL 模式。如果启用了这个模式,MySQL 才会保留 CHAR 类型字段的尾随空格,默认没有启动。 #查看sql_mode mysql> SHOW VARIABLES LIKE 'sql_mode'; 【解决/规避方法】与应用确认存储的数据,正确定义数据
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
102 42
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
56 25
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等