三高Mysql - Mysql索引和查询优化讲解(偏理论部分)(下)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 三高Mysql - Mysql索引和查询优化讲解(偏理论部分)(下)

查询优化排查


查询优化的排查意味着我们需要先了解Mysql的各个组件在各步骤中做了哪些事情,下面这张图来自于《高性能Mysql》,对于一次客户端的请求,大致分为下面的流程:


网络异常,图片无法展示
|


  1. 客户端发送请求
  2. 服务器查询执行缓存
  • 不重要,8.0之后已经删除
  1. 服务端进行SQL解析和预处理
  • 权限检查
  • 词法解析
  • 语法树
  1. 优化器生成执行计划
  • 优化器存在的问题?
  • 优化器如何工作?
  1. 根据执行计划调用存储引擎的APi接口执行查询
  2. 结果返回客户端

对于关系型的数据库来说,核心部分在于查询优化器和执行计划的部分,因为不管我们如何编写SQL语句,如果没有强大的优化器和执行计划那么一切都是空谈,所以本部分的重点也会围绕优化器进行讲解,在此之前我们先看看其他组件的工作:

首先查询缓存不需要过多解释,他的作用是当用户重复执行一个查询的时候会内部对于结果进行缓存,但是一旦用户修改查询条件,缓存就失效了,在早期的互联网环境中这种处理很不错,可以减少磁盘IO和CPU的压力,但是到了现在的环境下显然不适合,所以8.0删除也是可以理解的。

接着是解析器,解析器这部分主要工作是通过解析语法形成解析树对于语句进行预处理,预处理可以类比左我们编译器把我们写的编程语句“翻译”为机器代码的过程,让下一步的优化器可以认识这颗解析树去进行解析,

如果想要了解SQL解析优化的底层过程,可以从这篇文章入手:

SQL解析在美团的应用 - 美团技术团队 (meituan.com)

在上面的博客中提到了一个DBA必须掌握的工具pt-query-digest,分析慢查询日志,下面这个文章中提供了一个实际的案例来排查和优化,案例较为简单适合刚接触这个工具的人进行学习和思考,这里一并列出来了。

使用 pt-query-digest 分析 RDS MySQL 慢查询日志 | 亚马逊AWS官方博客 (amazon.com)

SQL解析部分笔记:

词法分析:核心代码在sql/sql_lex.c文件中的,MySQLLex→lex_one_Token

MySQL语法分析树生成过程:全部的源码在sql/sql_yacc.yy中,在MySQL5.6中有17K行左右代码

最核心的结构是SELECT_LEX,其定义在sql/sql_lex.h

下面我们来深入看看优化器的部分工作内容以及Mysql优化历史:

由于讲述优化器的内容较少,这里直接总结《高性能Mysql》的内容,优化器也不需要研究和记忆,因为随着版本的迭代不断更新优化器会不断调整,一切要以真实实验为准:


1. 子查询关联

下面的查询在通常情况下我们会认为先进行子查询,然后通过for循环扫描film表进行匹配操作,然后从explain的结果中可以看到这里的查询线进行了全表扫描,然后通过关联索引进行第二层的for循环查询,这样的写法类似exists


explain select * from sakila.film where film_id in (select film_id from film_actor where actor_id)
-- 1  SIMPLE  film    ALL PRIMARY       1000  100.00  
-- 1  SIMPLE  film_actor    ref idx_fk_film_id  idx_fk_film_id  2 sakila.film.film_id 5 90.00 Using where; Using index; FirstMatch(film)


优化这个子查询的方式使用关联查询替代子查询,但是需要注意这里存在where条件才会走索引,否则和上面的结果没有区别:


explain select film.* from sakila.film film  join film_actor actor using (film_id) where actor.actor_id = 1


另一种是使用exists的方式进行关联匹配。


explain select * from film where exists (select * from film_actor actor where actor.film_id =  film.film_id and actor.actor_id = 1);


可以看到哪怕到了5.8的版本,Mysql的子查询优化既然没有特别大的改进,所以通常情况下如果不确定in查询的内容大小,建议用exists或者join进行查询,另外也不要相信什么in查询就一定慢点说法,在不同的mysql优化器版本中可能会有不同的效果。


2. union查询


虽然多数情况下我们会用union替换or,但是更多的情况是应该尽量避免使用union,因为union查询会产生临时表和中间结果集容易导致优化索引失效,需要注意的是 union会触发内部的排序动作,也就是说union会等价于order by的排序,如果数据不是强烈要求不能重复,那么更建议使用union all,对于优化器来说这样工作更加简单,直接把两个结果集凑在一起就行,也不会进行排序。

union查询能不用就不用,除非是用来代替or查询的时候酌情考虑是否有必要使用。

