對擁有一個幾十萬行表的 MySQL 效能最佳化的簡單辦法(轉)

對擁有一個幾十萬行表的 MySQL 效能最佳化的簡單辦法(轉)[@more@]資料庫的最佳化大概是在系統管理中最具有挑戰性的了，因為其對人員的素質要求幾乎是全方面的，好的 DBA 需要各種綜合素質。在排除了作業系統，應用等引起的效能問題以外，最佳化資料庫最核心的實際上就是配置引數的調整。本文透過一個簡單的引數調整，實現了對擁有一個幾十萬行表的 group by 最佳化的例子。透過這個簡單的調整，資料庫效能有了突飛猛進的提升。
本例子是針對 MySQL 調整的，不像其他商業資料庫，MySQL 沒有檢視，特別是 Oracle 可以利用固化檢視來提升查詢效能，沒有儲存過程，因此效能的調整幾乎只能透過配置合適的引數來實現。

調整的具體步驟(例子針對 pLog 0.3x 的部落格系統)：

發現最多的 slow log 是：
SELECT category_id, COUNT(*) AS 'count' FROM plog_articles WHERE blog_id = 2 AND status = 'PUBLISHED' group by category_id;
一般在 20s 以上，甚至 30s 。
而當 blog_id=1 或者其他時，都能很快的選出結果。
於是懷疑索引有問題，重新建立索引，但無濟於事。 EXPLAIN 結果如下：
mysql> EXPLAIN SELECT category_id, COUNT(*) AS 'count' FROM plog_articles WHERE blog_id = 2 AND status = 'PUBLISHED' group by category_id;
+---------------+------+------------------+------------------+---------+-------------+------+----------------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------------+------+------------------+------------------+---------+-------------+------+----------------------------------------------+
| plog_articles | ref | idx_article_blog | idx_article_blog | 5 | const,const | 4064 | Using where; Using temporary; Using filesort |
+---------------+------+------------------+------------------+---------+-------------+------+----------------------------------------------+
1 row in set (0.00 sec)

於是想到每次檢視 blog_id = 2 的部落格時，系統負載就提高，有較高的 swap 。於是檢視 temporary table 有關的資料，果然有這樣的說法：

If you create a lot of disk-based temporary tables, increase the size of tmp_table_size if you can do so safely. Keep in mind that setting the value too high may result in excessive swapping or MySQL running out of memory if too many threads attempt to allocate in-memory temporary tables at the same time. Otherwise, make sure that tmpdir points to a very fast disk that's not already doing lots of I/O.
Another problem that doesn't show up in the slow query log is an excessive use of disk-based temporary tables. In the output of EXPLAIN, you'll often see Using temporary. It indicates that MySQL must create a temporary table to complete the query. However, it doesn't tell you whether that temporary table will be in memory or on disk. That's controlled by the size of the table and MySQL's tmp_table_size variable.
If the space required to build the temporary table is less than or equal to tmp_table_size, MySQL keeps it in memory rather than incur the overhead and time required to write the data to disk and read it again. However, if the space required exceeds tmp_table_size, MySQL creates a disk-based table in its tmpdir directory (often /tmp on Unix systems.) The default tmp_table_size size is 32 MB.
To find out how often that happens, compare the relative sizes of the Created_tmp_tables and Created_tmp_disk_tables counters:

調整 tmp_table_size為 80M 左右後，以上語句 14s 即可解決。

這個引數是 DBA 很容易忽視的。

其實，不單單是資料庫，就是作業系統，也是受 tmp 的影響巨大，例如安裝軟體到 d: 盤，如果 TMP 環境變數指向 c: 盤，而 c: 空間不夠，照樣可能導致安裝失敗。

因此讓 TMP 有足夠的空間可以說是計算機系統裡一個普遍適用的原則(寫程式也是一樣)。