mysql優化指南之優化篇详情 - mysql,mysql優化縱情向前的強仔日志

二、優化

現在的理解數據庫優化有四個維度，分別是：
硬件升級、系統配置、表結構設計、SQL語句及索引。
那優化的成本和效果分別如下：
優化成本：硬件升級>系統配置>表結構設計>SQL語句及索引。
優化效果：硬件升級由下圖可以看出性價比排名也是硬件升級

編輯
一般我們我們在項目中做事也是選擇性價比最高的項來開始做，下面也從這個順序講：

（一）SQL語句及索引

根據當前計算機硬件的基本性能指標及其在數據庫中主要操作內容，可以整理4個優化法則：
1、減少數據訪問（減少磁盤訪問）
2、返回更少數據（減少網絡傳輸或磁盤訪問）
3、減少交互次數（減少網絡傳輸）
4、減少服務器CPU開銷（減少CPU及內存開銷）
以下是每個優化法則層級對應優化效果及成本經驗參考：

優化法則性能提升效果優化成本減少數據訪問1~1000低返回更少數據1~100低減少交互次數1~20低減少服務器CPU開銷1~5低

1、減少數據訪問

1.1、創建並使用正確的索引

MySQL支持多種索引類型，這些索引類型在不同的存儲引擎中可能有所不同。以下是MySQL中常見的索引類型，以及它們通常支持的存儲引擎：

在上面的幾種索引中我們在實際生產中使用最多的是B+Tree索引，其他的使用場景比較少，B+Tree的原理如下：
1.非葉子節點不存據，只存儲索引值，這樣便於存儲更多的索引值
2.葉子節點包含了所有的索引值和data數據
3.葉子節點用指針連接，提高區間的訪問性能

編輯
B+樹進行範圍查找時，從根節點開始，對節點內的索引值序列採用二分法查找，查找定位兩個節點的索引值，然後利用葉子節點的指針進行遍歷即可。

1.1.1、索引字段的選擇

那我們一般在什麼字段上建索引？這是一個非常複雜的話題，需要對業務及數據充分分析後才能得出結果。主鍵及外鍵通常都要有索引，其它需要建索引的字段應滿足以下條件：
1、字段出現在查詢條件中，並且查詢條件可以使用索引；
2、語句執行頻率高，一天會有幾千次以上；
3、更新非常頻繁的字段不適合創建索引；
4、索引使用<>時，效果一般;
5、通過字段條件可篩選的記錄集一般要小於總數的10%

1.1.2、什麼條件會使用索引

當字段上建有索引時，通常以下情況會使用索引：
INDEX_COLUMN = ?
INDEX_COLUMN > ?
INDEX_COLUMN >= ?
INDEX_COLUMN < ?
INDEX_COLUMN
INDEX_COLUMN between ? and ?
INDEX_COLUMN in (?,?,...,?)
INDEX_COLUMN like ?||'%'（後導模糊查詢）
T1. INDEX_COLUMN=T2. COLUMN1（兩個表通過索引字段關聯）

1.1.3、索引失效場景：

不等於操作不能使用索引：
NDEX_COLUMN <> ?
INDEX_COLUMN not in (?,?,...,?)
經過普通運算或函數運算後的索引字段不能使用索引：
function(INDEX_COLUMN) = ?
INDEX_COLUMN + 1 = ?
INDEX_COLUMN || 'a' = ?
含前導模糊查詢的Like語法不能使用索引：
INDEX_COLUMN like '%'||?
INDEX_COLUMN like '%'||?||'%'
B-TREE索引裏不保存字段為NULL值記錄，因此IS NULL不能使用索引：
INDEX_COLUMN is null
類型轉換：
NUMBER_INDEX_COLUMN='12345'
CHAR_INDEX_COLUMN=12345

1.1.4、索引使用技巧：

使用聯合索引的查詢：

MySQL可以為多個字段創建索引，一個索引可以包括16個字段。對於聯合索引，只有查詢條件中使用了這些字段中第一個字段時，索引才會生效。如果存在範圍查詢，比如between、>、

SQL語句中IN包含的值不應過多，不超過200個
當只需要一條數據的時候使用limit 1
排序字段加索引
如果限制條件中其他字段沒有索引儘量少用or
儘量用union all代替union
使用OR關鍵字的查詢：

查詢語句的查詢條件中只有OR關鍵字，且OR前後的兩個條件中的列都是索引時，索引才會生效，否則，索引不生效。

索引下推
覆蓋索引（不回表）
區分in和exists、 not in和not exists：

區分in和exists主要是造成了驅動順序的改變（這是性能變化的關鍵），如果是exists，那麼以外層表為驅動表，先被訪問，如果是IN，那麼先執行子查詢。所以IN適合於外表大而內表小的情況； EXISTS適合於外表小而內表大的情況。
關於not in和not exists，推薦使用not exists，

分段查詢

數據量大可以分段進行查詢，循環遍歷，將結果合併處理進行展示

