數(shù)據(jù)庫(kù)知識(shí)筆記

1、強(qiáng)制使用decimal存儲(chǔ)小數(shù)，不使用float、double，防止精度損失，如果存儲(chǔ)的數(shù)據(jù)范圍超過 decimal 的范圍，建議將數(shù)據(jù)拆成整數(shù)和小數(shù)分開存儲(chǔ)。這也是阿里的強(qiáng)制開發(fā)規(guī)范。

2、高效分頁(yè)

limit m,n其實(shí)質(zhì)就是先執(zhí)行l(wèi)imit m+n，然后從第m行取n行，這樣當(dāng)limit翻頁(yè)越往后翻m越大，性能越低。比如

select * from A limit 100000,10，這種sql語(yǔ)句的性能是很差的，建議改成下面的版本:

selec id,name,age from A where id >=(select id from A limit 100000,1) limit 10

3、InnoDB的行鎖加上死鎖檢測(cè)機(jī)制會(huì)導(dǎo)致數(shù)據(jù)庫(kù)CPU短時(shí)間內(nèi)被占滿，導(dǎo)致整庫(kù)幾乎無(wú)法響應(yīng)。 

4、對(duì)數(shù)據(jù)分組的總結(jié):

1 分組函數(shù)只能出現(xiàn)在選擇列表、having、order by子句中(不能出現(xiàn)在where中) 。

2 如果在select語(yǔ)句中同時(shí)包含有where,group by, having, order by 那么它們的順序是where,group by, having, order by 。

3 在選擇列中如果有列、表達(dá)式和分組函數(shù)，那么這些列和表達(dá)式必須有一個(gè)出現(xiàn)在group by 子句中，否則就會(huì)出錯(cuò)。

如SELECT deptno, AVG(sal), MAX(sal) FROM emp GROUP by deptno HAVING AVG(sal) < 2000;

這里deptno就一定要出現(xiàn)在group by 中。

5、sql排序默認(rèn)是升序(從小到大)，直接order by，降序是desc。

6、子查詢比關(guān)聯(lián)查詢效率高。

MySQL：

mysql的innodb的底層存儲(chǔ)模型是B+樹，它使用主鍵作為聚簇索引，使用插入的數(shù)據(jù)作為葉子節(jié)點(diǎn)，通過主鍵可以很快找到葉子節(jié)點(diǎn)，從而快速獲取記錄。主鍵最好要自增。因?yàn)樽栽鲋麈I可以讓插入的數(shù)據(jù)按主鍵順序插入到底層的B+樹的葉子節(jié)點(diǎn)中，由于是按序的，這種插入幾乎不需要去移動(dòng)已有的其它數(shù)據(jù)，所以插入效率很高。如果主鍵不是自增的，那么每次主鍵的值近似隨機(jī)，這時(shí)候就有可能需要移動(dòng)大量數(shù)據(jù)來(lái)保證B+樹的特性，增加了不必要的開銷。

對(duì)于mysql的join，它用的是Nested Loop Join算法，也就是通過前一個(gè)表查詢的結(jié)果集去后一個(gè)表中查詢，比如前一個(gè)表的結(jié)果集是100條數(shù)據(jù)，后一個(gè)表有10W數(shù)據(jù)，那么就需要在100*10W的數(shù)據(jù)集合中去過濾得到最終的結(jié)果集。因此，盡量用小結(jié)果集的表去和大表做join，同時(shí)在join的字段上建立索引，如果建不了索引，就需要設(shè)置足夠大的join buffer size