前言
文章內容輸出來源：拉勾教育Java高薪訓練營；
mysql 索引我們在面試是必問的，剛好我在拉勾訓練營學習了 mysql 索引的相關知識，這里整理下來，自己對MySQL 索引有了全面了理解，面試的時候再也不怕啦，
索引型別
索引可以提升查詢速度，會影響where查詢，以及order by排序，MySQL索引型別如下：
從索引存盤結構劃分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
從應用層次劃分：普通索引、唯一索引、主鍵索引、復合索引
從索引鍵值型別劃分：主鍵索引、輔助索引（二級索引）
從資料存盤和索引鍵值邏輯關系劃分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）
準備作業
首先我們來創建一張表吧，然后創建索引的操作在這張表中來進行，
DROP TABLE IF EXISTS `r_resume`;
CREATE TABLE `r_resume` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sex` varchar(10) DEFAULT NULL COMMENT '性別',
`birthday` varchar(30) DEFAULT NULL COMMENT '出生日期',
`work_year` varchar(100) DEFAULT NULL COMMENT '作業年限',
`phone` varchar(20) DEFAULT NULL COMMENT '手機號碼',
`email` varchar(100) DEFAULT NULL COMMENT '郵箱',
`status` varchar(80) DEFAULT NULL COMMENT '目前狀態',
`resumeName` varchar(500) DEFAULT NULL COMMENT '簡歷名稱',
`name` varchar(40) DEFAULT NULL,
`createTime` datetime DEFAULT NULL COMMENT '創建日期',
`headPic` varchar(100) DEFAULT NULL COMMENT '頭像',
`isDel` int(2) DEFAULT NULL COMMENT '是否洗掉 默認值0-未洗掉 1-已洗掉',
`updateTime` datetime DEFAULT NULL COMMENT '簡歷更新時間',
`userId` int(11) DEFAULT NULL COMMENT '用戶ID',
`isDefault` int(2) DEFAULT NULL COMMENT '是否為默認簡歷 0-默認 1-非默認',
`highestEducation` varchar(20) DEFAULT '' COMMENT '最高學歷',
`deliverNearByConfirm` int(2) DEFAULT '0' COMMENT '投遞附件簡歷確認 0-需要確認 1-不需要確認',
`refuseCount` int(11) NOT NULL DEFAULT '0' COMMENT '簡歷被拒絕次數',
`markCanInterviewCount` int(11) NOT NULL DEFAULT '0' COMMENT '被標記為可面試次數',
`haveNoticeInterCount` int(11) NOT NULL DEFAULT '0' COMMENT '已通知面試次數',
`oneWord` varchar(100) DEFAULT '' COMMENT '一句話介紹自己',
`liveCity` varchar(100) DEFAULT '' COMMENT '居住城市',
`resumeScore` int(3) DEFAULT NULL COMMENT '簡歷得分',
`userIdentity` int(1) DEFAULT '0' COMMENT '用戶身份1-學生 2-工人',
`isOpenResume` int(1) DEFAULT '3' COMMENT '人才搜索-開放簡歷 0-關閉，1-打開，2-簡歷未達到投放標準被動關閉 3-從未設定過開放簡歷',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2195388 DEFAULT CHARSET=utf8;
普通索引
這是最基本的索引型別，基于普通欄位建立的索引，沒有任何限制，
創建普通索引的方法如下：
##方式一
create index 索引名 on 表名(欄位);
create index index_work_year on r_resume(work_year);
##方式二
ALTER TABLE 表名 add INDEX 索引名(欄位);
ALTER TABLE r_resume add INDEX index_sex(sex);
## 查看索引
show index from 表名
show index from r_resume
## 洗掉索引
drop index 索引名 on 表名
唯一索引
和普通索引的區別，索引欄位的值必須唯一，但是允許有空值，在創建或者修改表是追加唯一約束，就會自動的創建對應的唯一索引，
創建唯一索引的方法如下：
##方式一
create unique index 索引名 on 表名(欄位);
CREATE UNIQUE INDEX index_userid on r_resume(userId)
##方式二
ALTER TABLE 表名 add INDEX 索引名(欄位);
ALTER TABLE r_resume add UNIQUE INDEX index_userid(userId);
主鍵索引
它是一種特殊的唯一索引，不允許有空值，在創建或修改表時追加主鍵約束即可，每個表只能有一個主鍵，
創建主鍵索引的方法如下：
alter table 表名 add primary KEY(欄位名)
復合索引
單一索引是指索引列為一列的情況，即新建索引的陳述句只實施在一列上；用戶可以在多個列上建立索引，這種索引叫做組復合索引（組合索引），復合索引可以代替多個單一索引，相比多個單一索引復合索引所需的開銷更小，索引同時有兩個概念叫做窄索引和寬索引，窄索引是指索引列為1-2列的索引，寬索引也就是索引列超過2列的索引，設計索引的一個重要原則就是能用窄索引不用寬索引，因為窄索引往往比組合索引更有效
創建組合索引的方法如下：
##方式一
create index 索引名 on 表名(欄位1,欄位2);
create index index_work_year_sex on r_resume(work_year,sex)
##方式二
ALTER TABLE 表名 add INDEX 索引名(欄位1,欄位2);
ALTER TABLE r_resume add INDEX index_work_year_sex(work_year,sex);
復合索引使用注意事項：
何時使用復合索引，要根據where條件建索引，注意不要過多使用索引，過多使用會對更新操作效率有很大影響，
如果表已經建立了(col1，col2)，就沒有必要再單獨建立（col1）；如果現在有(col1)索引，如果查詢需要col1和col2條件，可以建立(col1,col2)復合索引，對于查詢有一定提高，
全文索引
查詢操作在資料量比較少時，可以使用like模糊查詢，但是對于大量的文本資料檢索，效率很低，如果使用全文索引，查詢速度會比like快很多倍，在MySQL 5.6 以前的版本，只有MyISAM存盤引擎支持全文索引，從MySQL 5.6開始MyISAM和InnoDB存盤引擎均支持，
創建全文索引的方法如下：
##方式一
create FULLTEXT index 索引名 on 表名(欄位1);
create fulltext index index_status on r_resume(`status`)
##方式二
ALTER TABLE 表名 add FULLTEXT INDEX 索引名(欄位1);
ALTER TABLE r_resume add fulltext INDEX index_status(`status`);
和常用的like模糊查詢不同，全文索引有自己的語法格式，使用 match 和 against 關鍵字，比如
select * from r_resume where MATCH(`status`) AGAINST('我目前已離職')
全文索引使用注意事項：
show variables like '%ft%';
-
全文索引必須在字串、文本欄位上建立，
-
全文索引欄位值必須在最小字符和最大字符之間的才會有效，（innodb：3-84；myisam：4-84）
-
全文索引欄位值要進行切詞處理，按syntax字符進行切割，例如b+aaa，切分成b和aaa
-
全文索引匹配查詢，默認使用的是等值匹配，例如a匹配a，不會匹配ab,ac，如果想匹配可以在布爾模式下搜索a*
索引原理
MySQL官方對索引定義：是存盤引擎用于快速查找記錄的一種資料結構，需要額外開辟空間和資料維護作業，
索引是物理資料頁存盤，在資料檔案中（InnoDB，ibd檔案），利用資料頁(page)存盤，
索引可以加快檢索速度，但是同時也會降低增刪改操作速度，索引維護需要代價，
索引涉及的理論知識：二分查找法、Hash 和 B+Tree，
B 樹結構
-
索引值和data資料分布在整棵樹結構中
-
每個節點可以存放多個索引值及對應的data資料
-
樹節點中的多個索引值從左到右升序排列
B樹的搜索：從根節點開始，對節點內的索引值序列采用二分法查找，如果命中就結束查找，沒有命中會進入子節點重復查找程序，直到所對應的的節點指標為空，或已經是葉子節點了才結束，
B+ 樹結構
-
非葉子節點不存盤data資料，只存盤索引值，這樣便于存盤更多的索引值
-
葉子節點包含了所有的索引值和data資料
-
葉子節點用指標連接，提高區間的訪問性能
相比B樹，B+樹進行范圍查找時，只需要查找定位兩個節點的索引值，然后利用葉子節點的指標進行遍歷即可，而B樹需要遍歷范圍內所有的節點和資料，顯然B+Tree效率高，
聚集索引
聚簇索引和非聚簇索引：B+Tree的葉子節點存放主鍵索引值和行記錄就屬于聚簇索引；如果索引值和行記錄分開存放就屬于非聚簇索引，
在InnoDB引擎中，主鍵索引采用的就是聚簇索引結構存盤，
聚簇索引是一種資料存盤方式，InnoDB的聚簇索引就是按照主鍵順序構建 B+Tree結構，B+Tree的葉子節點就是行記錄，行記錄和主鍵值緊湊地存盤在一起， 這也意味著 InnoDB 的主鍵索引就是資料表本身，它按主鍵順序存放了整張表的資料，占用的空間就是整個表資料量的大小，通常說的主鍵索引就是聚集索引，
索引分析與優化
Explain
MySQL 提供了一個 EXPLAIN 命令，它可以對 SELECT 陳述句進行分析，并輸出 SELECT 執行的詳細資訊，供開發人員有針對性的優化，例如：
EXPLAIN select * from r_resume where MATCH(`status`) AGAINST('我目前已離職') ;
explain select * from r_resume where id> 2195333;
-
select_type
表示查詢的型別，常用的值如下：
-
SIMPLE ： 表示查詢陳述句不包含子查詢或union
-
PRIMARY：表示此查詢是最外層的查詢
-
UNION：表示此查詢是UNION的第二個或后續的查詢
-
DEPENDENT UNION：UNION中的第二個或后續的查詢陳述句，使用了外面查詢結果
-
UNION RESULT：UNION的結果
-
SUBQUERY：SELECT子查詢陳述句
-
DEPENDENT SUBQUERY：SELECT子查詢陳述句依賴外層查詢的結果，
-
explain select * from r_resume where id> 2195333 union select * from r_resume where id=1;
explain select * from r_resume where id in(select id from r_resume where sex='男');
-
type
表示存盤引擎查詢資料時采用的方式，比較重要的一個屬性，通過它可以判斷出查詢是全表掃描還是基于索引的部分掃描，常用屬性值如下，從上至下效率依次增強，
-
ALL：表示全表掃描，性能最差，
-
index：表示基于索引的全表掃描，先掃描索引再掃描全表資料，
-
range：表示使用索引范圍查詢，使用>、>=、<、<=、in等等，
-
ref：表示使用非唯一索引進行單值查詢，
-
eq_ref：一般情況下出現在多表join查詢，表示前面表的每一個記錄，都只能匹配后面表的一行結果，
-
const：表示使用主鍵或唯一索引做等值查詢，常量查詢，
-
NULL：表示不用訪問表，速度最快，
-
explain select * from r_resume where id= 2195333 union select * from r_resume where id=1;
explain select * from r_resume where id in(select id from r_resume where sex='男');
explain select * from r_resume where sex='男';
explain select * from r_resume where id> 2195333;
explain select * from r_resume where id= 2195333;
-
possible_keys：表示查詢時能夠使用到的索引，注意并不一定會真正使用，顯示的是索引名稱，
-
key：表示查詢時真正使用到的索引，顯示的是索引名稱，
-
rows：MySQL查詢優化器會根據統計資訊，估算SQL要查詢到結果需要掃描多少行記錄，原則上rows是越少效率越高，可以直觀的了解到SQL效率高低，
-
key_len：表示查詢使用了索引的位元組數量，可以判斷是否全部使用了組合索引，
key_len的計算規則如下：
-
字串型別
字串長度跟字符集有關：latin1=1、gbk=2、utf8=3、utf8mb4=4
char(n)：n*字符集長度
varchar(n)：n * 字符集長度 + 2位元組
-
數值型別
TINYINT：1個位元組
SMALLINT：2個位元組
MEDIUMINT：3個位元組
INT、FLOAT：4個位元組
BIGINT、DOUBLE：8個位元組
-
時間型別
DATE：3個位元組
TIMESTAMP：4個位元組
DATETIME：8個位元組
-
欄位屬性
NULL屬性占用1個位元組，如果一個欄位設定了NOT NULL，則沒有此項，
-
-
Extra：表示很多額外的資訊，各種操作會在Extra提示相關資訊，常見幾種如下：
- Using where: 表示查詢需要通過索引回表查詢資料
- Using index: 表示查詢需要通風索引就可以滿足所需資料
- Using fifilesort：查詢出來的結果需要額外的排序，資料量小在記憶體，大的話在磁盤，因此有Using fifilesort建議優化，
- Using temprorary：查詢使用到了臨時表，一般出現于去重、分組等操作，
回表查詢
在之前介紹過，InnoDB索引有聚簇索引和輔助索引，聚簇索引的葉子節點存盤行記錄，InnoDB必須要有，且只有一個，輔助索引的葉子節點存盤的是主鍵值和索引欄位值，通過輔助索引無法直接定位行記錄，通常情況下，需要掃碼兩遍索引樹，先通過輔助索引定位主鍵值，然后再通過聚簇索引定位行記錄，這就叫做回表查詢，它的性能比掃一遍索引樹低，
總結：通過索引查詢主鍵值，然后再去聚簇索引查詢記錄資訊
覆寫索引
在MySQL官網，類似的說法出現在explain查詢計劃優化章節，即explain的輸出結果Extra欄位為Using index時，能夠觸發索引覆寫，不管是SQL-Server官網，還是MySQL官網，都表達了：只需要在一棵索引樹上就能獲取SQL所需的所有列資料，無需回表，速度更快，這就叫做索引覆寫，
實作索引覆寫最常見的方法就是：將被查詢的欄位，建立到組合索引，
最左前綴原則
復合索引使用時遵循最左前綴原則，最左前綴顧名思義，就是最左優先，即查詢中使用到最左邊的列，那么查詢就會使用到索引，如果從索引的第二列開始查找，索引將失效，
LIKE 查詢
MySQL 在使用 like 模糊查詢時，索引能不能起作用？
MySQL在使用Like模糊查詢時，索引是可以被使用的，只有把%字符寫在后面才會使用到索引，
explain select * from r_resume where `status` like '%離職%';（沒有用到索引）
explain select * from r_resume where `status` like '離職%'; （用到了索引）
explain select * from r_resume where `status` like '離職%'; （沒有用到）
NULL 查詢
如果 MySQL 表的某一列含有 NULL 值，那么包含該列的索引是否有效？
有效，
explain select * from r_resume where email is null;
對MySQL來說，NULL是一個特殊的值，從概念上講，NULL意味著“一個未知值”，它的處理方式與其他值有些不同，比如：不能使用=，<，>這樣的運算子，對NULL做算術運算的結果都是NULL，count時不會包括NULL行等，NULL比空字串需要更多的存盤空間等，
雖然MySQL可以在含有NULL的列上使用索引，但NULL和其他資料還是有區別的，不建議列上允許為NULL，最好設定NOT NULL，并給一個默認值，比如0和 ‘’ 空字串等，如果是datetime型別，也可以設定系統當前時間或某個固定的特殊值，例如’1970-01-01 00:00:00’，
查詢優化
慢查詢定位
1、開啟慢查詢
查看 MySQL 資料庫是否開啟了慢查詢日志和慢查詢日志檔案的存盤位置的命令如下：
show variables like '%slow_query_log%';
通過如下命令開啟慢查詢日志：
SET global slow_query_log = ON; //開啟慢查詢的開關
SET global slow_query_log_file = 'OAK-slow.log'; //修改慢查詢日志存放的位置
SET global log_queries_not_using_indexes = ON; //沒有用到索引的查詢就會記錄
SET long_query_time = 10;//查詢時間超過10s 就會記錄
2、慢日志查詢方式
-
直接找到對應的檔案，通過記事本查看，
time：日志記錄的時間
User@Host：執行的用戶及主機
Query_time：執行的時間
Lock_time：鎖表時間
Rows_sent：發送給請求方的記錄數，結果數量
Rows_examined：陳述句掃描的記錄條數
SET timestamp：陳述句執行的時間點
select…：執行的具體的SQL陳述句
-
使用mysqldumpslow查看
MySQL 提供了一個慢查詢日志分析工具mysqldumpslow，可以通過該工具分析慢查詢日志內容，
在 MySQL bin目錄下執行下面命令可以查看該使用格式，
perl mysqldumpslow.pl --help
慢查詢優化
慢查詢原因總結
-
全表掃描：explain分析type屬性all
-
全索引掃描：explain分析type屬性index
-
索引過濾性不好：靠索引欄位選型、資料量和狀態、表設計
-
頻繁的回表查詢開銷：盡量少用select *，使用覆寫索引
如何判斷是否為慢查詢？
MySQL判斷一條陳述句是否為慢查詢陳述句，主要依據SQL陳述句的執行時間，它把當前陳述句的執行時間跟 long_query_time 引數做比較，如果陳述句的執行時間 > long_query_time，就會把這條執行陳述句記錄到慢查詢日志里面，long_query_time 引數的默認值是 10s，該引數值可以根據自己的業務需要進行調整，
如何判斷是否應用了索引？
SQL陳述句是否使用了索引，可根據SQL陳述句執行程序中有沒有用到表的索引，可通過 explain 命令分析查看，檢查結果中的 key 值，是否為NULL，
應用了索引是否一定快？
查詢是否使用索引，只是表示一個SQL陳述句的執行程序；而是否為慢查詢，是由它執行的時間決定的，也就是說是否使用了索引和是否是慢查詢兩者之間沒有必然的聯系，
我們在使用索引時，不要只關注是否起作用，應該關心索引是否減少了查詢掃描的資料行數，如果掃描行數減少了，效率才會得到提升，對于一個大表，不止要創建索引，還要考慮索引過濾性，過濾性好，執行速度才會快，
如何提高過濾性？
靠索引欄位選型、資料量和狀態、表設計，
假如有一個5000萬記錄的用戶表，通過sex='男’索引過濾后，還需要定位3000萬，SQL執行速度也不會很快，其實這個問題涉及到索引的過濾性，比如1萬條記錄利用索引過濾后定位10條、100條、1000條，那他們過濾性是不同的，索引過濾性與索引欄位、表的資料量、表設計結構都有關系，
分頁查詢優化
一般性分頁
般的分頁查詢使用簡單的 limit 子句就可以實作，limit格式如下：
SELECT * FROM 表名 LIMIT [offset,] rows
第一個引數指定第一個回傳記錄行的偏移量，注意從0開始；
第二個引數指定回傳記錄行的最大數目；
如果只給定一個引數，它表示回傳最大的記錄行數目；
如果偏移量固定，回傳記錄量對執行時間有什么影響？
select * from user limit 10000,1;
select * from user limit 10000,10;
select * from user limit 10000,100;
select * from user limit 10000,1000;
select * from user limit 10000,10000;
結果：在查詢記錄時，回傳記錄量低于100條，查詢時間基本沒有變化，差距不大，隨著查詢記錄量越大，所花費的時間也會越來越多，
如果查詢偏移量變化，回傳記錄數固定對執行時間有什么影響？
select * from user limit 1,100;
select * from user limit 10,100;
select * from user limit 100,100;
select * from user limit 1000,100;
select * from user limit 10000,100;
結果：在查詢記錄時，如果查詢記錄量相同，偏移量超過100后就開始隨著偏移量增大，查詢時間急劇的增加，（這種分頁查詢機制，每次都會從資料庫第一條記錄開始掃描，越往后查詢越慢，而且查詢的資料越多，也會拖慢總查詢速度，）
分頁優化方案：
-
利用覆寫索引優化
select * from user limit 10000,100; select id from user limit 10000,100; -
利用子查詢優化
select * from user limit 10000,100; select * from user where id>= (select id from user limit 10000,1) limit 100;
使用了id做主鍵比較(id>=)，并且子查詢使用了覆寫索引進行優化，
總結
拉勾老師講解的筆記很詳細，根據老師講的，自己動手練習一遍，感覺對自己幫助很大，覺得有用的小伙伴趕緊收藏吧，
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/73837.html
標籤：其他