14. 慢查询、索引、执行计划详解

已于 2023-07-19 20:12:31 修改
阅读量1k 收藏 9
点赞数 3
分类专栏： # 3. 数据持久化 文章标签： 执行计划和索引 慢查询 sql优化方式 启动慢查询 mysqldumpslow mysql索引 数据库索引
于 2022-10-15 00:15:40 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yang134679/article/details/127329516
版权

文章目录

慢查询

一、慢查询定义及作用

慢查询日志，顾名思义，就是查询慢的日志。
分析MySQL语句查询性能的方法除了使用 EXPLAIN 输出执行计划，还可以让MySQL记录下查询超过指定时间的语句，我们将超过指定时间的SQL语句查询称为“慢查询”。
该日志能为sql语句的优化带来很好的帮助。默认情况下慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。

二、启动慢查询

配置文件地址
  windows ： D:\WorkSoftWare\DataDB\MySQL\MySQL Server 5.7\my.ini(我的本地路径)
  linux : my.cnf
  
文件内容
  log-output=FILE,TABLE
  slow-query-log=1
  slow_query_log_file="LAPTOP-D67UA1L3-slow.log"
  long_query_time=3

配置文件中各参数含义

  1. log_output 日志存放的地方,可以是数据库表（[table]），文件（[file],或者是([file,table])）
  2. slow-query-log 启动停止慢查询日志（0否/1是）
  3. slow_query_log_file 指定慢查询日志的存储路径及文件（默认和数据文件放在一起）
  4. long_query_time 指定记录慢查询日志SQL执行时间的阈值（单位：秒，默认10秒）
  5. log_queries_not_using_indexes 是否记录未使用索引的SQL

三、慢查询记录

找到数据库的date目录,在下面可以找到LAPTOP-D67UA1L3-slow.log文件，该文件即为记录文件

D:\WorkSoftWare\DataDB\MySQL\MySQL Server 5.7\data

1 记录的内容

  1. 查询语句
  2. 数据修改语句
  3. 已经回滚的sql

2 记录内容详解

1. # Time: 2020-06-22T16:08:25.667840Z
2. # User@Host: root[root] @ localhost [::1]  Id:     1
3. # Query_time: 111.353229  
4. # Lock_time: 0.000000 
5. # Rows_sent: 2794  
6. # Rows_examined: 2551
7. use gdtreiking;
8. SET timestamp=1944;
9. SELECT * from rkrm$_log_diinfo d LEFT JOIN  rkrm$_log_buzinfo b on d.menuid = b.menuid;
行号介绍
1操作时间
2用户名、用户的ip信息、线程ID号
3执行sql花费的时间（单位毫秒）
4执行获得锁的时间
5获得的结果行数
6扫描的数据行数
7使用的库
8该sql执行的具体时间
9具体的sql语句

3 sql修改慢查询阈值，以及session问题

在navicat中先执行查询阈值操作,值为3秒。之后执行修改操作，设置为0秒。在查询的话值依旧为3秒。这是因为数据库的session问题。当我们新打开一个页签进行查询。我们就会发现阈值已经改变。

SHOW VARIABLES LIKE 'long_query_time';	/**查询阈值**/
SET GLOBAL long_query_time = 0;   			/**设置阈值**/

请添加图片描述请添加图片描述
请添加图片描述

四、慢查询分析工具[mysqldumpslow]

1 简介

mysql自带工具

D:\WorkSoftWare\DataDB\MySQL\MySQL Server 5.7\bin\mysqldumpslow.pl（我的安装位置）
mysqldumpslow.pl无法直接运行，配合activeperl等慢查询工具使用，安装activeperl后运行

2 使用语法

注意：


1. 一般使用mysql的data目录的绝对路径会有特殊符号，所以可以在bin目录下使用相对路径，或者将日志文件拷贝到路径简单为目录下使用。



2. 结果展示了，但是具体参数可能无法展示出来，一般为N替代。


