MySQL优化经验分享记录

最新推荐文章于 2023-12-05 18:10:32 发布
最新推荐文章于 2023-12-05 18:10:32 发布
阅读量2.8k 收藏 4
点赞数 8
分类专栏： mysql 文章标签： mysql优化 SQL优化 explain 数据库 MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_29550537/article/details/119139654
版权

1. 一段SQL

话不多说，直接看SQL

select * from 表A a -- force index(idx_doc_state)
left join (
	select doc_id, group_concat(name) names from 表B group by doc_id
) bb bb.doc_id = a.doc_Id
where 
	-- a.urgency = '无'
	-- a.urgency = '加急'
	a.urgency = '特急'
	and a.doc_state in ('02','03','04')
	-- 还有其他表A的条件，这里省略
order by a.create_time desc
limit 0,10

SQL- 1

这是一段我工作中遇到的SQL，有部分省略但整体不影响。写这篇博客主要是个给自己做个笔记，同时也想分享一下自己的经历，希望得到各位大佬的批评指点😁。

2. 表中的索引

  表A主键是doc_id，表B的doc_id是一个单列非唯一索引，表A另外还有两个索引分别是idx_create_time(create_time), idx_doc_state(doc_state)

3. 存在的问题

  奇怪的现象： 当条件urgency='无'时查询时间需要6s，当条件urgency='加急'时查询时间需要6s，当条件urgency='特急'时却只需要16ms。
  实在想不通，仅仅是改变了字段的值查询效率会差这么多。

4. 查看执行计划

  改变条件字段urgency的值是不会影响执行计划的，explain看一下：
在这里插入图片描述

图1:

  该执行计划中可以看出，type是index,可能使用的索引是idx_doc_state,真实使用的索引是idx_create_time,然后在Extra中包含Backward index scan。
  开始也没搞懂，type=index是扫描整棵索引树（idx_create_time索引树）的意思。Backward index scan意思是使用倒序索引扫描，应该是使用了order by a.create_time desc的原因,执行引擎会倒序扫描idx_create_time索引树。
  还是没有思路该如何优化。于是，我决定开始删减查询条件看能否发现些什么。当前urgency='加急'，第一步我先删除了limit 0,10条件，运气挺好，查询时间立刻从6s变成的了31ms。但是本来感觉有limit限制查询的数据会更少，查询效率应该会更好呀😂。再次查看执行计划，看看有什么变化。
在这里插入图片描述

图2:

  果然有变化，type变成了range级别，索引使用的是idx_doc_state然后Extra没有了Backward index scan,但是多了Using filesort 和 Using index condition意思是分别使用了文件排序和索引条件操作。
  虽然，删除limit条件后查询效率确实提高了，但limit也是分页必不可少的，但同时使用的索引也发生了变化，从idx_create_time变成了idx_doc_state，所以有可能是索引变化影响的查询效率。于是我还原了limit条件，然后在SQL语句“select * from 表A a ”后面加上了一段“force index(idx_doc_state)”表示强制A表使用索引idx_doc_state，执行SQL发现依然是31ms，没错的确使用idx_doc_state索引，大大提高查询效率。

5. 疑问

到这里我先留下几个疑问：

  1. urgency='加急'或者'无'时,为什么使用idx_doc_state的查询效率会更高呢?
  2. urgency='加急'或者'无'是,为什么使用idx_create_time的查询效率会非常的慢?
  3. urgency='特急'时,无论是使用idx_doc_state还是idx_create_time查询效率都很高?
  4. 默认情况下为什么MySQL执行引擎会选择idx_create_time索引进行查询?

6. 表中的数据分布

  现在我们先看看这两张表的数据的基本情况和分布:
  表A有数据1023980条,表B有数据1531条;
  表A的数据分布:
    其中urgency的不同值包含的数据条数分别是:
      ‘无’->1024882条 ‘加急’->6条 ‘特急’->92条;
    其中doc_status不同值包含的数据条数分别是
      '01'->1023680条 '02'->1243条 '03'->0条 '04'->57条;
  表A满足不同条件的数据分布:
    当urgency='无'，共有11条数据满足SQL所有条件；
    当urgency='加急'，共有2条数据满足SQL所有条件；
    当urgency='特急'，共有35条数据满足SQL所有条件。
  还有一点，这是测试环境的数据表，测试人员在表A中创建了大量的测试数据，所有100多万条urgency='无' and doc_state='01'的数据，这部分数据的create_time基本集中在一天。
通过下列SQL来查看一下数据的分布情况:

select 
	urgency, 
	doc_state, 
	left(create_time,10) ctime, 
	count(1) cnt 
