mysql的explain分析sql性能

可在sql前加explain:

explain format=json sql;
explain format=tree sql;
explain sql;

-- 其中shop_tid 是唯一键
explain select * from oms_order_____1d where shop_tid ='id';

-- 查询结果
id|select_type|table           |partitions|type |possible_keys          |key                    |key_len|ref  |rows|filtered|Extra|
--|-----------|----------------|----------|-----|-----------------------|-----------------------|-------|-----|----|--------|-----|
 1|SIMPLE     |oms_order_____1d|          |const|dwd_trd_order_all_1d_un|dwd_trd_order_all_1d_un|402    |const|   1|   100.0|     |

id: 执行顺序

id相同，从上往下依次执行
id不同，id越大优先级越高，越先被执行

select_type：查询的类型

1. SIMPLE：简单查询，查询中不包含子查询和UNION查询
2. PRIMARY: 如果存在子查询，PRIMARY就是最外层的查询
3. SUBQUERY: 如果存在子查询，SUBQUERY就是内层的子查询
4. DERIVED:在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归的执行这些子查询，把结果放在临时表中
5. UNION:若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT被标记为：DERIVED
6. UNION RESULT：从UNION表获得结果的SELECT

table : 查询涉及的表或衍生表

partitions:查询涉及到的分区

type： 访问类型

从最好到最差依次是：System > const > eq_ref > ref > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

system: 表中只有一条数据，相当于系统表； 这个类型是特殊的 const 类型；
const:表示通过索引一次就找到了，const用于比较primary key或者unique索引。如将主键置于where列表中，MySQL就能将该查询转化为一个常量。
eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
ref:非唯一性索引扫描，返回匹配某个单独值得所有行。
range:只检索给定范围的行，使用一个索引来选择行。Key列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in等的查询。
index:只遍历索引数。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然ALL和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
ALL:遍历全表以找到匹配的行 

possible_keys：

显示可能应用在这张表中的索引，一个或多个，但不一定会被查询实际使用

key：

实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中
覆盖索引：select的数据列的字段、顺序包含于所建立索引的字段、顺序，只用从索引中就能够得到，不用访问数据行，即查询的列要被所建立的索引覆盖

key_ len：

表示索引中所使用的字节数，可以通过该列计算查询中使用的索引字段的最大可能长度，并非实际使用长度。在不损失精确性的情况下，该值越小越好。

ref：

显示索引的哪些列被使用了，如果可能的话，是一个常数。哪些列或常数被用于查找索引列上的值。

rows：

根据表统计信息及索引选用情况，大致估算出找到所需的记录需要的行数。优化时此值越小越好。

filtered：返回结果的行数占读取行数的百分比，值越大越好

filtered=符合条件的行/rows*100

Extra：包含不适合在其他列显示，但又十分重要的信息

Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内事先建立的索引顺序进行读取。出现此信息表明排序部分出现问题，常产生于order by，需立即进行优化，如将order by后的字段顺序与所建索引的顺序相同，不要越级（见上图）。
Using temporary：使用了临时表来保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。此信息在表数据量很大时，对性能有很大的影响，应立即进行优化。优化原理大致同上
Using index：表示相应的select操作中使用了覆盖索引，避免了访问表的数据行，表示效率不错。如果同时出现using where，表明索引被用来执行键值的查找；若没有，则表面索引用来读取数据。
Using where：表明使用了where过滤。
Using join buffer：表明使用了连接缓存。
Impossible where：表明where子句的值总是false，不能用来获取任何元组。
Select tables optimized away：在没有group by子句的情况下，基于索引优化MIN/MAX操作或对于MyISAM存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即产生优化。
Distinct：优化distinct操作，在找到第一匹配的元组后停止对相同值的查找。

---

count() 查询效率：count(字段)<count(主键id)<count(1)≈count(*)=count(0)

SELECT COUNT(*) ：通过最小索引树遍历后不需要返回行数据直接+1
SELECT COUNT(1) :  count(1) 是需要返回数据的但是不做为空判断+1
SELECT COUNT(字段): count(字段) 是需要返回数据的但是不做为空判断+1，相比SELECT COUNT(1)占用的内存更多

所以尽量使用count(*)

---

CBO的工作原理

MySQL 数据库由 Server 层和 Engine 层组成：

Server 层有 SQL 分析器、SQL优化器、SQL 执行器，用于负责 SQL 语句的具体执行过程；
Engine 层负责存储具体的数据，如最常使用的 InnoDB 存储引擎，还有用于在内存中存储临时结果集的 TempTable 引擎。

SQL 优化器会分析所有可能的执行计划，选择成本最低的执行，这种优化器称之为：CBO（Cost-based Optimizer，基于成本的优化器）。

在 MySQL中，一条 SQL 的计算成本计算如下所示：

Cost  = Server Cost + Engine Cost
      = CPU Cost + IO Cost
      