必要時可以使用force index來強制查詢走某個索引
使用JOIN優化：
LEFT JOIN A表為驅動表， INNER JOIN MySQL會自動找出那個數據少的表作用驅動表， RIGHT JOIN B 表為驅動表。

編輯
從原理圖能夠直觀的看出如果能夠減少驅動表的話，減少嵌套循環中的循環次數，以減少 IO總量及CPU運算的次數。要注意的是被驅動表的索引字段作為on的限制字段。

數據字段長使用前置索引

等等，這樣的優化技巧還有很多，在具體實踐中再進行總結再進行補充。不過遵循這樣技巧後具體的是否索引生效還是實現需要看執行計劃。
正確的索引可以讓查詢性能提升100，1000倍以上，但索引會大大增加表記錄的DML(INSERT,UPDATE,DELETE)開銷，因此在一個表中創建什麼樣的索引需要平衡各種業務需求。索引對DML(INSERT,UPDATE,DELETE)附加的開銷有多少？這個沒有固定的比例，與每個表記錄的大小及索引字段大小密切相關，以下是一個普通表測試數據，僅供參考：
索引對於Insert性能降低56%
索引對於Update性能降低47%
索引對於Delete性能降低29%
因此對於寫IO壓力比較大的系統，表的索引需要仔細評估必要性，另外索引也會佔用一定的存儲空間。下面介紹下執行計劃。

1.2、sql執行計劃

SQL執行計劃是關係型數據庫最核心的技術之一，它表示SQL執行時的數據訪問算法。下來我們先了解一下執行計劃是怎樣的
在MySQL中可以使用EXPLAIN查看SQL執行計劃，用法：EXPLAIN SELECT * FROM tablename
下面來看一個例子：

編輯
下面解釋下每一行的含義

id：SELECT識別符。這是SELECT查詢序列號。這個不重要。
select_type：表示SELECT語句的類型。

例如：
1、 SIMPLE表示簡單查詢，其中不包含連接查詢和子查詢。
2、 PRIMARY表示主查詢，或者是最外面的查詢語句。
3、 UNION表示連接查詢的第2個或後面的查詢語句。
DEPENDENT UNION：UNION中的第二個或後續的查詢語句，使用了外面查詢結果
UNION RESULT：UNION的結果
SUBQUERY：SELECT子查詢語句
DEPENDENT SUBQUERY：SELECT子查詢語句依賴外層查詢的結果。
最常見的查詢類型是SIMPLE，表示我們的查詢沒有子查詢也沒用到UNION查詢

table表示查詢的表。
type表示表的連接類型。

以下的連接類型的順序是從最佳類型到最差類型：
1、 system表僅有一行，這是const類型的特列，平時不會出現，這個也可以忽略不計。
2、 const數據表最多隻有一個匹配行，因為只匹配一行數據，所以很快，常用於
3、 eq_refmysql手冊是這樣説的:"對於每個來自於前面的表的行組合，從該表中讀取一行。這可能是最好的聯接類型，除了const類型。它用在一個索引的所有部分被聯接使用並且索引是UNIQUE或PRIMARY KEY"。eq_ref可以用於使用=比較帶索引的列。
4、 ref查詢條件索引既不是UNIQUE也不是PRIMARY KEY的情況。ref可用於=或操作符的帶索引的列。
5、 ref_or_null該聯接類型如同ref，但是添加了MySQL可以專門搜索包含NULL值的行。在解決子查詢中經常使用該聯接類型的優化。
上面這五種情況都是很理想的索引使用情況。
6、 index_merge該聯接類型表示使用了索引合併優化方法。在這種情況下，key列包含了使用的索引的清單，key_len包含了使用的索引的最長的關鍵元素。
7、 unique_subquery該類型替換了下面形式的IN子查詢的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一個索引查找函數,可以完全替換子查詢,效率更高。
8、 index_subquery該聯接類型類似於unique_subquery。可以替換IN子查詢,但只適合下列形式的子查詢中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
9、 range只檢索給定範圍的行,使用一個索引來選擇行。
10、 index該聯接類型與ALL相同,除了只有索引樹被掃描。這通常比ALL快,因為索引文件通常比數據文件小。
11、 ALL對於每個來自於先前的表的行組合,進行完整的表掃描。（性能最差）

possible_keys指出MySQL能使用哪個索引在該表中找到行。

如果該列為NULL，説明沒有使用索引，可以對該列創建索引來提高性能。

key顯示MySQL實際決定使用的鍵(索引)。如果沒有選擇索引,鍵是NULL。
key_len顯示MySQL決定使用的鍵長度。如果鍵是NULL,則長度為NULL。
注意：key_len是確定了MySQL將實際使用的索引長度。
ref顯示使用哪個列或常數與key一起從表中選擇行。
rows顯示MySQL認為它執行查詢時必須檢查的行數。
Extra該列包含MySQL解決查詢的詳細信息

