數(shù)據(jù)庫(kù)的那點(diǎn)事

2024-09-07 22:12:45

字體：大中小

供稿：網(wǎng)友

談到數(shù)據(jù)庫(kù)，大家第一想法就是怎么去優(yōu)化，怎么讓查詢操作更快。我認(rèn)為最好的方式就是從開(kāi)始數(shù)據(jù)庫(kù)設(shè)計(jì)的時(shí)候就要盡量考慮周全。如果不幸是個(gè)老項(xiàng)目，就得從優(yōu)化入手了。接下就從設(shè)計(jì)和優(yōu)化談一下我的一些認(rèn)識(shí)和經(jīng)驗(yàn)。

1：數(shù)據(jù)庫(kù)的設(shè)計(jì)

一個(gè)好的數(shù)據(jù)庫(kù)設(shè)計(jì)方案對(duì)于數(shù)據(jù)庫(kù)的性能常常會(huì)起到事半功倍的效果。數(shù)據(jù)庫(kù)的設(shè)計(jì)包含數(shù)據(jù)庫(kù)架構(gòu)和業(yè)務(wù)表的設(shè)計(jì)。

1）數(shù)據(jù)庫(kù)架構(gòu)

根據(jù)不同的數(shù)據(jù)量和訪問(wèn)量，來(lái)設(shè)計(jì)不同的架構(gòu)。適合自己的才是最好的。

單實(shí)例：數(shù)據(jù)讀取和寫入都是一個(gè)數(shù)據(jù)庫(kù)實(shí)例。（備份實(shí)例不算在內(nèi)）。這個(gè)適用于小型的企業(yè)內(nèi)部系統(tǒng)。缺點(diǎn)是只適合數(shù)據(jù)量少的場(chǎng)景，優(yōu)點(diǎn)是能達(dá)到數(shù)據(jù)的強(qiáng)一致性。數(shù)據(jù)庫(kù)的那些事（全是干貨）
垂直拆分，多實(shí)例。不同的業(yè)務(wù)走不同的實(shí)例。同樣也是適用于單個(gè)業(yè)務(wù)，數(shù)據(jù)量不大，并且每個(gè)業(yè)務(wù)相對(duì)獨(dú)立，不產(chǎn)生關(guān)聯(lián)。
數(shù)據(jù)庫(kù)的那些事（全是干貨）

讀寫分離，主從架構(gòu)。通過(guò)主從結(jié)構(gòu)，主庫(kù)抗寫壓力，通過(guò)從庫(kù)來(lái)分擔(dān)讀壓力。適用于寫少讀多，數(shù)據(jù)一致性的實(shí)時(shí)性要求不高的應(yīng)用。
數(shù)據(jù)庫(kù)的那些事（全是干貨）

主從，集群結(jié)構(gòu)。適用于寫多，讀也多的應(yīng)用。實(shí)現(xiàn)是最為復(fù)雜的一種，需要考慮數(shù)據(jù)如何路由，后期擴(kuò)容也相對(duì)麻煩。需要設(shè)計(jì)初期在理解業(yè)務(wù)的基礎(chǔ)上，選擇好合適的路由策略。例如range，hash等數(shù)據(jù)庫(kù)的那些事（全是干貨）
云數(shù)據(jù)庫(kù)：阿里云rds等。適合沒(méi)有專業(yè)數(shù)據(jù)庫(kù)運(yùn)維團(tuán)隊(duì)的公司，使用非常方便和簡(jiǎn)單。
2）業(yè)務(wù)表的設(shè)計(jì)

常見(jiàn)業(yè)務(wù)表分類：

（1）配置表

這種表通常存放業(yè)務(wù)一些基礎(chǔ)的配置信息或者字典信息。表的數(shù)據(jù)量一般都比較小，修改變化的操作不太頻繁，通常都是Select查詢操作。

（2）狀態(tài)表

這種表通常存放在業(yè)務(wù)系統(tǒng)中實(shí)體讀象的狀態(tài)信息，常見(jiàn)的有用戶信息表，訂單信息表等。這種表的數(shù)據(jù)量與實(shí)體讀象的規(guī)模有直接關(guān)系，比如一個(gè)APP有多少注冊(cè)用戶，通常這個(gè)APP的用戶表都會(huì)有多少條記錄。狀態(tài)表的變化通常比較頻繁，而且Insert、Update、Select操作都會(huì)有，Delete操作是否有，通常會(huì)根據(jù)業(yè)務(wù)情況的規(guī)定決定。

（3）日志表

