MySQL優(yōu)化

1_優(yōu)化思路

1_1.應(yīng)急調(diào)優(yōu)思路

針對(duì)突然的業(yè)務(wù)卡頓,無(wú)法進(jìn)行正常的業(yè)務(wù)處理!需要馬上解決的場(chǎng)景!

1).show processlist(查看鏈接session狀態(tài))

2).explain(分析查詢計(jì)劃),show index from table(分析索引)

3).通過(guò)執(zhí)行計(jì)劃判斷,索引問(wèn)題(有沒(méi)有,合不合理)或者語(yǔ)句本身問(wèn)題

4).show status like '%lock%';(查詢鎖狀態(tài))

5).SESSION_ID;(殺掉有問(wèn)題的session)

1_2.常規(guī)調(diào)優(yōu)思路

針對(duì)業(yè)務(wù)周期性的卡頓,例如在每天10-11點(diǎn)業(yè)務(wù)特別慢,但是還能夠使用,過(guò)了這段時(shí)間就好了.

1).查看slow log,分析slow log,分析出查詢慢的語(yǔ)句

2).按照一定優(yōu)先級(jí),進(jìn)行一個(gè)一個(gè)的排查所有慢查詢

3).分析top sql ,進(jìn)行explain調(diào)試,查看語(yǔ)句執(zhí)行時(shí)間.

4).調(diào)整索引或者語(yǔ)句本身

2_查詢優(yōu)化

2_1.MySQL查詢執(zhí)行流程

MySQL中增刪改語(yǔ)句一般不會(huì)出現(xiàn)性能問(wèn)題,所以我們優(yōu)化的重心就是解決查詢的優(yōu)化問(wèn)題,所以我們要先了解查詢語(yǔ)句的執(zhí)行流程,才能對(duì)癥下藥.

1).客戶端將查詢發(fā)送到服務(wù)器;

2).服務(wù)器檢查查詢緩存,如果有,就從緩存中返回結(jié)果,沒(méi)有就進(jìn)行下一步

3).服務(wù)器解析sql語(yǔ)句,進(jìn)行預(yù)處理

4).查詢優(yōu)化器優(yōu)化查詢

5).生成執(zhí)行計(jì)劃,執(zhí)行引擎調(diào)用存儲(chǔ)引擎API執(zhí)行查詢

6).服務(wù)器將結(jié)果發(fā)送回客戶端

批注 2020-08-09 191107.jpg

2_2.查詢優(yōu)化

2_2_1.慢查詢?nèi)罩?/h4>

show variables like 'slow_query%'; //查看慢查詢?nèi)罩鹃_啟情況

show variables like 'long_query_time'; //查看慢查詢?nèi)罩緟?shù)設(shè)置情況

批注 2020-08-09 193601.jpg

開啟慢查詢?nèi)罩?/p>

批注 2020-08-09 193014.jpg

log-slow-queries:指定慢查詢?nèi)罩旧陕窂?/p>

long_query_time:5表示查詢超過(guò)5秒才記錄

慢查詢分析工具 mysqldumpslow

常用參數(shù)

批注 2020-08-09 193930.jpg

示例

按照時(shí)間排的top 5個(gè)SQL語(yǔ)句

mysqldumpslow -s t -t 5 /data/mysqldata/slow-query.log

按照時(shí)間排序且含有'like'的top 5個(gè)SQL語(yǔ)句

mysqldumpslow -s t -t 5 -g "like" /data/mysqldata/slow-query.log

2_2_2.EXPLAIN分析查詢

EXPLAIN可以顯示MySQL如何使用SQL執(zhí)行計(jì)劃.

使用方法,在select語(yǔ)句前面加上Explain就可以了:

EXPLAIN select * from student

批注 2020-08-09 200201.jpg

結(jié)果說(shuō)明

1艺蝴、id：這是SELECT的查詢序列號(hào)

2池户、select_type：select_type就是select的類型，可以有以下幾種：

SIMPLE：簡(jiǎn)單SELECT(不使用UNION或子查詢等)

PRIMARY：最外面的SELECT

UNION：UNION中的第二個(gè)或后面的SELECT語(yǔ)句

