資料千萬級別之多，佔用的儲存空間也比較大，可想而知它不會儲存在一塊連續的物理空間上，而是鏈式儲存在多個碎片的物理空間上。可能對於長字串的比較，就用更多的時間查詢與比較，這就導致用更多的時間。

可以做表拆分，減少單表字段數量，優化表結構。在保證主鍵有效的情況下，檢查主鍵索引的欄位順序，使得查詢語句中條件的欄位順序和主鍵索引的欄位順序保持一致。

主要兩種拆分 垂直拆分，水平拆分。

MySQL 資料庫某張表近千萬的資料，CRUD比較慢，如何優化？

說實話，這個資料量級， MySQL 單庫單表支撐起來完全沒有問題的，所以首先還是考慮資料庫本身的優化。

從上圖可以看到，資料庫優化通常可以通過以上幾點來實現：

硬體升級：也就是花更多的錢，升級我們資料庫硬體配置，包括 CPU、記憶體、磁碟、網路等等，但是這個方案成本高，而且不一定能起到非常好的效果。資料庫配置：修改資料庫的配置，有可能讓我們的 CRUD 操作變得更快，不過我也不建議大家把經歷放在這一點上面；首先，資料庫的配置通常由專業的 DBA 來負責；第二，大部分時候，預設的資料庫配置在大多數情況下已經是最優配置了。

對於開發人員來說，我們需要把注意力放在後面三點：

資料結構的優化，也就是表結構的優化資料型別的選擇：選用合適的資料結構。什麼叫做"合適的資料結構"，比如性別欄位，M表示男F表示女，那麼一個 char(1) 就足夠了，如果儲存人的年齡，那麼就沒有必要使用 INT 這麼大範圍的欄位了；適當的拆分：千萬不要試圖把所有的欄位放在一張表中，因為這會非常影響效能，通常一張表的欄位最好不要超過 30 個；適當的冗餘：如果一些常用的欄位，可能會用在不同的維度，那麼我們可以把這些欄位設計在多張表中，因為這樣可能會減少表關聯；欄位儘量設定成 not Null，儘量帶有預設值。SQL 語句的優化

優化 SQL 語句執行速度的方法有很多，比如：

儘量使用索引，儘量避免全表掃描，提高查詢速度；當然你不能無限制地建立索引；維護索引也會影響效能，會降低 DML 操作的速度；注意 SQL 語句的書寫，有一些錯誤的寫法可能會導致索引失效；儘量避免在 where 子句中對欄位進行 Null 值判斷（當然我們在表設計中，直接建議不要有 Null）；條件值多的情況下，儘量不要使用 in 和 not in ；select 的時候，使用具體的欄位代替 * 號避免返回大量資料，增加分頁；

說完了 MySQL 本身的優化，如果資料量進一步增大的話，我們還有什麼優化的方案呢？

讀寫分離

主庫用於寫，從庫用於讀，將讀寫分散在不同的資料庫上，利用多臺機器的資源，來提高資料庫的可用性和效能。

分庫分表

如果資料持續增多，超過了單臺 MySQL 的支撐上限，那麼只能用【分庫分表】這一招了；我們可以採用一定的路由規則，將資料儲存到不同的資料庫中。

當然，如果不是“迫不得已”，我是不太建議分庫分表的，因為這樣極大地增加了系統的複雜程度，並且會帶來更多的問題需要開發人員解決。

以上就是常用的 MySQL 優化方案，如果是千萬級資料量，優化 MySQL 本身即可。

首先排查程式碼問題，是否有不合理的sql，針對sql進行優化千萬的資料如果資料結構比較簡單，沒有佔用空間大的欄位，查詢效率原則上不會慢資料庫伺服器硬體效能是否需要升級，記憶體、cpu（增加核數）、更換為固態硬碟以上如果都沒有問題就需要考慮分表分庫了，需要針對業務做整體評估

優化的方向非常多

1.根據常用sql的條件欄位建索引，但是索引多也會造成磁碟使用量巨大。

2.慎用可能導致全表查的函式，嚴禁全表查資料

3.嚴禁三表關聯查，一個sql查崩一個系統不是說說的。

4.sql的條件順序，應該是先過濾資料量大的

6.資料量如果有無限增長的趨勢，應做動態分表分庫

7.人工索引，可以將人工做的索引放在另一張表裡面，查資料時，先查人工索引的表，確定id之後，直接通過id去目標表中查

8.檢視，如果這張表資料不怎麼改，檢視維護不頻繁可以使用

9.快取，查詢結果進行快取，查詢條件快取，rate演算法瞭解一下。

