草庐IT

MySQL：连Explain的Type类型都没搞清楚，怎敢说精通SQL优化？

程序员拾山 2023-03-28 原文

我们在使用SQL语句查询表数据时，提前用explain进行语句分析是一个非常好的习惯。通过explain输出sql的详细执行信息，就可以针对性的进行sql优化。

今天我们来分析一下，在explain中11种不同type代表的含义以及其应用场景。

1、system

应用场景：表中只有一条数据，且存储引擎可以准确的统计到这条数据。

system一般出现在MyISAM、memory类型的表查询中。

由于我们一般使用的存储引擎都是InnoDB，所以system这种类型很少会用到。

2、const

应用场景：通过主键或者唯一索引等值查询来定位一条数据。

比如：select * from test where id = 1。

我们知道，MySQL底层使用B+树来保存数据，其结构大体可类似下图，

那么我们在m字段上创建唯一索引约束，如果想找到m=103的记录，通过二分法只需简单两步就可以定位到m=103。

即100->102->103。

即使对于一张记录很多的真正的业务表，因为B+树矮胖的结构，定位一条唯一索引中的记录，速度也是非常快的。

可以粗略的认为，这种查询速度是常数级的。

所以，MySQL就把这种唯一索引或主键（主键也是一种唯一索引）等值匹配的查询定义为const（常数级）。

需要注意的是，由于唯一索引中允许存在多个null值，所以如果对唯一索引进行null值查询，是没法用const的。

3、eq_ref

应用场景：在进行多表连接查询时，被驱动表通过主键或唯一索引键进行等值查询。

比如：SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id。

4、ref

应用场景：普通二级索引等值查询。

比如：select * from t2 where key2 =4。

除了唯一索引，我们更多的会使用普通的二级索引。

由于通过二级索引，可能会查询到多个匹配值，相比const性能差那么一点。

MySQL就把这种类型的查询定义为了ref。

在上面我们说到，由于唯一索引可能存在多个null，所以用不了const。

那对于 select * from t2 where key2 is null 来说，不管是唯一索引还是普通索引，其最多用到ref这种类型。

5、ref_or_null

​应用场景：命中索引时，查询条件除了等值查询，还包含null值查询。

比如：select * from t2 where key2 =4 or key2 is null。

其实看名字就很容易理解，MySQL会在B+树上，找到key2=1和key2 is null 这两种记录范围值，然后拿到主键id去回表查询相关信息。

6、index_merge

应用场景：查询条件可以命中多个索引的情况。

比如：select * from t3 where key1 =3 or key2 =4、

索引合并其实也很好理解，当查询条件可以命中多个索引时，MySQL会尝试在两个索引树查找匹配的条件，然后将结果其合并起来。

7、unique_subquery

应用场景：查询条件包含子查询，并且子查询的列可以进行主键等值匹配。

比如：SELECT * FROM t2 WHERE t2.key2 IN ( SELECT id FROM t3 WHERE t2.key2 = t3.key2 ) OR t2.key2 = 1。

通过查看MySQL优化的执行sql，可以看到MySQL将in子查询优化为了exist语句，并且在主键索引上进行了等值查询。

MySQL优化后的语句：/* select#1 */ select `dbs`.`t2`.`id` AS `id`,`dbs`.`t2`.`key2` AS `key2` from `dbs`.`t2` where (<in_optimizer>(`dbs`.`t2`.`key2`,<exists>(<primary_index_lookup>(<cache>(`dbs`.`t2`.`key2`) in t3 on PRIMARY where ((`dbs`.`t2`.`key2` = `dbs`.`t3`.`key2`) and (<cache>(`dbs`.`t2`.`key2`) = `dbs`.`t3`.`id`))))) or (`dbs`.`t2`.`key2` = 1))。

8、index_subquery

应用场景：查询条件包含子查询，并且子查询的列可以通过索引进行等值匹配。

比如：SELECT * FROM t2 WHERE t2.key2 IN ( SELECT key1 FROM t3 WHERE t2.key2 = t3.key2 ) OR t2.key2 = 1。

index_subquery和unique_subquery的区别在于子查询中的列是唯一索引还是普通的二级索引。

9、range

应用场景：命中索引时，查询某一个范围内的结果。

比如：select * from t3 where t3.key1 >1 and t3.key1<3。

在实际的业务场景中，对某个列进行范围查询还是很常见的需求。

10、index

应用场景：直接在某个索引树上做条件判断，并且不需要回表。

比如：select t3.key1 from t3 where t3.key2 =6。

当我们创建了联合索引idx_key1_key2(key1,key2)时，判断条件key2=6时，其虽然不满足索引的最左前缀原则，但是我们可以遍历idx_key1_key2这颗索引树，找到key2=6的记录即可。

由于查询结果需要的key1在这个联合索引上，也不需要回表，此时就可以使用index。

相对来说，index的性能是比较慢的。

11、all

应用场景：直接遍历整个聚簇索引。

比如： select * from t1。

当MySQL无法通过where条件匹配到合适的索引或者因为全部扫描的代价更小时，MySQL就会选择all这种类型来全表扫描。

