【MYSQL】索引種類與說明

索引可以提高查詢的速度，對於常常查詢的欄位使用索引，有無設定的速度差可能會好幾倍。所以
範例1~例如：若資料上萬筆

SELECT * FROM table_name where datetime>= '2019-03-22 00:00:00'



AND datetime <= '2019-03-22 23:59:59';

如果在datetime欄位加上索引，和一個沒加索引若資料量龐大，兩個查詢的速度會差很多!

加上索引的，MYSQL會優先針對datetime去查詢，沒加索引的表，則是會全文搜尋。

索引(index)的類型

1.主鍵索引(primary key)。

語法：ALTER TABLE table_name ADD primary key (column) ;

• 不能為NULL
• 不能重覆
• 一個表只能有一個主鍵索引。

例如：用於，自動編號、身份證字號....等

2.唯一索引(unique index)。

語法：ALTER TABLE table_name ADD unique (column) ;
或CREATE UNIQUE INDEX index_name ON table_name(column);

• 可以為NULL 
• 不能重覆

例如：像是手機有號碼，E-MAIL....等，有些人不一定有手機或E-MAIL

3.一般索引、普通索引。

語法：（填加或直接創建）ALTER TABLE table_name ADD INDEX index_name (column) ;
或CREATE INDEX index_name ON table_name (column);

• 最基本的索引，無任何限制

4.組合索引。

語法：ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);

• 組合索引中最左邊的欄位column1一定要出現在條件中，才會使用索引

例如：以下兩個查詢會用到索引

SELECT * FROM table_name WHERE column1=val1;


SELECT * FROM table_name WHERE column1=val1 AND column2=val2;

以下兩個查詢不會用到索引，請注意條件中 column2並非索引的第一個欄位

SELECT * FROM tbl_name WHERE column2=val2;


SELECT * FROM tbl_name WHERE column2=val2 AND column3=val3;

5.全文索引。

語法：ALTER TABLE table_name ADD FULLTEXT (column);


或CREATE FULLTEXT INDEX index_name ON table_name(column);

查看索引，可以用三種方法：

SHOW {INDEX | INDEXES | KEYS} from table_name;

或是查看資料庫內的索引：

SHOW {INDEX | INDEXES | KEYS} from database_name;

或是針對表查看進行管理：

show create table 表名\G

刪除索引：

DROP INDEX index_name ON table_name;

特殊案例
測試EXPLAIN - 效能分析語句
在SELECT 前加入EXPLAIN 可以查出語句的執行效能
以下為例當我在ID上加上索引，實際查詢where id=20067，查詢時間很快

mysql> select id from users where id = 20067;
+-------+
| id    |
+-------+
| 20067 |
+-------+
1 row in set (0.00 sec)

mysql> explain select id,loginname from users where id = 20067;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | users | ref  | id            | id   | 4       | const |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

注意ID+1，其實都是查同一筆資料，但索引未產生效用MYSQL無法解析程式

mysql> select id from users where id +1 = 20068;
+-------+
| id    |
+-------+
| 20067 |
+-------+
1 row in set (0.88 sec)

mysql> explain select id,loginname from users where id +1 = 20068;
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | users | index | NULL          | id   | 4       | NULL | 576128 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)