MySQL資料庫校調和效能優化

0.簡介

原文連結: http://www.cnblogs.com/keerdi/

在Web應用程式體系架構中，資料持久層（通常是一個關聯式資料庫）是關鍵的核心部分，它對系統的效能有非常重要的影響。MySQL是目前使用最多的開源資料庫，但是mysql資料庫的預設設定效能非常的差，僅僅是一個玩具資料庫。因此在產品中使用MySQL資料庫必須進行必要的優化。

優化是一個複雜的任務，分層四個部分

  1. 資料庫設計
  2. 查詢優化
  3. 伺服器端優化
  4. 儲存引擎優化

1.資料庫設計和查詢優化

在MySQL Server效能調優中，首先要考慮的就是Database Schema設計,和Schema（模式）相似，查詢語句的設計也會影響MySQL的效能，應該避免寫出低效的SQL查詢。這一節將詳細討論這2方面的優化。

1.1 Schema Design

Schema設計時主要考慮：正規化，資料型別，索引。

1.1.1 正規化

通常資料庫標準化是讓資料庫設計符合某一級別的正規化，通常滿足第三正規化即可。也有第四正規化（也稱為 Boyce Codd正規化，BCNF）與第五正規化存在，但是在實際設計中很少考慮。忽視這些規則可能使得資料庫的設計不太完美，但這不應影響功能。

  • 1NF: 除去重複群, 資料表中的所有紀錄之屬性內含值都是基元值( Atomic Value ), 每個欄位只能有一個資料
  • 2NF: 除去部分相依, 每一個非鍵屬性必須完全依賴於主鍵
  • 3NF: 除去遞移相依, 各欄位與主鍵沒有遞移相依
  • BCNF( Boycee codd ): 除去其他因功能所造成的異常, 主鍵由多欄位組成, 主鍵中的欄位不可以相依於其它非主鍵.
    • 使用時機: 如果主鍵是由多個欄位組成
  • 4NF: 除去多值相依
  • 5NF: 除去剩下所有的異常
原先
學號 姓名 課程代碼 課程名稱 成績 老師編號 老師姓名
001 Kimi

C001
C002

XXX

ZZZ

100

100

T001

T002

A

B
002 CC

C001
C003

XXX

YYY

70

90

T001

T003

A

C

1 NF後, 每一個為只能有一個資料

學號 姓名 課程代碼 課程名稱 成績 老師編號 老師姓名
001 Kimi C001 XXX 100 T001 A
001 Kimi C002 ZZZ 100 T002 B
002 CC C001 XXX 70 T001 A
002 CC C003 YYY 90 T003 C
2NF後

學生表

學號 姓名
001 Kimi

成績表

學號 課程代碼 成績
001 C001 100

課程表, 老師編號相依課程代碼, 老師姓名相依老師編號, 老師姓名遞移相依課程代碼

課程代碼 課程名稱 老師編號 老師姓名
C001 XXX T001 A
3NF 後

課程表

課程代碼 課程名稱 老師編號
C001 XXX T001

老師表

老師編號 老師姓名
T001 A

BCNF, 主鍵中的欄位不可以相依於其它非主鍵

成績表 , 學號不相依成績, 課程代碼不相依成績; 成績相依學號, 成績相依課程代碼

學號 課程代碼 成績
001 C001 100
正規化的特點：
  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

1.1.2 資料型別

最基本的優化之一就是使表在磁碟上佔據的空間儘可能小。這能帶來效能非常大的提升，因為資料小，磁碟讀入較快，並且在查詢過程中表內容被處理所佔用的記憶體更少。同時，在更小的列上建索引，索引也會佔用更少的資源。 可以使用下面的技術可以使表的效能更好並且使儲存空間最小：

  1. 使用正確合適的型別，不要將數字儲存為字串。
    • TinyINT, SmallINT, MediumINT 作為整數, 非負整數加上unsigned
  2. 儘可能地使用最有效(最小)的資料型別。MySQL有很多節省磁碟空間和記憶體的專業化型別。
  3. 儘可能使用較小的整數型別使表更小。
    • 中等型整數MEDIUMINT經常比INT好一些，因為MEDIUMINT列使用的空間要少25%。
  4. 如果可能，宣告列為NOT NULL。它使任何事情更快而且每列可以節省一位。注意如果在應用程式中確實需要NULL，應該毫無疑問使用它，只是避免預設地在所有列上有它。NULL難查詢且站index空間
  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等。
  7. 用UNSIGNED INT來存IP, inet_addr(), inet_aton()和inet_ntoa()函数
  8. 不使用外鍵, 由程式碼來約束

  9. TIMESTAMP取代Datetime (TODO: why?)

    • TIMESTAMP 佔用空間更小：在 MySQL 中，TIMESTAMP 佔用 4 個字節，而 DATETIME 佔用 8 個字節。因此，如果你需要在數據庫中存儲大量的日期和時間，使用 TIMESTAMP 可以節省大量的存儲空間。
    • TIMESTAMP 支持更廣泛的範圍：TIMESTAMP 支持的日期範圍是從 1970 年 1 月 1 日到 2038 年 1 月 19 日，而 DATETIME 支持的日期範圍是從 1000 年 1 月 1 日到 9999 年 12 月 31 日。因此，如果你需要存儲超出 DATETIME 範圍的日期和時間，就必須使用 TIMESTAMP。
    • TIMESTAMP 更容易進行時區轉換：在 MySQL 中，TIMESTAMP 可以自動進行時區轉換，而 DATETIME 則需要手動進行轉換。這使得在多時區應用程序中使用 TIMESTAMP 更加方便。 MySQL TIMESTAMP vs DATETIME - Choosing the Right Date Type: https://www.mysqltutorial.org/mysql-timestamp.aspx

  10. 盡量不用unique

