MySQL-08.索引的建立和設計原則

C-08.索引的建立和設計原則

1.索引的宣告和使用

---

1.1 索引的分類

MySQL的索引包括普通索引、唯一性索引、全文索引、單列索引、多列索引和空間索引等。

• 從功能邏輯上分類，索引主要有4種，分別是普通索引，唯一索引，主鍵索引，全文索引。
• 按照物理實現方式，索引可以分為2種，聚簇索引和非聚簇索引。
• 按照作用欄位個數進行劃分，分為單列索引和聯合索引。

1.1.1 普通索引

在建立普通索引時，不附加任何限制條件，只是用於提高查詢效率。這類索引可以建立在任何資料型別中，其值是否唯一和非空，要由欄位本身的完整性約束條件決定。建立索引以後，可以透過索引進行查詢。例如，在表student的欄位name上建立一個普通索引，查詢記錄時就可以根據該索引進行查詢。

1.1.2 唯一性索引

使用UNIQUE引數可以設定索引為唯一性索引，在建立唯一性索引時，限制該索引的值必須是唯一的，但允許有空值。在一張資料表裡可以有多個唯一索引。

例如，在表student的欄位email中建立唯一性索引，那麼欄位email的值就必須是唯一的。透過唯一性索引，可以更快速地確定某條記錄。

1.1.3 主鍵索引

主鍵索引就是一種特殊的唯一性索引，在唯一索引的基礎上增加了不為空的約束，也就是 NOT NULL + UNIQUE，一張表裡最多隻有一個主鍵索引。

這是由主鍵索引的物理實現方式決定的，因為資料儲存在檔案中只能按照一種順序進行儲存。

1.1.4 單列索引

在表中的單個欄位上建立索引。單列索引只根據該欄位進行索引。單列索可以是普通索引，也可以是唯一性索引，還可以是全文索引。只要保證該索引只對應一個欄位即可。一個表可以有多個單列索引。

1.1.5 多列(組合、聯合)索引

多列索引是在表的多個欄位組合上建立一個索引。該索引指向建立時對應的多個欄位，可以透過這幾個欄位進行查詢，但是隻有查詢條件中使用了這些欄位中的第一個欄位時才會被使用。例如，在表中的欄位id、name和gender上建立一個多列索引idx_id_name_gender，只有在查詢條件中使用了欄位id時該索引才會被使用。使用組合索引時遵循最左字首集合。

1.1.6 全文索引

全文索引(也稱全文檢索)）是目前搜尋引擎使用的一種關鍵技術。它能夠利用【分詞技術】等多種演算法智慧分析出文字文字中關鍵詞的頻率和重要性，然後按照一定的演算法規則智慧地篩選出我們想要的搜尋結果。全文索引非常適合大型資料集，對於小的資料集，它的用處比較小。

使用引數FULLTEXT可以設定索引為全文索引。在定義索引的列上支援值的全文查詢，允許在這些索引列中插入重複值和空值。全文索引只能建立在CHAR、VARCHAR或TEXT型別及其系列型別的欄位上，查詢資料量較大的字串型別的欄位時，使用全文索引可以提高查詢速度。例如，表student的欄位information是TEXT型別，該欄位包含了很多文字資訊。在欄位information上建立全文索引後，可以提高查詢欄位information的速度。

全文索引典型的有兩種型別:自然語言的全文索引和布林全文索引。

• 自然語言搜尋引擎將計算每一個文件物件和查詢的相關度。這裡，相關度是基於匹配的關鍵詞的個數，以及關鍵詞在文件中出現的次數。在整個索引中出現次數越少的詞語，匹配時的相關度就越高。相反，非常常見的單詞將不會被搜尋，如果一個詞語的在超過50%的記錄中都出現了，那麼自然語言的搜尋將不會搜尋這類詞語。

MySQL資料庫從3.23.23版開始支援全文索引，但MySQL5.6.4以前只有Myisam支援，5.6.4版本以後innodb才支援，但是官方版本不支援中文分詞，需要第三方分詞外掛。在5.7.6版本，MySQL內建了ngram全文解析器，用來支援亞洲語種的分詞。測試或使用全文索引時，要先看一下自己的MySQL版本、儲存引擎和資料型別是否支援全文索引。

