MySQL 索引的設計原則(超詳細說明講解)

freeflydom

2025年3月7日 9:50 本文熱度 365

1. 測試數據準備

第1步：創(chuàng)建數據庫、創(chuàng)建表


CREATE DATABASE dbtest3;
USE dbtest3;
#1.創(chuàng)建學生表和課程表
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;
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步：創(chuàng)建模擬數據必需的存儲函數

說明： 創(chuàng)建函數，假如報錯：
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)
由于開啟過慢查詢日志bin-log, 我們就必須為我們的 function 指定一個參數。

主從復制，主機會將寫操作記錄在bin-log日志中。從機讀取bin-log日志，執(zhí)行語句來同步數據。如果使用函數來操作數據，會導致從機和主鍵操作時間不一致。所以，默認情況下，mysql不開啟創(chuàng)建函數設置。
查看mysql是否允許創(chuàng)建函數：
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

命令開啟：允許創(chuàng)建函數設置：
set global log_bin_trust_function_creators=1;    # 不加global只是當前窗口有效。

mysqld重啟，上述參數又會消失。永久方法：
windows下：my.ini[mysqld]加上：
log_bin_trust_function_creators=1 # 1 表示真-開啟，0 表示假-關閉
linux下：/etc/my.cnf 下my.cnf[mysqld]加上：
log_bin_trust_function_creators=1 # 1 表示真-開啟，0 表示假-關閉


#函數1：創(chuàng)建隨機產生字符串函數
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：創(chuàng)建隨機數函數
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;

第3步：創(chuàng)建插入模擬數據的存儲過程


# 存儲過程1：創(chuàng)建插入課程表存儲過程
DELIMITER //
CREATE PROCEDURE insert_course( max_num INT ) 
BEGIN
DECLARE i INT DEFAULT 0;
  SET autocommit = 0; #設置手動提交事務 
  REPEAT #循環(huán)
  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：創(chuàng)建插入學生信息表存儲過程
DELIMITER //
CREATE PROCEDURE insert_stu( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
 SET autocommit = 0; #設置手動提交事務
  REPEAT # 循環(huán)
  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);

第5步：查看是否含有這么多數據，數據是否插入成功

SELECT COUNT(*) FROM course;
SELECT COUNT(*) FROM student_info;

2. 哪些情況適合創(chuàng)建索引

2.1 字段的數值有唯一性的限制

索引本身可以起到約束的作用，比如：唯一索引，主鍵索引都是可以起到唯一性約束的，因此在我們的數據表中，如果某個字段是唯一性的 ，就可以直接創(chuàng)建唯一性索引 ，或者主鍵索引。這樣可以更快速地通過該索引來確定某條記錄。

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

業(yè)務上具有唯一特性的字段，即使是組合字段，也必須建成唯一索引。（來源：Alibaba）

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

2.2 頻繁作為 WHERE 查詢條件的字段

某個字段在SELECT語句的 WHERE 條件中經常被使用到，那么就需要給這個字段創(chuàng)建索引了。尤其是在數據量大的情況下，創(chuàng)建普通索引就可以大幅提升數據查詢的效率。

比如 student_info數據表（含100萬條數據），假設我們想要查詢 student_id=123110 的用戶信息。

沒有給 student_id 字段添加索引，執(zhí)行的速度是：

SELECT course_id,class_id,`name`,create_time,student_id
FROM student_info
WHERE student_id = 123110;

# 給 student_id 字段添加上索引之后
ALTER TABLE student_info
ADD INDEX ids_sid(student_id);

2.3 經常 GROUP BY 和 ORDER BY 的列

索引就是讓數據按照某種順序進行存儲或檢索，因此當我們使用 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;

# 刪除 idx_sid 索引
DROP INDEX ids_sid ON student_info;
# student_id 字段上沒有索引的
SELECT student_id, COUNT(*) AS num
FROM student_info
GROUP BY student_id LIMIT 100;

