精選歸納15個Mysql優化問題-mysql教程-PHP中文網

首先在開發過程中也需要開啟資料庫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等查询也可能引起索引失效

表设计有那些规范？

建表规约

表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型为 unsigned tinyint。说明：任何字段如果为非负数，则必须是 unsigned。
字段允许适当冗余，以提高查询性能，但必须考虑数据一致。e.g. 商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，

避免关联查询

。冗余字段遵循：
- 不是频繁修改的字段；
- 不是 varchar 超长字段，更不能是 text 字段。

索引规约

在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。
页面搜索严禁左模糊或者全模糊，如果需要请通过搜索引擎来解决。说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
- 正例：where a=? and b=? order by c; 索引: a_b_c。
- 反例：索引中有范围查找，那么索引有序性无法利用，如 WHERE a>10 ORDER BY b; 索引 a_b 无法排序。
利用延迟关联或者子查询优化超多分页场景。说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 的行，返回 N 行。当 offset 特别大的时候，效率会非常的低下，要么控制返回的总页数，要么对超过阈值的页数进行 SQL 改写。
建组合索引的时候，区分度最高的在最左边。
SQL 性能优化的目标，至少要达到 range 级别，要求是 ref 级别，最好是 consts。

SQL 语句

不要使用 count(列名) 或 count(常量) 来替代 count()，count() 是 SQL92 定义的标准统计行数的语句，跟数据库无关，跟 NULL 和非 NULL 无关。说明：count(*) 会统计值为 NULL 的行，而 count(列名) 不会统计此列为 NULL 值的行。
count(distinct column) 计算该列除 NULL 外的不重复行数。注意，count(distinct column1,column2) 如果其中一列全为 NULL，那么即使另一列用不同的值，也返回为 0。
当某一列的值全为 NULL 时，count(column) 的返回结果为 0，但 sum(column) 的返回结果为 NULL，因此使用 sum() 时需注意 NPE 问题。可以使用如下方式来避免 sum 的 NPE 问题。

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

登入後複製

使用 ISNULL() 来判断是否为 NULL 值。说明：NULL 与任何值的直接比较都为 NULL。
不得使用外鍵與級聯，一切外鍵概念必須在應用層解決。說明：以學生和成績的關係為例，學生表的 student_id 是主鍵，成績表的 student_id 則為外鍵。如果更新學生表中的 student_id，同時觸發成績表中的 student_id 更新，即為級聯更新。外鍵與級聯更新適用於單機低並發，不適合分散式、高並發叢集；級聯更新是強阻塞，存在資料庫更新風暴的風險；外鍵影響資料庫的插入速度。
禁止使用預存程序。儲存過程難以調試和擴展，更沒有移植性。
in 操作能避免則避免。若實在避免不了，需要仔細評估 in 後面的集合元素數量，控制在 1000 個之內。

ORM 映射

POJO 類別的布林屬性不能加is，而資料庫欄位必須加is_，要求在resultMap 中進行欄位與屬性的映射。
sql.xml 設定參數使用：#{}, #param#，不要使用${}，此種方式容易出現SQL 注入。
@Transactional 事務不要濫用。事務會影響資料庫的 QPS。另外，使用事務的地方需要考慮各方面的回溯方案，包括快取回溯、搜尋引擎回滾、訊息補償、統計修正等。

推薦學習：mysql影片教學

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

本網站聲明

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn