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