如果同時有 GROUP By 和 ORDER BY 的情況：比如我們按照 student_id 進行分組，同時按照創(chuàng)建時間降序的方式進行排序，這時我們就需要同時進行 GROUP BY 和 ORDER BY，那么是不是需要單獨創(chuàng)建 student_id 的索引和 create_time 的索引呢？

當我們對 student_id 和 create_time 分別創(chuàng)建索引，執(zhí)行下面的SQL查詢

# 添加單例索引
ALTER TABLE student_info
ADD INDEX idx_sid(student_id);
ALTER TABLE student_info
ADD INDEX idx_cre_time(create_time);
SELECT student_id, COUNT(*) AS num
FROM student_info
GROUP BY student_id
ORDER BY create_time DESC
LIMIT 100; # 5.212s

當我們添加對 student_id 和 create_time 組合創(chuàng)建聯合索引，執(zhí)行下面的SQL查詢

# 添加為 student_id 和 create_time 組合創(chuàng)建聯合索引
ALTER TABLE student_info
ADD INDEX idx_sid_cre_time(student_id,create_time DESC);
SHOW INDEX FROM student_info;

再次執(zhí)行該SQL語句：

2.4 UPDATE、DELETE 的中的WHERE 條件列添加索引

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

# update, delete 的 where 條件列添加索引
SHOW INDEX FROM student_info;
UPDATE student_info SET student_id = 10002
WHERE NAME = 'jfiodasjfoj';

為 name 添加上索引

# 添加索引
ALTER TABLE student_info
ADD INDEX idx_name(`name`);
UPDATE student_info SET student_id = 10002
WHERE NAME = 'jfiodasjfoj';

2.5 對于經常 DISTINCT 字段需要創(chuàng)建索引

有時候我們需要對某個字段進行去重，使用 DISTINCT，那么對這個字段創(chuàng)建索引，也會提升查詢效率。比如，我們想要查詢課程表中不同的 student_id 都有哪些，如果我們沒有對 student_id 創(chuàng)建索引，執(zhí)行 SQL 語句：

SELECT DISTINCT(student_id) FROM `student_info`;

運行結果（600637 條記錄，運行時間 0.683s ）：

如果我們對 student_id 創(chuàng)建索引，再執(zhí)行 SQL 語句：

SELECT DISTINCT(student_id) FROM `student_info`;

運行結果（600637 條記錄，運行時間 0.010s ）：

你能看到 SQL 查詢效率有了提升，同時顯示出來的 student_id 還是按照 遞增的順序 進行展示的。這是因為索引會對數據按照某種順序進行排序，所以在去重的時候也會快很多。

2.6 多表 JOIN 連接操作時，創(chuàng)建索引注意事項

首先， 連接表的數量盡量不要超過 3 張 ，因為每增加一張表就相當于增加了一次嵌套的循環(huán)，數量級增長會非常快，嚴重影響查詢的效率。
其次， 對 WHERE 條件創(chuàng)建索引 ，因為 WHERE 才是對數據條件的過濾。如果在數據量非常大的情況下，沒有 WHERE 條件過濾是非常可怕的。
最后， 對用于連接的字段創(chuàng)建索引 ，并且該字段在多張表中的 類型必須一致 。比如 course_id 在 student_info 表和 course 表中都為 int(11) 類型，而不能一個為 int 另一個為 varchar 類型。

舉個例子，如果我們只對 student_id 和 name 創(chuàng)建索引，執(zhí)行 SQL 語句：


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';

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';

2.7 使用列的類型小的創(chuàng)建索引

使用列小的類型，創(chuàng)建的索引占用的磁盤空間就比較小一些，因為MySQL8 是將索引和數據都是存放再一起的。

我們這里所說的類型大小 指的就是該類型表示的數據范圍的大小。