1.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）。 假設有一個表 users，包含三個欄位：id、name 和 age，現在已經在 (name, age) 上建立了索引，這時如果還在 name 上建立索引，就屬於在已有索引的字首上建立索引。為了避免這種情況，可以把 name 上的索引去掉。
  8. 控制單個索引的長度。使用key（name（8））在資料的前面幾個字元建立索引。
    CREATE INDEX idx_name ON mytable (name(8));
    如果需要根據 name 欄位的前 8 個字元進行查詢，那麼在前 8 個字元上建立索引可以提高查詢效率。但是，如果需要查詢的字元不一定在前 8 個位置，那麼這個索引就沒有太大的作用了，甚至可能會降低查詢效率
  9. 越是短的鍵值越好，最好使用integer。
  10. 在查詢中要使用到索引（使用explain檢視），可以減少讀磁碟的次數，加速讀取資料。
    EXPLAIN SELECT * FROM mytable WHERE id = 123;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | mytable | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
  11. 相近的鍵值比隨機好。Auto_increment就比uuid好
  12. Optimize table可以壓縮和排序index，注意不要頻繁執行。
  13. Analyze table可以更新資料。 ANALYZE TABLE 命令在 MySQL 中用於分析和更新表的統計信息，以優化查詢的效率。 它可以更新資料庫中表的統計信息，包括行數、平均行長、索引統計等等，從而讓 MySQL 的查詢優化器能夠更加準確地估計查詢的成本和選擇最優的查詢計劃。 儘管 ANALYZE TABLE 可以更新表的統計信息，但它並不能更新表中的實際資料。 如果需要更新資料，應該使用 UPDATE 命令。 需要注意的是，ANALYZE TABLE 會對表進行鎖定，因此在執行 ANALYZE TABLE 命令時，可能會對系統性能造成一定的影響。 為了減少這種影響，可以選擇在低峰期執行 ANALYZE TABLE 命令，或者使用分區表進行統計分析，從而分散鎖定的影響。

1.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分析查詢，做出優化。 在 MySQL 中，可以使用 --log-slow-queries 和 --long-query-time 選項來記錄慢查詢。通過記錄慢查詢，可以找出性能瓶頸並進行優化。 這裡的 --long-query-time 選項用於指定慢查詢的閾值，默認值是 10 秒。 以下是使用 --log-slow-queries 和 --long-query-time 選項來記錄慢查詢的示例：
    mysqld --log-slow-queries=/var/log/mysql/mysql-slow.log --long-query-time=2
    在記錄了慢查詢之後，可以使用 EXPLAIN 命令來分析查詢並進行優化。 EXPLAIN 命令用於模擬執行查詢並顯示查詢的執行計劃，從而讓你了解查詢的性能瓶頸。以下是使用 EXPLAIN 命令分析查詢的示例：
    EXPLAIN SELECT * FROM orders WHERE customer_id = 100;
    執行上述命令後，MySQL 會顯示一個包含了查詢執行計劃的結果集。通過分析執行計劃，可以了解查詢使用了哪些索引，以及如何避免全表掃描等性能瓶頸。 根據分析結果，可以進行相應的優化，從而提高查詢的性能。
  4. or改成in. or 效率O(n) , in 效率O(log n)
  5. 連續數值使用between 不用 in
  6. 使用 limit 分頁
  7. 避免
    • %xxx
    • Select *
    • 避免 WHERE 使用!= or <> 在 MySQL 中，WHERE 子句用於過濾查詢結果。使用 != 或 <> 運算符可以實現不等於的比較。然而，在使用這些運算符時，可能會對性能產生負面影響，尤其是在處理大數據量時。 原因在於，當使用 != 或 <> 運算符進行比較時，MySQL 會將整個表進行掃描，找出符合不等於條件的行。這將導致查詢性能下降，尤其是在處理大數據量時。 因此，為了提高性能，應該避免使用 != 或 <> 運算符。可以考慮使用其他運算符，例如 =、>、<、>= 和 <= 等，以實現相同的比較效果。 此外，還可以考慮使用 IN 或 NOT IN 運算符，以進一步優化查詢性能。 以下是一個避免使用 != 或 <> 運算符的示例：
       -- 不好的寫法
 SELECT * FROM orders WHERE status != 'cancelled';
 SELECT * FROM orders WHERE status <> 'cancelled';
      改成以下更好的寫法：
       -- 好的寫法
 SELECT * FROM orders WHERE status IN ('pending', 'processing', 'completed');
      這個示例中，將 != 改為了 <>，從而實現了相同的比較效果。 但是，這種寫法仍然會對性能產生負面影響，因為 MySQL 需要掃描整個表來找出符合條件的行。 因此，建議使用 IN 或 NOT IN 運算符，從而避免對性能產生負面影響。
    • 避免在 WHERE 子句中對字段進行NULL判斷 當在 WHERE 子句中對字段進行 NULL 判斷時，可能會對性能產生負面影響，尤其是在處理大數據量時。 原因在於，當在 WHERE 子句中對字段進行 NULL 判斷時，MySQL 需要掃描整個表來找出符合條件的行。這將導致查詢性能下降，尤其是在處理大數據量時。 因此，為了提高性能，應該避免在 WHERE 子句中對字段進行 NULL 判斷。可以考慮使用 IS NULL 或 IS NOT NULL 運算符，以實現相同的比較效果。此外，還可以考慮將字段設置為 NOT NULL，從而避免對性能產生負面影響
    • 不用列運算 select id where age+1=10 改成 select id where age=9
  8. SELECT * FROM book WHERE name LIKE "MySQL%" 改成SELECT * FROM book WHERE name >="MySQL" and name <"MySQM"
  9. Async Update
    1. 使用消息隊列：將需要更新的數據寫入消息隊列中，然後異步處理這些消息，進行數據庫的更新操作。
    2. 使用線程池：將更新操作交給線程池中的線程進行異步處理，從而釋放主線程的資源，提高系統的效率。
    3. 使用非阻塞IO：在進行數據庫操作的時候，使用非阻塞的IO方式，從而讓數據庫的更新操作在後台異步進行。
    4. 使用異步框架：使用支持異步操作的框架，比如Python的asyncio和Java的CompletableFuture等，從而實現異步的數據庫操作。

  10. 多個SQL statement 用一個預存程序( stored procedure )代替

    example:

