《Mysql應用10個MySQL性能調優的方法》要點：
本文介紹了Mysql應用10個MySQL性能調優的方法，希望對您有用。如果有疑問，可以聯系我們。

MYSQL教程MYSQL 應該是最流行了 WEB 后端數據庫.WEB 開發語言最近發展很快,PHP, Ruby, Python, Java 各有特點,雖然 NOSQL 最近越碓蕉嗟謀惶岬劍竅嘈糯蟛糠旨芄故故腔嵫≡ MYSQL 來做數據存儲.

MYSQL教程MYSQL 如此方便和穩定,以至于我們在開發 WEB 程序的時候很少想到它.即使想到優化也是程序級別的,比如,不要寫過于消耗資源的 SQL 語句.但是除此之外,在整個系統上仍然有很多可以優化的地方.

MYSQL教程1. 選擇合適的存儲引擎: InnoDB

MYSQL教程除非你的數據表使用來做只讀或者全文檢索 (相信現在提到全文檢索,沒人會用 MYSQL 了),你應該默認選擇 InnoDB .

MYSQL教程你自己在測試的時候可能會發現 MyISAM 比 InnoDB 速度快,這是因為： MyISAM 只緩存索引,而 InnoDB 緩存數據和索引,MyISAM 不支持事務.但是 如果你使用 innodb_flush_log_at_trx_commit = 2 可以獲得接近的讀取性能 (相差百倍) .

MYSQL教程1.1 如何將現有的 MyISAM 數據庫轉換為 InnoDB:

MYSQL教程1.2 為每個表分別創建 InnoDB FILE：

MYSQL教程這樣可以保證 ibdata1 文件不會過大,失去控制.尤其是在執行 mysqlcheck -o Call-databases 的時候.

MYSQL教程?

MYSQL教程2. 保證從內存中讀取數據,講數據保存在內存中

MYSQL教程2.1 足夠大的 innodb_buffer_pool_size

MYSQL教程推薦將數據完全保存在 innodb_buffer_pool_size ,即按存儲量規劃 innodb_buffer_pool_size 的容量.這樣你可以完全從內存中讀取數據,最大限度減少磁盤操作.

MYSQL教程2.1.1 如何確定 innodb_buffer_pool_size 足夠大,數據是從內存讀取而不是硬盤?
方法 1

MYSQL教程
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)

MYSQL教程發現 Innodb_buffer_pool_pages_free 為 0,則說明 buffer pool 已經被用光,需要增大 innodb_buffer_pool_size

MYSQL教程InnoDB 的其他幾個參數：

MYSQL教程方法 2

MYSQL教程或者用iostat -d -x -k 1 命令,查看硬盤的操作.

MYSQL教程2.1.2 服務器上是否有足夠內存用來規劃
執行 echo 1 > /proc/sys/vm/drop_caches 清除操作系統的文件緩存,可以看到真正的內存使用量.

MYSQL教程2.2 數據預熱

MYSQL教程默認情況,只有某條數據被讀取一次,才會緩存在 innodb_buffer_pool.所以,數據庫剛剛啟動,需要進行數據預熱,將磁盤上的所有數據緩存到內存中.數據預熱可以提高讀取速度.

MYSQL教程對于 InnoDB 數據庫,可以用以下方法,進行數據預熱:

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

MYSQL教程
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
;

MYSQL教程2. 執行

MYSQL教程3. 每次重啟數據庫,或者整庫備份前需要預熱的時候執行：

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

MYSQL教程2.3 不要讓數據存到 SWAP 中

MYSQL教程如果是專用 MYSQL 服務器,可以禁用 SWAP,如果是共享服務器,確定 innodb_buffer_pool_size 足夠大.或者使用固定的內存空間做緩存,使用 memlock 指令.

MYSQL教程?

MYSQL教程3. 定期優化重建數據庫

MYSQL教程mysqlcheck -o Call-databases 會讓 ibdata1 不斷增大,真正的優化只有重建數據表結構：

MYSQL教程
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;

MYSQL教程?

MYSQL教程4. 減少磁盤寫入操作

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

MYSQL教程但是需要注意如果用 1G 的 innodb_log_file_size ,假如服務器當機,需要 10 分鐘來恢復.

MYSQL教程推薦 innodb_log_file_size 設置為 0.25 * innodb_buffer_pool_size

MYSQL教程4.2 innodb_flush_log_at_trx_commit

MYSQL教程這個選項和寫磁盤操作密切相關：

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

MYSQL教程如果你的應用不涉及很高的安全性 (金融系統),或者基礎架構足夠安全,或者 事務都很小,都可以用 0 或者 2 來降低磁盤操作.

MYSQL教程4.3 避免雙寫入緩沖

MYSQL教程?

MYSQL教程5. 提高磁盤讀寫速度

MYSQL教程RAID0 尤其是在使用 EC2 這種虛擬磁盤 (EBS) 的時候,使用軟 RAID0 非常重要.

MYSQL教程?

MYSQL教程6. 充分使用索引

MYSQL教程6.1 查看現有表結構和索引

MYSQL教程6.2 添加必要的索引

MYSQL教程索引是提高查詢速度的唯一方法,比如搜索引擎用的倒排索引是一樣的原理.

MYSQL教程索引的添加需要根據查詢來確定,比如通過慢查詢日志或者查詢日志,或者通過 EXPLAIN 命令分析查詢.

MYSQL教程6.2.1 比如,優化用戶驗證表：
添加索引

MYSQL教程每次重啟服務器進行數據預熱

MYSQL教程添加啟動腳本到 my.cnf

MYSQL教程6.2.2 使用自動加索引的框架或者自動拆分表結構的框架
比如,Rails 這樣的框架,會自動添加索引,Drupal 這樣的框架會自動拆分表結構.會在你開發的初期指明正確的方向.所以,經驗不太豐富的人一開始就追求從 0 開始構建,實際是不好的做法.

MYSQL教程7. 分析查詢日志和慢查詢日志

MYSQL教程記錄所有查詢,這在用 ORM 系統或者生成查詢語句的系統很有用.

MYSQL教程注意不要在生產環境用,否則會占滿你的磁盤空間.

MYSQL教程記錄執行時間超過 1 秒的查詢：

MYSQL教程8. 激進的方法,使用內存磁盤

MYSQL教程現在基礎設施的可靠性已經非常高了,比如 EC2 幾乎不用擔心服務器硬件當機.而且內存實在是便宜,很容易買到幾十G內存的服務器,可以用內存磁盤,定期備份到磁盤.

MYSQL教程將 MYSQL 目錄遷移到 4G 的內存磁盤

MYSQL教程
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

MYSQL教程9. 用 NOSQL 的方式使用 MYSQL

MYSQL教程B-TREE 仍然是最高效的索引之一,所有 MYSQL 仍然不會過時.

MYSQL教程用 HandlerSocket 跳過 MYSQL 的 SQL 解析層,MYSQL 就真正變成了 NOSQL.

MYSQL教程10. 其他

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

MYSQL教程以上就是10個MySQL性能調優的方法,希望對大家的學習有所幫助.

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/5299.html