DEPENDENT UNION：UNION中的第二個(gè)或后面的SELECT語(yǔ)句泽篮，取決于外面的查詢

UNION RESULT：UNION的結(jié)果。

SUBQUERY：子查詢中的第一個(gè)SELECT

DEPENDENT SUBQUERY：子查詢中的第一個(gè)SELECT现喳，取決于外面的查詢

DERIVED：導(dǎo)出表的SELECT(FROM子句的子查詢)

3怔接、table：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的

4、type：這列最重要凹耙，顯示了連接使用了哪種類別,有無(wú)使用索引姿现，是使用Explain命令分析性能瓶頸的關(guān)鍵項(xiàng)之一。

結(jié)果值從好到壞依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

• system 表只有一行記錄（等于系統(tǒng)表）肖抱，這是const類型的特列备典，平時(shí)不會(huì)出現(xiàn)，這個(gè)也可以忽略不計(jì)
• const 表示通過(guò)索引一次就找到了意述，const用于比較primary key 或者unique索引提佣。因?yàn)橹黄ヅ湟恍袛?shù)據(jù)，所以很快荤崇。如將主鍵置于where列表中拌屏，MySQL就能將該查詢轉(zhuǎn)換為一個(gè)常量。
• eq_ref 唯一性索引掃描术荤，對(duì)于每個(gè)索引鍵倚喂，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描
• ref 非唯一性索引掃描瓣戚，返回匹配某個(gè)單獨(dú)值的所有行务唐，本質(zhì)上也是一種索引訪問(wèn)雳攘，它返回所有匹配某個(gè)單獨(dú)值的行，然而枫笛，它可能會(huì)找到多個(gè)符合條件的行吨灭，所以他應(yīng)該屬于查找和掃描的混合體。
• range 只檢索給定范圍的行刑巧，使用一個(gè)索引來(lái)選擇行喧兄，key列顯示使用了哪個(gè)索引，一般就是在你的where語(yǔ)句中出現(xiàn)between啊楚、< 吠冤、>、in等的查詢恭理，這種范圍掃描索引比全表掃描要好拯辙，因?yàn)樗恍枰_始于索引的某一點(diǎn)，而結(jié)束于另一點(diǎn)颜价，不用掃描全部索引涯保。
• index Full Index Scan，Index與All區(qū)別為index類型只遍歷索引樹周伦。這通常比ALL快夕春，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小。（也就是說(shuō)雖然all和Index都是讀全表专挪，但index是從索引中讀取的及志，而all是從硬盤讀取的）
• all Full Table Scan 將遍歷全表以找到匹配的行

一般來(lái)說(shuō)，得保證查詢至少達(dá)到range級(jí)別寨腔，最好能達(dá)到ref速侈，否則就可能會(huì)出現(xiàn)性能問(wèn)題。

5迫卢、possible_keys：列指出MySQL能使用哪個(gè)索引在該表中找到行

6锌畸、key：顯示MySQL實(shí)際決定使用的鍵（索引）。如果沒(méi)有選擇索引靖避，鍵是NULL

7潭枣、key_len：顯示MySQL決定使用的鍵長(zhǎng)度。如果鍵是NULL幻捏，則長(zhǎng)度為NULL盆犁。使用的索引的長(zhǎng)度。在不損失精確性的情況下篡九，長(zhǎng)度越短越好

8谐岁、ref：顯示使用哪個(gè)列或常數(shù)與key一起從表中選擇行。

9、rows：顯示MySQL認(rèn)為它執(zhí)行查詢時(shí)必須檢查的行數(shù)伊佃。

10窜司、Extra：包含MySQL解決查詢的詳細(xì)信息，也是關(guān)鍵參考項(xiàng)之一航揉。

Distinct
一旦MYSQL找到了與行相聯(lián)合匹配的行塞祈，就不再搜索了

Not exists
MYSQL 優(yōu)化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標(biāo)準(zhǔn)的行帅涂，

就不再搜索了

Range checked for each

