MYSQL 應(yīng)該是最流行了 WEB 后端數(shù)據(jù)庫(kù)。WEB 開(kāi)發(fā)語(yǔ)言最近發(fā)展很快，PHP， Ruby, Python, Java 各有特點(diǎn)，雖然 NOSQL 最近越來(lái)越多的被提到，但是相信大部分架構(gòu)師還是會(huì)選擇 MYSQL 來(lái)做數(shù)據(jù)存儲(chǔ)。

MYSQL 如此方便和穩(wěn)定，以至于我們?cè)陂_(kāi)發(fā) WEB 程序的時(shí)候很少想到它。即使想到優(yōu)化也是程序級(jí)別的，比如，不要寫過(guò)于消耗資源的 SQL 語(yǔ)句。但是除此之外，在整個(gè)系統(tǒng)上仍然有很多可以優(yōu)化的地方。

1. 選擇合適的存儲(chǔ)引擎: InnoDB

除非你的數(shù)據(jù)表使用來(lái)做只讀或者全文檢索 (相信現(xiàn)在提到全文檢索，沒(méi)人會(huì)用 MYSQL 了)，你應(yīng)該默認(rèn)選擇 InnoDB 。

你自己在測(cè)試的時(shí)候可能會(huì)發(fā)現(xiàn) MyISAM 比 InnoDB 速度快，這是因?yàn)椋?MyISAM 只緩存索引，而 InnoDB 緩存數(shù)據(jù)和索引，MyISAM 不支持事務(wù)。但是 如果你使用 innodb_flush_log_at_trx_commit = 2 可以獲得接近的讀取性能 (相差百倍) 。

1.1 如何將現(xiàn)有的 MyISAM 數(shù)據(jù)庫(kù)轉(zhuǎn)換為 InnoDB:

1.2 為每個(gè)表分別創(chuàng)建 InnoDB FILE：

這樣可以保證 ibdata1 文件不會(huì)過(guò)大，失去控制。尤其是在執(zhí)行 mysqlcheck -o –all-databases 的時(shí)候。

2. 保證從內(nèi)存中讀取數(shù)據(jù)，講數(shù)據(jù)保存在內(nèi)存中

2.1 足夠大的 innodb_buffer_pool_size

推薦將數(shù)據(jù)完全保存在 innodb_buffer_pool_size ，即按存儲(chǔ)量規(guī)劃 innodb_buffer_pool_size 的容量。這樣你可以完全從內(nèi)存中讀取數(shù)據(jù)，最大限度減少磁盤操作。

2.1.1 如何確定 innodb_buffer_pool_size 足夠大，數(shù)據(jù)是從內(nèi)存讀取而不是硬盤？
方法 1

mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';
+----------------------------------+--------+
| Variable_name          | Value |
+----------------------------------+--------+
| Innodb_buffer_pool_pages_data  | 129037 |
| Innodb_buffer_pool_pages_dirty  | 362  |
| Innodb_buffer_pool_pages_flushed | 9998  |
| Innodb_buffer_pool_pages_free  | 0   | !!!!!!!!
| Innodb_buffer_pool_pages_misc  | 2035  |
| Innodb_buffer_pool_pages_total  | 131072 |
+----------------------------------+--------+
6 rows in set (0.00 sec)

發(fā)現(xiàn) Innodb_buffer_pool_pages_free 為 0，則說(shuō)明 buffer pool 已經(jīng)被用光，需要增大 innodb_buffer_pool_size

InnoDB 的其他幾個(gè)參數(shù)：

方法 2

或者用iostat -d -x -k 1 命令，查看硬盤的操作。

2.1.2 服務(wù)器上是否有足夠內(nèi)存用來(lái)規(guī)劃
執(zhí)行 echo 1 > /proc/sys/vm/drop_caches 清除操作系統(tǒng)的文件緩存，可以看到真正的內(nèi)存使用量。

2.2 數(shù)據(jù)預(yù)熱

默認(rèn)情況，只有某條數(shù)據(jù)被讀取一次，才會(huì)緩存在 innodb_buffer_pool。所以，數(shù)據(jù)庫(kù)剛剛啟動(dòng)，需要進(jìn)行數(shù)據(jù)預(yù)熱，將磁盤上的所有數(shù)據(jù)緩存到內(nèi)存中。數(shù)據(jù)預(yù)熱可以提高讀取速度。

對(duì)于 InnoDB 數(shù)據(jù)庫(kù)，可以用以下方法，進(jìn)行數(shù)據(jù)預(yù)熱:

1. 將以下腳本保存為 MakeSelectQueriesToLoad.sql

SELECT DISTINCT
  CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
  ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
  FROM
  (
    SELECT
      engine,table_schema db,table_name tb,
      index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
    FROM
    (
      SELECT
        B.engine,A.table_schema,A.table_name,
        A.index_name,A.column_name,A.seq_in_index
      FROM
        information_schema.statistics A INNER JOIN
        (
          SELECT engine,table_schema,table_name
          FROM information_schema.tables WHERE
          engine='InnoDB'
        ) B USING (table_schema,table_name)
      WHERE B.table_schema NOT IN ('information_schema','mysql')
      ORDER BY table_schema,table_name,index_name,seq_in_index
    ) A
    GROUP BY table_schema,table_name,index_name
  ) AA
ORDER BY db,tb
;

2. 執(zhí)行

3. 每次重啟數(shù)據(jù)庫(kù)，或者整庫(kù)備份前需要預(yù)熱的時(shí)候執(zhí)行：

mysql -uroot < /root/SelectQueriesToLoad.sql > /dev/null 2>&1

2.3 不要讓數(shù)據(jù)存到 SWAP 中

