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