数据库-索引优化--分析执行计划

1. 分析最基础的sql ---select * from itdragon_order_list where transaction_id = "81X97310V32236260E";

mysql> select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
+-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+---------------------+
| id    | transaction_id     | gross | net  | stock_id | order_status | descript | finance_descript | create_type | order_level | input_user | input_date          |
+-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+---------------------+
| 10000 | 81X97310V32236260E |   6.6 | 6.13 |        1 |           10 | ok       | ok               | auto        |           1 | itdragon   | 2017-08-18 17:01:49 |
+-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+---------------------+

mysql> explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | itdragon_order_list | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+

几个重要的参数

• select_type : 查询类型是简单查询，简单的select语句没有union和子查询。
• type : 连接类型，all表示采用全表扫描的方式。

type--这是一个非常重要的参数，连接类型，常见的有：all , index , range , ref , eq_ref , const , system , null 八个级别。性能从最优到最差的排序：system > const > eq_ref > ref > range > index > all。

• possible_keys : 可能用到索引为null。
• key : 实际用到索引是null。
• key_len : 索引长度当然也是null。
• ref : 没有哪个列或者参数和key一起被使用。
• Extra : 使用了where查询。

2. 为transaction_id创建索引

mysql> create unique index idx_order_transaID on itdragon_order_list (transaction_id);
mysql> explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table               | partitions | type  | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | itdragon_order_list | NULL       | const | idx_order_transaID | idx_order_transaID | 453     | const |    1 |      100 | NULL  |
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+

• 唯一索引打印的type值是const。表示通过索引一次就可以找到。即找到值就结束扫描返回查询结果。
• 普通索引打印的type值是ref。表示非唯一性索引扫描。找到值还要继续扫描，直到将索引文件扫描完为止。
  很显然 constx性能要优于ref