1 初識索引
索引在MySQL中也叫是一種“鍵”，是存儲引擎用于快速找到記錄的一種數據結構。
索引對于良好的性能非常關鍵，尤其是當表中的數據量越來越大時，索引對于性能的影響愈發重要。索引優化應該是對查詢性能優化最有效的手段了。索引能夠輕易將查詢性能提高好幾個數量級。
索引相當于字典的音序表，如果要查某個字，如果不使用音序表，則需要從幾百頁中逐頁去查。
2 索引的原理
索引的目的在于提高查詢效率，與我們查閱圖書所用的目錄是一個道理：先定位到章，然后定位到該章下的一個小節，然后找到頁數。相似的例子還有：查字典，查火車車次，飛機航班等
本質都是：通過不斷地縮小想要獲取數據的范圍來篩選出最終想要的結果，同時把隨機的事件變成順序的事件，也就是說，有了這種索引機制，我們可以總是用同一種查找方式來鎖定數據。
2.1 B+樹
### b+樹性質 1.索引字段要盡量的小： 我們知道IO次數取決于b+數的高度h，假設當前數據表的數據為N，每個磁盤塊的數據項的數量是m，則有h=㏒(m+1)N，當數據量N一定的情況下，m越大，h越小；而m = 磁盤塊的大小 / 數據項的大小，磁盤塊的大小也就是一個數據頁的大小，是固定的，如果數據項占的空間越小，數據項的數量越多，樹的高度越低。這就是為什么每個數據項，即索引字段要盡量的小，比如int占4字節，要比bigint8字節少一半。這也是為什么b+樹要求把真實的數據放到葉子節點而不是內層節點，一旦放到內層節點，磁盤塊的數據項會大幅度下降，導致樹增高。當數據項等于1時將會退化成線性表。 2.索引的最左匹配特性： 當b+樹的數據項是復合的數據結構，比如(name,age,sex)的時候，b+數是按照從左到右的順序來建立搜索樹的，比如當(張三,20,F)這樣的數據來檢索的時候，b+樹會優先比較name來確定下一步的所搜方向，如果name相同再依次比較age和sex，最后得到檢索的數據；但當(20,F)這樣的沒有name的數據來的時候，b+樹就不知道下一步該查哪個節點，因為建立搜索樹的時候name就是第一個比較因子，必須要先根據name來搜索才能知道下一步去哪里查詢。比如當(張三,F)這樣的數據來檢索時，b+樹可以用name來指定搜索方向，但下一個字段age的缺失，所以只能把名字等于張三的數據都找到，然后再匹配性別是F的數據了， 這個是非常重要的性質，即索引的最左匹配特性。
2.2 聚合索引與輔助索引
數據庫中的B+樹索引可以分為聚集索引（clustered index）和輔助索引（secondary index），
聚集索引與輔助索引相同的是：不管是聚集索引還是輔助索引，其內部都是B+樹的形式，即高度是平衡的，葉子結點存放著所有的數據。
聚集索引與輔助索引不同的是：葉子結點存放的是否是一整行的信息
（1）聚集索引
（2）輔助索引
聚集索引與輔助索引區別
3 MySQL索引
3.1 了解索引
## 索引的功能 1. 索引的功能就是加速查找2. mysql中的primary key，unique，聯合唯一也都是索引，這些索引除了加速查找以外，還有約束的功能
## 創建/刪除索引的語法 ## #方法一：創建表時 CREATE TABLE 表名 ( 字段名1 數據類型 [完整性約束條件…], 字段名2 數據類型 [完整性約束條件…], [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [索引名] (字段名[(長度)] [ASC |DESC]) ); #方法二：CREATE在已存在的表上創建索引 CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(長度)] [ASC |DESC]) ; #方法三：ALTER TABLE在已存在的表上創建索引 ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(長度)] [ASC |DESC]) ; #刪除索引：DROP INDEX 索引名 ON 表名字;
示例
3.2 索引類型
#### 常用索引 #### #1、普通索引INDEX：加速查找 #2、唯一索引： -主鍵索引PRIMARY KEY：加速查找+約束（不為空、不能重復） -唯一索引UNIQUE:加速查找+約束（不能重復） #3、聯合索引： -PRIMARY KEY(id,name):聯合主鍵索引 -UNIQUE(id,name):聯合唯一索引 -INDEX(id,name):聯合普通索引
# 組合索引(最左前綴匹配)： - create unique index 索引名稱 on 表名(列名,列名) - drop unique index 索引名稱 on 表名 - create index ix_name_email on userinfo3(name,email,) - 最左前綴匹配【一定要包含最左邊的】 √ select * from userinfo3 where name='alex'; √ select * from userinfo3 where name='alex' and email='asdf'; × select * from userinfo3 where email='alex@qq.com'; # 組合索引效率 > 索引合并 組合索引 - (name,email,) select * from userinfo3 where name='alex' and email='asdf'; select * from userinfo3 where name='alex'; 索引合并【把多個單列索引合并使用】： - name - email select * from userinfo3 where name='alex' and email='asdf'; select * from userinfo3 where name='alex'; select * from userinfo3 where email='asdf';
#覆蓋索引：在索引文件中直接獲取數據。 即從輔助索引中就可以得到查詢記錄，而不需要查詢聚集索引中的記錄。
4 正確使用索引
（1）范圍問題，或者說條件不明確，條件中出現這些符號或關鍵字：>、>=、<、<=、!= 、between...and...、like、大于號、小于號
（2）盡量選擇區分度高的列作為索引,區分度的公式是count(distinct col)/count(*)，表示字段不重復的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、性別字段可能在大數據面前區分度就是0
（3）索引列不能在條件中參與計算，保持列“干凈”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是數據表中的字段值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’)
（4）and/or
#1、and與or的邏輯 條件1 and 條件2:所有條件都成立才算成立，但凡要有一個條件不成立則最終結果不成立 條件1 or 條件2:只要有一個條件成立則最終結果就成立 #2、and的工作原理 條件： a = 10 and b = 'xxx' and c > 3 and d =4 索引： 制作聯合索引(d,a,b,c) 工作原理: 對于連續多個and：mysql會按照聯合索引，從左到右的順序找一個區分度高的索引字段(這樣便可以快速鎖定很小的范圍)，加速查詢，即按照d—>a->b->c的順序 #3、or的工作原理 條件： a = 10 or b = 'xxx' or c > 3 or d =4 索引： 制作聯合索引(d,a,b,c) 工作原理: 對于連續多個or：mysql會按照條件的順序，從左到右依次判斷，即a->b->c->d
（5）最左前綴匹配原則
（6）其他注意事項
- 避免使用select * - 使用count(1) - 創建表時盡量使用 char 代替 varchar - 表的字段順序固定長度的字段優先 - 組合索引代替多個單列索引（由于mysql中每次只能使用一個索引，所以經常使用多個條件查詢時更適合使用組合索引） - 盡量使用短索引 - 使用連接（JOIN）來代替子查詢(Sub-Queries) - 連表時注意條件類型需一致 - 索引散列值（重復少）不適合建索引，例：性別不適合
5 查詢優化神器-explain
優化語句基本上都是在優化rows。具體用法和字段含義可以參考官網 explain-output
# 執行計劃：讓mysql預估執行操作(一般正確) all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const #id,email # 慢： select * from userinfo3 where name='alex' explain select * from userinfo3 where name='alex' type: ALL(全表掃描) select * from userinfo3 limit 1; # 快： select * from userinfo3 where email='alex' type: const(走索引)
6 慢查詢優化的基本步驟
# 0.先運行看看是否真的很慢，注意設置SQL_NO_CACHE # 1.where條件單表查，鎖定最小返回記錄表。這句話的意思是把查詢語句的where都應用到表中返回的記錄數最小的表開始查起，單表每個字段分別查詢，看哪個字段的區分度最高 # 2.explain查看執行計劃，是否與1預期一致（從鎖定記錄較少的表開始查詢） # 3.order by limit 形式的sql語句讓排序的表優先查 # 4.了解業務方使用場景 # 5.加索引時參照建索引的幾大原則 # 6.觀察結果，不符合預期繼續從0分析
7 慢日志管理
# 慢日志 - 執行時間 > 10 - 未命中索引 - 日志文件路徑 配置： - 內存 show variables like '%query%'; show variables like '%queries%'; set global 變量名 = 值 - 配置文件 mysqld --defaults-file='E:wupeiqimysql-5.7.16-winx64mysql-5.7.16-winx64my-default.ini' my.conf內容： slow_query_log = ON slow_query_log_file = D:/.... # 注意：修改配置文件之后，需要重啟服務
