跳轉到內容

MySQL/最佳化

Edit links
來自華夏公益教科書，開放的書本，開放的世界

在開始最佳化之前

[編輯 | 編輯原始碼]

當資料庫似乎“很慢”時，首先考慮以下所有要點，例如，透過在應用程式中使用更復雜的演算法來完全消除某個查詢，始終是最佳化它的最優雅方法:)

  1. 找到瓶頸（CPU、記憶體、I/O、哪些查詢）
  2. 最佳化應用程式（刪除不必要的查詢或快取 PHP 生成的網頁）
  3. 最佳化查詢（使用索引、臨時表或不同的連線方式）
  4. 最佳化資料庫伺服器（快取大小等）
  5. 最佳化系統（不同的檔案系統型別、交換空間和核心版本）
  6. 最佳化硬體（有時確實是最便宜、最快的途徑）

為了找到這些瓶頸，以下工具被證明很有幫助

vmstat
快速監控 cpu、記憶體和 I/O 使用情況，並確定哪個是瓶頸
top
檢查 mysqld 和應用程式的當前記憶體和 cpu 使用情況
mytop
找出哪些查詢導致問題
mysql-admin（GUI 應用程式，不要與 mysqladmin 混淆）
以非常方便的方式監控和調整 mysql
mysqlreport
其輸出應作為一種逐步檢查清單使用

使用這些工具，大多數應用程式也可以使用以下幾組進行非常廣泛的分類

  • 基於 I/O 和讀取（部落格、新聞）
  • 基於 I/O 和寫入（網頁訪問跟蹤器、會計資料收集）
  • 基於 CPU（複雜的內容管理系統、業務應用程式）

最佳化表

[編輯 | 編輯原始碼]

定期使用以下命令重新組織磁碟空間，這將減小表大小，而不會刪除任何記錄[1]

OPTIMIZE TABLE MyTable1

此外，在建立表時，最好使用其最小型別。例如

  • 如果數字始終為正數，則選擇 unsigned 型別，以便能夠在相同數量的位元組中儲存兩倍多的資料。
  • 要儲存當代日期（從 1970 年到 2038 年），最好使用 4 個位元組的 timestamp，而不是 8 個位元組的 datetime。[2]

最佳化查詢

[編輯 | 編輯原始碼]

使用 BENCHMARK 比較函式

[編輯 | 編輯原始碼]

BENCHMARK() 函式可用於比較 MySQL 函式或運算子的速度。例如

mysql> SELECT BENCHMARK(100000000, CONCAT('a','b'));
+---------------------------------------+
| BENCHMARK(100000000, CONCAT('a','b')) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (21.30 sec)

但是，這不能用於比較查詢

mysql> SELECT BENCHMARK(100, SELECT `id` FROM `lines`);
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for
the right syntax to use near 'SELECT `id` FROM `lines`)' at line 1

由於 MySQL 只需要一小部分時間來解析查詢，並且系統可能也忙於執行其他操作，因此執行時間少於 5-10 秒的基準測試可以被認為是完全沒有意義的，並且在這種數量級的執行時間差異可以被認為是純粹的巧合。

使用 EXPLAIN 分析函式

[編輯 | 編輯原始碼]

當您在 SELECT 語句之前加上 EXPLAIN 關鍵字時，MySQL 會解釋它將如何處理 SELECT，提供有關如何連線表以及連線順序的資訊。這允許在函式中放置一些最終的 提示。

在追求良好效能時，使用和理解 EXPLAIN 至關重要，因此官方文件的相關章節是必讀的！

一個簡單的例子

[編輯 | 編輯原始碼]

兩個都沒有索引的表的連線

 mysql> explain SELECT * FROM a left join b using (i) WHERE a.i < 2;
 +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
 | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
 |  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
 |  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL |    3 |             |
 +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
 2 rows in set (0.01 sec)

現在第二個表獲得了索引，explain 顯示 MySQL 現在知道只需要使用 3 行中的 2 行。

 mysql> ALTER TABLE b ADD KEY(i);
 Query OK, 3 rows affected (0.01 sec)
 Records: 3  Duplicates: 0  Warnings: 0

 mysql> explain SELECT * FROM a left join b using (i) WHERE a.i < 2;
 +----+-------------+-------+------+---------------+------+---------+----------+------+-------------+
 | id | select_type | table | type | possible_keys | key  | key_len | ref      | rows | Extra       |
 +----+-------------+-------+------+---------------+------+---------+----------+------+-------------+
 |  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL     |    4 | Using where |
 |  1 | SIMPLE      | b     | ref  | i             | i    | 5       | test.a.i |    2 |             |
 +----+-------------+-------+------+---------------+------+---------+----------+------+-------------+
 2 rows in set (0.00 sec)

現在第一個表也獲得了索引，以便 WHERE 條件可以得到改善，MySQL 在嘗試在資料檔案中搜索之前就知道第一個表中只有一行是相關的。

 mysql> ALTER TABLE a ADD KEY(i);
 Query OK, 4 rows affected (0.00 sec)
 Records: 4  Duplicates: 0  Warnings: 0

 mysql> explain SELECT * FROM a left join b using (i) WHERE a.i < 2;
 +----+-------------+-------+-------+---------------+------+---------+----------+------+-------------+
 | id | select_type | table | type  | possible_keys | key  | key_len | ref      | rows | Extra       |
 +----+-------------+-------+-------+---------------+------+---------+----------+------+-------------+
 |  1 | SIMPLE      | a     | range | i             | i    | 5       | NULL     |    1 | Using where |
 |  1 | SIMPLE      | b     | ref   | i             | i    | 5       | test.a.i |    2 |             |
 +----+-------------+-------+-------+---------------+------+---------+----------+------+-------------+
 2 rows in set (0.02 sec)

最佳化 MySQL 伺服器

[編輯 | 編輯原始碼]

狀態和伺服器變數

[編輯 | 編輯原始碼]

MySQL 可以透過觀察 狀態變數 和設定 伺服器變數 來進行監控和調整，這些變數可以是全域性的，也可以是每個會話的。狀態變數可以透過 SHOW [GLOBAL|SESSION] STATUS [LIKE '%foo%'] 或 mysqladmin [extended-]status 來監控。伺服器變數可以在 /etc/mysql/my.cnf 檔案中設定，也可以透過 SET [GLOBAL|SESSION] VARIABLE foo := bar 設定，並可以透過 mysqladmin variables 或 SHOW [GLOBAL|SESSION] VARIABLES [LIKE '%foo%'] 顯示。

通常，狀態變數以大寫字母開頭，伺服器變數以小寫字母開頭。

在處理上述每個會話系統變數時，始終應考慮必須將它們乘以 max_connections 以估計最大的記憶體消耗。如果不能這樣做，當比平時更多的客戶端連線到伺服器時，很容易在負載高峰時導致伺服器崩潰！可以使用以下公式進行快速粗略的估計

   min_memory_needed = global_buffers + (thread_buffers * max_connections)

   global_buffers:
       key_buffer
       innodb_buffer_pool
       innodb_log_buffer
       innodb_additional_mem_pool
       net_buffer

   thread_buffers:
       sort_buffer
       myisam_sort_buffer
       read_buffer
       join_buffer
       read_rnd_buffer

注意：尤其是在處理伺服器設定時，所有資訊都應在官方文件的相關章節中進行驗證，因為這些資訊可能會發生變化，本文作者缺乏有關伺服器內部工作原理的確認知識。


索引

[編輯 | 編輯原始碼]

索引是一種更快地定位元素的方法。這適用於單個元素，也適用於元素範圍。

實驗

[編輯 | 編輯原始碼]

注意：當您進行時間測試時，請確保停用查詢快取（在 my.cnf 中設定 query_cache_type=0），以強制每次輸入查詢時重新計算，而不是從快取中獲取預先計算的結果。


讓我們執行以下 Perl 程式

#!/usr/bin/perl

use strict;

print "DROP TABLE IF EXISTS weightin;\n";
print "CREATE TABLE weightin (
	id INT PRIMARY KEY auto_increment,
	line TINYINT,
	date DATETIME,
	weight FLOAT(8,3)
);\n";


# 2 millions records, interval = 100s
for (my $timestamp = 1000000000; $timestamp < 1200000000; $timestamp += 100) {
    my $date = int($timestamp + rand(1000) - 500);
    my $weight = rand(1000);
    my $line = int(rand(3)) + 1;
    print "INSERT INTO weightin (date, line, weight) VALUES (FROM_UNIXTIME($date), $line, $weight);\n";
}

它做了什麼？它模擬了工業生產線定期稱重物體的資料流，以便我們可以計算平均材料使用量。隨著時間的推移，大量記錄會堆積起來。

如何使用它？

 mysql> CREATE DATABASE industrial
 $ perl generate_huge_db.pl | mysql industrial
 real	6m21.042s
 user	0m37.282s
 sys	0m51.467s

我們可以用以下命令檢查元素的數量

 mysql> SELECT COUNT(*) FROM weightin;
 +----------+
 | count(*) |
 +----------+
 |  2000000 | 
 +----------+
 1 row in set (0.00 sec)

大小必須很重要

 $ perl generate_huge_db.pl > import.sql
 $ ls -lh import.sql
 -rw-r--r-- 1 root root 189M jun 15 22:08 import.sql
 
 $ ls -lh /var/lib/mysql/industrial/weightin.MYD
 -rw-rw---- 1 mysql mysql 35M jun 15 22:17 /var/lib/mysql/industrial/weightin.MYD
 
 $ time mysqldump industrial > dump.sql 
 real	0m9.599s
 user	0m3.792s
 sys	0m0.616s
 $ ls -lh dump.sql
 -rw-r--r-- 1 root root 79M jun 15 22:18 dump.sql
 
 $ time mysqldump industrial | gzip > dump.sql.gz 
 real	0m17.339s
 user	0m11.897s
 sys	0m0.488s
 $ ls -lh dump.sql.gz  
 -rw-r--r-- 1 root root 22M jun 15 22:19 dump.sql.gz

順便說一下，從轉儲中恢復速度要快得多，因為它使用的是擴充套件插入！

 # time zcat dump.sql.gz | mysql industrial
 real	0m31.772s
 user	0m3.436s
 sys	0m0.580s

此 SQL 命令將掃描所有記錄以獲取總和

 mysql> SELECT SUM(*) FROM weightin;

假設我們需要計算 2008 年 1 月 1 日使用的總材料

 mysql> SELECT COUNT(*), SUM(poids) FROM pesee WHERE date >= '2008-01-01' AND date < '2008-01-02';

即使對於這少量的記錄，MySQL 也需要瀏覽整個資料庫。這是因為記錄可能在任何地方：底部、結尾、中間，沒有任何保證記錄是有序的。

為了改進這一點，我們可以為 “date” 欄位新增索引。這意味著 MySQL 將建立一個新的隱藏表格，其中所有日期按時間順序排序，並存儲其偏移量（位置）在 “weightin” 表中，以便檢索完整的記錄。

由於索引已排序，因此 MySQL 查詢單個記錄（使用二進位制搜尋演算法）甚至資料範圍（查詢第一個和最後一個元素，範圍在兩者之間）要快得多。

新增索引

 ALTER TABLE weightin ADD INDEX (date);

如果查詢需要在欄位上進行計算（例如 TIME(date)），則索引不起作用，但對於範圍（例如 WHERE date < '2008-01-02'）有效。

您會注意到 .MYD 檔案的大小增加了

 $ ls -lh /var/lib/mysql/industrial/
 -rw-rw---- 1 mysql mysql  49M jun 15 22:36 weightin.MYI

這是 MySQL 儲存索引的地方。最初有一個 “id” 欄位的索引，這是所有主鍵的情況。


另一個例子

[編輯 | 編輯原始碼]

另一個例子：假設我們想最佳化這個查詢

 mysql> SELECT DISTINCT line FROM weightin;

我們可以透過在 “line” 欄位上新增索引來實現，以便將重複項分組在一起，從而避免查詢重新掃描整個表格來定位它們。

 ALTER TABLE weightin ADD INDEX (line);

