问答文章1 问答文章501 问答文章1001 问答文章1501 问答文章2001 问答文章2501 问答文章3001 问答文章3501 问答文章4001 问答文章4501 问答文章5001 问答文章5501 问答文章6001 问答文章6501 问答文章7001 问答文章7501 问答文章8001 问答文章8501 问答文章9001 问答文章9501

mysql 执行计划

发布网友 发布时间：2022-04-07 15:04

我来回答

共7个回答

懂视网 时间：2022-04-07 19:26

2.3 type in Explain

type 字段是执行计划中衡量 SQL 非常重要的依据，它展示了 SQL 语句的关联类型（访问类型），决定了 MySQL 是如何查找表中行的。

type 字段的值性能从最差到最优依次是 ALL, index, range, index_merge, ref, eq_ref, const, system。

为了能更好地理解各个类型的含义，我对上述每一种类型都举出了相应的示例。

并未全部列出，完整的解释可以看官方文档-EXPLAIN Join Types

2.3.1 ALL

ALL 表示全表扫描，意味着存储引擎查找记录时未走索引，所以它是性能最差的一种访问类型，如

mysql> explain select * from user where age+2=20;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+| 1 | SIMPLE | user | NULL | ALL | NULL  | NULL | NULL | NULL | 1002301 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+1 row in set, 1 warning (0.00 sec)复制代码

可以看到 rows 行的值为1002301，即扫描了全表的所有数据（扫描行数的值实际为估算），如果在生产环境有这样的 SQL，绝对是要优化的。

我们知道在 where 查询条件中，不应该对查询字段使用函数或表达式（应该写在等号不等号右侧），不了解此内容的可以看看我的上一篇博客 —— 我所理解的MySQL(二)索引。

这条查询语句在优化后应该是: select * from user where age=18，去掉等号左侧的表达式，优化后的执行计划如下:

mysql> explain select * from user where age=18;
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| 1 | SIMPLE | user | NULL | ref | idx_age_name | idx_age_name | 5 | const | 39360 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码

2.3.2 index

index 表示全索引树扫描，由于扫描的是索引树，所以比 ALL 形式的全表扫描性能要好。

同时，由于索引树本身就是有序的，可以避免排序。

mysql> explain select id,age from user where name='name1';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref | rows | filtered | Extra   |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+| 1 | SIMPLE | user | NULL | index | NULL  | idx_age_name | 116 | NULL | 1002301 | 10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)复制代码

示例查询语句如上所述，当查询条件存在于联合索引 idx_age_name 中，但又无法直接使用该索引（由于最左前缀原则），同时查询列 id,age 也存在于联合索引中，无须通过回表来获取时，执行计划中的访问类型 type 列就会是 index。

2.3.3 range

range 表示范围扫描，准确的说是基于索引树的范围扫描，扫描的是部分索引树，所以性能比 index 稍好。

需要注意的是，若使用 in 或者 or 时，也可以使用范围扫描。

mysql> explain select * from user where age>18 and age<20;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref | rows | filtered | Extra   |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+| 1 | SIMPLE | user | NULL | range | idx_age_name | idx_age_name | 5 | NULL | 36690 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+1 row in set, 1 warning (0.01 sec)

mysql> explain select * from user where age=18 or age=20;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref | rows | filtered | Extra   |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+| 1 | SIMPLE | user | NULL | range | idx_age_name | idx_age_name | 5 | NULL | 78720 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)复制代码

2.3.4 index_merge

index_merge 即索引合并，它表示在查询时 MySQL 会使用多个索引。

MySQL 在 where 语句中存在多个查询条件，并且其中存在多个字段可以分别使用到多个不同的索引，在这种情况下 MySQL 可以对多个索引树同时进行扫描，最后将它们的结果进行合并，如：

