1. 索引作用
作用: 提供類似于書中的目錄的作用,目的是為了優(yōu)化查詢
2. 索引的種類(算法)
B樹索引
hash索引
R樹索引
full text
GIS
3. B樹 基于不同的查找算法分類
1.) B - tree
2.) B + tree 在范圍查詢方面提供了更好的性能(> >= < <= )
image.png
3.) B*thee
4. 按功能上分類
4.1 輔助索引(s)如何構(gòu)建B樹結(jié)構(gòu)
(1). 索引是基于表中,列(索引鍵)的值生成的B樹結(jié)構(gòu)
(2). 首先提取此列所有的值,進(jìn)行自動排序
(3). 將排好序的值,均勻的分布到索引樹的葉子節(jié)點(diǎn)中(16K)
(4). 然后生成此索引鍵值所對應(yīng)得后端數(shù)據(jù)頁的指針
(5). 生成枝節(jié)點(diǎn)和根節(jié)點(diǎn),根據(jù)數(shù)據(jù)量級和索引鍵長度,生成合適的索引樹高度
id name age gender
select * from t1 where id=10;
問題: 基于索引鍵做where查詢,對于id列是順序IO,但是對于其他列的查詢,可能是隨機(jī)IO.
4.2 輔助索引分類
1.) 普通的單列輔助索引
2.) 聯(lián)合索引 (多列作為索引條件,生成索引樹,)
3.) 唯一索引 (索引列的值都是唯一的)
4.2 聚集索引 (C)
4.2.1 前提
(1)表中設(shè)置了主鍵,主鍵列就會自動被作為聚集索引.
(2)如果沒有主鍵,可以選擇唯一鍵作為聚集索引.
(3)聚集索引必須在建表時才有意義,一般是表的無關(guān)列(ID)
4.2.2 輔助索引(S)怎么構(gòu)建B樹結(jié)構(gòu)
(1) 在建表時,設(shè)置了主鍵列(ID)
(2) 在將來錄入數(shù)據(jù)時,就會按照ID列的順序存儲到磁盤上.(我們又稱之為聚集索引組織表)
(3) 將排好序的整行數(shù)據(jù),生成葉子節(jié)點(diǎn).可以理解為,磁盤的數(shù)據(jù)頁就是葉子節(jié)點(diǎn)
4.3 聚集索引和輔助索引構(gòu)成區(qū)別
聚集索引只能有一個,非空唯一,一般時主鍵
輔助索引,可以有多個,是配合聚集索引使用的
聚集索引葉子節(jié)點(diǎn),就是磁盤的數(shù)據(jù)行存儲的數(shù)據(jù)頁
MySQL是根據(jù)聚集索引,組織存儲數(shù)據(jù)的,如果創(chuàng)表時沒有沒有聚集索引,數(shù)據(jù)會隨機(jī)分配儲存在表中的,
輔助索引,只會提取索引鍵值,進(jìn)行自動排序生成B樹結(jié)構(gòu)
5. 索引樹的高度
影響索引樹的高度因素
1.) 數(shù)據(jù)量級(行數(shù))
解決辦法: 分表 分庫 分布式
2.) 索引列值長度
解決辦法: 前綴索引
3.) 數(shù)據(jù)類型
解決辦法: 可變長度字符串,用varchar, 固定填寫的,用enum
6. 索引命令
6.1 創(chuàng)建索引
1) 普通的單列輔助索引
alter table 表名 add index 索引名(列);
2) 聯(lián)合索引
alter table 表名 add index 索引名(列1,列2);
注: 創(chuàng)建聯(lián)合索引時, 列的先后順序不一樣,搜索的時間也不一樣
3) 前綴索引
alter table 表名 add index 索引名(列(取列的數(shù)據(jù)前多少位));
4) 唯一索引
alter table 表名 add unique index 索引名(列);
注: 建立唯一索引時.列內(nèi)的每行字符必須是唯一的,不能有重復(fù)
6.2 查看索引
desc 表名;
只顯示那列創(chuàng)建了索引和索引類型
show index from 表名;
顯示索引名和索引列
6.3 刪除索引
alter table 表名 drop index 索引名;
7. 執(zhí)行計(jì)劃獲取分析
(1)獲取到的是優(yōu)化器選擇完成的,他認(rèn)為代價最小的執(zhí)行計(jì)劃.
作用: 語句執(zhí)行前,先看執(zhí)行計(jì)劃信息,可以有效的防止性能較差的語句帶來的性能問題.
如果業(yè)務(wù)中出現(xiàn)了慢語句，我們也需要借助此命令進(jìn)行語句的評估排惨，分析優(yōu)化方案。
(2) select 獲取數(shù)據(jù)的方法
1.) 全表掃描(應(yīng)當(dāng)盡量避免,因?yàn)樾阅艿?
2.) 索引掃描
3.) 獲取不到數(shù)據(jù)
7.1 執(zhí)行計(jì)劃獲取
desc select [查詢的內(nèi)容] from 表 where [條件] ...
7.2 表中的中的信息
1.) table: 數(shù)據(jù)在的表
2.) possible_keys: 可能使用的索引
3.) key: 查詢時使用的索引
4.) type: 索引類型
5.) extra: 額外的信息
7.3 type 類型
從劣到優(yōu)(all index range ref eq_ref sysem,const)
7.3.1 all
不使用索引, 全表掃描.
1.) 查詢條件列 沒用索引
2.) 查詢條件中使用 <> , not in , like %條例%
注: 使用的輔助索引列 會出現(xiàn)這種情況
對于聚焦索引列,以上情況依然使用索引
7.3.2 index
全索引掃描
1.) 查詢需要獲取整個索引樹中的值時,
2.) 聯(lián)合索引中,任何一個非最左列作為查詢條件時,
7.3.3 range
索引范圍掃描
1.) 輔助索引 條件為 > < >= <= like in or
2.) 主鍵 <> , not in
例子:
1.
desc select * from city where id<5;
2.
desc select * from city where countrycode like 'CH%';
3.
desc select * from city where countrycode in ('CHN','USA');
注意:
1和2例子中,可以享受到B+樹的優(yōu)勢,但是3例子中是不能享受的.
所以,我們可以將3號列子改寫:
desc select * from city where countrycode='CHN'
union all
select * from city where countrycode='USA';
7.3.4 ref
非唯一性索引,等值查詢
7.3.5 eq_ref
在多表連接時,連接條件使用了唯一索引(uk pK)
7.3.6 system,const
唯一索引的等值查詢
7.4 extra 額外的信息
filesort ,文件排序
如果出現(xiàn) 說名搜索到的結(jié)果有額外的排序了
結(jié)論:
1.當(dāng)我們看到執(zhí)行計(jì)劃extra位置出現(xiàn)filesort,說明由文件排序出現(xiàn)
2.觀察需要排序(ORDER BY,GROUP BY ,DISTINCT )的條件,有沒有索引
- 根據(jù)子句的執(zhí)行順序,去創(chuàng)建聯(lián)合索引
7.4 聯(lián)合索引
1.) 當(dāng)判斷語句中出現(xiàn)的是 and 時 ,查詢時不考慮聯(lián)合索引中的順序,優(yōu)化器會自動調(diào)整 判斷條件的先后順序
注: 我們在這種情況下建索引時,需要考慮哪個列的唯一值更多,哪個放在索引左邊.
2.) 如果查詢語句中出現(xiàn)了多個判斷句(如where,
group by, 等) ,在建立索引時,要按句子的順序進(jìn)行建立.
注: having 一般情況下是 不走索引的
面試題
題目意思: 我們公司業(yè)務(wù)慢,請你從數(shù)據(jù)庫的角度分析原因
1.mysql出現(xiàn)性能問題,我總結(jié)有兩種情況:
（1）應(yīng)急性的慢：突然夯住
應(yīng)急情況:數(shù)據(jù)庫hang(卡了,資源耗盡)
處理過程:
1.show processlist; 獲取到導(dǎo)致數(shù)據(jù)庫hang的語句
- explain 分析SQL的執(zhí)行計(jì)劃,有沒有走索引,索引的類型情況
- 建索引,改語句
（2）一段時間慢(持續(xù)性的):
(1)記錄慢日志slowlog,分析slowlog
(2)explain 分析SQL的執(zhí)行計(jì)劃,有沒有走索引,索引的類型情況
(3)建索引,改語句
8 索引的應(yīng)用范圍
業(yè)務(wù)
1.產(chǎn)品的功能
2.用戶的行為
"熱"查詢語句 --->較慢--->slowlog
"熱"數(shù)據(jù)
8.1 建立索引的原則
為了使索引的使用效率更高峦阁，在創(chuàng)建索引時宅倒，必須考慮在哪些字段上創(chuàng)建索引和創(chuàng)建什么類型的索引.
1.) 建表時一定要有主鍵,一般是個無關(guān)列, 用于建立聚集索引
2.) 選擇唯一性索引
唯一性索引的值是唯一的慎璧，可以更快速的通過該索引來確定某條記錄偷溺。
例如逮栅，學(xué)生表中學(xué)號是具有唯一性的字段悴势。為該字段建立唯一性索引可以很快的確定某個學(xué)生的信息。
如果使用姓名的話措伐，可能存在同名現(xiàn)象特纤，從而降低查詢速度。
優(yōu)化方案:
(1) 如果非得使用重復(fù)值較多的列作為查詢條件(例如:男女),可以將表邏輯拆分
(2) 可以將此列和其他的查詢類,做聯(lián)和索引
3.) 經(jīng)常需要where 废士、ORDER BY叫潦、GROUP BY,join on等操作的字段時 可以建立聯(lián)合索引
4.) 如果索引字段的值很長蝇完，最好使用值的前綴來索引官硝。
5.) 限制索引的數(shù)目
索引的數(shù)目不是越多越好。
可能會產(chǎn)生的問題:
(1) 每個索引都需要占用磁盤空間短蜕，索引越多氢架，需要的磁盤空間就越大。
(2) 修改表時朋魔，對索引的重構(gòu)和更新很麻煩岖研。越多的索引，會使更新表變得很浪費(fèi)時間。
(3) 優(yōu)化器的負(fù)擔(dān)會很重,有可能會影響到優(yōu)化器的選擇.
percona-toolkit中有個工具,專門分析索引是否有用
6.) 刪除不再使用或者很少使用的索引
7.) 大表加索引,要在業(yè)務(wù)不繁忙期間操作
8.) 盡量少在 要經(jīng)常更新數(shù)據(jù)的列 上建立索引
8.2 查詢時不走索引
1.) 沒有查詢條件孙援，或者查詢條件沒有建立索引
在業(yè)務(wù)數(shù)據(jù)庫中害淤，特別是數(shù)據(jù)量比較大的表。是沒有全表掃描這種需求拓售。
1窥摄、對用戶查看是非常痛苦的。
2础淤、對服務(wù)器來講毀滅性的
優(yōu)化方案:
1.) 每次只顯示一定量的,(limit 10)
2.) 創(chuàng)建索引,或換成有索引的列進(jìn)行搜索
2.) 查詢的結(jié)果集崭放，超過了總數(shù)行數(shù)25%，優(yōu)化器覺得就沒有必要走索引了鸽凶。
優(yōu)化方案:
1.) 每次只顯示一定量的,(limit 10)
2.) 如果業(yè)務(wù)允許,可使用 nosql型數(shù)據(jù)庫
3.) 索引本身失效币砂，統(tǒng)計(jì)數(shù)據(jù)不真實(shí)
索引有自我維護(hù)的能力。
對于表內(nèi)容變化比較頻繁的情況下玻侥，有可能會出現(xiàn)索引失效决摧。
一般是刪除重建
現(xiàn)象:
有一條select語句平常查詢時很快,突然有一天很慢,會是什么原因
select? --->索引失效,，統(tǒng)計(jì)數(shù)據(jù)不真實(shí)
DML ? --->鎖沖突
4.) 查詢條件使用函數(shù)在索引列上使碾，或者對索引列進(jìn)行運(yùn)算蜜徽，運(yùn)算包括(+，-票摇，*拘鞋，/，! 等)
例子：
錯誤的例子：select * from test where id-1=9;
正確的例子：select * from test where id=10;
算術(shù)運(yùn)算
函數(shù)運(yùn)算
子查詢
5.) 10.2.5 隱式轉(zhuǎn)換導(dǎo)致索引失效.這一點(diǎn)應(yīng)當(dāng)引起重視.也是開發(fā)中經(jīng)常會犯的錯誤.
這樣會導(dǎo)致索引失效. 錯誤的例子：
mysql> alter table tab add index inx_tel(telnum);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tab;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| telnum | varchar(20) | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> explain select * from tab where telnum='1333333';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum=1333333;(telnum 創(chuàng)建庫時設(shè)置的為字符,查詢時有用數(shù)字類型搜索的)
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
``
>6.) <> not in 不走索引(輔助索引)
單獨(dú)的>,<,in 有可能走矢门，也有可能不走盆色，和結(jié)果集有關(guān)，盡量結(jié)合業(yè)務(wù)添加limit
or或in 盡量改成union
>7.) like "%_" 百分號在最前面不走
