MySQL性能優(yōu)化實踐（很全面，值得收藏）

一 題記

最近公司項目添加新功能，上線后發(fā)現有些功能的列表查詢時間很久。原因是新功能用到舊功能的接口，而這些舊接口的 SQL 查詢語句關聯(lián)5,6張表且編寫不夠規(guī)范，導致 MySQL 在執(zhí)行 SQL 語句時索引失效，進行全表掃描。原本負責優(yōu)化的同事有事請假回家，因此優(yōu)化查詢數據的問題落在筆者手中。筆者在查閱網上 SQL 優(yōu)化的資料后成功解決了問題，在此從全局角度，記錄和總結 MySQL 查詢優(yōu)化相關技巧。

二、優(yōu)化思路

數據查詢慢，不代表 SQL 語句寫法有問題。 首先，我們需要找到問題的源頭才能“對癥下藥”。筆者用一張流程圖展示 MySQL 優(yōu)化的思路：

MySQL性能優(yōu)化實踐（很全面，值得收藏）

無需更多言語，從圖中可以清楚地看出，導致數據查詢慢的原因有多種，如：緩存失效，在此一段時間內由于高并發(fā)訪問導致 MySQL 服務器崩潰；SQL 語句編寫問題；MySQL 服務器參數問題；硬件配置限制 MySQL 服務性能問題等。

三、查看 MySQL 服務器運行的狀態(tài)值

如果系統(tǒng)的并發(fā)請求數不高，且查詢速度慢，可以忽略該步驟直接進行 SQL 語句調優(yōu)步驟。

執(zhí)行命令：

show status

由于返回結果太多，此處不貼出結果。其中，在返回的結果中，我們主要關注 “Queries”、“Threadsconnected” 和 “Threadsrunning” 的值，即查詢次數、線程連接數和線程運行數。

我們可以通過執(zhí)行如下腳本監(jiān)控 MySQL 服務器運行的狀態(tài)值

#!/bin/bashwhile truedomysqladmin -uroot -p"密碼" ext | awk '/Queries/{q=$4}/Threads_connected/{c=$4}/Threads_running/{r=$4}END{printf("%d %d %d\n",q,c,r)}' >> status.txtsleep 1done

執(zhí)行該腳本 24 小時，獲取 status.txt 里的內容，再次通過 awk 計算==每秒請求 MySQL 服務的次數==

awk '{q=$1-last;last=$1}{printf("%d %d %d\n",q,$2,$3)}' status.txt

復制計算好的內容到 Excel 中生成圖表觀察數據周期性。

如果觀察的數據有周期性的變化，如上圖的解釋，需要修改緩存失效策略。

例如：

通過隨機數在[3,6,9] 區(qū)間獲取其中一個值作為緩存失效時間，這樣分散了緩存失效時間，從而節(jié)省了一部分內存的消耗。

當訪問高峰期時，一部分請求分流到未失效的緩存，另一部分則訪問 MySQL 數據庫，這樣減少了 MySQL 服務器的壓力。

四、獲取需要優(yōu)化的 SQL 語句

4.1 方式一：查看運行的線程

執(zhí)行命令：

show processlist

返回結果：

mysql> show processlist;+----+------+-----------+------+---------+------+----------+------------------+| Id | User | Host      | db   | Command | Time | State    | Info             |+----+------+-----------+------+---------+------+----------+------------------+|  9 | root | localhost | test | Query   |    0 | starting | show processlist |+----+------+-----------+------+---------+------+----------+------------------+1 row in set (0.00 sec)

從返回結果中我們可以了解該線程執(zhí)行了什么命令/SQL 語句以及執(zhí)行的時間。實際應用中，查詢的返回結果會有 N 條記錄。

其中，返回的 State 的值是我們判斷性能好壞的關鍵，其值出現如下內容，則該行記錄的 SQL 語句需要優(yōu)化：

Converting HEAP to MyISAM # 查詢結果太大時，把結果放到磁盤，嚴重Create tmp table #創(chuàng)建臨時表，嚴重Copying to tmp table on disk  #把內存臨時表復制到磁盤，嚴重locked #被其他查詢鎖住，嚴重loggin slow query #記錄慢查詢Sorting result #排序

4.2 方式二：開啟慢查詢日志

在配置文件 my.cnf 中的 [mysqld] 一行下邊添加兩個參數：

