[TOC]
真題
簡述MySQL分表操作和分區(qū)操作的工作原理，分別說說分區(qū)和分表的使用場景和各自的優(yōu)缺點(diǎn)。
分區(qū)表的原理
工作原理
對(duì)用戶而言，分區(qū)表是一個(gè)獨(dú)立的邏輯表，但是底層 MYSQL將其分成了多個(gè)物理子表，這對(duì)用戶來說是透明的，每一個(gè)分區(qū)表都會(huì)使用一個(gè)獨(dú)立的表文件。
創(chuàng)建表時(shí)使用 partition by子句定義每個(gè)分區(qū)存放的數(shù)據(jù)，執(zhí)行查詢時(shí)，優(yōu)化器會(huì)根據(jù)分區(qū)定義過濾那些沒有我們需要數(shù)據(jù)的分區(qū)，這樣查詢只需要查詢所需數(shù)據(jù)在的分區(qū)。
分區(qū)的主要目的是將數(shù)據(jù)按照一個(gè)較粗的粒度分在不同的表中，這樣可以將相關(guān)的數(shù)據(jù)存放在一起，而且如果想一次性刪除整個(gè)分區(qū)的數(shù)據(jù)也很方便。
使用場景
- 表非常大，無法全部存在內(nèi)存，或者只在表的最后有熱點(diǎn)數(shù)據(jù)其他都是歷史數(shù)據(jù)
- 分區(qū)表的數(shù)據(jù)更易維護(hù),可以對(duì)獨(dú)立的分區(qū)進(jìn)行獨(dú)立的操作
- 分區(qū)表的數(shù)據(jù)可以分布在不同的機(jī)器上,從而高效使用資源
- 可以使用分區(qū)表來避免某些特殊的瓶頸
- 備份和恢復(fù)獨(dú)立的分區(qū)
限制
- 一個(gè)表最多只能有
1024個(gè)分區(qū) - MySQL 5.1 版本中，分區(qū)表表達(dá)式必須是整數(shù)，5.5可以使用列分區(qū)
- 分區(qū)字段中如果有主鍵和唯一索引列，那么主鍵列和唯一索引列都必須包含進(jìn)來；
- 分區(qū)表中無法使用外鍵約束
- 需要對(duì)現(xiàn)有表的結(jié)構(gòu)進(jìn)行修改
- 所有分區(qū)都必須使用相同的存儲(chǔ)引擎
- 分區(qū)函數(shù)中可以使用的函數(shù)和表達(dá)式會(huì)有一些限制
- 某些存儲(chǔ)引擎不支持分區(qū)
- 對(duì)于MyISAM的分區(qū)表，不能使用
load index into cache - 對(duì)于MyISAM表，使用分區(qū)表時(shí)需要打開更多的文件描述符
分庫分表的原理
工作原理
通過一些hash算法或工具實(shí)現(xiàn)將一張數(shù)據(jù)表垂直或者水平進(jìn)行物理分割。
使用場景
- 單條記錄條數(shù)達(dá)到百萬到千萬級(jí)別
- 解決表鎖的問題
分表方式
水平分割
表很大，分割后可以降低在查詢時(shí)需要讀的數(shù)據(jù)和索引的頁數(shù)，同時(shí)也降低了索引的層數(shù)，提高查詢速度
水平分割
使用場景
- 表中的數(shù)據(jù)本身就有獨(dú)立性，例如表中分別記錄各個(gè)地區(qū)的數(shù)據(jù)或者不同時(shí)期的數(shù)據(jù),特別是有些數(shù)據(jù)常用，有些不常用
- 需要把數(shù)據(jù)存放在多個(gè)介質(zhì)上
水平分表缺點(diǎn)
- 給應(yīng)用增加復(fù)雜度，通常查詢時(shí)需要多個(gè)表名，查詢所有數(shù)據(jù)都需 UNION操作
- 在許多數(shù)據(jù)庫應(yīng)用中，這種復(fù)雜性會(huì)超過它帶來的優(yōu)點(diǎn)，查詢時(shí)會(huì)增加讀一個(gè)索引層的磁盤次數(shù)
垂直分割
把主鍵和一些列放在一個(gè)表，然后把主鍵和另外的列放在另一張表中。
垂直分割
使用場景
- 如果一個(gè)表中某些列常用，而另外一些列不常用
- 可以使數(shù)據(jù)行變小，一個(gè)數(shù)據(jù)頁能存儲(chǔ)更多數(shù)據(jù)，查詢時(shí)減少I/O次數(shù)
垂直分表缺點(diǎn)
- 管理冗余列，查詢所有數(shù)據(jù)需要
join操作
分表的缺點(diǎn)
- 有些分表的策略基于應(yīng)用層的邏輯算法，一旦邏輯算法改變，整個(gè)分表邏輯都會(huì)改變，擴(kuò)展性較差
- 對(duì)于應(yīng)用層來說,邏輯算法無疑增加開發(fā)成本
延伸：MySQL的主從復(fù)制原理及負(fù)載均衡
MySQL的主從復(fù)制原理
- 在主庫上把數(shù)據(jù)庫上把更改記錄到二進(jìn)制日志
- 從庫將主庫的日志復(fù)制到自己的中繼日志
- 從庫讀取中繼日志中的事件，將其重放在從庫數(shù)據(jù)庫中
主從復(fù)制解決的問題
- 數(shù)據(jù)分布：隨意停止或開始復(fù)制，并在不同地理位置分布數(shù)據(jù)備份
- 負(fù)載均衡：降低單個(gè)服務(wù)器的壓力
- 高可用和故障切換：幫助應(yīng)用程序避免單點(diǎn)失敗
- 升級(jí)測試：可以使用更高版本的 MYSQL作為從庫