我們在定義表結構的時候要顯式的指定列的類型，以整數類型為例，有TINYINT，MEDIUMINT，INT，BIGINT 等，它們占用的存儲空間依次遞增，能表示的整數范圍當然也是依次遞增，如果我們想要對某個整數列建立索引的話，在表示的整數范圍允許的情況下，盡量讓索引列使用較小的類型，比如我們能使用 INT 就不要使用 BIGINT ，能使用 MEDIUMINT 就不要使用 INT 。

2.8 使用字符串前綴創(chuàng)建索引

創(chuàng)建一張商戶表，因為地址字段比較長，在地址字段上建立前綴索引

創(chuàng)建一張商戶表，因為地址字段比較長，在地址字段上建立前綴索引

create table shop(address varchar(120) not null);
alter table shop add index(address(12));

問題是，截取多少呢？截取得多了，達不到節(jié)省索引存儲空間的目的；截取得少了，重復內容太多，字段的散列度(選擇性)會降低。怎么計算不同的長度的選擇性呢？

先看一下字段在全部數據中的選擇度：

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開發(fā)手冊》

【強制】在 varchar 字段上建立索引時，必須指定索引長度，沒必要對全字段建立索引，根據實際文本區(qū)分度決定索引長度。
說明：索引的長度與區(qū)分度是一對矛盾體，一般對字符串類型數據，長度為 20 的索引，區(qū)分度會 高達 90% 以上 ，可以使用 count(distinct left(列名, 索引長度))/count(*)的區(qū)分度來確定。

2.9 區(qū)分度高(散列性高)的列適合作為索引

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

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

拓展：聯合索引把區(qū)分度高(散列性高)的列放在前面。

2.10 使用最頻繁的列放到聯合索引的左側——索引最左側匹配

索引最左側匹配的原則，索引會優(yōu)先判斷最左側的字段是否，建立的索引，建立了索引就會走索引，如果左側的字段沒有走索引，就算后面的字段有索引，也不會走索引的。

3. 哪些情況不適合創(chuàng)建索引

3.1 在 where 篩選條件當中使用不到的字段，不要設置索引

你都不對該字段，進行篩選過濾，那么索引你沒有意義，因為你索引也是會增加磁盤空間大小的。

3.2 數據量小的表最好不要使用索引

舉例：創(chuàng)建表1：

CREATE TABLE t_without_index(
a INT PRIMARY KEY AUTO_INCREMENT, b INT
);

提供存儲過程1：

#創(chuàng)建存儲過程
DELIMITER //
CREATE PROCEDURE t_wout_insert() 
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 900
    DO
         INSERT INTO t_without_index(b) SELECT RAND()*10000; 
         SET i = i + 1;
    END WHILE;
    COMMIT;
END //
DELIMITER ;
#調用
CALL t_wout_insert();

創(chuàng)建表2：

CREATE TABLE t_with_index(
a INT PRIMARY KEY AUTO_INCREMENT, b INT,
INDEX idx_b(b) );

創(chuàng)建存儲過程2：

#創(chuàng)建存儲過程
DELIMITER //
CREATE PROCEDURE t_with_insert()
BEGIN
   DECLARE i INT DEFAULT 1;
   WHILE i <= 900
   DO
        INSERT INTO t_with_index(b) SELECT RAND()*10000;
        SET i = i + 1;
   END WHILE;
   COMMIT;
END //
DELIMITER ;
#調用
CALL t_with_insert();

查詢對比：

mysql> select * from t_without_index where b = 9879;
+------+------+
|a   |b   |
+------+------+
| 1242 | 9879 |
+------+------+
1 row in set (0.00 sec)
mysql> select * from t_with_index where b = 9879;
+-----+------+
|a |b   |
+-----+------+
| 112 | 9879 |
+-----+------+
1 row in set (0.00 sec)

你能看到運行結果相同，但是在數據量不大的情況下，索引就發(fā)揮不出作用了。

