我的编程空间,编程开发者的网络收藏夹
学习永远不晚

「MySQL系列」分析SQL执行时间及查询执行计划(附数据库和一千万数据)

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

「MySQL系列」分析SQL执行时间及查询执行计划(附数据库和一千万数据)

1. 创建表和导入一千万条数据

表和数据地址(gitee) https://gitee.com/flowerAndJava/millions_data


2. 大批量数据导入数据

a 将数据库导入服务器中(如果是windows系统,这步省略)

b 创建一个数据库

  1. 创建数据库(db2),表tb_sku 

c 命令行登录数据库

  1. mysql -u 用户名 -p 密码 ; 

d 切换到使用的数据库

  1. use db2; 

e 使用命令

  1. load data local infile '/tmp/tb_sku1.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n'

对命令解释: '/tmp/tb_sku1.sql' 数据的目录(windows目录例如:D:\life\tb_sku1.sql),tb_sku 要导入到的表。


注意: 我们之前使用insert的sql将数据导入到数据库中,但是往库中导入上千万数据会需要很久时间。

二 慢查询分析(查找执行时间长的sql)

2.1 show profiles

show profiles是mysql提供可以用来分析当前会话中语句执行的资源消耗情 况。可以用来SQL的调优测量。

2.1.1 设置MySQL支持profile

1. 查看是否支持

  1. select @@have_profiling 

 

结果为YES,代表支持。

2. 查看profiling(profiling默认是关闭的)

  1. select @@profiling 

  

结果为0,代表没有开启3. 开启profiling

  1. set profiling=1;  

2.1.2 show profiles的使用

1. 输入一系列查询语句

  1. show databases; 
  2. use db01; 
  3. show tables; 
  4. select * from tb_ksu where id < 5; 
  5. select count(*) from tb_ksu; 

2. 查看没一条SQL执行时间

  1. show profiles;    //如果执行没有反应,查看profiling是否开启了,命令为select @@profiling;  

 

查看没每一条sql执行时间。

3. 查询每一条sql每个阶段执行时间

  1. select profile for query 6;   //6,代表Query_ID 

  

上图解释

  1. Sending data MySQL线程开始访问数据行并把结果返回给客户端,而不仅仅是 
  2. 返回给客户端。在Sending data状态下,MySQL线程往往进行大量的磁盘读取 
  3. 操作,所以在查询中最耗时的状态。 

查看线程在什么资源上耗费过高 (类型 all、cpu、block io 、context、switch、page faults)

  1. show profile cpu for query 7; 

 

上图说明

2.2 慢查询日志

慢查询日志记录了所有执行时间超过参数(long_query_time)设置值并且扫描 记录数不少于min_examined_row_limit,的所有SQL日志。long_query_time默 认为10秒,最小为0,精度可以到微秒。

2.2.1 设置慢查询日志

1. 修改配置文件(慢查询日志默认关闭的) 修改配置文件命令 vi /etc/my.cnf 然后在配置文件最下方加入下面配置

  1. # 该参数用来控制慢查询日志是否开启,可取值:1和0,1代表开启,0代表关闭 
  2. slow_query_log=1 
  3. #该参数用来指定慢查询日志的文件名 
  4. slow_query_log_file=slow_query.log 
  5. #该选项用来配置查询的时间限制, 超过这个时间将认为是慢查询, 将进行日志记录, 默认10s 
  6. long_query_time=10 

2. 重启mysql服务

  1. service mysqld restart 

备注 如果执行命令报如下错误


请使用命令 systemctl restart mysqld.service

3. 查看慢查询日志目录

  1. cd /var/lib/mysql 

2.2.2 日志读取

1. 查询long_query_time的值

  1. show variables like 'long%'

 

2. 执行查询操作

  1. select * from tb_sku where id = '100000030074'\G; 
  2. select * from tb_sku where name like '%HuaWei手机Meta87384 Pro%'\G; 

查询慢查询日志