其中，CPU Cost 表示计算的开销，比如索引键值的比较、记录值的比较、结果集的排序……这些操作都在 Server 层完成；
IO Cost 表示引擎层 IO 的开销，MySQL 8.0 可以通过区分一张表的数据是否在内存中，分别计算读取内存 IO 开销以及读取磁盘 IO 的开销。

数据库 mysql 下的表 server_cost、engine_cost 则记录了对于各种成本的计算，如：

select * from mysql.server_cost;

表 server_cost 记录了 Server 层优化器各种操作的成本，这里面包括了所有 CPU Cost，其具体含义如下：
disk_temptable_create_cost：创建磁盘临时表的成本，默认为20。
disk_temptable_row_cost：磁盘临时表中每条记录的成本，默认为0.5。
key_compare_cost：索引键值比较的成本，默认为0.05，成本最小。
memory_temptable_create_cost：创建内存临时表的成本：默认为1。
memory_temptable_row_cost：内存临时表中每条记录的成本，默认为0.1。
row_evaluate_cost：记录间的比较成本，默认为0.1。

可以看到， MySQL 优化器认为如果一条 SQL 需要创建基于磁盘的临时表，则这时的成本是最大的，其成本是基于内存临时表的 20 倍。而索引键值的比较、记录之间的比较，其实开销是非常低的，但如果要比较的记录数非常多，则成本会变得非常大。

select * from mysql.engine_cost;

而表 engine_cost 记录了存储引擎层各种操作的成本，这里包含了所有的 IO Cost，具体含义如下:
io_block_read_cost：从磁盘读取一个页的成本，默认值为1。
memory_block_read_cost：从内存读取一个页的成本，默认值为0.25。

也就是说， MySQL 优化器认为从磁盘读取的开销是内存开销的 4 倍。

不过，上述所有的成本都是可以修改的，比如如果数据库使用是传统的 HDD 盘，性能较差，其随机读取性能要比内存读取慢 50 倍，那你可以通过下面的 SQL 修改成本：

INSERT INTO 
engine_cost(engine_name,device_type,cost_name,cost_value,last_update,comment) 
VALUES ('InnoDB',0,'io_block_read_cost',12.5,CURRENT_TIMESTAMP,'Using HDD for InnoDB');

FLUSH OPTIMIZER_COSTS; -- 重新加载
-- 只对修改之后新的连接生效，对修改之前已经建立的连接不生效（无论是否执行过FLUSH OPTIMIZER_COSTS语句）。

-- pay_time 是二级索引oms_order_all_1d_pay_time_IDX
explain format=json SELECT order_channel channel,count(*) order_count FROM `oms_order_all_1d` 
WHERE pay_time >= '2021-06-22 00:00:00' AND pay_time < '2021-06-24 00:00:00' AND (order_channel <> 'MA') GROUP BY `order_channel`;

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "45467.63" -- 总成本
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": false,
      "table": {
        "table_name": "oms_order_all_1d",
        "access_type": "range",
        "possible_keys": [
          "oms_order_all_1d_pay_time_IDX"
        ],
        "key": "oms_order_all_1d_pay_time_IDX",
        "used_key_parts": [
          "pay_time"
        ],
        "key_length": "6",
        "rows_examined_per_scan": 40052,
        "rows_produced_per_join": 36046,
        "filtered": "90.00",
        "index_condition": "((`bbcl_customer_test`.`oms_order_all_1d`.`pay_time` >= TIMESTAMP'2021-06-22 00:00:00') and (`bbcl_customer_test`.`oms_order_all_1d`.`pay_time` < TIMESTAMP'2021-06-24 00:00:00'))",
        "cost_info": {
          "read_cost": "41862.95",  # IO Cost(Engine Cost)
          "eval_cost": "3604.68", # CPU Cost(Server Cost)
          "prefix_cost": "45467.63", # 总成本
          "data_read_per_join": "455M" # 总的读取记录字节数
        },
        "used_columns": [
          "id",
          "pay_time",
          "order_channel"
        ],
        "attached_condition": "(`bbcl_customer_test`.`oms_order_all_1d`.`order_channel` <> 'MA')"
      }
    }
  }
}

---

案例1：未能使用创建的索引

MySQL 优化器永远是根据成本，选择出最优的执行计划。哪怕是同一条 SQL 语句，只要范围不同，优化器的选择也可能不同。 MySQL 会根据成本计算得到最优的执行计划。
如下面这两条 SQL：

-- pay_time 是二级索引oms_order_all_1d_pay_time_IDX

