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