- 分析最基礎(chǔ)的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 |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
幾個重要的參數(shù)
- select_type : 查詢類型是簡單查詢数焊，簡單的select語句沒有union和子查詢。
- type : 連接類型，all表示采用全表掃描的方式尔当。
type--這是一個非常重要的參數(shù)诱建，連接類型褂痰，常見的有：all , index , range , ref , eq_ref , const , system , null 八個級別总寻。性能從最優(yōu)到最差的排序：system > const > eq_ref > ref > range > index > all谤草。
- possible_keys : 可能用到索引為null衙傀。
- key : 實際用到索引是null抬吟。
- key_len : 索引長度當(dāng)然也是null。
- ref : 沒有哪個列或者參數(shù)和key一起被使用统抬。
- Extra : 使用了where查詢火本。
- 為transaction_id創(chuàng)建索引
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。表示通過索引一次就可以找到聪建。即找到值就結(jié)束掃描返回查詢結(jié)果钙畔。
- 普通索引打印的type值是ref。表示非唯一性索引掃描金麸。找到值還要繼續(xù)掃描擎析，直到將索引文件掃描完為止。
很顯然 constx性能要優(yōu)于ref
