MySQL 海量資料優化（理論+實戰） 吊打面試官

一、準備表資料

咱們建一張使用者表，表中的欄位有使用者ID、使用者名稱、地址、記錄建立時間，如圖所示

OK，接下來準備寫一個儲存過程插入一百萬條資料

CREATE TABLE `t_user` (
  `id` int NOT NULL,
  `user_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DELIMITER ;;
CREATE PROCEDURE user_insert()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i<1000000
DO
INSERT INTO t_user(id, user_name, address,  create_time) VALUES (i, CONCAT('mayun',i), '浙江杭州', now());
SET i=i+1;
END WHILE ;
commit;
END;;
CALL user_insert();

插入完後咱們看看資料條數

二、優化方式

1.分頁查詢優化

OK，咱們看下分頁limit到一定值時的耗時是多少

• limit 1000時

• limit 10000時

• limit 100000時

• limit 1000000時

可以看到limit值越大，耗時越長，這還只是一百萬資料，要是千萬級、億級呢？

OK不廢話，咱們馬上進行分頁優化

• 子查詢優化

可以看到比起之前 limit 1000000時的0.218s 效率提高了很多

• 使用JOIN分頁

可以看到比起之前 limit 1000000時的0.218s 效率也同樣提高了很多

• 使用前一次查詢的最大ID

可以看到這種方法效率最高，但依賴於需要知道最大ID，這種適合點選下一頁查詢（類似於滾動載入資料）的場景

• 通過偽列對ID進行分頁

然後可以開啟多個執行緒去進行最高效率查詢語句的批量查詢操作 0~10000，10001-20000.... 這樣子的話可以快速把全量資料查詢出來同步至快取中。

分頁優化總結：使用前一次查詢的最大ID進行查詢優化是效率最高的方法，但這種方法只適用於下一頁點選的這種操作，對於同步全量資料來說建議的方式使用偽列對ID進行分頁，然後開啟多個執行緒同時查詢，把全量資料載入到快取，以後面試官問你如何  快速獲取海量資料並載入到快取 你該知道怎麼回答了吧。

2.普通索引優化

先來看沒索引優化的情況下的查詢效率

可以看到這時沒用索引的情況，用了0.305S接下來看看加了索引後的結果

• 普通索引優化

只需要0.024S，我們可以EXPLAIN看下

可以看到使用了普通索引後查詢效率明顯增加

3.複合索引優化

複合索引什麼時候用？為什麼要用？圍繞著這兩問題，咱們先來說說複合索引什麼時候用

1. 單表中查詢、條件語句中具有較多個欄位

2. 使用索引會影響寫的效率，需要研究建立最優秀的索引

我們這裡建議一個複合索引

MySQL建立複合索引時實際建立了(user_name)、（user_name,address）、(user_name,address,create_time)三個索引,我們都知道每多一個索引，都會增加寫操作的開銷和磁碟空間的開銷，對於海量資料的表，這可是不小的開銷，所以你會發現我們在這裡使用複合索引一個頂三個，又能減少寫操作的開銷和磁碟空間的開銷。

當我們select user_name,address,create_time from t_user where user_name=xx and address = xxx時，MySQL可以直接通過遍歷索引取得資料，無需回表，這減少了很多的隨機IO操作。所以，在真正的實際應用中，這就是覆蓋索引，是複合索引中主要的提升效能的優化手段之一。

4.SQL查詢優化

1. 避免使用OR，看看例子

可以看到這條語句沒有使用到索引，是因為當or左右查詢欄位只有一個是索引，該索引失效，只有當or左右查詢欄位均為索引時，才會生效。

2. 不要使用like '%xx' %在左邊時索引失效

3. 使用複合索引時沒有遵循最左匹配原則

ref:這個連線型別只有在查詢使用了不是惟一或主鍵的鍵或者是這些型別的部分（比如，利用最左邊字首）時發生。 沒有值說明沒有利用最左字首原則

再來看個使用了最左字首的例子

4. 不要讓資料型別出現隱式轉化

可以看以下兩個例子

5. 不要在索引欄位上使用not，<>，!=，一樣會導致索引失效

6. 分解關聯查詢 例如這條語句

可以分解成

7.小表驅動大表 即小的資料集驅動大的資料集。如：以t_user，t_order兩表為例，兩表通過 t_user的id欄位進行關聯。

當 t_order表的資料集小於t_user表時,用 in 優化 exist,使用 in,兩表執行順序是先查 t_order 表,再查t_user表
select * from t_user where id in (select user_id from t_order)
 
當 t_user 表的資料集小於 t_order 表時，用 exist 優化 in,使用 exists,兩表執行順序是先查 t_user  表,再查 t_order  表
select * from t_user where exists (select 1 from B where t_order.user_id= t_user.id)

5.事務優化

首先了解下事務的隔離級別，資料庫共定義了四種隔離級別：

1. Serializable：可避免髒讀、不可重複讀、虛讀情況的發生。（序列化）

2. Repeatable read：可避免髒讀、不可重複讀情況的發生。（可重複讀）

3. Read committed：可避免髒讀情況發生（讀已提交）。

4. Read uncommitted：最低級別，以上情況均無法保證。(讀未提交)

可以通過 set transaction isolation level 設定事務隔離級別來提高效能

6.資料庫效能優化

開啟查詢快取

• 在解析一個查詢語句前，如果查詢快取是開啟的，那麼MySQL會檢查這個查詢語句是否命中查詢快取中的資料。如果當前查詢恰好命中查詢快取，在檢查一次使用者許可權後直接返回快取中的結果。這種情況下，查詢不會被解析，也不會生成執行計劃，更不會執行。MySQL將快取存放在一個引用表（不要理解成table，可以認為是類似於HashMap的資料結構），通過一個雜湊值索引，這個雜湊值通過查詢本身、當前要查詢的資料庫、客戶端協議版本號等一些可能影響結果的資訊計算得來。所以兩個查詢在任何字元上的不同（例如：空格、註釋），都會導致快取不會命中。

• 如果查詢中包含任何使用者自定義函式、儲存函式、使用者變數、臨時表、mysql庫中的系統表，其查詢結果都不會被快取。比如函式NOW()或者CURRENT_DATE()會因為不同的查詢時間，返回不同的查詢結果，再比如包含CURRENT_USER或者CONNECION_ID()的查詢語句會因為不同的使用者而返回不同的結果，將這樣的查詢結果快取起來沒有任何的意義。

• 既然是快取，就會失效，那查詢快取何時失效呢？MySQL的查詢快取系統會跟蹤查詢中涉及的每個表，如果這些表（資料或結構）發生變化，那麼和這張表相關的所有快取資料都將失效。正因為如此，在任何的寫操作時，MySQL必須將對應表的所有快取都設定為失效。如果查詢快取非常大或者碎片很多，這個操作就可能帶來很大的系統消耗，甚至導致系統僵死一會兒。而且查詢快取對系統的額外消耗也不僅僅在寫操作，讀操作也不例外：

• 任何的查詢語句在開始之前都必須經過檢查，即使這條SQL語句永遠不會命中快取  

• 如果查詢結果可以被快取，那麼執行完成後，會將結果存入快取，也會帶來額外的系統消耗 

• 基於此，我們要知道並不是什麼情況下查詢快取都會提高系統性能，快取和失效都會帶來額外消耗，只有當快取帶來的資源節約大於其本身消耗的資源時，才會給系統帶來效能提升。 但要如何評估開啟快取是否能夠帶來效能提升是一件非常困難的事情，也不在本文討論的範疇內。 如果系統確實存在一些效能問題，可以嘗試開啟查詢快取，並在資料庫設計上做一些優化，比如：

• 批量插入代替迴圈單條插入   . 合理控制快取空間大小，一般來說其大小設定為幾十兆比較合適   . 可以通過SQL\_CACHE和SQL\_NO\_CACHE來控制某個查詢語句是否需要進行快取   最後的忠告是不要輕易開啟查詢快取，特別是寫密集型應用。 如果你實在是忍不住，可以將query\_cache\_type設定為DEMAND，這時只有加入SQL\_CACHE的查詢才會走快取，其他查詢則不會，這樣可以非常自由地控制哪些查詢需要被快取。    當然查詢快取系統本身是非常複雜的，這裡討論的也只是很小的一部分，其他更深入的話題，比如： 快取是如何使用記憶體的？ 如何控制記憶體的碎片化？ 事務對查詢快取有何影響等等，讀者可以自行閱讀相關資料，這裡權當拋磚引玉吧。     **語法解析和預處理**

• MySQL通過關鍵字將SQL語句進行解析，並生成一顆對應的解析樹。 這個過程解析器主 要通過語法規則來驗證和解析。 比如SQL中是否使用了錯誤的關鍵字或者關鍵字的順序是否正確等等。 預處理則會根據MySQL規則進一步檢查解析樹是否合法。 比如檢查要查詢的資料表和資料列是否存在等等。

• 7.系統核心引數優化

```bash
#基礎配置
datadir=/data/datafile
socket=/var/lib/mysql/mysql.sock
log-error=/data/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character_set_server=utf8
#允許任意IP訪問
bind-address = 0.0.0.0
#是否支援符號連結，即資料庫或表可以儲存在my.cnf中指定datadir之外的分割槽或目錄，為0不開啟
#symbolic-links=0
#支援大小寫
lower_case_table_names=1
#二進位制配置
server-id = 1
log-bin = /data/log/mysql-bin.log
log-bin-index =/data/log/binlog.index
log_bin_trust_function_creators=1
expire_logs_days=7
#sql_mode定義了mysql應該支援的sql語法，資料校驗等
#mysql5.0以上版本支援三種sql_mode模式：ANSI、TRADITIONAL和STRICT_TRANS_TABLES。
#ANSI模式：寬鬆模式，對插入資料進行校驗，如果不符合定義型別或長度，對資料型別調整或截斷儲存，報warning警告。
#TRADITIONAL模式：嚴格模式，當向mysql資料庫插入資料時，進行資料的嚴格校驗，保證錯誤資料不能插入，報error錯誤。用於事物時，會進行事物的回滾。
#STRICT_TRANS_TABLES模式：嚴格模式，進行資料的嚴格校驗，錯誤資料不能插入，報error錯誤。
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#InnoDB儲存資料字典、內部資料結構的緩衝池，16MB已經足夠大了。
innodb_additional_mem_pool_size = 16M
#InnoDB用於快取資料、索引、鎖、插入緩衝、資料字典等
#如果是專用的DB伺服器，且以InnoDB引擎為主的場景，通常可設定實體記憶體的60%
#如果是非專用DB伺服器，可以先嚐試設定成記憶體的1/4
innodb_buffer_pool_size = 4G
#InnoDB的log buffer，通常設定為 64MB 就足夠了
innodb_log_buffer_size = 64M
#InnoDB redo log大小，通常設定256MB 就足夠了
innodb_log_file_size = 256M
#InnoDB redo log檔案組，通常設定為 2 就足夠了
innodb_log_files_in_group = 2
#共享表空間:某一個數據庫的所有的表資料，索引檔案全部放在一個檔案中，預設這個共享表空間的檔案路徑在data目錄下。預設的檔名為:ibdata1 初始化為10M。
#獨佔表空間:每一個表都將會生成以獨立的檔案方式來進行儲存，每一個表都有一個.frm表描述檔案，還有一個.ibd檔案。其中這個檔案包括了單獨一個表的資料內容以及索引內容，預設情況下它的儲存位置也是在表的位置之中。
#設定引數為1啟用InnoDB的獨立表空間模式，便於管理
innodb_file_per_table = 1
#InnoDB共享表空間初始化大小，預設是 10MB，改成 1GB，並且自動擴充套件
innodb_data_file_path = ibdata1:1G:autoextend
#設定臨時表空間最大4G
innodb_temp_data_file_path=ibtmp1:500M:autoextend:max:4096M
#啟用InnoDB的status file，便於管理員檢視以及監控
innodb_status_file = 1
#當設定為0，該模式速度最快，但不太安全，mysqld程序的崩潰會導致上一秒鐘所有事務資料的丟失。
#當設定為1，該模式是最安全的，但也是最慢的一種方式。在mysqld 服務崩潰或者伺服器主機crash的情況下，binary log 只有可能丟失最多一個語句或者一個事務。
#當設定為2，該模式速度較快，也比0安全，只有在作業系統崩潰或者系統斷電的情況下，上一秒鐘所有事務資料才可能丟失。
innodb_flush_log_at_trx_commit = 1
#設定事務隔離級別為 READ-COMMITED，提高事務效率，通常都滿足事務一致性要求
#transaction_isolation = READ-COMMITTED
#max_connections：針對所有的賬號所有的客戶端並行連線到MYSQL服務的最大並行連線數。簡單說是指MYSQL服務能夠同時接受的最大並行連線數。
#max_user_connections : 針對某一個賬號的所有客戶端並行連線到MYSQL服務的最大並行連線數。簡單說是指同一個賬號能夠同時連線到mysql服務的最大連線數。設定為0表示不限制。
#max_connect_errors：針對某一個IP主機連線中斷與mysql服務連線的次數，如果超過這個值，這個IP主機將會阻止從這個IP主機發送出去的連線請求。遇到這種情況，需執行flush hosts。
#執行flush host或者 mysqladmin flush-hosts，其目的是為了清空host cache裡的資訊。可適當加大，防止頻繁連線錯誤後，前端host被mysql拒絕掉
#在 show global 裡有個系統狀態Max_used_connections,它是指從這次mysql服務啟動到現在，同一時刻並行連線數的最大值。它不是指當前的連線情況，而是一個比較值。如果在過去某一個時刻，MYSQL服務同時有10
00個請求連線過來，而之後再也沒有出現這麼大的併發請求時，則Max_used_connections=1000.請注意與show variables 裡的max_user_connections的區別。#Max_used_connections / max_connections * 100% ≈ 85%
max_connections=600
max_connect_errors=1000
max_user_connections=400
#設定臨時表最大值，這是每次連線都會分配，不宜設定過大 max_heap_table_size 和 tmp_table_size 要設定一樣大
max_heap_table_size = 100M
tmp_table_size = 100M
#每個連線都會分配的一些排序、連線等緩衝，一般設定為 2MB 就足夠了
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
#建議關閉query cache，有些時候對效能反而是一種損害
query_cache_size = 0
#如果是以InnoDB引擎為主的DB，專用於MyISAM引擎的 key_buffer_size 可以設定較小，8MB 已足夠
#如果是以MyISAM引擎為主，可設定較大，但不能超過4G
key_buffer_size = 8M
#設定連線超時閥值，如果前端程式採用短連線，建議縮短這2個值，如果前端程式採用長連線，可直接註釋掉這兩個選項，是用預設配置(8小時)
#interactive_timeout = 120
#wait_timeout = 120
#InnoDB使用後臺執行緒處理資料頁上讀寫I/0請求的數量,允許值的範圍是1-64
#假設CPU是2顆4核的，且資料庫讀操作比寫操作多，可設定
#innodb_read_io_threads=5
#innodb_write_io_threads=3
#通過show engine innodb status的FILE I/O選項可檢視到執行緒分配
#設定慢查詢閥值，單位為秒
long_query_time = 120
slow_query_log=1 #開啟mysql慢sql的日誌
log_output=table,File #日誌輸出會寫表，也會寫日誌檔案，為了便於程式去統計，所以最好寫表
slow_query_log_file=/data/log/slow.log
##針對log_queries_not_using_indexes開啟後，記錄慢sql的頻次、每分鐘記錄的條數
#log_throttle_queries_not_using_indexes = 5
##作為從庫時生效,從庫複製中如何有慢sql也將被記錄
#log_slow_slave_statements = 1
##檢查未使用到索引的sql
#log_queries_not_using_indexes = 1
#快速預熱緩衝池
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
#列印deadlock日誌
innodb_print_all_deadlocks=1

這些引數可按照自己的實際伺服器以及資料庫的大小進行適當調整，主要起參考作用

8.表字段優化

很多系統一開始並沒有考慮表字段拆分的問題，因為拆分會帶來邏輯、部署、運維的各種複雜度，一般以整型值為主的表在千萬級以下，字串為主的表在五百萬以下，而事實上很多時候MySQL單表的效能依然有不少優化空間，甚至能正常支撐千萬級以上的資料量：

下面直接看下如何去優化欄位

1. 儘量使用TINYINT、SMALLINT、MEDIUM_INT作為整數型別而非INT，如果非負則加上UNSIGNED

2. 單表不要有太多欄位，建議在15以內

3. 儘量使用TIMESTAMP而非DATETIME

4. 使用列舉或整數代替字串型別

5. VARCHAR的長度只分配真正需要的空間

6. 避免使用NULL欄位，很難查詢優化且佔用額外索引空間

7. 用整型來存IP

9.分散式場景下常用優化手段

1. 升級硬體

Scale up，這個不多說了，根據MySQL是CPU密集型還是I/O密集型，通過提升CPU和記憶體、使用SSD，都能顯著提升MySQL效能

2. 讀寫分離

也是目前常用的優化，從庫讀主庫寫，一般不要採用雙主或多主引入很多複雜性，儘量採用文中的其他方案來提高效能。同時目前很多拆分的解決方案同時也兼顧考慮了讀寫分離

3. 使用快取

   快取可以發生在這些層次：

   MySQL內部：在系統核心引數優化介紹了相關設定

   資料訪問層：比如MyBatis針對SQL語句做快取，而Hibernate可以精確到單個記錄，這裡快取的物件主要是持久化物件Persistence Object

   應用服務層：這裡可以通過程式設計手段對快取做到更精準的控制和更多的實現策略，這裡快取的物件是資料傳輸物件Data Transfer Object

   Web層：針對web頁面做快取

   瀏覽器客戶端：使用者端的快取

   可以根據實際情況在一個層次或多個層次結合加入快取。這裡重點介紹下服務層的快取實現，目前主要有兩種方式：

   直寫式（Write Through）：在資料寫入資料庫後，同時更新快取，維持資料庫與快取的一致性。這也是當前大多數應用快取框架如Spring Cache的工作方式。這種實現非常簡單，同步好，但效率一般。

   回寫式（Write Back）：當有資料要寫入資料庫時，只會更新快取，然後非同步批量的將快取資料同步到資料庫上。這種實現比較複雜，需要較多的應用邏輯，同時可能會產生資料庫與快取的不同步，但效率非常高。

4. 水平拆分。

總結

其實MySQL的優化還有很多，有興趣的可以讀讀MySQL高效能優化的書，但以上這些是在我們實際生產環境中比較常用的優化手段，掌握這些，不是我吹，能吊打一般的面試官了。

原始碼地址：http://gitee.com/kobeboy/hyh-boot