移動互聯網時代，海量的用戶數據每天都在產生，基於用戶使用數據等這樣的分析，都需要依靠數據統計和分析，當數據量小時，數據庫方面的優化顯得不太重要，一旦數據量越來越大，系統響應會變慢，TPS直線下降，直至服務不可用。
可能有人會問，為何不用Oracle呢？確實，很多開發者寫代碼時並不會關心SQL的問題，凡是性能問題都交給DBA負責SQL優化，可是，不是每一個項目都會有DBA，也不是所有的項目都會採用Oracle數據庫，而且，Oracle數據庫在大數據量的背景下，解決性能問題，也不見的是一個非常輕鬆的事情。
那麼，MySQL能不能支撐億級的數據量呢，我的答案是肯定的，絕大部分的互聯網公司，它們採用的數據存儲方案，絕大部分都是以MySQL為主，不差錢的國企和銀行，以Oracle為主，而且有專職的DBA為你服務。
本文會以一個實際的項目應用為例，層層向大家剖析如何進行數據庫的優化。項目背景是企業級的統一消息處理平臺，客戶數據在5千萬加，每分鐘處理消息流水1千萬，每天消息流水1億左右。
雖說MySQL單表可以存儲10億級的數據，但這個時候性能非常差。既然一張表無法搞定，那麼就想辦法將數據放到多個地方來解決問題吧，於是，數據庫分庫分表的方案便產生了，目前比較普遍的方案有三個：分區，分庫分表，NoSQL/NewSQL。
在實際的項目中，往往是這三種方案的結合來解決問題，目前絕大部分系統的核心數據都是以RDBMS存儲為主，NoSQL/NewSQL存儲為輔。
分區
分區方案
分區表是由多個相關的底層表實現，這些底層表也是由句柄對象表示，所以我們也可以直接訪問各個分區，存儲引擎管理分區的各個底層表和管理普通表一樣（所有的底層表都必須使用相同的存儲引擎），分區表的索引只是在各個底層表上各自加上一個相同的索引，從存儲引擎的角度來看，底層表和一個普通表沒有任何不同，存儲引擎也無須知道這是一個普通表還是一個分區表的一部分。這個方案也不錯，它對用戶屏蔽了sharding的細節，即使查詢條件沒有sharding column，它也能正常工作（只是這時候性能一般）。不過它的缺點很明顯：很多的資源都受到單機的限制，例如連接數，網絡吞吐等。如何進行分區，在實際應用中是一個非常關鍵的要素之一。在我們的項目中，以客戶信息為例，客戶數據量5000萬加，項目背景要求保存客戶的銀行卡綁定關係，客戶的證件綁定關係，以及客戶綁定的業務信息。此業務背景下，該如何設計數據庫呢。項目一期的時候，我們建立了一張客戶業務綁定關係表，裡面冗餘了每一位客戶綁定的業務信息。基本結構大致如下：
移動互聯網時代，海量的用戶數據每天都在產生，基於用戶使用數據等這樣的分析，都需要依靠數據統計和分析，當數據量小時，數據庫方面的優化顯得不太重要，一旦數據量越來越大，系統響應會變慢，TPS直線下降，直至服務不可用。
可能有人會問，為何不用Oracle呢？確實，很多開發者寫代碼時並不會關心SQL的問題，凡是性能問題都交給DBA負責SQL優化，可是，不是每一個項目都會有DBA，也不是所有的項目都會採用Oracle數據庫，而且，Oracle數據庫在大數據量的背景下，解決性能問題，也不見的是一個非常輕鬆的事情。
那麼，MySQL能不能支撐億級的數據量呢，我的答案是肯定的，絕大部分的互聯網公司，它們採用的數據存儲方案，絕大部分都是以MySQL為主，不差錢的國企和銀行，以Oracle為主，而且有專職的DBA為你服務。
本文會以一個實際的項目應用為例，層層向大家剖析如何進行數據庫的優化。項目背景是企業級的統一消息處理平臺，客戶數據在5千萬加，每分鐘處理消息流水1千萬，每天消息流水1億左右。
雖說MySQL單表可以存儲10億級的數據，但這個時候性能非常差。既然一張表無法搞定，那麼就想辦法將數據放到多個地方來解決問題吧，於是，數據庫分庫分表的方案便產生了，目前比較普遍的方案有三個：分區，分庫分表，NoSQL/NewSQL。
在實際的項目中，往往是這三種方案的結合來解決問題，目前絕大部分系統的核心數據都是以RDBMS存儲為主，NoSQL/NewSQL存儲為輔。
分區
分區方案
分區表是由多個相關的底層表實現，這些底層表也是由句柄對象表示，所以我們也可以直接訪問各個分區，存儲引擎管理分區的各個底層表和管理普通表一樣（所有的底層表都必須使用相同的存儲引擎），分區表的索引只是在各個底層表上各自加上一個相同的索引，從存儲引擎的角度來看，底層表和一個普通表沒有任何不同，存儲引擎也無須知道這是一個普通表還是一個分區表的一部分。這個方案也不錯，它對用戶屏蔽了sharding的細節，即使查詢條件沒有sharding column，它也能正常工作（只是這時候性能一般）。不過它的缺點很明顯：很多的資源都受到單機的限制，例如連接數，網絡吞吐等。如何進行分區，在實際應用中是一個非常關鍵的要素之一。在我們的項目中，以客戶信息為例，客戶數據量5000萬加，項目背景要求保存客戶的銀行卡綁定關係，客戶的證件綁定關係，以及客戶綁定的業務信息。此業務背景下，該如何設計數據庫呢。項目一期的時候，我們建立了一張客戶業務綁定關係表，裡面冗餘了每一位客戶綁定的業務信息。基本結構大致如下：
查詢時，對銀行卡做索引，業務編號做索引，證件號做索引。隨著需求大增多，這張表的索引會達到10個以上。而且客戶解約再簽約，裡面會保存兩條數據，只是綁定的狀態不同。假設我們有5千萬的客戶，5個業務類型，每位客戶平均2張卡，那麼這張表的數據量將會達到驚人的5億，事實上我們系統用戶量還沒有過百萬時就已經不行了。MySQL數據庫中的數據是以文件的形勢存在磁盤上的，默認放在/mysql/data下面（可以通過my.cnf中的datadir來查看）， 一張表主要對應著三個文件，一個是frm存放表結構的，一個是myd存放表數據的，一個是myi存表索引的。這三個文件都非常的龐大，尤其是.myd文件，快5個G了。下面進行第一次分區優化，MySQL支持的分區方式有四種：
移動互聯網時代，海量的用戶數據每天都在產生，基於用戶使用數據等這樣的分析，都需要依靠數據統計和分析，當數據量小時，數據庫方面的優化顯得不太重要，一旦數據量越來越大，系統響應會變慢，TPS直線下降，直至服務不可用。
可能有人會問，為何不用Oracle呢？確實，很多開發者寫代碼時並不會關心SQL的問題，凡是性能問題都交給DBA負責SQL優化，可是，不是每一個項目都會有DBA，也不是所有的項目都會採用Oracle數據庫，而且，Oracle數據庫在大數據量的背景下，解決性能問題，也不見的是一個非常輕鬆的事情。
那麼，MySQL能不能支撐億級的數據量呢，我的答案是肯定的，絕大部分的互聯網公司，它們採用的數據存儲方案，絕大部分都是以MySQL為主，不差錢的國企和銀行，以Oracle為主，而且有專職的DBA為你服務。
本文會以一個實際的項目應用為例，層層向大家剖析如何進行數據庫的優化。項目背景是企業級的統一消息處理平臺，客戶數據在5千萬加，每分鐘處理消息流水1千萬，每天消息流水1億左右。
雖說MySQL單表可以存儲10億級的數據，但這個時候性能非常差。既然一張表無法搞定，那麼就想辦法將數據放到多個地方來解決問題吧，於是，數據庫分庫分表的方案便產生了，目前比較普遍的方案有三個：分區，分庫分表，NoSQL/NewSQL。
在實際的項目中，往往是這三種方案的結合來解決問題，目前絕大部分系統的核心數據都是以RDBMS存儲為主，NoSQL/NewSQL存儲為輔。
分區
分區方案
分區表是由多個相關的底層表實現，這些底層表也是由句柄對象表示，所以我們也可以直接訪問各個分區，存儲引擎管理分區的各個底層表和管理普通表一樣（所有的底層表都必須使用相同的存儲引擎），分區表的索引只是在各個底層表上各自加上一個相同的索引，從存儲引擎的角度來看，底層表和一個普通表沒有任何不同，存儲引擎也無須知道這是一個普通表還是一個分區表的一部分。這個方案也不錯，它對用戶屏蔽了sharding的細節，即使查詢條件沒有sharding column，它也能正常工作（只是這時候性能一般）。不過它的缺點很明顯：很多的資源都受到單機的限制，例如連接數，網絡吞吐等。如何進行分區，在實際應用中是一個非常關鍵的要素之一。在我們的項目中，以客戶信息為例，客戶數據量5000萬加，項目背景要求保存客戶的銀行卡綁定關係，客戶的證件綁定關係，以及客戶綁定的業務信息。此業務背景下，該如何設計數據庫呢。項目一期的時候，我們建立了一張客戶業務綁定關係表，裡面冗餘了每一位客戶綁定的業務信息。基本結構大致如下：
查詢時，對銀行卡做索引，業務編號做索引，證件號做索引。隨著需求大增多，這張表的索引會達到10個以上。而且客戶解約再簽約，裡面會保存兩條數據，只是綁定的狀態不同。假設我們有5千萬的客戶，5個業務類型，每位客戶平均2張卡，那麼這張表的數據量將會達到驚人的5億，事實上我們系統用戶量還沒有過百萬時就已經不行了。MySQL數據庫中的數據是以文件的形勢存在磁盤上的，默認放在/mysql/data下面（可以通過my.cnf中的datadir來查看）， 一張表主要對應著三個文件，一個是frm存放表結構的，一個是myd存放表數據的，一個是myi存表索引的。這三個文件都非常的龐大，尤其是.myd文件，快5個G了。下面進行第一次分區優化，MySQL支持的分區方式有四種：
在我們的項目中，range分區和list分區沒有使用場景，如果基於綁定編號做range或者list分區，綁定編號沒有實際的業務含義，無法通過它進行查詢，因此，我們就剩下 HASH 分區和 KEY 分區了， HASH 分區僅支持int類型列的分區，且是其中的一列。看看我們的庫表結構，發現沒有哪一列是int類型的，如何做分區呢？可以增加一列，綁定時間列，將此列設置為int類型，然後按照綁定時間進行分區，將每一天綁定的用戶分到同一個區裡面去。這次優化之後，我們的插入快了許多，但是查詢依然很慢，為什麼，因為在做查詢的時候，我們也只是根據銀行卡或者證件號進行查詢，並沒有根據時間查詢，相當於每次查詢，MySQL都會將所有的分區表查詢一遍。
然後進行第二次方案優化，既然hash分區和key分區要求其中的一列必須是int類型的，那麼創造出一個int類型的列出來分區是否可以。分析發現，銀行卡的那串數字有祕密。銀行卡一般是16位到19位不等的數字串，我們取其中的某一位拿出來作為表分區是否可行呢，通過分析發現，在這串數字中，其中確實有一位是0到9隨機生成的，不同的卡串長度，這一位不同，絕不是最後一位，最後位數字一般都是校驗位，不具有隨機性。我們新設計的方案，基於銀行卡號+隨機位進行KEY分區，每次查詢的時候，通過計算截取出這位隨機位數字，再加上卡號，聯合查詢，達到了分區查詢的目的，需要說明的是，分區後，建立的索引，也必須是分區列，否則的話，MySQL還是會在所有的分區表中查詢數據。那麼通過銀行卡號查詢綁定關係的問題解決了，那麼證件號呢，如何通過證件號來查詢綁定關係。前面已經講過，做索引一定是要在分區健上進行，否則會引起全表掃描。
我們再創建了一張新表，保存客戶的證件號綁定關係，每位客戶的證件號都是唯一的，新的證件號綁定關係表裡，證件號作為了主鍵，那麼如何來計算這個分區健呢，客戶的證件信息比較龐雜，有身份證號，港澳臺通行證，機動車駕駛證等等，如何在無序的證件號裡找到分區健。
為了解決這個問題，我們將證件號綁定關係表一分為二，其中的一張表專用於保存身份證類型的證件號，另一張表則保存其他證件類型的證件號，在身份證類型的證件綁定關係表中，我們將身份證號中的月數拆分出來作為了分區健，將同一個月出生的客戶證件號保存在同一個區，這樣分成了12個區，其他證件類型的證件號，數據量不超過10萬，就沒有必要進行分區了。這樣每次查詢時，首先通過證件類型確定要去查詢哪張表，再計算分區健進行查詢。
作了分區設計之後，保存2000萬用戶數據的時候，銀行卡表的數據保存文件就分成了10個小文件，證件表的數據保存文件分成了12個小文件，解決了這兩個查詢的問題，還剩下一個問題就是，業務編號呢，怎麼辦，一個客戶有多個簽約業務，如何進行保存，這時候，採用分區的方案就不太合適了，它需要用到分表的方案。
分庫分表
如何進行分庫分表，目前互聯網上有許多的版本，比較知名的一些方案：
- 阿里的TDDL，DRDS和cobar，
- 京東金融的sharding-jdbc；
- 間組織的MyCAT；
- 360的Atlas；
- 美團的zebra；
- 其他比如網易、58、京東等公司都有自研的中間件。
但是這麼多的分庫分表中間件方案，歸總起來，就兩類：client模式和proxy模式。
移動互聯網時代，海量的用戶數據每天都在產生，基於用戶使用數據等這樣的分析，都需要依靠數據統計和分析，當數據量小時，數據庫方面的優化顯得不太重要，一旦數據量越來越大，系統響應會變慢，TPS直線下降，直至服務不可用。
可能有人會問，為何不用Oracle呢？確實，很多開發者寫代碼時並不會關心SQL的問題，凡是性能問題都交給DBA負責SQL優化，可是，不是每一個項目都會有DBA，也不是所有的項目都會採用Oracle數據庫，而且，Oracle數據庫在大數據量的背景下，解決性能問題，也不見的是一個非常輕鬆的事情。
那麼，MySQL能不能支撐億級的數據量呢，我的答案是肯定的，絕大部分的互聯網公司，它們採用的數據存儲方案，絕大部分都是以MySQL為主，不差錢的國企和銀行，以Oracle為主，而且有專職的DBA為你服務。
本文會以一個實際的項目應用為例，層層向大家剖析如何進行數據庫的優化。項目背景是企業級的統一消息處理平臺，客戶數據在5千萬加，每分鐘處理消息流水1千萬，每天消息流水1億左右。
雖說MySQL單表可以存儲10億級的數據，但這個時候性能非常差。既然一張表無法搞定，那麼就想辦法將數據放到多個地方來解決問題吧，於是，數據庫分庫分表的方案便產生了，目前比較普遍的方案有三個：分區，分庫分表，NoSQL/NewSQL。
在實際的項目中，往往是這三種方案的結合來解決問題，目前絕大部分系統的核心數據都是以RDBMS存儲為主，NoSQL/NewSQL存儲為輔。
分區
分區方案
分區表是由多個相關的底層表實現，這些底層表也是由句柄對象表示，所以我們也可以直接訪問各個分區，存儲引擎管理分區的各個底層表和管理普通表一樣（所有的底層表都必須使用相同的存儲引擎），分區表的索引只是在各個底層表上各自加上一個相同的索引，從存儲引擎的角度來看，底層表和一個普通表沒有任何不同，存儲引擎也無須知道這是一個普通表還是一個分區表的一部分。這個方案也不錯，它對用戶屏蔽了sharding的細節，即使查詢條件沒有sharding column，它也能正常工作（只是這時候性能一般）。不過它的缺點很明顯：很多的資源都受到單機的限制，例如連接數，網絡吞吐等。如何進行分區，在實際應用中是一個非常關鍵的要素之一。在我們的項目中，以客戶信息為例，客戶數據量5000萬加，項目背景要求保存客戶的銀行卡綁定關係，客戶的證件綁定關係，以及客戶綁定的業務信息。此業務背景下，該如何設計數據庫呢。項目一期的時候，我們建立了一張客戶業務綁定關係表，裡面冗餘了每一位客戶綁定的業務信息。基本結構大致如下：
查詢時，對銀行卡做索引，業務編號做索引，證件號做索引。隨著需求大增多，這張表的索引會達到10個以上。而且客戶解約再簽約，裡面會保存兩條數據，只是綁定的狀態不同。假設我們有5千萬的客戶，5個業務類型，每位客戶平均2張卡，那麼這張表的數據量將會達到驚人的5億，事實上我們系統用戶量還沒有過百萬時就已經不行了。MySQL數據庫中的數據是以文件的形勢存在磁盤上的，默認放在/mysql/data下面（可以通過my.cnf中的datadir來查看）， 一張表主要對應著三個文件，一個是frm存放表結構的，一個是myd存放表數據的，一個是myi存表索引的。這三個文件都非常的龐大，尤其是.myd文件，快5個G了。下面進行第一次分區優化，MySQL支持的分區方式有四種：
在我們的項目中，range分區和list分區沒有使用場景，如果基於綁定編號做range或者list分區，綁定編號沒有實際的業務含義，無法通過它進行查詢，因此，我們就剩下 HASH 分區和 KEY 分區了， HASH 分區僅支持int類型列的分區，且是其中的一列。看看我們的庫表結構，發現沒有哪一列是int類型的，如何做分區呢？可以增加一列，綁定時間列，將此列設置為int類型，然後按照綁定時間進行分區，將每一天綁定的用戶分到同一個區裡面去。這次優化之後，我們的插入快了許多，但是查詢依然很慢，為什麼，因為在做查詢的時候，我們也只是根據銀行卡或者證件號進行查詢，並沒有根據時間查詢，相當於每次查詢，MySQL都會將所有的分區表查詢一遍。
然後進行第二次方案優化，既然hash分區和key分區要求其中的一列必須是int類型的，那麼創造出一個int類型的列出來分區是否可以。分析發現，銀行卡的那串數字有祕密。銀行卡一般是16位到19位不等的數字串，我們取其中的某一位拿出來作為表分區是否可行呢，通過分析發現，在這串數字中，其中確實有一位是0到9隨機生成的，不同的卡串長度，這一位不同，絕不是最後一位，最後位數字一般都是校驗位，不具有隨機性。我們新設計的方案，基於銀行卡號+隨機位進行KEY分區，每次查詢的時候，通過計算截取出這位隨機位數字，再加上卡號，聯合查詢，達到了分區查詢的目的，需要說明的是，分區後，建立的索引，也必須是分區列，否則的話，MySQL還是會在所有的分區表中查詢數據。那麼通過銀行卡號查詢綁定關係的問題解決了，那麼證件號呢，如何通過證件號來查詢綁定關係。前面已經講過，做索引一定是要在分區健上進行，否則會引起全表掃描。
我們再創建了一張新表，保存客戶的證件號綁定關係，每位客戶的證件號都是唯一的，新的證件號綁定關係表裡，證件號作為了主鍵，那麼如何來計算這個分區健呢，客戶的證件信息比較龐雜，有身份證號，港澳臺通行證，機動車駕駛證等等，如何在無序的證件號裡找到分區健。
為了解決這個問題，我們將證件號綁定關係表一分為二，其中的一張表專用於保存身份證類型的證件號，另一張表則保存其他證件類型的證件號，在身份證類型的證件綁定關係表中，我們將身份證號中的月數拆分出來作為了分區健，將同一個月出生的客戶證件號保存在同一個區，這樣分成了12個區，其他證件類型的證件號，數據量不超過10萬，就沒有必要進行分區了。這樣每次查詢時，首先通過證件類型確定要去查詢哪張表，再計算分區健進行查詢。
作了分區設計之後，保存2000萬用戶數據的時候，銀行卡表的數據保存文件就分成了10個小文件，證件表的數據保存文件分成了12個小文件，解決了這兩個查詢的問題，還剩下一個問題就是，業務編號呢，怎麼辦，一個客戶有多個簽約業務，如何進行保存，這時候，採用分區的方案就不太合適了，它需要用到分表的方案。
分庫分表
如何進行分庫分表，目前互聯網上有許多的版本，比較知名的一些方案：
- 阿里的TDDL，DRDS和cobar，
- 京東金融的sharding-jdbc；
- 間組織的MyCAT；
- 360的Atlas；
- 美團的zebra；
- 其他比如網易、58、京東等公司都有自研的中間件。
但是這麼多的分庫分表中間件方案，歸總起來，就兩類：client模式和proxy模式。
client模式
移動互聯網時代，海量的用戶數據每天都在產生，基於用戶使用數據等這樣的分析，都需要依靠數據統計和分析，當數據量小時，數據庫方面的優化顯得不太重要，一旦數據量越來越大，系統響應會變慢，TPS直線下降，直至服務不可用。
可能有人會問，為何不用Oracle呢？確實，很多開發者寫代碼時並不會關心SQL的問題，凡是性能問題都交給DBA負責SQL優化，可是，不是每一個項目都會有DBA，也不是所有的項目都會採用Oracle數據庫，而且，Oracle數據庫在大數據量的背景下，解決性能問題，也不見的是一個非常輕鬆的事情。
那麼，MySQL能不能支撐億級的數據量呢，我的答案是肯定的，絕大部分的互聯網公司，它們採用的數據存儲方案，絕大部分都是以MySQL為主，不差錢的國企和銀行，以Oracle為主，而且有專職的DBA為你服務。
本文會以一個實際的項目應用為例，層層向大家剖析如何進行數據庫的優化。項目背景是企業級的統一消息處理平臺，客戶數據在5千萬加，每分鐘處理消息流水1千萬，每天消息流水1億左右。
雖說MySQL單表可以存儲10億級的數據，但這個時候性能非常差。既然一張表無法搞定，那麼就想辦法將數據放到多個地方來解決問題吧，於是，數據庫分庫分表的方案便產生了，目前比較普遍的方案有三個：分區，分庫分表，NoSQL/NewSQL。
在實際的項目中，往往是這三種方案的結合來解決問題，目前絕大部分系統的核心數據都是以RDBMS存儲為主，NoSQL/NewSQL存儲為輔。
分區
分區方案
分區表是由多個相關的底層表實現，這些底層表也是由句柄對象表示，所以我們也可以直接訪問各個分區，存儲引擎管理分區的各個底層表和管理普通表一樣（所有的底層表都必須使用相同的存儲引擎），分區表的索引只是在各個底層表上各自加上一個相同的索引，從存儲引擎的角度來看，底層表和一個普通表沒有任何不同，存儲引擎也無須知道這是一個普通表還是一個分區表的一部分。這個方案也不錯，它對用戶屏蔽了sharding的細節，即使查詢條件沒有sharding column，它也能正常工作（只是這時候性能一般）。不過它的缺點很明顯：很多的資源都受到單機的限制，例如連接數，網絡吞吐等。如何進行分區，在實際應用中是一個非常關鍵的要素之一。在我們的項目中，以客戶信息為例，客戶數據量5000萬加，項目背景要求保存客戶的銀行卡綁定關係，客戶的證件綁定關係，以及客戶綁定的業務信息。此業務背景下，該如何設計數據庫呢。項目一期的時候，我們建立了一張客戶業務綁定關係表，裡面冗餘了每一位客戶綁定的業務信息。基本結構大致如下：
查詢時，對銀行卡做索引，業務編號做索引，證件號做索引。隨著需求大增多，這張表的索引會達到10個以上。而且客戶解約再簽約，裡面會保存兩條數據，只是綁定的狀態不同。假設我們有5千萬的客戶，5個業務類型，每位客戶平均2張卡，那麼這張表的數據量將會達到驚人的5億，事實上我們系統用戶量還沒有過百萬時就已經不行了。MySQL數據庫中的數據是以文件的形勢存在磁盤上的，默認放在/mysql/data下面（可以通過my.cnf中的datadir來查看）， 一張表主要對應著三個文件，一個是frm存放表結構的，一個是myd存放表數據的，一個是myi存表索引的。這三個文件都非常的龐大，尤其是.myd文件，快5個G了。下面進行第一次分區優化，MySQL支持的分區方式有四種：
在我們的項目中，range分區和list分區沒有使用場景，如果基於綁定編號做range或者list分區，綁定編號沒有實際的業務含義，無法通過它進行查詢，因此，我們就剩下 HASH 分區和 KEY 分區了， HASH 分區僅支持int類型列的分區，且是其中的一列。看看我們的庫表結構，發現沒有哪一列是int類型的，如何做分區呢？可以增加一列，綁定時間列，將此列設置為int類型，然後按照綁定時間進行分區，將每一天綁定的用戶分到同一個區裡面去。這次優化之後，我們的插入快了許多，但是查詢依然很慢，為什麼，因為在做查詢的時候，我們也只是根據銀行卡或者證件號進行查詢，並沒有根據時間查詢，相當於每次查詢，MySQL都會將所有的分區表查詢一遍。
然後進行第二次方案優化，既然hash分區和key分區要求其中的一列必須是int類型的，那麼創造出一個int類型的列出來分區是否可以。分析發現，銀行卡的那串數字有祕密。銀行卡一般是16位到19位不等的數字串，我們取其中的某一位拿出來作為表分區是否可行呢，通過分析發現，在這串數字中，其中確實有一位是0到9隨機生成的，不同的卡串長度，這一位不同，絕不是最後一位，最後位數字一般都是校驗位，不具有隨機性。我們新設計的方案，基於銀行卡號+隨機位進行KEY分區，每次查詢的時候，通過計算截取出這位隨機位數字，再加上卡號，聯合查詢，達到了分區查詢的目的，需要說明的是，分區後，建立的索引，也必須是分區列，否則的話，MySQL還是會在所有的分區表中查詢數據。那麼通過銀行卡號查詢綁定關係的問題解決了，那麼證件號呢，如何通過證件號來查詢綁定關係。前面已經講過，做索引一定是要在分區健上進行，否則會引起全表掃描。
我們再創建了一張新表，保存客戶的證件號綁定關係，每位客戶的證件號都是唯一的，新的證件號綁定關係表裡，證件號作為了主鍵，那麼如何來計算這個分區健呢，客戶的證件信息比較龐雜，有身份證號，港澳臺通行證，機動車駕駛證等等，如何在無序的證件號裡找到分區健。
為了解決這個問題，我們將證件號綁定關係表一分為二，其中的一張表專用於保存身份證類型的證件號，另一張表則保存其他證件類型的證件號，在身份證類型的證件綁定關係表中，我們將身份證號中的月數拆分出來作為了分區健，將同一個月出生的客戶證件號保存在同一個區，這樣分成了12個區，其他證件類型的證件號，數據量不超過10萬，就沒有必要進行分區了。這樣每次查詢時，首先通過證件類型確定要去查詢哪張表，再計算分區健進行查詢。
作了分區設計之後，保存2000萬用戶數據的時候，銀行卡表的數據保存文件就分成了10個小文件，證件表的數據保存文件分成了12個小文件，解決了這兩個查詢的問題，還剩下一個問題就是，業務編號呢，怎麼辦，一個客戶有多個簽約業務，如何進行保存，這時候，採用分區的方案就不太合適了，它需要用到分表的方案。
分庫分表
如何進行分庫分表，目前互聯網上有許多的版本，比較知名的一些方案：
- 阿里的TDDL，DRDS和cobar，
- 京東金融的sharding-jdbc；
- 間組織的MyCAT；
- 360的Atlas；
- 美團的zebra；
- 其他比如網易、58、京東等公司都有自研的中間件。
但是這麼多的分庫分表中間件方案，歸總起來，就兩類：client模式和proxy模式。
client模式
proxy模式
無論是client模式，還是proxy模式，幾個核心的步驟是一樣的：SQL解析，重寫，路由，執行，結果歸併。個人比較傾向於採用client模式，它架構簡單，性能損耗也比較小，運維成本低。如果在項目中引入mycat或者cobar，他們的單機模式無法保證可靠性，一旦宕機則服務就變得不可用，你又不得不引入HAProxy來實現它的高可用集群部署方案， 為了解決HAProxy的高可用問題，又需要採用Keepalived來實現。
移動互聯網時代，海量的用戶數據每天都在產生，基於用戶使用數據等這樣的分析，都需要依靠數據統計和分析，當數據量小時，數據庫方面的優化顯得不太重要，一旦數據量越來越大，系統響應會變慢，TPS直線下降，直至服務不可用。
可能有人會問，為何不用Oracle呢？確實，很多開發者寫代碼時並不會關心SQL的問題，凡是性能問題都交給DBA負責SQL優化，可是，不是每一個項目都會有DBA，也不是所有的項目都會採用Oracle數據庫，而且，Oracle數據庫在大數據量的背景下，解決性能問題，也不見的是一個非常輕鬆的事情。
那麼，MySQL能不能支撐億級的數據量呢，我的答案是肯定的，絕大部分的互聯網公司，它們採用的數據存儲方案，絕大部分都是以MySQL為主，不差錢的國企和銀行，以Oracle為主，而且有專職的DBA為你服務。
本文會以一個實際的項目應用為例，層層向大家剖析如何進行數據庫的優化。項目背景是企業級的統一消息處理平臺，客戶數據在5千萬加，每分鐘處理消息流水1千萬，每天消息流水1億左右。
雖說MySQL單表可以存儲10億級的數據，但這個時候性能非常差。既然一張表無法搞定，那麼就想辦法將數據放到多個地方來解決問題吧，於是，數據庫分庫分表的方案便產生了，目前比較普遍的方案有三個：分區，分庫分表，NoSQL/NewSQL。
在實際的項目中，往往是這三種方案的結合來解決問題，目前絕大部分系統的核心數據都是以RDBMS存儲為主，NoSQL/NewSQL存儲為輔。
分區
分區方案
分區表是由多個相關的底層表實現，這些底層表也是由句柄對象表示，所以我們也可以直接訪問各個分區，存儲引擎管理分區的各個底層表和管理普通表一樣（所有的底層表都必須使用相同的存儲引擎），分區表的索引只是在各個底層表上各自加上一個相同的索引，從存儲引擎的角度來看，底層表和一個普通表沒有任何不同，存儲引擎也無須知道這是一個普通表還是一個分區表的一部分。這個方案也不錯，它對用戶屏蔽了sharding的細節，即使查詢條件沒有sharding column，它也能正常工作（只是這時候性能一般）。不過它的缺點很明顯：很多的資源都受到單機的限制，例如連接數，網絡吞吐等。如何進行分區，在實際應用中是一個非常關鍵的要素之一。在我們的項目中，以客戶信息為例，客戶數據量5000萬加，項目背景要求保存客戶的銀行卡綁定關係，客戶的證件綁定關係，以及客戶綁定的業務信息。此業務背景下，該如何設計數據庫呢。項目一期的時候，我們建立了一張客戶業務綁定關係表，裡面冗餘了每一位客戶綁定的業務信息。基本結構大致如下：
查詢時，對銀行卡做索引，業務編號做索引，證件號做索引。隨著需求大增多，這張表的索引會達到10個以上。而且客戶解約再簽約，裡面會保存兩條數據，只是綁定的狀態不同。假設我們有5千萬的客戶，5個業務類型，每位客戶平均2張卡，那麼這張表的數據量將會達到驚人的5億，事實上我們系統用戶量還沒有過百萬時就已經不行了。MySQL數據庫中的數據是以文件的形勢存在磁盤上的，默認放在/mysql/data下面（可以通過my.cnf中的datadir來查看）， 一張表主要對應著三個文件，一個是frm存放表結構的，一個是myd存放表數據的，一個是myi存表索引的。這三個文件都非常的龐大，尤其是.myd文件，快5個G了。下面進行第一次分區優化，MySQL支持的分區方式有四種：
在我們的項目中，range分區和list分區沒有使用場景，如果基於綁定編號做range或者list分區，綁定編號沒有實際的業務含義，無法通過它進行查詢，因此，我們就剩下 HASH 分區和 KEY 分區了， HASH 分區僅支持int類型列的分區，且是其中的一列。看看我們的庫表結構，發現沒有哪一列是int類型的，如何做分區呢？可以增加一列，綁定時間列，將此列設置為int類型，然後按照綁定時間進行分區，將每一天綁定的用戶分到同一個區裡面去。這次優化之後，我們的插入快了許多，但是查詢依然很慢，為什麼，因為在做查詢的時候，我們也只是根據銀行卡或者證件號進行查詢，並沒有根據時間查詢，相當於每次查詢，MySQL都會將所有的分區表查詢一遍。
然後進行第二次方案優化，既然hash分區和key分區要求其中的一列必須是int類型的，那麼創造出一個int類型的列出來分區是否可以。分析發現，銀行卡的那串數字有祕密。銀行卡一般是16位到19位不等的數字串，我們取其中的某一位拿出來作為表分區是否可行呢，通過分析發現，在這串數字中，其中確實有一位是0到9隨機生成的，不同的卡串長度，這一位不同，絕不是最後一位，最後位數字一般都是校驗位，不具有隨機性。我們新設計的方案，基於銀行卡號+隨機位進行KEY分區，每次查詢的時候，通過計算截取出這位隨機位數字，再加上卡號，聯合查詢，達到了分區查詢的目的，需要說明的是，分區後，建立的索引，也必須是分區列，否則的話，MySQL還是會在所有的分區表中查詢數據。那麼通過銀行卡號查詢綁定關係的問題解決了，那麼證件號呢，如何通過證件號來查詢綁定關係。前面已經講過，做索引一定是要在分區健上進行，否則會引起全表掃描。
我們再創建了一張新表，保存客戶的證件號綁定關係，每位客戶的證件號都是唯一的，新的證件號綁定關係表裡，證件號作為了主鍵，那麼如何來計算這個分區健呢，客戶的證件信息比較龐雜，有身份證號，港澳臺通行證，機動車駕駛證等等，如何在無序的證件號裡找到分區健。
為了解決這個問題，我們將證件號綁定關係表一分為二，其中的一張表專用於保存身份證類型的證件號，另一張表則保存其他證件類型的證件號，在身份證類型的證件綁定關係表中，我們將身份證號中的月數拆分出來作為了分區健，將同一個月出生的客戶證件號保存在同一個區，這樣分成了12個區，其他證件類型的證件號，數據量不超過10萬，就沒有必要進行分區了。這樣每次查詢時，首先通過證件類型確定要去查詢哪張表，再計算分區健進行查詢。
作了分區設計之後，保存2000萬用戶數據的時候，銀行卡表的數據保存文件就分成了10個小文件，證件表的數據保存文件分成了12個小文件，解決了這兩個查詢的問題，還剩下一個問題就是，業務編號呢，怎麼辦，一個客戶有多個簽約業務，如何進行保存，這時候，採用分區的方案就不太合適了，它需要用到分表的方案。
分庫分表
如何進行分庫分表，目前互聯網上有許多的版本，比較知名的一些方案：
- 阿里的TDDL，DRDS和cobar，
- 京東金融的sharding-jdbc；
- 間組織的MyCAT；
- 360的Atlas；
- 美團的zebra；
- 其他比如網易、58、京東等公司都有自研的中間件。
但是這麼多的分庫分表中間件方案，歸總起來，就兩類：client模式和proxy模式。
client模式
proxy模式
無論是client模式，還是proxy模式，幾個核心的步驟是一樣的：SQL解析，重寫，路由，執行，結果歸併。個人比較傾向於採用client模式，它架構簡單，性能損耗也比較小，運維成本低。如果在項目中引入mycat或者cobar，他們的單機模式無法保證可靠性，一旦宕機則服務就變得不可用，你又不得不引入HAProxy來實現它的高可用集群部署方案， 為了解決HAProxy的高可用問題，又需要採用Keepalived來實現。
我們在項目中放棄了這個方案，採用了shardingjdbc的方式。回到剛才的業務問題，如何對業務類型進行分庫分表。分庫分表第一步也是最重要的一步，即sharding column的選取，sharding column選擇的好壞將直接決定整個分庫分表方案最終是否成功。
而sharding column的選取跟業務強相關。在我們的項目場景中，sharding column無疑最好的選擇是業務編號。通過業務編號，將客戶不同的綁定簽約業務保存到不同的表裡面去，查詢時，根據業務編號路由到相應的表中進行查詢，達到進一步優化SQL的目的。
前面我們講到了基於客戶簽約綁定業務場景的數據庫優化，下面我們再聊一聊，對於海量數據的保存方案。
垂直分庫
對於每分鐘要處理近1000萬的流水，每天流水近1億的量，如何高效的寫入和查詢，是一項比較大的挑戰。還是老辦法，分庫分表分區，讀寫分離，只不過這一次，我們先分表，再分庫，最後分區。
我們將消息流水按照不同的業務類型進行分表，相同業務的消息流水進入同一張表，分表完成之後，再進行分庫。我們將流水相關的數據單獨保存到一個庫裡面去，這些數據，寫入要求高，查詢和更新到要求低，將它們和那些更新頻繁的數據區分開。分庫之後，再進行分區。
移動互聯網時代，海量的用戶數據每天都在產生，基於用戶使用數據等這樣的分析，都需要依靠數據統計和分析，當數據量小時，數據庫方面的優化顯得不太重要，一旦數據量越來越大，系統響應會變慢，TPS直線下降，直至服務不可用。
可能有人會問，為何不用Oracle呢？確實，很多開發者寫代碼時並不會關心SQL的問題，凡是性能問題都交給DBA負責SQL優化，可是，不是每一個項目都會有DBA，也不是所有的項目都會採用Oracle數據庫，而且，Oracle數據庫在大數據量的背景下，解決性能問題，也不見的是一個非常輕鬆的事情。
那麼，MySQL能不能支撐億級的數據量呢，我的答案是肯定的，絕大部分的互聯網公司，它們採用的數據存儲方案，絕大部分都是以MySQL為主，不差錢的國企和銀行，以Oracle為主，而且有專職的DBA為你服務。
本文會以一個實際的項目應用為例，層層向大家剖析如何進行數據庫的優化。項目背景是企業級的統一消息處理平臺，客戶數據在5千萬加，每分鐘處理消息流水1千萬，每天消息流水1億左右。
雖說MySQL單表可以存儲10億級的數據，但這個時候性能非常差。既然一張表無法搞定，那麼就想辦法將數據放到多個地方來解決問題吧，於是，數據庫分庫分表的方案便產生了，目前比較普遍的方案有三個：分區，分庫分表，NoSQL/NewSQL。
在實際的項目中，往往是這三種方案的結合來解決問題，目前絕大部分系統的核心數據都是以RDBMS存儲為主，NoSQL/NewSQL存儲為輔。
分區
分區方案
分區表是由多個相關的底層表實現，這些底層表也是由句柄對象表示，所以我們也可以直接訪問各個分區，存儲引擎管理分區的各個底層表和管理普通表一樣（所有的底層表都必須使用相同的存儲引擎），分區表的索引只是在各個底層表上各自加上一個相同的索引，從存儲引擎的角度來看，底層表和一個普通表沒有任何不同，存儲引擎也無須知道這是一個普通表還是一個分區表的一部分。這個方案也不錯，它對用戶屏蔽了sharding的細節，即使查詢條件沒有sharding column，它也能正常工作（只是這時候性能一般）。不過它的缺點很明顯：很多的資源都受到單機的限制，例如連接數，網絡吞吐等。如何進行分區，在實際應用中是一個非常關鍵的要素之一。在我們的項目中，以客戶信息為例，客戶數據量5000萬加，項目背景要求保存客戶的銀行卡綁定關係，客戶的證件綁定關係，以及客戶綁定的業務信息。此業務背景下，該如何設計數據庫呢。項目一期的時候，我們建立了一張客戶業務綁定關係表，裡面冗餘了每一位客戶綁定的業務信息。基本結構大致如下：
查詢時，對銀行卡做索引，業務編號做索引，證件號做索引。隨著需求大增多，這張表的索引會達到10個以上。而且客戶解約再簽約，裡面會保存兩條數據，只是綁定的狀態不同。假設我們有5千萬的客戶，5個業務類型，每位客戶平均2張卡，那麼這張表的數據量將會達到驚人的5億，事實上我們系統用戶量還沒有過百萬時就已經不行了。MySQL數據庫中的數據是以文件的形勢存在磁盤上的，默認放在/mysql/data下面（可以通過my.cnf中的datadir來查看）， 一張表主要對應著三個文件，一個是frm存放表結構的，一個是myd存放表數據的，一個是myi存表索引的。這三個文件都非常的龐大，尤其是.myd文件，快5個G了。下面進行第一次分區優化，MySQL支持的分區方式有四種：
在我們的項目中，range分區和list分區沒有使用場景，如果基於綁定編號做range或者list分區，綁定編號沒有實際的業務含義，無法通過它進行查詢，因此，我們就剩下 HASH 分區和 KEY 分區了， HASH 分區僅支持int類型列的分區，且是其中的一列。看看我們的庫表結構，發現沒有哪一列是int類型的，如何做分區呢？可以增加一列，綁定時間列，將此列設置為int類型，然後按照綁定時間進行分區，將每一天綁定的用戶分到同一個區裡面去。這次優化之後，我們的插入快了許多，但是查詢依然很慢，為什麼，因為在做查詢的時候，我們也只是根據銀行卡或者證件號進行查詢，並沒有根據時間查詢，相當於每次查詢，MySQL都會將所有的分區表查詢一遍。
然後進行第二次方案優化，既然hash分區和key分區要求其中的一列必須是int類型的，那麼創造出一個int類型的列出來分區是否可以。分析發現，銀行卡的那串數字有祕密。銀行卡一般是16位到19位不等的數字串，我們取其中的某一位拿出來作為表分區是否可行呢，通過分析發現，在這串數字中，其中確實有一位是0到9隨機生成的，不同的卡串長度，這一位不同，絕不是最後一位，最後位數字一般都是校驗位，不具有隨機性。我們新設計的方案，基於銀行卡號+隨機位進行KEY分區，每次查詢的時候，通過計算截取出這位隨機位數字，再加上卡號，聯合查詢，達到了分區查詢的目的，需要說明的是，分區後，建立的索引，也必須是分區列，否則的話，MySQL還是會在所有的分區表中查詢數據。那麼通過銀行卡號查詢綁定關係的問題解決了，那麼證件號呢，如何通過證件號來查詢綁定關係。前面已經講過，做索引一定是要在分區健上進行，否則會引起全表掃描。
我們再創建了一張新表，保存客戶的證件號綁定關係，每位客戶的證件號都是唯一的，新的證件號綁定關係表裡，證件號作為了主鍵，那麼如何來計算這個分區健呢，客戶的證件信息比較龐雜，有身份證號，港澳臺通行證，機動車駕駛證等等，如何在無序的證件號裡找到分區健。
為了解決這個問題，我們將證件號綁定關係表一分為二，其中的一張表專用於保存身份證類型的證件號，另一張表則保存其他證件類型的證件號，在身份證類型的證件綁定關係表中，我們將身份證號中的月數拆分出來作為了分區健，將同一個月出生的客戶證件號保存在同一個區，這樣分成了12個區，其他證件類型的證件號，數據量不超過10萬，就沒有必要進行分區了。這樣每次查詢時，首先通過證件類型確定要去查詢哪張表，再計算分區健進行查詢。
作了分區設計之後，保存2000萬用戶數據的時候，銀行卡表的數據保存文件就分成了10個小文件，證件表的數據保存文件分成了12個小文件，解決了這兩個查詢的問題，還剩下一個問題就是，業務編號呢，怎麼辦，一個客戶有多個簽約業務，如何進行保存，這時候，採用分區的方案就不太合適了，它需要用到分表的方案。
分庫分表
如何進行分庫分表，目前互聯網上有許多的版本，比較知名的一些方案：
- 阿里的TDDL，DRDS和cobar，
- 京東金融的sharding-jdbc；
- 間組織的MyCAT；
- 360的Atlas；
- 美團的zebra；
- 其他比如網易、58、京東等公司都有自研的中間件。
但是這麼多的分庫分表中間件方案，歸總起來，就兩類：client模式和proxy模式。
client模式
proxy模式
無論是client模式，還是proxy模式，幾個核心的步驟是一樣的：SQL解析，重寫，路由，執行，結果歸併。個人比較傾向於採用client模式，它架構簡單，性能損耗也比較小，運維成本低。如果在項目中引入mycat或者cobar，他們的單機模式無法保證可靠性，一旦宕機則服務就變得不可用，你又不得不引入HAProxy來實現它的高可用集群部署方案， 為了解決HAProxy的高可用問題，又需要採用Keepalived來實現。
我們在項目中放棄了這個方案，採用了shardingjdbc的方式。回到剛才的業務問題，如何對業務類型進行分庫分表。分庫分表第一步也是最重要的一步，即sharding column的選取，sharding column選擇的好壞將直接決定整個分庫分表方案最終是否成功。
而sharding column的選取跟業務強相關。在我們的項目場景中，sharding column無疑最好的選擇是業務編號。通過業務編號，將客戶不同的綁定簽約業務保存到不同的表裡面去，查詢時，根據業務編號路由到相應的表中進行查詢，達到進一步優化SQL的目的。
前面我們講到了基於客戶簽約綁定業務場景的數據庫優化，下面我們再聊一聊，對於海量數據的保存方案。
垂直分庫
對於每分鐘要處理近1000萬的流水，每天流水近1億的量，如何高效的寫入和查詢，是一項比較大的挑戰。還是老辦法，分庫分表分區，讀寫分離，只不過這一次，我們先分表，再分庫，最後分區。
我們將消息流水按照不同的業務類型進行分表，相同業務的消息流水進入同一張表，分表完成之後，再進行分庫。我們將流水相關的數據單獨保存到一個庫裡面去，這些數據，寫入要求高，查詢和更新到要求低，將它們和那些更新頻繁的數據區分開。分庫之後，再進行分區。
這是基於業務垂直度進行的分庫操作，垂直分庫就是根據業務耦合性，將關聯度低的不同表存儲在不同的數據庫，以達到系統資源的飽和利用率。這樣的分庫方案結合應用的微服務治理，每個微服務系統使用獨立的一個數據庫。將不同模塊的數據分庫存儲，模塊間不能進行相互關聯查詢，如果有，要麼通過數據冗餘解決，要麼通過應用代碼進行二次加工進行解決。
若不能杜絕跨庫關聯查詢，則將小表到數據冗餘到大數據量大庫裡去。假如，流水大表中查詢需要關聯獲得渠道信息，渠道信息在基礎管理庫裡面，那麼，要麼在查詢時，代碼裡二次查詢基礎管理庫中的渠道信息表，要麼將渠道信息表冗餘到流水大表中。
將每天過億的流水數據分離出去之後，流水庫中單表的數據量還是太龐大，我們將單張流水錶繼續分區，按照一定的業務規則，（一般是查詢索引列）將單表進行分區，一個表編程N個表，當然這些變化對應用層是無法感知的。
移動互聯網時代，海量的用戶數據每天都在產生，基於用戶使用數據等這樣的分析，都需要依靠數據統計和分析，當數據量小時，數據庫方面的優化顯得不太重要，一旦數據量越來越大，系統響應會變慢，TPS直線下降，直至服務不可用。
可能有人會問，為何不用Oracle呢？確實，很多開發者寫代碼時並不會關心SQL的問題，凡是性能問題都交給DBA負責SQL優化，可是，不是每一個項目都會有DBA，也不是所有的項目都會採用Oracle數據庫，而且，Oracle數據庫在大數據量的背景下，解決性能問題，也不見的是一個非常輕鬆的事情。
那麼，MySQL能不能支撐億級的數據量呢，我的答案是肯定的，絕大部分的互聯網公司，它們採用的數據存儲方案，絕大部分都是以MySQL為主，不差錢的國企和銀行，以Oracle為主，而且有專職的DBA為你服務。
本文會以一個實際的項目應用為例，層層向大家剖析如何進行數據庫的優化。項目背景是企業級的統一消息處理平臺，客戶數據在5千萬加，每分鐘處理消息流水1千萬，每天消息流水1億左右。
雖說MySQL單表可以存儲10億級的數據，但這個時候性能非常差。既然一張表無法搞定，那麼就想辦法將數據放到多個地方來解決問題吧，於是，數據庫分庫分表的方案便產生了，目前比較普遍的方案有三個：分區，分庫分表，NoSQL/NewSQL。
在實際的項目中，往往是這三種方案的結合來解決問題，目前絕大部分系統的核心數據都是以RDBMS存儲為主，NoSQL/NewSQL存儲為輔。
分區
分區方案
分區表是由多個相關的底層表實現，這些底層表也是由句柄對象表示，所以我們也可以直接訪問各個分區，存儲引擎管理分區的各個底層表和管理普通表一樣（所有的底層表都必須使用相同的存儲引擎），分區表的索引只是在各個底層表上各自加上一個相同的索引，從存儲引擎的角度來看，底層表和一個普通表沒有任何不同，存儲引擎也無須知道這是一個普通表還是一個分區表的一部分。這個方案也不錯，它對用戶屏蔽了sharding的細節，即使查詢條件沒有sharding column，它也能正常工作（只是這時候性能一般）。不過它的缺點很明顯：很多的資源都受到單機的限制，例如連接數，網絡吞吐等。如何進行分區，在實際應用中是一個非常關鍵的要素之一。在我們的項目中，以客戶信息為例，客戶數據量5000萬加，項目背景要求保存客戶的銀行卡綁定關係，客戶的證件綁定關係，以及客戶綁定的業務信息。此業務背景下，該如何設計數據庫呢。項目一期的時候，我們建立了一張客戶業務綁定關係表，裡面冗餘了每一位客戶綁定的業務信息。基本結構大致如下：
查詢時，對銀行卡做索引，業務編號做索引，證件號做索引。隨著需求大增多，這張表的索引會達到10個以上。而且客戶解約再簽約，裡面會保存兩條數據，只是綁定的狀態不同。假設我們有5千萬的客戶，5個業務類型，每位客戶平均2張卡，那麼這張表的數據量將會達到驚人的5億，事實上我們系統用戶量還沒有過百萬時就已經不行了。MySQL數據庫中的數據是以文件的形勢存在磁盤上的，默認放在/mysql/data下面（可以通過my.cnf中的datadir來查看）， 一張表主要對應著三個文件，一個是frm存放表結構的，一個是myd存放表數據的，一個是myi存表索引的。這三個文件都非常的龐大，尤其是.myd文件，快5個G了。下面進行第一次分區優化，MySQL支持的分區方式有四種：
在我們的項目中，range分區和list分區沒有使用場景，如果基於綁定編號做range或者list分區，綁定編號沒有實際的業務含義，無法通過它進行查詢，因此，我們就剩下 HASH 分區和 KEY 分區了， HASH 分區僅支持int類型列的分區，且是其中的一列。看看我們的庫表結構，發現沒有哪一列是int類型的，如何做分區呢？可以增加一列，綁定時間列，將此列設置為int類型，然後按照綁定時間進行分區，將每一天綁定的用戶分到同一個區裡面去。這次優化之後，我們的插入快了許多，但是查詢依然很慢，為什麼，因為在做查詢的時候，我們也只是根據銀行卡或者證件號進行查詢，並沒有根據時間查詢，相當於每次查詢，MySQL都會將所有的分區表查詢一遍。
然後進行第二次方案優化，既然hash分區和key分區要求其中的一列必須是int類型的，那麼創造出一個int類型的列出來分區是否可以。分析發現，銀行卡的那串數字有祕密。銀行卡一般是16位到19位不等的數字串，我們取其中的某一位拿出來作為表分區是否可行呢，通過分析發現，在這串數字中，其中確實有一位是0到9隨機生成的，不同的卡串長度，這一位不同，絕不是最後一位，最後位數字一般都是校驗位，不具有隨機性。我們新設計的方案，基於銀行卡號+隨機位進行KEY分區，每次查詢的時候，通過計算截取出這位隨機位數字，再加上卡號，聯合查詢，達到了分區查詢的目的，需要說明的是，分區後，建立的索引，也必須是分區列，否則的話，MySQL還是會在所有的分區表中查詢數據。那麼通過銀行卡號查詢綁定關係的問題解決了，那麼證件號呢，如何通過證件號來查詢綁定關係。前面已經講過，做索引一定是要在分區健上進行，否則會引起全表掃描。
我們再創建了一張新表，保存客戶的證件號綁定關係，每位客戶的證件號都是唯一的，新的證件號綁定關係表裡，證件號作為了主鍵，那麼如何來計算這個分區健呢，客戶的證件信息比較龐雜，有身份證號，港澳臺通行證，機動車駕駛證等等，如何在無序的證件號裡找到分區健。
為了解決這個問題，我們將證件號綁定關係表一分為二，其中的一張表專用於保存身份證類型的證件號，另一張表則保存其他證件類型的證件號，在身份證類型的證件綁定關係表中，我們將身份證號中的月數拆分出來作為了分區健，將同一個月出生的客戶證件號保存在同一個區，這樣分成了12個區，其他證件類型的證件號，數據量不超過10萬，就沒有必要進行分區了。這樣每次查詢時，首先通過證件類型確定要去查詢哪張表，再計算分區健進行查詢。
作了分區設計之後，保存2000萬用戶數據的時候，銀行卡表的數據保存文件就分成了10個小文件，證件表的數據保存文件分成了12個小文件，解決了這兩個查詢的問題，還剩下一個問題就是，業務編號呢，怎麼辦，一個客戶有多個簽約業務，如何進行保存，這時候，採用分區的方案就不太合適了，它需要用到分表的方案。
分庫分表
如何進行分庫分表，目前互聯網上有許多的版本，比較知名的一些方案：
- 阿里的TDDL，DRDS和cobar，
- 京東金融的sharding-jdbc；
- 間組織的MyCAT；
- 360的Atlas；
- 美團的zebra；
- 其他比如網易、58、京東等公司都有自研的中間件。
但是這麼多的分庫分表中間件方案，歸總起來，就兩類：client模式和proxy模式。
client模式
proxy模式
無論是client模式，還是proxy模式，幾個核心的步驟是一樣的：SQL解析，重寫，路由，執行，結果歸併。個人比較傾向於採用client模式，它架構簡單，性能損耗也比較小，運維成本低。如果在項目中引入mycat或者cobar，他們的單機模式無法保證可靠性，一旦宕機則服務就變得不可用，你又不得不引入HAProxy來實現它的高可用集群部署方案， 為了解決HAProxy的高可用問題，又需要採用Keepalived來實現。
我們在項目中放棄了這個方案，採用了shardingjdbc的方式。回到剛才的業務問題，如何對業務類型進行分庫分表。分庫分表第一步也是最重要的一步，即sharding column的選取，sharding column選擇的好壞將直接決定整個分庫分表方案最終是否成功。
而sharding column的選取跟業務強相關。在我們的項目場景中，sharding column無疑最好的選擇是業務編號。通過業務編號，將客戶不同的綁定簽約業務保存到不同的表裡面去，查詢時，根據業務編號路由到相應的表中進行查詢，達到進一步優化SQL的目的。
前面我們講到了基於客戶簽約綁定業務場景的數據庫優化，下面我們再聊一聊，對於海量數據的保存方案。
垂直分庫
對於每分鐘要處理近1000萬的流水，每天流水近1億的量，如何高效的寫入和查詢，是一項比較大的挑戰。還是老辦法，分庫分表分區，讀寫分離，只不過這一次，我們先分表，再分庫，最後分區。
我們將消息流水按照不同的業務類型進行分表，相同業務的消息流水進入同一張表，分表完成之後，再進行分庫。我們將流水相關的數據單獨保存到一個庫裡面去，這些數據，寫入要求高，查詢和更新到要求低，將它們和那些更新頻繁的數據區分開。分庫之後，再進行分區。
這是基於業務垂直度進行的分庫操作，垂直分庫就是根據業務耦合性，將關聯度低的不同表存儲在不同的數據庫，以達到系統資源的飽和利用率。這樣的分庫方案結合應用的微服務治理，每個微服務系統使用獨立的一個數據庫。將不同模塊的數據分庫存儲，模塊間不能進行相互關聯查詢，如果有，要麼通過數據冗餘解決，要麼通過應用代碼進行二次加工進行解決。
若不能杜絕跨庫關聯查詢，則將小表到數據冗餘到大數據量大庫裡去。假如，流水大表中查詢需要關聯獲得渠道信息，渠道信息在基礎管理庫裡面，那麼，要麼在查詢時，代碼裡二次查詢基礎管理庫中的渠道信息表，要麼將渠道信息表冗餘到流水大表中。
將每天過億的流水數據分離出去之後，流水庫中單表的數據量還是太龐大，我們將單張流水錶繼續分區，按照一定的業務規則，（一般是查詢索引列）將單表進行分區，一個表編程N個表，當然這些變化對應用層是無法感知的。
分區表的設置，一般是以查詢索引列進行分區，例如，對於流水錶A，查詢需要根據手機號和批次號進行查詢，所以我們在創建分區的時候，就選擇以手機號和批次號進行分區，這樣設置後，查詢都會走索引，每次查詢MySQL都會根據查詢條件計算出來，數據會落在那個分區裡面，直接到對應的分區表中檢索即可，避免了全表掃描。
對於每天流水過億的數據，當然是要做歷史表進行數據遷移的工作了。客戶要求流水數據需要保存半年的時間，有的關鍵流水需要保存一年。刪數據是不可能的了，也跑不了路，雖然當時非常有想刪數據跑路的衝動。其實即時是刪數據也是不太可能的了，delete的拙劣表演先淘汰了，truncate也快不了多少，我們採用了一種比較巧妙方法，具體步驟如下：
- 創建一個原表一模一樣的臨時表1 create table test_a_serial_1 like test_a_serial;
- 將原表命名為臨時表2 alter table test_a_serial rename test_a_serial_{date};
- 將臨時表1改為原表 alter table able test_a_serial_1 rename able test_a_serial; 此時，當日流水錶就是一張新的空表了，繼續保存當日的流水，而臨時表2則保存的是昨天的數據和部分今天的數據，臨時表2到名字中的date時間是通過計算獲得的昨日的日期；每天會產生一張帶有昨日日期的臨時表2，每個表內的數據大約是有1000萬。
- 將當日表中的歷史數據遷移到昨日流水錶中去 這樣的操作都是用的定時任務進行處理，定時任務觸發一般會選擇凌晨12點以後，這個操作即時是幾秒內完成，也有可能會有幾條數據落入到當日表中去。因此我們最後還需要將當日表內的歷史流水數據插入到昨日表內； insert into test_a_serial_{date}(cloumn1,cloumn2….) select(cloumn1,cloumn2….) from test_a_serial where LEFT(create_time,8) > CONCAT(date); commit;
如此，便完成了流水數據的遷移；
根據業務需要，有些業務數據需要保存半年，超過半年的進行刪除,在進行刪除的時候，就可以根據表名中的_{date}篩選出大於半年的流水直接刪表；
半年的時間，對於一個業務流水錶大約就會有180多張表，每張表又有20個分區表，那麼如何進行查詢呢？由於我們的項目對於流水的查詢實時性要求不是特別高，因此我們在做查詢時，進行了根據查詢時間區間段進行路由查詢的做法。
大致做法時，根據客戶選擇的時間區間段，帶上查詢條件，分別去時間區間段內的每一張表內查詢，將查詢結果保存到一張臨時表內，然後，再去查詢臨時表獲得最終的查詢結果。
以上便是我們面對大數據量的場景下，數據庫層面做的相應的優化，一張每天一億的表，經過拆分後，每個表分區內的數據在500萬左右。這樣設計之後，我們還面臨了一些其他問題，例如流水的統計問題，這麼大量的數據，項目中的統計維度達到100多種，哪怕是每天count100次，也是及其困難多，我們採用了實時計算統計的方式來解決了這個問題。