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

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

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

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

你自己在測試的時(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ù)庫轉(zhuǎn)換為 InnoDB:

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

這樣可以保證 ibdata1 文件不會(huì)過大，失去控制。尤其是在執(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，則說明 buffer pool 已經(jīng)被用光，需要增大 innodb_buffer_pool_size

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

方法 2

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

2.1.2 服務(wù)器上是否有足夠內(nè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ù)庫剛剛啟動(dòng)，需要進(jìn)行數(shù)據(jù)預(yù)熱，將磁盤上的所有數(shù)據(jù)緩存到內(nèi)存中。數(shù)據(jù)預(yù)熱可以提高讀取速度。

對于 InnoDB 數(shù)據(jù)庫，可以用以下方法，進(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ù)庫，或者整庫備份前需要預(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ù)庫

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 分鐘來恢復(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 來降低磁盤操作。

4.3 避免雙寫入緩沖

5. 提高磁盤讀寫速度

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

6. 充分使用索引

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

6.2 添加必要的索引

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

索引的添加需要根據(jù)查詢來確定，比如通過慢查詢?nèi)罩净蛘卟樵內(nèi)罩?或者通過 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ì)在你開發(fā)的初期指明正確的方向。所以，經(jīng)驗(yàn)不太豐富的人一開始就追求從 0 開始構(gòu)建，實(shí)際是不好的做法。

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

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

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

記錄執(zhí)行時(shí)間超過 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ì)過時(shí)。

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

10. 其他

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

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

最新評論