數據庫分表以及優化需要注意什么

這篇文章給大家介紹數據庫分表以及優化需要注意什么，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

　　數據庫設計時，為什么進行分表?分庫?一般多少數據量開始分表?分庫?分庫分表的目的?什么是數據庫垂直拆分?水平拆分?分區等等

　　一：為什么要分表

　　當一張表的數據達到幾百萬時，你查詢一次所花的時間會變多，如果有聯合查詢的話，有可能會死在那兒了。分表的目的就在于此，減小數據庫的負擔，縮短查詢時間。日常開發中我們經常會遇到大表的情況，所謂的大表是指存儲了百萬級乃至千萬級條記錄的表。這樣的表過于龐大，導致數據庫在查詢和插入的時候耗時太長，性能低下，如果涉及聯合查詢的情況，性能會更加糟糕。分表和表分區的目的就是減少數據庫的負擔，提高數據庫的效率，通常點來講就是提高表的增刪改查效率。數據庫中的數據量不一定是可控的，在未進行分庫分表的情況下，隨著時間和業務的發展，庫中的表會越來越多，表中的數據量也會越來越大，相應地，數據操作，增刪改查的開銷也會越來越大;另外，由于無法進行分布式式部署，而一臺服務器的資源(CPU、磁盤、內存、IO 等)是有限的，最終數據庫所能承載的數據量、數據處理能力都將遭遇瓶頸。

　　二：分表的方案

　　1，做 mysql 集群，有人會問 mysql 集群，根分表有什么關系嗎?雖然它不是實際意義上的分表，但是它啟到了分表的作用，做集群的意義是什么呢?為一個數據庫減輕負擔，說白了就是減少 sql 排隊隊列中的 sql 的數量，舉個例子：有 10 個 sql 請求，如果放在一個數據庫服務器的排隊隊列中，他要等很長時間，如果把這 10 個 sql 請求，分配到 5 個數據庫服務器的排隊隊列中，一個數據庫服務器的隊列中只有 2 個，這樣等待時間是不是大大的縮短了呢?

　　linux mysql proxy 的安裝，配置，以及讀寫分離

　　mysql replication 互為主從的安裝及配置，以及數據同步

　　優點：擴展性好，沒有多個分表后的復雜操作(php 代碼)

　　缺點：單個表的數據量還是沒有變，一次操作所花的時間還是那么多，硬件開銷大。

　　2. 數據庫優化有哪些?分別需要注意什么?

　　SQL 優化的原則是：將一次操作需要讀取的 BLOCK 數減到最低，即在最短的時間達到最大的數據吞吐量。

　　調整不良 SQL 通常可以從以下幾點切入：

　　檢查不良的 SQL，考慮其寫法是否還有可優化內容

　　檢查子查詢 考慮 SQL 子查詢是否可以用簡單連接的方式進行重新書寫

　　檢查優化索引的使用

　　考慮數據庫的優化器

　　避免出現 SELECT * FROM table 語句，要明確查出的字段。

　　在一個 SQL 語句中，如果一個 where 條件過濾的數據庫記錄越多，定位越準確，則該 where 條件越應該前移。

　　查詢時盡可能使用索引覆蓋。即對 SELECT 的字段建立復合索引，這樣查詢時只進行索引掃描，不讀取數據塊。

　　在判斷有無符合條件的記錄時建議不要用 SELECT COUNT ()和 select top 1 語句。

　　使用內層限定原則，在拼寫 SQL 語句時，將查詢條件分解、分類，并盡量在 SQL 語句的最里層進行限定，以減少數據的處理量。

　　應絕對避免在 order by 子句中使用表達式。

　　如果需要從關聯表讀數據，關聯的表一般不要超過 7 個。

　　小心使用 IN 和 OR，需要注意 In 集合中的數據量。建議集合中的數據不超過 200 個。

