Author: ACatSmiling
Since: 2024-09-21
索引的宣告與使用
索引的分類
MySQL 的索引包括普通索引、唯一性索引、全文索引、單列索引、多列索引和空間索引等。
- 從
功能邏輯上說，索引主要有 4 種：普通索引、唯一索引、主鍵索引、全文索引。 - 按照
物理實現方式，索引可以分為 2 種：聚簇索引和非聚簇索引。 - 按照
作用欄位個數進行劃分，分成單列索引和聯合索引。
普通索引
在建立普通索引時，不附加任何限制條件，只是用於提高查詢效率。這類索引可以建立在任何資料型別中，其值是否唯一和非空，要由欄位本身的完整性約束性條件決定。建立索引以後，可以透過索引進行查詢。例如，在表 student 的欄位 name 上建立一個普通索引，查詢記錄時就可以根據該索引進行查詢。
唯一性索引
使用UNIQUE 引數可以設定索引為唯一性索引，在建立唯一性索引時，限制該索引的值必須是唯一的，但允許有空值。在一張資料表裡，可以有多個唯一索引。例如，在表 student 的欄位 email 中建立唯一性索引，那麼欄位 email 的值就必須是唯一的。透過唯一性索引，可以快速確定某條記錄。
主鍵索引
主鍵索引就是一種特殊的唯一性索引，在唯一索引的基礎上增加了不為空的約束，也就是 NOT NULL + UNIQUE，一張表裡最多隻有一個主鍵索引。
單列索引
在表中的單個欄位上建立索引，單列索引只根據該欄位進行索引。單列索引可以是普通索引，也可以是唯一性索引，還可以是全文索引，只要保證該索引只對應一個欄位即可。一張表中，可以有多個單列索引。
多列（組合、聯合）索引
在表中的多個欄位組合上建立索引。多列索引指向建立時對應的多個欄位，可以透過這幾個欄位進行查詢，但是隻有查詢條件中使用了這些欄位中的第一個欄位時才會被使用。例如，在表 student 的欄位 id、name 和 gender 上建立一個多列索引 idx_id_name_gender，只有在查詢條件中使用了欄位 id 時，該索引才會被使用。使用多列索引時，遵循最左字首集合。
全文索引
全文索引，也稱全文檢索，是目前搜尋引擎使用的一種關鍵技術。它能夠利用分詞技術等多種演算法智慧分析出文字文字中關鍵詞的頻率和重要性，然後按照一定的演算法規則智慧的篩選出想要的搜尋結果。全文索引非常適合大型資料集，對於小的資料集，它的用處比較小。
使用引數FULLTEXT可以設定索引為全文索引，在定義索引的列上支援值的全文查詢，允許在這些索引列中插入重複值和空值。全文索引只能建立在CHAR、VARCHAR或TEXT型別及其系列型別的欄位上，查詢資料量較大的字串型別的欄位時，使用全文索引可以提高查詢速度。例如，表 student 的欄位 information 是 TEXT 型別，該欄位包含了很多文字資訊，在欄位 information 上建立全文索引後，可以提高查詢欄位 information 的速度。
全文索引典型的有兩種型別：自然語言的全文索引和布林全文索引。
- 自然語言搜尋引擎將計算每一個文件物件和查詢的相關度。這裡，相關度是基於匹配的關鍵詞的個數，以及關鍵詞在文件中出現的次數。
在整個索引中出現次數越少的詞語，匹配時的相關度就越高。相反，非常常見的單詞將不會被搜尋，如果一個詞語在超過 50% 的記錄中都出現了，那麼自然語言的搜尋將不會搜尋這類詞語。
MySQL 資料庫從 3.23.23 版開始支援全文索引，但在 MySQL 5.6.4 之前只有 MyISAM 支援，5.6.4 版本以後 InnoDB 才支援，但是官方版本不支援中文分詞，需要第三方分詞外掛。在 5.7.6 版本，MySQL 內建了ngram 全文解析器，用來支援亞洲語種的分詞。測試或使用全文檢索時，需要先檢視一下 MySQL 版本、儲存引擎和資料型別，是否支援全文索引。
隨著大資料時代的到來，關係型資料庫應對全文索引的需求已力不從心，逐漸被 Solr、ElasticSearch 等專門的搜尋引擎所替代。
補充：空間索引
使用引數SPATIAL可以設定索引為空間索引。空間索引只能建立在空間資料型別上，這樣可以提高系統獲取空間資料的效率。MySQL 中的空間資料型別包括GEOMETRY、POINT、LINESTRING和POLYGON等。目前只有 MyISAM 儲存引擎支援空間檢索，而且索引的欄位不能為空值。
小結
不同的儲存引擎支援的索引型別也不一樣。
- InnoDB：支援 B+Tree、Full-Text 等索引，不支援 Hash 索引；
- MyISAM：支援 B+Tree、Full-Text 等索引，不支援 Hash 索引；
- Memory：支援 B+Tree、Hash 等索引，不支援 Full-Text 索引；
- NDB：支援 Hash 索引，不支援 B+Tree、Full-Text 等索引；
- Archive：不支援 B+Tree、Hash、Full-Text 等索引。
建立索引
MySQL 支援多種方法在單個或多個列上建立索引：
- 在建立表的定義語句
CREATE TABLE中指定索引列。 - 使用
ALTER TABLE語句在存在的表上建立索引。 - 使用
CREATE INDEX語句在已存在的表上新增索引。
建立表的時候建立索引
使用 CREATE TABLE 建立表時，除了可以定義列的資料型別外，還可以定義主鍵約束、外來鍵約束或者唯一性約束，而不論建立哪種約束，在定義約束的同時，相當於在指定列上建立了一個索引。
隱式的索引建立：
# 1. 隱式的新增索引（在新增有主鍵約束、唯一性約束或者外來鍵約束的欄位會自動的建立索引）
CREATE TABLE dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT, # 建立主鍵索引
dept_name VARCHAR(20)
)
CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT, # 主鍵索引
emp_name VARCHAR(20) UNIQUE, # 唯一索引
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
) # 外來鍵索引
顯式的索引建立，基本語法格式如下，共有七種情況：
CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]
- UNIQUE、FULLTEXT 和 SPATIAL 為可選引數，分別表示唯一索引、全文索引和空間索引。
- INDEX 與 KEY 為同義詞，兩者的作用相同，用來指定建立索引。
- index_name 指定索引的名稱，為可選引數，如果不指定，那麼 MySQL 預設 col_name 為索引名。
- col_name 為需要建立索引的欄位列，該欄位列必須從資料表中定義的多個列中選擇。
- length 為可選引數，表示索引的長度，只有字串型別的欄位才能指定索引長度。
- ASC 或 DESC 指定升序或者降序的索引值儲存。
- 特例：主鍵索引使用主鍵約束的方式來建立。
建立普通索引：
在 book 表中的 year_publication 欄位上建立普通索引，SQL 語句如下：
CREATE TABLE book(
book_id INT,
book_name VARCHAR(100),
authors VARCHAR(100),
info VARCHAR(100) ,
comment VARCHAR(100),
year_publication YEAR,
# 宣告索引
INDEX idx_bname(year_publication)
)
建立唯一索引：
CREATE TABLE test(
id INT NOT NULL,
name varchar(30) NOT NULL,
UNIQUE INDEX uk_idx_id(id)
)
主鍵索引：
設定為主鍵後資料庫會自動建立索引，InnoDB 為聚簇索引，語法：
CREATE TABLE student (
# 透過定義主鍵約束的方式定義主鍵索引
id INT(10) UNSIGNED AUTO_INCREMENT ,
student_no VARCHAR(200),
student_name VARCHAR(200),
PRIMARY KEY(id)
)
刪除主鍵索引：
ALTER TABLE student DROP PRIMARY KEY
修改主鍵索引：必須先刪除掉（DROP）原索引，再新建（ADD）索引。
建立單列索引：
CREATE TABLE test2(
id INT NOT NULL,
name CHAR(50) NULL,
INDEX single_idx_name(name(20))
)
建立組合索引：
CREATE TABLE test3(
id INT(11) NOT NULL,
name CHAR(30) NOT NULL,
age INT(11) NOT NULL,
info VARCHAR(255),
INDEX multi_idx(id, name, age)
)
組合索引在查詢時會遵守
最左索引原則，先進行 id 條件的比較，然後再進行 name 比較，最後才是 age，因此注意把最常用的查詢欄位放在索引的最左邊。
建立全文索引：
FULLTEXT 全文索引可以用於全文搜尋，並且只為CHAR、VARCHAR和TEXT列建立索引。索引總是對整個列進行，不支援區域性（字首）索引。
CREATE TABLE test4(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX futxt_idx_info(info)
) ENGINE=MyISAM
在 MySQL 5.7 及之後版本中可以不指定最後的 ENGINE，因為在此版本中 InnoDB 支援全文索引。
建立一個給 title 和 body 欄位新增全文索引的表：
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR (200),
body TEXT,
FULLTEXT index (title, body)
) ENGINE=INNODB
CREATE TABLE `papers` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
`content` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `title` (`title`, `content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
全文索引用 MATCH + AGAINST 方式查詢：
SELECT * FROM papers WHERE MATCH(title, content) AGAINST (‘查詢字串’);
注意：
- 使用全文索引前，搞清楚版本支援情況；
- 全文索引比 LIKE + % 快 N 倍，但是可能存在精度問題；
- 如果需要全文索引的是大量資料，建議先新增資料，再建立索引。
建立空間索引：
空間索引建立中，要求空間型別的欄位必須為非空 。
CREATE TABLE test5(
geo GEOMETRY NOT NULL,
SPATIAL INDEX spa_idx_geo(geo)
) ENGINE=MyISAM
在已經存在的表上建立索引
在已經存在的表中建立索引可以使用ALTER TABLE語句或者CREATE INDEX語句。
使用 ALTER TABLE 語句建立索引：
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]
示例：
# 唯一索引
ALTER TABLE book ADD UNIQUE INDEX uk_idex_bid(book_id)
# 單列索引
ALTER TABLE book ADD INDEX inx_cmt(comment(50))
# 組合索引
ALTER TABLE book ADD INDEX idx_author_info(authors(30), info(50))
宣告主鍵索引：
CREATE TABLE customer2(
id INT(10) UNSIGNED,
customer_no VARCHAR(200),
customer_name VARCHAR(200)
)
ALTER TABLE customer2 ADD PRIMARY KEY customer2(id)
使用 CREATE INDEX 建立索引：
CREATE INDEX語句可以在已經存在的表上新增索引，在 MySQL 中，CREATE INDEX 被對映到一個 ALTER TABLE 語句上，基本語法結構為：
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]
示例：
# 普通索引
CREATE INDEX idx_cmt ON book(comment)
# 唯一索引
CREATE UNIQUE INDEX uk_idx_bid ON book(book_id)
# 組合索引
CREATE INDEX mul_bid_bname_info ON book(book_id, book_name, info)
刪除索引
MySQ L中刪除索引使用ALTER TABLE或DROP INDEX語句，兩者可實現相同的功能，DROP INDEX 語句在內部被對映到一個 ALTER TABLE 語句中。
使用 ALTER TABLE 刪除索引
語法：
ALTER TABLE table_name DROP INDEX index_name
示例：
# 檢視索引是否存在
SHOW INDEX FROM book\G
# 刪除索引
ALTER TABLE book DROP INDEX idx_bk_id
新增 AUTO_INCREMENT 約束欄位的唯一索引不能被刪除。
使用 DROP INDEX 語句刪除索引
語法：
DROP INDEX index_name ON table_name
示例：
# 刪除索引
DROP INDEX idx_aut_info ON book
# 檢視索引是否刪除
SHOW INDEX FROM book\G
刪除表中的列時，如果要刪除的列為索引的組成部分，則該列也會從索引中刪除。如果組成索引的所有列都被刪除，則整個索引將被刪除。
MySQL 8.0 索引新特性
支援降序索引
降序索引以降序儲存鍵值。雖然在語法上，從 MySQL 4 版本就已經支援降序索引，但實際上該 DESC 定義是被忽略的，直到 MySQL 8 版本才開始真正支援降序索引（僅限於 InnoDB 儲存引擎）。
MySQL 在 8.0 版本之前建立的仍然是升序索引，使用時進行反向掃描，這大大降低了資料庫的效率。在某些場景下，降序索引意義重大。例如，如果一個查詢，需要對多個列進行排序，且順序要求不一致，那麼使用降序索引，將會避免資料庫使用額外的檔案排序操作，從而提高效能。
示例：
mysql> CREATE TABLE ts1(a int, b int, index idx_a_b(a, b desc));
Query OK, 0 rows affected (0.04 sec)
mysql> SHOW CREATE TABLE ts1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| ts1 | CREATE TABLE `ts1` (
`a` int DEFAULT NULL,
`b` int DEFAULT NULL,
KEY `idx_a_b` (`a`,`b` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
從結果可以看出，索引已經是降序了。
向資料表 ts1 中插入 800 條隨機資料，執行語句如下：
mysql> DELIMITER //
mysql> CREATE PROCEDURE ts_insert()
-> BEGIN
-> DECLARE i INT DEFAULT 1;
-> WHILE i < 800
-> DO
-> insert into ts1 select rand()*80000,rand()*80000;
-> SET i = i + 1;
-> END WHILE;
-> commit;
-> END //
Query OK, 0 rows affected (0.02 sec)
mysql> DELIMITER ;
mysql> CALL ts_insert();
Query OK, 0 rows affected (3.95 sec)
在 MySQL 8.0 版本中檢視資料表 ts1 的執行計劃：
mysql> EXPLAIN SELECT * FROM ts1 ORDER BY a, b DESC LIMIT 5;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | ts1 | NULL | index | NULL | idx_a_b | 10 | NULL | 5 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
從結果可以看出，執行計劃中掃描數為 5，並且沒有使用 Using filesort。
可以對比 MySQL 5.0 版本中相同查詢的執行計劃，執行計劃中掃描數遠大於 MySQL 8.0，並且使用了 Using filesort 排序。
Using filesort 是 MySQL 中一種速度比較慢的外部排序，能避免是最好的。多數情況下，管理員可以透過最佳化索引來儘量避免出現 Using filesort，從而提高資料庫執行速度。
降序索引只對查詢中特定的排序順序有效，如果使用不當，反而查詢效率更低。例如，將上述查詢的排序條件改為ORDER BY a desc, b desc，然後在 MySQL 8.0 版本中檢視資料表 ts1 的執行計劃：
mysql> EXPLAIN SELECT * FROM ts1 ORDER BY a DESC,b DESC LIMIT 5;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | ts1 | NULL | index | NULL | idx_a_b | 10 | NULL | 799 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
此時再對比 MySQL 5.0 版本中相同查詢的執行計劃，會發現 MySQL 5.0 中執行計劃會優於 MySQL 8.0。
隱藏索引
在 MySQL 5.7 版本及之前，只能透過顯式的方式刪除索引。此時，如果發現刪除索引後出現錯誤，又只能透過顯式建立索引的方式將刪除的索引建立回來。如果資料表中的資料量非常大，或者資料表本身比較大，這種操作就會消耗系統過多的資源，操作成本非常高。
從 MySQL 8.x 開始支援隱藏索引（invisible indexes），只需要將待刪除的索引設定為隱藏索引，使查詢最佳化器不再使用這個索引（即使使用 force index（強制使用索引），最佳化器也不會使用該索引），確認將索引設定為隱藏索引後系統不受任何響應，就可以徹底刪除索引。這種透過先將索引設定為隱藏索引，再刪除索引的方式就是軟刪除。
同時，如果想驗證某個索引刪除之後的查詢效能影響，也可以暫時先隱藏該索引。
索引預設是可見的，在使用 CREATE TABLE，CREATE INDEX 或者 ALTER TABLE 等語句時，可以透過VISIBLE或者INVISIBLE關鍵詞設定索引的可見性。
主鍵不能被設定為隱藏索引，當表中沒有顯式主鍵時，表中第一個唯一非空索引會成為隱式索引，也不能設定為隱藏索引。
當索引被隱藏時，它的內容仍然是和正常索引一樣實時更新的。如果一個索引需要長期被隱藏，那麼可以將其刪除，因為索引的存在會影響插入、更新和刪除的效能。
建立表時直接建立
在 MySQL 中建立隱藏索引透過 SQL 語句INVISIBLE來實現，語法：
CREATE TABLE tablename(
propname1 type1[CONSTRAINT1],
propname2 type2[CONSTRAINT2],
......
propnamen typen,
INDEX [indexname](propname1 [(length)]) INVISIBLE
)
上述語句比普通索引多了一個關鍵字INVISIBLE，用來標記索引為不可見索引。
示例：
CREATE TABLE book(
book_id INT,
book_name VARCHAR(100),
authors VARCHAR (100),
info VARCHAR (100),
comment VARCHAR (100),
year_publication YEAR,
# 建立不可見的索引
INDEX idx_cmt(comment) INVISIBLE
)
在已經存在的表上建立
語法：
CREATE INDEX indexname
ON tablename(propname[(length)]) INVISIBLE
示例：
CREATE INDEX idx_year_pub ON book(year_publication) INVISIBLE
透過 ALTER TABLE 語句建立
語法：
ALTER TABLE tablename
ADD INDEX indexname (propname[(length)]) INVISIBLE
示例：
ALTER TABLE book
ADD UNIQUE INDEX uk_idx_bname(book_name) INVISIBLE
切換索引可見狀態
已存在的索引可透過如下語句切換可見狀態：
# 切換成隱藏索引
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE
# 切換成非隱藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE
示例：
# 可見 ---> 不可見
ALTER TABLE book ALTER INDEX idx_year_pub INVISIBLE
# 不可見 ---> 可見
ALTER TABLE book ALTER INDEX idx_cmt VISIBLE
使隱藏索引對查詢最佳化器可見
在 MySQL 8.x 版本中，為索引提供了一種新的測試方式，可以透過查詢最佳化器的一個開關 （use_invisible_indexes）來開啟某個設定，使隱藏索引對查詢最佳化器可見。如果 use_invisible_indexes 設定為 off（預設），最佳化器會忽略隱藏索引。如果設定為 on，即使隱藏索引不可見，最佳化器在生成執行計劃時仍會考慮使用隱藏索引。
檢視：
mysql> SELECT @@optimizer_switch;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@optimizer_switch |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
修改：
mysql> SELECT @@optimizer_switch;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@optimizer_switch |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=on,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
索引的設計原則
資料準備
建立資料庫和表：
mysql> CREATE DATABASE atguigudb1;
Query OK, 1 row affected (0.01 sec)
mysql> USE atguigudb1;
Database changed
mysql> CREATE TABLE `student_info` (
-> `id` INT(11) NOT NULL AUTO_INCREMENT,
-> `student_id` INT NOT NULL ,
-> `name` VARCHAR(20) DEFAULT NULL,
-> `course_id` INT NOT NULL ,
-> `class_id` INT(11) DEFAULT NULL,
-> `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`id`)
-> ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 3 warnings (0.04 sec)
mysql> CREATE TABLE `course` (
-> `id` INT(11) NOT NULL AUTO_INCREMENT,
-> `course_id` INT NOT NULL ,
-> `course_name` VARCHAR(40) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
建立模擬資料必需的儲存函式：
# 函式 1：建立隨機產生字串函式
mysql> DELIMITER //
mysql> CREATE FUNCTION rand_string(n INT)
-> RETURNS VARCHAR(255) # 函式會返回一個字串
-> BEGIN
-> DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
-> DECLARE return_str VARCHAR(255) DEFAULT '';
-> DECLARE i INT DEFAULT 0;
-> WHILE i < n DO
-> SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1 + RAND() * 52), 1));
-> SET i = i + 1;
-> END WHILE;
-> RETURN return_str;
-> END //
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
# 函式 2：建立隨機數函式
mysql> DELIMITER //
mysql> CREATE FUNCTION rand_num(from_num INT, to_num INT) RETURNS INT(11)
-> BEGIN
-> DECLARE i INT DEFAULT 0;
-> SET i = FLOOR(from_num + RAND() * (to_num - from_num + 1)) ;
-> RETURN i;
-> END //
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> DELIMITER ;
主從複製，主機會將寫操作記錄在 bin-log 日誌中。從機讀取 bin-log 日誌，執行語句來同步資料。如果使用函式來運算元據，會導致從機和主機操作時間不一致。所以，預設情況下，MySQL 不開啟建立函式設定。
檢視 MySQL 是否允許建立函式：
mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin_trust_function_creators | OFF | +---------------------------------+-------+ 1 row in set (0.01 sec)命令開啟，允許建立函式設定：
mysql> SET GLOBAL log_bin_trust_function_creators=1; Query OK, 0 rows affected (0.00 sec)如果不進行如上設定，建立函式時會報錯：
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
建立插入模擬資料的儲存過程：
# 儲存過程 1：建立插入課程表的儲存過程
mysql> DELIMITER //
mysql> CREATE PROCEDURE insert_course(max_num INT)
-> BEGIN
-> DECLARE i INT DEFAULT 0;
-> SET autocommit = 0; # 設定手動提交事務
-> REPEAT # 迴圈
-> SET i = i + 1; # 賦值
-> INSERT INTO course(course_id, course_name) VALUES (rand_num(10000, 10100), rand_string(6));
-> UNTIL i = max_num
-> END REPEAT;
-> COMMIT; # 提交事務
-> END //
Query OK, 0 rows affected (0.02 sec)
mysql> DELIMITER ;
# 儲存過程 2：建立插入學生資訊表的儲存過程
mysql> DELIMITER //
mysql> CREATE PROCEDURE insert_stu(max_num INT)
-> BEGIN
-> DECLARE i INT DEFAULT 0;
-> SET autocommit = 0; # 設定手動提交事務
-> REPEAT # 迴圈
-> SET i = i + 1; # 賦值
-> INSERT INTO student_info(course_id, class_id, student_id, name) VALUES (rand_num(10000, 10100), rand_num(10000, 10200), rand_num(1, 200000), rand_string(6));
-> UNTIL i = max_num
-> END REPEAT;
-> COMMIT; # 提交事務
-> END //
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
呼叫儲存過程：
mysql> CALL insert_course(100);
Query OK, 0 rows affected (0.02 sec)
mysql> CALL insert_stu(1000000);
Query OK, 0 rows affected (57.34 sec)
適合建立索引的情景
欄位的數值有唯一性的限制
索引本身可以起到約束的作用，比如唯一索引、主鍵索引都是可以起到唯一性約束的，因此在資料表中，如果某個欄位是唯一性的，就可以直接建立唯一性索引，或者主鍵索引。這樣，可以更快速的透過該索引來確定某條記錄。
業務上具有唯一特性的欄位，即使是組合欄位，也必須建成唯一索引。（來源：Alibaba）
說明：不要以為唯一索引影響了 INSERT 速度，實際上這個速度損耗可以忽略，但提高查詢速度是明顯的。
頻繁作為 WHERE 查詢條件的欄位
某個欄位在 SELECT 語句的 WHERE 條件中經常被使用到，那麼就需要給這個欄位建立索引了。尤其是在資料量大的情況下，建立普通索引就可以大幅提升資料查詢的效率。
經常 GROUP BY 和 ORDER BY 的列
索引其實就是讓資料按照某種順序進行儲存或檢索。當我們使用 GROUP BY 對資料進行分組查詢，或者使用 ORDER BY 對資料進行排序的時候，如果對分組或者排序的欄位建立索引，本身索引的資料就已經排好序了，進行分組查詢和排序操作效能不是很 nice 嗎？另外，如果待排序的列有多個，那麼可以在這些列上建立組合索引。
- 如果僅僅使用 GROUP BY 或者 ORDER BY，且後面只有一個欄位，則單獨建立索引；如果後面跟多個欄位，則建立組合索引。
- 如果既有 GROUP BY 又有 ORDER BY，那就建立聯合索引，且 GROUP BY 的欄位寫在前面，ORDER BY 的欄位寫在後面。MySQL 8.0 後的版本也可以考慮使用降序索引。
UPDATE、DELETE 的 WHERE 條件列
對資料按照某個條件進行查詢後再進行 UPDATE 或 DELETE 的操作，如果對 WHERE 欄位建立了索引，就能大幅提升效率。原理是因為需要先根據 WHERE 條件列檢索出來這條記錄，然後再對它進行更新或刪除。如果進行更新的時候，更新的欄位是非索引欄位，提升的效率會更明顯，這是因為非索引欄位更新不需要對索引進行維護。
DISTINCT 欄位需要建立索引
有時候需要對某個欄位進行去重，使用DISTINCT，那麼對這個欄位建立索引，也會提升查詢效率。這是因為索引會對資料按照某種順序進行排序，所以在去重的時候會快很多。
多表 JOIN 連線操作時，建立索引注意事項
首先，連線表的數量儘量不要超過 3 張，因為每增加一張表就相當於增加了一次巢狀的迴圈，數量級增長會非常快（n, n^2, n^3, …），嚴重影響查詢的效率。
其次，對 WHERE 條件建立索引，因為 WHERE 才是對資料條件的過濾。如果在資料量非常大的情況下，沒有 WHERE 條件過濾是非常可怕的。
最後，對用於連線的欄位建立索引，並且該欄位在多張表中的型別必須一致。比如 course_id 在 student_info 表和 course 表中都為 int(11) 型別，而不能一個為 int 另一個為 varchar 型別。否則在查詢時，雖然也會進行隱式的型別轉換，轉換時會使用函式，但會導致索引失效。
使用列的型別小的建立索引
此處說的型別大小，指的就是該型別表示的資料範圍的大小。
在定義表結構的時候，要顯式的指定列的型別，以整數型別為例，有 TINYINT、MEDIUMINT、INT、BIGINT 等，它們佔用的儲存空間依次遞增，能表示的整數範圍當然也是依次遞增。如果想要對某個整數列建立索引的話，在表示的整數範圍允許的情況下，儘量讓索引列使用較小的型別，比如能使用 INT 就不要使用 BIGINT，能使用 MEDIUMINT 就不要使用 INT。原因如下：
- 資料型別越小，在查詢時進行的比較操作就越快；
- 資料型別越小，索引佔用的儲存空間就越小，在一個資料頁內就可以放下更多的記錄，從而減少磁碟 I/O 帶來的效能損耗，也就意味著可以把更多的資料頁快取在記憶體中，從而加快讀寫效率。
這個建議對於表的主鍵來說更加適應，因為不僅是聚簇索引中會儲存主鍵值，其他所有的二級索引的節點處，都會儲存一份記錄的主鍵值，如果主鍵使用更小的資料型別，也就意味著節省更多的儲存空間和更高效的 I/O。
使用字串字首建立索引
假設字串很長，那儲存一個字串就需要佔用很大的儲存空間。在需要為這個字串列建立索引時，就意味著在對應的 B+Tree 中有這樣的兩個問題：
- B+Tree 索引中的記錄需要把該列的完整字串儲存起來，更費時，而且字串越長，在索引中佔用的儲存空間越大。
- 如果 B+Tree 索引中索引列儲存的字串很長，那在作字串比較時會佔用更多的時間。
此時，可以透過擷取欄位的前面一部分內容建立索引，即字首索引。這樣，在查詢記錄時，雖然不能精確的定位到記錄的位置，但是能定位到相應字串字首所在的位置，然後根據字首相同的記錄的主鍵值，回表查詢完整的字串值。即節約空間，又減少了字串的比較時間，還大體能解決排序的問題。
例如，TEXT 和 BLOG 型別的欄位，進行全文檢索會很浪費時間，如果只檢索欄位前面的若干字元，可以提高檢索速度。
示例：
# 建立一張商戶表
CREATE TABLE shop(address varchar(120) NOT NULL);
# 因為地址欄位比較長，在地址欄位上建立字首索引
ALTER TABLE shop ADD INDEX(address(12));
問題是，字首擷取多少呢？擷取的多了，達不到節省索引儲存空間的目的，擷取的少了，重複內容太多，欄位的雜湊度 (選擇性)會降低。 怎麼計算不同的長度的選擇性呢？
先看一下欄位在全部資料中的選擇度：
SELECT COUNT(DISTINCT address) / COUNT(*) FROM shop
然後，透過不同長度去計算，與全表的選擇性對比，公式：
COUNT(DISTINCT LEFT(列名, 索引長度)) / COUNT(*)
例如：
SELECT
COUNT(DISTINCT LEFT(address, 10)) / COUNT(*) AS sub10, -- 擷取前 10 個字元的選擇度
COUNT(DISTINCT LEFT(address, 15)) / COUNT(*) AS sub11, -- 擷取前 15 個字元的選擇度
COUNT(DISTINCT LEFT(address, 20)) / COUNT(*) AS sub12, -- 擷取前 20 個字元的選擇度
COUNT(DISTINCT LEFT(address, 25)) / COUNT(*) AS sub13 -- 擷取前 25 個字元的選擇度
FROM shop
擴充：Alibaba《Java開發手冊》
【 強制 】在 varchar 欄位上建立索引時，必須指定索引長度，沒必要對全欄位建立索引，根據實際文字區分度決定索引長度。
說明：索引的長度與區分度是一對矛盾體，一般對字串型別資料，長度為 20 的索引，區分度會高達 90% 以上，可以使用
COUNT(DISTINCT LEFT(列名, 索引長度)) / COUNT(*)計算的區分度來確定。
引申：索引列字首對排序的影響。
如果使用了索引列字首，比方說前邊只把 address 列的前 12 個字元放到了二級索引中，下邊的查詢會有問題：
SELECT * FROM shop ORDER BY address LIMIT 12
因為二級索引中不包含完整的 address 列資訊，所以無法對前 12 個字元相同，後邊的字元不同的記錄進行排序，也就是使用索引列字首的方式無法支援使用索引排序，只能使用檔案排序。
區分度高（雜湊性高）的列適合作為索引
列的基數指的是某一個列中不重複資料的個數，比方說某個列包含值 2，5，8，2，5，8，2，5，8，雖然有 9 條記錄，但該列的基數是 3。也就是說，在記錄行數一定的情況下，列的基數越大，該列中的值越分散；列的基數越小，該列中的值越集中。這個列基數指標非常重要，直接影響是否能有效的利用索引。最好為列的基數大的列建立索引，為基數太小的列建立索引，效果可能不好。
可以使用公式SELECT COUNT(DISTINCT a) / COUNT(*) FROM t1計算區分度，越接近 1 越好，一般超過33%就算是比較高效的索引。
擴充：聯合索引中，應把區分度高（雜湊性高）的列，放在前面。
使用最頻繁的列放到聯合索引的左側
這樣也可以較少的建立一些索引。同時，由於最左字首原則，可以增加聯合索引的使用率。
在多個欄位都要建立索引的情況下，聯合索引優於單值索引
原因：
- 索引建立的多，維護的成本也高。
- 多個欄位進行聯合查詢時，其實只使用到一個索引。
- 在建立聯合索引的相關欄位做查詢時，聯合索引都能生效，使用頻率比較高，足夠最佳化 SQL 執行的速度了。
限制索引的數目
在實際工作中，也需要注意平衡，索引的數目不是越多越好。我們需要限制每張表上的索引數量，建議單張表索引數量不超過 6 個。原因：
-
每個索引都需要佔用磁碟空間，索引越多，需要的磁碟空間就越大。
-
索引會影響 INSERT、DELETE、 UPDATE 等語句的效能，因為表中的資料更改的同時，索引也會進行調整和更新，會造成負擔。
-
最佳化器在選擇如何最佳化查詢時，會根據統一資訊，對每一個可以用到的索引來進行評估，以生成出一個最好的執行計劃，如果同時有很多個索引都可以用於查詢，會增加 MySQL 最佳化器生成執行計劃的時間，降低查詢效能。這是因為表中建立的索引過多，最佳化器在 possible_keys 中選擇合適的 key 時需要的成本也會更多。比如下面查詢中 possible_keys 有兩個，實際使用的 key 只有一個，這個過程是最佳化器判斷的。
mysql> EXPLAIN SELECT student_id, COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100; +----+-------------+--------------+------------+-------+--------------------------+---------+---------+------+--------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+--------------------------+---------+---------+------+--------+----------+---------------------------------+ | 1 | SIMPLE | student_info | NULL | index | idx_sid,idx_cre_time_sid | idx_sid | 4 | NULL | 997449 | 100.00 | Using temporary; Using filesort | +----+-------------+--------------+------------+-------+--------------------------+---------+---------+------+--------+----------+---------------------------------+ 1 row in set, 1 warning (0.00 sec)
不適合建立索引的情景
在 WHERE 中使用不到的欄位，不要設定索引
WHERE 條件（包括 GROUP BY 和 ORDER BY）裡用不到的欄位，不需要建立索引，索引的價值是快速定位，如果起不到定位的欄位，通常是不需要建立索引的。
資料量小的表最好不要使用索引
如果表記錄太少，比如少於 1000 個，那麼是不需要建立索引的。表記錄太少，是否建立索引對查詢效率的影響並不大，甚至說，查詢花費的時間，可能比遍歷索引的時間還要短，索引可能不會產生最佳化效果。
有大量重複資料的列上不要建立索引
在條件表示式中經常用到的不同值較多的列上建立索引，但欄位中如果有大量重複資料，也不用建立索引。比如性別欄位，往往會有大量重複欄位，如果建立索引，不但不會提高查詢效率，反而會嚴重降低資料更新速度。
索引的價值是快速定位，如果想要定位的資料有很多，那麼索引就失去了它的價值，比如通常情況下的性別欄位。當資料重複度大，比如高於 10% 的時候，也就不需要對這個欄位使用索引。
避免對經常更新的表建立過多的索引
第一層含義：頻繁更新的欄位不一定需要建立索引。因為更新資料的時候，也需要更新索引，如果索引太多，在更新索引的時候也會造成負擔，從而影響效率。
第二層含義：避免對經常更新的表建立過多的索引，並且索引中的列儘可能少。此時，雖然提高了查詢速度，同時卻會降低更新表的速度。
不建議用無序的值作為索引
例如身份證、UUID（在索引比較時需要轉為 ASCⅡ，並且插入時可能造成頁分裂）、MD5、HASH、無序長字串等。
刪除不再使用或者很少使用的索引
表中的資料被大量更新，或者資料的使用方式被改變後，原有的一些索引可能不再需要。資料庫管理員應當定期找出這些索引，將它們刪除，從而減少索引對更新操作的影響。
不要定義冗餘或重複的索引
有時候無意或者有意的對同一個列建立了多個索引，比如：index(a, b, c) 相當於 index(a)、index(a, b)、index(a, b, c)。
冗餘索引：
CREATE TABLE person_info(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
KEY idx_name (name(10))
)
- 透過 idx_name_birthday_phone_number 索引就可以對 name 列進行快速搜尋，再建立一 個專門針對 name 列的索引就算是一個 冗餘索引，維護這個索引只會增加維護的成本，並不會對搜尋有 什麼好處。
重複索引：
CREATE TABLE repeat_index_demo (
col1 INT PRIMARY KEY,
col2 INT,
UNIQUE uk_idx_c1 (col1),
INDEX idx_c1 (col1)
)
- col1 既是主鍵，又給它定義為一個唯一索引，還給它定義了一個普通索引，可是主鍵本身就會生成聚簇索引，所以定義的唯一索引和普通索引是重複的，這種情況要避免。
小結
索引是一把雙刃劍，可以提高查詢效率，但也會降低插入和更新的速度，並佔用磁碟空間。
使用索引的最終目的是為了使查詢的速度變快，上面給出的原則是最基本的準則，但是不能拘泥於上面的準則，在日常的學習和工作中需要進行不斷的實踐，根據應用的實際情況進行分析和判斷，從而選擇最合適的索引方式。
原文連結
https://github.com/ACatSmiling/zero-to-zero/blob/main/RelationalDatabase/mysql-advanced.md