共計 7098 個字符，預計需要花費 18 分鐘才能閱讀完成。

本篇文章給大家分享的是有關如何優化 MYSQL 性能，丸趣 TV 小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著丸趣 TV 小編一起來看看吧。

1. MySQL 性能優化簡介

在 Web 應用程序體系架構中，數據持久層 (通常是一個關系數據庫) 是關鍵的核心部分，它對系統的性能有非常重要的影響。MySQL 是目前使用最多的開源數據庫，但是 MySQL 數據庫的默認設置性能非常的差，僅僅是一個玩具數據庫。因此在產品中使用 MySQL 數據庫必須進行必要的優化。

優化是一個復雜的任務，本文描述 MySQL 相關的數據庫設計和查詢優化，服務器端優化，存儲引擎優化。

2. 數據庫設計和查詢優化

在 MySQL 性能優化中，首先要考慮的就是 Database Schema 設計，這一點是非常重要的。一個糟糕的 Schema 設計即使在性能調優的 MySQL Server 上運行，也會表現出很差的性能; 和 Schema 相似，查詢語句的設計也會影響 MySQL 的性能，應該避免寫出低效的 SQL 查詢。這一節將詳細討論這兩方面的優化。

2.1 Schema Design

Schema 的優化取決于將要運行什么樣的 query，不同的 query 會有不同的 Schema 優化方案。2.2 節將介紹 Query Design 的優化。Schema 設計同樣受到預期數據集大小的影響。Schema 設計時主要考慮：標準化，數據類型，索引。

2.1.1 標準化

標準化是在數據庫中組織數據的過程。其中包括，根據設計規則創建表并在這些表間建立關系; 通過取消冗余度與不一致相關性，該設計規則可以同時保護數據并提高數據的靈活性。通常數據庫標準化是讓數據庫設計符合某一級別的范式，通常滿足第三范式即可。也有第四范式 (也稱為 Boyce Codd 范式，BCNF)) 與第五范式存在，但是在實際設計中很少考慮。忽視這些規則可能使得數據庫的設計不太完美，但這不應影響功能。

標準化的特點：

1) 所有的“對象”都在它自己的 table 中，沒有冗余。

2) 數據庫通常由 E - R 圖生成。

3) 簡潔，更新屬性通常只需要更新很少的記錄。

4) Join 操作比較耗時。

5) Select，sort 優化措施比較少。

6) 適用于 OLTP 應用。

非標準化的特點：

1) 在一張表中存儲很多數據，數據冗余。

2) 更新數據開銷很大，更新一個屬性可能會更新很多表，很多記錄。

3) 在刪除數據是有可能丟失數據。

4) Select，order 有很多優化的選擇。

5) 適用于 DSS 應用。

標準化和非標準化都有各自的優缺點，通常在一個數據庫設計中可以混合使用，一部分表格標準化，一部分表格保留一些冗余數據：

1) 對 OLTP 使用標準化，對 DSS 使用非標準化

2) 使用物化視圖。MySQL 不直接支持該數據庫特性，但是可以用 MyISAM 表代替。

3) 冗余一些數據在表格中，例如將 ref_id 和 name 存在同一張表中。但是要注意更新問題。

4) 對于一些簡單的對象，直接使用 value 作為建。例如 IP address 等

5) Reference by PRIMARY/UNIQUE KEY。MySQL 可以優化這種操作，例如：

java 代碼

select city_name from city,state where state_id=state.id and state.code=‘CA’”converted to“select city_name from city where state_id=12

2.1.2 數據類型

最基本的優化之一就是使表在磁盤上占據的空間盡可能小。這能帶來性能非常大的提升，因為數據小，磁盤讀入較快，并且在查詢過程中表內容被處理所占用的內存更少。同時，在更小的列上建索引，索引也會占用更少的資源。

可以使用下面的技術可以使表的性能更好并且使存儲空間最小：

1) 使用正確合適的類型，不要將數字存儲為字符串。

2) 盡可能地使用最有效 (最小) 的數據類型。MySQL 有很多節省磁盤空間和內存的專業化類型。

3) 盡可能使用較小的整數類型使表更小。例如，MEDIUMINT 經常比 INT 好一些，因為 MEDIUMINT 列使用的空間要少 25%。

