Mysql Slow Query

MySQL slow query optimize table rebuild indx alter table explain index 慢查詢重建索引

MySQL慢查詢是指執行時間超過預設閾值的查詢語句，可能會影響數據庫性能和響應時間。

判斷是否存在慢查

在MySQL中，可以通過以下方式來判斷是否存在慢查詢：

1. 慢查詢日誌（Slow Query Log）：

MySQL提供了慢查詢日誌功能，記錄執行時間超過指定閾值的查詢語句。通過啟用慢查詢日誌，並設置合適的閾值，可以捕捉到執行時間較長的查詢語句。你可以查看慢查詢日誌文件，找出執行時間較長的查詢語句，並進一步分析優化。

以下是在MySQL中查找慢查詢日誌的方法：

確認慢查詢日誌是否已啟用：在MySQL配置文件中，通常是my.cnf或my.ini文件中，查找以下配置項：

slow_query_log = 1
slow_query_log_file = /path/to/slow-query.log

如果slow_query_log的值為1，並且指定了slow_query_log_file的路徑，則慢查詢日誌已啟用。

使用MySQL命令行客戶端查看慢查詢日誌的路徑：

SHOW VARIABLES LIKE 'slow_query_log_file';

執行以上命令後，會顯示慢查詢日誌文件的路徑。

使用Linux命令行查找慢查詢日誌文件：如果你有SSH或終端訪問到MySQL服務器的操作系統，可以使用ls命令查找慢查詢日誌文件。根據上一步的查詢結果，執行以下命令：

ls /path/to/slow-query.log

以上命令將顯示慢查詢日誌文件的位置和文件名。

請注意，如果慢查詢日誌未啟用或未配置，或者沒有適當的權限，你可能無法查找或訪問慢查詢日誌文件。

另外，在MySQL命令行客戶端中，你也可以使用以下命令查看慢查詢日誌的內容：

SHOW GLOBAL VARIABLES LIKE 'slow_query_log';

這將顯示慢查詢日誌是否啟用。

SHOW GLOBAL VARIABLES LIKE 'long_query_time';

這將顯示慢查詢的閾值，表示執行時間超過該值的查詢將被記錄在慢查詢日誌中。

每條慢查詢日誌記錄通常包含以下信息：

Timestamp（時間戳）：記錄查詢執行的時間點。
Query_time（查詢時間）：記錄查詢執行的時間，以秒為單位。
Lock_time（鎖定時間）：如果查詢涉及到鎖定操作，記錄查詢鎖定的時間，以秒為單位。
Rows_examined（掃描行數）：記錄查詢涉及的掃描行數，表示查詢執行過程中訪問的行數。
Rows_sent（返回行數）：記錄查詢返回的行數，表示查詢結果集中的行數。
SQL_text（SQL語句）：記錄執行的查詢語句本身。

一個典型的慢查詢日誌記錄可能如下所示：

# Time: 2022-01-01T10:00:00.000000Z
# Query_time: 2.346789 Lock_time: 0.123456 Rows_examined: 100000 Rows_sent: 10
SET timestamp=1641015600;
SELECT * FROM `users` WHERE `age` > 30;

以上日誌記錄顯示了執行時間為2.346789秒的查詢語句，查詢涉及了100,000行的掃描，返回了10行的結果集。查詢語句是SELECT * FROM users WHERE age > 30;。

具體的慢查詢日誌格式和內容可以通過配置文件（如my.cnf或my.ini）中的slow_query_log_format參數進行自定義。不同的MySQL版本和配置可能會有些許差異。

如何定期清理慢查詢

