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

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

開(kāi)發(fā)過(guò)程如何排查SQL？

排查思路

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

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

• 首先在開(kāi)發(fā)過(guò)程中也需要開(kāi)啟數(shù)據(jù)庫(kù)Mysql的慢查詢

  SET GLOBAL slow_query_log='on';

• 其次設(shè)置慢SQL的最小時(shí)間

  注意：這里時(shí)間單位是s秒但是有6位小數(shù)因此可以表示到微妙的時(shí)間力度，一般單表SQL執(zhí)行時(shí)間在20ms之內(nèi)為宜，反之理解就是在開(kāi)發(fā)過(guò)程中，如果你執(zhí)行的sql語(yǔ)句超過(guò)了20ms則你需要去關(guān)注它。

  SET GLOBAL long_query_time=0.02;

• 為方便操作可以把慢SQL記錄到表中而不是文件中

  SET GLOBAL log_output='TABLE';

• 最后通過(guò)mysql.slow_log表就可以查詢到記錄的慢SQL

使用工具

在勇哥給大家開(kāi)發(fā)的軟件中，也提供了圖形化的界面來(lái)一鍵幫助大家快速實(shí)現(xiàn)上述功能。

生產(chǎn)環(huán)境SQL問(wèn)題如何排查？

排查思路

生成SQL問(wèn)題的排查就相對(duì)復(fù)雜一點(diǎn)點(diǎn)，但是整體的思路還是通過(guò)慢SQL來(lái)排查，具體思路如下：

• 首先開(kāi)啟數(shù)據(jù)庫(kù)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ù)庫(kù)Mysql的慢查詢

  著重注意：生產(chǎn)的慢SQL最好在使用時(shí)，才去開(kāi)啟，用完后關(guān)閉，避免日志記錄影響到業(yè)務(wù)性能

  SET GLOBAL slow_query_log='off';

SQL怎么調(diào)優(yōu)？

SQL調(diào)優(yōu)融合多方面的知識(shí)，總體來(lái)說(shuō)常見(jiàn)從表結(jié)構(gòu)、表索引、兩方面來(lái)優(yōu)化。

表結(jié)構(gòu)優(yōu)化

1、合理的使用字段類及長(zhǎng)度

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

當(dāng)然有的朋友就可能說(shuō)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í)行過(guò)程中使用臨時(shí)表來(lái)存儲(chǔ)某些操作的中間結(jié)果，該過(guò)程由 MySQL 自動(dòng)完成，用戶無(wú)法手工干預(yù)，且這種內(nèi)部表對(duì)用戶來(lái)說(shuō)是不可見(jiàn)的。

