資料庫sql優化總結-百萬級資料庫優化方案+數據分析 有三張百萬級數據表 知識點表（ex_subject_point）9,316條數據 試題表（ex_question_junior）2,159,519條數據 有45個欄位 知識點試題關係表（ex_question_r_knowledge）3,156,... ...
項目背景
有三張百萬級數據表
知識點表（ex_subject_point）9,316條數據
試題表（ex_question_junior）2,159,519條數據 有45個欄位
知識點試題關係表（ex_question_r_knowledge）3,156,155條數據
測試資料庫為：mysql （5.7）
1.對查詢進行優化，要儘量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。
案例分析：
SELECT ex_question_junior.QUESTION_ID FROM ex_question_junior WHERE ex_question_junior.GRADE_ID=15
執行時間：17.609s （多次執行，在17s左右徘徊）
優化後：給GRADE_ID欄位添加索引後
執行時間為：11.377s（多次執行，在11s左右徘徊）
備註：我們一般在什麼欄位上建索引？
這是一個非常複雜的話題，需要對業務及數據充分分析後再能得出結果。主鍵及外鍵通常都要有索引，其它需要建索引的欄位應滿足以下條件：
a、欄位出現在查詢條件中，並且查詢條件可以使用索引；
b、語句執行頻率高，一天會有幾千次以上；
c、通過欄位條件可篩選的記錄集很小，那數據篩選比例是多少才適合？
這個沒有固定值，需要根據表數據量來評估，以下是經驗公式，可用於快速評估：
小表(記錄數小於10000行的表)：篩選比例<10%；
大表：(篩選返回記錄數)<(表總記錄數*單條記錄長度)/10000/16
單條記錄長度≈欄位平均內容長度之和+欄位數*2
以下是一些欄位是否需要建B-TREE索引的經驗分類：
2、應儘量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描
select id from t where num is null最好不要給資料庫留NULL，儘可能的使用 NOT NULL填充資料庫.
備註、描述、評論之類的可以設置為 NULL，其他的，最好不要使用NULL。
不要以為 NULL 不需要空間，比如：char(100) 型，在欄位建立時，空間就固定了， 不管是否插入值（NULL也包含在內），都是占用 100個字元的空間的，如果是varchar這樣的變長欄位， null 不占用空間。
可以在num上設置預設值0，確保表中num列沒有null值，然後這樣查詢：
select id from t where num = 0
案例分析：
在mysql資料庫中對欄位進行null值判斷，是不會放棄使用索引而進行全表掃描的。
SELECT ex_question_junior.QUESTION_ID FROM ex_question_junior WHERE IS_USE is NULL
執行時間是：11.729s
SELECT ex_question_junior.QUESTION_ID FROM ex_question_junior WHERE IS_USE =0
執行時間是12.253s
時間幾乎一樣。
3、應儘量避免在 where 子句中使用 != 或 <> 操作符，否則將引擎放棄使用索引而進行全表掃描。
案例分析：
在mysql資料庫中where 子句中使用 != 或 <> 操作符，引擎不會放棄使用索引。
EXPLAIN
SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior
WHERE ex_question_junior.GRADE_ID !=15
執行時間是：17.579s
