國(guó)家市場(chǎng)監(jiān)督管理總局60號(hào)令百度seo排名原理
?SQL性能分析
SQL執(zhí)行頻率
MySQL 客戶端連接成功后，通過(guò) show [session|global] status 命令可以提供服務(wù)器狀態(tài)信息。通過(guò)如下指令，可以查看當(dāng)前數(shù)據(jù)庫(kù)的INSERT、UPDATE、DELETE、SELECT的訪問(wèn)頻次，通過(guò)sql語(yǔ)句的訪問(wèn)頻次，我們可以判斷到當(dāng)前數(shù)據(jù)庫(kù)到底是以查詢?yōu)橹?#xff0c;還是以增刪改為主，從而為數(shù)據(jù)庫(kù)優(yōu)化提供參考依據(jù)。 如果是以增刪改為主，我們可以考慮不對(duì)其進(jìn)行索引的優(yōu)化。 如果是以查詢?yōu)橹?#xff0c;那么就要考慮對(duì)數(shù)據(jù)庫(kù)的索引進(jìn)行優(yōu)化了。
慢查詢?nèi)罩?
如果當(dāng)前數(shù)據(jù)庫(kù)是以查詢?yōu)橹?/strong>，我們可以通過(guò)慢查詢?nèi)罩緛?lái)查看耗時(shí)較長(zhǎng)的查詢，慢查詢?nèi)罩居涗浟怂袌?zhí)行時(shí)間超過(guò)指定參數(shù)（long_query_time，單位：秒，默認(rèn)10秒）的所有 SQL語(yǔ)句的日志。MySQL的慢查詢?nèi)罩灸J(rèn)沒有開啟，我們可以查看一下系統(tǒng)變量 slow_query_log。
?慢查詢?nèi)罩灸J(rèn)是關(guān)閉的
?開啟慢查詢?nèi)罩?#xff0c;需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息，同時(shí)重啟mysql服務(wù)，使配置文件生效
# 開啟MySQL慢日志查詢開關(guān)
slow_query_log=1
# 設(shè)置慢日志的時(shí)間為2秒，SQL語(yǔ)句執(zhí)行時(shí)間超過(guò)2秒，就會(huì)視為慢查詢，記錄慢查詢?nèi)罩?long_query_time=2
?查詢600萬(wàn)數(shù)據(jù)的表
?毫無(wú)疑問(wèn)出現(xiàn)在慢查詢?nèi)罩旧?img referrerpolicy="no-referrer" alt="a25b6db262ed4c7fa437a364afaeea05.png" src="https://img-blog.csdnimg.cn/a25b6db262ed4c7fa437a364afaeea05.png" />
profile詳情
show profiles 能夠在做SQL優(yōu)化時(shí)幫助我們了解時(shí)間都耗費(fèi)到哪里去了。通過(guò)have_profiling 參數(shù)，能夠看到當(dāng)前MySQL是否支持profile操作：
#查詢是否有profile參數(shù)
select @@have_profiling
#查詢profile功能是否開啟 0表示關(guān)閉 1表示開啟
select @@profiling
?可以看到，當(dāng)前MySQL是支持 profile操作的，但是開關(guān)是關(guān)閉的?？梢酝ㄟ^(guò)set語(yǔ)句在 session/global級(jí)別開啟profiling：
SET profiling = 1;?執(zhí)行一系列的業(yè)務(wù)SQL的操作，然后通過(guò)如下指令查看指令的執(zhí)行耗時(shí)：
# 查看每一條SQL的耗時(shí)基本情況
show profiles;
# 查看指定query_id的SQL語(yǔ)句各個(gè)階段的耗時(shí)情況
show profile for query query_id;
# 查看指定query_id的SQL語(yǔ)句CPU的使用情況
show profile cpu for query query_id;
explain
EXPLAIN 或者 DESC命令獲取 MySQL 如何執(zhí)行 SELECT 語(yǔ)句的信息，包括在 SELECT 語(yǔ)句執(zhí)行 過(guò)程中表如何連接和連接的順序。在select語(yǔ)句開頭加上explain關(guān)鍵詞即可，通過(guò)explain各字段的值，我們可以分析出此時(shí)的sql語(yǔ)句走不走索引，有沒有回表查詢等情況，方便我們進(jìn)行sql優(yōu)化
?Explain 執(zhí)行計(jì)劃中各個(gè)字段的含義:
| 字段 | 含義 |
|---|---|
| id | select查詢的序列號(hào)，表示查詢中執(zhí)行select子句或者是操作表的順序 (id相同，執(zhí)行順序從上到下；id不同，值越大，越先執(zhí)行)。 |
| select_type | 表示 SELECT 的類型，常見的取值有 SIMPLE（簡(jiǎn)單表，即不使用表連接 或者子查詢）、PRIMARY（主查詢，即外層的查詢）、 UNION（UNION 中的第二個(gè)或者后面的查詢語(yǔ)句）、 SUBQUERY（SELECT/WHERE之后包含了子查詢）等 |
| type | 表示連接類型，性能由好到差的連接類型為NULL、system、const、eq_ref、ref、range、 index、all 。 |
| possible_key | 顯示可能應(yīng)用在這次查詢的索引，一個(gè)或多個(gè)。 |
| key | 實(shí)際使用的索引，如果為NULL，則沒有使用索引。 |
| key_len | 表示索引中使用的字節(jié)數(shù)， 該值為索引字段最大可能長(zhǎng)度，并非實(shí)際使用長(zhǎng)度，在不損失精確性的前提下， 長(zhǎng)度越短越好。 |
| rows | MySQL認(rèn)為必須要執(zhí)行查詢的行數(shù)，在innodb引擎的表中，是一個(gè)估計(jì)值， 可能并不總是準(zhǔn)確的。 |
| filtered | 表示返回結(jié)果的行數(shù)占需讀取行數(shù)的百分比，filtered 的值越大越好。 |
?索引使用
效率驗(yàn)證
準(zhǔn)備一張百萬(wàn)數(shù)據(jù)的表，根據(jù)id查詢數(shù)據(jù)，我們可以發(fā)現(xiàn)此時(shí)耗時(shí)僅為0.01秒，這是因?yàn)閕d默認(rèn)就是主鍵索引，這是已經(jīng)有索引的查詢情況
?根據(jù)name字段查詢時(shí)，此時(shí)沒有建立name字段的索引，可以看到耗時(shí)達(dá)到6秒之久
建立索引之后，根據(jù)name查詢，耗時(shí)僅有0.01秒，可見索引能大大提升查詢效率
最左前綴法則
如果索引了多列（聯(lián)合索引），要遵守最左前綴法則。最左前綴法則指的是查詢從索引的最左列開始， 并且不跳過(guò)索引中的列。如果跳躍某一列，索引將會(huì)部分失效(后面的字段索引失效)。
以tb_user表為例，此時(shí)有idx_user_pro_age_sta這個(gè)聯(lián)合索引，這個(gè)聯(lián)合索引涉及到三個(gè)字段，順序分別為：profession， age，status。
?對(duì)于最左前綴法則指的是，查詢時(shí)，最左變的列，也就是profession必須存在，否則索引全部失效。 而且中間不能跳過(guò)某一列，否則該列后面的字段索引將失效。
explain select * from tb_user where profession = '軟件工程' and age = 31 and status
= '0'?聯(lián)合索引的三個(gè)字段都在聯(lián)合索引必然生效，只要where條件后面的三個(gè)字段都在索引就會(huì)生效，與字段的先后順序無(wú)關(guān)。
?我們嘗試profession后面的兩個(gè)字段逐步減少，會(huì)發(fā)現(xiàn)聯(lián)合索引仍然生效，正說(shuō)明只要索引的最左邊的字段在where子句中索引就會(huì)生效。從圖中我們也可以推測(cè)出profession字段索引長(zhǎng)度為47、age 字段索引長(zhǎng)度為2、status字段索引長(zhǎng)度為5。
?但此時(shí)我們將查詢條件中的profession去掉，此時(shí)聯(lián)合索引失效。
?如果最左的列存在，但是跳過(guò)了中間的列，那么只會(huì)中間列之后的索引都不會(huì)生效，雖然走了聯(lián)合索引，但長(zhǎng)度只有47，為profession字段索引長(zhǎng)度。
?范圍查詢
聯(lián)合索引中，出現(xiàn)范圍查詢(>,<)，范圍查詢右側(cè)的列索引失效。從索引長(zhǎng)度可以看到status字段的索引并沒有生效
?但如果我們將范圍查詢加上等號(hào)即≥和≤這種形式，聯(lián)合索引就生效，在業(yè)務(wù)允許的情況下，盡可能的使用類似于 >= 或<=，而避免使用 > 或 <，來(lái)避免索引失效
?索引失效情況
?索引列函數(shù)運(yùn)算
當(dāng)根據(jù)phone字段進(jìn)行等值匹配查詢時(shí), 索引生效。
?當(dāng)進(jìn)行函數(shù)運(yùn)算時(shí)，索引就失效，走的是全表掃描
如果進(jìn)行的是數(shù)值運(yùn)算，索引仍然生效
?字符串不加引號(hào)
如果不給phone字段添加引號(hào)，造成索引類型不匹配，索引也會(huì)失效
?模糊查詢
如果僅僅是尾部模糊匹配，索引不會(huì)失效。如果是頭部模糊匹配，索引失效。
頭部模糊查詢，走的是全表掃描
?如果是尾部模糊，則走聯(lián)合索引，索引生效，如果前后模糊查詢，由于前面模糊查詢已使索引失效，所以也是索引失效
?or連接條件
用or分割開的條件， 如果or前的條件中的列有索引，而后面的列中沒有索引，那么涉及的索引都不會(huì) 被用到，因?yàn)閛r前面的用了索引，or后面的列沒有索引還是要走全表掃描，mysql優(yōu)化器就會(huì)判斷直接全表掃描，避免浪費(fèi)一次查找索引樹的時(shí)間
數(shù)據(jù)分布影響
相同的SQL語(yǔ)句，只是傳入的字段值不同，最終的執(zhí)行計(jì)劃也完全不一樣，這是因?yàn)镸ySQL在查詢時(shí)，會(huì)評(píng)估使用索引的效率與走全表掃描的效率，如果走全表掃描更快，則放棄索引，走全表掃描。 因?yàn)樗饕怯脕?lái)索引少量數(shù)據(jù)的，如果通過(guò)索引查詢返回大批量的數(shù)據(jù)，則還不 如走全表掃描來(lái)的快，此時(shí)索引就會(huì)失效。即數(shù)據(jù)分布情況也會(huì)影響索引是否生效
?SQL提示
sql提示就是在執(zhí)行查詢時(shí)我們自己指定要使用的索引
?此時(shí)我們有?profession這個(gè)字段的單列索引
?我們可以看到，possible_keys中 idx_user_pro_age_sta,idx_user_pro 這兩個(gè) 索引都可能用到，最終MySQL選擇了idx_user_pro_age_sta索引。這是MySQL自動(dòng)選擇的結(jié)果。我們想看到的是走單列索引，畢竟我的mysql我做主，這時(shí)候就要用到sql提示啦！
我們可以在select語(yǔ)句時(shí)加上use index(索引名)來(lái)建議mysql使用我們指定的索引（僅僅是建議，mysql內(nèi)部還會(huì)再次進(jìn)行評(píng)估）
?上面的use index 僅僅是建議，要是mysql不聽怎么辦？這時(shí)候就需要force index來(lái)強(qiáng)制mysql執(zhí)行我們指定的索引！即使效率可能下降，但爺樂(lè)意，千金難買爺樂(lè)意，下圖我們可以看到正常情況下肯定不會(huì)走sta索引的，畢竟我們where子句是根據(jù)profession來(lái)查的，但通過(guò)我們的強(qiáng)制，也能讓mysql執(zhí)行這一索引
覆蓋索引
盡量使用覆蓋索引，減少select *，?覆蓋索引是指查詢使用了索引，并且需要返回的列，在該索引中已經(jīng)全部能夠找到。使用覆蓋索引能減少能大大提高查詢，其原因就是需要返回的列在索引列已經(jīng)全部找到，不需要回表查詢了，這也是mysql優(yōu)先使用聯(lián)合索引的原因
前綴索引
介紹
當(dāng)字段類型為字符串（varchar，text，longtext等）時(shí)，有時(shí)候需要索引很長(zhǎng)的字符串，這會(huì)讓 索引變得很大，查詢時(shí)，浪費(fèi)大量的磁盤IO， 影響查詢效率。此時(shí)可以只將字符串的一部分前綴，建 立索引，這樣可以大大節(jié)約索引空間，從而提高索引效率。
?語(yǔ)法
create index idx_xxxx on table_name(column(n)) ;為tb_user表的email字段，建立長(zhǎng)度為5的前綴索引。
?前綴長(zhǎng)度
可以根據(jù)索引的選擇性來(lái)決定，而選擇性是指不重復(fù)的索引值（基數(shù)）和數(shù)據(jù)表的記錄總數(shù)的比值， 索引選擇性越高則查詢效率越高， 唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的。即要保證取得的前綴盡量唯一不重復(fù)。
?查詢流程
前綴索引相當(dāng)于二級(jí)索引，但他匹配到時(shí)，必須回表查詢，確認(rèn)根據(jù)前綴索引匹配到行數(shù)據(jù)的email值跟sql語(yǔ)句的email值是否一樣，同時(shí)要遍歷到鏈表的下一個(gè)元素，看是否與前綴索引匹配，如果是就要重復(fù)剛剛的流程然后返回?cái)?shù)據(jù)，如果不是，直接返回?cái)?shù)據(jù)即可
?單列索引與聯(lián)合索引
?前文提到的覆蓋索引，mysql會(huì)優(yōu)先使用聯(lián)合索引，以此來(lái)減少回表查詢，提高查詢效率
單列索引：即一個(gè)索引只包含單個(gè)列。
聯(lián)合索引：即一個(gè)索引包含了多個(gè)列。
?索引設(shè)計(jì)原則
1).針對(duì)于數(shù)據(jù)量較大，且查詢比較頻繁的表建立索引。
2). 針對(duì)于常作為查詢條件（where）、排序（order by）、分組（group by）操作的字段建立索引。
3). 盡量選擇區(qū)分度高的列作為索引，盡量建立唯一索引，區(qū)分度越高，使用索引的效率越高。
4). 如果是字符串類型的字段，字段的長(zhǎng)度較長(zhǎng)，可以針對(duì)于字段的特點(diǎn)，建立前綴索引。 5). 盡量使用聯(lián)合索引，減少單列索引，查詢時(shí)，聯(lián)合索引很多時(shí)候可以覆蓋索引，節(jié)省存儲(chǔ)空間， 避免回表，提高查詢效率。
6). 要控制索引的數(shù)量，索引并不是多多益善，索引越多，維護(hù)索引結(jié)構(gòu)的代價(jià)也就越大，會(huì)影響增刪改的效率，同時(shí)索引也會(huì)占用硬盤空間。?
7). 如果索引列不能存儲(chǔ)NULL值，請(qǐng)?jiān)趧?chuàng)建表時(shí)使用NOT NULL約束它。當(dāng)優(yōu)化器知道每列是否包含 NULL值時(shí)，它可以更好地確定哪個(gè)索引最有效地用于查詢。