· Distinct:MySQL發現第1個匹配行後,停止為當前的行組合搜索更多的行。
· Not exists:MySQL能夠對查詢進行LEFT JOIN優化,發現1個匹配LEFT JOIN標準的行後,不再為前面的的行組合在該表內檢查更多的行。
· range checked for each record (index map: #):MySQL沒有發現好的可以使用的索引,但發現如果來自前面的表的列值已知,可能部分索引可以使用。
· Using filesort:MySQL需要額外的一次傳遞,以找出如何按排序順序檢索行。
· Using index:從只使用索引樹中的信息而不需要進一步搜索讀取實際的行來檢索表中的列信息。
· Using temporary:為了解決查詢,MySQL需要創建一個臨時表來容納結果。
· Using where:WHERE 子句用於限制哪一個行匹配下一個表或發送到客户。
· Using sort_union(...), Using union(...), Using intersect(...):這些函數説明如何為index_merge聯接類型合併索引掃描。
· Using index for group-by:類似於訪問表的Using index方式,Using index for group-by表示MySQL發現了一個索引,可以用來查詢GROUP BY或DISTINCT查詢的所有列,而不要額外搜索硬盤訪問實際的表。

1.3、使用緩存

在讀多寫少的情況下可以使用本地緩存或者分佈式緩存來解決一部分讀流量在緩存層面承接，不必到數據庫層面。

2、返回更少的數據

2.1、數據分頁處理

一般數據分頁方式有：

2.1.1、客户端(應用程序或瀏覽器)分頁

將數據從應用服務器全部下載到本地應用程序或瀏覽器，在應用程序或瀏覽器內部通過本地代碼進行分頁處理
優點：編碼簡單，減少客户端與應用服務器網絡交互次數
缺點：首次交互時間長，佔用客户端內存
適應場景：客户端與應用服務器網絡延時較大，但要求後續操作流暢，如手機GPRS，超遠程訪問（跨國）等等。

2.1.2、應用服務器分頁

將數據從數據庫服務器全部下載到應用服務器，在應用服務器內部再進行數據篩選。以下是一個應用服務器端Java程序分頁的示例：
List list=executeQuery(“select * from employee order by id”);
Int count= list.size();
List subList= list.subList(10, 20);
優點：編碼簡單，只需要一次SQL交互，總數據與分頁數據差不多時性能較好。
缺點：總數據量較多時性能較差。
適應場景：數據庫系統不支持分頁處理，數據量較小並且可控。

2.1.3、數據庫SQL分頁

採用數據庫SQL分頁需要兩次SQL完成
一個SQL計算總數量
一個SQL返回分頁後的數據
優點：性能好
缺點：編碼複雜，需要兩次SQL交互。
SELECT * FROM table_name ORDER BY some_column LIMIT limit_value OFFSET offset_value;
當使用大的 OFFSET 值時，MySQL 必須掃描並跳過許多行才能到達所需的行。一種優化技術是使用“上一頁的最後一個 ID”而不是 OFFSET 來獲取下一頁的數據。
SELECT * FROM table_name WHERE id > last_id_of_previous_page ORDER BY id LIMIT page_size;

2.2、只返回需要的字段

2.2.1通過去除不必要的返回字段可以提高性能

調整前：select * from product where company_id=?;
調整後：select id,name from product where company_id=?;

2.2.2 垂直分表

T_FILE（ID,FILE_NAME,FILE_SIZE,FILE_TYPE,FILE_CONTENT）
我們可以分拆成兩張一對一的關係表：
T_FILE（ID,FILE_NAME,FILE_SIZE,FILE_TYPE）
T_FILECONTENT（ID, FILE_CONTENT）
通過這種分拆，可以大大提少T_FILE表的單條記錄及總大小，這樣在查詢T_FILE時性能會更好，當需要查詢FILE_CONTENT字段內容時再訪問T_FILECONTENT表。

2.2.3 覆蓋索引

有些時候，我們只是訪問表中的幾個字段，並且字段內容較少，我們可以為這幾個字段單獨建立一個組合索引，這樣就可以直接只通過訪問索引就能得到數據，一般索引佔用的磁盤空間比表小很多，所以這種方式可以大大減少磁盤IO開銷。
如：select id,name from company where type='2';
如果這個SQL經常使用，我們可以在type,id,name上創建組合索引
create index my_comb_index on company(type,id,name);
有了這個組合索引後，SQL就可以直接通過my_comb_index索引返回數據，不需要訪問company表。

3、減少交互次數

3.1、batch DML

數據庫訪問框架一般都提供了批量提交的接口，jdbc支持batch的提交處理方法，當你一次性要往一個表中插入1000萬條數據時，如果採用普通的executeUpdate處理，那麼和服務器交互次數為1000萬次，按每秒鐘可以向數據庫服務器提交10000次估算，要完成所有工作需要1000秒。如果採用批量提交模式，1000條提交一次，那麼和服務器交互次數為1萬次，交互次數大大減少。採用batch操作一般不會減少很多數據庫服務器的物理IO，但是會大大減少客户端與服務端的交互次數，從而減少了多次發起的網絡延時開銷，同時也會降低數據庫的CPU開銷。
假設要向一個普通表插入1000萬數據，每條記錄大小為1K字節，表上沒有任何索引，客户端與數據庫服務器網絡是100Mbps，以下是根據現在一般計算機能力估算的各種batch大小性能對比值：

編輯
以上僅是理論計算值，實際情況需要根據具體環境測量。

3.2、In List

很多時候我們需要按一些ID查詢數據庫記錄，我們可以採用一個ID一個請求發給數據庫，這樣的我們將單次查詢改為in，用ID INLIST的這種方式寫SQL，需要注意的是數據庫對in的個數限制，一般mysql是300,但是上了200以上可以找dba進行評估
select * from mytable where id in(:id1,id2,...,idn);

3.3、使用存儲過程

大型數據庫一般都支持存儲過程，合理的利用存儲過程也可以提高系統性能。如你有一個業務需要將A表的數據做一些加工然後更新到B表中，但是又不可能一條SQL完成，這時你需要如下3步操作：
a：將A表數據全部取出到客户端；
b：計算出要更新的數據；
c：將計算結果更新到B表。
如果採用存儲過程你可以將整個業務邏輯封裝在存儲過程裏，然後在客户端直接調用存儲過程處理，這樣可以減少網絡交互的成本。
當然，存儲過程也並不是十全十美，存儲過程有以下缺點：
a、不可移植性，每種數據庫的內部編程語法都不太相同，當你的系統需要兼容多種數據庫時最好不要用存儲過程。
b、學習成本高，DBA一般都擅長寫存儲過程，但並不是每個程序員都能寫好存儲過程，除非你的團隊有較多的開發人員熟悉寫存儲過程，否則後期系統維護會產生問題。
c、業務邏輯多處存在，採用存儲過程後也就意味着你的系統有一些業務邏輯不是在應用程序裏處理，這種架構會增加一些系統維護和調試成本。
d、存儲過程和常用應用程序語言不一樣，它支持的函數及語法有可能不能滿足需求，有些邏輯就只能通過應用程序處理。
e、如果存儲過程中有複雜運算的話，會增加一些數據庫服務端的處理成本，對於集中式數據庫可能會導致系統可擴展性問題。
個人觀點：普通業務邏輯儘量不要使用存儲過程，定時性的ETL任務或報表統計函數可以根據團隊資源情況採用存儲過程處理。

4、減少數據庫服務器CPU運算

4.1、合理使用排序

普通OLTP系統排序操作一般都是在內存裏進行的，對於數據庫來説是一種CPU的消耗，曾在PC機做過測試，單核普通CPU在1秒鐘可以完成100萬條記錄的全內存排序操作，所以説由於現在CPU的性能增強，對於普通的幾十條或上百條記錄排序對系統的影響也不會很大。但是當你的記錄集增加到上萬條以上時，你需要注意是否一定要這麼做了，大記錄集排序不僅增加了CPU開銷，而且可能會由於內存不足發生硬盤排序的現象，當發生硬盤排序時性能會急劇下降，這種需求需要與DBA溝通再決定，取決於你的需求和數據，所以只有你自己最清楚，而不要被別人説排序很慢就嚇倒。
以下列出了可能會發生排序操作的SQL語法：
Order by
Group by
Distinct
Exists子查詢
Not Exists子查詢
In子查詢
Not In子查詢
Union（並集），Union All也是一種並集操作，但是不會發生排序，如果你確認兩個數據集不需要執行去除重複數據操作，那請使用Union All 代替Union。
Minus（差集）
Intersect（交集）

4.2、大量複雜運算在客户端處理

什麼是複雜運算，一般我認為是一秒鐘CPU只能做10萬次以內的運算。如含小數的對數及指數運算、三角函數、3DES及BASE64數據加密算法等等。如果有大量這類函數運算，儘量放在客户端處理，一般CPU每秒中也只能處理1萬-10萬次這樣的函數運算，放在數據庫內不利於高併發處理。

（二）數據庫結構優化

1、優化表結構

一般在創建庫和數據表時，就應該考慮每張表的數據類型大小。為每個表的數據字段選擇合適的類型會減少數據表每一行的存儲大小。設想下，每行節約十幾個字節，那麼100W行的數據量節約的存儲量是相當可觀的，且存儲量越小的表執行查詢的速度也就越快。對於數據表的字段類型常用設計原則如下：
b、儘量少使用DOUBLE和DECIMAL類型；
c、時間類型上，儘量使用TIMESTAMP而非DATETIME，其存儲空間只需要 DATETIME 類型的一半；
d、單表不要有太多字段，建議在50以內；
e、儘量設置NOT NULL，避免使用NULL字段，NULL字段很難查詢優化且佔用額外索引空間；
f、對於只包含特定類型的字段，可以使用enum、set 等符合數據類型；
g、數值型字段的比較比字符串的比較效率高得多，字段類型儘量使用最小、最簡單的數據類型。例如P地址可以使用int類型；
h、儘量使用TINYINT、SMALLINT、MEDIUM_INT作為整數類型而非INT，如果非負則加上UNSIGNED；
j、 VARCHAR的長度只分配真正需要的空間；
k、少使用text等大字段；

2、設計中間表

設計中間表，一般針對於統計分析功能，或者實時性不高的需求（OLTP、OLAP）

3、設計冗餘字段

為減少關聯查詢，創建合理的冗餘字段（創建冗餘字段還需要注意數據一致性問題）

4、主鍵優化

每張表建議都要有一個主鍵（主鍵索引），而且主鍵類型最好是有順序的數值類型。

5 、表拆分

5.1 垂直拆分

垂直拆分按照字段進行拆分，其實就是把組成一行的多個列分開放到不同的表中，這些表具有不同的結構，拆分後的表具有更少的列。例如用户表中的一些字段可能經常訪問，可以把這些字段放進一張表裏，另外一些不經常使用的信息就可以放進另外一張表裏。插入的時候使用事務，也可以保證兩表的數據一致。缺點也很明顯，由於拆分出來的兩張表存在一對一的關係，需要使用冗餘字段，而且需要join操作，我們在使用的時候可以分別取兩次，這樣的來説既可以避免join操作，又可以提高效率。
還有就是按業務平台的不同類型功能模塊進行拆分，比如分為訂單庫、資源庫、用户庫。拆分之後，每個業務平台中的應用工程只訪問對應的業務數據庫，一方面將單點數據庫拆分成了多個，分攤了單庫的讀寫IO壓力；另一方面，拆分後的數據庫各自獨立，實現了業務隔離，不再互相影響。

5.2 水平拆分

水平拆分按照行進行拆分，常見的就是分庫分表。以用户表為例，可以取用户ID，然後對ID取10的餘數，將用户均勻的分配進這 0-9這10個表中。查找的時候也按照這種規則，又快又方便。有些表業務關聯比較強，那麼可以使用按時間劃分的。例如每天的數據量很大，需要每天新建一張表。這種業務類型就是需要高速插入，但是對於查詢的效率不太關心。表越大，插入數據所需要索引維護的時間也就越長。
水平拆分優點：
a、經過拆分後，不存在單庫數據量大和高併發的性能瓶頸；
b、提高了系統的穩定性和負載能力；
c、緩解單庫IO壓力；
水平拆分缺點：
a、分庫事務的一致性難以解決；
b、跨庫Join表性能問題，邏輯複雜；
c、跨庫count/order by/group by以及聚合函數問題；
d、切分策略如何選擇，策略問題很可能導致數據分佈不均勻問題；
e、全局主鍵問題；
應對水平拆分問題的方案：
a、跨庫事務問題
解決跨庫事務問題主要可以通過兩種方法。第一種方法，使用分佈式事務，簡單有效但是性能代價高。第二種方式，應用程序和數據庫共同控制保證，將一個跨多個數據庫的分佈式事務分拆成多個僅處於單個數據庫上面的小事務，並通過應用程序來總控各個小事務。該方案優點在於能夠靈活控制，缺點在於改造量較大。
b、跨庫Join表的問題
對於業務平台的數據持久層來説，涉及複雜的Join多表查詢在所難免。解決這一問題的普遍做法是分兩次查詢解決。在第一次查詢的結果集中找出關聯數據的id，根據這些id發起第二次請求得到關聯數據。
c、跨節點的count,order by,group by以及聚合函數問題
與解決跨節點join問題的類似，只需要分別在各個單庫上得到結果後在業務應用端進行合併。和join不同的是每個結點的查詢可以採用多線程方式並行執行（在jdk8中可以用CompletableFuture解決），因此很多時候它的合併速度要比單個大數據量的錶快很多。
d、數據分片策略選擇
水平拆分中一個比較重要的問題就是按照什麼邏輯策略來進行數據分片（即為拆分庫表）。一種方案是按照地域類的屬性進行拆分；另外一種方案則是按照訂單ID/用户ID進行拆分。按照地域類的屬性進行拆分會使得數據聚合度比較高，做聚合查詢比較簡單，實現也相對簡單，缺點是數據分佈不均勻。按訂單ID拆分則正相反，優點是數據分佈均勻，不會出現一個數據庫數據極大或極小的情況，缺點是數據太分散，不利於做聚合查詢。比如，按訂單ID拆分後，一個客户的訂單可能分佈在不同的數據庫中，查詢一個客户下面的所有訂單，可能需要查詢多個數據庫。對於這種情況，一種解決方案是將需要聚合查詢的數據做冗餘表，冗餘的表不做拆分，同時在業務開發過程中，減少聚合查詢。
e、全局主鍵問題
原本依賴數據庫生成主鍵（比如自增）的表在拆分後需要自己實現主鍵的生成，因為一般拆分規則是建立在主鍵上的，所以在插入新數據時需要確定主鍵後才能找到存儲的表。在實際應用中，可以參考全局發號器方案。
分庫分表的組件可以用Sharding-JDBC或者MyCat。

5.3 分區

當數據庫單表存的數據量過大時候，可以考慮採用分區表的方案來解決。MySQL的分區表是由多個相關的物理子表組成，這些表也是可以由句柄對象表示，所以對於用户也可以直接訪問各個分區，存儲引擎管理分區的各個物理表和管理普通表一樣（所有的底層表都必須使用相同的存儲引擎），分區表的索引只是在各個底層表上各自加上一個相同的索引。
MySQL在創建表時使用PARTITION BY子句定義每個分區存放的數據。在執行查詢時，優化器會根據分區定義過濾那些沒有需要數據的分區，這樣查詢就無須掃描所有分區—只需查找包含數據的分區即可。
MySQL主要支持4種模式的分區：range分區、list預定義列表分區，hash 分區，key鍵值分區。
MySQL分區表的優點：
a、單表可以存儲更多的數據；
b、分區表的數據更容易維護，可以通過清除整塊分區以批量刪除大量數據，也可以增加新的分區來支持新插入的數據；
c、部分查詢能夠從查詢條件確定只落在少數分區上，查詢執行速度比較快；
d、分區表的數據還可以分佈在不同的物理設備上，從而高效地利用多個硬件設備；
e、可以使用分區表來避免某些特殊瓶頸，例如InnoDB單個索引的互斥訪問、ext3文件系統的inode鎖競爭；
f、可以備份和恢復單個分區；
分區表的限制和缺點
a、一個表最多隻能分1024個區；
b、如果分區字段中有主鍵或者唯一索引的列，那麼所有主鍵列和唯一索引都必須包含；
c、分區表中無法使用外鍵約束；
下面是根據時間字段來建立分區表的一個示例：
分區表適合的場景
a、最適合的場景數據的時間序列性比較強，則可以按時間來分區，查詢時加上時間範圍條件效率會非常高，同時對於不需要的歷史數據能很容易批量刪除;
b、如果數據有明顯的熱點，而且除了這部分數據，其他數據很少被訪問到，那麼可以將熱點數據單獨放在一個分區，讓這個分區的數據能夠有機會都緩存在內存中，查詢時只訪問一個很小的分區表，能夠有效使用索引和緩存；

6、讀寫分離

大型網站會有大量的併發訪問，如果還是傳統的數據結構，或者只是單單靠一台服務器扛，如此多的數據庫連接操作，數據庫必然會崩潰，數據丟失的話，後果更是不堪設想。這時候，我們需要考慮如何減少數據庫的聯接。我們發現一般情況對數據庫而言都是“讀多寫少”，也就説對數據庫讀取數據的壓力比較大，這樣分析可以採用數據庫集羣的方案。其中一個是主庫，負責寫入數據，我們稱為寫庫；其它都是從庫，負責讀取數據，我們稱為讀庫。這樣可以緩解一台服務器的訪問壓力。

7、數據庫集羣

如果訪問量非常大，雖然使用讀寫分離能夠緩解壓力，但是一旦寫操作一台服務器都不能承受了，這個時候我們就需要考慮使用多台服務器實現寫操作。例如可以使用MyCat搭建MySql集羣，對ID求3的餘數，這樣可以把數據分別存放到3台不同的服務器上，由MyCat負責維護集羣節點的使用。

（三）硬件優化

可以從以下幾個方面考慮：
1、配置較大的內存。足夠大的內存，是提高MySQL數據庫性能的方法之一。內存的IO比硬盤快的多，可以增加系統的緩衝區容量，使數據在內存停留的時間更長，以減少磁盤的IO。
2、磁盤I/O相關
使用SSD或者PCIe SSD設備，至少獲得數百倍甚至萬倍的IOPS提升；
儘可能選用RAID-10，而非RAID-5
使用機械盤的話，儘可能選擇高轉速的，例如選用15000RPM，而不是7200RPM的盤

（四）MySQL服務器參數

通過優化MySQL的參數可以提高資源利用率，從而達到提高MySQL服務器性能的目的。MySQL的配置參數都在my.conf或者my.ini文件的[mysqld]組中，常用的參數如下：

read_buffer_size：順序讀的緩衝區，變大提高順序讀效率，
read_rnd_buffer_size：隨機讀的緩衝區，變大提高隨機讀效率，
sort_buffer_size：排序緩衝區，變大提高排序效率，
join_buffer_size：連接緩衝區，變大提高表連接效率，
binlog_cache_size：二進制緩衝區，變大提高binlog寫入效率，默認32768，
tmp_table_size：臨時表緩衝區，變大提高臨時表存儲效率，
innodb_log_buffer_size：變大提高Redo日誌寫入效率，
key_buffer_size：表索引高速緩衝，變大提高 MyISAM 表索引讀寫效率，
query_cache_size：查詢緩存，變大提高查詢結果返回效率，建議關閉，
innodb_buffer_pool_size：用於緩存行數據、索引數據，以及事務鎖和自適應哈希等，可以設置為物理內存的50%-80%，
bing_format:可以設置為row，讓數據更加安全可靠，
wait_timeout：當MySQL連接閒置超過一定時間後將會被強行關閉。MySQL默認的wait-timeout值為8個小時。設置這個值是非常有意義的，比如你的網站有大量的MySQL鏈接請求（每個MySQL連接都是要內存資源開銷的），由於你的程序的原因有大量的連接請求空閒啥事也不幹，白白佔用內存資源，或者導致MySQL超過最大連接數從來無法新建連接導致“Too many connections”的錯誤。在設置之前你可以查看一下你的MYSQL的狀態（可用showprocesslist)，如果經常發現MYSQL中有大量的Sleep進程，則需要修改wait-timeout值了。
max_connections：是指MySql的最大連接數，如果服務器的併發連接請求量比較大，建議調高此值，以增加並行連接數量，當然這建立在機器能支撐的情況下，因為如果連接數越多，介於MySql會為每個連接提供連接緩衝區，就會開銷越多的內存，所以要適當調整該值，不能盲目提高設值。MySQL服務器允許的最大連接數16384。
max_user_connections：max_user_connections是指每個數據庫用户的最大連接針對某一個賬號的所有客户端並行連接到MYSQL服務的最大並行連接數。簡單説是指同一個賬號能夠同時連接到mysql服務的最大連接數。設置為0表示不限制。
default-storage-engine：default-storage-engine= InnoDB(設置InnoDB類型，另外還可以設置MyISAM類型)設置創建數據庫及表默認存儲類型。
innodb_flush_log_at_trx_commit設置為0或2，innodb_flush_log_at_trx_commit是MySQL InnoDB存儲引擎獨有的參數，用於控制InnoDB的Redo log日誌記錄方式。通過調優該參數，可以提升數據庫的性能和數據安全性。該參數的取值範圍為0、1、2，不同的值代表MySQL數據庫的Redo log不同的刷盤的方式：

