项目布景
有三张百万级数据表
知识点表(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=1
执行时间：17.609s (屡次执行，在17s左右徘徊)
优化后：给GRADE_ID字段添加索引后
执行时间为：11.377s(屡次执行，在11s左右徘徊)
备注：我们一般在什么字段上建索引？
这是一个很是复杂的话题，需要对业务及数据充分阐发后再能得出结果。主键及外键通常都要有索引，其它需要建索引的字段应满足以下条件：
a、字段呈现在查询条件中，并且查询条件可以使用索引；
b、语句执行频率高，一天会有几千次以上；
c、通过字段条件可筛选的记录集很小，那数据筛选比例是几多才适合？
这个没有固定值，需要根据表数据量来评估，以下是经验公式，可用于快速评估：
小表(记录数小于10000行的表)：筛选比例<10%；
年夜表：(筛选返回记录数)
单条记录长度≈字段平均内容长度之和+字段数*2
以下是一些字段是否需要建B-TREE索引的经验分类：
数据库sql优化总结之百万级数据库优化方案+案例阐发-1.jpg (65.47 KB, 下载次数: 0)
2018-7-15 21:12 上传
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_IDFROM ex_question_junior WHERE IS_USE is NULL
执行时间是：11.729s
SELECT ex_question_junior.QUESTION_IDFROM ex_question_junior WHERE IS_USE =0
执行时间是12.253s
时间几乎一样。
3、应尽量避免在 where 子句中使用 != 或 <> 操作符，不然将引擎抛却使用索引而进行全表扫描。
案例阐发：
在mysql数据库中where 子句中使用 != 或 <> 操作符，引擎不会抛却使用索引。
EXPLAINSELECT ex_question_junior.QUESTION_IDFROM ex_question_junior WHERE ex_question_junior.GRADE_ID !=15
数据库sql优化总结之百万级数据库优化方案+案例阐发-2.jpg (23.18 KB, 下载次数: 0)
2018-7-15 21:12 上传
执行时间是：17.579s
数据库sql优化总结之百万级数据库优化方案+案例阐发-3.jpg (35.39 KB, 下载次数: 0)
2018-7-15 21:12 上传
执行时间是：16.966s
4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将致使引擎抛却使用索引而进行全表扫描
案例阐发：
GRADE_ID字段有索引，QUESTION_TYPE没索引
数据库sql优化总结之百万级数据库优化方案+案例阐发-4.jpg (23.51 KB, 下载次数: 0)
2018-7-15 21:12 上传
执行时间是：11.661s
优化方案：
通过union all 体例，把有索引字段和非索引字段分隔。索引字段就有效果了
数据库sql优化总结之百万级数据库优化方案+案例阐发-5.jpg (31.94 KB, 下载次数: 0)
2018-7-15 21:12 上传
执行时间是：11.811s
可是，非索引字段依然查询速度会很慢，所以查询条件，能加索引的尽量加索引
5.in 和 not in 也要慎用，不然会致使全表扫描
案例阐发
注：在mysql数据库中where 子句中对索引字段使用 in 和 not in操作符，引擎不会抛却使用索引。
数据库sql优化总结之百万级数据库优化方案+案例阐发-6.jpg (39.02 KB, 下载次数: 0)
2018-7-15 21:12 上传
注：在mysql数据库中where 子句中对不是索引字段使用 in 和 not in操作符，会致使全表扫描。
数据库sql优化总结之百万级数据库优化方案+案例阐发-7.jpg (35.71 KB, 下载次数: 0)
2018-7-15 21:12 上传
案例阐发2：
用between和in的区别
SELECT ex_question_junior.QUESTION_IDFROM ex_question_juniorWHERE ex_question_junior.QUESTION_TYPE IN(1,2,3,4)
执行时间为1.082s
SELECT ex_question_junior.QUESTION_IDFROM ex_question_juniorWHERE 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表数据量小
****************************************************************************
SELECT *FROM ex_question_r_knowledgeWHERE 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_knowledgeWHERE 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)
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适合
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 )
执行时间是：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模糊全匹配也将致使全表扫描
案例阐发
EXPLAIN SELECT * FROM ex_subject_point WHERE ex_subject_point.path like "%/11/%"
数据库sql优化总结之百万级数据库优化方案+案例阐发-8.jpg (32.94 KB, 下载次数: 0)
2018-7-15 21:12 上传
若要提高效率，可以考虑全文检索。lucene了解一下。或者其他可以提供全文索引的nosql数据库，好比tt server或MongoDB
还会陆续更新，还有几个小节。
昨天晚上突发奇想，like 模糊全匹配，会致使全表扫描，那模糊后匹配和模糊前匹配也会是全表扫描吗？
今天开电脑，做了下测试。结果如下：
like模糊后匹配，不会致使全表扫描
like模糊前匹配，会致使全表扫描
MY SQL的原理就是这样的，LIKE模糊全匹配会致使索引失效，进行全表扫描；LIKE模糊前匹配也会致使索引失效，进行全表扫描；可是LIKE模糊后匹配，索引就会有效果。
