數據庫的優化大概是在系統管理中最具有挑戰性的了，因為其對人員的素質要求幾乎是全方面的，好的 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.
