精選歸納15個Mysql優化問題

這篇文章為大家帶來了關於mysql的相關知識，其中主要介紹了關於SQL優化的相關問題，包括了在開發過程中是怎麼排查SQL語句、怎麼排查生產環境SQL問題等等內容，下面一起來看一下，希望對大家有幫助。

推薦學習：mysql影片教學

#開發過程如何排查SQL？

排查想法

對大部分程式設計師來說，在開發過程中排查SQL基本上就是空白。但隨著產業的內卷，對一開發過程越來越重視且專業，其中一項就是開發過程中盡可能解決掉SQL問題，避免生產才暴露SQL問題。那麼在開發過程中如何方便的進行程式的SQL排查呢？

其想法還是使用Mysql的慢日誌來實現：

• 首先在開發過程中也需要開啟資料庫Mysql的慢查詢

  SET GLOBAL slow_query_log='on';

• 其次設定慢SQL的最小時間

  注意：這裡時間單位是s秒但是有6位小數因此可以表示到微妙的時間力度，一般單表SQL執行時間在20ms之內為宜，反之理解就是在開發過程中，如果你執行的sql語句超過了20ms則你需要去關注它。

  SET GLOBAL long_query_time=0.02;

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

  SET GLOBAL log_output='TABLE';

• 最後透過mysql.slow_log表就可以查詢到記錄的慢SQL

# 

使用工具

#在勇哥為大家開發的軟體中，也提供了圖形化的介面來一鍵幫助大家快速實現上述功能。

 

生產環境SQL問題如何追蹤？

排查想法

產生SQL問題的檢查就相對複雜一點點，但是整體的思路還是透過慢SQL來排查，具體思維如下：

• 先開啟資料庫Mysql的慢查詢

  SET GLOBAL slow_query_log='on';

• 其次設定慢SQL的最小時間

  SET GLOBAL long_query_time=0.02;

• 一般產生時把慢​​SQL放到檔案

  SET GLOBAL log_output='FILE';

• 下載慢SQL日誌檔案到本機

• #最後關閉資料庫Mysql的慢查詢

  著重註意：生產的慢SQL最好在使用時，才去開啟，用完後關閉，避免日誌記錄影響到業務效能

  SET GLOBAL slow_query_log='off';

SQL怎麼調優？

SQL調優融合多面向的知識，整體來說常見從表格結構、資料表索引、兩方面來最佳化。

表結構優化

1、合理的使用字段類別及長度

舉個例子來理解：就一個性別字段，用tinyint(1)存儲佔用1字節，用int(1)存儲佔用4個字節，如果有100W條記錄，那麼用int存儲的表就比tinyint存儲的表文件大小多2.8M左右，因此在讀取int類型存儲的表時檔案大，讀速度相比讀tinyint的慢。這其實就是為什麼說要合理使用欄位類型長度的本質：就是減少儲存的檔案大小，以提供讀取效能。

當然有的朋友就可能說2.8M並不影響大局，因此可以忽略。對於這個想法勇哥要補充一嘴：一個表假設有10個字段，你的系統一共有30個表，那麼再看一下多出的文件大小是多少？ （2.8Mx10x30=840M，840M你用迅雷超級下載也要花好幾秒，這個時間在計算機裡面算是很慢了...）

2、合理的使用冗餘設計

2.1、冗餘設計背景－臨時表

Mysql內部存在一種特殊且輕量級的臨時表，它是被Mysql自動建立和刪除的。主要在SQL的執行過程中使用臨時表來儲存某些操作的中間結果，該過程由 MySQL 自動完成，使用者無法手動幹預，且這種內部表對使用者來說是不可見的。

內部臨時表在 SQL 語句的最佳化過程中非常重要，MySQL 中的許多操作都要依賴內部臨時表來進行最佳化操作。但是使用內部臨時表需要建立表格以及中間資料的存取代價，所以在寫 SQL 語句的時候應該盡量去避免使用臨時表。

那麼場景的那些場景Mysql內部會使用暫存表呢？

• 多重資料表關聯查詢(JOIN)中，order by 或group by使用的資料列不是第一個資料表的列

• group by 的列不是索引列時

• distinct和group by 聯合使用

• order by 語句中使用了distinct關鍵字

• group by的列時索引列，但資料量過大時

2.2、如何查看是否使用內部暫存表？

透過Explain關鍵字或工具的功能按鈕，查看SQL的執行過程，在結果中的Extra列中如果出現Using temporary關鍵字，則表示你的SQL語句在執行時使用了臨時表。

如下圖，角色Role表和角色群組Role_Group是多對1的關係，在關聯查詢的時候，排序使用role_group的id排序則會使用臨時表（見下圖1），如果排序使用role的id則不會使用臨時表（見圖2）。

 

2.3、如何解決不使用內部暫存表？

這個問題解決有兩個方案，一是調整SQL語句避免使用臨時表，另一個方案就是在表中冗餘儲存。 例如2.2中的圖一範例如果一定要按照role_group的id排序，則可以按照role表中的group_id排序，而這列正是冗餘儲存的role_group表中id列值。

3、合理的使用分庫分錶

分庫分錶不僅用於大數量情況下的最佳化，其中垂直分錶還可以使用到SQL調優下。 （這裡我就不去解釋垂直和水平分錶了，有興趣的私訊我）

例如：一個文章表一般設計不會包括文章內容這個大欄位。

文章內容這個大字段是單獨放置到一張表中

為什麼文章表要採用以上設計而不把欄位合併到一表中呢？

我們先來計算一道數學題，假設一篇文章總共1M大小，其中文章內容，824KB，其餘字段200KB，這樣的文章一共有100W條，則：

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

• 方案二，如果用垂直分錶存儲，則基本表時200KBx100W，內容表824KBx100W

