Mysql優(yōu)化問題有哪些

2024-07-24 12:34:19

字體：大中小

供稿：網(wǎng)友

　　開發(fā)過程如何排查SQL？
　　排查思路
　　對(duì)于大部分程序員來說，在開發(fā)過程中排查SQL基本是空白。但隨著行業(yè)的內(nèi)卷，對(duì)一開發(fā)過程越來越重視和專業(yè)，其中一項(xiàng)就是開發(fā)過程中盡可能解決掉SQL問題，避免生產(chǎn)才暴露SQL問題。那么在開發(fā)過程中如何方便的進(jìn)行程序的SQL排查呢？

　　其思路還是使用Mysql的慢日志來實(shí)現(xiàn)：

　　首先在開發(fā)過程中也需要開啟數(shù)據(jù)庫Mysql的慢查詢
　　SET GLOBAL slow_query_log='on';
　　其次設(shè)置慢SQL的最小時(shí)間
　　注意：這里時(shí)間單位是s秒但是有6位小數(shù)因此可以表示到微妙的時(shí)間力度，一般單表SQL執(zhí)行時(shí)間在20ms之內(nèi)為宜，反之理解就是在開發(fā)過程中，如果你執(zhí)行的sql語句超過了20ms則你需要去關(guān)注它。
　　SET GLOBAL long_query_time=0.02;
　　為方便操作可以把慢SQL記錄到表中而不是文件中
　　SET GLOBAL log_output='TABLE';
　　最后通過mysql.slow_log表就可以查詢到記錄的慢SQL
　
　　使用工具
　　在勇哥給大家開發(fā)的軟件中，也提供了圖形化的界面來一鍵幫助大家快速實(shí)現(xiàn)上述功能。

　　生產(chǎn)環(huán)境SQL問題如何排查？
　　排查思路
　　生成SQL問題的排查就相對(duì)復(fù)雜一點(diǎn)點(diǎn)，但是整體的思路還是通過慢SQL來排查，具體思路如下：

　　首先開啟數(shù)據(jù)庫Mysql的慢查詢
　　SET GLOBAL slow_query_log='on';
　　其次設(shè)置慢SQL的最小時(shí)間
　　SET GLOBAL long_query_time=0.02;
　　一般生成時(shí)把慢SQL放到文件
　　SET GLOBAL log_output='FILE';
　　下載慢SQL日志文件到本地
　　最后關(guān)閉數(shù)據(jù)庫Mysql的慢查詢
　　著重注意：生產(chǎn)的慢SQL最好在使用時(shí)，才去開啟，用完后關(guān)閉，避免日志記錄影響到業(yè)務(wù)性能
　　SET GLOBAL slow_query_log='off';
　　SQL怎么調(diào)優(yōu)？
　　SQL調(diào)優(yōu)融合多方面的知識(shí)，總體來說常見從表結(jié)構(gòu)、表索引、兩方面來優(yōu)化。

　　表結(jié)構(gòu)優(yōu)化
　　1、合理的使用字段類及長度
　　舉個(gè)例子來理解：就一個(gè)性別字段，用tinyint(1)存儲(chǔ)占用1字節(jié)，用int(1)存儲(chǔ)占用4個(gè)字節(jié)，如果有100W條記錄，那么用int存儲(chǔ)的表就比tinyint存儲(chǔ)的表文件大小多2.8M左右，因此在讀取int類型存儲(chǔ)的表時(shí)文件大，讀速度相比讀tinyint的慢。這其實(shí)就是為什么說要合理使用字段類型長度的本質(zhì)：就是減少存儲(chǔ)的文件大小，以提供讀性能。

　　當(dāng)然有的朋友就可能說2.8M并不影響大局，因此可以忽略。對(duì)于此想法勇哥要補(bǔ)充一嘴：一個(gè)表假設(shè)有10個(gè)字段，你的系統(tǒng)一共有30個(gè)表，那么再看一下多出的文件大小是多少？（2.8Mx10x30=840M，840M你用迅雷超級(jí)下載也要花好幾秒，這個(gè)時(shí)間在計(jì)算機(jī)里面算是很慢了...）

　　2、合理的使用冗余設(shè)計(jì)
　　2.1、冗余設(shè)計(jì)背景——臨時(shí)表

