iMySQL(一)

1 如何在數(shù)據(jù)庫中存儲(chǔ)層級(jí)結(jié)構(gòu)


(1) 鄰接表模型


  • 求節(jié)點(diǎn)的路徑
    舉例來說煌往,“Cherry”所在的路徑為Food > Fruit > Red > Cherry蜗顽。在這里,我們可以從Cherry開始查起恢筝,然后遞歸查詢查詢節(jié)點(diǎn)前的節(jié)點(diǎn)诞外,直到某節(jié)點(diǎn)的父節(jié)點(diǎn)ID為0(Food)。

  • 優(yōu)點(diǎn)
    簡單易懂

  • 缺點(diǎn)
    執(zhí)行起來效率低下府蔗。我們對(duì)于每個(gè)結(jié)果晋控,期望只需要一次查詢;可是當(dāng)使用鄰接表模型時(shí)嵌套的遞歸使用了多次查詢姓赤,當(dāng)樹很大的時(shí)候赡译,這種慢就會(huì)表現(xiàn)得尤為明顯。

上帝是公平的不铆,當(dāng)在執(zhí)行時(shí)效率低下蝌焚,意味著可以增加預(yù)處理的程度。

(2)MPTT(修改過的前序遍歷算法)

  • 原理
    現(xiàn)在我們把樹“橫”著放狂男。如下圖所示综看,我們首先從根節(jié)點(diǎn)(“Food”)開始,先在它左側(cè)標(biāo)記“1”岖食,然后我們到“Fruit”红碑,左側(cè)標(biāo)記“2”,接著按照前序遍歷的順序遍歷完樹泡垃,依次在每個(gè)節(jié)點(diǎn)的左右側(cè)標(biāo)記數(shù)字析珊。
  • 實(shí)現(xiàn)
  • 打印樹
    如果要想打印樹,你只需要知道你要檢索的節(jié)點(diǎn)蔑穴。比如忠寻,想要打印“Fruit”的子樹,可以查詢左數(shù)大于2而小于11的節(jié)點(diǎn)存和。
SELECT * FROM tree WHERE lft BETWEEN 2 AND 11;
  • 求節(jié)點(diǎn)的路徑
    對(duì)于某節(jié)點(diǎn)奕剃,我們只需求出左數(shù)值小于其左數(shù)值衷旅、右數(shù)大于其右數(shù)的所有節(jié)點(diǎn)。比如說“Cherry”這個(gè)節(jié)點(diǎn)(4-5)
SELECT title FROM tree WHERE lft < 4 AND rgt > 5 ORDER BY lft ASC;
  • 增加節(jié)點(diǎn)
    我們想添加“Strawberry“到”Red“節(jié)點(diǎn)下纵朋,那么“Red”節(jié)點(diǎn)的右數(shù)就得從6到8柿顶,而“Yellow”就得從7-10變成9-12,以此類推操软。更新Red節(jié)點(diǎn)就意味著大于5的左數(shù)和右數(shù)都要增加2嘁锯。
UPDATE tree SET rgt=rgt+2 WHERE rgt>5;
UPDATE tree SET lft=lft+2 WHERE lft>5;

INSERT INTO tree SET lft=6, rgt=7, title='Strawberry';
  • 優(yōu)點(diǎn)
    查詢的時(shí)候只需要一條SQL語句

2 進(jìn)入正題

  • Mysql架構(gòu)
    • Parser
      將用戶的查詢語句進(jìn)行解析,并創(chuàng)建一個(gè)內(nèi)部的數(shù)據(jù)結(jié)構(gòu)——分析樹
    • Optimizer
      各種優(yōu)化聂薪,例如重寫查詢家乘、選擇讀取表的順序,以及使用哪個(gè)索引等藏澳。
  • 類目表結(jié)構(gòu)(是否可以改進(jìn)?)
CREATE TABLE `itemcats` (
  `cid` int(11) NOT NULL,
  `parent_cid` int(11) DEFAULT NULL,
  `name` varchar(50) NOT NULL,
  `is_parent` tinyint(1) NOT NULL,
  `status` varchar(20) DEFAULT NULL,
  `sort_order` int(11) DEFAULT NULL,
  `lft` int(11) NOT NULL,
  `rght` int(11) NOT NULL,
  `level` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `parent_cid` (`parent_cid`),
  KEY `lft_rght` (`lft`,`rght`),
  KEY `level` (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • 為什么InnoDB表要建議用自增列做主鍵?
    如果InnoDB表的數(shù)據(jù)寫入順序能和B+樹索引的葉子節(jié)點(diǎn)順序一致的話仁锯,這時(shí)候存取效率是最高的
    • 使用自增列(INT/BIGINT類型)做主鍵,這時(shí)候?qū)懭腠樞蚴亲栽龅陌试兀虰+數(shù)葉子節(jié)點(diǎn)分裂順序一致扑馁;
    • 如果一個(gè)InnoDB表又沒有顯示主鍵,又有可以被選擇為主鍵的唯一索引凉驻,但該唯一索引可能不是遞增關(guān)系時(shí)(例如字符串腻要、UUID、多字段聯(lián)合唯一索引的情況)涝登,該表的存取效率就會(huì)比較差雄家。
  • 數(shù)值類型
  • 字符串類型
    • char(n)和varchar(n)中括號(hào)中n代表字符的個(gè)數(shù)聘芜,并不代表字節(jié)個(gè)數(shù)间聊,所以當(dāng)使用了中文的時(shí)候(UTF8)意味著可以插入m個(gè)中文甘邀,但是實(shí)際會(huì)占用m*3個(gè)字節(jié)鉴象。
    • 同時(shí)char和varchar最大的區(qū)別就在于char不管實(shí)際value都會(huì)占用n個(gè)字符的空間,而varchar只會(huì)占用實(shí)際字符應(yīng)該占用的空間+1萤衰,并且實(shí)際空間+1<=n霎俩。
    • 超過char和varchar的n設(shè)置后袭厂,字符串會(huì)被截?cái)唷?/li>
    • char在存儲(chǔ)的時(shí)候會(huì)截?cái)辔膊康目崭窠P蹋瑅archar和text不會(huì)媳纬。
    • varchar會(huì)使用1-3個(gè)字節(jié)來存儲(chǔ)長度,text不會(huì)施掏。