这种方式也是最不推荐的。

最后

总得来说，我们在进行查询时，查询类型可分为两大类：全部扫描和索引查询。

索引查询又可以细分：

  1. 唯一索引等值查询。
  2. 普通索引等值查询。
  3. 普通索引范围查询。
  4. 扫描整个索引树。
对于一条查询sql来说，不同的查询类型虽然结果可能是一样的，但是其性能却可能天差地别。

不同类型性能从强到差：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all。

建议大家在平时书写sql时，多用explain进行分析，尝试去优化代码，只有不断的实践，才能让自己的sql能力越来越强。

精通Explainkeykey2style数据库MySQL$MySQLSQL优化

有关MySQL：连Explain的Type类型都没搞清楚，怎敢说精通SQL优化？的更多相关文章

  1. ruby-on-rails - Rails 3.2.1 中 ActionMailer 中的未定义方法 'default_content_type=' - 2

    我在我的项目中添加了一个系统来重置用户密码并通过电子邮件将密码发送给他，以防他忘记密码。昨天它运行良好(当我实现它时)。当我今天尝试启动服务器时，出现以下错误。=>BootingWEBrick=>Rails3.2.1applicationstartingindevelopmentonhttp://0.0.0.0:3000=>Callwith-dtodetach=>Ctrl-CtoshutdownserverExiting/Users/vinayshenoy/.rvm/gems/ruby-1.9.3-p0/gems/actionmailer-3.2.1/lib/action_mailer

  2. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  3. ruby-on-rails - Rails 单表继承 : How to override the value written to the type field - 2

    在我的系统中，我已经定义了STI。Dog继承自Animal，在animals表中有一个type列，其值为"Dog"。现在我想让SpecialDog继承自dog，只是为了在某些特殊情况下稍微修改一下行为。数据还是一样。我需要通过SpecialDog运行的所有查询，以返回数据库中类型为Dog的值。我的问题是因为我有一个type列，rails将WHERE"animals"."type"IN('SpecialDog')附加到我的查询中，所以我不能获取原始的Dog条目。所以我想要的是以某种方式覆盖rails在通过SpecialDog访问数据库时使用的值，使其表现得像Dog。有没有办法覆盖用于类型

  4. Hive SQL 五大经典面试题 - 2

    目录第1题连续问题分析：解法：第2题分组问题分析：解法：第3题间隔连续问题分析：解法：第4题打折日期交叉问题分析：解法：第5题同时在线问题分析：解法：第1题连续问题如下数据为蚂蚁森林中用户领取的减少碳排放量iddtlowcarbon10012021-12-1212310022021-12-124510012021-12-134310012021-12-134510012021-12-132310022021-12-144510012021-12-1423010022021-12-154510012021-12-1523.......找出连续3天及以上减少碳排放量在100以上的用户分析：遇到这类

  5. sql - 查询忽略时间戳日期的时间范围 - 2

    我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

  6. ruby-on-rails - 无法安装 mysql2 0.3.14 gem - 2

    我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby​​目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin

  7. ruby - 如何使用 ruby​​ mysql2 执行事务 - 2

    我已经开始使用mysql2gem。我试图弄清楚一些基本的事情——其中之一是如何明确地执行事务(对于批处理操作，比如多个INSERT/UPDATE查询)。在旧的ruby-mysql中，这是我的方法:client=Mysql.real_connect(...)inserts=["INSERTINTO...","UPDATE..WHEREid=..",#etc]client.autocommit(false)inserts.eachdo|ins|beginclient.query(ins)rescue#handleerrorsorabortentirelyendendclient.commi

  8. sql - 在 Rails Console for PostgreSQL 的表中显示数据 - 2

    我找到了这样的东西:Rails:Howtolistdatabasetables/objectsusingtheRailsconsole?这一行没问题:ActiveRecord::Base.connection.tables并返回所有表但是ActiveRecord::Base.connection.table_structure("users")产生错误:ActiveRecord::Base.connection.table_structure("projects")我认为table_structure不是Postgres方法。如何列出Postgres数据库的Rails控制台中表中的所有

  9. ruby - 防止SQL注入(inject)/好的Ruby方法 - 2

    Ruby中防止SQL注入(inject)的好方法是什么？ 最佳答案 直接使用ruby？使用准备好的语句:require'mysql'db=Mysql.new('localhost','user','password','database')statement=db.prepare"SELECT*FROMtableWHEREfield=?"statement.execute'value'statement.fetchstatement.close 关于ruby-防止SQL注入(inject

  10. ruby - Formtastic，拥有 :as input type - 2

    如何将自己的字段类型添加到formtastic中？例如，我需要一个自定义的日期时间输入，我想要这样的东西::my_date%>这显然是行不通的，因为formtastic不知道:my_date(只有:boolean、:string、:datetime等等...)但是我怎样才能添加额外的输入类型呢？ 最佳答案 您需要添加自定义输入法:classMyCustomFormtasticFormBuilder这非常适合新的HTML5输入类型。你可以这样使用它:MyCustomFormtasticFormBuilderdo|f|%>:my_dat

随机推荐