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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
云数据库 RDS PostgreSQL,高可用系列 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 关联数据,效率可能还会更高一些



相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
4月前
|
缓存 NoSQL 关系型数据库
美团面试:MySQL有1000w数据,redis只存20w的数据,如何做 缓存 设计?
美团面试:MySQL有1000w数据,redis只存20w的数据,如何做 缓存 设计?
美团面试:MySQL有1000w数据,redis只存20w的数据,如何做 缓存 设计?
|
2月前
|
SQL 人工智能 关系型数据库
如何实现MySQL百万级数据的查询?
本文探讨了在MySQL中对百万级数据进行排序分页查询的优化策略。面对五百万条数据,传统的浅分页和深分页查询效率较低,尤其深分页因偏移量大导致性能显著下降。通过为排序字段添加索引、使用联合索引、手动回表等方法,有效提升了查询速度。最终建议根据业务需求选择合适方案:浅分页可加单列索引,深分页推荐联合索引或子查询优化,同时结合前端传递最后一条数据ID的方式实现高效翻页。
116 0
|
3月前
|
SQL 缓存 关系型数据库
MySQL 慢查询是怎样优化的
本文深入解析了MySQL查询速度变慢的原因及优化策略,涵盖查询缓存、执行流程、SQL优化、执行计划分析(如EXPLAIN)、查询状态查看等内容,帮助开发者快速定位并解决慢查询问题。
127 0
|
13天前
|
存储 关系型数据库 MySQL
在CentOS 8.x上安装Percona Xtrabackup工具备份MySQL数据步骤。
以上就是在CentOS8.x上通过Perconaxtabbackup工具对Mysql进行高效率、高可靠性、无锁定影响地实现在线快速全量及增加式数据库资料保存与恢复流程。通过以上流程可以有效地将Mysql相关资料按需求完成定期或不定期地保存与灾难恢复需求。
83 10
|
16天前
|
缓存 关系型数据库 MySQL
降低MySQL高CPU使用率的优化策略。
通过上述方法不断地迭代改进,在实际操作中需要根据具体场景做出相对合理判断。每一步改进都需谨慎评估其变动可能导致其他方面问题,在做任何变动前建议先在测试环境验证其效果后再部署到生产环境中去。
58 6
|
2月前
|
存储 SQL 关系型数据库
MySQL 核心知识与索引优化全解析
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。 索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。 索引优化部分,列出了索引创建的六大原则
|
2月前
|
SQL 存储 缓存
MySQL 如何高效可靠处理持久化数据
本文详细解析了 MySQL 的 SQL 执行流程、crash-safe 机制及性能优化策略。内容涵盖连接器、分析器、优化器、执行器与存储引擎的工作原理,深入探讨 redolog 与 binlog 的两阶段提交机制,并分析日志策略、组提交、脏页刷盘等关键性能优化手段,帮助提升数据库稳定性与执行效率。
|
2月前
|
存储 SQL 关系型数据库
MySQL 动态分区管理:自动化与优化实践
本文介绍了如何利用 MySQL 的存储过程与事件调度器实现动态分区管理,自动化应对数据增长,提升查询性能与数据管理效率,并详细解析了分区创建、冲突避免及实际应用中的关键注意事项。
105 0
|
3月前
|
人工智能 运维 关系型数据库
数据库运维:mysql 数据库迁移方法-mysqldump
本文介绍了MySQL数据库迁移的方法与技巧,重点探讨了数据量大小对迁移方式的影响。对于10GB以下的小型数据库,推荐使用mysqldump进行逻辑导出和source导入;10GB以上可考虑mydumper与myloader工具;100GB以上则建议物理迁移。文中还提供了统计数据库及表空间大小的SQL语句,并讲解了如何使用mysqldump导出存储过程、函数和数据结构。通过结合实际应用场景选择合适的工具与方法,可实现高效的数据迁移。
578 1
|
4月前
|
负载均衡 算法 关系型数据库
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
本文聚焦 MySQL 集群架构中的负载均衡算法,阐述其重要性。详细介绍轮询、加权轮询、最少连接、加权最少连接、随机、源地址哈希等常用算法,分析各自优缺点及适用场景。并提供 Java 语言代码实现示例,助力直观理解。文章结构清晰,语言通俗易懂,对理解和应用负载均衡算法具有实用价值和参考价值。
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!

推荐镜像

更多