　　<> 用 < 、> 代替，> 用 >= 代替，< 用 <= 代替，這樣可以有效的利用索引。 　　在查詢時盡量減少對多余數據的讀取包括多余的列與多余的行。 　　對于復合索引要注意，例如在建立復合索引時列的順序是 F1，F2，F3，則在 where 或 order by 子句中這些字段出現的順序要與建立索引時的字段順序一致，且必須包含第一列。只能是 F1 或 F1，F2 或 F1，F2，F3。否則不會用到該索引。 　　多表關聯查詢時，寫法必須遵循以下原則，這樣做有利于建立索引，提高查詢效率。格式如下 　　select sum(table1.je) from table1 table1, table2 table2, table3 table3 where (table1的等值條件(=)) and(table1的非等值條件) and (table2與table1的關聯條件) and (table2的等值條件) and (table2的非等值條件) and(table3與table2的關聯條件) and (table3的等值條件) and (table3的非等值條件)。 　　注：關于多表查詢時 from 后面表的出現順序對效率的影響還有待研究。 　　子查詢問題。對于能用連接方式或者視圖方式實現的功能，不要用子查詢 　　在 WHERE 子句中，避免對列的四則運算，特別是 where 條件的左邊，嚴禁使用運算與函數對列進行處理。比如有些地方 substring 可以用 like 代替。 　　如果在語句中有 not in(in)操作，應考慮用 not exists(exists)來重寫，最好的辦法是使用外連接實現。 　　對一個業務過程的處理，應該使事物的開始與結束之間的時間間隔越短越好，原則上做到數據庫的讀操作在前面完成，數據庫寫操作在后面完成，避免交叉。 　　請小心不要對過多的列使用列函數和 order by,group by 等，謹慎使用 disti 軟件開發 t。 　　用 union all 代替 union，數據庫執行 union 操作，首先先分別執行 union 兩端的查詢，將其放在臨時表中，然后在對其進行排序，過濾重復的記錄。 　　當已知的業務邏輯決定 query A 和 query B 中不會有重復記錄時，應該用 union all 代替 union，以提高查詢效率。 　　20、選取最適用的字段屬性 ，MySQL 可以很好的支持大數據量的存取，但是一般說來，數據庫中的表越小，在它上面執行的查詢也就會越快。因此，在創建表的時候，為了獲得更好的性能，我們可以將表中字段的寬度設得盡可能小。 　　例如，在定義郵政編碼這個字段時，如果將其設置為 CHAR (255), 顯然給數據庫增加了不必要的空間，甚至使用 VARCHAR 這種類型也是多余的，因為 CHAR (6) 就可以很好的完成任務了。同樣的，如果可以的話，我們應該使用 MEDIUMINT 而不是 BIGIN 來定義整型字段。 　　另外一個提高效率的方法是在可能的情況下，應該盡量把字段設置為 NOTNULL，這樣在將來執行查詢的時候，數據庫不用去比較 NULL 值。 　　對于某些文本字段，例如 “省份” 或者 “性別”，我們可以將它們定義為 ENUM 類型。因為在 MySQL 中，ENUM 類型被當作數值型數據來處理，而數值型數據被處理起來的速度要比文本類型快得多。這樣，我們又可以提高數據庫的性能。 　　3. web 開發方面會遇到哪些緩存?分別如何優化?

　　瀏覽器緩存

　　在任何現代瀏覽器上 (如 IE, FireFox, Chrome) 折騰清除隱私數據的對話框，你很可能會注意到 “緩存” 這個設置項。

　　代理服務器緩存

　　Web 代理服務器使用同樣的緩存原理，只是規模更大。代理以同樣的方式服務千萬用戶，大公司和 ISP 經常在他們的防火墻或者單獨的設備(也被稱為中介 (intermediaries))上架設代理緩存。

　　網關緩存

　　也被稱為 “反向代理緩存” 或 “替代緩存”。網關緩存同樣是起中介作用的，不過不是網絡管理員部署的，而多半是網站管理員(公司專門的運維工程師、或 UED 或程序組某人 Add)部署，這樣更容易擴展與維護。

　　4. 給你 256M 的內存，統計 10G 文件每個關鍵字出現的次數如何實現?

　　思路