最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末钮惠,一起剝皮案震驚了整個(gè)濱河市,隨后出現(xiàn)的幾起案子七芭,更是在濱河造成了極大的恐慌素挽,老刑警劉巖,帶你破解...
    沈念sama閱讀 217,907評(píng)論 6 506
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件狸驳,死亡現(xiàn)場離奇詭異预明,居然都是意外死亡缩赛,警方通過查閱死者的電腦和手機(jī),發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,987評(píng)論 3 395
  • 文/潘曉璐 我一進(jìn)店門撰糠,熙熙樓的掌柜王于貴愁眉苦臉地迎上來峦筒,“玉大人,你說我怎么就攤上這事窗慎。” “怎么了卤材?”我有些...
    開封第一講書人閱讀 164,298評(píng)論 0 354
  • 文/不壞的土叔 我叫張陵遮斥,是天一觀的道長。 經(jīng)常有香客問我扇丛,道長术吗,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 58,586評(píng)論 1 293
  • 正文 為了忘掉前任帆精,我火速辦了婚禮较屿,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘卓练。我一直安慰自己隘蝎,他們只是感情好,可當(dāng)我...
    茶點(diǎn)故事閱讀 67,633評(píng)論 6 392
  • 文/花漫 我一把揭開白布襟企。 她就那樣靜靜地躺著嘱么,像睡著了一般。 火紅的嫁衣襯著肌膚如雪顽悼。 梳的紋絲不亂的頭發(fā)上曼振,一...
    開封第一講書人閱讀 51,488評(píng)論 1 302
  • 那天,我揣著相機(jī)與錄音蔚龙,去河邊找鬼冰评。 笑死,一個(gè)胖子當(dāng)著我的面吹牛木羹,可吹牛的內(nèi)容都是我干的甲雅。 我是一名探鬼主播,決...
    沈念sama閱讀 40,275評(píng)論 3 418
  • 文/蒼蘭香墨 我猛地睜開眼汇跨,長吁一口氣:“原來是場噩夢啊……” “哼务荆!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起穷遂,我...
    開封第一講書人閱讀 39,176評(píng)論 0 276
  • 序言:老撾萬榮一對(duì)情侶失蹤函匕,失蹤者是張志新(化名)和其女友劉穎,沒想到半個(gè)月后蚪黑,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體盅惜,經(jīng)...
    沈念sama閱讀 45,619評(píng)論 1 314
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡中剩,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,819評(píng)論 3 336
  • 正文 我和宋清朗相戀三年,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了抒寂。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片结啼。...
    茶點(diǎn)故事閱讀 39,932評(píng)論 1 348
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡,死狀恐怖屈芜,靈堂內(nèi)的尸體忽然破棺而出郊愧,到底是詐尸還是另有隱情,我是刑警寧澤井佑,帶...
    沈念sama閱讀 35,655評(píng)論 5 346
  • 正文 年R本政府宣布属铁,位于F島的核電站,受9級(jí)特大地震影響躬翁,放射性物質(zhì)發(fā)生泄漏焦蘑。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,265評(píng)論 3 329
  • 文/蒙蒙 一盒发、第九天 我趴在偏房一處隱蔽的房頂上張望例嘱。 院中可真熱鬧,春花似錦宁舰、人聲如沸拼卵。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,871評(píng)論 0 22
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽间学。三九已至,卻和暖如春印荔,著一層夾襖步出監(jiān)牢的瞬間低葫,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 32,994評(píng)論 1 269
  • 我被黑心中介騙來泰國打工仍律, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留嘿悬,地道東北人。 一個(gè)月前我還...
    沈念sama閱讀 48,095評(píng)論 3 370
  • 正文 我出身青樓水泉,卻偏偏與公主長得像善涨,于是被迫代替她去往敵國和親。 傳聞我的和親對(duì)象是個(gè)殘疾皇子草则,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,884評(píng)論 2 354

推薦閱讀更多精彩內(nèi)容