索引檔案的大小增加了

 -rw-rw---- 1 mysql mysql  65M jun 15 22:38 weightin.MYI

一般注意事項

[編輯 | 編輯原始碼]

對於 SELECT 查詢，始終被問到的第一個也是最重要的一個問題是，索引（也稱為“鍵”）是否已配置，以及如果已配置，資料庫伺服器是否實際使用它們。


1. 檢查索引是否實際被使用

可以使用 “EXPLAIN” 命令檢查單個查詢。對於整個伺服器，應監控 “Sort_%” 變數，因為它們指示 MySQL 多久需要瀏覽整個資料檔案，因為沒有可用的索引。

2. 索引是否已緩衝

將索引儲存在記憶體中可以大大提高讀取效能。 “Key_reads / Key_read_requests” 的商表示 MySQL 在需要鍵時實際訪問磁碟上的索引檔案的頻率。Key_writes 也是如此，使用 mysqlreport 為您執行此處的計算。如果百分比過高，MyISAM 的 key_buffer_size 和 InnoDB 的 innodb_buffer_pool_size 是要調整的相應變數。

Key_blocks_% 變數可用於檢視實際使用了配置的鍵緩衝區中的多少。單位為 1KB，除非在 key_cache_block_size 中另有設定。由於 MySQL 在內部使用了一些塊，因此必須檢查 key_blocks_unused。要估計緩衝區的大小，可以將相關的 .MYI 檔案的大小加起來。對於 InnoDB，有 innodb_buffer_pool_size，儘管在這種情況下，不僅索引會被緩衝，資料也會被緩衝。

3. 其他設定

sort_buffer_size（每個執行緒）是用於 ORDER BY 和 GROUP BY 的記憶體。myisam_sort_buffer_size 是完全不同的東西，不應修改。

read_buffer_size（每個執行緒）是在進行全表掃描時一次從磁碟讀取到記憶體的記憶體塊的大小，因為大型表格無法完全放入記憶體。這很少需要調整。

查詢快取

[編輯 | 編輯原始碼]

如果您有基於讀取的應用程式，那麼不使用 4.0.1 以下的任何 MySQL 版本的主要原因是，從該版本開始，MySQL 能夠儲存 SELECT 查詢的結果，直到其表格被修改。

查詢快取可以使用 query_cache_% 變數進行配置。這裡最重要的全域性變數是 query_cache_size 和 query_cache_limit，它們可以防止結果異常大的單個查詢（大於此大小）佔用整個快取。

請注意，查詢快取塊的大小可變，最小大小為 query_cache_min_res_unit，因此在完全重新整理快取後，空閒塊的數量理想情況下只有一個。Qcache_free_blocks 的值較大僅表示碎片率高。

值得監控以下變數

  • Qcache_free_blocks
如果此值很高，則表示碎片率很高，儘管這並不一定是一件壞事。
  • Qcache_not_cached
如果此值很高，則表示有太多不可快取的查詢（例如，因為它們使用像 now() 這樣的函式），或者 query_cache_limit 的值太低。
  • Qcache_lowmem_prunes
這是由於快取已滿而不是因為它們的底層表格已被修改而被清除的舊結果的數量。必須增加 query_cache_size 以降低此變數的值。

示例

空快取

mysql> SHOW VARIABLES LIKE 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0.00 sec)

已使用的快取（savannah.gnu.org）

mysql> SHOW VARIABLES LIKE "query_cache_size";
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 33554432 |
+------------------+----------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE "Qcache%";
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1409     |
| Qcache_free_memory      | 27629552 |
| Qcache_hits             | 7925191  |
| Qcache_inserts          | 3400435  |
| Qcache_lowmem_prunes    | 2946778  |
| Qcache_not_cached       | 71255    |
| Qcache_queries_in_cache | 4546     |
| Qcache_total_blocks     | 10575    |
+-------------------------+----------+
8 rows in set (0.00 sec)

匹配的 my.cnf 配置引數為

query_cache_size = 32M