slow_query_log = 1slow_query_log_file=/var/lib/mysql/slow-query.loglong_query_time = 2log_queries_not_using_indexes = 1

其中，slowquerylog = 1 表示開啟慢查詢；slowquerylogfile 表示慢查詢日志存放的位置；longquerytime = 2 表示查詢 >=2 秒才記錄日志；logqueriesnotusing_indexes = 1 記錄沒有使用索引的 SQL 語句。

注意：slowquerylog_file 的路徑不能隨便寫，否則 MySQL 服務器可能沒有權限將日志文件寫到指定的目錄中。建議直接復制上文的路徑。

修改保存文件后，重啟 MySQL 服務。在 /var/lib/mysql/ 目錄下會創(chuàng)建 slow-query.log 日志文件。連接 MySQL 服務端執(zhí)行如下命令可以查看配置情況。

show variables like 'slow_query%';show variables like 'long_query_time';

測試慢查詢日志：

mysql> select sleep(2);+----------+| sleep(2) |+----------+|        0 |+----------+1 row in set (2.00 sec)

打開慢查詢日志文件

[root@localhost mysql]# vim /var/lib/mysql/slow-query.log/usr/sbin/mysqld, Version: 5.7.19-log (MySQL Community Server (GPL)). started with:Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sockTime                 Id Command    Argument# Time: 2017-10-05T04:39:11.408964Z# User@Host: root[root] @ localhost []  Id:     3# Query_time: 2.001395  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0use test;SET timestamp=1507178351;select sleep(2);

我們可以看到剛才執(zhí)行了 2 秒的 SQL 語句被記錄下來了。

雖然在慢查詢日志中記錄查詢慢的 SQL 信息，但是日志記錄的內容密集且不易查閱。因此，我們需要通過工具將 SQL 篩選出來。

MySQL 提供 mysqldumpslow <u style="-webkit-tap-highlight-color: transparent; box-sizing: border-box;">工具對日志進行分析</u>。我們可以使用 mysqldumpslow --help 查看命令相關用法。

常用參數如下：

    -s：排序方式，后邊接著如下參數        c：訪問次數        l：鎖定時間        r：返回記錄        t：查詢時間    al：平均鎖定時間    ar：平均返回記錄書    at：平均查詢時間    -t：返回前面多少條的數據    -g：翻遍搭配一個正則表達式，大小寫不敏感

案例：

獲取返回記錄集最多的10個sqlmysqldumpslow -s r -t 10 /var/lib/mysql/slow-query.log獲取訪問次數最多的10個sqlmysqldumpslow -s c -t 10 /var/lib/mysql/slow-query.log獲取按照時間排序的前10條里面含有左連接的查詢語句mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow-query.log

五、分析 SQL 語句

5.1 方式一：explain

篩選出有問題的 SQL，我們可以使用 MySQL 提供的 explain 查看 SQL 執(zhí)行計劃情況（關聯(lián)表，表查詢順序、索引使用情況等）。

用法：

explain select * from category;

返回結果：

mysql> explain select * from category;+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+|  1 | SIMPLE      | category | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

字段解釋：1) id：select 查詢序列號。id相同，執(zhí)行順序由上至下；id不同，id值越大優(yōu)先級越高，越先被執(zhí)行

  1. select_type：查詢數據的操作類型，其值如下：
  • simple：簡單查詢，不包含子查詢或 union
  • primary:包含復雜的子查詢，最外層查詢標記為該值
  • subquery：在 select 或 where 包含子查詢，被標記為該值
  • derived：在 from 列表中包含的子查詢被標記為該值，MySQL 會遞歸執(zhí)行這些子查詢，把結果放在臨時表
  • union：若第二個 select 出現在 union 之后，則被標記為該值。若 union 包含在 from 的子查詢中，外層 select 被標記為 derived
  • union result：從 union 表獲取結果的 select

  1. table：顯示該行數據是關于哪張表

  2. partitions：匹配的分區(qū)

  3. type：表的連接類型，其值，性能由高到底排列如下：

  • system：表只有一行記錄，相當于系統(tǒng)表
  • const：通過索引一次就找到，只匹配一行數據
  • eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常用于主鍵或唯一索引掃描
  • ref：非唯一性索引掃描，返回匹配某個單獨值的所有行。用于=、< 或 > 操作符帶索引的列
  • range：只檢索給定范圍的行，使用一個索引來選擇行。一般使用between、>、<情況
  • index：只遍歷索引樹
  • ALL：全表掃描，性能最差