　　$handle=fopen("/tmp/uploadfile.txt","r")ordie("Couldn't get handle");if($handle){while(!feof($handle)){$buffer=fgets($handle,4096);// Process buffer here..}fclose($handle);}

　　5. PHP 的生命周期 / 啟動流程

　　完整的生命周期為模塊初始化、請求初始化、請求處理、請求關閉、模塊關閉五大階段。

　　cli 模式下，每個腳本都會完整的執行上面的五大階段;對于 fastcgi 模式而言，只在啟動時會執行模塊初始化，之后的請求都走了請求初始化、處理請求、請求關閉三大階段，在 fastcgi 關閉時執行模塊關閉階段。各個擴展的加載也是在模塊初始化階段完成的。

　　6. 說一下 PHP 的(內存)垃圾回收機制

　　每一個變量對應一個 zval 數據結構，在該結構內還有一個 val 結構體，該結構體內有一個引用計數(php7 而言，對于 php5，這個引用計數是保存在 zval 結構中的)，標識該對象的引用數，當對象的引用計數為 0 時代表這個對象可被回收。

　　對象的 refcount 減少的時機：修改變量、函數返回(釋放局部變量)、unset 變量

　　對于數組和對象而言，可能存在變量中的成員引用變量本身的情況，也就是循環引用，這樣會造成這個變量永遠不會被內存回收，而成為垃圾。

　　PHP 里對于這種情況給出了垃圾回收機制：如果數組、對象的引用計數減少而且不為零，則認為他們可能是垃圾，把他們放到垃圾收集器里。等垃圾收集器到了一定的數量之后，進行垃圾處理：對所有可能的垃圾 refcount 減 1，如果為 1，說明是垃圾，則進行內存回收;如果不為 1，說明還有其他變量在使用，refcount 重新加 1;這種對象復用以及垃圾回收機制在其他語言中也有體現：redis 中也使用了引用計數表示每個對象的引用數量。

　　7. PHP7 與 PHP5 的區別

　　改進的性能 - 將 PHPNG 代碼合并到 PHP7 中，速度是 PHP 5 的兩倍。

　　降低內存消耗 - 優化的 PHP 7 使用較少的資源。

　　標量類型聲明 - 現在可以強制執行參數和返回類型。

　　一致的 64 位支持 - 對 64 位體系結構機器的一致支持。

　　改進了異常層次 - 異常層次得到了改進

　　許多致命的錯誤轉換為例外 - 例外范圍增加，涵蓋許多致命的錯誤轉換為例外。

　　安全隨機數發生器 - 增加新的安全隨機數發生器 API。

　　已棄用的 SAPI 和擴展已刪除 - 各種舊的和不受支持的 SAPI 和擴展從最新版本中刪除。

　　空合并運算符(?) - 添加了新的空合并運算符。

　　返回和標量類型聲明 - 支持所添加的返回類型和參數類型。

　　匿名類 - 支持匿名添加。

　　零成本斷言 - 支持零成本斷言增加。

　　8. MongoDB 應用場景

　　mongodb 支持副本集、索引、自動分片，可以保證較高的性能和可用性。

　　更高的寫入負載

　　默認情況下，MongoDB 更側重高數據寫入性能，而非事務安全，MongoDB 很適合業務系統中有大量 “低價值” 數據的場景。但是應當避免在高事務安全性的系統中使用 MongoDB，除非能從架構設計上保證事務安全。

　　高可用性

　　MongoDB 的復副集 (Master-Slave) 配置非常簡潔方便，此外，MongoDB 可以快速響應的處理單節點故障，自動、安全的完成故障轉移。這些特性使得 MongoDB 能在一個相對不穩定(如云主機)的環境中，保持高可用性。

　　數據量很大或者未來會變得很大

　　依賴數據庫 (MySQL) 自身的特性，完成數據的擴展是較困難的事，在 MySQL 中，當一個單達表到 5-10GB 時會出現明顯的性能降級，此時需要通過數據的水平和垂直拆分、庫的拆分完成擴展，使用 MySQL 通常需要借助驅動層或代理層完成這類需求。而 MongoDB 內建了多種數據分片的特性，可以很好的適應大數據量的需求。

　　基于位置的數據查詢

　　MongoDB 支持二維空間索引，因此可以快速及精確的從指定位置獲取數據。

　　表結構不明確

　　在一些傳統 RDBMS 中，增加一個字段會鎖住整個數據庫 / 表，或者在執行一個重負載的請求時會明顯造成其它請求的性能降級。通常發生在數據表大于 1G 的時候(當大于 1TB 時更甚)。 因 MongoDB 是文檔型數據庫，為非結構貨的文檔增加一個新字段是很快速的操作，并且不會影響到已有數據。另外一個好處當業務數據發生變化時，是將不在需要由 DBA 修改表結構。

　　9. PHP 短信驗證碼防刷機制

　　1、時間限制：60 秒后才能再次發送

　　從發送驗證碼開始，前端(客戶端)會進行一個 60 秒的倒數，在這一分鐘之內，用戶是無法提交多次發送信息的請求的。這種方法雖然使用得比較普遍，但是卻不是非常有用，技術稍微好點的人完全可以繞過這個限制，直接發送短信驗證碼。

　　2、手機號限制：同一個手機號，24 小時之內不能夠超過 5 條

　　對使用同一個手機號碼進行注冊或者其他發送短信驗證碼的操作的時候，系統可以對這個手機號碼進行限制，例如，24 小時只能發送 5 條短信驗證碼，超出限制則進行報錯(如：系統繁忙，請稍后再試)。然而，這也只能夠避免人工手動刷短信而已，對于批量使用不同手機號碼來刷短信的機器，這種方法也是無可奈何的。

　　3、短信驗證碼限制：30 分鐘之內發送同一個驗證碼

　　網上還有一種方法說：30 分鐘之內，所有的請求，所發送的短信驗證碼都是同一個驗證碼。第一次請求短信接口，然后緩存短信驗證碼結果，30 分鐘之內再次請求，則直接返回緩存的內容。對于這種方式，不是很清楚短信接口商會不會對發送緩存信息收取費用，如果有興趣可以了解了解。

　　4、前后端校驗：提交 Token 參數校驗

　　這種方式比較少人說到，個人覺得可以這種方法值得一試。前端(客戶端)在請求發送短信的時候，同時向服務端提交一個 Token 參數，服務端對這個 Token 參數進行校驗，校驗通過之后，再向請求發送短信的接口向用戶手機發送短信。

　　5、唯一性限制：微信產品，限制同一個微信 ID 用戶的請求數量

　　如果是微信的產品的話，可以通過微信 ID 來進行識別，然后對同一個微信 ID 的用戶限制，24 小時之內最多只能夠發送一定量的短信。

　　6、產品流程限制：分步驟進行

　　例如注冊的短信驗證碼使用場景，我們將注冊的步驟分成 2 步，用戶在輸入手機號碼并設置了密碼之后，下一步才進入驗證碼的驗證步驟。

　　7、圖形驗證碼限制：圖形驗證通過后再請求接口

　　用戶輸入圖形驗證碼并通過之后，再請求短信接口獲取驗證碼。為了有更好的用戶體驗，也可以設計成：一開始不需要輸入圖形驗證碼，在操作達到一定量之后，才需要輸入圖形驗證碼。具體情況請根據具體場景來進行設計。

　　8、IP 及 Cookie 限制：限制相同的 IP/Cookie 信息最大數量

　　使用 Cookie 或者 IP，能夠簡單識別同一個用戶，然后對相同的用戶進行限制(如：24 小時內最多只能夠發送 20 條短信)。然而，Cookie 能夠清理、IP 能夠模擬，而且 IP 還會出現局域網相同 IP 的情況，因此，在使用此方法的時候，應該根據具體情況來思考。

　　9、短信預警機制，做好出問題之后的防護

　　以上的方法并不一定能夠完全杜絕短信被刷，因此，我們也應該做好短信的預警機制，即當短信的使用量達到一定量之后，向管理員發送預警信息，管理員可以立刻對短信的接口情況進行監控和防護。

　　10. 如何設計一個高并發的系統

　　① 數據庫的優化，包括合理的事務隔離級別、SQL 語句優化、索引的優化

　　② 使用緩存，盡量減少數據庫 IO

　　③ 分布式數據庫、分布式緩存

　　④ 服務器的負載均衡

　　11. PHP 的控制反轉 (IOC) 和依賴注入 (DI) 概念

　　IOC(inversion of control)控制反轉模式;控制反轉是將組件間的依賴關系從程序內部提到外部來管理;

　　DI(dependency injection)依賴注入模式;依賴注入是指將組件的依賴通過外部以參數或其他形式注入;

　　12. mySQL 里有 2000w 數據，redis 中只存 20w 的數據，如何保證 redis 中的數據都是熱點數據

　　相關知識：redis 內存數據集大小上升到一定大小的時候，就會施行數據淘汰策略(回收策略)。redis 提供 6 種數據淘汰策略：

　　volatile-lru：從已設置過期時間的數據集(server.db [i].expires)中挑選最近最少使用的數據淘汰

　　volatile-ttl：從已設置過期時間的數據集(server.db [i].expires)中挑選將要過期的數據淘汰

　　volatile-random：從已設置過期時間的數據集(server.db [i].expires)中任意選擇數據淘汰

　　allkeys-lru：從數據集(server.db [i].dict)中挑選最近最少使用的數據淘汰

　　allkeys-random：從數據集(server.db [i].dict)中任意選擇數據淘汰

　　no-enviction(驅逐)：禁止驅逐數據

關于數據庫分表以及優化需要注意什么就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。