當innodb_flush_log_at_trx_commit=1時，InnoDB將在每次事務提交時將log buffer的數據更新到文件系統os buffer中，並調用文件系統的flush操作將數據緩存更新至磁盤中。此種方式下，數據庫完全遵守ACID特性，安全性較高。
當innodb_flush_log_at_trx_commit=2時，InnoDB將在每次事務提交時將log buffer中的數據更新到文件系統緩存中，每秒鐘將文件系統緩存中的數據更新到磁盤一次，該操作由操作系統調度。因為DDL變更或其他InnoDB內部原因會導致更新磁盤的操作獨立於innodb_flush_log_at_trx_commit參數設置，不能完全保證每秒更新磁盤一次，沒有被更新到磁盤中的事務可能會因宕機而丟失。
當innodb_flush_log_at_trx_commit=0時，InnoDB會每秒鐘將log buffer中的數據更新到磁盤中。因為DDL變更或其他InnoDB內部原因會導致更新磁盤的操作獨立於innodb_flush_log_at_trx_commit參數設置，並不能完全保證每秒將數據更新到磁盤一次。因此，在實例崩潰恢復場景中，可能會出現丟失1秒鐘的事務。
需要注意的是，當innodb_flush_log_at_trx_commit設置為0或2時，並不能完全保證每秒將數據更新到磁盤一次，但也有可能更頻繁地更新數據到磁盤。因此，在實際應用中，需要根據性能和數據安全性等方面的需求來設置。

