數(shù)據(jù)庫優(yōu)化的幾種方式詳細(xì)介紹

1、Sql優(yōu)化主要優(yōu)化的還是查詢, 優(yōu)化查詢的話, 索引優(yōu)化是最有效的方案。

首先要根據(jù)需求寫出結(jié)構(gòu)良好的SQL，然后根據(jù)SQL 在表中建立有效的索引。但是如果索引太多，不但會影響寫入的效率，對查詢也有一定的影響。

定位慢SQL然后并優(yōu)化

這是最常用，每一個技術(shù)人員都應(yīng)該掌握基本的SQL調(diào)優(yōu)手段(包括方法、工具、輔助系統(tǒng)等)。這里以MySQL為例，最常見的方式是，由自帶的慢查詢?nèi)罩净蛘唛_源的慢查詢系統(tǒng)定位到具體的出問題的SQL，然后使用explain。profile等工具來逐步調(diào)優(yōu)，最后經(jīng)過測試達(dá)到效果后上線。

explain + sql語句查詢sql執(zhí)行過程, 通過執(zhí)行計劃，我們能得到哪些信息：

A：哪些步驟花費的成本比較高

B：哪些步驟產(chǎn)生的數(shù)據(jù)量多，數(shù)據(jù)量的多少用線條的粗細(xì)表示，很直觀

C：每一步執(zhí)行了什么動作

優(yōu)化索引

(1)索引列務(wù)必重復(fù)度低, where條件字段上需要建立索引

(2)使用索引就不能用OR查詢，否則索引不起作用

(3)使用索引，like模糊查詢不能以%開頭

(4)查詢條件務(wù)必以索引列開頭，否則索引失效

(5)復(fù)合索引遵守最左原則。

避免索引失效

A：盡量不要在where 子句中對字段進(jìn)行null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描

B：應(yīng)盡量避免在where 子句中使用!= 或<> 操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描。

C：應(yīng)盡量避免在where 子句中使用or 來連接條件，如果一個字段有索引，一個字段沒有索引，將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描

D：不做列運(yùn)算where age + 1 = 10，任何對列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫教程函數(shù)。計算表達(dá)式等, 都會是索引失效。

E：查詢like，如果是‘%aaa’ 也會造成索引失效。

2、Sql語句調(diào)優(yōu)

·根據(jù)業(yè)務(wù)場景建立覆蓋索引只查詢業(yè)務(wù)需要的字段，如果這些字段被索引覆蓋，將極大的提高查詢效率。

·多表連接的字段上需要建立索引，這樣可以極大提高表連接的效率。

·where 條件字段上需要建立索引, 但Where 條件上不要使用運(yùn)算函數(shù)，以免索引失效。

·排序字段上, 分組字段上需要建立索引。

·優(yōu)化insert 語句: 批量列插入數(shù)據(jù)要比單個列插入數(shù)據(jù)效率高。

·優(yōu)化order by 語句: 在使用order by 語句時, 不要使用select *,select 后面要查有索引的列, 如果一條sql 語句中對多個列進(jìn)行排序, 在業(yè)務(wù)允許情況下, 盡量同時用升序或同時用降序。

·優(yōu)化group by 語句: 在我們對某一個字段進(jìn)行分組的時候, Mysql默認(rèn)就進(jìn)行了排序, 但是排序并不是我們業(yè)務(wù)所需的, 額外的排序會降低效率。 所以在用的時候可以禁止排序, 使用order by null禁用。

select age, count(*) from emp group by age order by null

·盡量避免子查詢, 可以將子查詢優(yōu)化為join 多表連接查詢。

3、合理的數(shù)據(jù)庫設(shè)計

根據(jù)數(shù)據(jù)庫三范式來進(jìn)行表結(jié)構(gòu)的設(shè)計。設(shè)計表結(jié)構(gòu)時，就需要考慮如何設(shè)計才能更有效的查詢, 遵循數(shù)據(jù)庫三范式：

i. 第一范式：數(shù)據(jù)表中每個字段都必須是不可拆分的最小單元，也就是確保每一列的原子性;

ii. 第二范式：滿足一范式后，表中每一列必須有唯一性，都必須依賴于主鍵;

iii. 第三范式：滿足二范式后，表中的每一列只與主鍵直接相關(guān)而不是間接相關(guān)(外鍵也是直接相關(guān))，字段沒有冗余。

注意：沒有最好的設(shè)計，只有最合適的設(shè)計，所以不要過分注重理論。三范式可以作為一個基本依據(jù)，不要生搬硬套。

有時候可以根據(jù)場景合理地反規(guī)范化：

A：分割表。

B：保留冗余字段。當(dāng)兩個或多個表在查詢中經(jīng)常需要連接時，可以在其中一個表上增加若干冗余的字段，以避免表之間的連接過于頻繁，一般在冗余列的數(shù)據(jù)不經(jīng)常變動的情況下使用。

C：增加派生列。派生列是由表中的其它多個列的計算所得，增加派生列可以減少統(tǒng)計運(yùn)算，在數(shù)據(jù)匯總時可以大大縮短運(yùn)算時間, 前提是這個列經(jīng)常被用到, 這也就是反第三范式。

4、分表

水平分割(按行)，垂直分割(按列)

分表場景

A： 根據(jù)經(jīng)驗，MySQL 表數(shù)據(jù)一般達(dá)到百萬級別，查詢效率就會很低。

B： 一張表的某些字段值比較大并且很少使用?？梢詫⑦@些字段隔離成單獨一張表，通過外鍵關(guān)聯(lián)，例如考試成績，我們通常關(guān)注分?jǐn)?shù)，不關(guān)注考試詳情。

水平分表策略

C：按時間分表：當(dāng)數(shù)據(jù)有很強(qiáng)的實效性，例如微博的數(shù)據(jù)，可以按月分割。

按區(qū)間分表：例如用戶表1 到一百萬用一張表，一百萬到兩百萬用一張表。

hash分表：通過一個原始目標(biāo)id 或者是名稱按照一定的hash 算法計算出數(shù)據(jù)存儲的表名。

猜你喜歡：

MySQL數(shù)據(jù)庫的高效檢索如何實現(xiàn)？

如何修改MySQL數(shù)據(jù)庫字符集編碼？

數(shù)據(jù)庫和數(shù)據(jù)庫系統(tǒng)分別是什么？

JDBC詳細(xì)教程與jdbc連接數(shù)據(jù)庫方法

黑馬程序員java培訓(xùn)課程