目錄
1.表結構設計 4
1.1.資料庫三範式（3NF） 4
1.2.錯誤設計 4
1.3.正确設計 5
1.4.反3NF 5
- 字段類型設計 6
2.1.資料庫類型選擇原則 6
2.2.舉例說明 6
2.2.1.int類型的選用 6
2.2.2.datetime和timestamp 7
2.3.字段設計其他注意點 8
3.索引優化 8
3.1.索引分類 8
3.2.索引優化注意點 8
4.SQL優化 9
4.1.優化思路 9
4.2.基本原則 9
4.3.舉例說明 10
4.3.1.分批處理 10
4.3.2.操作符<>優化 10
4.3.3.OR優化 10
4.3.4.IN優化 10
4.3.5.不做列運算 11
4.3.6.避免Select all 11
4.3.7.Like優化 11
4.3.8.Join優化 11
4.3.9.Limit優化 12
1.表結構設計
主要從資料庫設計三大範式分析咱們設計表結構需要的注意點。大家在遵從三大範式的基礎上能靈活變通，結合自己的業務需要，設計出更高效的表結構。
1.1.資料庫三範式（3NF）
1NF： 保證每列的原子性：即表的列具有原子性，不可再分解。隻要資料庫是關系型資料庫，就自動滿足1NF。（例如：很多場景字段存儲以“,”分隔存儲，這種情況要避免，不利于檢索、建立索引、維護（新增、修改、删除），應該再做個關聯表）
2NF： 保證一張表隻描述一件事情：表中的記錄是唯一的，就滿足2NF，通常 我們設計主鍵來實作，主鍵一般不含業務邏輯，自增長。
3NF：保證每列都和主鍵直接相關： 即表中不要有備援資料。（特殊情況下可做适當備援，比如DRDS分庫分表情況下的查詢，做适當備援避免表關聯查詢。）
1.2.錯誤設計
圖1
1.3.正确設計
圖2
1.4.反3NF
沒有備援的資料庫未必是最好的資料庫，有時為了提高運作效率，就必須降低範式标準，适當保留備援資料。具體做法是： 在概念資料模型設計時遵守第三範式，降低範式标準的工作放到實體資料模型設計時考慮。降低範式就是增加字段，允許備援。例如下圖查詢浏覽次數:
- 字段類型設計
在表字段設計中，字段類型至關重要，合理的資料類型能幫助開發者減少很大的存儲空間，提升資料的檢索效率；甚至可以友善後期的資料庫維護人員。
2.1.資料庫類型選擇原則
原則遵循：更簡單或者占用空間更小
1、如果長度能夠滿足，整型盡量使用tinyint、smallint、medium_int而非int。
2、如果字元串長度确定，采用char類型。
3、如果varchar能夠滿足，不采用text類型。
4、精度要求較高的使用decimal類型，也可以使用BIGINT，比如精确兩位小數就乘以100後儲存。
5、盡量采用timestamp而非datetime。
2.2.舉例說明
2.2.1.int類型的選用
整型字段類型包含 tinyint、smallint、mediumint、int、bigint 五種，占用空間大小及存儲範圍如下圖所示：
存儲位元組越小，占用空間越小。是以本着最小化存儲的原則，我們要盡量選擇合适的整型，下面給出幾個常見案例及選擇建議。
1、根據存儲範圍選擇合适的類型，比如人的年齡用 unsigned tinyint（範圍 0~255，人的壽命不會超過 255 歲）；
2、若存儲的資料為非負數值，建議使用 UNSIGNED 辨別，可以擴大正數的存儲範圍。
3、短資料使用 TINYINT 或 SMALLINT，比如：人類年齡，城市代碼。
4、存儲狀态變量的字段用 TINYINT ，比如：是否删除，0代表未删除 1代表已删除。
5、主鍵列，無負數，建議使用 INT UNSIGNED 或者 BIGINT UNSIGNED；預估字段數字取值會超過 42 億，使用 BIGINT 類型。
2.2.2.datetime和timestamp
相比datetime，timestamp占用更少的空間，以UTC的格式儲存自動轉換時區。
timestamp翻譯為漢語即"時間戳"，它是目前時間到 Unix元年(1970 年 1 月 1 日 0 時 0 分 0 秒)的秒數，占用4個位元組，而且是以UTC的格式儲存，它會自動檢索目前時區并進行轉換。datetime以8個位元組儲存，不會進行時區的檢索。也就是說，對于timestamp來說，如果儲存時的時區和檢索時的時區不一樣，那麼拿出來的資料也不一樣。對于datetime來說，存什麼拿到的就是什麼。下面給出幾個常見案例及選擇建議。
1、根據存儲範圍來選取，比如生産時間，保存期限等時間建議選取datetime，因為datetime能存儲的範圍更廣。
2、記錄本行資料的插入時間和修改時間建議使用timestamp。
3、和時區相關的時間字段選用timestamp。
4、如果隻是想表示年、日期、時間的還可以使用 year、 date、 time，它們分别占據 1、3、3 位元組，而datetime就是它們的集合。
2.3.字段設計其他注意點
1、避免空值
MySQL中字段為NULL時依然占用空間，會使索引、索引統計更加複雜。從NULL值更新到非NULL無法做到原地更新，容易發生索引分裂影響性能。盡可能将NULL值用有意義的值代替，也能避免SQL語句裡面包含is not null的判斷。
2、text類型優化
由于text字段儲存大量資料，表容量會很早漲上去，影響其他字段的查詢性能。建議抽取出來放在子表裡，用業務主鍵關聯。
3.索引優化
我們需要知道索引其實是一種資料結構，其功能是幫助我們快速比對查找到需要的資料行，是資料庫性能優化最常用的工具之一。其作用相當于超市裡的導購員、書本裡的目錄。
3.1.索引分類
1、普通索引：最基本的索引。
2、組合索引：多個字段上建立的索引，能夠加速複合查詢條件的檢索。
3、唯一索引：與普通索引類似，但索引列的值必須唯一，允許有空值。
4、組合唯一索引：列值的組合必須唯一。
5、主鍵索引：特殊的唯一索引，用于唯一辨別資料表中的某一條記錄，不允許有空值，一般用primary key限制。
6、全文索引：用于海量文本的查詢，MySQL5.6之後的InnoDB和MyISAM均支援全文索引。由于查詢精度以及擴充性不佳，更多的企業選擇Elasticsearch。
3.2.索引優化注意點
1、分頁查詢很重要，如果查詢資料量超過30%，MYSQL不會使用索引。
2、單表索引數不超過5個、單個索引字段數不超過5個。
3、字元串可使用字首索引，字首長度控制在5-8個字元。
4、字段唯一性太低，增加索引沒有意義，如：是否删除、性别。
5、更新十分頻繁的字段上不宜建立索引：因為更新操作會變更B+樹，重建索引。這個過程是十分消耗資料庫性能的。
6、建立索引時避免以下錯誤觀念：索引越多越好，認為一個查詢就需要建一個索引；甯缺勿濫，認為索引會消耗空間、嚴重拖慢更新和新增速度；抵制唯一索引，認為業務的唯一性一律需要在應用層通過“先查後插”方式解決；過早優化，在不了解系統的情況下就開始優化。
4.SQL優化
SQL性能優化的本質就是減少運算次數、減少記憶體消耗、涉及盡量少的資料。
4.1.優化思路
1、定位優化對象的性能瓶頸：優化前需了解查詢的瓶頸是IO還是CPU，可通過PROFILING很容易定位查詢的瓶頸；
2、明确優化目标；
3、從Explain入手；
4、多使用profile；
4.2.基本原則
1、永遠用小結果集驅動大結果集；
From子句中sql解析順序為從右向左，執行時會以最左邊的表為基礎表循環與右邊表資料做笛卡爾積，是以以小結果集驅動能減少循環次數，進而減少對被驅動結果集的通路，進而減少被驅動表的鎖定。 2、盡可能在索引中完成排序；
排序算法有兩種：a.查出排序字段和行指針，排序，再通過行指針獲得行資料所需列，傳回結果集；b.取出所有排序列資料，在排序緩沖區中排完序直接傳回結果集。
索引排序是利用索引的有序性對資料排序的。
3、隻取出子集需要的colums
4、僅僅使用最有效的過濾條件；
5、盡可能避免複雜的Join和子查詢；
4.3.舉例說明
4.3.1.分批處理
批量更新一張表資料時，當需要更新的資料集特别大時，可以分批次更新，限制每個批次的更新數量。業務真實場景：讀取EXCEL資料批量插入表中：
int begin = 0;
int end = begin + maxValue ;
while (begin <= insertList.size()-1){
List<MonthlyStaffDetail> child = insertList.subList(begin, end <= insertList.size() ? end : insertList.size());
//分批插入資料
batchInsert(child);
begin = end;
end = begin + maxValue ;
}
4.3.2.操作符<>優化
通常<>操作符無法使用索引，舉例如下，查詢金額不為100元的訂單：
select id from orders where amount != 100;
如果金額為100的訂單極少，這種資料分布嚴重不均的情況下，有可能使用索引。鑒于這種不确定性，采用union聚合搜尋結果，改寫方法如下：
(select id from orders where amount > 100)
union all
(select id from orders where amount < 100 and amount > 0)
4.3.3.OR優化
在Innodb引擎下or無法使用組合索引，比如：
select id，product_name from orders where mobile_no = '13421800407' or user_id = 100;
OR無法命中mobile_no + user_id的組合索引，可采用union，如下所示：
(select id，product_name from orders where mobile_no = '13421800407')
union
(select id，product_name from orders where user_id = 100);
此時id和product_name字段都有索引，查詢才最高效。
4.3.4.IN優化
IN适合主表大子表小，EXIST适合主表小子表大。由于查詢優化器的不斷更新，很多場景這兩者性能差不多一樣了。
舉例如下：
select o.id from orders o where o.user_id in(select u.id from user where u.level = 'VIP');
采用JOIN如下所示：
select o.id from orders o left join user u on o.user_id = u.id where u.level = 'VIP';
4.3.5.不做列運算
通常在查詢條件列運算會導緻索引失效，如下所示：
查詢當日訂單
select id from order where date_format(create_time，'%Y-%m-%d') = '2019-07-01';
date_format函數會導緻這個查詢無法使用索引，改寫後：
select id from order where create_time between '2019-07-01 00:00:00' and '2019-07-01 23:59:59';
4.3.6.避免Select all
如果不查詢表中所有的列，避免使用SELECT *，它會進行全表掃描，不能有效利用索引。
4.3.7.Like優化
like用于模糊查詢，舉個例子（field已建立索引）：
SELECT column FROM table WHERE field like '%keyword%';
這個查詢未命中索引，換成下面的寫法：
SELECT column FROM table WHERE field like 'keyword%';
4.3.8.Join優化
join的實作是采用Nested Loop Join算法，就是通過驅動表的結果集作為基礎資料，通過該結資料作為過濾條件到下一個表中循環查詢資料，然後合并結果。如果有多個join，則将前面的結果集作為循環資料，再次到後一個表中查詢資料。
驅動表和被驅動表盡可能增加查詢條件，滿足ON的條件而少用Where，用小結果集驅動大結果集。
被驅動表的join字段上加上索引，無法建立索引的時候，設定足夠的Join Buffer Size。禁止join連接配接三個以上的表，嘗試增加備援字段。 4.3.9.Limit優化
limit用于分頁查詢時越往後翻性能越差，解決的原則：縮小掃描範圍，如下所示：
select * from orders order by id desc limit 100000,10
耗時0.4秒
select * from orders order by id desc limit 1000000,10
耗時5.2秒
先篩選出ID縮小查詢範圍，寫法如下：
select * from orders where id > (select id from orders order by id desc limit 1000000, 1) order by id desc limit 0,10
耗時0.5秒
如果查詢條件僅有主鍵ID，寫法如下：
select id from orders where id between 1000000 and 1000010 order by id desc