一般現(xiàn)在對于業(yè)務(wù)要查詢的數(shù)據(jù)量以及要保持的并發(fā)量高于一定配置的單實例 MySQL 的極限的情況，都會采取分庫分表的方案解決。當(dāng)然，現(xiàn)在也有很多 new SQL 的分布式數(shù)據(jù)庫的解決方案，如果你用的是 MySQL，那么你可以考慮 TiDB(實現(xiàn)了 MySQL 協(xié)議，兼容 MySQL 客戶端以及 SQL 語句)。如果你用的是的 PgSQL，那么你可以考慮使用 YugaByteDB(實現(xiàn)了 PgSQL 協(xié)議，兼容 PgSQL 客戶端以及 SQL 語句)，他們目前都有自己的云部署解決方案，你可以試試：

• TiDB Cloud。
• YugaByte Cloud。

但是對于傳統(tǒng)分庫分表的項目，底層的數(shù)據(jù)庫還是基于 MySQL 以及 PgSQL 這樣的傳統(tǒng)關(guān)系型數(shù)據(jù)庫。一般在業(yè)務(wù)剛開始的時候，會考慮按照某個分片鍵多分一些表，例如訂單表，我們估計用戶直接要查的訂單記錄是最近一年內(nèi)的。如果是一年前的，提供其他入口去查，這時候查的就不是有業(yè)務(wù)數(shù)據(jù)庫了，而是歸檔數(shù)據(jù)庫，例如 HBase 這樣的。例如我們估計一年內(nèi)用戶訂單，最多不會超過 10 億，更新的并發(fā) TPS (非查詢 QPS)不會超過 10 萬/s。那么我們可以考慮分成 64 張表(個數(shù)最好是 2^n，因為 2^n 取余數(shù) = 對 2^n - 1 取與運算，減少分片鍵運算量)。然后我們還會定時的歸檔掉一年前的數(shù)據(jù)，使用類似于 delete from table 這樣的語句進行“徹底刪除”(注意這里是引號的刪除)。這樣保證業(yè)務(wù)表的數(shù)據(jù)量級一直維持在

然而，日久天長以后，會發(fā)現(xiàn)，某些帶分片鍵(這里就是用戶 id)的普通查詢，也會有些慢，有些走錯本地索引。

查詢越來越慢的原因

例如這個 SQL：

select * from t_pay_record
WHERE
((
  user_id = 'user_id1' 
  AND is_del = 0 
)) 
ORDER BY
  id DESC 
  LIMIT 201.2.3.4.5.6.7.8.9.

這個表的分片鍵就是 user_id。

一方面，正如我在“為什么我建議在復(fù)雜但是性能關(guān)鍵的表上所有查詢都加上 force index”中說的，數(shù)據(jù)量可能有些超出我們的預(yù)期，導(dǎo)致某些分片表大于一定界限，導(dǎo)致 MySQL 對于索引的隨機采樣越來越不準(zhǔn)，由于統(tǒng)計數(shù)據(jù)不是實時更新，而是更新的行數(shù)超過一定比例才會開始更新。并且統(tǒng)計數(shù)據(jù)不是全量統(tǒng)計，是抽樣統(tǒng)計。所以在表的數(shù)據(jù)量很大的時候，這個統(tǒng)計數(shù)據(jù)很難非常準(zhǔn)確。依靠表本身自動刷新數(shù)據(jù)機制，參數(shù)比較難以調(diào)整(主要是 STATS_SAMPLE_PAGES 這個參數(shù)，STATS_PERSISTENT 我們一般不會改，我們不會能接受在內(nèi)存中保存，這樣萬一數(shù)據(jù)庫重啟，表就要重新分析，這樣減慢啟動時間，STATS_AUTO_RECALC 我們也不會關(guān)閉，這樣會導(dǎo)致優(yōu)化器分析的越來越不準(zhǔn)確)，很難預(yù)測出到底調(diào)整到什么數(shù)值最合適。并且業(yè)務(wù)的增長，用戶的行為導(dǎo)致的數(shù)據(jù)的傾斜，也是很難預(yù)測的。通過 Alter Table 修改某個表的 STATS_SAMPLE_PAGES 的時候，會導(dǎo)致和 Analyze 這個 Table 一樣的效果，會在表上加讀鎖，會阻塞表上的更新以及事務(wù)。所以不能在這種在線業(yè)務(wù)關(guān)鍵表上面使用。所以最好一開始就能估計出大表的量級，但是這個很難。

