1、explain

mysql关联条件查询 mysql关联查询关键字_mysql


explain关键字用于查看sql语句的执行计划、有没有上索引、没有有做全标扫描等，expain查询的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

  • id:选择标识符，id越大，查询的优先级越高。id相同，则查询从上往下执行。
  • select_type:表示查询的类型。查询的类型有SIMPLE（简单查询，不使用uniton或者自查询）、PRIMARY（子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY）、UNION（UNION）、DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)、UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)、SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)、DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)、DERIVED(派生表的SELECT, FROM子句的子查询)、UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)。
  • table:显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称。
  • type:表示表的连接类型，常用的类型有： ALL（遍历全表）、index（遍历索引树）、range（只检索给定范围的行，使用一个索引来选择行）、 ref（表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值）、eq_ref（类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件）、const、system（当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system）、NULL（MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成，性能最好）
  • possible_keys:指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）
  • key:表示实际使用的索引：key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中，如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
  • key_len:表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
  • ref:列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
  • rows:估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
  • Extra:执行情况的描述和说明

2、SQL优化思路

对于一个SQL语句，查询优化器先看是不是能转换成JOIN，再将JOIN进行优化
优化分为：1. 条件优化，2.计算全表扫描成本，3. 找出所有能用到的索引，4. 针对每个索引计算不同的访问
方式的成本，5. 选出成本最小的索引以及访问方式

2.1、开启优化器日志

set optimizer_trace="enabled=on";
-- 执行sql
-- 查询日志信息
select * from information_schema.OPTIMIZER_TRACE;
--关闭
set optimizer_trace="enabled=off";

2.2、常量传递（constant-propagation）

a = 1 AND b > a     转化为    a = 1 AND b > 1

2.3、等值传递

a = b and b = c and c = 5   转换为   = 5 and b = 5 and c = 5

2.4、移除无用的条件

a = 1 and 1 = 1  转换为   a = 1

2.5 、基于成本

一个查询可以有不同的执行方案，可以选择某个索引进行查询，也可以选择全表扫描，查询优化器会选择其中成本最低的方案去执行查询。

2.5.1、I/O成本

InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载
到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。

2.5.2、CPU成本

读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。
InnoDB存储引擎规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默
认是0.2。

2.5.3、基于成本的优化步骤

在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询。
下边我们就以一个实例来分析一下这些步骤，单表查询语句如下：

select * from employees.titles where emp_no > '10101' and emp_no < '20000' and to_date
= '1991-10-10';
1、根据查询条件，找出所有可能使用的索引
   emp_no > '10101'，这个搜索条件可以使用主键索引PRIMARY。
   to_date = '1991-10-10'，这个搜索条件可以使用二级索引idx_titles_to_date。
   综上所述，上边的查询语句可能用到的索引，也就是possible keys只有PRIMARY和idx_titles_to_date。
2、计算全表扫描的代价
对于InnoDB存储引擎来说，全表扫描的意思就是把聚簇索引中的记录都依次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集，所以需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。由于查询成本=I/O成本+CPU成本，所以计算全表扫描的代价需要两个信息：
1. 聚簇索引占用的页面数
2. 该表中的记录数
MySQL为每个表维护了一系列的统计信息， SHOW TABLE STATUS 语句来查看表的统计信息。
SHOW TABLE STATUS LIKE 'titles';
Rows
表示表中的记录条数。对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来
说，该值是一个估计值。
Date_length
表示表占用的存储空间字节数。使用MyISAM存储引擎的表来说，该值就是数据文件的大小，对于使用InnoDB存
储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：
Data_length = 聚簇索引的页面数量✖️每个页面的大小
我们的titles使用默认16KB的页面大小，而上边查询结果显示Data_length的值是20512768，所以我们可以反向来
推导出聚簇索引的页面数量：
聚簇索引的页面数量 = Data_length ÷ 16 ÷ 1024 = 20512768 ÷ 16 ÷ 1024 = 1252
我们现在已经得到了聚簇索引占用的页面数量以及该表记录数的估计值，所以就可以计算全表扫描成本了。但是
MySQL在真实计算成本时会进行一些微调。
I/O成本：1252*1 = 1252。1252指的是聚簇索引占用的页面数，1.0指的是加载一个页面的成本常数。
CPU成本：442070*0.2=88414。442070指的是统计数据中表的记录数，对于InnoDB存储引擎来说是一个估计
值，0.2指的是访问一条记录所需的成本常数
总成本：1252+88414 = 89666。
综上所述，对于titles的全表扫描所需的总成本就是89666。

未完待续