语法：
    perl mysqldumpslow.pl -s t -t 3 ./../data\LAPTOP-D67UA1L3-slow.log
        -s (排序字段，后可接 c,t,l,r,at,al,ar)
            c: 总次数，
            t: 总时间，
            l: 锁的时间，
            r: 总数据行，
            at: t的平均数，例如[at = 总时间/总次数]
            al: l的平均数，
            ar: r的平均数，
            -g: 后边可以写一个正则匹配模式，大小写不敏感的
        -t top 指定取前面几天作为结果输出
    
结果：
    Reading mysql slow query log from ./../data\LAPTOP-D67UA1L3-slow.log
    Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
      Time: N-N-23T12:N:N.682287Z
      # User@Host: root[root] @ localhost [::N]  Id:    N
      # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
      SET timestamp=N;
      SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'S' ORDER BY TABLE_SCHEMA, TABLE_TYPE
    
    Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
      Time: N-N-23T12:N:N.273833Z
      # User@Host: root[root] @ localhost [::N]  Id:    N
      # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
      SET timestamp=N;
      SHOW TABLE STATUS LIKE 'S'
    
    Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
      Time: N-N-23T12:N:N.749521Z
      # User@Host: root[root] @ localhost [::N]  Id:    N
      # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
      SET timestamp=N;
      SHOW TABLE STATUS LIKE 'S'

五、数据库三大范式


一、第一范式

数据库表的每一列都是不可分割的原子数据项

二、第二范式

在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）。即要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。

三、第三范式

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。简而言之，说的是实体和实体之间的联系，就是关联表，他们之间用主键连起来，又叫外键关联。

六、常用优化方式

1. 服务器硬件优化

比如固态硬盘的存储速度是机械硬盘的10倍以上。

2. MySql服务器优化

linux系统要比windows系统稳定，并且运行速度要快。

3. SQL本身优化

具体如何优化，可以参考这篇文章

 //错误例句：该SQL中的关联子查询尽可能改成关联查询
 select a.meta1, a.meta2, a.meta3, (select b.meta1 from tableb b where a.meta1 = b.meta1) as meta4 from tablea a order by a.meta2, a.meta3, a.meta4

4. 反范式设计优化

  1. 反范式化是针对范式化而言的。
  2. 所谓的反范式化是为了性能和读取效率的考虑，而适当的对数据库设计范式的要求进行违反。
  3. 允许存在少量冗余，话句话说反范式化就是使用空间换取时间。

5. 索引优化

七、索引和执行计划

1 索引分类

1.普通索引：即一个索引只包含单列，一个表可以有多个单例索引
2.唯一索引：索引列的值必须唯一，但允许有空值
3.复合索引：即一个索引包含多个列

2 索引基础语法

1.查看索引：SHOW INDEX FROM TABLE_NAME
2.创建索引：CREATE [UNIQUE] INDEX indexName ON TABLE(columnname(length));
            ALTER TABLE tableName ADD [UNIQUE] INDEX  [indexName] ON (columnname(length));
        
3.删除索引：DROP INDEX [indexName] ON [tableName]
例如: 
    //总数 278414
    select count(*) from mytest
    //2.753秒
    SELECT * FROM mytest ORDER BY database_name,table_name,column_name,data_type
    //创建索引
    CREATE INDEX order_index ON mytest (database_name,table_name,column_name,data_type)
    //0.216秒
    SELECT * FROM mytest where database_name = 'DW_HQ_ERP_SC' ORDER BY database_name,table_name,column_name,data_type
    //删除索引
    DROP INDEX order_index ON mytest

请添加图片描述

3 检验sql是否使用了索引——执行计划

执行计划是什么？

使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
语法：Explain + SQL语句
例如：Explain SELECT * FROM mytest where database_name = 'DW_HQ_ERP_SC' ORDER BY database_name,table_name,column_name,data_type

请添加图片描述

4 执行计划分析——是否使用了索引

在这里插入图片描述

5 执行计划分析——索引是否被充分利用

执行计划中对于索引是否充分使用问题使用了key_len算法。
执行计划结果字段key_len就是按照以下规则计算结果.结果其实就是指的字节数
类型: varchar(+2) char(+0)
字符编码：utf8(+3)......
字段定义长度
是否为空: 是(+1) 否(+0)

①、定义的索引:

②、元数据信息:
在这里插入图片描述
③、key_len算法:
在这里插入图片描述