Record（index map:#）
沒(méi)有找到理想的索引议薪，因此對(duì)于從前面表中來(lái)的每一 個(gè)行組合，MYSQL檢查使用哪個(gè)索引媳友，并用它來(lái)從表中返回行斯议。這是使用索引的最慢的連接之一

Using filesort
看 到這個(gè)的時(shí)候，查詢就需要優(yōu)化了醇锚。MYSQL需要進(jìn)行額外的步驟來(lái)發(fā)現(xiàn)如何對(duì)返回的行排序哼御。它根據(jù)連接類型以及存儲(chǔ)排序鍵值和匹配條件的全部行的行指針來(lái) 排序全部行

Using index
列數(shù)據(jù)是從僅僅使用了索引中的信息而沒(méi)有讀取實(shí)際的行動(dòng)的表返回的，這發(fā)生在對(duì)表 的全部的請(qǐng)求列都是同一個(gè)索引的部分的時(shí)候

Using temporary
看到這個(gè)的時(shí)候焊唬，查詢需要優(yōu)化了恋昼。這 里，MYSQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)存儲(chǔ)結(jié)果求晶，這通常發(fā)生在對(duì)不同的列集進(jìn)行ORDER BY上焰雕，而不是GROUP BY上

Using where
使用了WHERE從句來(lái)限制哪些行將與下一張表匹配或者是返回給用戶衷笋。如果不想返回表中的全部行芳杏，并且連接類型ALL或index， 這就會(huì)發(fā)生辟宗，或者是查詢有問(wèn)題

3_索引優(yōu)化

3_1.索引類型

1.唯一索引

索引列的值必須唯一爵赵，但允許有空值。如果是組合索引泊脐，則列值的組合必須唯一

主鍵一定是唯一索引

2.普通索引

最基本的索引空幻，它沒(méi)有任何限制，用于加速查詢容客。

3.主鍵索引

是一種特殊的唯一索引秕铛，一個(gè)表只能有一個(gè)主鍵，不允許有空值缩挑。一般是在建表的時(shí)候同時(shí)創(chuàng)建主鍵索引但两。

4.組合索引

指多個(gè)字段上創(chuàng)建的索引，只有在查詢條件中使用了創(chuàng)建索引時(shí)的第一個(gè)字段供置，索引才會(huì)被使用谨湘。使用組合索引時(shí)遵循最左前綴集合。

創(chuàng)建方法:

ａ.　建表的時(shí)候一起創(chuàng)建

CREATE TABLE mytable ( id int(11) , name VARCHAR(32) , INDEX index_mytable_id_name (id,name) );

b. 建表后，直接創(chuàng)建索引

CREATE INDEX index_mytable_id_name ON mytable(id,name);

c. 修改表結(jié)構(gòu)

ALTER TABLE mytable ADD INDEX index_mytable_id_name　(id,name);

5.全文索引

主要用來(lái)查找文本中的關(guān)鍵字紧阔，而不是直接與索引中的值相比較坊罢。

fulltext索引跟其它索引大不相同，它更像是一個(gè)搜索引擎擅耽，而不是簡(jiǎn)單的where語(yǔ)句的參數(shù)匹配活孩。

fulltext索引配合match against操作使用，而不是一般的where語(yǔ)句加like秫筏。

它可以在create table诱鞠，alter table ，create index使用这敬，不過(guò)目前只有char航夺、varchar，text 列上可以創(chuàng)建全文索引崔涂。

創(chuàng)建方法：

ａ. 建表的時(shí)候一起創(chuàng)建

CREATE TABLE article ( id int(11) NOT NULL AUTO_INCREMENT , title char(250) NOT NULL , contents text NULL , create_at int(10) NULL DEFAULT NULL , PRIMARY KEY (id), FULLTEXT (contents) );

b. 建表后阳掐，直接創(chuàng)建索引

CREATE FULLTEXT INDEX index_article_contents ON article(contents);

c. 修改表結(jié)構(gòu)

ALTER TABLE article ADD FULLTEXT INDEX index_article_contents　(contents);

6.聚集索引

InnoDB聚集索引的葉子節(jié)點(diǎn)存儲(chǔ)行記錄，因此冷蚂， InnoDB必須要有缭保，且只有一個(gè)聚集索引：

