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

时间：2021-05-02

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

一 准备数据

在分析之前是不是有这样的困惑，我的数据量这么少。我如何分析SQL执行效率。不要慌，我们已准备了一千万条数据。接下来看看如何将这一千万条数据快速导入到数据库中。

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

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

2. 大批量数据导入数据

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

b 创建一个数据库

  • 创建数据库（db2），表tb_sku

    • c 命令行登录数据库

  • mysql-u用户名-p密码;

    • d 切换到使用的数据库

  • usedb2;

    • e 使用命令

  • loaddatalocalinfile'/tmp/tb_sku1.sql'intotable`tb_sku`fieldsterminatedby','linesterminatedby'\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. 查看是否支持

  • select@@have_profiling

    • 结果为YES,代表支持。

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

  • select@@profiling

    • 结果为0，代表没有开启

    3. 开启profiling

  • setprofiling=1;

    • 2.1.2 show profiles的使用

    1. 输入一系列查询语句

  • showdatabases;
  • usedb01;
  • showtables;
  • select*fromtb_ksuwhereid<5;
  • selectcount(*)fromtb_ksu;

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

  • showprofiles;//如果执行没有反应，查看profiling是否开启了，命令为select@@profiling;

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

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

  • selectprofileforquery6;//6,代表Query_ID

    • 上图解释

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

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

  • showprofilecpuforquery7;

    • 上图说明

    2.2 慢查询日志

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

    2.2.1 设置慢查询日志

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

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

    • 2. 重启mysql服务

  • servicemysqldrestart

    • 备注

    如果执行命令报如下错误

    请使用命令 systemctl restart mysqld.service

    3. 查看慢查询日志目录

  • cd/var/lib/mysql

    • 2.2.2 日志读取

    1. 查询long_query_time的值

  • showvariableslike'long%';

    • 2. 执行查询操作

  • select*fromtb_skuwhereid='100000030074'\G;
  • select*fromtb_skuwherenamelike'%HuaWei手机Meta87384Pro%'\G;

    • 3. 查询慢查询日志

    a 使用cat

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

    三 explain执行计划、索引使用和SQL优化

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

    3.1 执行explain命令，进行分析

  • explainselect*fromtb_skuwhereid='100000030074';

  • explainselect*fromtb_skuwherenamelike'%HuaWei手机Meta87384Pro%';

    • 执行计划字段解释

    3.2 对字段取值解释

    1. id

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

    • 2. select_type

    3. type

    结果由好到坏

  • NULL>system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
  • system>const>eq_ref>ref>range>index>ALL

    • 4. key

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

    • 5. rows

  • 扫描行的数量。

    • 6. filtered

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

    • 总结这篇文章可以分为三个点。

    1 往数据库导入一千万数据，查看上面文章，按照步骤操作没有任何问题。

    2 查找慢查询语句，查看慢查询日志。按照命令执行就好

    3 对sql执行进行分析，查看sql是否使用索引和执行效率。

    原文地址：https://mp.weixin.qq.com/s/Z5CdTipX_UQO6xgA0ietFg

    声明：本页内容来源网络，仅供用户参考；我单位不保证亦不表示资料全面及准确无误，也不保证亦不表示这些资料为最新信息，如因任何原因，本网内容或者用户因倚赖本网内容造成任何损失或损害，我单位将不会负任何法律责任。如涉及版权问题，请提交至online#300.cn邮箱联系删除。

    相关文章