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