（1）如果表定義了PK，則PK就是聚集索引蝙茶；

（2）如果表沒(méi)有定義PK艺骂，則第一個(gè)not NULL unique列是聚集索引；

（3）否則隆夯，InnoDB會(huì)創(chuàng)建一個(gè)隱藏的row-id作為聚集索引钳恕；

InnoDB普通索引的葉子節(jié)點(diǎn)存儲(chǔ)主鍵值。

3_2.索引的存儲(chǔ)結(jié)構(gòu)類型

1.B+Tree

MySQL中索引的默認(rèn)使用方案

非葉子節(jié)點(diǎn)不存儲(chǔ)data,只存儲(chǔ)索引

葉子節(jié)點(diǎn)包含所有索引字段

葉子節(jié)點(diǎn)之間用指針鏈接,提高區(qū)間訪問(wèn)的性能

不同的存儲(chǔ)引擎其索引的存儲(chǔ)結(jié)構(gòu)稍有不同蹄衷，比如：

• MyISAM：非聚簇索引忧额，data里存的是指向數(shù)據(jù)地址的指針，所以有三個(gè)文件（.frm .MYI .MYD）
• InnoDB：聚簇索引愧口，data里就放的是數(shù)據(jù)睦番，所以有兩個(gè)文件(.frm .idb)

2.hash

hash索引是基于hash表實(shí)現(xiàn)的，只有查詢條件精確匹配hash索引中的所有列的時(shí)候耍属，才能用到hash索引托嚣。對(duì)于hash索引中的所有列，存儲(chǔ)引擎都會(huì)為每一行計(jì)算一個(gè)hash碼厚骗，hash索引中存儲(chǔ)的就是hash碼和指針示启。因?yàn)閔ash索引本身只需要存儲(chǔ)對(duì)應(yīng)的hash值，所以索引的結(jié)構(gòu)十分緊湊溯捆，這也讓hash索引查找的速度非吵笊Γ快厦瓢。

限制

• 使用哈市索引兩次查找，第一次找到相應(yīng)的行啤月，第二次讀取數(shù)據(jù)煮仇，但是被頻繁訪問(wèn)到的行一般會(huì)緩存在內(nèi)存中，這點(diǎn)對(duì)數(shù)據(jù)庫(kù)性能的影響不大谎仲。
• hash索引不能用于外排序
• 只支持等值查詢浙垫，包括=、IN郑诺、<=>夹姥。不支持范圍查詢
• 當(dāng)出現(xiàn)hash沖突的時(shí)候，存儲(chǔ)引擎必須遍歷整個(gè)鏈表中的所有行指針辙诞，逐行比較辙售，直到找到所有的符合條件的行，若hash沖突很多的話飞涂，一些索引的維護(hù)代價(jià)機(jī)會(huì)很高旦部，所以說(shuō)hash索引不適用于選擇性很差的列上（重復(fù)值很多）。姓名较店、性別士八、身份證（合適）

3_3.索引的使用

雖然索引能夠?yàn)椴檎規(guī)?lái)速度上的提升,但是也會(huì)對(duì)性能有一些損失.

索引會(huì)增加寫操作的成本
太多的索引會(huì)增加查詢優(yōu)化器的選擇時(shí)間
索引會(huì)占用額外的存儲(chǔ)空間

使用索引的場(chǎng)景

主鍵自動(dòng)建立的唯一索引;
經(jīng)常作為查詢條件在where或者order by 語(yǔ)句后面出現(xiàn)的列要建立索引
作為排序的列要建立索引
查詢中與其他表關(guān)聯(lián)的字段,外鍵關(guān)系建議索引
高并發(fā)條件下傾向建立組合索引
用于聚合函數(shù)的列可以建立索引,例如使用count(name),name列就要建立索引
一張表的索引個(gè)數(shù)最好不要大于5個(gè)

同樣，對(duì)于有些列不應(yīng)該創(chuàng)建索引梁呈。一般來(lái)說(shuō)婚度，不應(yīng)該創(chuàng)建索引的的這些列具有下列特點(diǎn)：