這種表通常用來(lái)記錄業(yè)務(wù)系統(tǒng)中某種實(shí)體的狀態(tài)信息，常見(jiàn)的有用戶登錄表、充值信息記錄表等。這種表的數(shù)據(jù)規(guī)模通常比較大，而且如果業(yè)務(wù)狀態(tài)變化頻繁，記錄的變化信息比較多，這種表的數(shù)據(jù)量和插入性能都要求比較高。日志表的操作，通常會(huì)以Insert操作為主，個(gè)別業(yè)務(wù)會(huì)對(duì)日志表進(jìn)行查詢。MySQL五種特殊需求架構(gòu)中的高性能寫入架構(gòu)，主要就是應(yīng)用這種表的需求。

（4）歸檔表

這種表，是將上面三種OLTP業(yè)務(wù)表的數(shù)據(jù)進(jìn)行歸檔或者冷熱分離的表。對(duì)線上業(yè)務(wù)三類表進(jìn)行數(shù)據(jù)歸檔、冷熱分離，一方面可以控制線上業(yè)務(wù)表的數(shù)據(jù)規(guī)模，保證業(yè)務(wù)表性能；另一方面進(jìn)行歸檔后，可用于對(duì)歸檔歷史數(shù)據(jù)進(jìn)行更好的查詢反映和支持。歸檔表的數(shù)據(jù)量大小與對(duì)應(yīng)的線上表大小、歸檔周期有關(guān)。歸檔表的操作，除了歸檔過(guò)程的數(shù)據(jù)加載外，主要就是Select查詢操作了，歸檔后就算是只讀表。

（5）統(tǒng)計(jì)數(shù)據(jù)表

統(tǒng)計(jì)數(shù)據(jù)表，是指業(yè)務(wù)有離線統(tǒng)計(jì)分析需求時(shí)，需要將各種線上表和歸檔表的數(shù)據(jù)，通過(guò)ETL過(guò)程流轉(zhuǎn)到線上OLAP統(tǒng)計(jì)分析系統(tǒng)中的原始數(shù)據(jù)表。這類表通常數(shù)據(jù)量會(huì)非常大，一個(gè)OLAP統(tǒng)計(jì)分析平臺(tái)會(huì)匯總多個(gè)線上業(yè)務(wù)系統(tǒng)的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)分析。統(tǒng)計(jì)數(shù)據(jù)表的操作，除了數(shù)據(jù)流轉(zhuǎn)動(dòng)作外，主要就是各種統(tǒng)計(jì)分析程序的訪問(wèn)計(jì)算。

（6）統(tǒng)計(jì)結(jié)果表

統(tǒng)計(jì)結(jié)果表是在業(yè)務(wù)有離線統(tǒng)計(jì)分析需求時(shí)，各種統(tǒng)計(jì)分析過(guò)程訪問(wèn)統(tǒng)計(jì)數(shù)據(jù)表中的數(shù)據(jù)，按照一定的邏輯進(jìn)行統(tǒng)計(jì)分析后的結(jié)果數(shù)據(jù)。這種統(tǒng)計(jì)結(jié)果數(shù)據(jù)，通常數(shù)據(jù)量會(huì)比較小。統(tǒng)計(jì)結(jié)果表的操作，處理結(jié)果流轉(zhuǎn)動(dòng)作外，主要就是供訪問(wèn)接口進(jìn)行Select查詢。

對(duì)業(yè)務(wù)表類型的梳理，可以對(duì)所有的業(yè)務(wù)系統(tǒng)進(jìn)行一個(gè)大體的劃分，做到心中有數(shù)。

下面是設(shè)計(jì)表的一些經(jīng)驗(yàn)：

