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 小結

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

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