　　Mysql內(nèi)部存在一種特殊且輕量級(jí)的臨時(shí)表，它是被Mysql自動(dòng)創(chuàng)建和刪除的。主要在SQL的執(zhí)行過程中使用臨時(shí)表來存儲(chǔ)某些操作的中間結(jié)果，該過程由 MySQL 自動(dòng)完成，用戶無法手工干預(yù)，且這種內(nèi)部表對(duì)用戶來說是不可見的。

　　內(nèi)部臨時(shí)表在 SQL 語句的優(yōu)化過程中非常重要，MySQL 中的很多操作都要依賴于內(nèi)部臨時(shí)表來進(jìn)行優(yōu)化操作。但是使用內(nèi)部臨時(shí)表需要?jiǎng)?chuàng)建表以及中間數(shù)據(jù)的存取代價(jià)，所以在寫 SQL 語句的時(shí)候應(yīng)該盡量去避免使用臨時(shí)表。

　　那么場景的那些場景Mysql內(nèi)部會(huì)使用臨時(shí)表呢？

　　多表關(guān)聯(lián)查詢(JOIN)中，order by 或group by使用的列不是第一個(gè)表的列
　　group by 的列不是索引列時(shí)
　　distinct和group by 聯(lián)合使用
　　order by 語句中使用了distinct關(guān)鍵字
　　group by 的列時(shí)索引列，但數(shù)據(jù)量過大時(shí)
　　2.2、如何查看是否使用內(nèi)部臨時(shí)表？

　　通過Explain關(guān)鍵字或者工具的功能按鈕，查看SQL的執(zhí)行過程，在結(jié)果中的Extra列中如果出現(xiàn)Using temporary關(guān)鍵字，則說明你的SQL語句在執(zhí)行時(shí)使用了臨時(shí)表。

　　角色Role表和角色組Role_Group是多對(duì)1的關(guān)系，在關(guān)聯(lián)查詢的時(shí)候，排序使用role_group的id排序則會(huì)使用臨時(shí)表（見下圖1），如果排序使用role的id則不會(huì)使用臨時(shí)表（見圖2）。

　　2.3、如何解決不使用內(nèi)部臨時(shí)表？

　　這個(gè)問題解決有兩個(gè)方案，一是調(diào)整SQL語句避免使用臨時(shí)表，另外一個(gè)方案就是在表中冗余存儲(chǔ)。比如2.2中的圖一例子如果一定要按照role_group的id排序，則可以按照role表中的group_id排序，而這列正是冗余存儲(chǔ)的role_group表中id列值。

　　3、合理的使用分庫分表
　　分庫分表不僅用于大數(shù)量情況下的優(yōu)化，其中垂直分表還可以使用到SQL調(diào)優(yōu)下。（這里我就不去解釋垂直和水平分表了，感興趣的私信我）

　　例如：一個(gè)文章表一般設(shè)計(jì)不會(huì)包括文章內(nèi)容這個(gè)大字段。

　　文章內(nèi)容這個(gè)大字段是單獨(dú)放置到一張表中

　　為什么文章表要采用以上設(shè)計(jì)而不把字段合并到一表中呢？

　　我們先來計(jì)算一道數(shù)學(xué)題，假設(shè)一篇文章總共1M大小，其中文章內(nèi)容，824KB，其余字段200KB，這樣的文章一共有100W條，則：

　　方案一，如果用一個(gè)表存儲(chǔ)，則這個(gè)表大小是100W*1M=100WM
　　方案二，如果用垂直分表存儲(chǔ)，則基本表時(shí)200KBx100W，內(nèi)容表824KBx100W
　　我們在前端有文章列表和文章詳情兩個(gè)頁面，分別要直接從數(shù)據(jù)庫中查詢相關(guān)內(nèi)容，則：

　　方案一，文章列表和文章詳情的查詢都會(huì)從100WM數(shù)據(jù)中查詢
　　方案二，文章列表會(huì)從200KBx100W中查詢，文章詳情會(huì)從824KBx100W中查詢（當(dāng)前也可能還需要從200KBx100W中查詢）
　　說到這里，相信大家心中應(yīng)該有一個(gè)清晰的答案了吧！垂直拆表可以讓不同業(yè)務(wù)場景的查詢的數(shù)據(jù)量不同，常常這個(gè)數(shù)據(jù)量往往小于總表數(shù)據(jù)量，這就比從固定很大小的量中查詢更靈活和高效率。

