柚子快報激活碼778899分享：MySQL 索引總結(jié)（2）

BOL跨境優(yōu)選坊綜合2025-05-05470

http://yzkb.51969.com/

013 為什么官方建議使用自增長主鍵作為索引？（說一下自增主鍵和字符串類型主鍵的區(qū)別和影響）

自增主鍵能夠維持底層數(shù)據(jù)順序?qū)懭胱x取可以由b+樹的二分查找定位支持范圍查找，范圍數(shù)據(jù)自帶順序

字符串無法完成以上操作

014 使用int自增主鍵后最大id是10，刪除id 10和9，再添加一條記錄，最后添加的id是幾？刪除后重啟mysql然后添加一條記錄最后id是幾？

刪除之后

如果重啟，會從最大的id開始遞增如果沒重啟，會延續(xù)刪除之前最大的id開始遞增

015 索引的優(yōu)缺點是什么？

優(yōu)點

聚簇（主鍵）索引：

順序讀寫范圍快速查找范圍查找自帶順序

非聚簇索引：

條件查詢避免全表掃描scan范圍，排序，分組查詢返回行id，排序分組后，再回表查詢完整數(shù)據(jù)，有可能利用順序讀寫覆蓋索引不需要回表操作

索引的代價

索引是個好東西，可不能亂建，它在空間和時間上都會有消耗：

空間上的代價

每建立一個索引都要為它建立一棵B+樹，每一棵B+樹的每一個節(jié)點都是一個數(shù)據(jù)頁，一個頁默認會占用 16KB 的存儲空間，一棵很大的B+樹由許多數(shù)據(jù)頁組成，那就是很大的一片存儲空間。

時間上的代價

每次對表中的數(shù)據(jù)進行增、刪、改操作時，都需要去修改各個B+樹索引。而增、刪、改操作可能會對節(jié)點和記錄的排序造成破壞，所以存儲引擎需要額外的時間進行一些記錄移位、頁面分裂、頁面回收等操作來維護好節(jié)點和記錄的排序。如果我們建了許多索引，每個索引對應(yīng)的B+樹都要進行相關(guān)的維護操作，會給性能拖后腿。

B 樹和 B+ 樹都可以作為索引的數(shù)據(jù)結(jié)構(gòu)，在 MySQL 中采用的是 B+ 樹。

但B樹和B+樹各有自己的應(yīng)用場景，不能說B+樹完全比B樹好，反之亦然。

016 使用索引一定能提升效率嗎？

不一定

少量數(shù)據(jù)全表掃描也很快，可以直接獲取到全量數(shù)據(jù)唯一索引會影響插入速度，但建議使用索引過多會影響更新，插入，刪除數(shù)據(jù)速度

017 如果是大段文本內(nèi)容，如何創(chuàng)建（優(yōu)化）索引？

B 樹和 B+ 樹都可以作為索引的數(shù)據(jù)結(jié)構(gòu)，**在 MySQL 中采用的是 B+ 樹。**

第一種方式是分表存儲，然后創(chuàng)建索引

第二是使用es為大文本創(chuàng)建索引

021 CRUD時聚簇索引與非聚簇索引的區(qū)別是什么？

聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多，因為插入要保證主鍵不能重復(fù)聚簇索引范圍，排序查找效率高，因為是有序的非聚簇索引訪問需要兩次索引查找，第一次找到主鍵值，第二次根據(jù)主鍵值找到行數(shù)據(jù)

022 非聚簇索引為什么不存數(shù)據(jù)地址值而存儲主鍵？

因為聚簇索引中有時會引發(fā)分頁操作、重排操作數(shù)據(jù)有可能會移動

023 什么是回表操作？

id age name sex

age -> index

select * from user where age >20 ;

第一次取回id，第二次（回表）根據(jù)id拿到完整數(shù)據(jù)

select * from user where age >20 ;

024 什么是覆蓋索引？

id age name sex

age -> index

select * from user where age >20 ;

第一次取回id，第二次（回表）根據(jù)id拿到完整數(shù)據(jù)

age,name -> index

select age from user where age >20 and name like"張%" ;

覆蓋索引不會回表查詢，查詢效率也是比較高的

025 非聚集索引一定回表查詢嗎?

不一定，只要b+樹中包含的字段（創(chuàng)建索引的字段），覆蓋（包含）想要select 的字段，那么就不會回表查詢了。

026 為什么要回表查詢？直接存儲數(shù)據(jù)不可以嗎？

為了控制非聚簇索引的大小

027 如果把一個 InnoDB 表的主鍵刪掉，是不是就沒有主鍵，就沒辦法進行回表查詢了？

不是，InnoDB會生成rowid輔助回表查詢

028 什么是聯(lián)合索引，組合索引，復(fù)合索引？

為c2和c3列建立聯(lián)合索引，如下所示：

