問答專欄Q & A COLUMN
大家在剛開始搭建項目的時候可能考慮的不夠全面，隨著產(chǎn)品的推廣 、業(yè)務(wù)場景的復(fù)雜和使用用戶越來越多 數(shù)據(jù)會呈現(xiàn)快速增長。當(dāng)數(shù)據(jù)達(dá)到千萬級的時候 就會發(fā)現(xiàn) 查詢速度越來越慢 用戶體驗也就越來越差，那怎樣提升千萬級數(shù)據(jù)查詢效率呢？小萌簡單整理了一下，希望對大家有所幫助！
優(yōu)化數(shù)據(jù)庫設(shè)計：
數(shù)據(jù)字段類型使用varchar/nvarchar 替換 char/nchar，變長字段存儲空間小，節(jié)省存儲空間。在查詢的時候小的空間字段搜索效率更高。
查詢的時候避免全表掃描，可以在where和order by 的字段上建立索引。
where 查詢子句中不對null值做判斷，會導(dǎo)致檢索引擎放棄使用索引而使用全表掃描，如：select id,name from user where age is null 可以設(shè)置age 的默認(rèn)值為0，保證沒有null值，修改后的sql查詢語句為：select id,name from user where age = 0。
謹(jǐn)慎使用索引，索引不是越多越好。一般一張表的索引數(shù)不要超過6個，如果太多要討論業(yè)務(wù)是否合理或者是否索引建在了不常用的字段上。索引可以提高select 查詢的效率，但是也響應(yīng)降低了 insert和update 的效率，因為在執(zhí)行insert和update時也可能會重建索引。
盡量不要更新索引數(shù)據(jù)，因為索引數(shù)據(jù)的順序是表記錄的物理順序，一旦發(fā)生改變將會導(dǎo)致整個表記的順序發(fā)生改變，將會消耗大量資源。如果業(yè)務(wù)需要頻繁更新索引數(shù)據(jù)列 就要考慮索引是否創(chuàng)建合理，比如 用戶ID、身份證號碼或者手機(jī)號碼不經(jīng)常改變的列可以考慮創(chuàng)建索引。
字符型字段如果符合業(yè)務(wù)需求可以修改為數(shù)字類型字段，因為字符型字段會降低查詢和連接的性能，并且增加存儲的開銷。執(zhí)行搜索的適合查詢和連接會逐個比較字符串的每一個字符，如果是數(shù)據(jù)類型比對一次就可以了。
SQL查詢優(yōu)化
where 查詢語句中避免使用**!= 或<> **操作符，搜索引擎會執(zhí)行全表掃描而不執(zhí)行創(chuàng)建的索引。
where 查詢語句中 避免使用 or 來連接條件查詢數(shù)據(jù)，也會導(dǎo)致搜索引擎執(zhí)行全表掃描而不執(zhí)行創(chuàng)建的索引，例如：select id,name from user where age = 18 or age = 25 可以修改為 select id,name from user where age = 18 union all select id,name from user where age = 25。
** in 和 not in也避免使用，也將導(dǎo)致全表掃描，例如：select id,name from user where age in (18,19,20)** 如果是連續(xù)的則可以考慮使用between and，例如：select id,name from user where age between 18 and 20。
like 語句導(dǎo)致全表掃描，例如：select id,name from user where name like ‘%微信%’。
wehre 查詢語句中避免使用參數(shù)，也會全表掃描，SQL在運(yùn)行時才會進(jìn)行局部變量的解析，優(yōu)化程序不能將訪問計劃的選擇推遲到運(yùn)行時；它必須在編譯時進(jìn)行選擇。如果編譯時建立訪問計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進(jìn)行全表掃描：select id,name from user where age = @age 當(dāng)然也可以改為強(qiáng)制使用索引：select id,name from user with(index(索引名)) where age =@ age
where查詢語句避免使用表達(dá)式，也會導(dǎo)致查詢的時候放棄使用索引導(dǎo)致全表掃描。例select id,name from user where age/2 = 10 ** 可以更改為 ** select id,name from user where age = 102*。
where查詢語句避免使用函數(shù)操作，也會導(dǎo)致查詢的時候放棄使用索引導(dǎo)致全表掃描。例如：select id,name from user where substring(name,1,3)=’abc’ 可以改為select id,name from user where name like ‘a(chǎn)bc%’。
不要使用select * from user 查詢，要用具體的字段名。不要返回任何用不到的字段。
不要使用游標(biāo)，大家都知道游標(biāo)的效率非常差。
避免出現(xiàn)大實務(wù)業(yè)務(wù)，會降低系統(tǒng)的并發(fā)能力。
Java后臺優(yōu)化
使用JDBC連接數(shù)據(jù)庫。
合理使用數(shù)據(jù)緩存。
控制好內(nèi)存，不要全部數(shù)據(jù)放到內(nèi)出做處理，可以邊讀邊處理。
少創(chuàng)造對象。
數(shù)據(jù)庫性能優(yōu)化
使用存儲過程
如果在具體業(yè)務(wù)實現(xiàn)過程中，可以使用存儲過程操作數(shù)據(jù)庫可以盡量使用，由于存儲過程是存放在數(shù)據(jù)庫服務(wù)器上的一次性被設(shè)計、編碼、測試，被再次調(diào)用，需要執(zhí)行該存儲過程可以很簡單的使用?？梢蕴岣唔憫?yīng)速度，減少網(wǎng)絡(luò)使用流量等等。
硬件調(diào)整
影響數(shù)據(jù)庫性能的也可能是磁盤和網(wǎng)絡(luò)吞吐量，可以通過擴(kuò)大虛擬內(nèi)存，把數(shù)據(jù)庫服務(wù)器和主服務(wù)器分開部署。數(shù)據(jù)服務(wù)器吞吐量調(diào)為最大。
調(diào)整數(shù)據(jù)庫
如果在實際業(yè)務(wù)實現(xiàn)中對表查詢頻率過高，可以對表創(chuàng)建索引；按照where查詢條件建立索引，盡量為整型鍵建立為有且只有一個簇集索引，數(shù)據(jù)在物理上按順序在數(shù)據(jù)頁上，縮短查找范圍，為在查詢經(jīng)常使用的全部列建立非簇集索引，能最大地覆蓋查詢；但是索引不可太多，執(zhí)行UPDATE DELETE INSERT語句需要用于維護(hù)這些索引的開銷量急劇增加；避免在索引中有太多的索引鍵；避免使用大型數(shù)據(jù)類型的列為索引；保證每個索引鍵值有少數(shù)行。
評論0
加載中...
1.熟悉mysql innodb存儲引擎原理，參考mysql技術(shù)內(nèi)幕innodb存儲引擎
2.熟悉b+樹結(jié)構(gòu)
3.熟悉sql優(yōu)化技巧，參考高性能mysql
4.熟悉鎖，事務(wù)
5.能夠使用常用的監(jiān)控方法和工具，監(jiān)控數(shù)據(jù)庫性能，比如druid，慢sql日志，zabbix
6.能夠評估系統(tǒng)流量，合理使用緩存，隊列，數(shù)據(jù)庫引擎選擇，來提高mysql性能，降低壓力
評論0
加載中...0
回答0
回答0
回答0
回答0
回答0
回答10
回答0
回答0
回答0
回答