設置日誌輪轉：在MySQL配置文件（如my.cnf或my.ini）中，配置慢查詢日誌的輪轉規則。通過設置log_rotate參數，可以指定日誌的最大大小或最長保留時間。一旦達到指定條件，系統將自動進行日誌輪轉，將當前日誌文件重命名或壓縮，並創建一個新的空日誌文件。
配置日誌保留週期：根據需求，設定慢查詢日誌的保留週期。可以通過配置expire_logs_days參數來指定日誌文件保留的天數。超過指定天數的日誌文件將被自動刪除。
手動清理日誌：如果需要手動清理慢查詢日誌，可以按照以下步驟進行操作：
1. 停止MySQL服務：確保在清理過程中數據庫處於停止狀態，以防止日誌文件被鎖定或正在寫入。
2. 備份日誌文件：在清理之前，最好先備份慢查詢日誌文件，以防止意外數據丟失。
3. 刪除或移動舊日誌文件：使用操作系統的命令或文件管理工具，刪除或移動不再需要的舊日誌文件。可以按照日期或其他自定義規則進行篩选和刪除。
自動化清理腳本：為了簡化清理過程，可以編寫一個自動化腳本，定期執行慢查詢日誌的清理操作。腳本可以根據預設的規則，自動刪除舊的慢查詢日誌文件，確保系統中保留的日誌文件不會無限增長。

2. EXPLAIN命令：

使用EXPLAIN命令可以分析查詢語句的執行計劃，了解MySQL優化器如何處理查詢，並預估查詢語句的性能。通過檢查查詢的執行計劃，可以發現潛在的性能問題，例如是否使用了合適的索引、是否存在全表掃描等。

3. SHOW PROCESSLIST命令：

通過SHOW PROCESSLIST命令可以查看當前正在執行的查詢語句和連接信息。你可以觀察是否有長時間運行的查詢，以及它們的狀態和執行時間。這可以幫助你找出可能的慢查詢，並進行進一步分析和優化。

數據庫性能分析工具：使用第三方的數據庫性能分析工具，如pt-query-digest、Percona Toolkit等，可以幫助你識別慢查詢、分析查詢性能、查找潛在的性能瓶頸等。

當發現慢查詢時，可以考慮以下優化措施

1. 優化查詢語句

檢查查詢語句的寫法和結構，考慮是否可以進行優化，如避免不必要的JOIN操作、減少數據掃描、避免不必要的子查詢或使用適當的連接類型等。

2. 創建合適的索引

通過分析查詢語句和執行計劃，確定是否可以創建或優化索引來加快查詢速度。可以通過使用EXPLAIN命令來檢查查詢執行計劃，判斷是否正確使用了索引

3. 避免全表掃描

全表掃描是指沒有使用索引，而是對整個表進行遍歷的查詢方式。如果發現有全表掃描的情況，可以考慮添加適當的索引或重新設計查詢語句。

4. 適當的數據類型和字段寬度

選擇適當的數據類型和字段寬度可以減少磁盤和內存的使用，並提高查詢性能。

5. 定期維護和優化

定期執行數據庫維護任務，如索引重建、碎片整理、收集統計信息和優化表等，以保持數據庫的健康狀態和性能穩定。

6. 數據庫分片和分區

如果數據量非常大，可以考慮使用數據庫分片或分區技術來水平拆分數據，提高查詢效率。

7. 調整數據庫參數

根據系統的配置和需求，適當調整MySQL的參數設置，如緩衝區大小、連接數限制等。

MySQL 索引重建

MySQL 索引重建是指重新構建表的索引結構，以優化索引性能和減少索引空間佔用的過程。索引重建可以解決索引碎片化的問題，提高查詢性能和整體數據庫性能。

索引在數據庫中用於加速數據檢索操作，但在數據不斷插入、更新和刪除的過程中，索引會發生碎片化。碎片化的索引會導致索引樹的不連續性，降低查詢性能，並佔用過多的磁盤空間。

通過執行索引重建操作，可以重新組織和優化索引結構，使其更加連續有序，提高查詢性能。索引重建的過程會創建一個新的索引結構，然後將數據從舊索引結構複製到新的索引結構中，最後替換舊索引。

索引重建的步驟包括：

創建新的索引結構：根據表的索引定義，創建一個新的、空的索引結構，用於存儲重建後的索引數據。
複製數據：按照索引順序，逐條從舊索引結構中讀取數據，並插入到新的索引結構中。
索引切換：當新索引結構中的數據複製完成後，將新索引結構切換為活動索引，替換舊索引結構。

需要注意的是，索引重建可能會消耗較長的時間和資源，並且可能會對數據庫的讀寫操作產生一定的影響。因此，在執行索引重建之前，建議在低負載時段或離線環境中進行，以避免對生產環境的影響。

1. DROP INDEX + RECREATE INDEX.

就...是刪除索引，然後重新創建索引。

-- 刪除索引
ALTER TABLE table_name DROP INDEX index_name;

-- 創建索引
ALTER TABLE table_name ADD INDEX index_name (column_name);

2. ALTER TABLE 方法

ALTER TABLE 語句：使用 ALTER TABLE 語句可以對錶進行結構修改，包括創建、修改和刪除索引。通過添加、刪除或重新定義索引，你可以重建索引結構。例如，使用 ALTER TABLE DROP INDEX 和 ALTER TABLE ADD INDEX 命令來刪除和重新添加索引。

show indexes from tablename不會顯示索引創建時間

ALTER TABLE table_name ENGINE=InnoDB;

上述語句會重建指定表的索引，其中 table_name 是要進行索引重建的表名。 ENGINE=InnoDB 表示使用 InnoDB 存儲引擎，你可以根據實際情況選擇適當的存儲引擎。其實ALTER TABLE xxx ENGINE=InnoDB 其實等價於REBUILD表（REBUILD表就是重建表的意思），所以索引也等價於重新創建了。

在另外一個窗口，我們對比t1.ibd的創建時間，如下所示，也間接驗證了表和索引都REBUILD了。（這裡是MySQL 8.0.18 ，如果是之前的版本，還有frm之類的文件。）

[root@db-server MyDB]# ls -lrt t1*
-rw-r-----. 1 mysql mysql 131072 Oct 20 08:18 t1.ibd

[root@db-server MyDB]# stat t1.ibd
  File: ‘t1.ibd’
  Size: 131072          Blocks: 224        IO Block: 4096   regular file
Device: fd00h/64768d    Inode: 106665154   Links: 1
Access: (0640/-rw-r-----)  Uid: ( 1000/   mysql)   Gid: ( 1000/   mysql)
Context: system_u:object_r:mysqld_db_t:s0
Access: 2019-10-20 08:18:25.911990445 +0800
Modify: 2019-10-20 08:18:33.626989940 +0800
Change: 2019-10-20 08:18:33.626989940 +0800
 Birth: -
[root@db-server MyDB]# stat t1.ibd
  File: ‘t1.ibd’
  Size: 131072          Blocks: 224        IO Block: 4096   regular file
Device: fd00h/64768d    Inode: 106665156   Links: 1
Access: (0640/-rw-r-----)  Uid: ( 1000/   mysql)   Gid: ( 1000/   mysql)
Context: system_u:object_r:mysqld_db_t:s0
Access: 2019-10-20 08:20:50.866980953 +0800
Modify: 2019-10-20 08:20:51.744980896 +0800
Change: 2019-10-20 08:20:51.744980896 +0800
 Birth: -

3. REPAIR TABLE 方法,這種方法對於InnoDB存儲引擎的表無效。

REPAIR TABLE方法用於修復被破壞的表，而且它僅僅能用於MyISAM, ARCHIVE,CSV類型的表

mysql> CREATE TABLE t (
    ->     c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     c2 VARCHAR(100),
    ->     c3 VARCHAR(100) )
    -> ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT table_name,create_time FROM  information_schema.TABLES WHERE table_name='t';
+------------+---------------------+
| table_name | create_time         |
+------------+---------------------+
| t          | 2019-10-20 08:35:43 |
+------------+---------------------+
1 row in set (0.00 sec)

然後對錶t進行修復操作，發現表的create_time沒有變化，如下所示：

mysql> REPAIR TABLE t;
+--------+--------+----------+----------+
| Table  | Op     | Msg_type | Msg_text |
+--------+--------+----------+----------+
| MyDB.t | repair | status   | OK       |
+--------+--------+----------+----------+
1 row in set (0.01 sec)

mysql> SELECT table_name,create_time FROM  information_schema.TABLES WHERE table_name='t';
+------------+---------------------+
| table_name | create_time         |
+------------+---------------------+
| t          | 2019-10-20 08:35:43 |
+------------+---------------------+
1 row in set (0.00 sec)

在另外一個窗口，我們發現索引文件t.MYI的修改時間和狀態更改時間都變化了，所以判斷索引重建（Index Rebuild）了