c2，c3 - > index

c3,c2 -> index

where c3=?

全職匹配

最左前綴

029 復(fù)合索引創(chuàng)建時字段順序不一樣使用效果一樣嗎？

我們也可以同時以多個列的大小作為排序規(guī)則，也就是同時為多個列建立索引，比方說我們想讓B+樹按照 c2和c3列的大小進行排序，這個包含兩層含義：

先把各個記錄和頁按照c2列進行排序。在記錄的c2列相同的情況下，采用c3列進行排序B+樹葉子節(jié)點處的記錄由c2列、c3列和主鍵c1列組成本質(zhì)上也是二級索引

create index idx_c2_c3 on user (c2,c3);

030 什么是唯一索引？

隨表一起創(chuàng)建索引：

CREATE TABLE customer (

id INT UNSIGNED AUTO_INCREMENT,

customer_no VARCHAR(200),

customer_name VARCHAR(200),

PRIMARY KEY(id), -- 主鍵索引：列設(shè)定為主鍵后會自動建立索引，唯一且不能為空。

UNIQUE INDEX uk_no (customer_no), -- 唯一索引：索引列值必須唯一，允許有NULL值，且NULL可能會出現(xiàn)多次。

KEY idx_name (customer_name), -- 普通索引：既不是主鍵，列值也不需要唯一，單純的為了提高查詢速度而創(chuàng)建。

KEY idx_no_name (customer_no,customer_name) -- 復(fù)合索引：即一個索引包含多個列。

);

單獨建創(chuàng)索引：

CREATE TABLE customer1 (

id INT UNSIGNED,

customer_no VARCHAR(200),

customer_name VARCHAR(200)

);

ALTER TABLE customer1 ADD PRIMARY KEY customer1(id); -- 主鍵索引

CREATE UNIQUE INDEX uk_no ON customer1(customer_no); -- 唯一索引

CREATE INDEX idx_name ON customer1(customer_name); -- 普通索引

CREATE INDEX idx_no_name ON customer1(customer_no,customer_name); -- 復(fù)合索引

031 唯一索引是否影響性能？

是

032 什么時候使用唯一索引？

業(yè)務(wù)需求唯一字段的時候，一般不考慮性能問題

. 【強制】業(yè)務(wù)上具有唯一特性的字段，即使是多個字段的組合，也必須建成唯一索引。說明：不要以為唯一索引影響了 insert 速度，這個速度損耗可以忽略，但提高查找速度是明顯的；另外，即使在應(yīng)用層做了非常完善的校驗控制，只要沒有唯一索引，根據(jù)墨菲定律，必然有臟數(shù)據(jù)產(chǎn)生。

033 什么時候適合創(chuàng)建索引，什么時候不適合創(chuàng)建索引？

適合創(chuàng)建索引

頻繁作為where條件語句查詢字段關(guān)聯(lián)字段需要建立索引排序字段可以建立索引分組字段可以建立索引(因為分組前提是排序) 統(tǒng)計字段可以建立索引（如.count(),max()）

不適合創(chuàng)建索引

頻繁更新的字段不適合建立索引 where，分組，排序中用不到的字段不必要建立索引可以確定表數(shù)據(jù)非常少不需要建立索引參與mysql函數(shù)計算的列不適合建索引

創(chuàng)建索引時避免有如下極端誤解：

1）寧濫勿缺。認為一個查詢就需要建一個索引。

2）寧缺勿濫。認為索引會消耗空間、嚴重拖慢更新和新增速度。

3）抵制惟一索引。認為業(yè)務(wù)的惟一性一律需要在應(yīng)用層通過“先查后插”方式解決。

034 什么是索引下推？

5.6之前的版本是沒有索引下推這個優(yōu)化的

**Using index condition：**叫作 Index Condition Pushdown Optimization （索引下推優(yōu)化）

如果沒有索引下推（ICP），那么MySQL在存儲引擎層找到滿足content1 > 'z'條件的第一條二級索引記錄。主鍵值進行回表，返回完整的記錄給server層，server層再判斷其他的搜索條件是否成立。如果成立則保留該記錄，否則跳過該記錄，然后向存儲引擎層要下一條記錄。如果使用了索引下推（ICP），那么MySQL在存儲引擎層找到滿足content1 > 'z'條件的第一條二級索引記錄。不著急執(zhí)行回表，而是在這條記錄上先判斷一下所有關(guān)于idx_content1索引中包含的條件是否成立，也就是content1 > 'z' AND content1 LIKE '%a'是否成立。如果這些條件不成立，則直接跳過該二級索引記錄，去找下一條二級索引記錄；如果這些條件成立，則執(zhí)行回表操作，返回完整的記錄給server層。

總結(jié)：

未開啟索引下推：