4) 如果可能，聲明列為 NOT NULL。它使任何事情更快而且每列可以節省一位。注意如果在應用程序中確實需要 NULL，應該毫無疑問使用它，只是避免 默認地在所有列上有它。

5) 對于 MyISAM 表，如果沒有任何變長列(VARCHAR、TEXT 或 BLOB 列)，使用固定尺寸的記錄格式。這比較快但是不幸地可能會浪費一些空間。即使你已經用 CREATE 選項讓 VARCHAR 列 ROW_FORMAT=fixed，也可以提示想使用固定長度的行。

6) 使用 sample character set，例如 latin1。盡量少使用 utf-8，因為 utf- 8 占用的空間是 latin1 的 3 倍。可以在不需要使用 utf- 8 的字段上面使用 latin1，例如 mail，url 等。

2.1.3 索引

所有 MySQL 列類型可以被索引。對相關列使用索引是提高 SELECT 操作性能的最佳途徑。使用索引應該注意以下幾點：

1) MySQL 只會使用前綴，例如 key(a, b) …where b=5 將使用不到索引。

2) 要選擇性的使用索引。在變化很少的列上使用索引并不是很好，例如性別列。

3) 在 Unique 列上定義 Unique index。

4) 避免建立使用不到的索引。

5) 在 Btree index 中(InnoDB 使用 Btree)，可以在需要排序的列上建立索引。

6) 避免重復的索引。

7) 避免在已有索引的前綴上建立索引。例如：如果存在 index(a，b)則去掉 index(a)。

8) 控制單個索引的長度。使用 key(name(8))在數據的前面幾個字符建立索引。

9) 越是短的鍵值越好，最好使用 integer。

10) 在查詢中要使用到索引(使用 explain 查看)，可以減少讀磁盤的次數，加速讀取數據。

11) 相近的鍵值比隨機好。Auto_increment 就比 uuid 好。

12) Optimize table 可以壓縮和排序 index，注意不要頻繁運行。

13) Analyze table 可以更新數據。

2.2 Designing queries

查詢語句的優化是一個 Case by case 的問題，不同的 sql 有不同的優化方案，在這里我只列出一些通用的技巧。

1) 在有 index 的情況下，盡量保證查詢使用了正確的 index。可以使用 EXPLAIN select …查看結果，分析查詢。

2) 查詢時使用匹配的類型。例如 select * from a where id=5，如果這里 id 是字符類型，同時有 index，這條查詢則使用不到 index，會做全表掃描，速度會很慢。正確的應該是 … where id=”5”，加上引號表明類型是字符。

3) 使用 –log-slow-queries –long-query-time= 2 查看查詢比較慢的語句。然后使用 explain 分析查詢，做出優化。

3. 服務器端優化

3.1 MySQL 安裝

MySQL 有很多發行版本，最好使用 MySQL AB 發布的二進制版本。也可以下載源代碼進行編譯安裝，但是編譯器和類庫的一些 bug 可能會使編譯完成的 MySQL 存在潛在的問題。

如果安裝 MySQL 的服務器使用的是 Intel 公司的處理器，可以使用 intel c++ 編譯的版本，在 Linux World2005 的一篇 PPT 中提到，使用 intel C++ 編譯器編譯的 MySQL 查詢速度比正常版本快 30% 左右。Intel c++ 編譯版本可以在 MySQL 官方網站下載。

3.2 服務器設置優化

MySQL 默認的設置性能很差，所以要做一些參數的調整。這一節介紹一些通用的參數調整，不涉及具體的存儲引擎(主要指 MyISAM，InnoDB，相關優化在 4 中介紹)。

–character-set：如果是單一語言使用簡單的 character set 例如 latin1。盡量少用 Utf-8，utf- 8 占用空間較多。

–memlock：鎖定 MySQL 只能運行在內存中，避免 swapping，但是如果內存不夠時有可能出現錯誤。

–max_allowed_packet：要足夠大，以適應比較大的 SQL 查詢，對性能沒有太大影響，主要是避免出現 packet 錯誤。

–max_connections：server 允許的最大連接。太大的話會出現 out of memory。

–table_cache：MySQL 在同一時間保持打開的 table 的數量。打開 table 開銷比較大。一般設置為 512。

–query_cache_size：用于緩存查詢的內存大小。

–datadir：mysql 存放數據的根目錄，和安裝文件分開在不同的磁盤可以提高一點性能。