　　表索引優(yōu)化
　　1、合理的添加索引列
　　大多數(shù)人對(duì)應(yīng)索引的理解層次都在“索引可以加快查詢的速度”，然而這句話勇哥要補(bǔ)充下半句“索引可以加快查詢的速度，也可以減慢數(shù)據(jù)插入或修改的速度”。

　　如果一個(gè)表有5個(gè)索引，那么可以簡單的把一個(gè)索引當(dāng)成一個(gè)表，則這就會(huì)有1張表+6張索引表=相當(dāng)于有6張表，那么這6張表在什么時(shí)候會(huì)操作呢？我們來計(jì)算一下：

　　insert操作，數(shù)據(jù)插入后，需要去對(duì)5張索引表插入索引數(shù)據(jù)
　　delete操作，數(shù)據(jù)刪除后，需要去把5張索引表中的索引刪除
　　update操作
　　如果修改了索引列的數(shù)據(jù)，則先修改數(shù)據(jù)，還需要修改索引表中的索引
　　如果沒有修改索引列的數(shù)據(jù)，則只修改數(shù)據(jù)表
　　select操作
　　如果命中查詢索引，則先查詢索引，再查數(shù)據(jù)表
　　如果沒命中查詢索引，則直接查數(shù)據(jù)表
　　通過以上的計(jì)算，你會(huì)神奇的發(fā)現(xiàn)，索引個(gè)數(shù)越多，對(duì)于insert、delete、update操作是有影響的，而且是負(fù)影響。所以對(duì)于索引竟可能評(píng)估其帶來的影響小于查詢的收益，才去添加，而不是盲目的添加。

　　2、合理的調(diào)配復(fù)合索引列個(gè)數(shù)和順序
　　復(fù)合索引指的是包括有多個(gè)列的索引，它能有效的減少表的索引個(gè)數(shù)，平衡了多個(gè)字段需要多個(gè)索引直接的性能平衡，但是再使用復(fù)合索引的時(shí)候，需要注意索引列個(gè)數(shù)和順序的問題。

　　先說列個(gè)數(shù)的問題，指的是一個(gè)復(fù)合索引中包括的列字段太多影響性能的問題，主要是對(duì)update操作的性能影響，如下紅字：

　　如果修改了索引列的數(shù)據(jù)，則先修改數(shù)據(jù)，還需要修改索引表中的索引，如果索引列個(gè)數(shù)越多則修改該索引的概率越大
　　如果沒有修改索引列的數(shù)據(jù)，則只修改數(shù)據(jù)表
　　再說復(fù)合索引中列順序的問題，是指索引的最左匹配原則，即最左優(yōu)先，在檢索數(shù)據(jù)時(shí)從聯(lián)合索引的最左邊開始匹配，這個(gè)比較容易理解，就不多做闡述。

