索引分類(lèi)
分類(lèi)?????????????? 含義???????????????????????????????????????????????????? 特點(diǎn)???????????????????????????????????????? 關(guān)鍵字
主鍵索引??? 針對(duì)表中主鍵創(chuàng)建的索引??????????????????? 默認(rèn)自動(dòng)創(chuàng)建，只能有一個(gè)????????? PRIMARY
唯一索引??? 避免同一個(gè)表中某數(shù)據(jù)列中的值重復(fù)? 可以有多個(gè)???????????????????????????????? ? UNIQUE
常規(guī)索引??? 快速定位特定數(shù)據(jù)??????? ???????????????????? ? 可以有多個(gè)????????
全文索引??? 全文索引查找的是文本中的關(guān)鍵詞 ? ?? 可以有多個(gè) ????????????????????????????????? FULLTEXT
在InnoDB存儲(chǔ)引擎中，根據(jù)索引的存儲(chǔ)形式，又可分為以下兩種：
分類(lèi)??????????????? 含義??????????????? 特點(diǎn)????????????
聚焦索引??? 將數(shù)據(jù)存儲(chǔ)與索引放到了一塊，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)保存了行數(shù)據(jù)??? 有且僅能有一個(gè)
二級(jí)索引? ? 將數(shù)據(jù)與索引分開(kāi)存儲(chǔ)，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)關(guān)聯(lián)的是對(duì)應(yīng)的主鍵??? 可以存在多個(gè)
聚焦索引選取規(guī)則
1、如果存在主鍵，主鍵索引就是聚焦索引
2、如果不存在主鍵，將使用第一個(gè)唯一索引作為聚焦索引
3、如果表沒(méi)有主鍵，或沒(méi)有合適的唯一索引，則InnoDB會(huì)自動(dòng)生成一個(gè)rowid作為隱藏的聚焦索引
select * form user where name=“Arm”；
利用二級(jí)索引找到對(duì)應(yīng)聚焦索引（回表查詢(xún)）然后找到對(duì)應(yīng)數(shù)據(jù)
思考：
1、以下SQL語(yǔ)句，哪個(gè)執(zhí)行效率高？為什么？
select * from user where id =10；
select * from user where name =‘a(chǎn)rm’；
備注：id為主鍵，name字段創(chuàng)建的有索引
答：第一個(gè)語(yǔ)句執(zhí)行效率高，因?yàn)閳?zhí)行第二個(gè)語(yǔ)句name字段是二級(jí)索引，還需要回表檢索到聚焦索引，才能找到對(duì)應(yīng)的數(shù)據(jù)。
2、InnoDB主鍵索引的B+Tree高度有多高？
假設(shè)：
一行數(shù)據(jù)的大小為1KB，一頁(yè)（16K）中可以存儲(chǔ)16行這樣的數(shù)據(jù)。InnoDB的指針占用6個(gè)字節(jié)的空間，主鍵即使是bigint，占用的字節(jié)數(shù)為8。
樹(shù)的高度為2可以存儲(chǔ)的：
n*8+(n+1)*6=1024*16
主鍵：n = 1170
指針：n+1=1171
存儲(chǔ)量（KB）1171*16=18736KB
樹(shù)的高度為3可以存儲(chǔ)：
1171*1171*16=21939856KB=21426MB=21GB
索引語(yǔ)法
創(chuàng)建索引
語(yǔ)法：
CREATE [UNIQUE|FULLTEXT]INDEX 索引名 ON 表名(被索引列名...);案例
1、name字段為姓名字段，該字段的值可能會(huì)重復(fù)，為該字段創(chuàng)建索引
CREATE INDEX ON user1(name1);2、phone手機(jī)號(hào)字段的值，是非空，且唯一，為該字段創(chuàng)建唯一索引
CREATE UNIQUE INDEX index_to_phone ON user1(phone);3、為profession、age、status創(chuàng)建聯(lián)合索引
CREATE INDEX idx_user_pro_age_sta ON user1(profession,age,status);4、為Email建立合適的索引來(lái)提升查詢(xún)的效率
CREATE INDEX index_user_email ON user1(email);查看索引
查看當(dāng)前表所具有的索引：
show index from 表名;刪除索引
刪除索引：
DROP INDEX 索引名 ON 表名;SQL性能分析
1、SQL執(zhí)行頻率
MYSQL客戶(hù)端連接成功后，通過(guò)show[session|global]status命令可以提供服務(wù)器狀態(tài)信息。
通過(guò)如下指令可以查看當(dāng)前數(shù)據(jù)庫(kù)的insert，update，delete，select的訪(fǎng)問(wèn)頻次：
SHOW GLOBAL STATUS LIKE 'Com_______';2、慢查詢(xún)?nèi)罩?/h4>
慢查詢(xún)?nèi)罩居涗浰袌?zhí)行時(shí)間超過(guò)指定參數(shù)（long_query_time，單位：秒，默認(rèn)10秒）的所有SQL語(yǔ)句的日志。MYSQL的慢查詢(xún)?nèi)罩灸J(rèn)并沒(méi)有開(kāi)啟，需要在MYSQL的配置文件：
C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
找到指定文件內(nèi)，修改圖下信息：
配置完成之后，通過(guò)以下指令重啟MYSQL服務(wù)器進(jìn)行測(cè)試，查看慢日志文件中記錄的信息：C:\ProgramData\MySQL\MySQL Server 8.0\Data\**-slow.log
3、profiles詳情
show profiles能夠在做SQL優(yōu)化時(shí)幫助我們了解時(shí)間都耗費(fèi)到哪里去了。通過(guò)have_profiling參數(shù)，能夠看到是否支持profiles，yes是支持。
select @@have_profiling;默認(rèn)profiling默認(rèn)是關(guān)閉的，通過(guò)set語(yǔ)句在session/global級(jí)別開(kāi)啟profiling
查詢(xún)是否開(kāi)啟：select
0關(guān)閉1開(kāi)啟
設(shè)置開(kāi)啟
set @@profiling=1;然后執(zhí)行一系列sql語(yǔ)句之后
通過(guò)
show profiles;查看各個(gè)sql語(yǔ)句的耗時(shí)。
查詢(xún)SQL語(yǔ)句在各個(gè)階段執(zhí)行耗時(shí)情況
show profiles for query query_id;查詢(xún)指定query_id的SQL語(yǔ)句CPU的使用情況
show profiles cpu for query query_id;4、explain執(zhí)行計(jì)劃
explain執(zhí)行計(jì)劃各字段的含義：
explain sql語(yǔ)句
索引的使用
驗(yàn)證索引效率
在未創(chuàng)建索引之前，執(zhí)行SQL語(yǔ)句查看SQL的耗時(shí)
select * from tb_sku where sn ='10000002258';耗時(shí)20.03s
針對(duì)字段創(chuàng)建索引
create index idx_sku_sn on tb_sku(sn);然后再次執(zhí)行相同的SQL語(yǔ)句，再次查看SQL的耗時(shí)情況。
select * from tb_sku where sn ='10000002258';耗時(shí)0.00s
使用原則
索引失效
最左前綴法則
如果索引了多列（聯(lián)合索引），要遵守最左前綴法則指的是查詢(xún)從索引的最左列開(kāi)始，并且不跳過(guò)索引中的列。如果跳過(guò)某一列，索引將部分失效（后面的字段索引失效）。
create full_text index idx_pro_age_sta on tb_user(profession,age,status);
# 走索引
explain select * from tb_user where profession="軟件工程" and age=31 and status="0";
# 走索引
explain select * from tb_user where profession="軟件工程" and age=31;
# 走索引
explain select * from tb_user where profession="軟件工程";
# 走索引 status失效
explain select * from tb_user where profession="軟件工程" and status="0";
# 不走索引
explain select * from tb_user where age=31 and status="0";
# 不走索引
explain select * from tb_user where status="0";范圍查詢(xún)
聯(lián)合索引中，出現(xiàn)范圍查詢(xún)（>,<），范圍查詢(xún)右側(cè)的列索引失效（規(guī)避辦法加上>=）
# age后索引失效
explain select * from tb_user where profession="軟甲工程" and age>60 and status='0';
# 規(guī)避辦法
explain select * from tb_user where profession="軟甲工程" and age>=60 and status='0';索引列運(yùn)算操作
不要在索引列進(jìn)行運(yùn)算操作，索引將失效
explain select * from tb_user where substring(phone,10,2)='15';字符串類(lèi)型不加引號(hào)
字符串類(lèi)型的字段使用時(shí)，不加引號(hào)，索引將失效
explain select * from tb_user where profession="軟甲工程" and age>=60 and status='0';
# status索引失效
explain select * from tb_user where profession="軟甲工程" and age>=60 and status=0;模糊查詢(xún)
如果僅僅是尾部模糊匹配，索引不會(huì)失效，如果是頭部模糊匹配，索引失效。
# 走索引
explain select * from tb_user where profession="軟甲工程";
# 走索引
explain select * from tb_user where profession like "軟甲%" ;
# 不走索引
explain select * from tb_user where profession like "%工程";or連接的條件
用or分隔開(kāi)的條件，如果or前的條件中的列有索引，而后面的列中沒(méi)有索引，那么涉及的索引豆瓣不會(huì)被用到。
解決辦法：針對(duì)or條件后的字段建立索引
數(shù)據(jù)分布影響
如果MYSQL評(píng)估使用索引比全表更慢，則不使用索引
# 數(shù)據(jù)總量 190000000-1900000020
# 走全表掃描
select * from tb_user where phone>='190000005'
# 走索引
select * from tb_user where phone>='190000015'
SQL提示
SQL提示，是優(yōu)化數(shù)據(jù)庫(kù)的一個(gè)重要手段，簡(jiǎn)單來(lái)說(shuō)，就是在SQL語(yǔ)句中加入一些人為的提示來(lái)達(dá)到優(yōu)化操作的目的。
use index（給個(gè)建議）
explain select * from tb_user use index(idx_user_pro) where profession="軟件工程";
ignore index（忽略）
explain select * from tb_user ignore index(idx_user_pro) where profession="軟件工程";
force index（強(qiáng)制）
explain select * from tb_user force index(idx_user_pro) where profession="軟件工程";
覆蓋索引
覆蓋索引：指的是查詢(xún)字段在索引中全部能找到。
盡量使用覆蓋索引（查詢(xún)使用了索引，并且要返回的列能在該索引中全部找到），減少select*（要回表查詢(xún)，使性能下降）
注意：
using index condition：查找使用了索引，但需要回表查詢(xún)數(shù)據(jù)
usingwhere；using index：查找使用了索引，但需要的數(shù)據(jù)在索引列能找到，不需要回表查詢(xún)
思考題：
建立一個(gè)包含 username和password的聯(lián)合索引
前綴索引
當(dāng)字段類(lèi)型為字符串（varchar、text等）時(shí)，有時(shí)候需要索引很長(zhǎng)的字符串，這會(huì)讓索引變得很大，查詢(xún)時(shí)浪費(fèi)很大的磁盤(pán)IO空間，影響查詢(xún)效率。此時(shí)可以只將字符串的一部分前綴，建立索引，這樣可以大大節(jié)約索引空間，從而提高索引效率。
創(chuàng)建語(yǔ)法
create index 索引名 on 表名（列名（選取長(zhǎng)度n））;前綴長(zhǎng)度計(jì)算
根據(jù)索引的選擇性來(lái)決定，選擇性指的是不重復(fù)的索引值和數(shù)據(jù)表中的記錄總數(shù)的比值，索引選擇性越高查詢(xún)效率越高，唯一索引的選擇性是1
select count(distinct email)/count(*)/ from tb_user;
select count(distinct substring(email,1,5))/count(*) from tb_user;單列索引和聯(lián)合索引
單列索引：一個(gè)索引包含一個(gè)列
聯(lián)合索引：一個(gè)索引包含多個(gè)列
在業(yè)務(wù)場(chǎng)景中，如果存在多個(gè)查詢(xún)條件，考慮針對(duì)查詢(xún)字段建立索引時(shí)，建立聯(lián)合索引，而非單列索引。
單列索引可能會(huì)回表查詢(xún)
聯(lián)合索引情況
設(shè)計(jì)原則
1、針對(duì)數(shù)據(jù)量較大，且查詢(xún)較頻繁的表建立索引。
2、針對(duì)于常作為查詢(xún)條件、排序條件、分組操作的字段建立索引。
3、盡量選擇分區(qū)度高的列作為索引，盡量建立唯一索引，區(qū)分度越高、使用索引的效率越高。
4、如果字符串類(lèi)型是字段，字段的長(zhǎng)度較長(zhǎng)，可以根據(jù)字段特點(diǎn)建立前綴索引。
5、盡量使用聯(lián)合索引、減少使用單列索引，查詢(xún)時(shí)，聯(lián)合索引很多時(shí)候可以覆蓋索引，節(jié)省存儲(chǔ)空間，避免回表，提高查詢(xún)效率。
6、要控制索引的數(shù)量，索引并不是多多益善，索引越多，維護(hù)索引結(jié)構(gòu)的代價(jià)也就越大，會(huì)影響增刪改的效率。文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-817419.html
7、如果索引列不能存儲(chǔ)NULL值，請(qǐng)?jiān)趧?chuàng)建表時(shí)使用NOT NULL約束它，當(dāng)優(yōu)化器知道每列是否包含NULL值時(shí)，它可以更好的確定哪個(gè)索引最有效的用于查詢(xún)。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-817419.html
到了這里，關(guān)于MYSQL之索引語(yǔ)法與使用的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