內(nèi)部臨時(shí)表在 SQL 語(yǔ)句的優(yōu)化過(guò)程中非常重要，MySQL 中的很多操作都要依賴于內(nèi)部臨時(shí)表來(lái)進(jìn)行優(yōu)化操作。但是使用內(nèi)部臨時(shí)表需要?jiǎng)?chuàng)建表以及中間數(shù)據(jù)的存取代價(jià)，所以在寫(xiě) SQL 語(yǔ)句的時(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 語(yǔ)句中使用了distinct關(guān)鍵字

• group by 的列時(shí)索引列，但數(shù)據(jù)量過(guò)大時(shí)

2.2、如何查看是否使用內(nèi)部臨時(shí)表？

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

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

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

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

3、合理的使用分庫(kù)分表

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

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

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

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

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

• 方案一，如果用一個(gè)表存儲(chǔ)，則這個(gè)表大小是100W*1M=100WM

• 方案二，如果用垂直分表存儲(chǔ)，則基本表時(shí)200KBx100W，內(nèi)容表824KBx100W

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

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

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

說(shuō)到這里，相信大家心中應(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è)索引，那么可以簡(jiǎn)單的把一個(gè)索引當(dāng)成一個(gè)表，則這就會(huì)有1張表+6張索引表=相當(dāng)于有6張表，那么這6張表在什么時(shí)候會(huì)操作呢？我們來(lái)計(jì)算一下：

• insert操作，數(shù)據(jù)插入后，需要去對(duì)5張索引表插入索引數(shù)據(jù)

• delete操作，數(shù)據(jù)刪除后，需要去把5張索引表中的索引刪除

• update操作

  • 如果修改了索引列的數(shù)據(jù)，則先修改數(shù)據(jù)，還需要修改索引表中的索引

  • 如果沒(méi)有修改索引列的數(shù)據(jù)，則只修改數(shù)據(jù)表

• select操作

  • 如果命中查詢索引，則先查詢索引，再查數(shù)據(jù)表

  • 如果沒(méi)命中查詢索引，則直接查數(shù)據(jù)表

通過(guò)以上的計(jì)算，你會(huì)神奇的發(fā)現(xiàn)，索引個(gè)數(shù)越多，對(duì)于insert、delete、update操作是有影響的，而且是負(fù)影響。所以對(duì)于索引竟可能評(píng)估其帶來(lái)的影響小于查詢的收益，才去添加，而不是盲目的添加。

2、合理的調(diào)配復(fù)合索引列個(gè)數(shù)和順序

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

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

• 如果修改了索引列的數(shù)據(jù)，則先修改數(shù)據(jù)，還需要修改索引表中的索引，如果索引列個(gè)數(shù)越多則修改該索引的概率越大

• 如果沒(méi)有修改索引列的數(shù)據(jù)，則只修改數(shù)據(jù)表

再說(shuō)復(fù)合索引中列順序的問(wèn)題，是指索引的最左匹配原則，即最左優(yōu)先，在檢索數(shù)據(jù)時(shí)從聯(lián)合索引的最左邊開(kāi)始匹配，這個(gè)比較容易理解，就不多做闡述。

那些情況索引會(huì)失效？

• 索引無(wú)法存儲(chǔ)null值，當(dāng)使用is null或is not nulli時(shí)會(huì)全表掃描

• like查詢以"%"開(kāi)頭

• 對(duì)于復(fù)合索引，查詢條件中沒(méi)有給出索引中第一列的值時(shí)

• mysql內(nèi)部評(píng)估全表掃描比索引快時(shí)

• or、!=、<>、in、not in等查詢也可能引起索引失效

表設(shè)計(jì)有那些規(guī)范？

建表規(guī)約

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

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

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

   。冗余字段遵循：

   • 不是頻繁修改的字段；

   • 不是 varchar 超長(zhǎng)字段，更不能是 text 字段。

索引規(guī)約

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

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

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

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

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

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

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

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

SQL 語(yǔ)句

1. 不要使用 count(列名) 或 count(常量) 來(lái)替代 count()，count() 是 SQL92 定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語(yǔ)句，跟數(shù)據(jù)庫(kù)無(wú)關(guān)，跟 NULL 和非 NULL 無(wú)關(guān)。 說(shuō)明：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 問(wèn)題。 可以使用如下方式來(lái)避免 sum 的 NPE 問(wèn)題。

SELECT IF(ISNULL(SUM(g), 0, SUM(g))) FROM table;

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

2. 不得使用外鍵與級(jí)聯(lián)，一切外鍵概念必須在應(yīng)用層解決。 說(shuō)明：以學(xué)生和成績(jī)的關(guān)系為例，學(xué)生表的 student_id 是主鍵，成績(jī)表的 student_id 則為外鍵。如果更新學(xué)生表中的 student_id，同時(shí)觸發(fā)成績(jī)表中的 student_id 更新，即為級(jí)聯(lián)更新。外鍵與級(jí)聯(lián)更新適用于單機(jī)低并發(fā)，不適合分布式、高并發(fā)集群；級(jí)聯(lián)更新是強(qiáng)阻塞，存在數(shù)據(jù)庫(kù)更新風(fēng)暴的風(fēng)險(xiǎn)；外鍵影響數(shù)據(jù)庫(kù)的插入速度。

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

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

ORM 映射

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

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

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

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