最后注意union的产生排序不受控制的,可能会出现意料之外的结果。


3. 并行查询优化


并行查询优化在8.0中终于有了实现,可以根据参数:innodb_parallel_read_threads =并行数来验证。

由于个人是M1的CPU,读者可以根据自己的实际情况进行实验。


set local innodb_parallel_read_threads = 1;
select count(*) from payment;
set local innodb_parallel_read_threads = 6;
select count(*) from payment;


从执行结果可以看到仅仅是1万多条数据的count(*)查询就有明显直观的差距:


网络异常,图片无法展示
|


4. 哈希关联


官方文档的介绍地址:Mysql官方文档哈希关联

在MySQL 8.0.18中Mysql终于增加了哈希关联的功能。在此之前的版本中,Mysql的优化器通常只支持for循环嵌套关联,曲线救国的方法是建立一个哈希索引或者使用Memory存储引擎,而新版本提供的哈希关联则提供了一种新的对关联方式,哈希关联的方式如下:

把一张小表数据存储到内存中的哈希表里,通过匹配大表中的数据计算哈希值,并把符合条件的数据从内存中返回客户端。

对于Mysql的哈希关联,我们直接使用官方的例子:


CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
CREATE TABLE t3 (c1 INT, c2 INT);
EXPLAIN
     SELECT * FROM t1
         JOIN t2 ON t1.c1=t2.c1;
-- Using where; Using join buffer (hash join)


除开等值查询以外,Mysql的8.0.20之后提供了更多的支持,比如在 MySQL 8.0.20 及更高版本中,连接不再需要包含至少一个等连接条件才能使用哈希连接,除此之外它还包括下面的内容:


-- 8.0.20 支持范围查询哈希关联
EXPLAIN  SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1;
-- 8.0.20 支持 in关联
EXPLAIN  SELECT * FROM t1 
        WHERE t1.c1 IN (SELECT t2.c2 FROM t2);
-- 8.0.20 支持 not exists 关联
EXPLAIN  SELECT * FROM t2 
         WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.c1 = t2.c2);
-- 8.0.20 支持 左右外部连接
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1;


注意8.0.18版本的哈希关联仅仅支持join查询,对于可能会带来笛卡尔积的左连和右连接查询是不支持的。但是在后续的版本中提供了更多查询条件支持

另外,8.0.20版本之前想要查看是否使用hash join,需要结合 format=tree 选项。


网络异常,图片无法展示
|


最终Mysql在8.0.18版本中曾经提供过开关哈希索引和设置优化器提示optimizer_switch等参数来判定是否给予hash join的提示,真是闲的蛋疼(官方自己也这么认为)所以在8.0.19立马就把这些参数给废弃。

注意哈希连接不是没有限制的,了解哈希关联的流程就会发现如果哈希表过大,会导致整个哈希关联过程在磁盘中完成其速度可想而知,所以官方提供了下面的建议:

  • 增加join_buffer_size,也就是增加哈希关联的哈希表缓存大小,防止进入磁盘关联。
  • 增加open_files_limit数量,这个参数什么意思这里就不介绍了,意义是增加这个参数可以增加关联的时候关联次数。

吐槽:说句心里话自Mysql被Oracle收购之后,越来越商业化的同时进步也越来越小,in查询优化这一点其实在很多开源库甚至Mysql的原作者给解决了,但是Mysql到了8.0依然和多年前的《高性能Mysql》结果没有差别。哎。。。。。

Mysql数据库的发展也告诉我们时刻保持开放的心态,吸取教训正视不足和改进,才不会被时代逐渐淘汰。


5. 松散索引


松散索引在Mysql5.6之后已经支持,松散索引简单理解就是在进行多列索引扫描的时候,即使次索引不是有序的,但是跳过索引是有序的,也可以走索引来快速匹配数据。


松散索引的优化细节放到了下半部分的文章,这里简单讲述一下大致的工作原理。


网络异常,图片无法展示
|


  1. 查询同时更新数据

在Postgresql中,支持下面的语法:


update tbl_info
set name = tmp.name
from 
(select name from tbl_user where name ='xxx')
tmp
[where ....]
-- 比如下面的写法:
UPDATE `sakila`.`actor` SET `first_name` = 'PENELOPE'
from 
(select address,address_id from address where address_id = 1) tmp
 WHERE `actor_id` = 1 and actor.actor_id = tmp.address_id;


但是很可惜这种语法在Mysql是没有办法实现也是不支持的,哪怕到了8.0.26依然没有支持,这和Mysql的优化器设计有着本质的关系。

  1. 优化器提示设置

优化器提示没有多少意义,这里直接略过了。


  1. 最大值和最小值优化

