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

• TiDB Cloud。
• YugaByte Cloud。

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

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

查詢?cè)絹碓铰脑?/h4>

例如這個(gè) 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.

這個(gè)表的分片鍵就是 user_id。

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

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

但是，有時(shí)候即使索引走對(duì)了，查詢依然有點(diǎn)慢。具體去看這個(gè) SQL 掃描的數(shù)據(jù)行數(shù)的時(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.

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

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

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

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

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

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

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

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 對(duì)于聚簇索引是在索引原始位置上進(jìn)行更新，對(duì)于二級(jí)索引，如果二級(jí)索引列發(fā)生更新則是在原始記錄上打上刪除標(biāo)記，然后在新的地方記錄。這樣和之前一樣，會(huì)造成很多存儲(chǔ)碎片。

綜上所述：

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

解決方案 - 重建表

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

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

針對(duì) InnoDB 表使用 Optimize Table 命令需要注意的一些點(diǎn)：

1.針對(duì)大部分 InnoDB 表的 Optimize Table，其實(shí)等價(jià)于重建表 + Analyze命令(等價(jià)于語句 ALTER TABLE ... FORCE)，但是與 Analyze 命令不同的是， Optimize Table 是 online DDL 并且優(yōu)化了機(jī)制，只會(huì)在準(zhǔn)備階段和最后的提交階段獲取表鎖，這樣大大減少了業(yè)務(wù) DML 阻塞時(shí)間，也就是說，這是一個(gè)可以考慮在線執(zhí)行的優(yōu)化語句(針對(duì) 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ù)低峰的時(shí)候執(zhí)行 Optimize Table，因?yàn)楹蛨?zhí)行其他的 Online DDL 一樣，會(huì)創(chuàng)建并記錄臨時(shí)日志文件，該文件記錄了DDL操作期間所有 DML 插入、更新、刪除的數(shù)據(jù)，如果是在業(yè)務(wù)高峰的時(shí)候執(zhí)行，很可能會(huì)造成日志過大，超過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.對(duì)于這種情況，如果我們已經(jīng)處于業(yè)務(wù)低峰時(shí)段，但還是報(bào)這個(gè)錯(cuò)誤，我們可以稍微調(diào)大innodb_online_alter_log_max_size 的大小，但是不能調(diào)太大，建議每次調(diào)大 128 MB(默認(rèn)是 128 MB)。如果這個(gè)過大，會(huì)可能有兩個(gè)問題：(1)最后的提交階段，由于日志太大，提交耗時(shí)過長(zhǎng)，導(dǎo)致鎖時(shí)間過長(zhǎng)。(2)由于業(yè)務(wù)壓力導(dǎo)致一直不斷地寫入這個(gè)臨時(shí)文件，但是一直趕不上，導(dǎo)致業(yè)務(wù)高峰到得時(shí)候這個(gè)語句還在執(zhí)行。

4.建議在執(zhí)行的時(shí)候，如果要評(píng)估這個(gè)對(duì)于線上業(yè)務(wù)的影響，可以針對(duì)鎖wait/synch/sxlock/innodb/dict_sys_lock 和 wait/synch/sxlock/innodb/dict_operation_lock 這兩個(gè)鎖進(jìn)行監(jiān)控，如果這兩個(gè)鎖相關(guān)鎖事件太多，并且線上有明顯的慢 SQL，建立還是 kill 掉選其他時(shí)間執(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;