所以，我們考慮對于數(shù)據(jù)量比較大的表，最好能提前通過分庫分表控制每個表的數(shù)據(jù)量，但是業(yè)務(wù)增長與產(chǎn)品需求都是不斷在迭代并且變復(fù)雜的。很難保證不會出現(xiàn)大并且索引比較復(fù)雜的表。這種情況下需要我們，在適當(dāng)調(diào)高 STATS_SAMPLE_PAGES 的前提下，對于一些用戶觸發(fā)的關(guān)鍵查詢 SQL，使用 force index 引導(dǎo)它走正確的索引。

但是，有時候即使索引走對了，查詢依然有點慢。具體去看這個 SQL 掃描的數(shù)據(jù)行數(shù)的時候，發(fā)現(xiàn)并沒有很多。

+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+-------------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys                                                                           | key         | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+-------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_pay_record | NULL       | index | idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del | idx_user_id | 32      | NULL |   16  |     0.01 | Using where |
+----+-------------+--------------+------------+-------+-----------------------------------------------------------------------------------------+-------------+---------+------+-------+----------+-------------+1.2.3.4.5.

可能還是會有偶現(xiàn)的這樣的慢 SQL，并且隨著時間推移越來越多，這個就和 MySQL InnoDB 里面的刪除機制有關(guān)系了。目前大部分業(yè)務(wù)表都用的 InnoDB 引擎，并且都用的默認(rèn)的行格式 Dynamic，在這種行格式下我們在插入一條數(shù)據(jù)的時候，其結(jié)構(gòu)大概如下所示：

記錄頭中，有刪除標(biāo)記：

當(dāng)發(fā)生導(dǎo)致記錄長度變化的更新時，例如變長字段實際數(shù)據(jù)變得更長這種，會將原來的記錄標(biāo)記為刪除，然后在末尾創(chuàng)建更新后的記錄。當(dāng)刪除一條記錄的時候，也是只是標(biāo)記記錄頭的刪除標(biāo)記。

對于這種可能的碎片化，MySQL InnoDB 也是有期望并且措施的，即每個頁面 InnoDB 引擎只會存儲占用 93% 空間的數(shù)據(jù)，剩下的就是為了能讓長度變化的更新不會導(dǎo)致數(shù)據(jù)跑到其他頁面。但是相對的，如果 Delete 就相當(dāng)于完全浪費了存儲空間了。

一般情況下這種不會造成太大的性能損耗，因為刪除一般是刪的老的數(shù)據(jù)，更新一般集中在最近的數(shù)據(jù)。例如訂單發(fā)生更新，一般是時間最近的訂單才會更新，很少會有很久前的訂單基本不會更新，并且歸檔刪除的一般也是很久之前的訂單。但是隨著業(yè)務(wù)越來越復(fù)雜，歸檔邏輯也越來越復(fù)雜，比如不同類型的訂單時效不一樣，可能出現(xiàn)一年前還有未結(jié)算的預(yù)購訂單不能歸檔。久而久之，你的數(shù)據(jù)可能會變成這樣：

這樣導(dǎo)致，原來你需要掃描很少頁的數(shù)據(jù)，隨著時間的推移，碎片越來越多，要掃描的頁越來越多，這樣 SQL 執(zhí)行會越來越慢。

以上是對于表本身數(shù)據(jù)存儲的影響，對于二級索引，由于 MVCC 機制的存在，導(dǎo)致頻繁更新索引字段會對索引也造成很多空洞。參考文檔：

https://dev.mysql.com/doc/refman/8.0/en/innodb-multi-versioning.html。

InnoDB multiversion concurrency control (MVCC) treats secondary indexes differently than clustered indexes. Records in a clustered index are updated in-place, and their hidden system columns point undo log entries from which earlier versions of records can be reconstructed. Unlike clustered index records, secondary index records do not contain hidden system columns nor are they updated in-place.

我們知道，MySQL InnoDB 對于聚簇索引是在索引原始位置上進行更新，對于二級索引，如果二級索引列發(fā)生更新則是在原始記錄上打上刪除標(biāo)記，然后在新的地方記錄。這樣和之前一樣，會造成很多存儲碎片。

綜上所述：

1. MySQL InnoDB 的會改變記錄長度的 Dynamic 行格式記錄 Update，以及 Delete 語句，其實是原有記錄的刪除標(biāo)記打標(biāo)記。雖然 MySQL InnoDB 對于這個有做預(yù)留空間的優(yōu)化，但是日積月累，隨著歸檔刪除數(shù)據(jù)的增多，會有很多內(nèi)存碎片降低掃描效率。
2. MVCC 機制對于二級索引列的更新，是在原始記錄上打上刪除標(biāo)記，然后在新的地方記錄，導(dǎo)致二級索引的掃描效率也隨著時間積累而變慢。

解決方案 - 重建表

對于這種情況，我們可以通過重建表的方式解決。重建表其實是一舉兩得的行為：第一可以優(yōu)化這種存儲碎片，減少要掃描的行數(shù);第二可以重新 analyze 讓 SQL 優(yōu)化器采集數(shù)據(jù)更準(zhǔn)確。

