1、數據庫服務器的優化步驟

2、查看系統性能參數

• 可以使用show status語句查詢一些MySQL數據庫服務器的性能參數 執行頻率
• 語法格式：show [ global | session ] status like '參數' ；
• 常用性能參數如下所示

3、慢查詢日志（定位執行慢的SQL）

• MySQL慢查詢日志用來記錄MySQL中響應時間超過設定閾值的語句，具體運行時間超過 long_query_time 值的SQL將會被記錄到慢查詢日志中。long_query_time的默認值為 10。

• 默認情況下，MySQL數據庫 沒有開啟慢查詢日志，需要手動設置參數。

• 不是調優需要一般不建議啟動該參數，因為開啟慢查詢日志或多或少會對性能造成一定影響。

• 查詢慢查詢日志是否開啟

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.01 sec)

• 開啟 slow_query_log

mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.12 sec)mysql> show variables like 'slow_query_log%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /var/lib/mysql/rqtanc-slow.log |
+---------------------+--------------------------------+
2 rows in set (0.00 sec)

• 查詢 long_query_time 閾值

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

• 修改long_query_time 閾值

[root@rqtanc ~]# vim /etc/my.cnf#設置
long_query_time = 5#重啟mysql
[root@rqtanc ~]# systemctl status mysqld.service

• 查看慢查詢數目

mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

4、慢查詢日志分析工具： mysqldumpslow

• 查看 mysqldumpslow 幫助信息

[root@rqtanc ~]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]Parse and summarize the MySQL slow query log. Options are--verbose    verbose--debug      debug--help       write this text to standard output-v           verbose-d           debug-s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is defaultal: average lock timear: average rows sentat: average query timec: countl: lock timer: rows sentt: query time  -r           reverse the sort order (largest last instead of first)-t NUM       just show the top n queries-a           don't abstract all numbers to N and strings to 'S'-n NUM       abstract numbers with at least n digits within names-g PATTERN   grep: only consider stmts that include this string-h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),default is '*', i.e. match all-i NAME      name of server instance (if using mysql.server startup script)-l           don't subtract lock time from total time

• 分析文件源為：

mysql> show variables like 'slow_query_log_file%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log_file | /var/lib/mysql/rqtanc-slow.log |
+---------------------+--------------------------------+
1 rows in set (0.00 sec)

• 執行以下語句進行分析

[root@rqtanc ~]# mysqldumpslow -a -s t -t 5 /var/lib/mysql/rqtanc-slow.logReading mysql slow query log from /var/lib/mysql/rqtanc-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hostsDied at /usr/bin/mysqldumpslow line 162, <> chunk 1.

5、查看SQL執行成本：show profile

• 見 MySQL-SQL執行流程及原理 一文

6、分析查詢語句：explain

參考 官方文檔

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

6.1、id列

• id 列的值標識了查詢執行中的每一步操作，并反映了這些步驟的執行順序和嵌套關系。
• id如果相同，可以認為是一組從上往下執行
• 在所有組中，id值越大，優先級越高，越先執行
• 每一個id值表示一趟獨立的查詢，一個SQL的查詢趟數越少越好

6.2、select_type 列

• 一個大的查詢語句中可以包含若干個select關鍵字，每個關鍵字代表一個小的查詢語句，而每個select關鍵字的from子句中可以包含若干張表
• 每一張表對應執行計劃輸出中的一條記錄，對于同一個select關鍵字中的表來說，他的id值是相同的

6.3、partitions列

• 出現位置：partitions 列通常出現在執行計劃的結果中，用于指示每個操作所涉及的分區信息。
• 分區名稱：對于涉及到分區的操作，partitions 列可能會顯示涉及的具體分區名稱。
• 分區范圍：對于涉及到范圍分區的操作，partitions 列可能會顯示涉及的分區范圍。
• 作用
  • 顯示分區信息：partitions 列提供了有關查詢涉及的分區的信息，包括查詢在執行過程中訪問了哪些分區。
  • 性能優化：分析查詢涉及的分區信息可以幫助優化查詢性能，例如確保查詢只訪問必要的分區，避免不必要的分區掃描。

6.4、type 列

• 總結
  • type 列提供了關于查詢執行時訪問表數據的方式的信息。
  • 分析 type 列的值可以幫助你了解查詢執行的效率，從而進行優化。
  • 應該盡量避免使用全表掃描（type 為 all），而更傾向于使用索引來加速查詢。

6.5、explain四種輸出格式 語法：EXPLAIN FORMAT= [ JSON | TREE | EXTENDED ]

• 傳統格式：表格形勢
• json格式：
  • 將查詢執行計劃輸出為 JSON 格式的數據。這種格式適用于對查詢執行計劃進行進一步的自動化處理和分析，例如通過腳本進行解析和比較。JSON 格式輸出了與標準格式相同的信息，但以 JSON 對象的形式表示，每個屬性對應于查詢執行計劃中的一個字段。
• tree格式：
  • 樹形格式提供了更具可讀性的查詢執行計劃信息。輸出結果以樹形結構呈現，每個節點代表查詢執行計劃中的一個操作。每個節點包含的信息通常與標準格式相同，但以樹形結構展示，更直觀地顯示了查詢執行的流程和嵌套關系。
• 拓展格式（EXTENDED）輸出：
  • 提供了比標準格式更詳細的查詢執行計劃信息。除了標準格式中的列外，還包括了額外的信息，如每個操作的狀態、掃描方式、索引長度等。這種格式適用于對查詢執行細節進行深入分析和調優，提供了更多的信息用于性能優化。

6.6、SHOW WARNINGS

• 用于顯示最近執行的語句產生的警告信息。警告通常是一些執行中的問題或不符合預期的情況的提示。
• SHOW WARNINGS 命令可以幫助你識別并了解這些問題，以便及時采取措施解決。
• 警告信息可能包含以下內容：
  • Warning：警告的代碼或編號。
  • Level：警告的級別，通常是 Note、Warning 或 Error。
  • Message：警告的具體消息描述

7、分析優化器執行計劃：trace

• optimizer_trace 可以跟蹤優化器做出得各種決策（如：訪問表的方法、各種開銷計算、各種轉換等，并將跟蹤結果記錄到 information_schema.optimizer_trace表中）
• 此功能默認關閉，開啟trace并將格式設置為json，同時設置trace最大能夠使用的內存大小，避免解析過程中因內存過小而不能夠完整展示
• 可分析的語句：
  • select
  • insert
  • update
  • delete
  • replace
  • explain
  • set
  • declare
  • case
  • if
  • return
  • call

8、MySQL監控分析視圖：sys schema

• 主機相關：以 host_summary開頭，主要匯總了IO延遲的信息
• InnoDB相關：以innodb開頭，匯總了innodb buffer信息和事務等待innodb鎖的信息
• I/O相關：以io開頭，匯總了等待I/O及使用量的情況
• 內存使用情況：以memory開頭，從主機、線程、事件等角度展示內存使用的情況
• 連接與會話信息: process list和session相關視圖，總結了會話相關情況
• 表相關：以schema_table開頭的視圖，展示了表的統計信息
• 索引信息：統計了索引的使用情況，包含冗余索引和未使用索引的情況
• 語句相關：以statement開頭，包含執行全表掃描，使用臨時表，排序等的語句信息
• 用戶相關：以user開頭的視圖，統計了用戶使用的文件I/O、執行語句統計信息
• 等待事件相關信息：以wait開頭，展示等待事件的延遲情況