首頁(yè)技術(shù)文章正文

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

更新時(shí)間:2021-04-28 來(lái)源:黑馬程序員 瀏覽量:

1577370495235_學(xué)IT就到黑馬程序員.gif

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

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


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

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

explain + sql語(yǔ)句查詢sql執(zhí)行過(guò)程, 通過(guò)執(zhí)行計(jì)劃,我們能得到哪些信息:

A:哪些步驟花費(fèi)的成本比較高

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

C:每一步執(zhí)行了什么動(dòng)作


優(yōu)化索引

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

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

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

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

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


避免索引失效

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

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

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

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

E:查詢like,如果是‘%aaa’ 也會(huì)造成索引失效。


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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

有時(shí)候可以根據(jù)場(chǎng)景合理地反規(guī)范化:

A:分割表。

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

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


4、分表

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

分表場(chǎng)景

A: 根據(jù)經(jīng)驗(yàn),MySQL 表數(shù)據(jù)一般達(dá)到百萬(wàn)級(jí)別,查詢效率就會(huì)很低。

B: 一張表的某些字段值比較大并且很少使用。可以將這些字段隔離成單獨(dú)一張表,通過(guò)外鍵關(guān)聯(lián),例如考試成績(jī),我們通常關(guān)注分?jǐn)?shù),不關(guān)注考試詳情。

水平分表策略

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

按區(qū)間分表:例如用戶表1 到一百萬(wàn)用一張表,一百萬(wàn)到兩百萬(wàn)用一張表。

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



猜你喜歡:

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

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

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

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

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

分享到:
在線咨詢 我要報(bào)名
和我們?cè)诰€交談!