根據(jù)篩選條件在索引樹中篩選第一個條件獲得結(jié)果集后回表操作進行其他條件篩選再次回表查詢

開啟索引下推：在條件查詢時，當前索引樹如果滿足全部篩選條件，可以在當前樹中完成全部篩選過濾，得到比較小的結(jié)果集再進行回表操作

035 有哪些情況會導致索引失效？

計算、函數(shù)導致索引失效

-- 顯示查詢分析

EXPLAIN SELECT * FROM emp WHERE emp.name LIKE 'abc%';

EXPLAIN SELECT * FROM emp WHERE LEFT(emp.name,3) = 'abc'; --索引失效

LIKE以%，_ 開頭索引失效

拓展：Alibaba《Java開發(fā)手冊》

【強制】頁面搜索嚴禁左模糊或者全模糊，如果需要請走搜索引擎來解決。

EXPLAIN SELECT * FROM emp WHERE name LIKE '%ab%'; --索引失效

不等于(!= 或者<>)索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name = 'abc' ;

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <> 'abc' ; --索引失效

IS NOT NULL 失效和 IS NULL

EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL;

EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL; --索引失效

**注意：**當數(shù)據(jù)庫中的數(shù)據(jù)的索引列的NULL值達到比較高的比例的時候，即使在IS NOT NULL 的情況下 MySQL的查詢優(yōu)化器會選擇使用索引，此時type的值是range（范圍查詢）

-- 將 id>20000 的數(shù)據(jù)的 name 值改為 NULL

UPDATE emp SET `name` = NULL WHERE `id` > 20000;

-- 執(zhí)行查詢分析，可以發(fā)現(xiàn) IS NOT NULL 使用了索引

-- 具體多少條記錄的值為NULL可以使索引在IS NOT NULL的情況下生效，由查詢優(yōu)化器的算法決定

EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL

類型轉(zhuǎn)換導致索引失效

EXPLAIN SELECT * FROM emp WHERE name='123';

EXPLAIN SELECT * FROM emp WHERE name= 123; --索引失效

復(fù)合索引未用左列字段失效如果mysql覺得全表掃描更快時（數(shù)據(jù)少）;

036 為什么LIKE以%開頭索引會失效？

id,name,age

name 創(chuàng)建索引

select * from user where name like ‘%明’

type=all

select name,id from user where name like ‘%明’

type=index

張明

(name,age)

其實并不會完全失效，覆蓋索引下會出現(xiàn)type=index，表示遍歷了索引樹，再回表查詢，

覆蓋索引沒有生效的時會直接type=all

沒有高效使用索引是因為字符串索引會逐個轉(zhuǎn)換成accii碼，生成b+樹時按首個字符串順序排序，類似復(fù)合索引未用左列字段失效一樣，跳過開始部分也就無法使用生成的b+樹了

037 一個表有多個索引的時候，能否手動選擇使用哪個索引？

不可用手動直接干預(yù)，只能通過mysql優(yōu)化器自動選擇

038 如何查看一個表的索引？

show index from t_emp; // 顯示表上的索引

explain select * from t_emp where id=1; // 顯示可能會用到的索引及最終使用的索引

039 能否查看到索引選擇的邏輯？是否使用過optimizer_trace？

set session optimizer_trace="enabled=on",end_markers_in_json=on;

SELECT * FROM information_schema.OPTIMIZER_TRACE;

set session optimizer_trace="enabled=off";

040 多個索引優(yōu)先級是如何匹配的？

主鍵（唯一索引）匹配全值匹配（單值匹配）最左前綴匹配范圍匹配索引掃描全表掃描

一般性建議

? 對于單鍵索引，盡量選擇過濾性更好的索引（例如：手機號，郵件，身份證）

? 在選擇組合索引的時候，過濾性最好的字段在索引字段順序中，位置越靠前越好。

? 選擇組合索引時，盡量包含where中更多字段的索引

? 組合索引出現(xiàn)范圍查詢時，盡量把這個字段放在索引次序的最后面

? 盡量避免造成索引失效的情況

041 使用Order By時能否通過索引排序？

沒有過濾條件不走索引

042 通過索引排序內(nèi)部流程是什么？

select name,id from user where name like ‘%明’ order by name；

select name,id，age from user where name like ‘%明’

關(guān)鍵配置：

sort_buffer可供排序的內(nèi)存緩沖區(qū)大小max_length_for_sort_data 單行所有字段總和限制，超過這個大小啟動雙路排序

通過索引檢過濾篩選條件索到需要排序的字段+其他字段（如果是符合索引）判斷索引內(nèi)容是否覆蓋select的字段

如果覆蓋索引，select的字段和排序都在索引上，那么在內(nèi)存中進行排序，排序后輸出結(jié)果如果索引沒有覆蓋查詢字段，接下來計算select的字段是否超過max_length_for_sort_data限制，如果超過，啟動雙路排序，否則使用單路