sync_binlog設置為N,sync_binlog是MySQL Binlog日誌的重要參數，用於控制Binlog的更新策略，通過對該參數的調優，可以提升數據庫的性能和數據安全性：

當sync_binlog=1時，MySQL會在每次事務提交後，將Log Buffer中的數據更新到磁盤上，此時MySQL安全性較高，但是IO消耗也較高。
當sync_binlog=0時，MySQL會在每次事務提交後將binlog_cache中的數據更新至文件系統緩衝區，但不會進行持久化，而是依賴操作系統來調度數據刷入磁盤。
當sync_binlog=N時，MySQL會在每N組事務提交後將數據更新到磁盤中。通過這種方式，可以在一定程度上平衡MySQL的性能和數據的安全性。如果N設置得比較大，可以提高系統的性能，但會降低數據的安全性。
綜上所述，innodb_flush_log_at_trx_commit和sync_binlog參數需要根據具體的需求來設置：

在對數據安全性要求較高的場景下，建議將這兩個參數設置為1。在對實例性能要求較高的場景下，建議將這兩個參數設置為0或者將innodb_flush_log_at_trx_commit設置為0，sync_binlog設置為N，以提高系統的性能，但需要注意可能會增加數據丟失的風險。
mrr設置為on，mrr_cost_based: on，儘可能的使用mmr來將隨機磁盤讀轉化為順序磁盤讀，從而提高了索引查詢的性能
啓用BKA，BKA主要適用於join的表上有索引可利用。