-- 未使用索引。因为此语句的查询范围大，优化器认为使用通过主键进行全表扫描的成本比通过二级索引 oms_order_all_1d_pay_time_IDX 的成本要低
explain SELECT order_channel channel,count(*) order_count 
FROM `oms_order_all_1d`
WHERE pay_time >= '2021-06-17 00:00:00' AND pay_time < '2021-06-24 00:00:00'
AND (order_channel <> 'MA') GROUP BY `order_channel`;
-- 查询结果
id|select_type|table           |partitions|type|possible_keys                |key|key_len|ref|rows  |filtered|Extra                       |
--|-----------|----------------|----------|----|-----------------------------|---|-------|---|------|--------|----------------------------|
 1|SIMPLE     |oms_order_all_1d|          |ALL |oms_order_all_1d_pay_time_IDX|   |       |   |399425|    45.0|Using where; Using temporary|

-- 使用索引
explain SELECT order_channel channel,count(*) order_count 
FROM `oms_order_all_1d`
WHERE pay_time >= '2021-06-22 00:00:00' AND pay_time < '2021-06-24 00:00:00'
AND (order_channel <> 'MA') GROUP BY `order_channel`;
-- 查询结果
id|select_type|table           |partitions|type |possible_keys                |key                          |key_len|ref|rows |filtered|Extra                                              |
--|-----------|----------------|----------|-----|-----------------------------|-----------------------------|-------|---|-----|--------|---------------------------------------------------|
 1|SIMPLE     |oms_order_all_1d|          |range|oms_order_all_1d_pay_time_IDX|oms_order_all_1d_pay_time_IDX|6      |   |40052|    90.0|Using index condition; Using where; Using temporary|

可以对未使用索引的语句强制使用索引，来对比成本

explain format=json SELECT order_channel channel,count(*) order_count 
FROM `oms_order_all_1d`
WHERE pay_time >= '2021-06-17 00:00:00' AND pay_time < '2021-06-24 00:00:00'
AND (order_channel <> 'MA') GROUP BY `order_channel`;

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "51932.71"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": false,
      "table": {
        "table_name": "oms_order_all_1d",
        "access_type": "ALL",
        "possible_keys": [
          "oms_order_all_1d_pay_time_IDX"
        ],
        "rows_examined_per_scan": 399373,
        "rows_produced_per_join": 179717,
        "filtered": "45.00",
        "cost_info": {
          "read_cost": "33960.97",
          "eval_cost": "17971.74",
          "prefix_cost": "51932.71",
          "data_read_per_join": "2G"
        },
        "used_columns": [
          "id",
          "pay_time",
          "order_channel"
        ],
        "attached_condition": "((`bbcl_customer_test`.`oms_order_all_1d`.`pay_time` >= TIMESTAMP'2021-06-17 00:00:00') and (`bbcl_customer_test`.`oms_order_all_1d`.`pay_time` < TIMESTAMP'2021-06-24 00:00:00') and (`bbcl_customer_test`.`oms_order_all_1d`.`order_channel` <> 'MA'))"
      }
    }
  }
}


-- FORCE INDEX(oms_order_all_1d_pay_time_IDX)强制使用索引oms_order_all_1d_pay_time_IDX
explain format=json SELECT order_channel channel,count(*) order_count 
FROM `oms_order_all_1d` FORCE INDEX(oms_order_all_1d_pay_time_IDX)
WHERE pay_time >= '2021-06-17 00:00:00' AND pay_time < '2021-06-24 00:00:00'
AND (order_channel <> 'MA') GROUP BY `order_channel`;

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "216012.46"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": false,
      "table": {
        "table_name": "oms_order_all_1d",
        "access_type": "range",
        "possible_keys": [
          "oms_order_all_1d_pay_time_IDX"
        ],
        "key": "oms_order_all_1d_pay_time_IDX",
        "used_key_parts": [
          "pay_time"
        ],
        "key_length": "6",
        "rows_examined_per_scan": 199686,
        "rows_produced_per_join": 179717,
        "filtered": "90.00",
        "index_condition": "((`bbcl_customer_test`.`oms_order_all_1d`.`pay_time` >= TIMESTAMP'2021-06-17 00:00:00') and (`bbcl_customer_test`.`oms_order_all_1d`.`pay_time` < TIMESTAMP'2021-06-24 00:00:00'))",
        "cost_info": {
          "read_cost": "198040.72",
          "eval_cost": "17971.74",
          "prefix_cost": "216012.46",
          "data_read_per_join": "2G"
        },
        "used_columns": [
          "id",
          "pay_time",
          "order_channel"
        ],
        "attached_condition": "(`bbcl_customer_test`.`oms_order_all_1d`.`order_channel` <> 'MA')"
      }
    }
  }
}


可以看到强制使用索引的成本比全表扫描的成本要高
成本上看，全表扫描低于使用二级索引。故，MySQL 优化器没有使用二级索引 oms_order_all_1d_pay_time_IDX。

为什么全表扫描比二级索引查询快呢？ 因为二级索引需要回表，当回表的记录数非常大时，成本就会比直接扫描要慢，因此这取决于回表的记录数。
所以，第二条 SQL 语句，只是时间范围发生了变化，但是 MySQL 优化器就会自动使用二级索引 oms_order_all_1d_pay_time_IDX了

---