MySQL 索引

1 什麼是索引

• 在資料庫表中，對欄位建立索引可以大大提高查詢速度。通過善用這些索引，可以令 MySQL 的查詢和
  執行更加高效。
• 如果合理的設計且使用索引的 MySQL 是一輛蘭博基尼的話，那麼沒有設計和使用索引的 MySQL 就是
  一個人力三輪車。拿漢語字典的目錄頁（索引）打比方，我們可以按拼音、筆畫、偏旁部首等排序的目
  錄（索引）快速查詢到需要的字

---

2 常見索引分類

索引名稱	說明
主鍵索引（primary key）	主鍵是一種唯一性索引,每個表只能有一個主鍵, 用於標識資料表中的每一條記錄
唯一索引 （unique）	唯一索引指的是 索引列的所有值都只能出現一次, 必須唯一.
普通索引 （index）	最常見的索引,作用就是 加快對資料的存取速度

MySql 將一個表的索引都儲存在同一個索引檔案中, 如果對中資料進行增刪改操作,MySql 都會自動的更
新索引.

2.1 主鍵索引 (PRIMARY KEY)

特點: 主鍵是一種唯一性索引,每個表只能有一個主鍵,用於標識資料表中的某一條記錄。

• 一個表可以沒有主鍵，但最多隻能有一個主鍵，並且主鍵值不能包含NULL。

• 建立一個只有欄位的資料庫表 demo01

CREATE TABLE demo01(
	did INT,
	dname VARCHAR(20),
	hobby VARCHAR(30)
);

建立主鍵索引

• 語法格式

• 建立表的時候直接新增主鍵索引 (最常用)

CREATE TABLE 表名(
	-- 新增主鍵 (主鍵是唯一性索引,不能為null,不能重複,)
	欄位名 型別 PRIMARY KEY,
);

• 修改表結構 新增主鍵索引

ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 )

• 為 demo1 表新增主鍵索引

ALTER TABLE demo01 ADD PRIMARY KEY ( did )

---

2.2 唯一索引(UNIQUE)

特點: 索引列的所有值都只能出現一次, 必須唯一.

• 唯一索引可以保證資料記錄的唯一性。事實上，在許多場合，人們建立唯一索引的目的往往不是為了 提高存取速度，而只是為了避免資料出現重複。

建立唯一索引

語法格式

• 建立表的時候直接新增主鍵索引

CREATE TABLE 表名(
	列名 型別(長度),
	-- 新增唯一索引
	UNIQUE [索引名稱] (列名)
);

• 使用 create 語句建立: 在已有的表上建立索引

create unique index 索引名 on 表名(列名(長度))

• 修改表結構新增索引

ALTER TABLE 表名 ADD UNIQUE ( 列名 )

• 為 hobby 欄位新增唯一索引

create unique index ind_hobby on demo01(hobby)

測試

• 向表中插入資料

INSERT INTO demo01 VALUES(1,'張三','DBJ');
# 報錯Duplicate entry 'DBJ' for key 'hobby'
# 唯一索引保證了資料的唯一性,索引的效率也提升了
INSERT INTO demo01 VALUES(2,'李四','DBJ');

---

3 普通索引 (INDEX)

• 普通索引（由關鍵字 KEY 或 INDEX 定義的索引）的唯一任務是加快對資料的存取速度。
• 因此，應該只為那些最經常出現在查詢條件（WHERE column=）或排序條件（ORDERBY column）中的資料列建立
  索引。

建立普通索引

語法格式

• 使用 create index 語句建立: 在已有的表上建立索引

create index 索引名 on 表名(列名[長度])

• 修改表結構新增索引

ALTER TABLE 表名 ADD INDEX 索引名 (列名)

• 給 dname 欄位新增索引

# 給dname欄位新增索引
alter table demo01 add index dname_indx(dname);

4 刪除索引

• 由於索引會佔用一定的磁碟空間，因此，為了避免影響資料庫的效能，應該及時刪除不再使用的索
  引

• 語法格式

ALTER TABLE table_name DROP INDEX index_name;

• 刪除 demo01 表中名為 dname_indx 的普通索引

ALTER TABLE demo01 DROP INDEX dname_indx;

---

3 索引效能測試

1 匯入資料表

• 下載測試 sql

• 找到軟體資料夾下的 測試索引.sql 檔案, 點選執行

• 執行出現以下問題

• Err 1153 - Got a packet bigger than 'max_allowed_packet' bytes

• 由於資料量較大，mysql 會對單表資料量較大的 SQL 做限制，10w 條資料的字串超出了 max_allowed_packet

  的允許範圍。

解決辦法

找到 ：max_allowed_packet 可以ctrl+f 搜尋 修改預設值為419430400 大概是400M

檢視我們是否修改成功

下面步入正題

• 查詢 test_index 表中的總記錄數 ·表中有 500萬條資料

SELECT COUNT(*) FROM test_index;

• 測試

在沒有新增索引的情況下, 使用 dname 欄位進行查詢

#未新增索引,進行分組查詢
SELECT * FROM test_index GROUP BY dname;

為dname欄位新增普通索引

• 注意: 一般我們都是在建立表的時候 就確定需要新增索引的欄位

#新增索引
ALTER TABLE test_index ADD INDEX dname_indx(dname);

4 索引的優缺點總結

• 新增索引首先應考慮在 where 及 order by 涉及的列上建立索引。
• 索引的優點
  1. 大大的提高查詢速度
  2. 可以顯著的減少查詢中分組和排序的時間。
• 索引的缺點
  1. 建立索引和維護索引需要時間，而且資料量越大時間越長
  2. 當對錶中的資料進行增加，修改，刪除的時候，索引也要同時進行維護，降低了資料的維護
     速度