數(shù)據(jù)庫(kù)優(yōu)化策略有哪些？【Java常見(jiàn)面試題】

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

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

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

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

explain + sql語(yǔ)句查詢(xún)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查詢(xún)，否則索引不起作用

(3)使用索引，like模糊查詢(xún)不能以%開(kāi)頭

(4)查詢(xún)條件務(wù)必以索引列開(kāi)頭，否則索引失效

(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è)字段沒(méi)有索引，將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描

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

E：查詢(xún)like，如果是‘%aaa’ 也會(huì)造成索引失效。

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

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

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

·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

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

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ì)才能更有效的查詢(xún), 遵循數(shù)據(jù)庫(kù)三范式：

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

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

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

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

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

A：分割表。

B：保留冗余字段。當(dāng)兩個(gè)或多個(gè)表在查詢(xún)中經(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í)別，查詢(xún)效率就會(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 或者是名稱(chēng)按照一定的hash 算法計(jì)算出數(shù)據(jù)存儲(chǔ)的表名。

猜你喜歡：

什么是關(guān)系型數(shù)據(jù)庫(kù)？看這張圖就懂了

數(shù)據(jù)庫(kù)中字段類(lèi)型和約束有什么作用?

數(shù)據(jù)庫(kù)存儲(chǔ)結(jié)構(gòu)是什么樣的？

關(guān)系型數(shù)據(jù)庫(kù)與非關(guān)系型數(shù)據(jù)庫(kù)有哪些優(yōu)缺點(diǎn)？

傳智教育java培訓(xùn)課程