對(duì)MySQL性能優(yōu)化的簡(jiǎn)單辦法是怎樣的

對(duì)MySQL性能優(yōu)化的簡(jiǎn)單辦法是怎樣的，針對(duì)這個(gè)問題，這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答，希望可以幫助更多想解決這個(gè)問題的小伙伴找到更簡(jiǎn)單易行的方法。

對(duì)擁有一個(gè)幾十萬行表的 MySQL 性能優(yōu)化的簡(jiǎn)單辦法(轉(zhuǎn))[@more@]數(shù)據(jù)庫的優(yōu)化大概是在系統(tǒng)管理中最具有挑戰(zhàn)性的了，因?yàn)槠鋵?duì)人員的素質(zhì)要求幾乎是全方面的，好的 DBA 需要各種綜合素質(zhì)。在排除了操作系統(tǒng)，應(yīng)用等引起的性能問題以外，優(yōu)化數(shù)據(jù)庫最核心的實(shí)際上就是配置參數(shù)的調(diào)整。本文通過一個(gè)簡(jiǎn)單的參數(shù)調(diào)整，實(shí)現(xiàn)了對(duì)擁有一個(gè)幾十萬行表的 group by 優(yōu)化的例子。通過這個(gè)簡(jiǎn)單的調(diào)整，數(shù)據(jù)庫性能有了突飛猛進(jìn)的提升。
本例子是針對(duì) MySQL 調(diào)整的，不像其他商業(yè)數(shù)據(jù)庫，MySQL 沒有視圖，特別是 Oracle 可以利用固化視圖來提升查詢性能，沒有存儲(chǔ)過程，因此性能的調(diào)整幾乎只能通過配置合適的參數(shù)來實(shí)現(xiàn)。

調(diào)整的具體步驟(例子針對(duì) pLog 0.3x 的博客系統(tǒng))：

發(fā)現(xiàn)最多的 slow log 是：
SELECT category_id, COUNT(*) AS 'count' FROM plog_articles WHERE blog_id = 2 AND status = 'PUBLISHED' group by category_id;
一般在 20s 以上，甚至 30s 。
而當(dāng) blog_id=1 或者其他時(shí)，都能很快的選出結(jié)果。
于是懷疑索引有問題，重新建立索引，但無濟(jì)于事。 EXPLAIN 結(jié)果如下：
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 的博客時(shí)，系統(tǒng)負(fù)載就提高，有較高的 swap 。于是查看 temporary table 有關(guān)的資料，果然有這樣的說法：

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:

調(diào)整 tmp_table_size為 80M 左右后，以上語句 14s 即可解決。

這個(gè)參數(shù)是 DBA 很容易忽視的。

其實(shí)，不單單是數(shù)據(jù)庫，就是操作系統(tǒng)，也是受 tmp 的影響巨大，例如安裝軟件到 d: 盤，如果 TMP 環(huán)境變量指向 c: 盤，而 c: 空間不夠，照樣可能導(dǎo)致安裝失敗。

因此讓 TMP 有足夠的空間可以說是計(jì)算機(jī)系統(tǒng)里一個(gè)普遍適用的原則(寫程序也是一樣)。

關(guān)于對(duì)MySQL性能優(yōu)化的簡(jiǎn)單辦法是怎樣的問題的解答就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識(shí)。