清除快取（在測試新查詢的效率時很有用）

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

等待鎖

[編輯 | 編輯原始碼]

Table_locks_% 變數顯示必須等待的查詢數量，因為它們嘗試訪問的表格當前被其他查詢鎖定。這些情況可能是由於 “LOCK TABLE” 語句以及例如對同一表格的同步寫入訪問造成的。


表格快取

[編輯 | 編輯原始碼]

MySQL 需要一定的時間才能“開啟”表格並讀取其元資料，例如列名等。

如果許多執行緒試圖訪問同一個表格，它會被多次開啟。

為了加快速度，元資料可以在 table_cache（自 MySQL 5.1.3 以來也稱為 table_open_cache）中快取。

此設定的理想值是 max_connections 的數量乘以每個 SELECT 通常使用的表格數量。

使用 mysqlreport 或檢視當前 Open_tables 和自 Opened_tables 以及 Uptime，可以計算出每秒所需的表格開啟次數（但要考慮夜間等非高峰時段）。

連線和執行緒

[編輯 | 編輯原始碼]

對於每個客戶端連線（也稱為會話），MySQL 在主 mysqld 程序下建立一個獨立的執行緒。對於每秒有數百個新連線的大型網站，建立執行緒本身可能會消耗大量時間。為了加快速度，空閒執行緒可以在其客戶端斷開連線後被快取。經驗法則是，每秒不應新建超過一個執行緒。向伺服器傳送多個查詢的客戶端應使用 持久連線，例如使用 PHPs mysql_pconnect() 函式。

此快取可以使用 thread_cache_size 進行配置，並使用 threads_% 變數進行監控。

為了避免超載，如果當前使用的連線數超過 max_connections，則 MySQL 會阻止新的連線。從 max_used_connections 開始，並監控 Aborted_clients 中被拒絕的連線數量，以及 Aborted_connections 中超時連線的數量。使用持久連線的客戶端忘記斷開連線很容易導致拒絕服務，因此請注意！通常，連線在空閒 wait_timeout 秒後關閉。


臨時表格

[編輯 | 編輯原始碼]

MySQL 在排序或分組結果時建立臨時表格是完全正常的。這些表格要麼儲存在記憶體中，要麼太大而寫入磁碟，這自然要慢得多。Created_tmp_% 變數中的磁碟表格數量應可忽略不計，否則應重新考慮 max_heap_table_size 和 tmp_table_size 中的設定。

延遲寫入

[編輯 | 編輯原始碼]

在將 Web 伺服器訪問日誌檔案寫入資料庫等情況下，對於同一表格中的許多後續 INSERT 查詢（用於不太重要的資料），可以透過建議伺服器暫時快取寫入請求，然後將一批資料傳送到磁碟來提高效能。

但請注意，所有提到的方法都與 ACID 相容性相沖突，因為 INSERT 查詢在資料實際寫入磁碟之前向客戶端確認 OK，因此在斷電或伺服器崩潰的情況下仍可能丟失資料。此外，文件中提到的副作用通常讀起來就像現代藥物的病人資訊手冊……

使用 `CREATE` 或 `ALTER TABLE` 語句，可以為 MyISAM 表設定 **DELAY_KEY_WRITE** 選項。缺點是，在崩潰後，表會自動標記為損壞，需要檢查/修復，這可能需要一些時間。

InnoDB 可以使用 **innodb_flush_log_at_trx_commit** 選項來延遲資料寫入。如果伺服器崩潰，資料本身應該是保持一致的，只需要重建索引。

**INSERT DELAYED** 在每個查詢的基礎上對主要的儲存引擎生效。

進一步閱讀

[編輯 | 編輯原始碼]

關於 MySQL 伺服器最佳化的有用連結

參考資料

[編輯 | 編輯原始碼]
  1. ↑ https://dev.mysql.com.tw/doc/refman/5.1/en/optimize-table.html
  2. ↑ https://dev.mysql.com.tw/doc/refman/5.1/en/datetime.html
檢索自 "https://wikibook.tw/w/index.php?title=MySQL/Optimization&oldid=4419106"
華夏公益教科書