啓用sql：SET global optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
算法原理：將外層循環的行/結果集存入join buffer，內存循環的每一行數據與整個buffer中的記錄做比較，可以減少內層循環的掃描次數。

（五）linux參數

在 Linux 系統中，關於 JO 調度問題，建議使用 deadline 或者 noop模式。不要使用cfq模式，因為會影響數據庫性能。其中，對數據庫這種隨機讀寫的場景最有利的算注是deadline 模式。在新興的固態硬盤（比如 SSD、 Fusion I/O）上，最簡單的noop反而可能好的算法，因為其他3個算法的優化是基於縮短尋道時間的，而固態硬盤沒有所謂的尋道且1/O響應時間非常短。
設置內核參數vm.swappiness＝1

該參數表示使用swap的意向，要不惜一切代價避免使用swap（交換）分區。swapim參數值可設置範圍在0-100之間。低參數值會讓內核儘量少用交換，高參數值會使內核更地去使用交換空間。不建議設置為0，因為有可能會引發out of memory，但可以設置為1示儘量不使用swap。

文件系統選擇推薦使用xfs

推薦在Linux 下使用xfs文件系統，其次是選擇ext4文件系統，放棄ext3。xis是一性能的日誌文件系統，特別擅長處理大文件，對比ext3、ext4。MySQL在xfs上一般有性能、更高的吞吐，相比 ext4更能保證數據完整。

