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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL 数据访问与查询优化:提升性能的实战策略和解耦优化技巧(二)

2. 执行计划成本不等价于实际执行的成本

有时候某些执行计划需要读取更多的页面,但是它的成本却更小,因为这些页面都是顺序读或这些页面都已经在内存中时,那么它的访问成本将会很小,MySQL 层面并不知道哪些页面在内存中,哪些在磁盘,所以查询时执行过程到底需要多少次 IO 是无法得知的

  1. MySQL 最优可能与你想的不一样

MySQL 优化是基于成本模型的优化,但是有可能不是最快的优化;A join B join C,一定是先读 A 再读 B 再读 C 嘛?或者先读 C 再读 B 再读 A 嘛?这个过程是没办法预估的,它是根据优化器内部的一个规则来选择先读哪个再读哪个表的

  1. MySQL 不会考虑不受其控制的操作成本

在计算成本值时,并不会考虑有多少的并发情况,因为 MySQL 并不知道,也无法预估,只能基于单条查询来做预估,所以这个值不是那么准确,但大部分情况下是没有问题的

  1. MySQL 不会考虑不受其控制的操作成本

当执行存储过程或用户自定义函数时,因为不是 MySQL 提供的默认功能,所以 MySQL 中可能不会有对应的统计信息,因此不会考虑此类操作的成本

优化器的优化策略

  1. 静态优化表示直接对解析树进行分析后完成优化
  2. 动态优化表示与查询的上下文(查询缓存)有关,也可能跟取值、索引对应的行数有关
  3. MySQL 对查询的静态优化只需要一次,但对动态优化在每次执行时都需要重新评估,举例说明:比如 A join B,A 表在查询缓存里,B 表不在,这里就要做个判断,A 表在内存或磁盘完全是两种不一样的处理方式,所以查询上下文指的是在一个会话里,之前的查询操作对当前的 SQL 语句产生的影响

优化器的优化类型

  1. 重新定义关联表的顺序

数据表的关联并不总是按照查询中的指定顺序进行的,决定关联顺序是优化器很重要的功能,比如:A join B join C,看起来是先读 A 再读 B 再读 C,实际上可能不是这样的,可能会先读 C 再读 B 最后再读 A,它里面有一个优化机制在作判断

  1. 外连接转换为内连接,内连接的效率要高于外连接,原因:在于外连接在操作步骤上要比内连接多出来一步
  2. 使用等价变换规则,MySQL 可以使用一些等价的变化来简化、规划表达式

例如:条件中包含 a != 4 这个判断,可以替换成 a > 4 & a < 4 这个操作;在实际工作时,能用一个表达式解决绝不要用两个表达式,方便优化器进行优化.

  1. 优化 COUNT、MAX、MIN

索引、列是否可以为空,通常可以帮助 MySQL 来优化这类表达式,例如:要找到某一列的最小值,只需要查询索引的最左端记录即可,无须进行全文扫描比较

  1. 预估并转化为常数表达式

当 MySQL 检测到一个表达式可以转换为常数时,就会一直把表达式作为常数进行处理

mysql> explain select film.film_id,film_actor.actor_id from film inner join film_actor using(film_id) where film.film_id = 1;
+----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys  | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | film       | NULL       | const | PRIMARY        | PRIMARY        | 2       | const |    1 |   100.00 | Using index |
|  1 | SIMPLE      | film_actor | NULL       | ref   | idx_fk_film_id | idx_fk_film_id | 2       | const |   10 |   100.00 | Using index |
+----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+

type = const,const 表示常数,执行效率是比较高的,尽可能把我们 SQL 中的表达式转化成常量值

  1. 索引覆盖扫描:当索引中的列包含在所有查询中需要使用到的列时,可以使用覆盖索引
  2. 子查询优化:MySQL 在某些情况下,可以将子查询转换为一种效率更高的形式,从而减少多个查询、多次对数据进行访问,例如:经常查询的数据放入到缓存中
  3. 等值传播
    若两个列的值通过等值关联,那么 MySQL 能够把其中一个列的 where 条件传递到另外一个
explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500;

使用了 film_id 字段进行等值关联,film_id 列不仅适用于 film 表同时也适用于 film_actor 表

explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500 and film_actor.film_id > 500;

关联查询

MySQL 关联查询很重要,但是关联查询执行的策略比较简单;MySQL 对任何关联都会执行嵌套循环的关联操作,即 MySQL 先在一张表中循环取出单条数据,然后再嵌套到下一表中寻找匹配的行,直到找到所有表中匹配的行为止

