SQL優(yōu)化一.插入優(yōu)化二.主鍵優(yōu)化1頁(yè)分裂此時(shí)為主鍵亂序插入50應(yīng)該放在47后面但如果47所在的頁(yè)已經(jīng)放不下50這個(gè)數(shù)據(jù)了這時(shí)候mysql會(huì)創(chuàng)建一個(gè)新頁(yè)并找到47所在頁(yè)的50%的位置把50%后的數(shù)據(jù)放在新頁(yè)中再在后面插入新數(shù)據(jù)最后調(diào)整鏈表指針保持順序這種現(xiàn)象叫做頁(yè)分裂。2頁(yè)合并此時(shí) 2#page 中刪除記錄達(dá)到了閾值會(huì)判斷相鄰兩頁(yè)是否可以合并合并完如下3主鍵設(shè)計(jì)原則1.滿(mǎn)足業(yè)務(wù)需求的情況下盡量降低主鍵的長(zhǎng)度因?yàn)榫鄞厮饕粡埍碇挥幸粋€(gè)但二級(jí)索引可以有很多個(gè)二級(jí)索引的葉子節(jié)點(diǎn)存放的是主鍵主鍵過(guò)長(zhǎng)會(huì)占過(guò)多磁盤(pán)空間而且搜索的時(shí)候會(huì)浪費(fèi)磁盤(pán) I/O2.插入數(shù)據(jù)時(shí)盡量選擇順序插入選擇使用自增主鍵順序插入時(shí)一個(gè)頁(yè)寫(xiě)不下了就寫(xiě)下一個(gè)頁(yè)就行了亂序插入時(shí)會(huì)有頁(yè)分裂現(xiàn)象3.盡量不要使用 UUID 做主鍵或使用其他自然主鍵如身份證號(hào)。因?yàn)檫@些數(shù)據(jù)沒(méi)有順序就會(huì)亂序插入而且長(zhǎng)度通常較長(zhǎng)4.業(yè)務(wù)操作時(shí)避免對(duì)主鍵的修改三.order by 優(yōu)化創(chuàng)建索引前使用的是 filesort此時(shí)給agephone創(chuàng)建聯(lián)合索引創(chuàng)建索引后使用的是Using index 直接通過(guò)索引返回了有序數(shù)據(jù)根據(jù)age和phone倒序排也沒(méi)有使用filesort Backward index scan反向掃描索引之前都是先根據(jù)age排再根據(jù)phone排序如果先根據(jù)phone再根據(jù)age排會(huì)怎么樣呢此時(shí)也用到了 filesort 因?yàn)槲覀儎?chuàng)建索引時(shí)第一個(gè)字段是age 先按phone排序違背了最左前綴法則要是按 age asc phone desc排序呢還是用到了filesort以為創(chuàng)建索引時(shí)是按age asc 再 phone asc 創(chuàng)建的此時(shí)按phone 倒序排 就需要額外的排列按age asc phone desc創(chuàng)建一個(gè)新的索引會(huì)怎么樣呢查詢(xún)索引發(fā)現(xiàn)該索引的Collection 是D 其他的都是A 可知此處D代表降序的意思再次查詢(xún) 此時(shí)走的索引是剛才創(chuàng)建的索引 這次沒(méi)有使用filesort按兩種不同的方法排序所創(chuàng)建的索引的區(qū)別如下注意事項(xiàng)四.group by優(yōu)化刪掉索引后 查詢(xún)profession結(jié)果如下創(chuàng)建聯(lián)合索引后查詢(xún)profession結(jié)果如下查詢(xún)age 結(jié)果如下查詢(xún)profession和age結(jié)果如下先用profession過(guò)濾 結(jié)果如下注意事項(xiàng)五.limit優(yōu)化六.count優(yōu)化七.update 優(yōu)化視圖一.介紹及基本語(yǔ)法介紹創(chuàng)建根據(jù)student的id和name字段創(chuàng)建視圖stu_v_1創(chuàng)建好了左側(cè)會(huì)有顯示查詢(xún)修改很明顯根據(jù)左側(cè)的數(shù)據(jù)就能看出來(lái) 修改成功使用第二種方法也修改成功刪除二.檢查選項(xiàng)cascaded視圖既然是一個(gè)虛擬的表自然可以增刪改查但是視圖實(shí)際上并不存儲(chǔ)數(shù)據(jù)那么如果增加一條數(shù)據(jù)數(shù)據(jù)去哪了再次根據(jù)student表重新創(chuàng)建視圖并且成功插入一條數(shù)據(jù)結(jié)果數(shù)據(jù)插入到了student表里再次插入一條id為30的數(shù)據(jù)student表里也成功插入了這條數(shù)據(jù)但是再次對(duì)視圖進(jìn)行查詢(xún)發(fā)現(xiàn)id為30的數(shù)據(jù)沒(méi)有查出來(lái)這是因?yàn)槲覀儎?chuàng)建這個(gè)視圖時(shí)規(guī)定了where id 20我們插入的數(shù)據(jù)有的卻查不到那要怎么辦呢可以在后面加上with cascaded check option加上這個(gè)語(yǔ)句重新創(chuàng)建視圖插入數(shù)據(jù)可以發(fā)現(xiàn)插入id為30的數(shù)據(jù)時(shí)報(bào)錯(cuò)了避免了之前的情況于是引出了檢查選項(xiàng)這個(gè)概念視圖除了基于表創(chuàng)建也可以基于另一個(gè)視圖創(chuàng)建創(chuàng)建第一個(gè)視圖時(shí)不加 with check option增刪改查時(shí)不會(huì)檢查where條件但要是第二個(gè)視圖基于第一個(gè)視圖創(chuàng)建時(shí)加上了with check option 給視圖v2增刪改查時(shí) 不僅會(huì)檢查v2的條件也會(huì)檢查v1的條件例子v2插入7報(bào)錯(cuò)因?yàn)椴粷M(mǎn)足v2的id10插入26報(bào)錯(cuò)因?yàn)椴粷M(mǎn)足v1的id20插入15成功如果又有視圖v3基于v2創(chuàng)建且不加with check option 會(huì)怎末樣呢執(zhí)行過(guò)程插入11 滿(mǎn)足id15 成功插入17 因?yàn)関3沒(méi)加檢查選項(xiàng) 不會(huì)對(duì)v3本身的條件進(jìn)行檢查 但是會(huì)接著檢查v2的條件 因?yàn)関2加了with check option 滿(mǎn)足接著檢查v1的條件因?yàn)関2基于v1創(chuàng)建且加了檢查選項(xiàng)滿(mǎn)足成功插入 28 檢查v2的條件滿(mǎn)足檢查v1的條件不滿(mǎn)足失敗三.檢查選項(xiàng)local給v2加上with local check option對(duì)v2增刪改查時(shí)同樣會(huì)看他所依賴(lài)的視圖有沒(méi)有加檢查選項(xiàng)但是如果他所依賴(lài)的視圖 不加檢查選項(xiàng)的話(huà) 就不會(huì)像cascaded那樣檢查了所有它的依賴(lài)視圖的條件了四.視圖的更新及視圖的作用五.案例比如案例二根據(jù)三表聯(lián)查創(chuàng)建視圖后面直接查視圖就能簡(jiǎn)化操作