（六）其他

1、數據庫連接池

使用數據庫連接池可以將資源重用，得到更快的系統響應速度，一般是先查詢根據上文提到的max_connections和max_user_connections後，來配置應用程序中的數據庫連接池，常用的數據庫連接池有druid、dbcp、HikariCP等，一般在druid中要配置如下幾項：

當然項目組要根據自己使用的連接池和實際情況進行壓測後調整。

2、使用場景

我們要分析自己的場景是olap還是oltp，olap和oltp的區別是這樣的，數據處理大致可以分成兩大類：聯機事務處理OLTP （ on-line transaction processing ）、聯機分析處理OLAP （ On-Line Analytical Processing ）。兩者的之間的比較如下：

編輯
OLTP系統最容易出現瓶頸的地方就是CPU與磁盤子系統。
（1）CPU出現瓶頸常表現在邏輯讀總量與計算性函數或者是過程上，
邏輯讀總量等於單個語句的邏輯讀乘以執行次數，如果單個語句執行速度雖然很快，但是執行次數非常多，那麼，也可能會導致很大的邏輯讀總量。設計的方法與優化的方法就是減少單個語句的邏輯讀，或者是減少它們的執行次數。
另外一些計算型的函數的頻繁使用，也會消耗大量的CPU時間，造成系統的負載升高，正確的設計方法或者是優化方法，需要儘量避免計算過程，如保存計算結果到統計表就是一個好的方法。
（2）磁盤子系統在OLTP環境中，它的承載能力一般取決於它的IOPS處理能力. 因為在OLTP環境中，磁盤物理讀一般都是db file sequential read，也就是單塊讀，但是這個讀的次數非常頻繁。如果頻繁到磁盤子系統都不能承載其IOPS的時候，就會出現大的性能問題。
OLTP比較常用的設計與優化方式為Cache技術與B-tree索引技術，Cache決定了很多語句不需要從磁盤子系統獲得數據，所以，這樣數據庫層面 cache與buffer對OLTP系統是很重要的。另外，在索引使用方面，語句越簡單越好，這樣執行計劃也穩定，而且一定要使用綁定變量，減少語句解析，儘量減少表關聯，儘量減少分佈式事務。因為併發量很高，批量更新時要分批快速提交，以避免阻塞的發生。
OLAP這樣的系統中，語句的執行量不是考核標準，因為一條語句的執行時間可能會非常長，讀取的數據也非常多。所以，在這樣的系統中，考核的標準往往是磁盤子系統的吞吐量（帶寬），如能達到多少MB/s的流量。這類系統強調數據分析，強調SQL執行時長，強調磁盤I/O，強調分區等。
磁盤子系統的吞吐量則往往取決於磁盤的個數，這個時候，Cache基本是沒有效果的，數據庫的讀寫類型基本上是Db文件分散讀取與直接路徑讀/寫。應儘量採用個數比較多的磁盤以及比較大的帶寬，如4Gb的光纖接口。
分區技術在OLAP系統中的重要性主要體現在數據庫管理上，刪除數據可以通過分區進行刪除，至於分區在性能上的影響，它可以使得一些大表的掃描變得很快（只掃描單個分區）。
對於OLAP系統，在內存上可優化的餘地很小，增加CPU 處理速度和磁盤I/O 速度是最直接的提高數據庫性能的方法，當然這也意味着系統成本的增加。比如我們要對幾億條或者幾十億條數據進行聚合處理，這種海量的數據，全部放在內存中操作是很難的，同時也沒有必要，因為這些數據快很少重用，緩存起來也沒有實際意義，而且還會造成物理I/O相當大。所以這種系統的瓶頸往往是磁盤I/O上面的。