隨著大資料時代的到來，關係型資料庫應對全文索引的需求已力不從心，逐漸被solr 、ElasticSearch等專門的搜尋引擎所替代。

1.1.7 補充：空間索引

使用引數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等索引。

1.2 建立索引

MySQL支援多種方法在單個或多個列上建立索引：在建立表的定義語句CREATE TABLE中指定索引列，使用ALTER TABLE語句在存在的表上建立索引，或者使用CREATE INDEX語句在已存在的表上新增索引。

1.2.1 建立表的時候建立索引

使用CREATE TABLE建立表時，除了可以定義列的資料型別外，還可以定義主鍵約束，外來鍵約束或者唯一性約束，而不論建立那種約束，在定義約束時相當於在指定列上建立了一個索引。

舉例

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指定升序或者降序的索引值儲存，注意降序索引是MySQL8.0的新特性，該關鍵字，在MySQL8.0以下版本不支援，定義為desc但是實際索引還是asc的；

1.顯示建立普通索引

CREATE TABLE book(
    book_id INT ,
    book_name VARCHAR(100),
    `authors` VARCHAR(100),
    info VARCHAR(100) ,
    `comment` VARCHAR(100),
    year_publication YEAR,
    INDEX(book_name)
);

2.顯示建立唯一索引

CREATE TABLE book1(
    book_id INT ,
    book_name VARCHAR(100),
    `authors` VARCHAR(100),
    info VARCHAR(100) ,
    `comment` VARCHAR(100),
    year_publication YEAR,
    UNIQUE INDEX uk_idx_cmt(comment)
);
#唯一性索引所在的欄位，可以新增多個null值

3.主鍵索引

主鍵索引，只能透過新增主鍵約束的方式，進行建立。

• 建立主鍵索引

CREATE TABLE book2(
    book_id INT PRIMARY KEY,
    book_name VARCHAR(100),
    `authors` VARCHAR(100),
    info VARCHAR(100) ,
    `comment` VARCHAR(100),
    year_publication YEAR
);

• 刪除主鍵索引

ALTER TABLE book2
DROP PRIMARY KEY; 
#注意，如果主鍵索引建立時，加了auto_increment約束，會導致，該語句執行失敗。原因，auto_increment必須修飾在unique的列上。

• 修改主鍵索引，必須先刪除索引，再新增索引

4.單列索引

單列索引，就是作用在單列上的索引。上面1-3都是單列索引，不在演示。

5.組合索引

CREATE TABLE book3(
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    book_name VARCHAR(100),
    `authors` VARCHAR(100),
    info VARCHAR(100) ,
    `comment` VARCHAR(100),
    year_publication YEAR,
    INDEX mul_idx_info_cmt(info,comment)
);

注意組合索引，在使用時，必須要有在建立組合索引時的最左列，才能生效。

比如針對book3表，雖然索引是建立在info和comment列上的，但是在where子句中必須使用info列的子句，才能使用到該索引。

EXPLAIN
SELECT * FROM book3 
WHERE `comment`= 'test';
#在語句的執行結果中，key列的值是null

EXPLAIN
SELECT * FROM book3 
WHERE info= 'test' and `comment`= 'test';
#在語句的執行結果中，key列的值是mul_idx_info_cmt 代表是用來組合索引

EXPLAIN
SELECT * FROM book3 
WHERE info= 'test';#在語句的執行結果中，key列的值是mul_idx_info_cmt 代表是用來組合索引

#特殊
EXPLAIN
SELECT * FROM book3 
WHERE `comment`= 'test' and info= 'test';#在語句的執行結果中，key列的值是mul_idx_info_cmt 代表是用來組合索引
#注意，我的個人理解，雖然定義的組合索引的順序是先info後comment，但是因為在MySQL的SELECT語句的執行流程中，有一個select最佳化器的元件，索引可能會自動最佳化，該sql語句，調整為先info後comment。但是不推薦，這種寫法。

