MySQL联合索引中不同区分度列的顺序对查询性能的影响-explain

结合15.8.2 EXPLAIN Statement，回顾一下explain语句。

explain可以对 SELECT, DELETE, INSERT, REPLACE, UPDATE, TABLE的执行过程进行分析展示，不过最常用来分析SELECT的执行过程。

看下对SELECT语句explain的字段含义：

Column	Meaning
select_type	The SELECT type
table	The table for the output row
partitions	The matching partitions ，非分区表为null
type	The join type
possible_keys	The possible indexes to choose
key	The index actually chosen
key_len	The length of the chosen key
ref	The columns compared to the index
rows	Estimate of rows to be examined
filtered	Percentage of rows filtered by table condition
Extra	Additional information

关于字段具体内容可参考：10.8.2 EXPLAIN Output Format

explain可指定输入格式为TREE：

explain format = TREE select * from flow where random_string = '00002c6e-f375-4c17-81ef-7cf446386a5b';

结果：

-> Filter: (flow.random_string = '00002c6e-f375-4c17-81ef-7cf446386a5b')  (cost=36116 rows=99568)
    -> Covering index skip scan on flow using idx_binaryint_randomstring over random_string = '00002c6e-f375-4c17-81ef-7cf446386a5b'  (cost=36116 rows=99568)

explain可指定输入格式为JSON：

explain format = JSON select * from flow where random_string = '00002c6e-f375-4c17-81ef-7cf446386a5b';

结果：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "36116.17"
    },
    "table": {
      "table_name": "flow",
      "access_type": "range",
      "possible_keys": [
        "idx_binaryint_randomstring"
      ],
      "key": "idx_binaryint_randomstring",
      "used_key_parts": [
        "binary_int",
        "random_string"
      ],
      "key_length": "1026",
      "rows_examined_per_scan": 99568,
      "rows_produced_per_join": 99568,
      "filtered": "100.00",
      "using_index_for_skip_scan": true,
      "cost_info": {
        "read_cost": "26159.38",
        "eval_cost": "9956.80",
        "prefix_cost": "36116.18",
        "data_read_per_join": "98M"
      },
      "used_columns": [
        "id",
        "random_string",
        "binary_int"
      ],
      "attached_condition": "(`test`.`flow`.`random_string` = '00002c6e-f375-4c17-81ef-7cf446386a5b')"
    }
  }
}