from 表A
group by urgency, doc_state, left(create_time,10)
order by ctime desc

SQL- 2

在这里插入图片描述

图3:

大致情况如图3，其中省略了部分行，不影响效果展示。以上就是该表的数据分布情况。

7. 问题分析

问题1：

  接下来我们先带着以上问题来分析一下，首先是第1个问题，当SQL使用的索引是idx_doc_state时，无论urgency的取值是'无'、'加急'还是'特急'SQL的执行效率基本都在31ms左右。原因很简，因为表总共包含1023980条数据，其中就有1023680 条数据是doc_state='01'，而我们要查询的数据仅在'02'、'03'和'04'范围内的数据，这部分数据总共才1300条，而这部分数据足够少，所以通过idx_doc_state索引我们可以很快的找出这部分数据。

问题2：

  然后是第2问题，默认情况下优化器选择的是idx_create_time索引进行查询，由图1的执行计划可以看出，type=index和Extra=Backward index scan执行器会按顺序倒序扫描idx_create_time索引树来查找数据，先扫描到时间最大的一条数据获取主键ID，然后回表判断其他条件是否满足。如果满足，则成功获取到A表的一条数据；如果不满足，就继续扫描第二条数据，由于limit 0,10的限制，需要获取到前10条满足条件的数据，便会停止扫描查找，如果不足10条，便会一直扫描直到扫描完所有的数据为止。
  现在我们来观察图3，可以看到大部分数据集中在第6行，条件是urgency='无' and doc_state='01'，显然这部分数据1010102条都不满足我们的SQL条件，并且这些数据时间都集中在2021-07-02这一天。我们就以第6行数据为界观察，可以看到所有加急的数据都在第6行之上，所以扫描少量数据都可以找到全部加急的数据，但是由于limit 0,10的限制条件，需要扫描到10条数据或者扫描完整张表才会停止扫描，因为加急数据总共还不足10条，所以最终会完整的扫描整张表才能获取到数据，这个查询效率肯定会很低。
  顺便一提: 图3中第6行包含的数据被扫描了，就差不多相当于扫描了整张表了效率会很低。因为这边部分数据量和整张表数据总数差不多了，而且这部分数据都是不满足条件的数据。
  以上数据分布中所说，当urgency='加急'，共有2条数据满足条件，也就是说这两条数据正好在图3中的第3行中包含了，如果修改SQL limit 0,2，索引依然是idx_create_time是否马上就可以扫描到满足条件的两条数据然后返回呢？经验证，正是如此，这里就不展示了。当然urgency='无'时也是同理，以为图3第6行之前的时间范围内的数据中没有找够10条满足条件的数据，所以会继续向下扫描的图3第6行这部分的数据然后判断筛选，所以效率会很低。 而urgency='特急'时，以上数据分布中说满足SQL条件的数据有35条，这中间大部份数据都在图3第6行2021-07-02以上时间范围内，所以在扫描图3第6行包含的数据之前，就能过获取到10条满足条件的数据，并终止扫描。因为扫描的数据量少所以查询的效率也高(扩展思考,见最后补充中的第2个问题)。

问题3:

  第3个问题通过上面的解释应该清楚了，使用idx_doc_state索引时是因为要查询的a.doc_state in ('02','03','04')范围内的数据量少，通过索引可以很快查到。而使用idx_create_time索引时，因为order by a.create_time desc倒序扫描索引的原因，而满足其他条件及limit 0,10条件的数据都在图3第6行时间2021-07-02之前的时间范围内，所以也只需要扫描少量的数据，就能查到满足条件的数据，然后停止扫描返回数据。

问题4：

  最后是第4个问题，为什么MySQL会选择使用idx_create_time索引来查询数据。这个考虑到MySQL查询成本的计算.。具体这么计算这里就不介绍了，可以通过在 explain 后面加format=json来查看，例如:

explain format=json
select * from 表A a -- force index(idx_doc_state)
left join (
	select doc_id, group_concat(name) names from 表B group by doc_id
) bb bb.doc_id = a.doc_Id
where 
	-- a.urgency = '无'
	-- a.urgency = '加急'
	a.urgency = '特急'
and a.doc_state in ('02','03','04')
-- 还有其他表A的条件，这里省略
order by a.create_time desc
limit 0,10

SQL- 3