如果是專用 MYSQL 服務(wù)器，可以禁用 SWAP，如果是共享服務(wù)器，確定 innodb_buffer_pool_size 足夠大?；蛘呤褂霉潭ǖ膬?nèi)存空間做緩存，使用 memlock 指令。

3. 定期優(yōu)化重建數(shù)據(jù)庫(kù)

mysqlcheck -o –all-databases 會(huì)讓 ibdata1 不斷增大，真正的優(yōu)化只有重建數(shù)據(jù)表結(jié)構(gòu)：

CREATE TABLE mydb.mytablenew LIKE mydb.mytable;
INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytablezap;
ALTER TABLE mydb.mytablenew RENAME mydb.mytable;
DROP TABLE mydb.mytablezap;

4. 減少磁盤寫入操作

4.1 使用足夠大的寫入緩存 innodb_log_file_size

但是需要注意如果用 1G 的 innodb_log_file_size ，假如服務(wù)器當(dāng)機(jī)，需要 10 分鐘來(lái)恢復(fù)。

推薦 innodb_log_file_size 設(shè)置為 0.25 * innodb_buffer_pool_size

4.2 innodb_flush_log_at_trx_commit

這個(gè)選項(xiàng)和寫磁盤操作密切相關(guān)：

innodb_flush_log_at_trx_commit = 1 則每次修改寫入磁盤
innodb_flush_log_at_trx_commit = 0/2 每秒寫入磁盤

如果你的應(yīng)用不涉及很高的安全性 (金融系統(tǒng))，或者基礎(chǔ)架構(gòu)足夠安全，或者 事務(wù)都很小，都可以用 0 或者 2 來(lái)降低磁盤操作。

4.3 避免雙寫入緩沖

5. 提高磁盤讀寫速度

RAID0 尤其是在使用 EC2 這種虛擬磁盤 (EBS) 的時(shí)候，使用軟 RAID0 非常重要。

6. 充分使用索引

6.1 查看現(xiàn)有表結(jié)構(gòu)和索引

6.2 添加必要的索引

索引是提高查詢速度的唯一方法，比如搜索引擎用的倒排索引是一樣的原理。

索引的添加需要根據(jù)查詢來(lái)確定，比如通過(guò)慢查詢?nèi)罩净蛘卟樵內(nèi)罩?或者通過(guò) EXPLAIN 命令分析查詢。

6.2.1 比如，優(yōu)化用戶驗(yàn)證表：
添加索引

每次重啟服務(wù)器進(jìn)行數(shù)據(jù)預(yù)熱

添加啟動(dòng)腳本到 my.cnf

6.2.2 使用自動(dòng)加索引的框架或者自動(dòng)拆分表結(jié)構(gòu)的框架
比如，Rails 這樣的框架，會(huì)自動(dòng)添加索引，Drupal 這樣的框架會(huì)自動(dòng)拆分表結(jié)構(gòu)。會(huì)在你開(kāi)發(fā)的初期指明正確的方向。所以，經(jīng)驗(yàn)不太豐富的人一開(kāi)始就追求從 0 開(kāi)始構(gòu)建，實(shí)際是不好的做法。

7. 分析查詢?nèi)罩竞吐樵內(nèi)罩?/span>

記錄所有查詢，這在用 ORM 系統(tǒng)或者生成查詢語(yǔ)句的系統(tǒng)很有用。

注意不要在生產(chǎn)環(huán)境用，否則會(huì)占滿你的磁盤空間。

記錄執(zhí)行時(shí)間超過(guò) 1 秒的查詢：

8. 激進(jìn)的方法，使用內(nèi)存磁盤

現(xiàn)在基礎(chǔ)設(shè)施的可靠性已經(jīng)非常高了，比如 EC2 幾乎不用擔(dān)心服務(wù)器硬件當(dāng)機(jī)。而且內(nèi)存實(shí)在是便宜，很容易買到幾十G內(nèi)存的服務(wù)器，可以用內(nèi)存磁盤，定期備份到磁盤。

將 MYSQL 目錄遷移到 4G 的內(nèi)存磁盤

mkdir -p /mnt/ramdisk
sudo mount -t tmpfs -o size=4000M tmpfs /mnt/ramdisk/
mv /var/lib/mysql /mnt/ramdisk/mysql
ln -s /tmp/ramdisk/mysql /var/lib/mysql
chown mysql:mysql mysql

9. 用 NOSQL 的方式使用 MYSQL

B-TREE 仍然是最高效的索引之一，所有 MYSQL 仍然不會(huì)過(guò)時(shí)。

用 HandlerSocket 跳過(guò) MYSQL 的 SQL 解析層，MYSQL 就真正變成了 NOSQL。

10. 其他

單條查詢最后增加 LIMIT 1，停止全表掃描。
將非”索引”數(shù)據(jù)分離，比如將大篇文章分離存儲(chǔ)，不影響其他自動(dòng)查詢。
不用 MYSQL 內(nèi)置的函數(shù)，因?yàn)閮?nèi)置函數(shù)不會(huì)建立查詢緩存。
PHP 的建立連接速度非常快，所有可以不用連接池，否則可能會(huì)造成超過(guò)連接數(shù)。當(dāng)然不用連接池 PHP 程序也可能將
連接數(shù)占滿比如用了 @ignore_user_abort(TRUE);
使用 IP 而不是域名做數(shù)據(jù)庫(kù)路徑，避免 DNS 解析問(wèn)題

以上就是10個(gè)MySQL性能調(diào)優(yōu)的方法，希望對(duì)大家的學(xué)習(xí)有所幫助。

最新評(píng)論