項目背景

有三張百萬級數據表

知識點表（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 涉及的列上建立索引。

案例分析：

2. SELECT ex_question_junior.QUESTION_ID
3. FROM ex_question_junior
4. WHERE ex_question_junior.GRADE_ID=1

執行時間：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值判斷，是不會放棄使用索引而進行全表掃描的。

2. SELECT ex_question_junior.QUESTION_ID
3. FROM ex_question_junior
4. WHERE IS_USE is NULL

執行時間是：11.729s

2. SELECT ex_question_junior.QUESTION_ID
3. FROM ex_question_junior
4. WHERE IS_USE =0

執行時間是12.253s

時間幾乎一樣。

3、應盡量避免在 where 子句中使用 != 或 <> 操作符，否則將引擎放棄使用索引而進行全表掃描。

案例分析：

在mysql資料庫中where 子句中使用 != 或 <> 操作符，引擎不會放棄使用索引。

2. EXPLAIN
3. SELECT ex_question_junior.QUESTION_ID
4. FROM ex_question_junior
5. WHERE ex_question_junior.GRADE_ID !=15

執行時間是：17.579s

執行時間是：16.966s

4.應盡量避免在 where 子句中使用 or 來連接條件，如果一個欄位有索引，一個欄位沒有索引，將導致引擎放棄使用索引而進行全表掃描

案例分析：

GRADE_ID欄位有索引，QUESTION_TYPE沒索引

執行時間是：11.661s

優化方案：

通過union all 方式，把有索引欄位和非索引欄位分開。索引欄位就有效果了

執行時間是：11.811s

但是，非索引欄位依然查詢速度會很慢，所以查詢條件，能加索引的盡量加索引

5.in 和 not in 也要慎用，否則會導致全表掃描

案例分析

註：在mysql資料庫中where 子句中對索引欄位使用 in 和 not in操作符，引擎不會放棄使用索引。

註：在mysql資料庫中where 子句中對不是索引欄位使用 in 和 not in操作符，會導致全表掃描。

案例分析2：

用between和in的區別

2. SELECT ex_question_junior.QUESTION_ID
3. FROM ex_question_junior
4. WHERE ex_question_junior.QUESTION_TYPE IN(1,2,3,4)

執行時間為1.082s

2. SELECT ex_question_junior.QUESTION_ID
3. FROM ex_question_junior
4. WHERE ex_question_junior.QUESTION_TYPE between 1 and 4

執行時間為0.924s

時間上是相差不多的

案例分析3：

用exists 和 in區別：結論1. in()適合B表比A表數據大的情況2. exists()適合B表比A表數據小的情況當A表數據與B表數據一樣大時,in與exists效率差不多,可任選一個使用.語法

select * from A

where id in(select id from B)

ex_question_r_knowledge表數據量大，ex_subject_point表數據量小

****************************************************************************

2. SELECT *
3. FROM ex_question_r_knowledge
4. WHERE ex_question_r_knowledge.SUBJECT_POINT_ID IN
5. (
6. SELECT ex_subject_point.SUBJECT_POINT_ID
7. FROM ex_subject_point
8. WHERE ex_subject_point.SUBJECT_ID=7
9. )

2. SELECT *
3. FROM ex_question_r_knowledge
4. WHERE exists
5. (
6. SELECT 1
7. FROM ex_subject_point
8. WHERE ex_subject_point.SUBJECT_ID=7
9. AND ex_subject_point.SUBJECT_POINT_ID = ex_question_r_knowledge.SUBJECT_POINT_ID
10. )

SELECT *

FROM ex_question_r_knowledge

WHERE exists

(

SELECT 1

FROM ex_subject_point

WHERE ex_subject_point.SUBJECT_ID=7

AND ex_subject_point.SUBJECT_POINT_ID = ex_question_r_knowledge.SUBJECT_POINT_ID

)

執行時間是：13.537s

*************************************************************************

用in適合

2. SELECT *
3. FROM ex_subject_point
4. WHERE
5. ex_subject_point.SUBJECT_POINT_ID IN( SELECT
6. ex_question_r_knowledge.SUBJECT_POINT_ID FROM
7. ex_question_r_knowledge WHERE
8. ex_question_r_knowledge.GRADE_TYPE=2 )

SELECT * FROM ex_subject_point WHERE

ex_subject_point.SUBJECT_POINT_ID IN( SELECT

ex_question_r_knowledge.SUBJECT_POINT_ID FROM

ex_question_r_knowledge WHERE

ex_question_r_knowledge.GRADE_TYPE=2 )

執行時間是：1.554s

SELECT *

FROM ex_subject_point

WHERE exists(

SELECT ex_question_r_knowledge.SUBJECT_POINT_ID

FROM ex_question_r_knowledge

WHERE ex_question_r_knowledge.GRADE_TYPE=2

AND ex_question_r_knowledge.SUBJECT_POINT_ID= ex_subject_point.SUBJECT_POINT_ID

)

執行時間是：11.978s

6、like模糊全匹配也將導致全表掃描

案例分析

2. EXPLAIN
3. SELECT *
4. FROM ex_subject_point
5. WHERE ex_subject_point.path like "%/11/%"

若要提高效率，可以考慮全文檢索。lucene了解一下。或者其他可以提供全文索引的nosql資料庫，比如tt server或MongoDB

還會陸續更新，還有幾個小節。

昨天晚上突發奇想，like 模糊全匹配，會導致全表掃描，那模糊後匹配和模糊前匹配也會是全表掃描嗎？

今天開電腦，做了下測試。結果如下：

like模糊後匹配，不會導致全表掃描

like模糊前匹配，會導致全表掃描

MY SQL的原理就是這樣的，LIKE模糊全匹配會導致索引失效，進行全表掃描；LIKE模糊前匹配也會導致索引失效，進行全表掃描；但是LIKE模糊後匹配，索引就會有效果。

限於本人水平，如果文章和代碼有表述不當之處，還請不吝賜教。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！