　　那些情況索引會(huì)失效？
　　索引無法存儲(chǔ)null值，當(dāng)使用is null或is not nulli時(shí)會(huì)全表掃描
　　like查詢以"%"開頭
　　對(duì)于復(fù)合索引，查詢條件中沒有給出索引中第一列的值時(shí)
　　mysql內(nèi)部評(píng)估全表掃描比索引快時(shí)
　　or、!=、<>、in、not in等查詢也可能引起索引失效
　　表設(shè)計(jì)有那些規(guī)范？
　　建表規(guī)約
　　表達(dá)是與否概念的字段，必須使用 is_xxx 的方式命名，數(shù)據(jù)類型為 unsigned tinyint。說明：任何字段如果為非負(fù)數(shù)，則必須是 unsigned。
　　字段允許適當(dāng)冗余，以提高查詢性能，但必須考慮數(shù)據(jù)一致。e.g. 商品類目名稱使用頻率高，字段長度短，名稱基本一成不變，可在相關(guān)聯(lián)的表中冗余存儲(chǔ)類目名稱，避免關(guān)聯(lián)查詢。冗余字段遵循：
　　不是頻繁修改的字段；
　　不是 varchar 超長字段，更不能是 text 字段。
　　索引規(guī)約
　　在 varchar 字段上建立索引時(shí)，必須指定索引長度，沒必要對(duì)全字段建立索引，根據(jù)實(shí)際文本區(qū)分度決定索引長度即可。
　　頁面搜索嚴(yán)禁左模糊或者全模糊，如果需要請通過搜索引擎來解決。說明：索引文件具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引。
　　如果有 order by 的場景，請注意利用索引的有序性。order by 最后的字段是組合索引的一部分，并且放在索引組合順序的最后，避免出現(xiàn) file_sort 的情況，影響查詢性能。
　　正例：where a=? and b=? order by c; 索引: a_b_c。
　　反例：索引中有范圍查找，那么索引有序性無法利用，如 WHERE a>10 ORDER BY b; 索引 a_b 無法排序。
　　利用延遲關(guān)聯(lián)或者子查詢優(yōu)化超多分頁場景。說明：MySQL 并不是跳過 offset 行，而是取 offset+N 行，然后返回放棄前 offset 的行，返回 N 行。當(dāng) offset 特別大的時(shí)候，效率會(huì)非常的低下，要么控制返回的總頁數(shù)，要么對(duì)超過閾值的頁數(shù)進(jìn)行 SQL 改寫。
　　建組合索引的時(shí)候，區(qū)分度最高的在最左邊。
　　SQL 性能優(yōu)化的目標(biāo)，至少要達(dá)到 range 級(jí)別，要求是 ref 級(jí)別，最好是 consts。
　　SQL 語句
　　不要使用 count(列名) 或 count(常量) 來替代 count()，count() 是 SQL92 定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語句，跟數(shù)據(jù)庫無關(guān)，跟 NULL 和非 NULL 無關(guān)。說明：count(*) 會(huì)統(tǒng)計(jì)值為 NULL 的行，而 count(列名) 不會(huì)統(tǒng)計(jì)此列為 NULL 值的行。
　　count(distinct column) 計(jì)算該列除 NULL 外的不重復(fù)行數(shù)。注意，count(distinct column1,column2) 如果其中一列全為 NULL，那么即使另一列用不同的值，也返回為 0。
　　當(dāng)某一列的值全為 NULL 時(shí)，count(column) 的返回結(jié)果為 0，但 sum(column) 的返回結(jié)果為 NULL，因此使用 sum() 時(shí)需注意 NPE 問題。可以使用如下方式來避免 sum 的 NPE 問題。
　　SELECT IF(ISNULL(SUM(g), 0, SUM(g))) FROM table;
　　使用 ISNULL() 來判斷是否為 NULL 值。說明：NULL 與任何值的直接比較都為 NULL。
　　不得使用外鍵與級(jí)聯(lián)，一切外鍵概念必須在應(yīng)用層解決。說明：以學(xué)生和成績的關(guān)系為例，學(xué)生表的 student_id 是主鍵，成績表的 student_id 則為外鍵。如果更新學(xué)生表中的 student_id，同時(shí)觸發(fā)成績表中的 student_id 更新，即為級(jí)聯(lián)更新。外鍵與級(jí)聯(lián)更新適用于單機(jī)低并發(fā)，不適合分布式、高并發(fā)集群；級(jí)聯(lián)更新是強(qiáng)阻塞，存在數(shù)據(jù)庫更新風(fēng)暴的風(fēng)險(xiǎn)；外鍵影響數(shù)據(jù)庫的插入速度。
　　禁止使用存儲(chǔ)過程。存儲(chǔ)過程難以調(diào)試和擴(kuò)展，更沒有移植性。
　　in 操作能避免則避免。若實(shí)在避免不了，需要仔細(xì)評(píng)估 in 后面的集合元素?cái)?shù)量，控制在 1000 個(gè)之內(nèi)。
　　ORM 映射
　　POJO 類的布爾屬性不能加 is，而數(shù)據(jù)庫字段必須加 is_，要求在 resultMap 中進(jìn)行字段與屬性的映射。
　　sql.xml 配置參數(shù)使用：#{}, #param#，不要使用 ${}，此種方式容易出現(xiàn) SQL 注入。
　　@Transactional 事務(wù)不要濫用。事務(wù)會(huì)影響數(shù)據(jù)庫的 QPS。另外，使用事務(wù)的地方需要考慮各方面的回滾方案，包括緩存回滾、搜索引擎回滾、消息補(bǔ)償、統(tǒng)計(jì)修正等。
　　關(guān)于“Mysql優(yōu)化問題有哪些”這篇文章的內(nèi)容就介紹到這里，感謝各位的閱讀！

（編輯：武林網(wǎng)）

上一篇：mysql的日志文件是什么

下一篇：mysql索引有哪些優(yōu)點(diǎn)