6.全文索引-瞭解

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;

在MySQL5.7及之後版本中可以不指定最後的ENGINE了，因為在此版本中InnoDB支援全文索引。

全文索引的使用

#LIKE方式
SELECT * FROM test where name LIKE '%查詢字串%';

#全文索引
SELECT * FROM test4 where MATCH(info) AGAINST('查詢字串');

7.空間索引-瞭解

空間索引建立中，要求空間型別的欄位必須為 非空 。

CREATE TABLE test5(
    geo GEOMETRY NOT NULL,
    SPATIAL INDEX spa_idx_geo(geo)
) ENGINE=MyISAM;

1.2.2 在已存在的表建立索引

1. 使用ALTER TABLE語句建立索引 ALTER TABLE語句建立索引的基本語法如下：

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]

2. 使用CREATE INDEX建立索引 CREATE INDEX語句可以在已經存在的表上新增索引，在MySQL中，

CREATE INDEX被對映到一個ALTER TABLE語句上，基本語法結構為：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

1.3 刪除索引

1. 使用ALTER TABLE刪除索引 ALTER TABLE刪除索引的基本語法格式如下：

ALTER TABLE table_name DROP INDEX index_name;

2. 使用DROP INDEX語句刪除索引 DROP INDEX刪除索引的基本語法格式如下：

DROP INDEX index_name ON table_name;

提示刪除表中的列時，如果要刪除的列為索引的組成部分，則該列也會從索引中刪除。如果組成索引的所有列都被刪除，則整個索引將被刪除。

對於新增了AUTO_INCREMENT約束欄位的唯一性索引不能被刪除。

2.MySQL8.0索引新特性

---

2.1 支援降序索引

降序索引以降序儲存鍵值。雖然在語法上，從MysQL 4版本開始就已經支援降序索引的語法了，但實際上該DESC定義是被忽略的，直到MysQL 8.x版本才開始真正支援降序索引(僅限於InnoDB儲存引擎)。

MySQL在8.0版本之前建立的仍然是升序索引，使用時進行反向掃描，這大大降低了資料庫的效率。在某些場景下，降序索引意義重大。例如，如果一個查詢，需要對多個列進行排序，且順序要求不一致，那麼使用降序索引將會避免資料庫使用額外的檔案排序操作，從而提高效能。

舉例：分別在MySQL5.7版本和MySQL8.0版本中建立資料表ts1

CREATE TABLE ts1(a int, b int index(a asc,b desc));

MySQL8.0版本

從結果可以看出，索引已經是降序了。下面繼續測試降序索引在執行計劃中的表現。

mysql> show create table ts1\G
*************************** 1. row ***************************
       Table: ts1