注：前5種情況都是理想情況的索引使用情況。通常優(yōu)化至少到range級別，最好能優(yōu)化到 ref

  1. possible_keys：指出 MySQL 使用哪個索引在該表找到行記錄。如果該值為 NULL，說明沒有使用索引，可以建立索引提高性能

  2. key：顯示 MySQL 實際使用的索引。如果為 NULL，則沒有使用索引查詢

  3. key_len：表示索引中使用的字節(jié)數，通過該列計算查詢中使用的索引的長度。在不損失精確性的情況下，長度越短越好 顯示的是索引字段的最大長度，并非實際使用長度

  4. ref：顯示該表的索引字段關聯(lián)了哪張表的哪個字段

  5. rows：根據表統(tǒng)計信息及選用情況，大致估算出找到所需的記錄或所需讀取的行數，數值越小越好

  6. filtered：返回結果的行數占讀取行數的百分比，值越大越好

  7. extra：包含不合適在其他列中顯示但十分重要的額外信息，常見的值如下：

  • using filesort：說明 MySQL 會對數據使用一個外部的索引排序，而不是按照表內的索引順序進行讀取。出現該值，應該優(yōu)化 SQL
  • using temporary：使用了臨時表保存中間結果，MySQL 在對查詢結果排序時使用臨時表。常見于排序 order by 和分組查詢 group by。出現該值，應該優(yōu)化 SQL
  • using index：表示相應的 select 操作使用了覆蓋索引，避免了訪問表的數據行，效率不錯
  • using where：where 子句用于限制哪一行
  • using join buffer：使用連接緩存
  • distinct：發(fā)現第一個匹配后，停止為當前的行組合搜索更多的行

注意：出現前 2 個值，SQL 語句必須要優(yōu)化。

5.2 方式二：profiling

使用 profiling 命令可以了解 SQL 語句消耗資源的詳細信息（每個執(zhí)行步驟的開銷）。

5.2.1 查看 profile 開啟情況

select @@profiling;

返回結果：

mysql> select @@profiling;+-------------+| @@profiling |+-------------+|           0 |+-------------+1 row in set, 1 warning (0.00 sec)

0 表示關閉狀態(tài),1 表示開啟

5.2.2 啟用 profile

set profiling = 1;

返回結果：

mysql> set profiling = 1;  Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> select @@profiling;+-------------+| @@profiling |+-------------+|           1 |+-------------+1 row in set, 1 warning (0.00 sec)

在連接關閉后，profiling 狀態(tài)自動設置為關閉狀態(tài)。

5.2.3 查看執(zhí)行的 SQL 列表

show profiles;

返回結果：

mysql> show profiles;+----------+------------+------------------------------+| Query_ID | Duration   | Query                        |+----------+------------+------------------------------+|        1 | 0.00062925 | select @@profiling           ||        2 | 0.00094150 | show tables                  ||        3 | 0.00119125 | show databases               ||        4 | 0.00029750 | SELECT DATABASE()            ||        5 | 0.00025975 | show databases               ||        6 | 0.00023050 | show tables                  ||        7 | 0.00042000 | show tables                  ||        8 | 0.00260675 | desc role                    ||        9 | 0.00074900 | select name,is_key from role |+----------+------------+------------------------------+9 rows in set, 1 warning (0.00 sec)

該命令執(zhí)行之前，需要執(zhí)行其他 SQL 語句才有記錄。

5.2.4 查詢指定 ID 的執(zhí)行詳細信息

show profile for query Query_ID;

返回結果：

mysql> show profile for query 9;+----------------------+----------+| Status               | Duration |+----------------------+----------+| starting             | 0.000207 || checking permissions | 0.000010 || Opening tables       | 0.000042 || init                 | 0.000050 || System lock          | 0.000012 || optimizing           | 0.000003 || statistics           | 0.000011 || preparing            | 0.000011 || executing            | 0.000002 || Sending data         | 0.000362 || end                  | 0.000006 || query end            | 0.000006 || closing tables       | 0.000006 || freeing items        | 0.000011 || cleaning up          | 0.000013 |+----------------------+----------+15 rows in set, 1 warning (0.00 sec)