千萬但不算啥問題(單機單庫單表情況下)，索引加一下，也有可能是複雜sql導致的，可以看看執行計劃，是否有直接優化空間，也可以根據業務直接儲存一些需要查表計算的冗餘資料，還有一點，升級mysql8.0，官方的優化遠遠要比自己的優化強大的多，一些複雜sql可以用新函式代替等等，如果這樣還解決不了，理論這是不可能，但如果真該無法解決，只能金錢大法，提升配置，分庫分表，讀寫分離等等，單機變叢集，反正砸錢

不太清楚表結構，不過普通的表近千萬的資料，我跟你說，這個量對於mysql是小意思。如果慢，先從查詢入手，看看查詢的sql語句的執行計劃，看看語句寫得是否規範，比如是否用到一些內部函式導致沒有走索引，然後根據執行計劃調整sql，新增索引。一般做到這一步就可以解決大部分慢的問題。再看插入修改資料，還是要看錶裡面是否有一些特殊欄位，另外就是要看看是否有多餘的索引，解決一下。如果以上方法都解決不了，就要考慮分表，分庫，讀寫分離等方法。總之最簡單快捷解決你這個問題從索引下手。

一個千萬級的資料庫的表查詢問題屬於小問題。做好幾個方面就能有很大改觀了：

1、硬體上的升級，換成固態硬碟組成Raid5,記憶體至少加到32G；

2、資料庫構架升級為多個節點的負載均衡構架，對於Mysql推薦使用第三方資料庫中間軟體完成Mycat;

3、精確的資料庫優化，分為幾個步驟（重點）：

Mysql配置檔案引數的上的優化，具體可百度一下；在需要查詢和排序的欄位上建好索引，把預設值都改為不為Null（數字值的為0，字串型的為空字元，日期型的為0000-00-00），對於已經為Null的欄位，全部用相應的有效值代替；對於需要進行字串模糊搜尋的，則在資料庫上配套使用ElasticSearch之類的全文索引技術，摒棄資料庫自身的like或是instr等SQL語句式的字串模糊搜尋；對於需要多表查詢且某個表的資料為靜態資料（解釋：指的是長時間內不會發生變更的資料。例如：商品的分類表，幾乎不經常變更）的，可以先靜態資料存放到快取中，待應用取出後，再與快取資料進行拼接完成資料的輸出。舉例說明：

需求：查詢商品資訊時，要根據商品表中的商品分類ID讀取到商品分類的名稱。

步驟1：將商品分類表的資料以分ID做為快取索引，全部寫入到MemCache中;

步驟2：按條件讀取到符合預期的商品列表資訊；

步驟3：迴圈讀取商品列表，根據列表中每一行的資料的商品分類ID,去讀取MemCache中的分類名稱；4、針對業務上的優化，對於那些時效性不強的查詢，改為後臺任務定期生成結果，而不是實時查詢的方式完成。例如：找出昨天的日誌記錄，可以使用計劃任務的方式去定期生成昨天的日誌記錄結果，待使用者需要資料時，直接從生成的日誌記錄結果讀取返回即可。此舉，可大大減少資料庫的查詢數量和負載。

調整mysql配置引數

若是讀為主，可以考慮開啟query_cache，以及調整一些引數值：sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size

優化索引

對出現在where子句中的欄位加索引， 全表掃描的效能通常是很差的，要儘量避免。

組合索引和覆蓋索引

硬體升級

更多的記憶體和更快的硬碟。如果是InnoDB引擎，多利用點記憶體，減輕磁碟IO負載，因為IO往往是資料庫伺服器的瓶頸；

分割槽分表

mysql自帶分割槽表，對應用是透明的，無需更改程式碼, 但是sql語句是需要針對分割槽表做優化的，sql的where條件中要帶上分割槽條件的列，從而使查詢定位到少量的分割槽上，否則就會掃描全部分割槽

分表和分割槽的思路是差不多的，都是將查詢固定到一個範圍，降低資料讀取量，相對分割槽來說，分表不需要知道分割槽欄位，但是需要知道分表規則。就導致需要修改現有程式碼來適配現有的分表邏輯，而且當資料量增加的時候，分表數量會一直遞增，導致後期資料處理困難。

讀寫分離

主從複製，對主庫進行CUD，對從庫進行R，保證R不會受到CUD的影響，保證了查詢效能，可以在應用層修改，也可以用第三方工具。

由於Mysql主從複製是非同步的，主從在同一時刻難免存在資料不一致的問題，特別是主從機器是跨機房或者跨地域，網路延遲大，資料很難保持強一致性。所以若是要求資料強一致性，這種方法就不合適

1. 選取最適用的欄位屬性