4. 存儲引擎優化

MySQL 支持不同的存儲引擎，主要使用的有 MyISAM 和 InnoDB。

4.1 MyISAM

MyISAM 管理非事務表。它提供高速存儲和檢索，以及全文搜索能力。MyISAM 在所有 MySQL 配置里被支持，它是默認的存儲引擎，除非配置 MySQL 默認使用另外一個引擎。

4.1.1 MyISAM 特性

4.1.1.1 MyISAM Properties

1) 不支持事務，宕機會破壞表

2) 使用較小的內存和磁盤空間

3) 基于表的鎖，并發更新數據會出現嚴重性能問題

4) MySQL 只緩存 Index，數據由 OS 緩存

4.1.1.2 Typical MyISAM usages

1) 日志系統

2) 只讀或者絕大部分是讀操作的應用

3) 全表掃描

4) 批量導入數據

5) 沒有事務的低并發讀 / 寫

4.1.2 MyISAM 優化要點

1) 聲明列為 NOT NULL，可以減少磁盤存儲。

2) 使用 optimize table 做碎片整理，回收空閑空間。注意僅僅在非常大的數據變化后運行。

3) Deleting/updating/adding 大量數據的時候禁止使用 index。使用 ALTER TABLE t DISABLE KEYS。

4) 設置 myisam_max_[extra]_sort_file_size 足夠大，可以顯著提高 repair table 的速度。

4.1.3 MyISAM Table Locks

1) 避免并發 insert，update。

2) 可以使用 insert delayed，但是有可能丟失數據。

3) 優化查詢語句。

4) 水平分區。

5) 垂直分區。

6) 如果都不起作用，使用 InnoDB。

4.1.4 MyISAM Key Cache

1) 設置 key_buffer_size variable。MyISAN 最主要的 cache 設置，用于緩存 MyISAM 表格的 index 數據，該參數只對 MyISAM 有影響。通常在只使用 MyISAM 的 Server 中設置 25-33% 的內存大小。

2) 可以使用幾個不同的 Key Caches(對一些 hot data)。

a) SET GLOBAL test.key_buffer_size=512*1024;

b) CACHE INDEX t1.i1, t2.i1, t3 IN test;

2) Preload index 到 Cache 中可以提高查詢速度。因為 preloading index 是順序的，所以非常快。

a) LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;

4.2 InnoDB

InnoDB 給 MySQL 提供了具有提交，回滾和崩潰恢復能力的事務安全 (ACID 兼容) 存儲引擎。InnoDB 提供 row level lock，并且也在 SELECT 語句提供一個 Oracle 風格一致的非鎖定讀。這些特色增加了多用戶部署和性能。沒有在 InnoDB 中擴大鎖定的需要，因為在 InnoDB 中 row level lock 適合非常小的空間。InnoDB 也支持 FOREIGN KEY 約束。在 SQL 查詢中，你可以自由地將 InnoDB 類型的表與其它 MySQL 的表的類型混合起來，甚至在同一個查詢中也可以混合。

InnoDB 是為在處理巨大數據量時獲得最大性能而設計的。它的 CPU 使用效率非常高。

InnoDB 存儲引擎已經完全與 MySQL 服務器整合，InnoDB 存儲引擎為在內存中緩存數據和索引而維持它自己的緩沖池。InnoDB 存儲它的表 索引在一個表空間中，表空間可以包含數個文件(或原始磁盤分區)。這與 MyISAM 表不同，比如在 MyISAM 表中每個表被存在分離的文件中。InnoDB 表可以是任何大小，即使在文件尺寸被限制為 2GB 的操作系統上。

許多需要高性能的大型數據庫站點上使用了 InnoDB 引擎。著名的 Internet 新聞站點 Slashdot.org 運行在 InnoDB 上。Mytrix, Inc. 在 InnoDB 上存儲超過 1TB 的數據，還有一些其它站點在 InnoDB 上處理平均每秒 800 次插入 / 更新的負荷。

4.2.1 InnoDB 特性

4.2.1.1 InnoDB Properties

1) 支持事務，ACID，外鍵。

2) Row level locks。

3) 支持不同的隔離級別。

4) 和 MyISAM 相比需要較多的內存和磁盤空間。

5) 沒有鍵壓縮。

6) 數據和索引都緩存在內存 hash 表中。

