本篇文章給大家?guī)砹岁P(guān)于mysql的相關(guān)知識(shí)，其中主要介紹了關(guān)于SQL優(yōu)化的相關(guān)問題，包括了在開發(fā)過程中是怎么排查SQL語句、怎么排查生產(chǎn)環(huán)境SQL問題等等內(nèi)容，下面一起來看一下，希望對(duì)大家有幫助。

推薦學(xué)習(xí)：mysql視頻教程

開發(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í)表。

那么場(chǎng)景的那些場(chǎng)景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

我們?cè)谇岸擞形恼铝斜砗臀恼略斍閮蓚€(gè)頁面，分別要直接從數(shù)據(jù)庫中查詢相關(guān)內(nèi)容，則：

• 方案一，文章列表和文章詳情的查詢都會(huì)從100WM數(shù)據(jù)中查詢

• 方案二，文章列表會(huì)從200KBx100W中查詢，文章詳情會(huì)從824KBx100W中查詢（當(dāng)前也可能還需要從200KBx100W中查詢）

說到這里，相信大家心中應(yīng)該有一個(gè)清晰的答案了吧！垂直拆表可以讓不同業(yè)務(wù)場(chǎng)景的查詢的數(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ī)約

1. 表達(dá)是與否概念的字段，必須使用 is_xxx 的方式命名，數(shù)據(jù)類型為 unsigned tinyint。 說明：任何字段如果為非負(fù)數(shù)，則必須是 unsigned。

2. 字段允許適當(dāng)冗余，以提高查詢性能，但必須考慮數(shù)據(jù)一致。e.g. 商品類目名稱使用頻率高，字段長度短，名稱基本一成不變，可在相關(guān)聯(lián)的表中冗余存儲(chǔ)類目名稱，

   避免關(guān)聯(lián)查詢

   。冗余字段遵循：

   • 不是頻繁修改的字段；

   • 不是 varchar 超長字段，更不能是 text 字段。

索引規(guī)約

1. 在 varchar 字段上建立索引時(shí)，必須指定索引長度，沒必要對(duì)全字段建立索引，根據(jù)實(shí)際文本區(qū)分度決定索引長度即可。

2. 頁面搜索嚴(yán)禁左模糊或者全模糊，如果需要請(qǐng)通過搜索引擎來解決。 說明：索引文件具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引。

3. 如果有 order by 的場(chǎng)景，請(qǐng)注意利用索引的有序性。order by 最后的字段是組合索引的一部分，并且放在索引組合順序的最后，避免出現(xiàn) file_sort 的情況，影響查詢性能。

   • 正例：where a=? and b=? order by c; 索引: a_b_c。

   • 反例：索引中有范圍查找，那么索引有序性無法利用，如 WHERE a>10 ORDER BY b; 索引 a_b 無法排序。

4. 利用延遲關(guān)聯(lián)或者子查詢優(yōu)化超多分頁場(chǎng)景。 說明：MySQL 并不是跳過 offset 行，而是取 offset+N 行，然后返回放棄前 offset 的行，返回 N 行。當(dāng) offset 特別大的時(shí)候，效率會(huì)非常的低下，要么控制返回的總頁數(shù)，要么對(duì)超過閾值的頁數(shù)進(jìn)行 SQL 改寫。

5. 建組合索引的時(shí)候，區(qū)分度最高的在最左邊。

6. SQL 性能優(yōu)化的目標(biāo)，至少要達(dá)到 range 級(jí)別，要求是 ref 級(jí)別，最好是 consts。

SQL 語句

1. 不要使用 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 值的行。

2. count(distinct column) 計(jì)算該列除 NULL 外的不重復(fù)行數(shù)。注意，count(distinct column1,column2) 如果其中一列全為 NULL，那么即使另一列用不同的值，也返回為 0。

3. 當(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;

1. 使用 ISNULL() 來判斷是否為 NULL 值。 說明：NULL 與任何值的直接比較都為 NULL。

2. 不得使用外鍵與級(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ù)庫的插入速度。

3. 禁止使用存儲(chǔ)過程。存儲(chǔ)過程難以調(diào)試和擴(kuò)展，更沒有移植性。

4. in 操作能避免則避免。若實(shí)在避免不了，需要仔細(xì)評(píng)估 in 后面的集合元素?cái)?shù)量，控制在 1000 個(gè)之內(nèi)。

ORM 映射

1. POJO 類的布爾屬性不能加 is，而數(shù)據(jù)庫字段必須加 is_，要求在 resultMap 中進(jìn)行字段與屬性的映射。

2. sql.xml 配置參數(shù)使用：#{}, #param#，不要使用 ${}，此種方式容易出現(xiàn) SQL 注入。

3. @Transactional 事務(wù)不要濫用。事務(wù)會(huì)影響數(shù)據(jù)庫的 QPS。另外，使用事務(wù)的地方需要考慮各方面的回滾方案，包括緩存回滾、搜索引擎回滾、消息補(bǔ)償、統(tǒng)計(jì)修正等。

推薦學(xué)習(xí)：mysql視頻教程