哪些情況適合創(chuàng)建索引
1. 字段的數(shù)值有唯一性的限制
業(yè)務(wù)上具有唯一特性的字段，即使是組合字段，也必須建成唯一索引。（來源：Alibaba）
說明：不要以為唯一索引影響了 insert 速度，這個(gè)速度損耗可以忽略，但提高查找速度是明顯的。
2. 頻繁作為 WHERE 查詢條件的字段
某個(gè)字段在SELECT語句的 WHERE 條件中經(jīng)常被使用到，那么就需要給這個(gè)字段創(chuàng)建索引了。尤其是在
數(shù)據(jù)量大的情況下，創(chuàng)建普通索引就可以大幅提升數(shù)據(jù)查詢的效率。
比如student_info數(shù)據(jù)表（含100萬條數(shù)據(jù)），假設(shè)我們想要查詢 student_id=123110 的用戶信息。
3. 經(jīng)常 GROUP BY 和 ORDER BY 的列
索引就是讓數(shù)據(jù)按照某種順序進(jìn)行存儲(chǔ)或檢索，因此當(dāng)我們使用 GROUP BY 對(duì)數(shù)據(jù)進(jìn)行分組查詢，或者
使用 ORDER BY 對(duì)數(shù)據(jù)進(jìn)行排序的時(shí)候，就需要 對(duì)分組或者排序的字段進(jìn)行索引 。如果待排序的列有多
個(gè)，那么可以在這些列上建立 組合索引 。
4. UPDATE、DELETE 的 WHERE 條件列
對(duì)數(shù)據(jù)按照某個(gè)條件進(jìn)行查詢后再進(jìn)行 UPDATE 或 DELETE 的操作，如果對(duì) WHERE 字段創(chuàng)建了索引，就
能大幅提升效率。原理是因?yàn)槲覀冃枰雀鶕?jù) WHERE 條件列檢索出來這條記錄，然后再對(duì)它進(jìn)行更新或
刪除。如果進(jìn)行更新的時(shí)候，更新的字段是非索引字段，提升的效率會(huì)更明顯，這是因?yàn)榉撬饕侄胃?/p>
新不需要對(duì)索引進(jìn)行維護(hù)。
5.DISTINCT 字段需要?jiǎng)?chuàng)建索引
有時(shí)候我們需要對(duì)某個(gè)字段進(jìn)行去重，使用 DISTINCT，那么對(duì)這個(gè)字段創(chuàng)建索引，也會(huì)提升查詢效率。
比如，我們想要查詢課程表中不同的 student_id 都有哪些，如果我們沒有對(duì) student_id 創(chuàng)建索引，執(zhí)行
SQL 語句：
SELECT DISTINCT(student_id) FROM `student_info`;
運(yùn)行結(jié)果（600637 條記錄，運(yùn)行時(shí)間 0.683s ）：
如果我們對(duì) student_id 創(chuàng)建索引，再執(zhí)行 SQL 語句：
SELECT DISTINCT(student_id) FROM `student_info`;
運(yùn)行結(jié)果（600637 條記錄，運(yùn)行時(shí)間 0.010s ）：
你能看到 SQL 查詢效率有了提升，同時(shí)顯示出來的 student_id 還是按照 遞增的順序 進(jìn)行展示的。這是因
為索引會(huì)對(duì)數(shù)據(jù)按照某種順序進(jìn)行排序，所以在去重的時(shí)候也會(huì)快很多。
6. 多表 JOIN 連接操作時(shí)，創(chuàng)建索引注意事項(xiàng)
首先， 連接表的數(shù)量盡量不要超過 3 張 ，因?yàn)槊吭黾右粡埍砭拖喈?dāng)于增加了一次嵌套的循環(huán)，數(shù)量級(jí)增
長(zhǎng)會(huì)非常快，嚴(yán)重影響查詢的效率。
其次， 對(duì) WHERE 條件創(chuàng)建索引 ，因?yàn)?WHERE 才是對(duì)數(shù)據(jù)條件的過濾。如果在數(shù)據(jù)量非常大的情況下，
沒有 WHERE 條件過濾是非?？膳碌?。
最后， 對(duì)用于連接的字段創(chuàng)建索引 ，并且該字段在多張表中的 類型必須一致 。比如 course_id 在
student_info 表和 course 表中都為 int(11) 類型，而不能一個(gè)為 int 另一個(gè)為 varchar 類型。
舉個(gè)例子，如果我們只對(duì) student_id 創(chuàng)建索引，執(zhí)行 SQL 語句：
運(yùn)行結(jié)果（1 條數(shù)據(jù)，運(yùn)行時(shí)間 0.189s ）：
這里我們對(duì) name 創(chuàng)建索引，再執(zhí)行上面的 SQL 語句，運(yùn)行時(shí)間為 0.002s 。
7. 使用列的類型小的創(chuàng)建索引
8. 使用字符串前綴創(chuàng)建索引
創(chuàng)建一張商戶表，因?yàn)榈刂纷侄伪容^長(zhǎng)，在地址字段上建立前綴索引
create table shop(address varchar(120) not null);
alter table shop add index(address(12));
問題是，截取多少呢？截取得多了，達(dá)不到節(jié)省索引存儲(chǔ)空間的目的；截取得少了，重復(fù)內(nèi)容太多，字
段的散列度(選擇性)會(huì)降低。怎么計(jì)算不同的長(zhǎng)度的選擇性呢？
先看一下字段在全部數(shù)據(jù)中的選擇度：
select count(distinct address) / count(*) from shop;
通過不同長(zhǎng)度去計(jì)算，與全表的選擇性對(duì)比：
公式：
count(distinct left(列名, 索引長(zhǎng)度))/count(*)
例如：
select count(distinct left(address,10)) / count(*) as sub10, -- 截取前10個(gè)字符的選擇度
count(distinct left(address,15)) / count(*) as sub11, -- 截取前15個(gè)字符的選擇度
count(distinct left(address,20)) / count(*) as sub12, -- 截取前20個(gè)字符的選擇度
count(distinct left(address,25)) / count(*) as sub13 -- 截取前25個(gè)字符的選擇度
from shop;
引申另一個(gè)問題：索引列前綴對(duì)排序的影響
拓展：Alibaba《Java開發(fā)手冊(cè)》
【 強(qiáng)制 】在 varchar 字段上建立索引時(shí)，必須指定索引長(zhǎng)度，沒必要對(duì)全字段建立索引，根據(jù)實(shí)際文本
區(qū)分度決定索引長(zhǎng)度。
說明：索引的長(zhǎng)度與區(qū)分度是一對(duì)矛盾體，一般對(duì)字符串類型數(shù)據(jù)，長(zhǎng)度為 20 的索引，區(qū)分度會(huì) 高達(dá)
90% 以上 ，可以使用 count(distinct left(列名, 索引長(zhǎng)度))/count(*)的區(qū)分度來確定。
9. 區(qū)分度高(散列性高)的列適合作為索引
10. 使用最頻繁的列放到聯(lián)合索引的左側(cè)
這樣也可以較少的建立一些索引。同時(shí)，由于"最左前綴原則"，可以增加聯(lián)合索引的使用率。
11. 在多個(gè)字段都要?jiǎng)?chuàng)建索引的情況下，聯(lián)合索引優(yōu)于單值索引
28.哪些情況不適合創(chuàng)建索引
1. 在where中使用不到的字段，不要設(shè)置索引
2. 數(shù)據(jù)量小的表最好不要使用索引
結(jié)論：在數(shù)據(jù)表中的數(shù)據(jù)行數(shù)比較少的情況下，比如不到 1000 行，是不需要?jiǎng)?chuàng)建索引的。
3. 有大量重復(fù)數(shù)據(jù)的列上不要建立索引
舉例1：要在 100 萬行數(shù)據(jù)中查找其中的 50 萬行（比如性別為男的數(shù)據(jù)），一旦創(chuàng)建了索引，你需要先
訪問 50 萬次索引，然后再訪問 50 萬次數(shù)據(jù)表，這樣加起來的開銷比不使用索引可能還要大。
舉例2：假設(shè)有一個(gè)學(xué)生表，學(xué)生總數(shù)為 100 萬人，男性只有 10 個(gè)人，也就是占總?cè)丝诘?10 萬分之 1。
學(xué)生表 student_gender 結(jié)構(gòu)如下。其中數(shù)據(jù)表中的 student_gender 字段取值為 0 或 1，0 代表女性，1 代
表男性。
如果我們要篩選出這個(gè)學(xué)生表中的男性，可以使用：
運(yùn)行結(jié)果（10 條數(shù)據(jù)，運(yùn)行時(shí)間 0.696s ）：
結(jié)論：當(dāng)數(shù)據(jù)重復(fù)度大，比如 高于 10% 的時(shí)候，也不需要對(duì)這個(gè)字段使用索引。
4. 避免對(duì)經(jīng)常更新的表創(chuàng)建過多的索引
5. 不建議用無序的值作為索引
例如身份證、UUID(在索引比較時(shí)需要轉(zhuǎn)為ASCII，并且插入時(shí)可能造成頁分裂)、MD5、HASH、無序長(zhǎng)字
符串等。
6. 刪除不再使用或者很少使用的索引
7. 不要定義冗余或重復(fù)的索引
① 冗余索引
舉例：建表語句如下
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 索引就可以對(duì) name 列進(jìn)行快速搜索，再創(chuàng)建一
個(gè)專門針對(duì) name 列的索引就算是一個(gè) 冗余索引 ，維護(hù)這個(gè)索引只會(huì)增加維護(hù)的成本，并不會(huì)對(duì)搜索有
什么好處。
② 重復(fù)索引
另一種情況，我們可能會(huì)對(duì)某個(gè)列 重復(fù)建立索引 ，比方說這樣：
CREATE TABLE repeat_index_demo (
col1 INT PRIMARY KEY,
col2 INT,
UNIQUE uk_idx_c1 (col1),
INDEX idx_c1 (col1)
);
我們看到，col1 既是主鍵、又給它定義為一個(gè)唯一索引，還給它定義了一個(gè)普通索引，可是主鍵本身就
會(huì)生成聚簇索引，所以定義的唯一索引和普通索引是重復(fù)的，這種情況要避免。
歡迎共同進(jìn)步：
QQ群：1007576722
https://huchao.blog.csdn.net/article/details/124220802?spm=1001.2014.3001.5502
