簡單使用 MySQL 索引

• 2022 年 7 月 24 日
• 筆記
• MySQL

MySQL 索引

1 什麼是索引

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

2 常見索引分類

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. 當對表中的數據進行增加，修改，刪除的時候，索引也要同時進行維護，降低了數據的維護
     速度