1.隨機(jī)讀取連續(xù)多條記錄。經(jīng)過實(shí)踐，可以隨機(jī)讀取多條連續(xù)的數(shù)據(jù)記錄，里頭取值的一般都是主鍵ID來進(jìn)行最大值、最小值的讀?。?/p>
成都創(chuàng)新互聯(lián)"三網(wǎng)合一"的企業(yè)建站思路。企業(yè)可建設(shè)擁有電腦版、微信版、手機(jī)版的企業(yè)網(wǎng)站。實(shí)現(xiàn)跨屏營(yíng)銷，產(chǎn)品發(fā)布一步更新，電腦網(wǎng)絡(luò)+移動(dòng)網(wǎng)絡(luò)一網(wǎng)打盡，滿足企業(yè)的營(yíng)銷需求！成都創(chuàng)新互聯(lián)具備承接各種類型的網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作項(xiàng)目的能力。經(jīng)過10年的努力的開拓，為不同行業(yè)的企事業(yè)單位提供了優(yōu)質(zhì)的服務(wù)，并獲得了客戶的一致好評(píng)。
SELECT?*?FROM?example_table??AS?t1??JOIN?(SELECT?ROUND(RAND()?*?((SELECT?MAX(id)?FROM?example_table)-(SELECT?MIN(id)?FROM?example_table))+(SELECT?MIN(id)?FROM?example_table))?AS?id)?AS?t2?WHERE?t1.id?=?t2.id?ORDER?BY?t1.id?LIMIT?5;
2.隨機(jī)多條數(shù)據(jù)。以下兩種都是隨機(jī)讀取數(shù)據(jù)，查詢數(shù)據(jù)速度、隨機(jī)范圍都基本相差不大
SELECT?*?FROM?example_table?WHERE?id?=?((SELECT?MAX(id)?FROM?example_table)-(SELECT?MIN(id)?FROM?example_table))?*?RAND()?+?(SELECT?MIN(id)?FROM?example_table)?LIMIT?5;
SELECT?*?FROM?example_table?WHERE?id=(SELECT?floor(RAND()?*?((SELECT?MAX(id)?FROM?example_table)-(SELECT?MIN(id)?FROM?example_table))?+?(SELECT?MIN(id)?FROM?example_table)))?ORDER?BY?id?LIMIT?5;
數(shù)據(jù)庫(kù)優(yōu)化有很多可以講，按照支撐的數(shù)據(jù)量來分可以分為兩個(gè)階段：?jiǎn)螜C(jī)數(shù)據(jù)庫(kù)和分庫(kù)分表，前者一般可以支撐500W或者10G以內(nèi)的數(shù)據(jù)，超過這個(gè)值則需要考慮分庫(kù)分表。另外，一般大企業(yè)面試往往會(huì)從單機(jī)數(shù)據(jù)庫(kù)問起，一步一步問到分庫(kù)分表，中間會(huì)穿插很多數(shù)據(jù)庫(kù)優(yōu)化的問題。本文試圖描述單機(jī)數(shù)據(jù)庫(kù)優(yōu)化的一些實(shí)踐，數(shù)據(jù)庫(kù)基于mysql，如有不合理的地方，歡迎指正。
1、表結(jié)構(gòu)優(yōu)化
在開始做一個(gè)應(yīng)用的時(shí)候，數(shù)據(jù)庫(kù)的表結(jié)構(gòu)設(shè)計(jì)往往會(huì)影響應(yīng)用后期的性能，特別是用戶量上來了以后的性能。因此，表結(jié)構(gòu)優(yōu)化是一個(gè)很重要的步驟。
1.1、字符集
一般來說盡量選擇UTF-8，雖然在存中午的時(shí)候GBK比UTF-8使用的存儲(chǔ)空間少，但是UTF-8兼容各國(guó)語言，其實(shí)我們不必為了這點(diǎn)存儲(chǔ)空間而犧牲了擴(kuò)展性。事實(shí)上，后期如果要從GBK轉(zhuǎn)為UTF-8所要付出的代價(jià)是很高的，需要進(jìn)行數(shù)據(jù)遷移，而存儲(chǔ)空間完全可以用花錢擴(kuò)充硬盤來解決。
1.2、主鍵
在使用mysql的innodb的時(shí)候，innodb的底層存儲(chǔ)模型是B+樹，它使用主鍵作為聚簇索引，使用插入的數(shù)據(jù)作為葉子節(jié)點(diǎn)，通過主鍵可以很快找到葉子節(jié)點(diǎn)，從而快速獲取記錄。因此在設(shè)計(jì)表的時(shí)候需要增加一個(gè)主鍵，而且最好要自增。因?yàn)樽栽鲋麈I可以讓插入的數(shù)據(jù)按主鍵順序插入到底層的B+樹的葉子節(jié)點(diǎn)中，由于是按序的，這種插入幾乎不需要去移動(dòng)已有的其它數(shù)據(jù)，所以插入效率很高。如果主鍵不是自增的，那么每次主鍵的值近似隨機(jī)，這時(shí)候就有可能需要移動(dòng)大量數(shù)據(jù)來保證B+樹的特性，增加了不必要的開銷。
1.3、字段
1.3.1、建了索引的字段必須加上not null約束，并且設(shè)置default值
1.3.2、不建議使用float、double來存小數(shù)，防止精度損失，建議使用decimal
1.3.3、不建議使用Text/blob來保存大量數(shù)據(jù)，因?yàn)閷?duì)大文本的讀寫會(huì)造成比較大的I/O開銷，同時(shí)占用mysql的緩存，高并發(fā)下會(huì)極大的降低數(shù)據(jù)庫(kù)的吞吐量，建議將大文本數(shù)據(jù)保存在專門的文件存儲(chǔ)系統(tǒng)中，mysql中只保存這個(gè)文件的訪問地址，比如博客文章可以保存在文件中，mysql中只保存文件的相對(duì)地址。
1.3.4、varchar類型長(zhǎng)度建議不要超過8K。
1.3.5、時(shí)間類型建議使用Datetime，不要使用timestamp，雖然Datetime占用8個(gè)字節(jié)，而timestamp只占用4個(gè)字節(jié)，但是后者要保證非空，而且后者是對(duì)時(shí)區(qū)敏感的。
1.3.6、建議表中增加gmt_create和gmt_modified兩個(gè)字段，用來記錄數(shù)據(jù)創(chuàng)建的修改時(shí)間。這兩個(gè)字段建立的原因是方便查問題。
1.4、索引創(chuàng)建
1.4.1、這個(gè)階段由于對(duì)業(yè)務(wù)并不了解，所以盡量不要盲目加索引，只為一些一定會(huì)用到索引的字段加普通索引。
1.4.2、創(chuàng)建innodb單列索引的長(zhǎng)度不要超過767bytes，如果超過會(huì)用前255bytes作為前綴索引
1.4.3、創(chuàng)建innodb組合索引的各列索引長(zhǎng)度不要超過767bytes，一共加起來不要超過3072bytes
2、SQL優(yōu)化
一般來說sql就那么幾種：基本的增刪改查，分頁(yè)查詢，范圍查詢，模糊搜索，多表連接
2.1、基本查詢
一般查詢需要走索引，如果沒有索引建議修改查詢，把有索引的那個(gè)字段加上，如果由于業(yè)務(wù)場(chǎng)景沒法使用這個(gè)字段，那么需要看這個(gè)查詢調(diào)用量大不大，如果大，比如每天調(diào)用10W+，這就需要新增索引，如果不大，比如每天調(diào)用100+，則可以考慮保持原樣。另外，select * 盡量少用，用到什么字段就在sql語句中加什么，不必要的字段就別查了，浪費(fèi)I/O和內(nèi)存空間。
2.2、高效分頁(yè)
limit m,n其實(shí)質(zhì)就是先執(zhí)行l(wèi)imit m+n，然后從第m行取n行，這樣當(dāng)limit翻頁(yè)越往后翻m越大，性能越低。比如
select * from A limit 100000,10，這種sql語句的性能是很差的，建議改成下面的版本:
selec id,name,age from A where id =(select id from A limit 100000,1) limit 10
2.3、范圍查詢
范圍查詢包括between、大于、小于以及in。Mysql中的in查詢的條件有數(shù)量的限制，若數(shù)量較小可以走索引查詢，若數(shù)量較大，就成了全表掃描了。而between、大于、小于等，這些查詢不會(huì)走索引，所以盡量放在走索引的查詢條件之后。
2.4、模糊查詢like
使用 like %name%這樣的語句是不會(huì)走索引的，相當(dāng)于全表掃描，數(shù)據(jù)量小的時(shí)候不會(huì)有太大的問題，數(shù)據(jù)量大了以后性能會(huì)下降的很厲害，建議數(shù)據(jù)量大了以后使用搜索引擎來代替這種模糊搜索，實(shí)在不行也要在模糊查詢前加個(gè)能走索引的條件。
2.5、多表連接
子查詢和join都可以實(shí)現(xiàn)在多張表之間取數(shù)據(jù)，但是子查詢性能較差，建議將子查詢改成join。對(duì)于mysql的join，它用的是Nested Loop Join算法，也就是通過前一個(gè)表查詢的結(jié)果集去后一個(gè)表中查詢，比如前一個(gè)表的結(jié)果集是100條數(shù)據(jù)，后一個(gè)表有10W數(shù)據(jù)，那么就需要在100*10W的數(shù)據(jù)集合中去過濾得到最終的結(jié)果集。因此，盡量用小結(jié)果集的表去和大表做join，同時(shí)在join的字段上建立索引，如果建不了索引，就需要設(shè)置足夠大的join buffer size。如果以上的技巧都無法解決join所帶來的性能下降的問題，那干脆就別用join了，將一次join查詢拆分成兩次簡(jiǎn)單查詢。另外，多表連接盡量不要超過三張表，超過三張表一般來說性能會(huì)很差，建議拆分sql。
3、數(shù)據(jù)庫(kù)連接池優(yōu)化
數(shù)據(jù)庫(kù)連接池本質(zhì)上是一種緩存，它是一種抗高并發(fā)的手段。數(shù)據(jù)庫(kù)連接池優(yōu)化主要是對(duì)參數(shù)進(jìn)行優(yōu)化，一般我們使用DBCP連接池，它的具體參數(shù)如下：
3.1 initialSize
初始連接數(shù)，這里的初始指的是第一次getConnection的時(shí)候，而不是應(yīng)用啟動(dòng)的時(shí)候。初始值可以設(shè)置為并發(fā)量的歷史平均值
3.2、minIdle
最小保留的空閑連接數(shù)。DBCP會(huì)在后臺(tái)開啟一個(gè)回收空閑連接的線程，當(dāng)該線程進(jìn)行空閑連接回收的時(shí)候，會(huì)保留minIdle個(gè)連接數(shù)。一般設(shè)置為5，并發(fā)量實(shí)在很小可以設(shè)置為1.
3.3、maxIdle
最大保留的空閑連接數(shù)，按照業(yè)務(wù)并發(fā)高峰設(shè)置。比如并發(fā)高峰為20，那么當(dāng)高峰過去后，這些連接不會(huì)馬上被回收，如果過一小段時(shí)間又來一個(gè)高峰，那么連接池就可以復(fù)用這些空閑連接而不需要頻繁創(chuàng)建和關(guān)閉連接。
3.4、maxActive
最大活躍連接數(shù)，按照可以接受的并發(fā)極值設(shè)置。比如單機(jī)并發(fā)量可接受的極值是100，那么這個(gè)maxActive設(shè)置成100后，就只能同時(shí)為100個(gè)請(qǐng)求服務(wù)，多余的請(qǐng)求會(huì)在最大等待時(shí)間之后被拋棄。這個(gè)值必須設(shè)置，可以防止惡意的并發(fā)攻擊，保護(hù)數(shù)據(jù)庫(kù)。
3.5、maxWait
獲取連接的最大等待時(shí)間，建議設(shè)置的短一點(diǎn)，比如3s，這樣可以讓請(qǐng)求快速失敗，因?yàn)橐粋€(gè)請(qǐng)求在等待獲取連接的時(shí)候，線程是不可以被釋放的，而單機(jī)的線程并發(fā)量是有限的，如果這個(gè)時(shí)間設(shè)置的過長(zhǎng)，比如網(wǎng)上建議的60s，那么這個(gè)線程在這60s內(nèi)是無法被釋放的，只要這種請(qǐng)求一多，應(yīng)用的可用線程就少了，服務(wù)就變得不可用了。
3.6、minEvictableIdleTimeMillis
連接保持空閑而不被回收的時(shí)間，默認(rèn)30分鐘。
3.7、validationQuery
用于檢測(cè)連接是否有效的sql語句，一般是一條簡(jiǎn)單的sql，建議設(shè)置
3.8、testOnBorrow
申請(qǐng)連接的時(shí)候?qū)B接進(jìn)行檢測(cè)，不建議開啟，嚴(yán)重影響性能
3.9、testOnReturn
歸還連接的時(shí)候?qū)B接進(jìn)行檢測(cè)，不建議開啟，嚴(yán)重影響性能
3.10、testWhileIdle
開啟了以后，后臺(tái)清理連接的線程會(huì)沒隔一段時(shí)間對(duì)空閑連接進(jìn)行validateObject，如果連接失效則會(huì)進(jìn)行清除，不影響性能，建議開啟
3.11、numTestsPerEvictionRun
代表每次檢查鏈接的數(shù)量，建議設(shè)置和maxActive一樣大，這樣每次可以有效檢查所有的鏈接。
3.12、預(yù)熱連接池
對(duì)于連接池，建議在啟動(dòng)應(yīng)用的時(shí)候進(jìn)行預(yù)熱，在還未對(duì)外提供訪問之前進(jìn)行簡(jiǎn)單的sql查詢，讓連接池充滿必要的連接數(shù)。
4、索引優(yōu)化
當(dāng)數(shù)據(jù)量增加到一定程度后，靠sql優(yōu)化已經(jīng)無法提升性能了，這時(shí)候就需要祭出大招：索引。索引有三級(jí)，一般來說掌握這三級(jí)就足夠了，另外，對(duì)于建立索引的字段，需要考慮其選擇性。
4.1、一級(jí)索引
在where后面的條件上建立索引，單列可以建立普通索引，多列則建立組合索引。組合索引需要注意最左前綴原則。
4.2、二級(jí)索引
如果有被order by或者group by用到的字段，則可以考慮在這個(gè)字段上建索引，這樣一來，由于索引天然有序，可以避免order by以及group by所帶來的排序，從而提高性能。
4.3、三級(jí)索引
如果上面兩招還不行，那么就把所查詢的字段也加上索引，這時(shí)候就形成了所謂的索引覆蓋，這樣做可以減少一次I/O操作，因?yàn)閙ysql在查詢數(shù)據(jù)的時(shí)候，是先查主鍵索引，然后根據(jù)主鍵索引去查普通索引，然后根據(jù)普通索引去查相對(duì)應(yīng)的記錄。如果我們所需要的記錄在普通索引里都有，那就不需要第三步了。當(dāng)然，這種建索引的方式比較極端，不適合一般場(chǎng)景。
4.4、索引的選擇性
在建立索引的時(shí)候，盡量在選擇性高的字段上建立。什么是選擇性高呢？所謂選擇性高就是通過這個(gè)字段查出來的數(shù)據(jù)量少，比如按照名字查一個(gè)人的信息，查出來的數(shù)據(jù)量一般會(huì)很少，而按照性別查則可能會(huì)把數(shù)據(jù)庫(kù)一半的數(shù)據(jù)都查出來，所以，名字是一個(gè)選擇性高的字段，而性別是個(gè)選擇性低的字段。
5、歷史數(shù)據(jù)歸檔
當(dāng)數(shù)據(jù)量到了一年增加500W條的時(shí)候，索引也無能為力，這時(shí)候一般的思路都是考慮分庫(kù)分表。如果業(yè)務(wù)沒有爆發(fā)式增長(zhǎng)，但是數(shù)據(jù)的確在緩慢增加，則可以不考慮分庫(kù)分表這種復(fù)雜的技術(shù)手段，而是進(jìn)行歷史數(shù)據(jù)歸檔。我們針對(duì)生命周期已經(jīng)完結(jié)的歷史數(shù)據(jù)，比如6個(gè)月之前的數(shù)據(jù)，進(jìn)行歸檔。我們可以使用quartz的調(diào)度任務(wù)在凌晨定時(shí)將6個(gè)月之前的數(shù)據(jù)查出來，然后存入遠(yuǎn)程的hbase服務(wù)器。當(dāng)然，我們也需要提供歷史數(shù)據(jù)的查詢接口，以備不時(shí)之需。
以上就是對(duì)mysql 單機(jī)數(shù)據(jù)庫(kù)的優(yōu)化資料整理，后續(xù)繼續(xù)補(bǔ)充相關(guān)資料，謝謝大家對(duì)本站的支持！
order by rand() 就是隨機(jī)排序
order by rand() limit 1 就是隨機(jī)獲取一行數(shù)據(jù).
滿足2個(gè)條件,第一是 `Sex`='男' 和`Ctiy`='廣州'
select * from `user` where`Sex`='男' AND `Ctiy`='廣州' order by rand() limit 1
隨機(jī)讀取可用newid()，取一條可用top 1
舉例：如在表test中隨機(jī)抽取一條未被讀取的數(shù)據(jù)，執(zhí)行語句為：
select?top?1?*?from?test?order?by?newid()第一次執(zhí)行結(jié)果：
第二次執(zhí)行結(jié)果：
結(jié)論：兩次執(zhí)行結(jié)果有可能一樣，也有可能不一樣，也就是實(shí)現(xiàn)了隨機(jī)的效果。
重慶分公司
重慶分公司
