SQL 語法 EXPLAIN

測試環境為 CentOS 7 x86_64 虛擬機

當我們使用 SELECT 時要如何得知其搜尋效能? 這時候可以透過 EXPLAIN 來看.

先建立一個測試用資料庫 (testdb) , 與 tables (employee) 格式為 Name char(20), Dept char(20), jobTitle char(20) 各 20 個字元.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use testdb;
Database changed
MariaDB [testdb]> create table employee (Name char(20),Dept char(20),jobTitle char(20));
Query OK, 0 rows affected (0.02 sec)

MariaDB [testdb]> DESCRIBE employee; 
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| Name     | char(20) | YES  |     | NULL    |       |
| Dept     | char(20) | YES  |     | NULL    |       |
| jobTitle | char(20) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

新增幾筆資料.

MariaDB [testdb]> INSERT INTO employee VALUES ('Ben','Testing','Engineer');
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> INSERT INTO employee VALUES ('Tank','Sales','PM');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO employee VALUES ('Vic','Hardware' ,'Engineer');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO employee VALUES ('Lily','Hardware' ,'Sensor Engineer');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO employee VALUES ('Max','Testing' ,'Sensor Engineer');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> SELECT * FROM employee;
+------+----------+-----------------+
| Name | Dept     | jobTitle        |
+------+----------+-----------------+
| Ben  | Testing  | Engineer        |
| Tank | Sales    | PM              |
| Vic  | Hardware | Engineer        |
| Lily | Hardware | Sensor Engineer |
| Max  | Testing  | Sensor Engineer |
+------+----------+-----------------+
5 rows in set (0.00 sec)

為了比較 SELECT 的效能(使用 EXPLAIN 來驗證),所以在資料表結構中的 Name 資料欄位 設定成為 Primary , Unique & Index Key.

  • NONE (不使用 Primary , Unique , Index Key)
    MariaDB [testdb]> EXPLAIN SELECT * FROM employee WHERE Name LIKE 'Ben';
    +------+-------------+----------+------+---------------+------+---------+------+------+-------------+
    | id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
    +------+-------------+----------+------+---------------+------+---------+------+------+-------------+
    |    1 | SIMPLE      | employee | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
    +------+-------------+----------+------+---------------+------+---------+------+------+-------------+
    1 row in set (0.01 sec)
    

    後面我們再來一起比較 有無使用 Primary , Unique , Index Key 的差別.

  • Primary
    剛剛在建立資料表的時候並沒有設定 PRIMARY KEY,可以透過 ALTER 來修改.
    PRIMARY KEY (主鍵) 必須為 NOT NULL (不能為空) 並且要 UNIQUE (唯一).資料表只有一個欄位可以為 PRIMARY KEY.

    MariaDB [testdb]> ALTER TABLE employee ADD PRIMARY KEY(Name);
    Query OK, 5 rows affected (0.03 sec)               
    Records: 5  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> DESCRIBE employee;
    +----------+----------+------+-----+---------+-------+
    | Field    | Type     | Null | Key | Default | Extra |
    +----------+----------+------+-----+---------+-------+
    | Name     | char(20) | NO   | PRI |         |       |
    | Dept     | char(20) | YES  |     | NULL    |       |
    | jobTitle | char(20) | YES  |     | NULL    |       |
    +----------+----------+------+-----+---------+-------+
    3 rows in set (0.01 sec)
    
    MariaDB [testdb]> SHOW INDEX FROM employee;
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | employee |          0 | PRIMARY  |            1 | Name        | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    1 row in set (0.00 sec)
    

    使用 EXPLAIN 來驗證一下效能.

    MariaDB [testdb]> EXPLAIN SELECT * FROM employee WHERE Name LIKE 'Ben';
    +------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
    | id   | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
    +------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
    |    1 | SIMPLE      | employee | range | PRIMARY       | PRIMARY | 20      | NULL |    1 | Using where |
    +------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
    1 row in set (0.01 sec)
    

    後面我們再來一起比較 有無使用 Primary , Unique , Index Key 的差別.

    要移除 PRIMARY KEY ,一樣是透過 ALTER 指令.因為資料表中只能有一個欄位設定成為 PRIMARY KEY ,所以在 DROP 移除時就不需要再指定了,還需把 欄位恢復成為可以 NULL .

    MariaDB [testdb]> ALTER TABLE employee DROP PRIMARY KEY;
    Query OK, 5 rows affected (0.02 sec)               
    Records: 5  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> ALTER TABLE employee MODIFY name CHAR(20) NULL;
    Query OK, 5 rows affected (0.02 sec)               
    Records: 5  Duplicates: 0  Warnings: 0
    
  • UNIQUE
    UNIQUE KEY(唯一鍵) 不能重覆,但是可以為 NULL (空),一樣是透過 LATER 修改.

    MariaDB [testdb]> ALTER TABLE employee ADD UNIQUE (Name);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> DESCRIBE employee;
    +----------+----------+------+-----+---------+-------+
    | Field    | Type     | Null | Key | Default | Extra |
    +----------+----------+------+-----+---------+-------+
    | name     | char(20) | YES  | UNI | NULL    |       |
    | Dept     | char(20) | YES  |     | NULL    |       |
    | jobTitle | char(20) | YES  |     | NULL    |       |
    +----------+----------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
    
    MariaDB [testdb]> SHOW INDEX FROM employee;
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | employee |          0 | name     |            1 | name        | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    1 row in set (0.00 sec)
    

    使用 EXPLAIN 來驗證一下效能.

    MariaDB [testdb]> EXPLAIN SELECT * FROM employee WHERE Name LIKE 'Ben';
    +------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
    | id   | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
    +------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
    |    1 | SIMPLE      | employee | range | name          | name | 21      | NULL |    1 | Using index condition |
    +------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
    1 row in set (0.00 sec)
    

    後面我們再來一起比較 有無使用 Primary , Unique , Index Key 的差別.

    移除 UNIQUE

    MariaDB [testdb]> ALTER TABLE employee DROP INDEX Name;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
  • Index Key
    Index Key (索引鍵) ,常做存取的的欄位可以設定成為索引,這樣可以增加搜尋速度,一樣是透過 LATER 修改.

    MariaDB [testdb]> ALTER TABLE employee ADD INDEX (Name);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [testdb]> DESCRIBE employee;
    +----------+----------+------+-----+---------+-------+
    | Field    | Type     | Null | Key | Default | Extra |
    +----------+----------+------+-----+---------+-------+
    | name     | char(20) | YES  | MUL | NULL    |       |
    | Dept     | char(20) | YES  |     | NULL    |       |
    | jobTitle | char(20) | YES  |     | NULL    |       |
    +----------+----------+------+-----+---------+-------+
    3 rows in set (0.01 sec)
    
    MariaDB [testdb]> SHOW INDEX FROM employee;
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | employee |          1 | name     |            1 | name        | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    1 row in set (0.00 sec)
    

    使用 EXPLAIN 來驗證一下效能.

    MariaDB [testdb]> EXPLAIN SELECT * FROM employee WHERE Name LIKE 'Ben';
    +------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
    | id   | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
    +------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
    |    1 | SIMPLE      | employee | range | name          | name | 21      | NULL |    1 | Using index condition |
    +------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
    1 row in set (0.00 sec)
    

    後面我們再來一起比較 有無使用 Primary , Unique , Index Key 的差別.

    移除 index.

    MariaDB [testdb]> ALTER TABLE employee DROP INDEX Name;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    