將字段很多的表分解成多個(gè)表：對(duì)于字段較多的表，如果有些字段的使用頻率很低，可以將這些字段分離出來(lái)，因?yàn)楫?dāng)一個(gè)表的數(shù)據(jù)量很大時(shí)，會(huì)由于使用頻率低的字段的存在而變慢。
增加中間表：對(duì)于需要經(jīng)常聯(lián)合查詢的表，可以建立中間表以提高查詢效率。
增加冗余字段：合理的加入冗余字段可以提高查詢速度。（冗余字段會(huì)導(dǎo)致一些問(wèn)題，比如，冗余字段的值在一個(gè)表中被修改了，就要同步關(guān)聯(lián)的表，否則會(huì)導(dǎo)致數(shù)據(jù)不一致。這要根據(jù)實(shí)際情況，平衡數(shù)據(jù)庫(kù)性能，進(jìn)行冗余字段的設(shè)計(jì)。）
所有字段均定義為NOT NULL，除非你真的想存儲(chǔ)null。
提前做好數(shù)據(jù)量的預(yù)估，進(jìn)行分表設(shè)計(jì)。不要等需要拆分時(shí)再拆，一般把表的數(shù)據(jù)量控制在千萬(wàn)級(jí)別。當(dāng)單表數(shù)據(jù)量達(dá)到一定程度時(shí)（MySQL5.x時(shí)代的性能拐點(diǎn)則為1KW - 2KW行級(jí)別，具體需根據(jù)實(shí)際情況測(cè)試），為了提升性能，最為常用的方法就是分表。分表的策略可以是垂直拆分（比如：不同訂單狀態(tài)的訂單拆分到不同的表），也可以是水平拆分（比如：按月將訂單拆分到不同表）。如果在業(yè)務(wù)層分表，會(huì)將邏輯變得復(fù)雜，而且分散。可以引入分表的中間件屏蔽分表后的細(xì)節(jié)，讓業(yè)務(wù)層像查詢單表一樣查詢分表后的數(shù)據(jù)。比如Mycat。（訪問(wèn)量不大，但是表數(shù)據(jù)很多的表，我們可以采取分區(qū)表，實(shí)現(xiàn)起來(lái)也比較簡(jiǎn)單）
選擇統(tǒng)一的字符集。MySQL采用“階梯”式的方式來(lái)設(shè)定字符集默認(rèn)值，每個(gè)數(shù)據(jù)庫(kù)，每張表都有自己的默認(rèn)值，它們逐層繼承，最終最靠底層的默認(rèn)設(shè)置將影響你創(chuàng)建的對(duì)象。不同字符集和校對(duì)規(guī)則之間的轉(zhuǎn)換可能會(huì)帶來(lái)額外的系統(tǒng)開(kāi)銷，影響數(shù)據(jù)庫(kù)的性能。
合理的設(shè)置主鍵和索引。主鍵分自增主鍵和業(yè)務(wù)主鍵。自增主鍵：寫入、查詢效率和磁盤利用率都高，但每次查詢都需要兩級(jí)索引，因?yàn)榫€上業(yè)務(wù)不會(huì)有直接使用主鍵列的查詢。業(yè)務(wù)主鍵：寫入、查詢效率和磁盤利用率都低，但可以使用一級(jí)索引，依賴覆蓋索引的特性，某些情況下在非主鍵索引上也可以實(shí)現(xiàn)1次索引完成查詢一般情況下都是采用業(yè)務(wù)主鍵。索引大概分為三類。主鍵索引：InnoDB會(huì)自動(dòng)在表的主鍵上創(chuàng)建索引，數(shù)據(jù)結(jié)構(gòu)使用B+Tree。非主鍵索引：非主鍵列上的索引為二級(jí)索引(因?yàn)橐淮尾樵冃枰檎覂蓚€(gè)索引樹(shù)) 聯(lián)合索引：聯(lián)合索引也叫多列索引，索引結(jié)構(gòu)的key包含多個(gè)字段，排序時(shí)先第一列比較，如果相同再按第二列比較，以此類推。
2：數(shù)據(jù)庫(kù)的優(yōu)化（mysql）

說(shuō)起mysql優(yōu)化，一定要了解一下mysql原理，這樣才能深入的理解那些sql規(guī)則。下圖展示了MySQL的邏輯架構(gòu)圖。

MySQL客戶端/服務(wù)端通信協(xié)議是“半雙工”的：在任一時(shí)刻，要么是服務(wù)器向客戶端發(fā)送數(shù)據(jù)，要么是客戶端向服務(wù)器發(fā)送數(shù)據(jù)，這兩個(gè)動(dòng)作不能同時(shí)發(fā)生。一旦一端開(kāi)始發(fā)送消息，另一端要接收完整個(gè)消息才能響應(yīng)它，所以我們無(wú)法也無(wú)須將一個(gè)消息切成小塊獨(dú)立發(fā)送，也沒(méi)有辦法進(jìn)行流量控制。客戶端用一個(gè)單獨(dú)的數(shù)據(jù)包將查詢請(qǐng)求發(fā)送給服務(wù)器，所以當(dāng)查詢語(yǔ)句很長(zhǎng)的時(shí)候，需要設(shè)置max_allowed_packet參數(shù)。當(dāng)服務(wù)器響應(yīng)客戶端請(qǐng)求時(shí)，客戶端必須完整的接收整個(gè)返回結(jié)果，而不能簡(jiǎn)單的只取前面幾條結(jié)果，然后讓服務(wù)器停止發(fā)送。因而在實(shí)際開(kāi)發(fā)中，盡量保持查詢簡(jiǎn)單且只返回必需的數(shù)據(jù)，減小通信間數(shù)據(jù)包的大小和數(shù)量是一個(gè)非常好的習(xí)慣，這也是查詢中盡量避免使用SELECT *以及加上LIMIT限制的原因之一。

1）sql的優(yōu)化。通過(guò)explain和show profiles進(jìn)行執(zhí)行計(jì)劃分析，找出問(wèn)題，進(jìn)行針對(duì)性的優(yōu)化。其中創(chuàng)建高效索引是最有效的一個(gè)手段。