6. 执行计划分析——其他信息介绍

①、select_type属性:
在这里插入图片描述
②、type属性:
在这里插入图片描述
在这里插入图片描述

③、key_len属性:
在这里插入图片描述
在这里插入图片描述

7 索引使用10大军规

1.尽量索引字段全值匹配 
1.Explain select * from mytest where database_name = 'DW_HQ_ERP_SC'
2.Explain select * from mytest where database_name = 'DW_HQ_ERP_SC' and table_name = 'ADRC'
3.Explain select * from mytest where database_name = 'DW_HQ_ERP_SC' and table_name = 'ADRC' and column_name = 'STR_SUPPL3'
4.Explain select * from mytest where database_name = 'DW_HQ_ERP_SC' and table_name = 'ADRC' and column_name = 'STR_SUPPL3' and data_type = 'VARCHAR2'

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

2.最佳左前缀法则。 
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
当索引顺序为：`database_name`, `table_name`, `column_name`, `data_type`
1.错误示例，直接从第二个索引字段开始:
  Explain select * from mytest where table_name = 'ADRC'
2.错误示例，跳过了table_name字段:
  Explain select * from mytest where database_name = 'DW_HQ_ERP_SC' and column_name = 'STR_SUPPL3'
3.正确示例，从索引的最左前列开始并且不跳过索引中的列:
  Explain select * from mytest where database_name = 'DW_HQ_ERP_SC' and table_name = 'ADRC' and column_name = 'STR_SUPPL3' and data_type = 'VARCHAR2'
3.不要在索引列上做任何操作。 
不在索引列上做任何操作（计算、函数、（自动或手动）类型转换），会导致索引失效而转向全表扫描。

例如该sql语句：
在这里插入图片描述
检验是否使用了索引:
在这里插入图片描述

4.范围条件放最后

存储引擎不能使用索引中范围条件右边的列。
例如有一个如下的查询语句：
    select * from table where age > 12 and age <= 18
而这个table表的索引顺序为
    `age`, `name`, `sex`
那么元数据age作为第一个索引字段，需要进行索引优化，调整table表的索引顺序为
    `name`, `sex`, `age` 
将age属性作为索引顺序中最后一个值，即可完成优化。
8.like查询要当心 
like条件使用时%应该加在匹配条件后面
1.%放在后面
    Explain select * from mytest where database_name = 'DW_HQ_ERP_SC' and table_name = 'ADRC' and column_name = 'STR_SUPPL3' and data_type like 'V%'
2.%放在前面
    Explain select * from mytest where database_name = 'DW_HQ_ERP_SC' and table_name = 'ADRC' and column_name = 'STR_SUPPL3' and data_type like '%V'
3.覆盖索引
    所查询的列都是索引中的列。这样即使like中的%在前面，也是会用到索引的
    Explain select database_name,table_name,column_name,data_type	from mytest where database_name = 'DW_HQ_ERP_SC' and table_name = 'ADRC' and column_name = 'STR_SUPPL3' and data_type like '%V'

1.%放在后面
在这里插入图片描述
2.%放在前面
在这里插入图片描述

9.字符型数据要加引号

