APP
目錄
1.對查詢進行優(yōu)化，要盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。
2、應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描
3、應盡量避免在 where 子句中使用 != 或 <> 操作符，否則將引擎放棄使用索引而進行全表掃描。
4.應盡量避免在 where 子句中使用 or 來連接條件，如果一個字段有索引，一個字段沒有索引，將導致引擎放棄使用索引而進行全表掃描
項目背景
有三張百萬級數(shù)據(jù)表
知識點表（ex_subject_point）9,316條數(shù)據(jù)
試題表（ex_question_junior）2,159,519條數(shù)據(jù) 有45個字段
知識點試題關系表（ex_question_r_knowledge）3,156,155條數(shù)據(jù)
測試數(shù)據(jù)庫為：mysql （5.7）
?
1.對查詢進行優(yōu)化，要盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。案例分析：
SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior
WHERE ex_question_junior.GRADE_ID=1執(zhí)行時間：17.609s （多次執(zhí)行，在17s左右徘徊）
優(yōu)化后：給GRADE_ID字段添加索引后
執(zhí)行時間為：11.377s（多次執(zhí)行，在11s左右徘徊）
備注：我們一般在什么字段上建索引？
這是一個非常復雜的話題，需要對業(yè)務及數(shù)據(jù)充分分析后再能得出結(jié)果。主鍵及外鍵通常都要有索引，其它需要建索引的字段應滿足以下條件：
a、字段出現(xiàn)在查詢條件中，并且查詢條件可以使用索引；
b、語句執(zhí)行頻率高，一天會有幾千次以上；
c、通過字段條件可篩選的記錄集很小，那數(shù)據(jù)篩選比例是多少才適合？
這個沒有固定值，需要根據(jù)表數(shù)據(jù)量來評估，以下是經(jīng)驗公式，可用于快速評估：
小表(記錄數(shù)小于10000行的表)：篩選比例<10%；
大表：(篩選返回記錄數(shù))<(表總記錄數(shù)*單條記錄長度)/10000/16
單條記錄長度≈字段平均內(nèi)容長度之和+字段數(shù)*2
以下是一些字段是否需要建B-TREE索引的經(jīng)驗分類：
select id from t where num is null
最好不要給數(shù)據(jù)庫留NULL，盡可能的使用?NOT NULL填充數(shù)據(jù)庫.
備注、描述、評論之類的可以設置為?NULL，其他的，最好不要使用NULL。
不要以為?NULL?不需要空間，比如：char(100)?型，在字段建立時，空間就固定了，?不管是否插入值（NULL也包含在內(nèi)），都是占用?100個字符的空間的，如果是varchar這樣的變長字段，?null?不占用空間。
可以在num上設置默認值0，確保表中num列沒有null值，然后這樣查詢：
select id from t where num = 0
案例分析：
在mysql數(shù)據(jù)庫中對字段進行null值判斷，是不會放棄使用索引而進行全表掃描的。
SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior
WHERE IS_USE is NULL?
執(zhí)行時間是：11.729s
?
SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior
WHERE IS_USE =0執(zhí)行時間是12.253s
時間幾乎一樣。
3、應盡量避免在 where 子句中使用 != 或 <> 操作符，否則將引擎放棄使用索引而進行全表掃描。案例分析：
在mysql數(shù)據(jù)庫中where 子句中使用 != 或 <> 操作符，引擎不會放棄使用索引。
EXPLAIN
SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior
WHERE ex_question_junior.GRADE_ID !=15
執(zhí)行時間是：17.579s
執(zhí)行時間是：16.966s
4.應盡量避免在 where 子句中使用 or 來連接條件，如果一個字段有索引，一個字段沒有索引，將導致引擎放棄使用索引而進行全表掃描案例分析：
GRADE_ID字段有索引，QUESTION_TYPE沒索引
執(zhí)行時間是：11.661s
優(yōu)化方案：
通過union all 方式，把有索引字段和非索引字段分開。索引字段就有效果了
執(zhí)行時間是：11.811s
但是，非索引字段依然查詢速度會很慢，所以查詢條件，能加索引的盡量加索引
5.in 和 not in 也要慎用，否則會導致全表掃描案例分析
注：在mysql數(shù)據(jù)庫中where 子句中對索引字段使用 in 和 not in操作符，引擎不會放棄使用索引。
注：在mysql數(shù)據(jù)庫中where 子句中對不是索引字段使用 in 和 not in操作符，會導致全表掃描。
案例分析2：
用between和in的區(qū)別
SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior
WHERE ex_question_junior.QUESTION_TYPE IN(1,2,3,4)執(zhí)行時間為1.082s
SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior
WHERE ex_question_junior.QUESTION_TYPE between 1 and 4?
執(zhí)行時間為0.924s
?
時間上是相差不多的
案例分析3：
用exists 和 in區(qū)別：結(jié)論
用exists 和 in區(qū)別：結(jié)論
1. in()適合B表比A表數(shù)據(jù)大的情況（A<B）
select * from A
where id in(select id from B)
2. exists()適合B表比A表數(shù)據(jù)小的情況(A>B)
select * from A
where exists(
select 1 from B where B.id = A.id
)
3.當A表數(shù)據(jù)與B表數(shù)據(jù)一樣大時,in與exists效率差不多,可任選一個使用.語法
select * from A
where id in(select id from B)
ex_question_r_knowledge表數(shù)據(jù)量大，ex_subject_point表數(shù)據(jù)量小
****************************************************************************
ex_question_r_knowledge(A)表數(shù)據(jù)量大，ex_subject_point表數(shù)據(jù)量小(B)(A>B)
用exists適合
SELECT *
FROM ex_question_r_knowledge
WHERE ex_question_r_knowledge.SUBJECT_POINT_ID IN
(
SELECT ex_subject_point.SUBJECT_POINT_ID
FROM ex_subject_point
WHERE ex_subject_point.SUBJECT_ID=7
)?
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
)?
執(zhí)行時間是：13.537s
*************************************************************************
ex_subject_point表數(shù)據(jù)量小(A),ex_question_r_knowledge(B)表數(shù)據(jù)量大(A<B)
用in適合
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 )?
?
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 )執(zhí)行時間是：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
)執(zhí)行時間是：11.978s
6、like模糊全匹配也將導致全表掃描案例分析
EXPLAIN
SELECT *
FROM ex_subject_point
WHERE ex_subject_point.path like "%/11/%"?
若要提高效率，可以考慮全文檢索。lucene了解一下?；蛘咂渌梢蕴峁┤乃饕膎osql數(shù)據(jù)庫，比如tt server或MongoDB
還會陸續(xù)更新，還有幾個小節(jié)。
?
昨天晚上突發(fā)奇想，like 模糊全匹配，會導致全表掃描，那模糊后匹配和模糊前匹配也會是全表掃描嗎？
今天開電腦，做了下測試。結(jié)果如下：
like模糊后匹配，不會導致全表掃描
like模糊前匹配，會導致全表掃描
MY SQL的原理就是這樣的，LIKE模糊全匹配會導致索引失效，進行全表掃描；LIKE模糊前匹配也會導致索引失效，進行全表掃描；但是LIKE模糊后匹配，索引就會有效果。
?
參考：
?
https://mp.weixin.qq.com/s?__biz=MzIxMjg4NDU1NA==&mid=2247483684&idx=1&sn=f5abc60e696b2063e43cd9ccb40df101&chksm=97be0c01a0c98517029ff9aa280b398ab5c81fa1fcfe0e746222a3bfe75396d9eea1e249af38&mpshare=1&scene=1&srcid=0606XGHeBS4RBZloVv786wBY#rd
?
***************************************************************************
作者：小虛竹
歡迎任何形式的轉(zhuǎn)載，但請務必注明出處。
限于本人水平，如果文章和代碼有表述不當之處，還請不吝賜教。
?
我不是個偉大的程序員，我只是個有著一些優(yōu)秀習慣的好程序員而己
?
本文摘自 ：https://blog.51cto.com/u