每行都是狀態(tài)變化的過程以及它們持續(xù)的時間。Status 這一列和 show processlist 的 State 是一致的。因此，需要優(yōu)化的注意點與上文描述的一樣。

5.2.5 獲取 CPU、 Block IO 等信息

show profile block io,cpu for query Query_ID;show profile cpu,block io,memory,swaps,context switches,source for query Query_ID;show profile all for query Query_ID;

六、優(yōu)化手段

主要以查詢優(yōu)化、索引使用和表結構設計方面進行講解。

6.1 查詢優(yōu)化

  1. 避免 SELECT *，需要什么數據，就查詢對應的字段。

  2. 小表驅動大表，即小的數據集驅動大的數據集。如：以 A，B 兩表為例，兩表通過 id 字段進行關聯(lián)。

當 B 表的數據集小于 A 表時，用 in 優(yōu)化 exist；使用 in ，兩表執(zhí)行順序是先查 B 表，再查 A 表

select * from A where id in (select id from B)

當 A 表的數據集小于 B 表時，用 exist 優(yōu)化 in；使用 exists，兩表執(zhí)行順序是先查 A 表，再查 B 表

select * from A where exists (select 1 from B where B.id = A.id)

  1. 一些情況下，可以使用連接代替子查詢，因為使用 join，MySQL 不會在內存中創(chuàng)建臨時表。

  2. 適當添加冗余字段，減少表關聯(lián)。

  3. 合理使用索引（下文介紹）。如：為排序、分組字段建立索引，避免 filesort 的出現。更多：來一份MySQL索引數據結構及優(yōu)化清單

6.2 索引使用

6.2.1 適合使用索引的場景

  1. 主鍵自動創(chuàng)建唯一索引

  2. 頻繁作為查詢條件的字段

  3. 查詢中與其他表關聯(lián)的字段

  4. 查詢中排序的字段

  5. 查詢中統(tǒng)計或分組字段

6.2.2 不適合使用索引的場景

  1. 頻繁更新的字段

  2. where 條件中用不到的字段

  3. 表記錄太少

  4. 經常增刪改的表

  5. 字段的值的差異性不大或重復性高

6.2.3 索引創(chuàng)建和使用原則

  1. 單表查詢：哪個列作查詢條件，就在該列創(chuàng)建索引

  2. 多表查詢：left join 時，索引添加到右表關聯(lián)字段；right join 時，索引添加到左表關聯(lián)字段

  3. 不要對索引列進行任何操作（計算、函數、類型轉換）

  4. 索引列中不要使用 !=，<> 非等于

  5. 索引列不要為空，且不要使用 is null 或 is not null 判斷

  6. 索引字段是字符串類型，查詢條件的值要加''單引號,避免底層類型自動轉換

違背上述原則可能會導致索引失效，具體情況需要使用 explain 命令進行查看

6.2.4 索引失效情況

除了違背索引創(chuàng)建和使用原則外，如下情況也會導致索引失效：

  1. 模糊查詢時，以 % 開頭

  2. 使用 or 時，如：字段1（非索引）or 字段2（索引）會導致索引失效。

  3. 使用復合索引時，不使用第一個索引列。

index(a,b,c) ，以字段 a,b,c 作為復合索引為例：

MySQL性能優(yōu)化實踐（很全面，值得收藏）

6.3.1 選擇合適的數據類型6.3 數據庫表結構設計

  1. 使用可以存下數據最小的數據類型

  2. 使用簡單的數據類型。int 要比 varchar 類型在mysql處理簡單

  3. 盡量使用 tinyint、smallint、mediumint 作為整數類型而非 int

  4. 盡可能使用 not null 定義字段，因為 null 占用4字節(jié)空間

  5. 盡量少用 text 類型,非用不可時最好考慮分表

  6. 盡量使用 timestamp 而非 datetime

  7. 單表不要有太多字段，建議在 20 以內

6.3.2 表的拆分

當數據庫中的數據非常大時，查詢優(yōu)化方案也不能解決查詢速度慢的問題時，我們可以考慮拆分表，讓每張表的數據量變小，從而提高查詢效率。

  1. 垂直拆分：將表中多個列分開放到不同的表中。例如用戶表中一些字段經常被訪問，將這些字段放在一張表中，另外一些不常用的字段放在另一張表中。插入數據時，使用事務確保兩張表的數據一致性。

  2. 水平拆分：按照行進行拆分。例如用戶表中，使用用戶ID，對用戶ID取10的余數，將用戶數據均勻的分配到0~9的10個用戶表中。查找時也按照這個規(guī)則查詢數據。