根据各个表匹配的行,返回查询中需要的各个列,MySQL 会尝试在最后一个关联表中找到所有匹配的行,若最后一个关联表无法找到更多的行之后,MySQL 返回到上一层次的关联表,看是否能够找到更多匹配的记录,以此类推迭代执行

整体的思路如此,但是要注意实际的执行过程中有多种形式

通过案例来演示,不同顺序执行方式对查询性能的影响

mysql> explain select film.film_id,film.title,film.release_year,actor.actor_id,actor.first_name,actor.last_name from film inner join film_actor using(film_id) inner join actor using(actor_id);
+----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys          | key     | key_len | ref                       | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------------+------+----------+-------------+
|  1 | SIMPLE      | actor      | NULL       | ALL    | PRIMARY                | NULL    | NULL    | NULL                      |  200 |   100.00 | NULL        |
|  1 | SIMPLE      | film_actor | NULL       | ref    | PRIMARY,idx_fk_film_id | PRIMARY | 2       | sakila.actor.actor_id     |   27 |   100.00 | Using index |
|  1 | SIMPLE      | film       | NULL       | eq_ref | PRIMARY                | PRIMARY | 2       | sakila.film_actor.film_id |    1 |   100.00 | NULL        |
+----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------------+------+----------+-------------+

执行完以后会发现执行顺序:actor、film_actor、film,查看所耗费的成本如下:

mysql> show status like 'last_query_cost'; 
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 7892.932509 |
+-----------------+-------------+

若想按照自己预想的规定顺序执行,使用 straight_join 关键字,如下:

mysql> explain select straight_join film.film_id,film.title,film.release_year,actor.actor_id,actor.first_name,actor.last_name from film inner join film_actor using(film_id) inner join actor using(actor_id);
+----+-------------+------------+------------+--------+------------------------+----------------+---------+----------------------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys          | key            | key_len | ref                        | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+------------------------+----------------+---------+----------------------------+------+----------+-------------+
|  1 | SIMPLE      | film       | NULL       | ALL    | PRIMARY                | NULL           | NULL    | NULL                       | 1000 |   100.00 | NULL        |
|  1 | SIMPLE      | film_actor | NULL       | ref    | PRIMARY,idx_fk_film_id | idx_fk_film_id | 2       | sakila.film.film_id        |    5 |   100.00 | Using index |
|  1 | SIMPLE      | actor      | NULL       | eq_ref | PRIMARY                | PRIMARY        | 2       | sakila.film_actor.actor_id |    1 |   100.00 | NULL        |
+----+-------------+------------+------------+--------+------------------------+----------------+---------+----------------------------+------+----------+-------------+

表加载顺序:film、film_actor、actor,查看其耗费的成本

mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 8885.087226 |
+-----------------+-------------+

通过这两种对比,可以看出 MySQL 选择了成本更低的方式来执行 SQL 语句

排序优化

无论如何排序,都是一个成本很高的操作,从性能的角度出发,应该尽可能的避免排序或尽可能避免对大量数据排序

推荐使用 索引排序,但是当不能使用索引时,MySQL 就需要自己进行排序,若数据量小则在内存中进行,若数据量大就需要使用磁盘,MySQL 称之为 filesort

若需排序的数量小于排序缓冲区(show variables like '%sort_buffer_size%')MySQL 使用内存进行快速排序操作,若内存不够排序,MySQL 就会将树分块,对每个独立块使用快速排序进行排序,并将各个块排序结果存放在磁盘上,然后对每个排好序的块进行合并,最后返回结果

mysql> show variables like '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size        | 262144  |
+-------------------------+---------+

在进行排序时还会涉及到两种排序算法 > 两次传输排序、单次传输排序

两次传输排序

第一次数据读取是将需要排序的字段读取出来,然后进行排序;第二次是将排好序的结果按照所需去读取数据行

这种方式效率比较低,原因:第二次读取数据时因为已经排好序,需要去读取所有记录,而此时更多的是随机 IO,读取数据成本会比较高

两次传输的优势在于,在排序时存储尽可能少的数据,让排序缓冲区可以尽可能的容纳行数来进行排序操作

单次传输排序

先读取查询需要的所有列,然后再根据给定列进行排序,最后直接返回排序后的结果,此方式只需要一次顺序 IO 读取所有的数据,无须任何的随机 IO,问题在于查询列特别多时,会占用大量的存储空间,无法存储大量的数据

当需要排序的列总大小超过 max_length_for_sort_data 参数定义的字节数,MySQL 会选择两次传输排序,否则使用单次传输排序,Of Course,用户可以设置此参数的值来选择排序的方式

mysql> show variables like '%max_length_for_sort_data%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024  |
+--------------------------+-------+

大数据量查询优化

基于大数据量查询优化场景下,从而优化特定类型的查询

优化 COUNT 查询

在进行 COUNT 计数查询时需要注意以下几点:

  1. 总有人认为 MyISAM 存储引擎中 COUNT 函数效率比较快,这是有前提条件的,只有没有任何 where 条件的 COUNT(*) 才是比较快的;MyISAM 有一个变量来记录插入的行数,所以会比较快,但是一旦携带了 WHERE 条件去统计数量,变量记录的值就不准确了,仍然还是要和普通查询一样,挨个遍历
  2. 使用近似值,在某些应用场景中,不需要完全精确的值,可以参考使用近似值来代替,比如:使用 Explain 来获取近似值或采用计算近似值的算法 > HyperLogLog
  3. 一般情况下,COUNT 需要扫描大量的行才能获取精确的数据,其实是更复杂的优化,在实际操作时可以考虑使用索引覆盖扫描增加汇总表提前统计好数量引入外部缓存系统;插入数据时进行汇总表、外部缓存的累加操作,等需要总的记录数时直接取值就可以了,不需要从数据表中进行统计,但是一定要借助外部的一些系统

优化关联查询

首先要确保 on、using 子句中的关联列有索引,在创建索引时要考虑到关联的顺序

业务系统中,一般都是关联主键、外键没有什么问题;特殊情况下,会用普通列作关联查询,这时就可能会出现问题,所以最好还是使用索引列;使用索引列的效率是比较高的,既然创建了索引就一定要用,比如 > 建了主键还不用主键来优化就没什么意义了

确保任何 group by、order by 中表达式只涉及到一个表中的列(关联表查询时,只使用一个表中的字段进行排序、分组)这样 MySQL 才有可能使用索引来优化这个过程;group by、order by 能使用索引还是要用,这样效率是比较高的,不使用的话就会有问题,比如:filesort 磁盘轮转排序效率低缓

优化子查询

子查询优化,建议是尽可能使用关联查询进行代替,不要使用对应的子查询;在某些应用场景里,可以用子查询也可以用 JOIN 方式,不推荐使用子查询;每次 SELECT 时会得到一个结果,子查询的记过会放到临时表里,临时表就会涉及到 IO,与其这样还不如直接使用 JOIN 关联数据,效率可能还会更高一些



相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
6天前
|
监控 关系型数据库 MySQL
MySQL自增ID耗尽应对策略:技术解决方案全解析
在数据库管理中,MySQL的自增ID(AUTO_INCREMENT)属性为表中的每一行提供了一个唯一的标识符。然而,当自增ID达到其最大值时,如何处理这一情况成为了数据库管理员和开发者必须面对的问题。本文将探讨MySQL自增ID耗尽的原因、影响以及有效的应对策略。
21 3
|
7天前
|
监控 关系型数据库 MySQL
Linux环境下MySQL数据库自动定时备份策略
在Linux环境下,MySQL数据库的自动定时备份是确保数据安全和可靠性的重要措施。通过设置定时任务,我们可以每天自动执行数据库备份,从而减少人为错误和提高数据恢复的效率。本文将详细介绍如何在Linux下实现MySQL数据库的自动定时备份。
20 3
|
6天前
|
存储 监控 关系型数据库
MySQL自增ID耗尽解决方案:应对策略与实践技巧
在MySQL数据库中,自增ID(AUTO_INCREMENT)是一种特殊的属性,用于自动为新插入的行生成唯一的标识符。然而,当自增ID达到其最大值时,会发生什么?又该如何解决?本文将探讨MySQL自增ID耗尽的问题,并提供一些实用的解决方案。
13 1
|
14天前
|
缓存 监控 关系型数据库
如何根据监控结果调整 MySQL 数据库的参数以提高性能?
【10月更文挑战第28天】根据MySQL数据库的监控结果来调整参数以提高性能,需要综合考虑多个方面的因素
53 1
|
14天前
|
监控 关系型数据库 MySQL
如何监控和诊断 MySQL 数据库的性能问题?
【10月更文挑战第28天】监控和诊断MySQL数据库的性能问题是确保数据库高效稳定运行的关键
30 1
|
14天前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
37 1
|
8天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
22 4
|
6天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
19 1
|
1月前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
61 3
Mysql(4)—数据库索引
|
15天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
77 1