來比較一下有無使用 Primary , Unique , Index Key 的差別.

NONE:

+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | employee | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+

PRIMARY KEY:

+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | employee | range | PRIMARY       | PRIMARY | 20      | NULL |    1 | Using where |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+

UNIQUE KEY:

+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
| id   | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
|    1 | SIMPLE      | employee | range | name          | name | 21      | NULL |    1 | Using index condition |
+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+

INDEX KEY:

+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
| id   | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
|    1 | SIMPLE      | employee | range | name          | name | 21      | NULL |    1 | Using index condition |
+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+

可以看到有差別的欄位是 type , possible_keys , key , key_len , rows , Extra ,欄位所代表的意涵如下:

Column name Description
id Sequence number that shows in which order tables are joined.
select_type What kind of SELECT the table comes from.
table Alias name of table. Materialized temporary tables for sub queries are named <subquery#>
type How rows are found from the table (join type).
possible_keys keys in table that could be used to find rows in the table
key The name of the key that is used to retrieve rows. NULL is no key was used.
key_len How many bytes of the key that was used (shows if we are using only parts of the multi-column key).
ref The reference that is used to as the key value.
rows An estimate of how many rows we will find in the table for each key lookup.
Extra Extra information about this join.

其他敘述請參考 – https://mariadb.com/kb/en/library/explain/

最主可以看 rows 欄位的值(越小代表執行效能越好) , NONE (無使用 Primary , Unique , Index Key) 的時候為 5,因為當我們搜索 Name 時並沒有建立索引,整個資料表搜尋了 5筆才找到資料.相反的使用 Primary , Unique , Index Key 時則為 1.

沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料