上面的SQL会返回json格式的执行计划的详细信息，里面有个query_cost的字段显示当前SQL的查询成本值，值越小通常认为效率越高，通过开启SQL中注释的部分force index(idx_doc_state)就可以查看到使用索引idx_doc_state的执行成本，因为使用idx_create_time索引的执行成本比使用idx_doc_state的执行成本低，索引MySQL选择了使用idx_create_time索引来查询数据.。但是，成本值低也不一定查询效率就会高，就像本例中的SQL，成本值只是一个常规估计值，而本例是人为添加了测试数据，大部分数据集中在一点，分布极其不均匀，已经不常规了😁,所以具体情况具体分析吧。

8. 补充问题

  1. 为什么表A的possible_keys中只有idx_doc_state，而使用的索引却是idx_create_time；同时为什么刚开始优化SQL时，去掉limit 0,10执行计划使用的索引会变成idx_doc_state？
    答： 这源于MySQL对limit和order by子句的优化，详情请看官方文档https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html。
    在这里插入图片描述
    检点来说就是MySQL优化器会对带有ORDER BY或GROUP BY和LIMIT子句的查询进行优化，在默认情况下尝试选择有序索引以提高查询效率。

  2. 如果urgency='特急'情况，上面数据分布可以看出满足条件的数据有35条，如果最后5条数据包含在图3第6行以下时间范围内，那么limit 30,10会很慢吗?
    答： 是的，如果当前选择的是MySQL选择的是idx_create_time确实会很慢。但是点条件为limit 30,10时，MySQL选择使用idx_doc_state索引进行查询，因为它的查询成本更低。

新风s
关注
专栏目录
MySQL 性能优化的最佳20多条经验分享
09-11
MySQL性能优化是提升Web应用效率的关键环节，尤其是在处理大量数据操作时。以下是一些关键的优化策略： 1. **为查询缓存优化你的查询** MySQL的查询缓存能够显著提升性能，尤其是在重复查询相同数据的情况下。...
（译）MySQL 8.0实验室---MySQL中的倒序索引（Descending Indexes）
weixin_33701294的博客
08-21 674
  译者注：MySQL 8.0之前，不管是否指定索引建的排序方式，都会忽略创建索引时候指定的排序方式（语法上不会报错），最终都会创建为ASC方式的索引，在执行查询的时候，只存在forwarded（正向）方式对索引进行扫描。关于正向索引和反向索引，逻辑上很容易理解，这里有两个相关的概念：正向索引或者反向（倒序）索引，两者都是在构建B树索引时候的相关字段排序方式，是B索引树的逻辑存储方式正向扫描（f...