[root@db-server MyDB]# ls -lrt t.*
-rw-rw----. 1 mysql mysql 8608 Oct 20 08:35 t.frm
-rw-rw----. 1 mysql mysql 1024 Oct 20 08:35 t.MYI
-rw-rw----. 1 mysql mysql    0 Oct 20 08:35 t.MYD

[root@db-server MyDB]# stat t.MYI
  File: `t.MYI'
  Size: 1024            Blocks: 8          IO Block: 4096   regular file
Device: fd00h/64768d    Inode: 1836747     Links: 1
Access: (0660/-rw-rw----)  Uid: (   27/   mysql)   Gid: (   27/   mysql)
Access: 2019-10-20 08:36:02.395428301 +0800
Modify: 2019-10-20 08:35:43.112562600 +0800
Change: 2019-10-20 08:35:43.112562600 +0800

[root@db-server MyDB]# stat t.MYI
  File: `t.MYI'
  Size: 1024            Blocks: 8          IO Block: 4096   regular file
Device: fd00h/64768d    Inode: 1836747     Links: 1
Access: (0660/-rw-rw----)  Uid: (   27/   mysql)   Gid: (   27/   mysql)
Access: 2019-10-20 08:37:19.686899429 +0800
Modify: 2019-10-20 08:37:10.271475420 +0800
Change: 2019-10-20 08:37:10.271475420 +0800

4. OPTIMIZE TABLE 方法

OPTIMIZE TABLE也可以對索引進行重建，官方文檔的介紹如下： https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html

OPTIMIZE TABLE reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depend on the storage engine used by that table.

OPTIMIZE TABLE uses online DDL for regular and partitioned InnoDB tables, which reduces downtime for concurrent DML operations. The table rebuild triggered by OPTIMIZE TABLE is completed in place. An exclusive table lock is only taken briefly during the prepare phase and the commit phase of the operation. During the prepare phase, metadata is updated and an intermediate table is created. During the commit phase, table metadata changes are committed.

OPTIMIZE TABLE rebuilds the table using the table copy method under the following conditions:

When the old_alter_table system variable is enabled.
When the server is started with the --skip-new option.

OPTIMIZE TABLE using online DDL is not supported for InnoDB tables that contain FULLTEXT indexes. The table copy method is used instead.

簡單來說，OPTIMIZE TABLE操作使用Online DDL模式修改Innodb普通表和分區表，該方式會在prepare階段和commit階段持有表級鎖：在prepare階段修改表的元數據並且創建一個中間表，在commit階段提交元數據的修改。由於prepare階段和commit階段在整個事務中的時間比例非常小，可以認為該OPTIMIZE TABLE的過程中不影響表的其他並發操作。

測試驗證如下，對錶t1做了OPTIMIZE TABLE後，表的創建時間變成了2019-10-20 08:41:57

mysql> OPTIMIZE TABLE t1;
+---------+----------+----------+-------------------------------------------------------------------+
| Table   | Op       | Msg_type | Msg_text                                                          |
+---------+----------+----------+-------------------------------------------------------------------+
| MyDB.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| MyDB.t1 | optimize | status   | OK                                                                |
+---------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.67 sec)

mysql> SELECT table_name,create_time FROM  information_schema.TABLES WHERE table_name='t1';
+------------+---------------------+
| TABLE_NAME | CREATE_TIME         |
+------------+---------------------+
| t1         | 2019-10-20 08:41:57 |
+------------+---------------------+
1 row in set (0.00 sec)

ANALYZE TABLE 會重建索引嗎?

不准確，ANALYZE TABLE 語句不會重建索引。 它的作用是分析表的索引和統計信息，並更新 MySQL 查詢優化器的統計信息，以幫助優化查詢執行計劃。 ANALYZE TABLE 語句執行時，會掃描表中的數據，併計算索引的基數（distinct values）和平均長度等統計信息。這些統計信息可以幫助 MySQL 查詢優化器做出更準確的查詢執行計劃，以提高查詢性能。雖然 ANALYZE TABLE 不會直接重建索引，但它可以間接地影響索引性能。通過更新統計信息，MySQL 查詢優化器可以更好地估計索引的選擇性和數據分佈，從而生成更好的查詢計劃，提高查詢性能。

Mysql Slow Query 慢查詢