問題描述

我在做一個(gè)網(wǎng)站程序，大致要求效果如下。用戶分為1-5這五個(gè)級(jí)別，數(shù)字越大權(quán)限越高。

我有一堆內(nèi)容，級(jí)別越高的用戶可見內(nèi)容越多。例如有內(nèi)容：A、B、C、D、E，用戶組1的可見：A用戶組2的可見：A、B…………用戶組5的可見：A、B、C、D、E如果要實(shí)現(xiàn)這種功能，該如何建立數(shù)據(jù)庫索引比較好呢？

之前有朋友跟我說在內(nèi)容（topic）表加上一列'group'，寫上可見的用戶等級(jí)1-5，然后建立group_tid的聯(lián)合索引。然后查詢tid<100周圍文章（例如當(dāng)前用戶組為3）時(shí)的語句就是：SELECT * FROM topic WHERE group>=3 AND tid<100 LIMIT 10;可實(shí)際發(fā)現(xiàn)這種索引是先將group>3的所有數(shù)據(jù)讀出來，再進(jìn)行選擇查詢。假如有100萬條數(shù)據(jù)，有50萬個(gè)group>3，該語句執(zhí)行就要從50萬條種篩選，效率極低。

看起來單列的索引只適用于group=*這樣的限制條件，而不能是<或>。所以想在此請(qǐng)教各路大神，有沒有過類似的需求？如何正確建立索引或分表？深表感謝！

補(bǔ)充1：其實(shí)把問題改變一下，就是如何在mysql兩個(gè)索引中使用<或>限定。這是個(gè)邏輯問題，目前的group_tid索引建立后類似于下圖：

即便我對(duì)group進(jìn)行了范圍限制，后面的tid還是在group的基礎(chǔ)上按順序排列的。如果我想知道group>1且tid<6的這種情況，不得不先把group2/3全部讀出再篩選。看起來只有重新規(guī)劃表結(jié)構(gòu)，各位有沒有類似經(jīng)驗(yàn)？

補(bǔ)充2：剛才收到了熱心朋友的幫助回答，說這種情況他曾經(jīng)遇到過。解決的方法是修改發(fā)布機(jī)制，將符合條件的帖子發(fā)布至各個(gè)等級(jí)。例如內(nèi)容A的級(jí)別是3，那么發(fā)帖時(shí)要同時(shí)建立三個(gè)數(shù)據(jù)行：group=1,tid=Agroup=2,tid=Agroup=3,tid=A這樣在內(nèi)容讀取時(shí)直接請(qǐng)求WHERE group=*都可以讀出符合條件內(nèi)容。但這種方法需要添加大量的關(guān)聯(lián)數(shù)據(jù)，甚至造成重復(fù)，有沒有其他解決途徑呢？

問題解答

其實(shí)你的思路已經(jīng)很對(duì)了。

tid上建立索引，根據(jù)group分表。

如果group >=3的組，在程序中動(dòng)態(tài)組合sql如下：

select * from group3 where tid < 100union all select * from group4 where tid < 100union all select * from group5 where tid < 100

以上索引生效，邏輯可用。

首先說明一下，在 Innodb 中，索引生不生效跟你使用 < 或 > 沒有必然關(guān)系。也不是說用 = 就一定能用上索引。當(dāng)全表查的性能要高于索引檢索查詢時(shí)，MySQL 會(huì)智能的放棄索引，選擇全表查詢。

如圖：

回到你的問題，如果某個(gè)索引，如 tid<100 檢索出的范圍相對(duì)較小時(shí)，索引是能夠用上的。

如果這兩個(gè)索引的結(jié)果集都很大的話，是否考慮添加其他過濾條件，比如根據(jù)創(chuàng)建時(shí)間只查近一個(gè)月的內(nèi)容。

分頁問題也可以通過主鍵ID來再次過濾。

首先，需要明白以下幾點(diǎn)：

對(duì)于一個(gè)表的查詢，每次最多只使用一個(gè)索引

對(duì)于聯(lián)合索引，從左往右依次進(jìn)行數(shù)據(jù)的篩選，所以如果第一個(gè)篩選條件針對(duì)了大于或者小于的話，第二個(gè)篩選條件由于在整個(gè)可選區(qū)域內(nèi)沒有確切的索引范圍，所以會(huì)將第一個(gè)篩選條件篩除來的數(shù)據(jù)都跑一遍

B-Tree索引的結(jié)構(gòu)類似于樹形結(jié)構(gòu)，見下圖，聯(lián)合索引從左往右的檢索，起始就是這個(gè)結(jié)構(gòu)從上往下查找分支的過程

索引的機(jī)制，簡單說來就是創(chuàng)建一個(gè)值到數(shù)據(jù)項(xiàng)的對(duì)應(yīng)表，這樣可以快速的從某一字段某個(gè)值定位到某一行，省卻了跑整個(gè)表去找對(duì)應(yīng)行的操作，所以比較快

B-Tree索引的結(jié)構(gòu)：

然后回到你的問題上，如果要大幅度提高效率，那么聯(lián)合索引的第一步就需要大幅度減少可以用于后續(xù)篩選的數(shù)據(jù)量，所以如果你要查tid < 100的話，先用tid篩選才能夠大幅度減少后續(xù)的B-Tree索引分支，所以如果要用聯(lián)合索引，則應(yīng)該是(tid, group)。

group條件的過濾性很差，單獨(dú)建立索引意義不大。

根據(jù)你描述的場(chǎng)景，只要tid的值不是太大（幾千的數(shù)量級(jí)），針對(duì)tid建立索引就可以了。如果還擔(dān)心tid條件過濾后的數(shù)據(jù)量大，可以創(chuàng)建tid,group的組合索引。

首先非常感謝各位對(duì)我問題的關(guān)注和回答！！問題解決之后針對(duì)boxsnake的建議有一個(gè)思考，在這里發(fā)一下。group_tid這種索引方式除了解決讀取之外還能解決分頁問題，例如我每頁文章數(shù)量是10，用戶級(jí)別為3，那么讀取時(shí)分別從group1、group2、group3中，按范圍tid<100各取10篇，即便某group中沒有符合條件的結(jié)果，幾項(xiàng)加起來也可以覆蓋全。

但如果用tid_group這種索引方式來讀取，如果需要group<=3的情況，我不知道該取多少篇文章。比方說取10篇，tid90-tid99，如果他們的group都是4，那么就無法取出符合條件的數(shù)值。而tid_group在限定group之前又必須對(duì)tid進(jìn)行限定，所以就沒法使用了。