CREATE PROCEDURE usp_AddProduct
(
   @Barcode varchar(13),
   @Caption nvarchar(50)
)
AS
BEGIN

   IF LEN(@Barcode) < 13 
      RAISERROR('Barcode length is too short.')

   INSERT INTO MyProducts (Barcode, Caption) VALUES (@Barcode, @Caption)

END

## 外部程式需要使用 EXECUTE 或 CALL 來呼叫預存程序。
EXEC usp_AddProduct '2293891100011', 'MyProductCaption'

2. 伺服器端優化

2.1 MySQL安裝

MySQL有很多發行版本，最好使用MySQL AB釋出的二進位制版本。也可以下載原始碼進行編譯安裝，但是編譯器和類庫的一些bug可能會使編譯完成的MySQL存在潛在的問題。 如果安裝MySQL的伺服器使用的是Intel公司的處理器，可以使用intel c++編譯的版本，在Linux World2005的一篇PPT中提到，使用intel C++編譯器編譯的MySQL查詢速度比正常版本快30%左右。Intel c++編譯版本可以在MySQL官方網站下載。

2.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。
  • --tablecache：MySQL在同一時間保持開啟的table的數量。開啟table開銷比較大。一般設定為512。 類似thread_cache_size, 但是用來緩存表文件, 主要用於 MyISAM
  • --query_cache_size： 用於快取查詢的記憶體大小。
    • Qcachehits / (Qcache_hits+Qcache_inserts) *100
    • 不建議太大, 256 MB
    • show status like 'Qcache_%'
  • --datadir：mysql存放資料的根目錄，和安裝檔案分開在不同的磁碟可以提高一點效能。
  • --wait_timeout : 數據連接閒置時間, 8 hrs -> 30 minis
  • --max_user_connection: 最大連接數, 0無上限, 最多設一個合理的上限
  • --thread_concurrency: 併發線程數, 設 CPU的 2 倍
  • --skip_name_resolve: 禁止對外部連接進行DNS解析
  • --key_buffer_size : 索引的緩存大小,
    • Server中設定25-33%的記憶體大小
    • 對 MyISAM影響大
  • --innodb_log_buffer_size : InnoDB事務日誌的緩衝區, 不建議超過 32 MB
  • --sort_buffer_size : 先用 index, 不行再來改 sort_buffer_size
  • --record_buffer : 每進行一個順序掃描, 為其掃描的每張表分配一個緩存區
  • --thread_cache_size : 保存當前沒有與連接關聯但是準備為後面新的連接服務的線程, 可以快速響應連接現成的請求而無需創新的

3. 儲存引擎優化

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

3.1 MyISAM

MyISAM管理非事務表。它提供高速儲存和檢索，以及全文搜尋能力。MyISAM在所有MySQL配置裡被支援，Mysql 5.5.5 之前預設是MyISAM.

犧牲完整性跟一至性來取得效能. Sacrifices some integrity and consistency feature for simplicity and performance.

3.1.1 MyISAM特性

3.1.1.1 MyISAM Properties

  1. 不支援事務，當機會破壞表
  2. 使用較小的記憶體和磁碟空間
  3. 基於Table的鎖，併發更新資料會出現嚴重效能問題
  4. MySQL只快取Index，資料由OS快取

3.1.1.2 Typical MyISAM usages

  1. 日誌系統
  2. 只讀或者絕大部分是讀操作的應用
  3. 全表掃描
  4. 批量匯入資料
  5. 沒有事務的低併發讀/寫

3.1.2 MyISAM優化要點

  1. 宣告列為NOT NULL，可以減少磁碟儲存。
  2. 使用optimize table做碎片整理，回收空閒空間。注意僅僅在非常大的資料變化後執行。
  3. Deleting/updating/adding大量資料的時候禁止使用index。使用ALTER TABLE t DISABLE KEYS。
  4. 設定myisammax[extra]_sort_file_size足夠大，可以顯著提高repair table的速度。