結論：在數據表中的數據行數比較少的情況下，比如不到 1000 行，是不需要創(chuàng)建索引的。

3.3 有大量重復數據的列上不要建立索引

舉例1：要在 100 萬行數據中查找其中的 50 萬行（比如性別為男的數據），一旦創(chuàng)建了索引，你需要先訪問 50 萬次索引，然后再訪問 50 萬次數據表，這樣加起來的開銷比不使用索引可能還要大。

舉例2：假設有一個學生表，學生總數為 100 萬人，男性只有 10 個人，也就是占總人口的 10 萬分之 1。學生表 student_gender 結構如下。其中數據表中的 student_gender 字段取值為 0 或 1，0 代表女性，1 代表男性。

CREATE TABLE student_gender( 
    student_id INT(11) NOT NULL,
    student_name VARCHAR(50) NOT NULL,
    student_gender TINYINT(1) NOT NULL,
    PRIMARY KEY(student_id)
)ENGINE = INNODB;

如果我們要篩選出這個學生表中的男性，可以使用：

SELECT * FROM student_gender WHERE student_gender = 1

運行結果（10 條數據，運行時間 0.696s ）：

結論：當數據重復度大，比如 高于 10% 的時候，也不需要對這個字段使用索引。

3.4 避免對經常更新的表創(chuàng)建過多的索引

因為你不斷更新表的同時，索引也是在同步更新的，索引更新是會消耗大量的時間。

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

例如身份證、UUID(在索引比較時需要轉為ASCII，并且插入時可能造成頁分裂)、MD5、HASH、無序長字符串等。

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

3.7 不要定義冗余或重復的索引

冗余索引

舉例：建表語句如下

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列進行快速搜索，再創(chuàng)建一個專門針對 name列的索引就算是一個 冗余索引 ，維護這個索引只會增加維護的成本，并不會對搜索有什么好處.。

重復索引：

另一種情況，我們可能會對某個列 重復建立索引 ，比方說這樣：

CREATE TABLE repeat_index_demo (
   col1 INT PRIMARY KEY,
   col2 INT,
   UNIQUE uk_idx_c1 (col1),
   INDEX idx_c1 (col1)
);

我們看到，col1 既是主鍵、又給它定義為一個唯一索引，還給它定義了一個普通索引，可是主鍵本身就會生成聚簇索引，所以定義的唯一索引和普通索引是重復的，這種情況要避免。

?轉自https://www.cnblogs.com/TheMagicalRainbowSea/p/18731859

該文章在 2025/3/7 9:50:55 編輯過

關鍵字查詢

設計

sql

mysql

索引

點晴ERP是一款針對中小制造業(yè)的專業(yè)生產管理軟件系統(tǒng),系統(tǒng)成熟度和易用性得到了國內大量中小企業(yè)的青睞。

點晴PMS碼頭管理系統(tǒng)主要針對港口碼頭集裝箱與散貨日常運作、調度、堆場、車隊、財務費用、相關報表等業(yè)務管理，結合碼頭的業(yè)務特點，圍繞調度、堆場作業(yè)而開發(fā)的。集技術的先進性、管理的有效性于一體，是物流碼頭及其他港口類企業(yè)的高效ERP管理信息系統(tǒng)。

點晴WMS倉儲管理系統(tǒng)提供了貨物產品管理,銷售管理,采購管理,倉儲管理,倉庫管理,保質期管理,貨位管理,庫位管理,生產管理,WMS管理系統(tǒng),標簽打印,條形碼,二維碼管理,批號管理軟件。

點晴免費OA是一款軟件和通用服務都免費，不限功能、不限時間、不限用戶的免費OA協同辦公管理系統(tǒng)。

午夜视频在线观看区二区-午夜视频在线观看视频-午夜视频在线观看视频在线观看-午夜视频在线观看完整高清在线-午夜视频在线观看网站-午夜视频在线观看亚洲天堂