MySQL索引
qq_56370103的博客
08-19 3225
索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。接下来就来了解一下索引的使用吧！
一个有趣的SQL优化案例
qq_39744051的博客
04-20 576
问题描述 分析 表的信息 估算cost start-up cost run cost 执行计划 实际执行时间 从内核视角来分析 解决方案 写在最后 Coding过程中经常会写SQL语句，有时写的SQL出现慢查询而被DBA鄙视。 今天我们一起从使用者，DBA，内核开发三个不同角度来分析和解决一个SQL性能问题，来重新认识逻辑优化与物理优化。 ..
MySQL 进阶 索引 -- SQL优化（插入数据优化：导入本地文件数据、主键优化、order by优化、group by优化、limit优化、count优化、update优化）
天行健 君子以自强不息，地势坤 君子以厚德载物。
04-19 993
MySQL 进阶 索引 SQL优化（插入数据优化：导入本地文件数据、主键优化、order by优化、group by优化、limit优化、count优化、update优化）
MySQL EXPLAIN详解
最新发布
修己xj的博客
12-05 1103
MySQL数据库是许多Web应用程序的底层支持，而查询性能的优化是确保系统高效运行的关键。在MySQL中，`EXPLAIN`是一项强大的工具，可帮助开发者深入了解查询语句的执行计划，从而更好地优化查询性能。本文将详细解析MySQL的`EXPLAIN`关键字，以揭开查询执行计划的面纱。
MySQL字符串与时间戳的转换
weixin_43751710的博客
05-06 1541
在Java中，想要实现对数据库中的特殊查询——查询某个时间段内的字段，但是传入的日期值是String型，而数据库中的日期是时间戳…… 可以使用如下方法： 数据库的有字符串转换为时间戳的函数unix_timestamp(?)。 public int countNum(String startTime, String endTime) { String SQL = "SELECT left(c...
MySQL索引优化技巧及经验分享
MySQL索引优化的基础知识 ## 1.1 索引的概念和作用 在MySQL中，索引是一种特殊的数据结构，它可以帮助数据库系统快速地定位到需要查询的数据，从而提高查询性能。本节将介绍索引的概念、作用以及使用场景。 ## ...
MySQL数据库性能优化案例分享：从实际场景中汲取优化经验
[MySQL数据库性能优化案例分享：从实际场景中汲取优化经验](https://img-blog.csdnimg.cn/66d785ec54b74c28afb47b77698a1255.png) # 1. MySQL数据库性能优化概述** MySQL数据库性能优化是一门技术，旨在提高数据库...
MySQL 8.0数据库性能优化与调优经验分享
## 1.1 MySQL 8.0数据库性能优化的重要性 ## 1.2 本文介绍的目的和方法 ## 2. MySQL 8.0性能调优概述 2.1 MySQL 8.0新特性的性能优势 2.2 性能调优的一般步骤 2.3 了解性能指标和监控工具 ### 3. 配置优化 在...
MySQL 8 新特性之降序索引
weixin_34102807的博客
05-22 719
MySQL 8.0终于支持降序索引了。其实，从语法上，MySQL 4就支持了，但正如官方文档所言，"they are parsed but ignored"，实际创建的还是升序索引。   无图无真相，同一个建表语句，看看MySQL 5.7和8.0的区别。 create table slowtech.t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc))...
MySql优化（四）索引优化分析（案例分析）
在路上
06-01 2465
写在前面： 我热爱技术，热爱分享，热爱生活， 我始终相信：技术是开源的，知识是共享的！ 博客里面的内容大部分均为原创，是自己日常的学习记录和总结，便于自己在后面的时间里回顾，当然也是希望可以分享自己的知识。目前的内容几乎是基础知识和技术入门，如果你觉得还可以的话不妨关注一下，我们共同进步！ 个人除了分享博客之外，也喜欢看书，写一点日常杂文和心情分享，如果你感兴趣，也可以关注关注！ 微信公众号：傲骄鹿先生 ...
Java8 根据多字段分组 统计求和实例
热门推荐
weixin_39643007的博客
05-24 1万+
1.本实例其实可以用一句简单的sql去实现 由于我们项目数据库中时间存的是13位的时间戳 所以必须得转行成日期格式 才能进行分组 如下： SELECT count(1) simpleNumber, SUM(penalty_amount) AS simplePenaltyAmount, Handling_department, create_time,...
Mysql explain执行计划详解
azbh111的博客
12-20 1051
explain可以查看sql的执行计划，根据执行计划，可以看出sql慢在哪，方便进行调优。 explain中比较重要的几个字段：type、key、key_len、rows、filtered、Extra。以下列举比较常见的情况 type type取值，一般而言从上到下性能依次降低 system 表中只有一条数据的情况 const 查询条件只有一个值，通过主键或者唯一索引精确匹配一条记录 eq_ref 发生在链表查询中 使用非唯一索引进行关联 ref 连表、不连表都可能发生
004_Mysql 之索引使用以及优化
qq_32057039的博客
03-21 191
也出现 Using index， 但是此时Extra中出现了 Backward index scan，这个代表反向扫描索引，因为在MySQL中我们创建的索引，默认索引的叶子节点是从小到大排序的，而此时我们查询排序时，是从大到小，所以，在扫描时，就是反向扫描，就会出现 Backward index scan。当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让 索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。所以需要针对于age也要建立索引。
打开order by的大门，一探究竟《死磕MySQL系列 十二》
咔咔博客
11-27 1905
没事看一下，order by 语句的几种算法系列文章 系列文章 一、原来一条select语句在MySQL是这样执行的《死磕MySQL系列 一》 二、一生挚友redo log、binlog《死磕MySQL系列 二》
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 19 章 服务器配置_19.15. 预置选项
weixin_34115824的博客
10-02 153
19.15.预置选项 下列“参数”是只读的，它们是在编译或安装PostgreSQL时决定的。同样，它们被排除在postgresql.conf文件例子之外。这些选项报告特定应用可能感兴趣的多种PostgreSQL行为，特别是管理前端相关的行为。 block_size(integer) 报告一个磁盘块的大小。它由编译服务器时BLCKSZ的值确定。...
mySQL定义降序索引_MySQL8新特性：降序索引详解
weixin_39531834的博客
02-02 539
前言mysql 8.0终于支持降序索引了。其实，从语法上，mysql 4就支持了，但正如官方文档所言，"they are parsed but ignored"，实际创建的还是升序索引。无图无真相，同一个建表语句，看看mysql 5.7和8.0的区别。create table slowtech.t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));mysql ...
PostgreSQL index only scan
luojinbai的专栏
03-02 3569
index only scan ,是我们用select选择字段的时候，所选的字段全部都有索引，那么只需在索引中取数据，就不必访问数据块了，从而提高效率。 1. 建表 postgres=# create table tb_index_test(id serial primary key,name character varying); CREATE TABLE postgres=# post
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值