我們在前端有文章列表和文章詳情兩個頁面，分別要直接從資料庫查詢相關內容，則：

• #方案二，文章清單和文章詳情的查詢都會從100WM資料查詢

• 方案二，文章清單會從200KBx100W查詢，文章詳情會從824KBx100W中查詢（目前也可能還需要從200KBx100W中查詢）

說到這裡，相信大家心中應該有一個清晰的答案了吧！ 垂直拆表可以讓不同業務場景的查詢的資料量不同，常常這個資料量往往小於總表資料量，這就比從固定很大小的量中查詢更靈活和高效率。

表索引最佳化

1、合理的新增索引列

大多數人對應索引的理解層次都在「索引可以加快查詢的速度”，然而這句話勇哥要補充下半句“索引可以加快查詢的速度，也可以減慢資料插入或修改的速度”。

如果一個表有5個索引，那麼可以簡單的把一個索引當成一個表，則這就會有1張表6張索引表=相當於有6張表，那麼這6張表什麼時候會操作呢？我們來計算一下：

• insert操作，資料插入後，需要去對5張索引表插入索引資料

• delete操作，數據刪除後，需要去把5張索引表中的索引刪除

• update操作

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

  • 如果沒有修改索引列的數據，則只修改資料表

• select操作

  • 如果命中查詢索引，則先查詢索引，再查資料表

  • 如果沒命中查詢索引，則直接查資料表

透過以上的計算，你會神奇的發現，索引個數越多，對於insert、delete 、update操作是有影響的，而且是負面影響。所以對於索引竟可能評估其帶來的影響小於查詢的收益，才去添加，而不是盲目的添加。

2、合理的调配复合索引列个数和顺序

复合索引指的是包括有多个列的索引，它能有效的减少表的索引个数，平衡了多个字段需要多个索引直接的性能平衡，但是再使用复合索引的时候，需要注意索引列个数和顺序的问题。

先说列个数的问题，指的是一个复合索引中包括的列字段太多影响性能的问题，主要是对update操作的性能影响，如下红字：

• 如果修改了索引列的数据，则先修改数据，还需要修改索引表中的索引，如果索引列个数越多则修改该索引的概率越大

• 如果没有修改索引列的数据，则只修改数据表

再说复合索引中列顺序的问题，是指索引的最左匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，这个比较容易理解，就不多做阐述。

那些情况索引会失效？

• 索引无法存储null值，当使用is null或is not nulli时会全表扫描

• like查询以"%"开头

• 对于复合索引，查询条件中没有给出索引中第一列的值时

• mysql内部评估全表扫描比索引快时

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

表设计有那些规范？

建表规约

1. 表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型为 unsigned tinyint。 说明：任何字段如果为非负数，则必须是 unsigned。

2. 字段允许适当冗余，以提高查询性能，但必须考虑数据一致。e.g. 商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，

   避免关联查询

   。冗余字段遵循：

   • 不是频繁修改的字段；

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

索引规约

1. 在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

2. 页面搜索严禁左模糊或者全模糊，如果需要请通过搜索引擎来解决。 说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

3. 如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。

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

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

4. 利用延迟关联或者子查询优化超多分页场景。 说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 的行，返回 N 行。当 offset 特别大的时候，效率会非常的低下，要么控制返回的总页数，要么对超过阈值的页数进行 SQL 改写。

5. 建组合索引的时候，区分度最高的在最左边。

6. SQL 性能优化的目标，至少要达到 range 级别，要求是 ref 级别，最好是 consts。

SQL 语句

1. 不要使用 count(列名) 或 count(常量) 来替代 count()，count() 是 SQL92 定义的标准统计行数的语句，跟数据库无关，跟 NULL 和非 NULL 无关。 说明：count(*) 会统计值为 NULL 的行，而 count(列名) 不会统计此列为 NULL 值的行。

2. count(distinct column) 计算该列除 NULL 外的不重复行数。注意，count(distinct column1,column2) 如果其中一列全为 NULL，那么即使另一列用不同的值，也返回为 0。

3. 当某一列的值全为 NULL 时，count(column) 的返回结果为 0，但 sum(column) 的返回结果为 NULL，因此使用 sum() 时需注意 NPE 问题。 可以使用如下方式来避免 sum 的 NPE 问题。

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

1. 使用 ISNULL() 来判断是否为 NULL 值。 说明：NULL 与任何值的直接比较都为 NULL。

2. 不得使用外鍵與級聯，一切外鍵概念必須在應用層解決。 說明：以學生和成績的關係為例，學生表的 student_id 是主鍵，成績表的 student_id 則為外鍵。如果更新學生表中的 student_id，同時觸發成績表中的 student_id 更新，即為級聯更新。外鍵與級聯更新適用於單機低並發，不適合分散式、高並發叢集；級聯更新是強阻塞，存在資料庫更新風暴的風險；外鍵影響資料庫的插入速度。

3. 禁止使用預存程序。儲存過程難以調試和擴展，更沒有移植性。

4. in 操作能避免則避免。若實在避免不了，需要仔細評估 in 後面的集合元素數量，控制在 1000 個之內。

ORM 映射

1. POJO 類別的布林屬性不能加is，而資料庫欄位必須加is_，要求在resultMap 中進行欄位與屬性的映射。

2. sql.xml 設定參數使用：#{}, #param#，不要使用${}，此種方式容易出現SQL 注入。

3. @Transactional 事務不要濫用。事務會影響資料庫的 QPS。另外，使用事務的地方需要考慮各方面的回溯方案，包括快取回溯、搜尋引擎回滾、訊息補償、統計修正等。

推薦學習：mysql影片教學

以上是精選歸納15個Mysql優化問題的詳細內容。更多資訊請關注PHP中文網其他相關文章！