4.2.1.2 InnoDB Good For

1) 需要事務的應用。

2) 高并發的應用。

3) 自動恢復。

4) 較快速的基于主鍵的操作。

4.2.2 InnoDB 優化要點

1) 盡量使用 short，integer 的主鍵。

2) Load/Insert 數據時按主鍵順序。如果數據沒有按主鍵排序，先排序然后再進行數據庫操作。

3) 在 Load 數據是為設置 SET UNIQUE_CHECKS=0，SET FOREIGN_KEY_CHECKS=0，可以避免外鍵和唯一性約束檢查的開銷。

4) 使用 prefix keys。因為 InnoDB 沒有 key 壓縮功能。

4.2.3 InnoDB 服務器端設定

innodb_buffer_pool_size：這是 InnoDB 最重要的設置，對 InnoDB 性能有決定性的影響。默認的設置只有 8M，所以默認的數據庫設置下面 InnoDB 性能很差。在只有 InnoDB 存儲引擎的數據庫服務器上面，可以設置 60-80% 的內存。更精確一點，在內存容量允許的情況下面設置比 InnoDB tablespaces 大 10% 的內存大小。

innodb_data_file_path：指定表數據和索引存儲的空間，可以是一個或者多個文件。最后一個數據文件必須是自動擴充的，也只有最后一個文件允許自動擴充。這樣，當空間用完后，自動擴充數據文件就會自動增長 (以 8MB 為單位) 以容納額外的數據。例如：innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend 兩個數據文件放在不同的磁盤上。數據首先放在 ibdata1 中，當達到 900M 以后，數據就放在 ibdata2 中。一旦達到 50MB，ibdata2 將以
8MB 為單位自動增長。如果磁盤滿了，需要在另外的磁盤上面增加一個數據文件。

innodb_autoextend_increment: 默認是 8M, 如果一次 insert 數據量比較多的話, 可以適當增加.

innodb_data_home_dir：放置表空間數據的目錄，默認在 mysql 的數據目錄，設置到和 MySQL 安裝文件不同的分區可以提高性能。

innodb_log_file_size：該參數決定了 recovery speed。太大的話 recovery 就會比較慢，太小了影響查詢性能，一般取 256M 可以兼顧性能和 recovery 的速度。

innodb_log_buffer_size：磁盤速度是很慢的，直接將 log 寫道磁盤會影響 InnoDB 的性能，該參數設定了 log buffer 的大小，一般 4M。如果有大的 blob 操作，可以適當增大。

innodb_flush_logs_at_trx_commit=2：該參數設定了事務提交時內存中 log 信息的處理。

1) = 1 時，在每個事務提交時，日志緩沖被寫到日志文件，對日志文件做到磁盤操作的刷新。Truly ACID。速度慢。

2) = 2 時，在每個事務提交時，日志緩沖被寫到文件，但不對日志文件做到磁盤操作的刷新。只有操作系統崩潰或掉電才會刪除最后一秒的事務，不然不會丟失事務。

3) = 0 時，日志緩沖每秒一次地被寫到日志文件，并且對日志文件做到磁盤操作的刷新。任何 mysqld 進程的崩潰會刪除崩潰前最后一秒的事務

innodb_file_per_table：可以存儲每個 InnoDB 表和它的索引在它自己的文件中。

transaction-isolation=READ-COMITTED: 如果應用程序可以運行在 READ-COMMITED 隔離級別，做此設定會有一定的性能提升。

innodb_flush_method：設置 InnoDB 同步 IO 的方式：

1) Default – 使用 fsync()。

2) O_SYNC 以 sync 模式打開文件，通常比較慢。

3) O_DIRECT，在 Linux 上使用 Direct IO。可以顯著提高速度，特別是在 RAID 系統上。避免額外的數據復制和 double buffering(mysql buffering 和 OS buffering)。

innodb_thread_concurrency：InnoDB kernel 最大的線程數。

1) 最少設置為(num_disks+num_cpus)*2。

2) 可以通過設置成 1000 來禁止這個限制

5. 緩存

緩存有很多種，為應用程序加上適當的緩存策略會顯著提高應用程序的性能。由于應用緩存是一個比較大的話題，所以這一部分還需要進一步調研。

 

以上就是如何優化 MYSQL 性能，丸趣 TV 小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注丸趣 TV 行業資訊頻道。