表中欄位的寬度設得儘可能小：char 的上限為 255 位元組（固定佔用空間），varchar 的上限 65535 位元組（實際佔用空間），text 的上限為 65535。

儘量把欄位設定為 NOT NULL，執行查詢的時候，資料庫不用去比較 NULL 值。

2. 使用連線（JOIN）來代替子查詢 (Sub-Queries)

連線（JOIN）之所以更有效率一些，是因為 MySQL 不需要在記憶體中建立臨時表來完成這個邏輯上的需要兩個步驟的查詢工作（聯合查詢的條件加索引更快）。

3. 使用聯合 (UNION) 來代替手動建立的臨時表

把需要使用臨時表的兩條或更多的 SELECT 查詢合併的一個查詢中。

SELECT Name, Phone FROM client UNION SELECT Name, BirthDate FROM author UNION SELECT Name, Supplier FROM product;

4. 事務

儘管我們可以使用子查詢（Sub-Queries）、連線（JOIN）和聯合（UNION）來建立各種各樣的查詢，但不是所有的資料庫操作都可以只用一條或少數幾條 SQL 語句就可以完成的。更多的時候是需要用到一系列的語句來完成某種工作。

作用是：

要麼語句塊中每條語句都操作成功，要麼都失敗。換句話說，就是可以保持資料庫中資料的一致性和完整性。

事物以 BEGIN 關鍵字開始，COMMIT 關鍵字結束。在這之間的一條 SQL 操作失敗，那麼，ROLLBACK 命令就可以把資料庫恢復到 BEGIN 開始之前的狀態。

5. 鎖定表

儘管事務是維護資料庫完整性的一個非常好的方法，但卻因為它的獨佔性，有時會影響資料庫的效能，尤其是在很大的應用系統中。

由於在事務執行的過程中，資料庫將會被鎖定，因此其它的使用者請求只能暫時等待直到該事務結束。

LOCK TABLE inventory WRITE

SELECT Quantity FROM inventory

WHEREItem="book";

...

UPDATE inventory SET Quantity=11

WHEREItem="book";

這裡，我們用一個 SELECT 語句取出初始資料，通過一些計算，用 UPDATE 語句將新值更新到表中。

包含有 WRITE 關鍵字的 LOCK TABLE 語句可以保證在 UNLOCK TABLES 命令被執行之前，不會有其它的訪問來對 inventory 進行插入、更新或者刪除的操作。

6、使用外來鍵

鎖定表的方法可以維護資料的完整性，但是它卻不能保證資料的關聯性。這個時候我們就可以使用外來鍵。

例如，外來鍵可以保證每一條銷售記錄都指向某一個存在的客戶。在這裡，外來鍵可以把 customerinfo 表中的 CustomerID 對映到 salesinfo 表中 CustomerID，任何一條沒有合法 CustomerID 的記錄都不會被更新或插入到 salesinfo 中。

CREATE TABLE customerinfo

(

CustomerID INT NOT NULL ,

PRIMARY KEY ( CustomerID )

CREATE TABLE salesinfo

(

SalesID INT NOT NULL,

CustomerID INT NOT NULL,

PRIMARY KEY(CustomerID, SalesID),

FOREIGN KEY (CustomerID) REFERENCES customerinfo

(CustomerID) ON DELETECASCADE

注意例子中的引數 “ON DELETE CASCADE”。該引數保證當 customerinfo 表中的一條客戶記錄被刪除的時候，salesinfo 表中所有與該客戶相關的記錄也會被自動刪除。

如果要在 MySQL 中使用外來鍵，一定要記住在建立表的時候將表的型別定義為事務安全表 InnoDB 型別。該型別不是 MySQL 表的預設型別。定義的方法是在 CREATE TABLE 語句中加上 TYPE=INNODB。

7. 使用索引

查詢語句當中包含有 MAX (), MIN () 和 ORDERBY 這些命令的時候，效能提高更為明顯。

索引應建立在那些將用於 JOIN, WHERE 判斷和 ORDER BY 排序的欄位上。儘量不要對資料庫中某個含有大量重複的值的欄位建立索引。對於一個 ENUM 型別的欄位來說，出現大量重複值是很有可能的情況，例如 customerinfo 中的 “province”.. 欄位，在這樣的欄位上建立索引將不會有什麼幫助；相反，還有可能降低資料庫的效能。

8. 優化的查詢語句

SELECT FROM order WHERE YEAR(OrderDate)<2001;

SELECT FROM order WHERE OrderDate<"2001-01-01";

SELECT FROM inventory WHERE Amount/7<24;

SELECT FROM inventory WHERE Amount<24*7;

避免在查詢中讓 MySQL 進行自動型別轉換，因為轉換過程也會使索引變得不起作用。