首先將存儲(chǔ)引擎限定在innodb
2種方式分表
- 水平分表彤恶，根據(jù)某一列或者某幾列將表按行分割到多張表中伟桅，達(dá)到減少每張表行數(shù)的目的
- 垂直分表退敦，將表的一些列拆分到多張表中伍俘，達(dá)到減少每張表列數(shù)的目的
分表真得能提升查詢性能嗎
分表分庫
如果將不同的表分到多個(gè)庫多個(gè)機(jī)器上邪锌，那一定是能提升性能的，畢竟你花錢買機(jī)器了癌瘾，總的可用CPU和內(nèi)存高了觅丰。這種情況不必考慮。
分表不分庫
如果所有分表都在同庫同機(jī)器上妨退，每個(gè)表的查詢都共用CPU和內(nèi)存妇萄，性能是怎么提升的？
1. 有索引的情況
例如這條查詢 select * from user_feeds where uid = ? 咬荷，且uid字段上有二級索引冠句，查詢步驟為：
- 確定索引的掃描范圍，即找到B+樹上uid=X的最左葉子節(jié)點(diǎn)和最右葉子節(jié)點(diǎn)幸乒，可能（不考慮已經(jīng)緩存到buffer pool中）需要進(jìn)行的最大IO次數(shù)為4（4層b+樹已經(jīng)可以存放巨量的數(shù)據(jù)了懦底，一般千萬級的是3層）
- 遍歷掃描范圍內(nèi)的索引數(shù)據(jù)，取出主鍵id
- 通過主鍵id進(jìn)行回表查詢罕扎，IO次數(shù)最大值為掃描范圍內(nèi)索引記錄數(shù)量乘以聚簇索引中定位一條記錄的IO次數(shù)（B+樹層數(shù)）
通過以上步驟可以看出聚唐，查詢的主要成本為：確定索引掃描范圍的IO次數(shù)（最大為4）、遍歷掃描范圍內(nèi)索引數(shù)據(jù)的CPU占用（與掃描范圍內(nèi)記錄數(shù)成正比）腔召、回表IO次數(shù)（與掃描范圍內(nèi)記錄數(shù)成正比杆查，且受聚簇索引B+樹層數(shù)影響）。所以查詢的成本主要取決于uid=X對應(yīng)的記錄數(shù)量和索引B+樹的層數(shù)宴咧。
來看一下分表后的查詢成本：
- 首先是確定索引掃描范圍的IO次數(shù)由B+樹的層數(shù)決定根灯，大部分情況下分表是會(huì)降低單表的樹的層數(shù)，例如由4層降到3層掺栅，可以減少一次IO
- 掃描范圍內(nèi)記錄數(shù)不會(huì)變烙肺，所以遍歷掃描范圍內(nèi)索引數(shù)據(jù)的CPU占用不變
- 回表IO次數(shù)，由于掃描范圍內(nèi)的記錄數(shù)不變氧卧，回表次數(shù)也不變桃笙，通過聚簇索引定位具體記錄數(shù)據(jù)IO次數(shù)可能會(huì)減少（同步驟1中普通索引確定掃描范圍的IO次數(shù)同理）
可見分表后主要的差別在于確定索引掃描范圍的IO次數(shù)和回表IO次數(shù)，即分表后B+樹的層數(shù)變化沙绝，變化范圍為0到3搏明。對于不需要回表的查詢鼠锈，差別在于用到的索引B+樹層數(shù)變化導(dǎo)致的確定掃描范圍的IO次數(shù)變化；對于需要回表的查詢星著，除了確定掃描范圍的IO次數(shù)變化购笆，還有回表IO次數(shù)變化（回表IO次數(shù)變化=記錄數(shù)X聚簇索引B+樹層數(shù)變化）。
2. 全表掃描的情況
以上是有二級索引的情況虚循，如果沒有索引同欠，需要全表掃描時(shí)，掃描整個(gè)聚簇索引的成本：加載數(shù)據(jù)頁到內(nèi)存的IO次數(shù)（聚簇索引的頁面數(shù)）横缔、掃描記錄的CPU占用（整個(gè)聚簇索引的記錄數(shù)量）铺遂。分表以后所有表總的記錄數(shù)量不變，頁面數(shù)量也可以認(rèn)為不變茎刚。所以全表掃描的查詢性能分表與不分表也不會(huì)有什么差別襟锐。
3. 垂直分表能帶來性能提升嗎
垂直分表，表記錄數(shù)不會(huì)改變膛锭，每條記錄數(shù)據(jù)占用空間會(huì)變小粮坞，從而導(dǎo)致單表聚簇索引每個(gè)數(shù)據(jù)頁可以存儲(chǔ)的記錄變多，聚簇索引的數(shù)據(jù)頁數(shù)量變少泉沾，原來uid=X的記錄可能分布在N個(gè)數(shù)據(jù)頁上捞蚂，分表后uid=X的記錄分布在的數(shù)據(jù)頁數(shù)量<=N。也就是減少回表IO的次數(shù)跷究。
但是姓迅，這種情況只限于合理的垂直分表，也就是select的列可以落在同一張表中俊马。
4. 分表對寫操作的性能提升（待續(xù)）
innodb索引數(shù)據(jù)大小估算
理論估算
索引列：uid丁存、created_at
單條記錄大小 uid4字節(jié)、created_at4字節(jié)柴我、主鍵post_id8字節(jié)解寝，頁號6字節(jié) 共22字節(jié)
單頁可以存放的記錄數(shù) 16kb（16384）/22 = 744，去除掉一些其他信息（header艘儒、槽信息）聋伦，再去除一些碎片（刪除造成的、沒填滿造成的）界睁，姑且認(rèn)為每頁存放500條記錄
如果總的記錄條數(shù)為5000w觉增，那么需要的葉子節(jié)點(diǎn)數(shù)量為 5000w/500 = 10w，B+樹需要兩層（500X500 = 25w）
索引葉子節(jié)點(diǎn)占用空間大小為 10wX16kb = 1.6GB
索引目錄節(jié)點(diǎn)占用空間大小為 500X16kb = 8M
實(shí)際業(yè)務(wù)場景中
測試環(huán)境
單條記錄大小 24
單頁可以存放的記錄數(shù) 682
與理論估算相近
// innodb統(tǒng)計(jì)信息查詢方法翻斟，注意sum_of_other_index_sizes代表的是所有二級索引占用的頁面數(shù)
select n_rows,'Secondary Indexes' AS 'BTrees',sum_of_other_index_sizes * 16384 AS Bytes,ROUND(sum_of_other_index_sizes * 16384 / n_rows) AS 'Bytes/row',sum_of_other_index_sizes AS Pages,ROUND(n_rows / sum_of_other_index_sizes) AS 'Rows/page' FROM innodb_table_stats where database_name = 'stt_post' and table_name = 'posts'
生產(chǎn)環(huán)境有限的信息
索引空間大小19.38GB逾礁，生產(chǎn)環(huán)境二級索引數(shù)量16個(gè)，平均每個(gè)索引空間大小約1.1GB访惜，比理論估算還小一些嘹履。