3.1.3 MyISAM Table Locks

  1. 避免併發insert，update。
  2. 可以使用insert delayed，但是有可能丟失資料。
  3. 優化查詢語句。
  4. 水平分割槽。
  5. 垂直分割槽。
  6. 如果都不起作用，使用InnoDB。

3.1.4 MyISAM Key Cache

  1. 設定key_buffer_size variable。MyISAN最主要的cache設定，用於快取MyISAM表格的index資料，該引數只對MyISAM有影響。通常在只使用MyISAM的Server中設定25-33%的記憶體大小。
  2. 可以使用幾個不同的Key Caches（對一些hot data）。
    • SET GLOBAL test.key_buffer_size=512*1024;
    • CACHE INDEX t1.i1, t2.i1, t3 IN test;
  3. Preload index到Cache中可以提高查詢速度。因為preloading index是順序的，所以非常快。
    • LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES；

3.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次插入/更新的負荷。

3.2.1 InnoDB特性

3.2.1.1 InnoDB Properties

ACID

  • Atomicity（原子性）：一個事務（transaction）中的所有操作，或者全部完成，或者全部不完成，不會結束在中間某個環節。事務在執行過程中發生錯誤，會被回滾)（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣。即，事務不可分割、不可約簡。
  • Consistency（一致性）：在事務開始之前和事務結束以後，資料庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設約束、觸發器)、級聯回滾等。
  • Isolation（隔離性）：資料庫允許多個並發事務同時對其數據進行讀寫和修改的能力，隔離性可以防止多個事務並發執行時由於交叉執行而導致數據的不一致。事務隔離分為不同級別，包括未提交讀（Read uncommitted）、提交讀（read committed）、可重複讀（repeatable read）和串行化（Serializable）。

  • Durability（持久性）：事務處理結束後，對數據的修改就是永久的，即便系統故障也不會丟失。

  • 支援事務，ACID，外來鍵。

  • Row level locks。
  • 支援不同的隔離級別。
  • 和MyISAM相比需要較多的記憶體和磁碟空間。
  • 沒有鍵壓縮。
  • 資料和索引都快取在記憶體hash表中。

3.2.1.2 InnoDB Good For

  1. 需要事務的應用。
  2. 高併發的應用。
  3. 自動恢復。
  4. 較快速的基於主鍵的操作。

3.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壓縮功能。

3.2.3 InnoDB伺服器端設定

  • innodb_buffer_pool_size：這是InnoDB最重要的設定，對InnoDB效能有決定性的影響。預設的設定只有8M，所以預設的資料庫設定下面InnoDB效能很差。在只有InnoDB儲存引擎的資料庫伺服器上面，可以設定60-80%的記憶體。更精確一點，在記憶體容量允許的情況下面設定比InnoDB tablespaces大10%的記憶體大小 ( 最小的tablespace 大小為10MB，最大的大小為64TB )。
  • 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來禁止這個限制

3.3 InnoDB vs MyISAM

innoDBvsMyISAM

4. 快取

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

5. MySQL效能優化的最佳實踐

TechTarget中國原創內容，原文連結：http://www.searchdatabase.com.cn/showcontent_38045.htm

今天，資料庫的操作越來越成為整個應用的效能瓶頸了，這點對於Web應用尤其明顯。關於資料庫的效能，這並不只是DBA才需要擔心的事，而這更是我們程式設計師需要去關注的事情。當我們去設計資料庫表結構，對運算元據庫時(尤其是查表時的SQL語句)，我們都需要注意資料操作的效能。這裡，我們不會講過多的SQL語句的優化，而只是針對MySQL這一Web應用最多的資料庫。希望下面的這些優化技巧對你有用。

5.1 為查詢快取優化你的查詢

大多數的MySQL伺服器都開啟了查詢快取。這是提高性最有效的方法之一，而且這是被MySQL的資料庫引擎處理的。當有很多相同的查詢被執行了多次的時候，這些查詢結果會被放到一個快取中，這樣，後續的相同的查詢就不用操作表而直接訪問快取結果了。

這裡最主要的問題是，對於程式設計師來說，這個事情是很容易被忽略的。因為，我們某些查詢語句會讓MySQL不使用快取。請看下面的示例：

// 查詢快取不開啟 
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()"); 

// 開啟查詢快取 
$today = date("Y-m-d"); 
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

上面兩條SQL語句的差別就是 CURDATE() ，MySQL的查詢快取對這個函式不起作用。所以，像 NOW() 和 RAND() 或是其它的諸如此類的SQL函式都不會開啟查詢快取，因為這些函式的返回是會不定的易變的。所以，你所需要的就是用一個變數來代替MySQL的函式，從而開啟快取。

5.2 EXPLAIN 你的 SELECT 查詢

使用 EXPLAIN 關鍵字可以讓你知道MySQL是如何處理你的SQL語句的。這可以幫你分析你的查詢語句或是表結構的效能瓶頸。

EXPLAIN 的查詢結果還會告訴你你的索引主鍵被如何利用的，你的資料表是如何被搜尋和排序的……等等，等等。

挑一個你的SELECT語句(推薦挑選那個最複雜的，有多表聯接的)，把關鍵字EXPLAIN加到前面。你可以使用phpmyadmin來做這個事。然後，你會看到一張表格。下面的這個示例中，我們忘記加上了group_id索引，並且有表聯接：

當我們為 group_id 欄位加上索引後：