6.3.3 讀寫分離

一般情況下對數據庫而言都是“讀多寫少”。換言之，數據庫的壓力多數是因為大量的讀取數據的操作造成的。我們可以采用數據庫集群的方案，使用一個庫作為主庫，負責寫入數據；其他庫為從庫，負責讀取數據。這樣可以緩解對數據庫的訪問壓力。

七、服務器參數調優(yōu)

7.1 內存相關

sortbuffersize 排序緩沖區(qū)內存大小

joinbuffersize 使用連接緩沖區(qū)大小

readbuffersize 全表掃描時分配的緩沖區(qū)大小

7.2 IO 相關

Innodblogfile_size 事務日志大小

Innodblogfilesingroup 事務日志個數

Innodblogbuffer_size 事務日志緩沖區(qū)大小

Innodbflushlogattrx_commit 事務日志刷新策略 ，其值如下：

0：每秒進行一次 log 寫入 cache，并 flush log 到磁盤

1：在每次事務提交執(zhí)行 log 寫入 cache，并 flush log 到磁盤

2：每次事務提交，執(zhí)行 log 數據寫到 cache，每秒執(zhí)行一次 flush log 到磁盤

7.3 安全相關

expirelogsdays 指定自動清理 binlog 的天數

maxallowedpacket 控制 MySQL 可以接收的包的大小

skipnameresolve 禁用 DNS 查找

read_only 禁止非 super 權限用戶寫權限

skipslavestart 級你用 slave 自動恢復

7.4 其他

max_connections 控制允許的最大連接數

tmptablesize 臨時表大小

maxheaptable_size 最大內存表大小

筆者并沒有使用這些參數對 MySQL 服務器進行調優(yōu)，具體詳情介紹和性能效果請參考文章末尾的資料或另行百度。

八、硬件選購和參數優(yōu)化

硬件的性能直接決定 MySQL 數據庫的性能。硬件的性能瓶頸，直接決定 MySQL 數據庫的運行數據和效率。

作為軟件開發(fā)程序員，我們主要關注軟件方面的優(yōu)化內容，以下硬件方面的優(yōu)化作為了解即可

8.1 內存相關

內存的 IO 比硬盤的速度快很多，可以增加系統(tǒng)的緩沖區(qū)容量，使數據在內存停留的時間更長，以減少磁盤的 IO

8.2 磁盤 I/O 相關

  1. 使用 SSD 或 PCle SSD 設備，至少獲得數百倍甚至萬倍的 IOPS 提升

  2. 購置陣列卡同時配備 CACHE 及 BBU 模塊，可以明顯提升 IOPS

  3. 盡可能選用 RAID-10，而非 RAID-5

8.3 配置 CUP 相關

在服務器的 BIOS 設置中，調整如下配置：

  1. 選擇 Performance Per Watt Optimized（DAPC）模式，發(fā)揮 CPU 最大性能

  2. 關閉 C1E 和 C States 等選項，提升 CPU 效率

  3. Memory Frequency（內存頻率）選擇 Maximum Performance

?著作權歸作者所有,轉載或內容合作請聯(lián)系作者
【社區(qū)內容提示】社區(qū)部分內容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內容（如有圖片或視頻亦包括在內）由作者上傳并發(fā)布，文章內容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內容

  • 數據庫優(yōu)化 sql語句優(yōu)化 索引優(yōu)化 加緩存 讀寫分離 分區(qū) 分布式數據庫（垂直切分） 水平切分 MyISAM和I...
    半瓶陽光o_o閱讀 658評論 0贊 2
  • 什么是數據庫？ 數據庫是存儲數據的集合的單獨的應用程序。每個數據庫具有一個或多個不同的API，用于創(chuàng)建，訪問，管理...
    chen_000閱讀 4,124評論 0贊 19
  • 六點多被迫醒來，穿上頭一天被汗?jié)耢F濕又晚上被潮濕的上衣，還有下半截裹滿了泥的褲子和已經全身糊滿泥的鞋子。在鐵皮客棧...
    榕樹家的故事閱讀 852評論 0贊 0

友情鏈接更多精彩內容