043 什么是雙路排序和單路排序

單路排序：一次取出所有字段進行排序，內(nèi)存不夠用的時候會使用磁盤

雙路排序：取出排序字段進行排序，排序完成后再次回表查詢所需要的其他字段

如果不在索引列上，filesort有兩種算法： mysql就要啟動雙路排序和單路排序

雙路排序（慢）

Select id,age,name from stu order by name;

? MySQL 4.1之前是使用雙路排序，字面意思就是兩次掃描磁盤，最終得到數(shù)據(jù)，讀取行指針和order by列，對他們進行排序，然后掃描已經(jīng)排序好的列表，按照列表中的值重新從列表中讀取對應(yīng)的數(shù)據(jù)輸出

? 從磁盤取排序字段，在buffer進行排序，再從磁盤取其他字段。

? 取一批數(shù)據(jù)，要對磁盤進行兩次掃描，眾所周知，I\O是很耗時的，所以在mysql4.1之后，出現(xiàn)了第二種改進的算法，就是單路排序。

單路排序（快）

從磁盤讀取查詢需要的所有列，按照order by列在buffer對它們進行排序，然后掃描排序后的列表進行輸出，它的效率更快一些，避免了第二次讀取數(shù)據(jù)。并且把隨機IO變成了順序IO，但是它會使用更多的空間，因為它把每一行都保存在內(nèi)存中了。

結(jié)論及引申出的問題

但是用單路有問題

在sort_buffer中，單路比多路要多占用很多空間，因為單路是把所有字段都取出, 所以有可能取出的數(shù)據(jù)的總大小超出了sort_buffer的容量，導致每次只能取sort_buffer容量大小的數(shù)據(jù)，進行排序（創(chuàng)建tmp文件，多路合并），排完再取sort_buffer容量大小，再排……從而多次I/O。

單路本來想省一次I/O操作，反而導致了大量的I/O操作，反而得不償失。

優(yōu)化策略

? 增大sort_buffer_size參數(shù)的設(shè)置

? 增大max_length_for_sort_data參數(shù)的設(shè)置

? 減少select 后面的查詢的字段。禁止使用select *

提高Order By的速度

\1. Order by時select * 是一個大忌。只Query需要的字段，這點非常重要。在這里的影響是：

l 當Query的字段大小總和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 類型時，會用改進后的算法——單路排序，否則用老算法——多路排序。

l 兩種算法的數(shù)據(jù)都有可能超出sort_buffer的容量，超出之后，會創(chuàng)建tmp文件進行合并排序，導致多次I/O，但是用單路排序算法的風險會更大一些，所以要提高sort_buffer_size。

\2. 嘗試提高 sort_buffer_size

l 不管用哪種算法，提高這個參數(shù)都會提高效率，當然，要根據(jù)系統(tǒng)的能力去提高，因為這個參數(shù)是針對每個進程（connection）的 1M-8M之間調(diào)整。 MySQL5.7和8.0，InnoDB存儲引擎默認值是1048576字節(jié)，1MB。

SHOW VARIABLES LIKE ‘%sort_buffer_size%’;

\3. 嘗試提高 max_length_for_sort_data

l 提高這個參數(shù)，會增加用改進算法的概率。

SHOW VARIABLES LIKE ‘%max_length_for_sort_data%’;

#5.7默認1024字節(jié)

#8.0默認4096字節(jié)

l 但是如果設(shè)的太高，數(shù)據(jù)總?cè)萘砍鰏ort_buffer_size的概率就增大，明顯癥狀是高的磁盤I/O活動和低的處理器使用率。如果需要返回的列的總長度大于max_length_for_sort_data，使用雙路算法，否則使用單路算法。1024-8192字節(jié)之間調(diào)整

044 group by 分組和order by在索引使用上有什么區(qū)別？

group by 使用索引的原則幾乎跟order by一致，唯一區(qū)別：

group by 先排序再分組，遵照索引建的最佳左前綴法則group by沒有過濾條件，也可以用上索引。Order By 必須有過濾條件才能使用上索引。

045 如果表中有字段為null，又被經(jīng)常查詢該不該給這個字段創(chuàng)建索引？

應(yīng)該創(chuàng)建索引，使用的時候盡量使用is null判斷。

IS NOT NULL 失效和 IS NULL

EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL;

EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL; --索引失效

-- 將 id>20000 的數(shù)據(jù)的 name 值改為 NULL

UPDATE emp SET `name` = NULL WHERE `id` > 20000;

-- 執(zhí)行查詢分析，可以發(fā)現(xiàn) IS NOT NULL 使用了索引

-- 具體多少條記錄的值為NULL可以使索引在IS NOT NULL的情況下生效，由查詢優(yōu)化器的算法決定