我們可以看到，前一個結果顯示搜尋了 7883 行，而後一個只是搜尋了兩個表的 9 和 16 行。檢視rows列可以讓我們找到潛在的效能問題。

5.3 當只要一行資料時使用 LIMIT 1

當你查詢表的有些時候，你已經知道結果只會有一條結果，但因為你可能需要去fetch遊標，或是你也許會去檢查返回的記錄數。

在這種情況下，加上 LIMIT 1 可以增加效能。這樣一樣，MySQL資料庫引擎會在找到一條資料後停止搜尋，而不是繼續往後查少下一條符合記錄的資料。

下面的示例，只是為了找一下是否有"Kimi"的使用者，很明顯，後面的會比前面的更有效率。(請注意，第一條中是Select *，第二條是Select 1)

// 沒有效率的：
$r = mysql_query("SELECT * FROM user WHERE name = 'Kimi'");
if (mysql_num_rows($r) > 0) {  
    // ...
}

// 有效率的：
$r = mysql_query("SELECT 1 FROM user WHERE name = 'Kimi' LIMIT 1");
if (mysql_num_rows($r) > 0) {
    // ...
}

5.4 為搜尋欄位建索引

索引並不一定就是給主鍵或是唯一的欄位。如果在你的表中，有某個欄位你總要會經常用來做搜尋，那麼，請為其建立索引吧。

從上圖你可以看到那個搜尋字串 “last_name LIKE ‘a%’”，一個是建了索引，一個是沒有索引，效能差了4倍左右。

另外，你應該也需要知道什麼樣的搜尋是不能使用正常的索引的。例如，當你需要在一篇大的文章中搜尋一個詞時，如： “WHERE post_content LIKE ‘%apple%’”，索引可能是沒有意義的。你可能需要使用MySQL全文索引 或是自己做一個索引(比如說：搜尋關鍵詞或是Tag什麼的)

5.5 在Join表的時候使用相當型別的例，並將其索引

如果你的應用程式有很多 JOIN 查詢，你應該確認兩個表中Join的欄位是被建過索引的。這樣，MySQL內部會啟動為你優化Join的SQL語句的機制。

而且，這些被用來Join的欄位，應該是相同的型別的。例如：如果你要把 DECIMAL 欄位和一個 INT 欄位Join在一起，MySQL就無法使用它們的索引。對於那些STRING型別，還需要有相同的字符集才行。(兩個表的字符集有可能不一樣)