鱼鱼大头鱼
关注
专栏目录
执行计划---索引扫描
滴水石穿
04-24 738
索引扫描 索引唯一扫描【Index unique scan】: 原理：从根节点到叶节点遍历整个树，直到查找到指定的条目，获取ROWID，然后根据ROWID查找到对应的数据行。 使用条件:WHERE条件中的列对应的是主键索引或唯一索引，而且该扫描方法只是用于单值扫描，也就是谓语为“=”。 hint:/*INDEX(table_alias index_name)*/ 索引范围扫描【Index
慢SQL调优-索引详解面试题
qq_42872034的博客
03-06 1387
最新的 Java 面试题，技术栈涉及 Java 基础、集合、多线程、Mysql、分布式、Spring全家桶、MyBatis、Dubbo、缓存、消息队列、Linux…等等，会持续更新。
一次MySQL线上慢查询分析及索引使用
weixin_34204057的博客
12-11 158
本文由作者郑智辉授权网易云社区发布。0.前言本文通过分析线上MySQL慢查询日志，定位出现问题的SQL，进行业务场景分析，结合索引的相关使用进行数据库优化。在两次处理问题过程中，进行的思考。1.简要描述在九月底某个新上的游戏业务MySQL慢查询日志# Time: 2017-09-30T14:56:13.974292+08:00 # Query_time: 6.048835 Lock_time: ...
Mysql慢日志、慢SQL
最新发布
爱思考的People
07-04 747
一般情况下，我们只需开启慢日志记录，配置下阈值时间，其余参数可按默认配置。在 MySQL 中，还可以设置将未走索引的SQL语句记录在慢日志查询文件中(默认为关闭状态)。long_query_time 的默认值为10，意思是运行10秒以上(不含10秒)的语句）。在MySQL中，日志输出格式有支持：FILE(默认)，TABLE 两种，可进行组合使用。默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。查看执行慢的SQL语句，需要先开启慢查询日志。
Mysql - 慢查询和执行计划
it_lihongmin的博客
04-07 614
慢查询的相关参数设置： long_query_time： 当查询时间超过多少秒才会被记录为慢查询，默认值为 10，可以设置为 0~10，可以指定设置为微秒； slow_query_log： 慢查询日志开关，默认值是 0 表示关闭， 设置为 1 表示开启； slow_query_log_file：host_name-slow.log，定义慢日志的保存地址，默认是主机名-slow.log；
07-MySql 慢查询分析-执行计划篇
my_momo_csdn的博客
06-19 714
文章目录MySql执行计划一、执行计划的作用二、使用方式三、含义分析3.1 id3.2 select_type3.3 table3.4 Type3.5 possible_keys3.6 keys3.7 key_len3.8 ref3.9 rows3.10 extra四、示例4.1 数据库4.2 示例一4.3 示例二五、参考六、附数据库信息 MySql执行计划 使用EXPLAIN命令可以查看MyS...
mysql---索引与执行计划
程序yuan同学
11-01 486
文章目录索引入门什么是索引MySQL中的索引索引的分类基础语法执行语法什么是执行语法执行计划的作用执行计划的语法执行计划详解 索引入门 什么是索引 MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。 可以得到索引的本质：索引是数据结构。 上面的理解比较抽象，举一个例子，平时看任何一本书，首先看到的都是目录，通过目录去查询书籍里面的内容会非常的迅速。 上图就是一本金瓶梅的书，书籍的目录是按顺序放置的，有第一节，第二节它本身就是一种顺序存放的数据结构，是一种顺序结构。 另
MySQL查询优化之索引的应用详解
09-10
`EXPLAIN`可以帮助开发者理解MySQL如何执行查询，包括是否使用了索引，以及哪些步骤可能成为性能瓶颈。 总的来说，设计和应用合理的索引策略是MySQL查询优化的核心。开发者应根据实际需求和表的使用场景，结合`...
mysql5.7.10开启慢查询详解
09-10
日志中会包含查询的执行时间、是否使用了索引、执行计划等信息。可以结合`EXPLAIN`命令进一步分析查询的执行情况，找出可能的问题并进行优化。 例如，如果发现某些查询频繁且没有使用索引，可以考虑创建适当的索引...
Explain执行计划详解
IT王小二的博客
01-24 3180
作者：IT王小二 博客：https://itwxe.com 一条查询 SQL 语句为什么会执行很慢？执行的时候走了什么索引？多表查询的时候 SQL 大体执行流程是怎么样的？Explain 执行计划详解给各位客官一一道来。 一、示例MySQL安装方式和版本 首先需要 MySQL 安装的客官看这两篇，小二演示使用的是 Docker 的安装方式： Linux(CentOS7)下二进制安装MySQL5.7.26 Docker搭建MySQL并挂载数据 小二安装完的 MySQL 数据库版本为 5.7.36。 二.
MySQL 各个索引的使用详解
01-19
慢查询日志通常也被称之为慢日志，因为它不仅仅只针对SELECT语句，像INSERT、UPDATE、DELETE等语句，只要响应时间超过所设定阈值都会记录在慢查询日志中。 1.3 如何开启慢查询日志命令 慢查询日志可以通过命令临时...
索引与慢查询
A_loser_A的博客
05-12 136
今天遇到一个慢查询的问题： EXPLAIN SELECT id , answer , answererPuid , choice , costTime, insertTime , isCorrect , questionId , questionType , quizRecordUuid , score , updateTime from t_quiz_answer_record where answererPuid=159214041 and questionType=13906 and...
MySql（二）索引与执行计划
你能找到这里，说明你有经天纬地之才，扭转乾坤只能，鬼神不测之计包藏宇宙之志，真乃乱世之枭雄，治世之能臣也
05-15 580
什么是索引： 在关系型数据库种，索引是一种单独的、物理的对数据库表种一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值得集合和相应的指向表中物理标识，这些值的数据页的逻辑指针清单。 （什么意思：索引的作用相当于图书的目录，可以根据目录中页码快速找到所需要的内容） 索引的分类： 1、普通索引：即一个索引只包含单个列，一个表可以有多个单列索引； 2、唯一索引：索引列的值必须唯一，但允许有空值，并且多个空值； 3、主键索引：与唯一索引之间的区别就在于不允许有空值，创建主键时会自动创建此索
MySQL执行计划与索引详解
zhengxc博客
10-28 877
MySQL执行计划与索引详解举个栗子简单使用explain中字段详解一、id列二. select_type列三、table列四、type列五、possible_keys列六、key列七、key_len列八、ref列九、 rows列十、Extra列索引最佳实践表结构最佳实践一、全值匹配二、最佳左前缀法则三、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描四...
Mysql索引与sql执行计划
weigeshikebi的博客
04-01 1091
我们在前面探讨了Mysql索引是什么这一节我们看下如何利用索引，及涉及到的sql执行计划索引优化执行计划参考地址：MySQL介绍很多时候，我们想知道sql会怎么执行，那有没有办法呢？explain关键字！Mysql提供了这个关键字让我们优化索引，使查询更快；分析优化器的表连接，使它采用最优的顺序。除此之外，explain还可以查看表结构EXPLAIN tb_name等同于DESCRIBE tb_n...
MySQL索引管理及执行计划
weixin_34010949的博客
04-28 148
MySQL索引管理及执行计划第1章 索引介绍:索引是对数据库表中一列或者多列 的值进行排序的一种结构,使用索引可以快速访问数据库表中的特定信息,如果想按特定职员的姓名来查找,则与他在表中搜索所有的行相比,索引有助于更快的获取信息索引的一个主要目的就是加快检索表中的数据的方法,既能协助信息搜索者尽快找到符合限制条件的记录ID的辅助数据结构1.1 索引的类型介绍: btree:...
关于索引来解决慢查询的问题
ll_superman的博客
08-29 848
写这篇博客并不是近期遇到这个问题，只是最近有点程序员的困惑，想着把自己知道的东西总结下来，这样心会安静点     首先我需要讲一下索引的概念。数据库的索引就是跟书本的索引一样，可以快速的找到你想找到的东西，从而大大提高性能。     当我们想去看看SQL语句走了哪些索引的时候,直接explain sql语句就可以查看了，例如：explain select * from user where u
mysql索引和慢查询
qq_31273845的博客
07-19 264
mysql的一些索引介绍
面试：如何排查慢查询（执行计划）怎么办？愣着干嘛？来白嫖呀
weixin_47067712的博客
07-22 265
目录 一、阅读前 二、explain 实战 2.1、初识执行计划： 2.2、分析联表SQL的执行计划 2.3、分析子查询SQL的执行计划 2.4、分析union SQL的执行计划 2.5、分析复杂SQL的执行计划 2.6、常见的执行计划的type 2.6.1、const 2.6.2、ref 2.6.3、eq_ref 2.6.4、eq_or_null 2.6.5、range 2.6.6、index 补充：索引的选择逻辑 2.7、详解ref列 .
MySQL执行计划详解与调优
通过执行计划，可以识别慢查询的原因，比如是否因为没有使用索引导致全表扫描，或者是否存在不必要的子查询。根据这些信息，可以优化SQL语句，添加合适的索引，或者调整查询结构，以提高查询效率。 六、Q&A： 这...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

鱼鱼大头鱼

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值