多列索引和索引順序：出現(xiàn)多個(gè)索引做相交操作時(shí)（多個(gè)AND條件），通常來(lái)說(shuō)一個(gè)包含所有相關(guān)列的索引要優(yōu)于多個(gè)獨(dú)立索引。在選擇性高的字段上建立索引，可以讓MySQL在查詢時(shí)過(guò)濾掉更多的行。對(duì)于多列索引，哪個(gè)索引字段在前面，取決于索引的選擇性的高低。選擇性高的索引排在前面，有利于提高查詢效率。例如聯(lián)合索引（user_group_id，trade_amount）用戶的群組肯定比訂單的交易金額的選擇性高。
覆蓋索引：如果一個(gè)索引包含或者說(shuō)覆蓋所有需要查詢的字段的值，那么就沒(méi)有必要再回表查詢，這就稱為覆蓋索引。覆蓋索引是非常有用的工具，可以極大的提高性能，因?yàn)椴樵冎恍枰獟呙杷饕龝?huì)帶來(lái)許多好處：
優(yōu)化關(guān)聯(lián)查詢：以小表驅(qū)動(dòng)大表。
子查詢盡量換成join。這是因?yàn)閖oin，MySQL不需要在內(nèi)存中創(chuàng)建臨時(shí)表來(lái)完成這個(gè)邏輯上的需求。
確保任何的GROUP BY和ORDER BY中的表達(dá)式只涉及到一個(gè)表中的列，這樣MySQL才有可能使用索引來(lái)優(yōu)化。
優(yōu)化LIMIT分頁(yè)：一個(gè)常見(jiàn)的問(wèn)題是當(dāng)偏移量非常大的時(shí)候，比如：LIMIT 10000 20這樣的查詢，MySQL需要查詢10020條記錄然后只返回20條記錄，前面的10000條都將被拋棄，這樣的代價(jià)非常高。優(yōu)化這種查詢一個(gè)最簡(jiǎn)單的辦法就是盡可能的使用覆蓋索引掃描，而不是查詢所有的列。然后根據(jù)需要做一次關(guān)聯(lián)查詢?cè)俜祷厮械牧小?duì)于偏移量很大時(shí)，這樣做的效率會(huì)提升非常大。考慮下面的查詢：修改前：SELECT film_id,description FROM film ORDER BY title LIMIT 50,5; 修改后： SELECT film.film_id,film.description FROM film INNER JOIN ( SELECT film_id FROM film ORDER BY title LIMIT 50,5 ) AS tmp USING(film_id);
優(yōu)化UNION：除非確實(shí)需要服務(wù)器去重，否則就一定要使用UNION ALL，如果沒(méi)有ALL關(guān)鍵字，MySQL會(huì)給臨時(shí)表加上DISTINCT選項(xiàng)，這會(huì)導(dǎo)致整個(gè)臨時(shí)表的數(shù)據(jù)做唯一性檢查，這樣做的代價(jià)非常高。
避免導(dǎo)致索引失效的寫法（1）負(fù)向條件查詢不能使用索引（not in/not exists都不是好習(xí)慣）（2）前導(dǎo)模糊查詢不能使用索引（like'XX%'）（3）數(shù)據(jù)區(qū)分度不大的字段不宜使用索引（4）在屬性上進(jìn)行計(jì)算不能命中索引（5）復(fù)合索引最左前綴不滿足
強(qiáng)制類型轉(zhuǎn)換會(huì)全表掃描
如果明確知道只有一條結(jié)果返回，limit 1能夠提高效率
2）合理設(shè)置mysql的部分參數(shù)，達(dá)到最高效。

thread_pool_size：如果主引擎(primary storage engine)為InnoDB,thread_pool_size最佳設(shè)置可能在16和36之間，最常見(jiàn)的優(yōu)化值傾向于24到36。
thread_pool_stall_limit：用處理被阻塞和長(zhǎng)時(shí)間運(yùn)行的語(yǔ)句，確保服務(wù)器不完全被阻塞。設(shè)置過(guò)長(zhǎng)會(huì)導(dǎo)致線程被阻塞，引起性能問(wèn)題。
tmp_table_size:通過(guò)設(shè)置tmp_table_size選項(xiàng)來(lái)增加一張臨時(shí)表的大小，例如做order by ,GROUP BY操作生成的臨時(shí)表。如果調(diào)高該值，MySQL同時(shí)將增加heap表的大小，可達(dá)到提高聯(lián)接查詢速度的效果，建議盡量?jī)?yōu)化查詢，要確保查詢過(guò)程中生成的臨時(shí)表在內(nèi)存中，避免臨時(shí)表過(guò)大導(dǎo)致生成基于硬盤的MyISAM表。

（編輯：武林網(wǎng)）

上一篇：SQL存儲(chǔ)過(guò)程中運(yùn)用BEGIN TRY捕捉錯(cuò)誤并保存

下一篇：innodb next-key lock引發(fā)的死鎖現(xiàn)象解析