从实际的情况来看Mysql最大值和最小值这两个函数使用并不是很多所以不再进行介绍了,另外无论什么样的数据库都不是很建议频繁使用函数,而是该用业务+简单SQL实现高效索引优化。

其他慢查询优化

对于慢查询的优化我们需要清楚优化是分为几种类别的,在Mysql中优化策略分为动态优化静态优化:静态优化主要为优化更好的写法,比如常数的排序和一些固定的优化策略等,这些动作通常在一次优化过程中就可以完成。而动态优化策略要复杂很多,可能会在执行的过程中优化,有可能在执行过后重新评估执行计划。

静态优化是受优化器影响的,不同版本有不同情况,所以这里讲述动态优化的情况,而动态优化主要包含下面的内容:


  • 关联表顺序,有时候关联表顺序和查询顺序不一定相同。
  • 重写外连接为内连接:如果一个外连接关联是没有必要的就优化掉外连接关联。
  • 等价替换,比如 a>5 and a= 5被优化为a >= 5 ,类似数学的逻辑公式简化
  • 优化count()、max()、min()等函数:有时候找最大和最小值只需要找最大和最小的索引记录,这时候由于不需要遍历,可以认为直接为哈希的获取记录的方式,所以在查询分析的 extra 里面进行体现(Select tables optimized away),比如:explain select max(actor_id) from actor;
  • 预估和转化常数:以连接查询为例,如果在查询条件中可以实现预估关联的记录条数,那么对于一个关联查询来说就有可能被优化器作为常数进行优化,因为事先取出记录的条数被优化器知晓。所以优化起来十分简单。
  • 子查询优化:子查询虽然有可能被索引优化但是需要尽量避免使用。
  • 覆盖索引扫描:让索引和查询列一致时非常高效的优化和执行方式
  • 提前终止查询:提前终止查询指的是当遇到一些查询条件会让查询提前完成的语句,优化器会提前判断加快数据的匹配和搜索速度
  • 等值传递,如果范围查询可以根据关联表查询优化,那么无需 显式的提示则可以直接搜索数据。


参考资料:


这里汇总了文章中出现的一些参考资料:


写在最后


上半部分以理论为主,下半部分将会着重实战内容进行介绍。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
23天前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
|
24天前
|
SQL 关系型数据库 MySQL
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
MySQL慢查询优化、索引优化,是必知必备,大厂面试高频,本文深入详解,建议收藏。关注【mikechen的互联网架构】,10年+BAT架构经验分享。
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
|
1天前
|
存储 关系型数据库 MySQL
Mysql索引:深入理解InnoDb聚集索引与MyisAm非聚集索引
通过本文的介绍,希望您能深入理解InnoDB聚集索引与MyISAM非聚集索引的概念、结构和应用场景,从而在实际工作中灵活运用这些知识,优化数据库性能。
25 7
|
28天前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案,包括索引优化、SQL慢查询优化和数据库表优化,帮助提升数据库性能。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
MySQL慢查询优化、索引优化、以及表等优化详解
|
17天前
|
关系型数据库 MySQL Java
MySQL索引优化与Java应用实践
【11月更文挑战第25天】在大数据量和高并发的业务场景下,MySQL数据库的索引优化是提升查询性能的关键。本文将深入探讨MySQL索引的多种类型、优化策略及其在Java应用中的实践,通过历史背景、业务场景、底层原理的介绍,并结合Java示例代码,帮助Java架构师更好地理解并应用这些技术。
22 2
|
1月前
|
搜索推荐 关系型数据库 MySQL
mysql like查询优化
通过合理的索引设计、使用全文索引、优化查询结构以及考虑分片和分区表,可以显著提高MySQL中 `LIKE`查询的性能。针对不同的应用场景选择合适的优化策略,能够有效地提升数据库查询效率,减少查询时间。希望这些方法和技巧能帮助您优化MySQL数据库中的模糊查询。
128 4
|
3天前
|
关系型数据库 MySQL 数据库
Python处理数据库:MySQL与SQLite详解 | python小知识
本文详细介绍了如何使用Python操作MySQL和SQLite数据库,包括安装必要的库、连接数据库、执行增删改查等基本操作,适合初学者快速上手。
45 15
|
3天前
|
关系型数据库 MySQL 数据库
数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
mysql数据库文件ibdata1、MYI、MYD损坏。 故障表现:1、数据库无法进行查询等操作;2、使用mysqlcheck和myisamchk无法修复数据库。
|
7天前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。
|
15天前
|
关系型数据库 MySQL 数据库
GBase 数据库如何像MYSQL一样存放多行数据
GBase 数据库如何像MYSQL一样存放多行数据