測試環境為 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.
沒有解決問題,試試搜尋本站其他內容