a 使用cat


b 如果慢查询日志很多,借助借助于mysql自带的mysqldumpslow工具,进行分类汇总


三 explain执行计划、索引使用和SQL优化(对某个sql进行分析)

通过以上步骤查询到效率低的SQL语句后,可以通过EXPLAIN命令获取Mysql如 何执行Select语句信息,包含select语句执行过程中表如何连接和连接的顺 序。

3.1 执行explain命令,进行分析

  1. explain select * from tb_sku where id = '100000030074'

 

  1. explain select * from tb_sku where name like '%HuaWei 手机Meta87384 Pro%'

 

执行计划字段解释

 

3.2 对字段取值解释

1. id

  1. A. id 相同表示加载表的顺序是从上到下。 
  2. B. id 不同id值越大,优先级越高,越先被执行。 
  3. C. id 有相同,也有不同,同时存在。id相同的可以认为是一组,从上往下顺序执行;在所有的组中,id的值越大,优先级越高,越先执行。 

 2. select_type

3. type


结果由好到坏

  1. NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range > index > ALL 
  2.  
  3. system > const > eq_ref > ref > range > index > ALL 

4. key

  1. A. possible_keys : 显示可能应用在这张表的索引, 一个或多个。 
  2. B. key : 实际使用的索引, 如果为NULL, 则没有使用索引。 
  3. C. key_len : 表示索引中使用的字节数, 该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下, 长度越短越好 。 

5. rows

  1. 扫描行的数量。 

6. filtered

  1. 这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例。 

 

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

「MySQL系列」分析SQL执行时间及查询执行计划(附数据库和一千万数据)

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

「MySQL系列」分析SQL执行时间及查询执行计划(附数据库和一千万数据)

本篇介绍「MySQL系列」分析SQL执行时间及查询执行计划,希望能够帮助到你。

分析SQL执行时间及查询执行计划(附数据库和一千万数据)

我们干开发面试工作的时候,发现对数据库的面试比重很大。说明对数据库的知识掌握对我们程序员越来越重要了。接下来这篇文章我们来看看如何分析我们的sql执行效率。首先找到执行慢的sql,然后对执行慢的SQL进行分析。

编程热搜

  • Python 学习之路 - Python
    一、安装Python34Windows在Python官网(https://www.python.org/downloads/)下载安装包并安装。Python的默认安装路径是:C:\Python34配置环境变量:【右键计算机】--》【属性】-
    Python 学习之路 - Python
  • chatgpt的中文全称是什么
    chatgpt的中文全称是生成型预训练变换模型。ChatGPT是什么ChatGPT是美国人工智能研究实验室OpenAI开发的一种全新聊天机器人模型,它能够通过学习和理解人类的语言来进行对话,还能根据聊天的上下文进行互动,并协助人类完成一系列
    chatgpt的中文全称是什么
  • C/C++中extern函数使用详解
  • C/C++可变参数的使用
    可变参数的使用方法远远不止以下几种,不过在C,C++中使用可变参数时要小心,在使用printf()等函数时传入的参数个数一定不能比前面的格式化字符串中的’%’符号个数少,否则会产生访问越界,运气不好的话还会导致程序崩溃
    C/C++可变参数的使用
  • css样式文件该放在哪里
  • php中数组下标必须是连续的吗
  • Python 3 教程
    Python 3 教程 Python 的 3.0 版本,常被称为 Python 3000,或简称 Py3k。相对于 Python 的早期版本,这是一个较大的升级。为了不带入过多的累赘,Python 3.0 在设计的时候没有考虑向下兼容。 Python
    Python 3 教程
  • Python pip包管理
    一、前言    在Python中, 安装第三方模块是通过 setuptools 这个工具完成的。 Python有两个封装了 setuptools的包管理工具: easy_install  和  pip , 目前官方推荐使用 pip。    
    Python pip包管理
  • ubuntu如何重新编译内核
  • 改善Java代码之慎用java动态编译

目录