在 MySQL 5.6.17 之前，我們需要借助外部工具 pt-online-schema-change 來幫助我們完成表的重建，pt-online-schema-change 工具的原理其實就是內(nèi)部新建表，在原表上加好觸發(fā)器同步更新到新建的表，并且同時復(fù)制數(shù)據(jù)到新建的表中，完成后，獲取全局鎖修改新建的表名字為原來的表名字，之后刪除原始表。MySQL 5.6.17 之后，Optimize table 命令變成了 Online DDL，僅僅在準(zhǔn)備階段以及最后的提交階段，需要獲取鎖，中間的執(zhí)行階段，是不需要鎖的，也就是不會阻塞業(yè)務(wù)的更新 DML。參考官網(wǎng)文檔：

https://dev.mysql.com/doc/refman/5.6/en/optimize-table.html。

Prior to Mysql 5.6.17, OPTIMIZE TABLE does not use online DDL. Consequently, concurrent DML (INSERT, UPDATE, DELETE) is not permitted on a table while OPTIMIZE TABLE is running, and secondary indexes are not created as efficiently.

As of MySQL 5.6.17, OPTIMIZE TABLE uses online DDL for regular and partitioned InnoDB tables, which reduces downtime for concurrent DML operations. The table rebuild triggered by OPTIMIZE TABLE is completed in place. An exclusive table lock is only taken briefly during the prepare phase and the commit phase of the operation. During the prepare phase, metadata is updated and an intermediate table is created. During the commit phase, table metadata changes are committed.

針對 InnoDB 表使用 Optimize Table 命令需要注意的一些點：

1.針對大部分 InnoDB 表的 Optimize Table，其實等價于重建表 + Analyze命令(等價于語句 ALTER TABLE ... FORCE)，但是與 Analyze 命令不同的是， Optimize Table 是 online DDL 并且優(yōu)化了機制，只會在準(zhǔn)備階段和最后的提交階段獲取表鎖，這樣大大減少了業(yè)務(wù) DML 阻塞時間，也就是說，這是一個可以考慮在線執(zhí)行的優(yōu)化語句(針對 MySQL 5.6.17之后是這樣)。

mysql> OPTIMIZE TABLE foo;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.foo | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+1.2.3.4.5.6.7.

2.雖然如此，還是要選擇在業(yè)務(wù)低峰的時候執(zhí)行 Optimize Table，因為和執(zhí)行其他的 Online DDL 一樣，會創(chuàng)建并記錄臨時日志文件，該文件記錄了DDL操作期間所有 DML 插入、更新、刪除的數(shù)據(jù)，如果是在業(yè)務(wù)高峰的時候執(zhí)行，很可能會造成日志過大，超過innodb_online_alter_log_max_size 的限制：

mysql> OPTIMIZE TABLE foo;
+----------+----------+----------+----------------------------------------------------------------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                                                                                   |
+----------+----------+----------+----------------------------------------------------------------------------------------------------------------------------+
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead                                                          |
| test.foo | optimize | error    | Creating index 'PRIMARY' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.|
| test.foo | optimize | status   | OK                                                                                                                         |
+----------+----------+----------+----------------------------------------------------------------------------------------------------------------------------+1.2.3.4.5.6.7.8.

3.對于這種情況，如果我們已經(jīng)處于業(yè)務(wù)低峰時段，但還是報這個錯誤，我們可以稍微調(diào)大innodb_online_alter_log_max_size 的大小，但是不能調(diào)太大，建議每次調(diào)大 128 MB(默認(rèn)是 128 MB)。如果這個過大，會可能有兩個問題：(1)最后的提交階段，由于日志太大，提交耗時過長，導(dǎo)致鎖時間過長。(2)由于業(yè)務(wù)壓力導(dǎo)致一直不斷地寫入這個臨時文件，但是一直趕不上，導(dǎo)致業(yè)務(wù)高峰到得時候這個語句還在執(zhí)行。

4.建議在執(zhí)行的時候，如果要評估這個對于線上業(yè)務(wù)的影響，可以針對鎖wait/synch/sxlock/innodb/dict_sys_lock 和 wait/synch/sxlock/innodb/dict_operation_lock 這兩個鎖進行監(jiān)控，如果這兩個鎖相關(guān)鎖事件太多，并且線上有明顯的慢 SQL，建立還是 kill 掉選其他時間執(zhí)行 Optimize table 語句。

select thread_id,event_id,event_name,timer_wait from events_waits_history where event_name Like "%dict%" order by thread_id;
SELECT event_name,COUNT_STAR FROM events_waits_summary_global_by_event_name 
where event_name Like "%dict%" ORDER BY COUNT_STAR DESC;