mysql> explain select * from user where id=1 or age=18;
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref | rows | filtered | Extra      |
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+| 1 | SIMPLE | user | NULL | index_merge | PRIMARY,idx_age_name | idx_age_name,PRIMARY | 5,4 | NULL | 39361 | 100.00 | Using sort_union(idx_age_name,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+1 row in set, 1 warning (0.00 sec)复制代码

上面这条查询语句中的 id=1 和 age=18 分别使用到了 PRIMARY 主键索引和 idx_age_name 联合索引，最后再将满足这两个条件的记录进行合并。

2.3.5 ref

ref 表示索引访问（索引查找），这种访问类型会出现在查询条件中以非聚簇索引列的常量值进行查询的情况。

比如在介绍全表扫描中优化后 SQL 的访问类型就是 ref。

2.3.6 eq_ref

eq_ref 这种访问类型会出现在连接查询时，通过聚簇索引进行连接的情况，此类型最多只返回一条符合条件的记录。若表的聚簇索引为联合索引，所有的索引列必须是等值查询，如：

mysql> explain select * from user user1 inner join user user2 where user1.id=user2.id limit 10;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+| 1 | SIMPLE | user1 | NULL | ALL | PRIMARY | NULL | NULL | NULL  | 1002301 | 100.00 | NULL |
| 1 | SIMPLE | user2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | all_in_one.user1.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+2 rows in set, 1 warning (0.00 sec)复制代码

2.3.7 const

const 这种访问类型会出现在通过聚簇索引进行常量等值查询的情况，如：

mysql> explain select * from user where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| 1 | SIMPLE | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码

2.4 key_len in Explain

在上一篇博客 —— 我所理解的MySQL(二)索引 中 5.2 部分字段匹配 中已经提到过关于索引长度的计算方式，这里再来总结一下。

2.4.1 字符类型

字符类型的字段若作为索引列，它的索引长度 = 字段定义长度 字符长度 + 是否默认NULL + 是否是变长字段*，其中：

  • 字段定义长度 就是定义表结构时跟在字段类型后括号中的数字
  • 字符长度 是常数，utf8=3, gbk=2, latin1=1
  • 是否默认NULL 也是常数，若字段默认值为 NULL，该值为1，因为 NULL 需要额外的一个字节来表示；否则该值为0
  • 是否是变长字段 也是常数，若该字段为变长字段，该值为2；否则该值为0

    • 所谓的变长字段就是 varchar，它所占用的就是字段实际内容的长度而非定义字段时的长度。而定长字段，也就是 char 类型，它所占用的空间就是自定字段时的长度，若超过会被截取。

    举个例子，为上述实例表中添加一个字符类型字段的索引。

    alter table user add index idx_name(`name`);复制代码

    然后通过 name 字段去做查询，查看执行计划。

    mysql> explain select * from user where name='name1';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| 1 | SIMPLE | user | NULL | ref | idx_name | idx_name | 111 | const | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.01 sec)复制代码

    可以看到，执行计划中 key_len 一列的值为 111。

    根据上述索引长度的计算公式，name 列字段定义长度为36，字符集类型为默认的 utf8，该字段默认允许 NULL，同时该字段是可变长字段 varchar。

    所以 idx_name 索引的索引长度=36*3+1+2=111，恰如执行计划中显示的值。

    2.4.2 其他定长类型

    对于定长类型的字段，其索引长度与它的数据类型长度是一致的。

    数据类型长度
    int4
    bigint8
    date3
    datetime8
    timestamp4
    float4
    double8

    需要注意的是，若该字段允许默认值为 NULL，与字符类型一样，其索引长度也需要加上1。

    mysql> explain select * from user where age=1;
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| 1 | SIMPLE | user | NULL | ref | idx_age_name | idx_age_name | 5 | const | 39366 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码

    如上面这个示例（本示例中索引只用到了 age 字段），age 字段为 int 类型，其索引长度本应为 4，但由于 age 字段默认允许为 NULL，所以它的索引长度就变成了5。

    2.5 rows in Explain

    扫描行数在执行计划中其实是一个估值，MySQL 会选择 N 个不同的索引数据页，计算平均值得到单页索引基数，然后再乘以索引页面数，就得到了扫描行数的估值。

    扫描行数就是优化器考量索引执行效率的因素之一，一般而言扫描行数越少，执行效率越高。

    2.6 Extra in Explain

    执行计划中 Extra 字段的常见类型有：

  • Using index: 使用了覆盖索引，以避免回表
  • Using index condition: 使用了索引下推，具体可以看我的上一篇博客 —— 我所理解的MySQL(二)索引
  • Using where: 表示MySQL 会通过 where 条件过滤记录
  • 全表扫描：where 中有该表字段作为搜索条件
  • 扫描索引树：where 中包含索引字段之外的其他字段作为搜索条件
  • Using temporary: MySQL 在对查询结果排序时会使用临时表
  • Using filesort: 对结果进行外部索引排序（文件排序），排序不走索引
  • 数据较少时在内存中排序，数据较多时在磁盘中排序
  • 尽量避免该信息出现在执行计划中

    • 相关免费学习推荐：mysql数据库(视频)

    热心网友 时间：2022-04-07 16:34

    mysql5.1以后有一个叫做event的命令。当到达指定时间就会执行你预先写好的sql语句。应该适合你用。
    下面是我前几天写的一个event句子正好用到，给你举个例子，需要的地方你自己修改一下
    delimiter |

    create event `event1`
    on schele
    at '2012-11-27 18:05:00'
    do
    begin

    UPDATE limit_master
    SET
    limit_value = 2,
    upd_date = now()
    WHERE
    limit_code = 0;

    end |
    delimiter ;
    这些是最基本的，我只是指定了时间。并且是一次执行，如果是每天执行的话在时间那里改为every + 你想要的时间（例如every 1 day / every 1 hour）。
    详细参照mysql手册，那里是最详细的

    热心网友 时间：2022-04-07 17:52

    若是08系统就比较方便，可以应用计算机上的“应用计划程序”，写一个定时任务。用记事本写上你所需的语句，再保存为cmd形式

    热心网友 时间：2022-04-07 19:26

    用触发器，或者写个脚本，放在定时任务中执行

    热心网友 时间：2022-04-07 21:18

    建立一个触发器吧，条件是23：59执行
    数据库自动把 Detail 表 中的数据 重新进行统计并插入到最后一行中这一操作

    热心网友 时间：2022-04-07 23:26

    mysql中有一个叫event的东西，你可以查一下文档，可以设置定时更新，执行一个过程的sql。

    热心网友 时间：2022-04-08 01:50

    额 每天怎么会有24:59呢追问sorry
    比如说 23:59分的时候！

    追答$insertId = mysql_insert_id();

    声明声明：本网页内容为用户发布，旨在传播知识，不代表本网认同其观点，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。E-MAIL:11247931@qq.com
    泰勒公式有哪些常见形式? 空调室内风机怎样工作 什么是县级以上医院 关于聚金宝 买白银问题 平安银行个人贵金属交易业务有哪些? 网易云音乐为什么歌这么少? 公路边种的有什么花 高速路上常用的绿化花卉有那些? 公路绿化用什么花 我一键开核后顺利进入WIN7 系统,可是一开网站就重启是怎么回事,我开6... 护栏没有预埋铁怎么安装? 16年过去,NBA“03白金一代”,前5顺位的现状如何? 微博的发送字数限制是多少? 愚人食盐中愚人之愚具体表现在?造成这一表现的原因? 愚人食盐这篇文中、为什么说这个人是“愚人” 大卫韦斯特职业生涯的总和同情况 桥梁护栏网安装如何安装 前NBA球星韦斯特沿街乞讨是怎么回事? 场地围网要怎么安装?详细的步骤是什么 还记得那位身家上亿的前NBA球星吗？破产后流浪街头，他近况如何了？ 为什么微博私信评论也有字数限制? 联通宽带,办理了200M的宽带,实测宽带50M什么情况。 如何删除索尼相机中的照片 联通200兆的宽带手机连接无线网测速只有50兆 200M宽带测速只有50M 索尼相机SR85E怎么删照片 索尼ilce6000l怎么批量删除照片? 索尼相机连电脑上怎么删照片!!! 电信宽带升级到200m,速度却是50m,原因是什么? 我家的宽带是200m,测试出来只有50m宽带的下载速度 桥梁护栏网的作用有哪些? 愚人食盐的寓意是什么?? 11岁孩子的心理问题 从新闻里得知自己被交易，德罗赞沉默发呆，魔兽是球迷告知，如今现状如何？ 愚人食盐 造成这一愚行的原因是什么 亨弗里斯是NBA防守悍将,曾娶卡戴珊为妻好风光,他现状如何? 在保护隐私的角度B站这些设置,要全选还是保持这样? 我儿子今年11岁,叛逆心理特别强,该不该打 有人知道Vivienne Westwood的发展史么。大神们帮帮忙 《愚人食盐》这个故事告诉我们一个什么道理? 五官人面天珠的含义? 愚人食盐这则故事告诉我们什么道理? 我儿子十一岁,有抑郁症非常自卑,他每天都说想死该怎么办? 维维安·韦斯特伍德的风格特色 水上桥梁施工未安装防护网违返哪条法规 愚人食盐这个小故事主要写了什么? 到底是vivienne westwood还是vivian westwood啊? 桥梁护栏网都有哪些 愚人食盐意思 米兰开发现状如何
    懂视 51dongshi.com 版权所有
    Copyright © 2019-2024