一、分區(qū)表是什么考阱？

圖 1 表 t 的磁盤文件

插入兩行記錄翠忠，落在 p_2018 和 p_2019?

Server? 1 個表，引擎層 4 個表：一個.frm 文件和 4 個.ibd 文件（每個分區(qū)對應(yīng)一個）

二乞榨、分區(qū)表的引擎層行為

分區(qū)表加間隙鎖秽之，說明對于 InnoDB 來說，這是 4 個表吃既。

圖 2 分區(qū)表間隙鎖示例

第 21 篇文章間隙鎖加鎖規(guī)則考榨。初始化，ftime 值分別是鹦倚，‘2017-4-1’ 和’2018-4-1’ 董虱。A 的 select 對索引 ftime 間隙加了鎖，普通加鎖狀態(tài)：sesion B 兩條insert鎖等待

圖 3 普通表的加鎖范圍

分區(qū)：第一個 insert 語句是可以執(zhí)行成功的。p_2018 和 p_2019 是兩個不同表愤诱，也就是說 2017-4-1下一個記錄云头，是 p_2018 分區(qū) supremum（不是 2018-4-1）。 T1 表 t? ftime 索引上：

圖 4 分區(qū)表 t 的加鎖范圍

A 的 select 只操作（深綠色）淫半，B 寫入2018-2-1 成功溃槐，2017-12-1 等 A 間隙鎖。

show engine innodb status?

圖 5 session B 被鎖住信息

MyISAM 分區(qū)表的例子科吭。alter table t engine=myisam昏滴，對于 MyISAM 引擎來說，這是 4 個表对人。

圖 6 用 MyISAM 表鎖驗證

A sleep時間設(shè)置100 秒谣殊。MyISAM 只支持表鎖，鎖表 t讀牺弄。引擎層實現(xiàn)姻几，其實是鎖 p_2018，其他不受影響的

B 正常執(zhí)行势告，第二條鎖等待狀態(tài)蛇捌。

手動分表和分區(qū)表區(qū)別

性能沒差別：按年份劃分，手工分表的邏輯咱台，依次執(zhí)行更新络拌。

server 層決定用哪個分區(qū)，應(yīng)用層代碼決定用哪個分表回溺。沒差別春贸。

區(qū)別：在 server 層打開表行為。

三遗遵、分區(qū)策略

分區(qū)表過多萍恕，超過 1000 個，MySQL 啟動時瓮恭，open_files_limit默認(rèn)值 1024雄坪，訪問表時厘熟，打開所有的文件屯蹦，個數(shù)超過了上限而報錯。只需要訪問一個分區(qū)绳姨，無法執(zhí)行登澜。

圖 7 insert 語句報錯

用 InnoDB 引擎的話，不會出現(xiàn)這個問題飘庄。

通用分區(qū)策略（generic partitioning）：MyISAM 分區(qū)策略脑蠕，server 層控制訪問分區(qū)。一開始支持分區(qū)表存在的代碼，文件管理谴仙、表管理粗糙迂求，性能問題。

本地分區(qū)策略（native partitioning）：InnoDB?內(nèi)部管理打開分區(qū)晃跺。只有 InnoDB 和 NDB 支持

四揩局、分區(qū)表的 server 層行為

server 層看，只是一個表

圖 8 分區(qū)表的 MDL 鎖??

圖 9 show processlist 結(jié)果

A 持有整個表 t 的 MDL 鎖掀虎，B 的 alter 語句被堵住凌盯。

分區(qū)表， DDL 影響大烹玉。普通分表驰怎， truncate 分表的時候，不會跟另外一個分表上的查詢語句二打，出現(xiàn) MDL 鎖沖突县忌。

1.? 第一次打開分區(qū)表時，訪問所有分區(qū)址儒；

2.? ?server 層芹枷，認(rèn)為同一張表，所有分區(qū)共用 MDL 鎖莲趣；

3.? 引擎層鸳慈，認(rèn)為不同表，MDL 鎖之后喧伞，只訪問必要分區(qū)走芋。

where 條件改成 where ftime>=‘2018-4-1’，查詢結(jié)果相同潘鲫，where 條件翁逞，訪問 p_2019 和 p_others 這兩個分區(qū)。

where 沒有分區(qū) key溉仑，訪問所有分區(qū)弓叛。沒有用分表key，訪問所有分表袍嬉。

五淫奔、分區(qū)表的應(yīng)用場景

優(yōu)勢，業(yè)務(wù)透明振定，業(yè)務(wù)代碼簡潔必怜。方便清理歷史數(shù)據(jù)。

alter table t drop partition 直接刪除分區(qū)文件后频，跟 drop 普通表類似梳庆。與 delete 語句刪除數(shù)據(jù)相比暖途，速度快、影響小膏执。

小結(jié)

范圍分區(qū)（range）為例介紹驻售。還支持 hash 、list 等分區(qū)方法更米。

分區(qū)表跟用戶分表比起來芋浮，兩個繞不開的問題：第一次訪問的時候需要訪問所有分區(qū)，共用 MDL 鎖壳快。注意：

1. 不是越細(xì)越好纸巷。單表或者單分區(qū)的數(shù)據(jù)一千萬行，是小表眶痰。DDL耗時嚴(yán)重

2.? 不預(yù)留太多（主從延遲）瘤旨，按月分區(qū)，年底創(chuàng)建12 個新分區(qū)即可竖伯。沒數(shù)據(jù)分區(qū)drop 掉存哲。

ps：規(guī)則預(yù)先設(shè)置好,后來修改麻煩

思考題

創(chuàng)建自增字段 id。分區(qū)表中主鍵必須包含分區(qū)字段七婴。怎么定義表主鍵呢祟偷？為什么？兩種：

（1）(ftime, id)：利用率高打厘，用 ftime 做分區(qū) key修肠，大多數(shù)語句包含 ftime ，用前綴索引户盯，減少索引嵌施。

（2） (id, ftime)

盡量用 InnoDB 引擎。要求至少有一個索引莽鸭，自增字段作為第一個字段吗伤，所以加id單獨索引。

?PRIMARY KEY (`id`,`ftime`),

? KEY `id` (`ftime`)也可以

評論1

分區(qū)表場景：歷史數(shù)據(jù)表改造,用存儲過程創(chuàng)建和改造硫眨；后臺數(shù)據(jù)分析匯總,比如日志數(shù)據(jù),便于清理

分區(qū)表業(yè)務(wù)采用是hash 用戶ID方式,大規(guī)模應(yīng)用分區(qū)表沒遇到過

評論2

innodb_open_files：限制Innodb打開表數(shù)量足淆。InnoDB引擎打開文件超過 innodb_open_files值，會關(guān)掉之前打開文件礁阁。

open_files_limit：用本地分區(qū)策略巧号，即使分區(qū)個數(shù)大于open_files_limit ，打開InnoDB分區(qū)表也不會報“打開文件過多”這個錯誤氮兵，innodb_open_files發(fā)揮作用裂逐。

評論3

add column after column_name跟add column不指定位置：

性能沒差別歹鱼，盡量加到最后一列（不要after column_name泣栈，）好處：

1. 分支可快速加列，瞬間完成

2. 先做備庫、切換南片、再做備庫掺涛，執(zhí)行ddl，用after column_name時用不上這種方式疼进。