// 在 state中查詢 company
$r = mysql_query("SELECT company_name FROM users 
LEFT JOIN companies ON (user.state = companies.state) 
WHERE user.id= $user_id");

// 兩個 state 字段應該是被建過索引, 而且是相同的類型, 字符集

5.6 千萬不要 ORDER BY RAND()

想打亂返回的資料行?隨機挑一個資料?真不知道誰發明了這種用法，但很多新手很喜歡這樣用。但你確不瞭解這樣做有多麼可怕的效能問題。

如果你真的想把返回的資料行打亂了，你有N種方法可以達到這個目的。這樣使用只讓你的資料庫的效能呈指數級的下降。這裡的問題是：MySQL會不得不去執行RAND()函式(很耗CPU時間)，而且這是為了每一行記錄去記行，然後再對其排序。就算是你用了Limit 1也無濟於事(因為要排序)

下面的示例是隨機挑一條記錄

// 千萬別這樣做
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");

// 這樣會更好
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0, $d[0]-1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

5.7 避免 SELECT *

從資料庫裡讀出越多的資料，那麼查詢就會變得越慢。並且，如果你的資料庫伺服器和WEB伺服器是兩臺獨立的伺服器的話，這還會增加網路傳輸的負載。

所以，你應該養成一個需要什麼就取什麼的好的習慣。

// 不推薦
$r = mysql_query("SELECT * FROM user WHERE user_id= 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";

// 這樣會更好
$r = mysql_query("SELECT username FROM user WHERE user_id= 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";

5.8 永遠為每張表設定一個ID

我們應該為資料庫裡的每張表都設定一個ID做為其主鍵，而且最好的是一個INT型的(推薦使用UNSIGNED)，並設定上自動增加的AUTO_INCREMENT標誌。

就算是你 users 表有一個主鍵叫 "email"的欄位，你也別讓它成為主鍵。使用 VARCHAR 型別來當主鍵會使用得效能下降。另外，在你的程式中，你應該使用表的ID來構造你的資料結構。

而且，在MySQL資料引擎下，還有一些操作需要使用主鍵，在這些情況下，主鍵的效能和設定變得非常重要，比如，叢集，分割槽……

在這裡，只有一個情況是例外，那就是“關聯表”的“外來鍵”，也就是說，這個表的主鍵，通過若干個別的表的主鍵構成。我們把這個情況叫做“外來鍵”。比如：有一個“學生表”有學生的ID，有一個“課程表”有課程ID，那麼，“成績表”就是“關聯表”了，其關聯了學生表和課程表，在成績表中，學生ID和課程ID叫“外來鍵”其共同組成主鍵。

5.9 使用 ENUM 而不是 VARCHAR

ENUM 型別是非常快和緊湊的。在實際上，其儲存的是 TINYINT，但其外表上顯示為字串。這樣一來，用這個欄位來做一些選項列表變得相當的完美。

如果你有一個欄位，比如"性別"，"國家"，"民族"，"狀態"或"部門"，你知道這些欄位的取值是有限而且固定的，那麼，你應該使用 ENUM 而不是 VARCHAR。

MySQL也有一個“建議”(見第十條)告訴你怎麼去重新組織你的表結構。當你有一個 VARCHAR 欄位時，這個建議會告訴你把其改成 ENUM 型別。使用 PROCEDURE ANALYSE() 你可以得到相關的建議。

5.10 從 PROCEDURE ANALYSE() 取得建議

PROCEDURE ANALYSE() 會讓 MySQL 幫你去分析你的欄位和其實際的資料，並會給你一些有用的建議。只有表中有實際的資料，這些建議才會變得有用，因為要做一些大的決定是需要有資料作為基礎的。

例如，如果你建立了一個 INT 欄位作為你的主鍵，然而並沒有太多的資料，那麼，PROCEDURE ANALYSE()會建議你把這個欄位的型別改成 MEDIUMINT 。或是你使用了一個 VARCHAR 欄位，因為資料不多，你可能會得到一個讓你把它改成 ENUM 的建議。這些建議，都是可能因為資料不夠多，所以決策做得就不夠準。

在phpmyadmin裡，你可以在檢視錶時，點選 “Propose table structure” 來檢視這些建議

一定要注意，這些只是建議，只有當你的表裡的資料越來越多時，這些建議才會變得準確。一定要記住，你才是最終做決定的人。

5.11 儘可能的使用 NOT NULL

除非你有一個很特別的原因去使用 NULL 值，你應該總是讓你的欄位保持 NOT NULL。這看起來好像有點爭議，請往下看。

首先，問問你自己“Empty”和“NULL”有多大的區別(如果是INT，那就是0和NULL)?如果你覺得它們之間沒有什麼區別，那麼你就不要使用NULL。(你知道嗎?在 oracle 裡，NULL 和 Empty 的字串是一樣的!)

不要以為 NULL 不需要空間，其需要額外的空間，並且，在你進行比較的時候，你的程式會更復雜。 當然，這裡並不是說你就不能使用NULL了，現實情況是很複雜的，依然會有些情況下，你需要使用NULL值。

5.12 Prepared Statements

Prepared Statements很像儲存過程，是一種執行在後臺的SQL語句集合，我們可以從使用 prepared statements 獲得很多好處，無論是效能問題還是安全問題。

Prepared Statements 可以檢查一些你繫結好的變數，這樣可以保護你的程式不會受到“SQL隱碼攻擊式”攻擊。當然，你也可以手動地檢查你的這些變數，然而，手動的檢查容易出問題，而且很經常會被程式設計師忘了。當我們使用一些framework或是ORM的時候，這樣的問題會好一些。

在效能方面，當一個相同的查詢被使用多次的時候，這會為你帶來可觀的效能優勢。你可以給這些Prepared Statements定義一些引數，而MySQL只會解析一次。

雖然最新版本的MySQL在傳輸Prepared Statements是使用二進位制形勢，所以這會使得網路傳輸非常有效率。

當然，也有一些情況下，我們需要避免使用Prepared Statements，因為其不支援查詢快取。但據說版本5.1後支援了。

在PHP中要使用prepared statements，你可以檢視其使用手冊：mysqli 擴充套件 或是使用資料庫抽象層，如： PDO.

// 建立 prepared statement
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {
    // 綁定參數
    $stmt->bind_param("s", $state);

    // 執行
    $stmt->execute();

    // 綁定結果
    $stmt->bind_result($username);

    // 移動游標
    $stmt->fetch();

    printf("%s is from %s \n",$username, $state);

    $stmt->close();
}

5.13 無緩衝的查詢

正常的情況下，當你在當你在你的指令碼中執行一個SQL語句的時候，你的程式會停在那裡直到沒這個SQL語句返回，然後你的程式再往下繼續執行。你可以使用無緩衝查詢來改變這個行為。

mysql_unbuffered_query() 傳送一個SQL語句到MySQL而並不像mysql_query()一樣去自動fethch和快取結果。這會相當節約很多可觀的記憶體，尤其是那些會產生大量結果的查詢語句，並且，你不需要等到所有的結果都返回，只需要第一行資料返回的時候，你就可以開始馬上開始工作於查詢結果了。

然而，這會有一些限制。因為你要麼把所有行都讀走，或是你要在進行下一次的查詢前呼叫 mysql_free_result() 清除結果。而且， mysql_num_rows() 或 mysql_data_seek() 將無法使用。所以，是否使用無緩衝的查詢你需要仔細考慮。

5.14 把IP地址存成 UNSIGNED INT

很多程式設計師都會建立一個 VARCHAR(15) 欄位來存放字串形式的IP而不是整形的IP。如果你用整形來存放，只需要4個位元組，並且你可以有定長的欄位。而且，這會為你帶來查詢上的優勢，尤其是當你需要使用這樣的WHERE條件：IP between ip1 and ip2。

我們必需要使用UNSIGNED INT，因為 IP地址會使用整個32位的無符號整形。

而你的查詢，你可以使用 INET_ATON() 來把一個字串IP轉成一個整形，並使用 INET_NTOA() 把一個整形轉成一個字串IP。在php中，也有這樣的函式 ip2long() 和 long2ip()。

PHP:

$r = "UPDATE users set ip = INET_ATON('$_SERVER['REMOTE_ADDR']}') 
WHERE user_id = $user_id";

Go: https://go.dev/play/p/sRTtEgdK6xE

package main

import (
    "encoding/binary"
    "fmt"
    "net"
)

func main() {
    ipAddress := "192.0.2.1" // example IPv4 address

    // parse IP address string
    ip := net.ParseIP(ipAddress)

    // convert to IPv4 address if it is IPv4
    ip = ip.To4()

    if ip == nil {
        return
    }

    // convert to unsigned int
    ipUint32 := binary.BigEndian.Uint32(ip)
    fmt.Println(ipUint32) // 3221225985

    // convert to byte array
    ipBytes := make([]byte, 4)
    binary.BigEndian.PutUint32(ipBytes, ipUint32)

    // convert to net.IP
    ip2 := net.IP(ipBytes)

    // convert to string
    ipAddress2 := ip2.String()
    fmt.Println(ipAddress2) // 192.0.2.1
}

5.15 固定長度的表會更快

如果表中的所有欄位都是“固定長度”的，整個表會被認為是 “static” 或 “fixed-length”。 例如，表中沒有如下型別的欄位： VARCHAR，TEXT，BLOB。只要你包括了其中一個這些欄位，那麼這個表就不是“固定長度靜態表”了，這樣，MySQL 引擎會用另一種方法來處理。

固定長度的表會提高效能，因為MySQL搜尋得會更快一些，因為這些固定的長度是很容易計算下一個資料的偏移量的，所以讀取的自然也會很快。而如果欄位不是定長的，那麼，每一次要找下一條的話，需要程式找到主鍵。

並且，固定長度的表也更容易被快取和重建。不過，唯一的副作用是，固定長度的欄位會浪費一些空間，因為定長的欄位無論你用不用，他都是要分配那麼多的空間。

使用“垂直分割”技術(見下一條)，你可以分割你的表成為兩個一個是定長的，一個則是不定長的。

5.16 垂直分割

“垂直分割”是一種把資料庫中的表按列變成幾張表的方法，這樣可以降低表的複雜度和欄位的數目，從而達到優化的目的。(以前，在銀行做過專案，見過一張表有100多個欄位，很恐怖)

示例一：在Users表中有一個欄位是家庭地址，這個欄位是可選欄位，相比起，而且你在資料庫操作的時候除了個人資訊外，你並不需要經常讀取或是改寫這個欄位。那麼，為什麼不把他放到另外一張表中呢? 這樣會讓你的表有更好的效能，大家想想是不是，大量的時候，我對於使用者表來說，只有使用者ID，使用者名稱，口令，使用者角色等會被經常使用。小一點的表總是會有好的效能。

示例二： 你有一個叫 “last_login” 的欄位，它會在每次使用者登入時被更新。但是，每次更新時會導致該表的查詢快取被清空。所以，你可以把這個欄位放到另一個表中，這樣就不會影響你對使用者ID，使用者名稱，使用者角色的不停地讀取了，因為查詢快取會幫你增加很多效能。

另外，你需要注意的是，這些被分出去的欄位所形成的表，你不會經常性地去Join他們，不然的話，這樣的效能會比不分割時還要差，而且，會是極數級的下降。

5.17 拆分大的 DELETE 或 INSERT 語句

如果你需要在一個線上的網站上去執行一個大的 DELETE 或 INSERT 查詢，你需要非常小心，要避免你的操作讓你的整個網站停止相應。因為這兩個操作是會鎖表的，表一鎖住了，別的操作都進不來了。

Apache 會有很多的子程序或執行緒。所以，其工作起來相當有效率，而我們的伺服器也不希望有太多的子程序，執行緒和資料庫連結，這是極大的佔伺服器資源的事情，尤其是記憶體。

如果你把你的表鎖上一段時間，比如30秒鐘，那麼對於一個有很高訪問量的站點來說，這30秒所積累的訪問程序/執行緒，資料庫連結，開啟的檔案數，可能不僅僅會讓你泊WEB服務Crash，還可能會讓你的整臺伺服器馬上掛了。

所以，如果你有一個大的處理，你定你一定把其拆分，使用 LIMIT 條件是一個好的方法。下面是一個示例：

while(1){
    // 每次只做1000條
    mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
    if (mysql_affected_rows() == 0) {
        // 沒得刪了 退出
        break;
    }
    // 每次都要休息一會
    usleep(50000);
}

5.18 越小的列會越快

對於大多數的資料庫引擎來說，硬碟操作可能是最重大的瓶頸。所以，把你的資料變得緊湊會對這種情況非常有幫助，因為這減少了對硬碟的訪問。

參看 MySQL 的文件 Storage Requirements 檢視所有的資料型別。

如果一個表只會有幾列罷了(比如說字典表，配置表)，那麼，我們就沒有理由使用 INT 來做主鍵，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 會更經濟一些。如果你不需要記錄時間，使用 DATE 要比 DATETIME 好得多。

當然，你也需要留夠足夠的擴充套件空間，不然，你日後來幹這個事，你會死的很難看，參看Slashdot的例子(2009年11月06日)，一個簡單的ALTER TABLE語句花了3個多小時，因為裡面有一千六百萬條資料。

5.19 選擇正確的儲存引擎

在 MySQL 中有兩個儲存引擎 MyISAM 和 InnoDB，每個引擎都有利有弊。酷殼以前文章《MySQL: InnoDB 還是 MyISAM?》討論和這個事情。

MyISAM 適合於一些需要大量查詢的應用，但其對於有大量寫操作並不是很好。甚至你只是需要update一個欄位，整個表都會被鎖起來，而別的程序，就算是讀程序都無法操作直到讀操作完成。另外，MyISAM 對於 SELECT COUNT(*) 這類的計算是超快無比的。

InnoDB 的趨勢會是一個非常複雜的儲存引擎，對於一些小的應用，它會比 MyISAM 還慢。他是它支援“行鎖” ，於是在寫操作比較多的時候，會更優秀。並且，他還支援更多的高階應用，比如：事務。

下面是MySQL的手冊

target=”_blank”MyISAM Storage Engine

InnoDB Storage Engine

5.20 使用一個物件關係對映器(Object Relational Mapper)

使用 ORM (Object Relational Mapper)，你能夠獲得可靠的效能增漲。一個ORM可以做的所有事情，也能被手動的編寫出來。但是，這需要一個高階專家。

ORM 的最重要的是“Lazy Loading”，也就是說，只有在需要的去取值的時候才會去真正的去做。但你也需要小心這種機制的副作用，因為這很有可能會因為要去建立很多很多小的查詢反而會降低效能。

ORM 還可以把你的SQL語句打包成一個事務，這會比單獨執行他們快得多得多。

目前，個人最喜歡的PHP的ORM是：Doctrine。

5.21 小心“永久連結”

“永久連結”的目的是用來減少重新建立MySQL連結的次數。當一個連結被建立了，它會永遠處在連線的狀態，就算是資料庫操作已經結束了。而且，自從我們的Apache開始重用它的子程序後——也就是說，下一次的HTTP請求會重用Apache的子程序，並重用相同的 MySQL 連結。

PHP手冊：mysql_pconnect()

在理論上來說，這聽起來非常的不錯。但是從個人經驗(也是大多數人的)上來說，這個功能製造出來的麻煩事更多。因為，你只有有限的連結數，記憶體問題，檔案控制代碼數，等等。

而且，Apache 執行在極端並行的環境中，會建立很多很多的了程序。這就是為什麼這種“永久連結”的機制工作地不好的原因。在你決定要使用“永久連結”之前，你需要好好地考慮一下你的整個系統的架構。

5.22 當查詢較慢的時候，可用Join來改寫一下該查詢來進行優化

  1. 盡可能從索引中直接獲取資料, 避免或減少直接掃描行資料頻率
  2. 盡可能減少掃描的紀錄數, 也就是先確定起始範圍, 再往後取 n 條
  3. Sub Query 只抓 id 只需少量的記憶體來排序
  4. 優先推薦使用INNER JOIN方式優化分頁算法
  5. 小表驅動大表
# 優化前
SELECT * FROM tableA WHERE op='1' and name='QQ' ORDER BY op DESC;

# 優化後
SELECT * FROM tableA as t 
INNER JOIN 
(SELECT op from tableA where op='1' and name ='QQ') as t2 ON t2.op = t.op
ORDER BY t.op DESC;

原文連結 : https://blog.csdn.net/yejr/article/details/70039403

# 從 935500 找 10 個
SELECT * FROM tableA WHERE ftype=1 ORDER BY id DESC LIMIT 935500,10;

# 子查詢優化
SELECT * FROM (SELECT * FROM tableA WHERE id >
(SELCET id FROM tableA WHERE ftype=1 ORDER BY id DESC LIMIT 935500,1) LIMIT 10)
ORDER BY id DESC;

# INNER JOIN 優化(較快)
SELECT *FROM tableA INNER JOIN (
SELECT id from tableA WHERE ftype=1 ORDER BY id DESC LIMIT 935500,10
) t2 
USING(id);
大分頁，帶WHERE 大分頁，不帶WHERE 大分頁平均提升比例 小分頁，帶WHERE 小分頁，不帶WHERE 總體平均提升比例
子查詢優化 28.20% 10.60% 19.40% 24.90% 554.40% 154.53%
INNER JOIN 優化 30.80% 30.20% 30.50% 156.50% 11.70% 57.30%

作者：KeerDi —— 北方的后生\ \ 出处：http://www.cnblogs.com/keerdi/\ \ 本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。

6. 《MySQL实战45讲》--丁奇

7. Reference

  1. https://www.cnblogs.com/123hll/p/7026309.html
  2. https://zh.wikipedia.org/wiki/ACID
  3. http://www.mysqlperformanceblog.com/
  4. Advanced MySQL Performance Optimization, Peter Zaitsev, Tobias Asplund, MySQL Users Conference 2005
  5. Improving MySQL Server Performance with Intel C++ Compiler，Peter Zaitsev，linux World 2005
  6. MySQL Performance Optimization, Peter Zaitsev, Percona Ltd, OPEN SOURCE DATABASE CONFERENCE 2006
  7. MySQL Server Settings Tuning, Peter Zaitsev, co-founder, Percona Ltd, 2007
  8. MySQL Reference Manual
  9. http://www.searchdatabase.com.cn/showcontent_38045.htm
  10. https://my.oschina.net/yejr/blog/878400
  11. 索引不是越多越好，理解索引结构原理，才有助于我们建立合适的索引！
© Kimi Tsai all right reserved.            Updated : 2023-07-12 09:04:53

results matching ""

    No results matching ""

    results matching ""

      No results matching ""