Create Table: CREATE TABLE `ts1` (
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  KEY `a` (`a`,`b` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

MySQL5.7版本

從結果可以看出，索引仍然是預設的升序。

mysql> show create table ts1\G
*************************** 1. row ***************************
       Table: ts1
Create Table: CREATE TABLE `ts1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

分別在MySQL 5.7版本和MySQL 8.0版本的資料表ts1中插入800條隨機資料，執行語句如下：

DELIMITER //

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 //
DELIMITER ;

CALL ts_insert();

在MySQL 5.7版本中檢視資料表ts1的執行計劃，結果如下：

EXPLAIN SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5;

從結果可以看出，執行計劃中掃描數為799，而且使用了Using filesort。

提示 Using filesort是MySQL中一種速度比較慢的外部排序，能避免是最好的。多數情況下，管理員可以透過最佳化索引來儘量避免出現Using filesort，從而提高資料庫執行速度。

在MySQL 8.0版本中檢視資料表ts1的執行計劃。從結果可以看出，執行計劃中掃描數為5，而且沒有使用Using filesort

注意 降序索引只對查詢中特定的排序順序有效，如果使用不當，反而查詢效率更低。例如，上述查詢排序條件改為order by a desc, b desc，MySQL 5.7的執行計劃要明顯好於MySQL 8.0。

將排序條件修改為order by a desc, b desc後，下面來對比不同版本中執行計劃的效果。 在MySQL 5.7版本中檢視資料表ts1的執行計劃，結果如下：

EXPLAIN SELECT * FROM ts1 ORDER BY a DESC,b DESC LIMIT 5;

在MySQL 8.0和MySQL 5.7 版本中檢視資料表ts1的執行計劃。

從結果可以看出，修改後MySQL 5.7的執行計劃要明顯好於MySQL 8.0。

2.2 隱藏索引

在MySQL 5.7版本及之前，只能透過顯式的方式刪除索引。此時，如果發現刪除索引後出現錯誤，又只能透過顯式建立索引的方式將刪除的索引建立回來。如果資料表中的資料量非常大，或者資料表本身比較大,這種操作就會消耗系統過多的資源，操作成本非常高。|

從MysQL 8.x開始支援隱藏索引(invisible indexes)，只需要將待刪除的索引設定為隱藏索引，使查詢最佳化器不再使用這個索引(即使使用force index(強制使用索引)，最佳化器也不會使用該索引)，確認將索引設定為隱藏索引後系統不受任何響應，就可以徹底刪除索引。這種透過先將索引設定為隱藏索引，再刪除索引的方式就是軟刪除。

同時，如果你想驗證某個索引刪除之後的查詢效能影響，就可以暫時先隱藏該索引。

注意，主鍵不能被設定為隱藏索引。當表中沒有顯示主鍵時，表中第一個唯一非空索引會稱為隱式主鍵，也不能設定為隱藏索引。

索引預設是可見的，在使用CREATE TABLE，CREATE INDEX或者ALTER TABLE等語句時可以透過VISIBLE或者INVISIBLE關鍵詞設定索引的可見性。

2.2.1 建立表時設定索引是否隱藏

語法和建立索引一致，只是新增了關鍵字，visible和invisible設定是否隱藏

CREATE TABLE book5(
     book_id INT PRIMARY KEY AUTO_INCREMENT,
    book_name VARCHAR(100),
    `authors` VARCHAR(100),
    info VARCHAR(100) ,
    `comment` VARCHAR(100),
    year_publication YEAR,
    #建立不可見的索引
    INDEX mul_idx_info(info) INVISIBLE
);

2.2.2 在已有表設定索引是否隱藏

語法類似

#建立表後，設定隱藏索引
ALTER TABLE book5
ADD UNIQUE INDEX uq_bookname(book_name) INVISIBLE;

CREATE INDEX idx_cmt ON book5(COMMENT);#預設可見

2.2.3 修改隱藏索引

#修改隱藏索引
ALTER TABLE book5 ALTER INDEX idx_cmt INVISIBLE; #可見 -> 不可見

ALTER TABLE book5 ALTER INDEX idx_cmt VISIBLE;#不可見 -> 可見

注意 當索引被隱藏時，它的內容仍然是和正常索引一樣實時更新的。如果一個索引需要長期被隱藏，那麼可以將其刪除，因為索引的存在會影響插入、更新和刪除的效能。

如果將index_cname索引切換成可見狀態，透過explain檢視執行計劃，發現最佳化器選擇了index_cname索引。

透過設定隱藏索引的可見性可以檢視索引對調優的幫助。

2.2.4 使隱藏索引對查詢最佳化器可見

在MySQL 8.x版本中，為索引提供了一種新的測試方式，可以透過查詢最佳化器的一個開關（use_invisible_indexes）來開啟某個設定，使隱藏索引對查詢最佳化器可見。如果use_invisible_indexes設定為off(預設)，最佳化器會忽略隱藏索引。如果設定為on，即使隱藏索引不可見，最佳化器在生成執行計劃時仍會考慮使用隱藏索引。

（1）在MySQL命令列執行如下命令檢視查詢最佳化器的開關設定。

mysql> select @@optimizer_switch \G

在輸出的結果資訊中找到如下屬性配置。

use_invisible_indexes=off

此屬性配置值為off，說明隱藏索引預設對查詢最佳化器不可見。

（2）使隱藏索引對查詢最佳化器可見，需要在MySQL命令列執行如下命令：

mysql> set session optimizer_switch="use_invisible_indexes=on";
Query OK, 0 rows affected (0.00 sec)

SQL語句執行成功，再次檢視查詢最佳化器的開關設定。

mysql> select @@optimizer_switch \G
*************************** 1. row ***************************
@@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_co
st_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_ind
exes=on,skip_scan=on,hash_join=on
1 row in set (0.00 sec)

此時，在輸出結果中可以看到如下屬性配置。

use_invisible_indexes=on

use_invisible_indexes屬性的值為on，說明此時隱藏索引對查詢最佳化器可見。

（3）使用EXPLAIN檢視以欄位invisible_column作為查詢條件時的索引使用情況。

explain
select * from book5 where book_name = 'test';

查詢最佳化器會使用隱藏索引來查詢資料。

（4）如果需要使隱藏索引對查詢最佳化器不可見，則只需要執行如下命令即可。

mysql> set session optimizer_switch="use_invisible_indexes=off";
Query OK, 0 rows affected (0.00 sec)

再次檢視查詢最佳化器的開關設定。

mysql> select @@optimizer_switch \G

此時，use_invisible_indexes屬性的值已經被設定為“off”。

（5）注意，此種方式，修改隱藏索引對查詢最佳化器是否可見，是會話級別，只對當前會話級別起作用。

3.索引的設計原則

---

為了使索引的使用效率更高，在建立索引時，必須考慮在哪些欄位上建立索引和建立什麼型別的索引。索引設計不合理或者缺少索引都會對資料庫和應用程式的效能造成障礙。高效的索引對於獲得良好的效能非常重要。設計索引時，應該考慮相應準則。

3.1 資料準備

第1步：建立資料庫、建立表

CREATE DATABASE atguigudb1;

USE atguigudb1;

#1.建立學生表和課程表
CREATE TABLE `student_info` (
    `id` INT(11) 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;

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;

第2步：建立模擬資料必需的儲存函式

#函式1：建立隨機產生字串函式
DELIMITER //
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 //
DELIMITER ;

#函式2：建立隨機數函式
DELIMITER //
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 //
DELIMITER ;

建立函式，假如報錯：

由於開啟過慢查詢日誌bin-log, 我們就必須為我們的function指定一個引數。

主從複製，主機會將寫操作記錄在bin-log日誌中。從機讀取bin-log日誌，執行語句來同步資料。如果使用函式來運算元據，會導致從機和主鍵操作時間不一致。所以，預設情況下，mysql不開啟建立函式設定。

SELECT @@log_bin_trust_function_creators;#檢視mysql是否允許建立函式

SET GLOBAL log_bin_trust_function_creators = 1;#臨時

log_bin_trust_function_creators=1 #在my.cnf的 mysqld分組下，新增引數，重啟服務 永久方法

第3步：建立插入模擬資料的儲存過程

# 儲存過程1：建立插入課程表儲存過程
DELIMITER //
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 //
DELIMITER ;

# 儲存過程2：建立插入學生資訊表儲存過程
DELIMITER //
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 //
DELIMITER ;

第4步：呼叫儲存過程

CALL insert_course(100);
CALL insert_stu(1000000);

3.2 哪些情況適合建立索引

1. 欄位的數值有唯一性的限制

索引本身可以起到約束的作用，比如唯一索引，主鍵索引都是可以起到唯一約束的作用的，因此在我們的資料表中，如果某個欄位是唯一性的，就可以建立唯一性索引或者主鍵索引。這樣可以更快速地透過該索引來確定某條記錄。

例如：學生表中學號是具有唯一性的欄位，為該欄位建立唯一性索引可以很快確定某個學生的資訊，使用姓名的話，可能存在同名現象，從而降低查詢速度。

業務上具有唯一特性的欄位，即使是組合欄位，也必須建成唯一索引。（來源：Alibaba）

說明：不要以為唯一索引影響了 insert 速度，這個速度損耗可以忽略，但提高查詢速度是明顯的。

2. 頻繁作為 WHERE 查詢條件的欄位

某個欄位在SELECT語句的 WHERE 條件中經常被使用到，那麼就需要給這個欄位建立索引了。尤其是在資料量大的情況下，建立普通索引就可以大幅提升資料查詢的效率。

比如student_info資料表（含100萬條資料），假設我們想要查詢 student_id=123110 的使用者資訊。

#student_id欄位上沒有索引的：
SELECT course_id, class_id, NAME, create_time, student_id 
FROM student_info
WHERE student_id = 123110;#0.192s，執行時間受限於硬體效能不同，時間可能不同，主要是和加索引後的進行對比

#給student_id欄位新增索引
ALTER TABLE student_info
ADD INDEX idx_sid(student_id);

#student_id欄位上有索引的：
SELECT course_id, class_id, NAME, create_time, student_id 
FROM student_info
WHERE student_id = 123110; #0.001s

3.經常GROUP BY和ORDER BY的列

索引就是讓資料按照某種順序進行儲存或檢索，因此當我們使用 GROUP BY 對資料進行分組查詢，或者使用 ORDER BY 對資料進行排序的時候，就需要對分組或者排序的欄位進行索引。如果待排序的列有多個，那麼可以在這些列上建立組合索引。

#student_id欄位上有索引的：
SELECT student_id, COUNT(*) AS num 
FROM student_info 
GROUP BY student_id LIMIT 100; #0.012s

#刪除idx_sid索引
DROP INDEX idx_sid ON student_info;


#student_id欄位上沒有索引的：
SELECT student_id, COUNT(*) AS num 
FROM student_info 
GROUP BY student_id LIMIT 100; #0.605s

SHOW INDEX FROM student_info;

#新增單列索引
ALTER TABLE student_info
ADD INDEX idx_sid(student_id);

ALTER TABLE student_info
ADD INDEX idx_cre_time(create_time);


#修改sql_mode
SELECT @@sql_mode;

SET @@sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;  #3.665s 原因，是因為studnet_id索引使用了，但是order by 排序，需要使用filesort導致慢sql的產生


#新增聯合索引
ALTER TABLE student_info
ADD INDEX idx_sid_cre_time(student_id,create_time DESC);

SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;  #0.216s 原因，是因為idx_sid_cre_time索引使用了
 
#再進一步：
ALTER TABLE student_info
ADD INDEX idx_cre_time_sid(create_time DESC,student_id);#新增聯合索引

DROP INDEX idx_sid_cre_time ON student_info;#刪除idx_sid_cre_time索引

SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;   #2.653s 不會使用到idx_cre_time_sid索引，是因為sql執行流程中group by先於order by執行，會使用到student_id的索引，但是因為有desc關鍵，會使用到filesort導致慢sql的生成

4.UPDATE、DELETE的WHERE條件列

對資料按照某個條件進行查詢後再進行 UPDATE 或 DELETE 的操作，如果對 WHERE 欄位建立了索引，就能大幅提升效率。原理是因為我們需要先根據 WHERE 條件列檢索出來這條記錄，然後再對它進行更新或刪除。如果進行更新的時候，更新的欄位是非索引欄位，提升的效率會更明顯，這是因為非索引欄位更新不需要對索引進行維護。

#④ UPDATE、DELETE 的 WHERE 條件列
SHOW INDEX FROM student_info;

UPDATE student_info SET student_id = 10002 
WHERE NAME = '462eed7ac6e791292a79';  #0.559s

#新增索引
ALTER TABLE student_info
ADD INDEX idx_name(NAME);


UPDATE student_info SET student_id = 10001 
WHERE NAME = '462eed7ac6e791292a79'; #0.001s

5.DISTINCT 欄位需要建立索引

有時候我們需要對某個欄位進行去重，使用 DISTINCT，那麼對這個欄位建立索引，也會提升查詢效率。同時顯示出來的 student_id 還是按照遞增的順序進行展示的。這是因為索引會對資料按照某種順序進行排序，所以在去重的時候也會快很多。

6.多表 JOIN 連線操作時，建立索引注意事項

首先，連線表的數量儘量不要超過 3 張，因為每增加一張表就相當於增加了一次巢狀的迴圈，數量級增長會非常快，嚴重影響查詢的效率。

其次，對 WHERE 條件建立索引，因為 WHERE 才是對資料條件的過濾。如果在資料量非常大的情況下，沒有 WHERE 條件過濾是非常可怕的。

最後，對用於連線的欄位建立索引，並且該欄位在多張表中的型別必須一致。比如 course_id 在student_info 表和 course 表中都為 int(11) 型別，而不能一個為 int 另一個為 varchar 型別。

SELECT s.course_id, NAME, s.student_id, c.course_name 
FROM student_info s JOIN course c
ON s.course_id = c.course_id
WHERE NAME = '462eed7ac6e791292a79'; #0.001s

DROP INDEX idx_name ON student_info;


SELECT s.course_id, NAME, s.student_id, c.course_name 
FROM student_info s JOIN course c
ON s.course_id = c.course_id
WHERE NAME = '462eed7ac6e791292a79'; #0.175s

7. 使用列的型別小的建立索引

我們這裡所說的型別大小指的就是該型別表示的資料範圍的大小。
我們在定義表結構的時候要顯式的指定列的型別，以整數型別為例，有TINYINT、MEDIUMINT、INT、
BIGINT等，它們佔用的儲存空間依次遞增，能表示的整數範圍當然也是依次遞增。如果我們想要對某個整數列建立索引的話，在表示的整數範圍允許的情況下，儘量讓索引列使用較小的型別，比如我們能使用INT就不要使用BIGINT，能使用MEDIUMINT就不要使用INT。這是因為:

• 資料型別越小，在查詢時進行的比較操作越快
• 資料型別越小，索引佔用的儲存空間就越少，在一個資料頁內就可以放下更多的記錄，從而減少磁碟I/0帶來的效能損耗，也就意味著可以把更多的資料頁快取在記憶體中，從而加快讀寫效率。

這個建議對於表的主鍵來說更加適用，因為不僅是聚簇索引中會儲存主鍵值，其他所有的二級索引的節點處都會儲存一份記錄的主鍵值，如果主鍵使用更小的資料型別，也就意味著節省更多的儲存空間和更高效的I/o。

8.使用字串字首建立索引

假設我們的字串很長，那儲存一個字串就需要佔用很大的儲存空間。在我們需要為這個字串列建立索引時，那就意味著在對應的B+樹中有這麼兩個問題:

• B+樹索引中的記錄需要把該列的完整字串儲存起來，更費時。而且字串越長，在索引中佔用的儲存空間越大。|
• 如果B+樹索引中索引列儲存的字串很長，那在做字串比較時會佔用更多的時間。

我們可以透過擷取欄位的前面一部分內容建立索引，這個就叫字首索引。這樣在查詢記錄時雖然不能精確的定位到記錄的位置，但是能定位到相應字首所在的位置，然後根據字首相同的記錄的主鍵值回表查詢完整的字串值。既節約空間，又減少了字串的比較時間，還大體能解決排序的問題。

例如，TEXT和BLOG型別的欄位，進行全文檢索會很浪費時間，如果只檢索欄位前面的若干字元，這樣可以提高檢索速度。

建立一張商戶表，因為地址欄位比較長，在地址欄位上建立字首索引

create table shop(address varchar(120) not null);

alter table shop add index(address(12));

問題是，擷取多少呢？擷取得多了，達不到節省索引儲存空間的目的；擷取得少了，重複內容太多，欄位的雜湊度(選擇性)會降低。怎麼計算不同的長度的選擇性呢？

透過不同長度去計算，與全表的選擇性對比：

公式：

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;

引申另一個問題：索引字首對排序的影響

如果使用了索引列字首，比方說前邊只把address列的前12個字元放到了二級索引中，下邊這個查詢可能就有點兒檻監尬了:

select * from shop
order by address
limit 12

因為二級索引中不包含完整的address列資訊，所以無法對前12個字元相同，後邊的字元不同的記錄進行排序，也就是使用索引列字首的方式無法支援使用索引排序，只能使用檔案排序。

擴充：Alibaba《Java開發手冊》

【 強制 】在 varchar 欄位上建立索引時，必須指定索引長度，沒必要對全欄位建立索引，根據實際文字區分度決定索引長度。

說明：索引的長度與區分度是一對矛盾體，一般對字串型別資料，長度為 20 的索引，區分度會 高達90% 以上 ，可以使用 count(distinct left(列名, 索引長度))/count(*)的區分度來確定。

9.區分度高(雜湊性高)的列適合作為索引

列的基數指的是某一列中不重複資料的個數，比方說某個列包含值2，5，8，2，5，8，2，5，8，雖然有9條記錄，但該列的基數卻是3。也就是說，在記錄行數一定的情況下，列的基數越大，該列中的值越分散;列的基數越小，該列中的值越集中。這個列的基數指標非常重要，直接影響我們是否能有效的利用索引。最好為列的基數大的列建立索引，為基數太小列的建立索引效果可能不好。

可以使用公式select count(distinct a)/count(*) from t1計算區分度，越接近1越好，一般超過33%就算是比較高效的索引了。

擴充:聯合索引把區分度高(雜湊性高)的列放在前面。

10.使用最頻繁的列放到聯合索引的左側

這樣也可以較少的建立一些索引。同時，由於"最左字首原則"，可以增加聯合索引的使用率。

11.在多個欄位都要建立索引的情況下，聯合索引優於單值索引

3.3 限制索引的數目

在實際工作中，我們也需要注意平衡，索引的數目不是越多越好。我們需要限制每張表上的索引數量，建議單張表索引數量不超過6個。原因:
每個索引都需要佔用磁碟空間，索引越多，需要的磁碟空間就越大。

索引會影響INSERT、DELETE、UPDATE等語句的效能，因為表中的資料更改的同時，索引也會進行調整和更新，會造成負擔。

最佳化器在選擇如何最佳化查詢時，會根據統一資訊，對每一個可以用到的索引來進行評估，以生成出一個最好的執行計劃，如果同時有很多個索引都可以用於查詢，會增加MysQL最佳化器生成執行計劃時間，降低查詢效能。

3.4 那些情況不適合建立索引

1. 在where中使用不到的欄位，不要設定索引

2. 資料量小的表最好不要使用索引

在資料表中的資料行數比較少的情況下，比如不到 1000 行，是不需要建立索引的。

3. 有大量重複資料的列上不要建立索引

舉例1：要在 100 萬行資料中查詢其中的 50 萬行（比如性別為男的資料），一旦建立了索引，你需要先訪問 50 萬次索引，然後再訪問 50 萬次資料表，這樣加起來的開銷比不使用索引可能還要大。

結論：當資料重複度大，比如 高於 10% 的時候，也不需要對這個欄位使用索引。

4. 避免對經常更新的表建立過多的索引

第一層含義︰頻繁更新的欄位不一定要建立索引。因為更新資料的時候，也需要更新索引，如果索引太多，在更新索引的時候也會造成負擔，從而影響效率。

第二層含義:避免對經常更新的表建立過多的索引，並且索引中的列儘可能少。此時，雖然提高了查詢速度，同時卻會降低更新表的速度。

5. 不建議用無序的值作為索引

表中的資料被大量更新，或者資料的使用方式被改變後，原有的一些索引可能不再需要。資料庫管理員應當定期找出這些索引，將它們刪除，從而減少索引對更新操作的影響。

6. 刪除不再使用或者很少使用的索引

7. 不要定義冗餘或重複的索引

冗餘就是要建立的索引列，已存在聯合索引中。

3.5 小結

索引是一把雙刃劍，可提高查詢效率，但也會降低插入和更新(包括增，刪，改)的速度並佔用磁碟空間。

只是為了記錄自己的學習歷程，且本人水平有限，不對之處，請指正。