第一，對(duì)于那些在查詢中很少使用或者參考的列不應(yīng)該創(chuàng)建索引官卡。這是因 為蝗茁，既然這些列很少使用到，因此有索引或者無(wú)索引味抖，并不能提高查詢速度评甜。相反灰粮，由于增加了索引仔涩，反而降低了系統(tǒng)的維護(hù)速度和增大了空間需求。
第二粘舟，對(duì)于那 些只有很少數(shù)據(jù)值的列也不應(yīng)該增加索引熔脂。這是因?yàn)椋捎谶@些列的取值很少柑肴，例如人事表的性別列霞揉，在查詢的結(jié)果中，結(jié)果集的數(shù)據(jù)行占了表中數(shù)據(jù)行的很大比 例晰骑，即需要在表中搜索的數(shù)據(jù)行的比例很大适秩。增加索引，并不能明顯加快檢索速度。
第三秽荞，對(duì)于那些定義為text, image和bit數(shù)據(jù)類型的列不應(yīng)該增加索引骤公。這是因?yàn)椋@些列的數(shù)據(jù)量要么相當(dāng)大扬跋，要么取值很少阶捆。
第四，當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí)钦听，不應(yīng)該創(chuàng)建索 引洒试。這是因?yàn)椋薷男阅芎蜋z索性能是互相矛盾的朴上。當(dāng)增加索引時(shí)垒棋，會(huì)提高檢索性能，但是會(huì)降低修改性能痪宰。當(dāng)減少索引時(shí)捕犬，會(huì)提高修改性能，降低檢索性能酵镜。因 此碉碉，當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí)，不應(yīng)該創(chuàng)建索引淮韭。

3_4.回表查詢

普通索引無(wú)法直接定位行記錄垢粮，那普通索引的查詢過(guò)程是怎么樣的呢？

通常情況下靠粪，需要掃碼兩遍索引樹蜡吧。

（1）id為PK，聚集索引占键，葉子節(jié)點(diǎn)存儲(chǔ)行記錄昔善；

（2）name為KEY，普通索引畔乙，葉子節(jié)點(diǎn)存儲(chǔ)PK值君仆，即id；

例如: select * from t where name ='lisi';

批注 2020-08-09 212638.jpg

如粉紅色路徑牲距，需要掃碼兩遍索引樹：

（1）先通過(guò)普通索引定位到主鍵值id=5返咱；

（2）在通過(guò)聚集索引定位到行記錄；

這就是所謂的回表查詢牍鞠，先定位主鍵值咖摹，再定位行記錄，它的性能較掃一遍索引樹更低难述。

解決方法:組合索引

組合索引底層B+樹除葉子節(jié)點(diǎn)外,每個(gè)節(jié)點(diǎn)都會(huì)組合索引里的每個(gè)索引

3_5.禁用索引

雖然萤晴，索引有許多優(yōu)點(diǎn)吐句， 但是，為表中的每一個(gè)列都增加索引店读，是非常不明智的蕴侧。這是因?yàn)椋黾铀饕灿性S多不利的一個(gè)方面两入。

第一净宵，創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)據(jù) 量的增加而增加裹纳。
第二择葡，索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外剃氧，每一個(gè)索引還要占一定的物理空間敏储，如果要建立聚簇索引，那么需要的空間就會(huì)更大朋鞍。
第三已添，當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候滥酥，索引也要?jiǎng)討B(tài)的維護(hù)更舞，這樣就降低了數(shù)據(jù)的維護(hù)速度。

因此,對(duì)于使用索引的表,每插入一條數(shù)據(jù),數(shù)據(jù)庫(kù)都會(huì)對(duì)插入的記錄建立索引.如果插入大量數(shù)據(jù),建立索引會(huì)降低數(shù)據(jù)插入的速度.所以,再批量插入之前可以禁用索引,插入完成后,再開啟索引,這樣統(tǒng)一建議索引,就減少了一條一條維護(hù)索引所帶來(lái)的性能損失.

禁用table_name索引的語(yǔ)句:

ALTER TABLE table_name DISABLE KEYS

開啟索引語(yǔ)句:

ALTER TABLE table_name ENABLE KEYS