几条关于数据库SQL优化总结

1. 善用EXPLAIN查询SQL执行计划

拿MySQL进行简单示例
explain select cve_id, jira_id from cve_display_cve_kernel where jira_id != ''
不使用索引执行

使用索引后执行

type列: 连接类型。 一个好的SQL要达到range级别以上，杜绝出现all级别。
key列: 使用到的索引名。NULL为没有选择索引，可以采取强制索引方式。
key_len: 索引长度。
rows: 扫描行数。该值是个预估值。
extra: 详细说明。

2. 尽量避免全表扫描, 首先考虑在where或order by字段上创建索引

3. SELECT语句务必指明字段名
select * 会导致很多不必要的消耗(CPU，I/O，内存，网络传输); 还会导致绑定问题，当运行select * 后，试想两张表中有名称相同的列，谁TM知道要用哪一列？本人就曾在项目开发中被此问题困扰许久…这可能会导致数据处理中的功能点崩溃。

4. 尽量避免在where字句中对null值进行判断，否则会导致搜索引擎放弃使用索引而进行全表扫描, 如:
select id from student where name is null
对于值为null的字段尽量使用default=0或default=" "来填充。

5. 尽量避免在where字句中使用or， 如果条件中有些字段有索引，另外的字段没有索引，会导致搜索引擎放弃使用索引而进行全表扫描， 如:
select cve_id from cve_display_cve_kernel where if_has_issue = 'Unknown' or kernel_name_id = 'v4.19'

可以使用UNION关键字进行查询

select cve_id from cve_display_cve_kernel where if_has_issue = 'Unknown'
union
select cve_id from cve_display_cve_kernel where kernel_name_id = 'v4.19'

如果允许重复字段的话，可以使用UNION ALL

select cve_id from cve_display_cve_kernel where if_has_issue = 'Unknown'
union all
select cve_id from cve_display_cve_kernel where kernel_name_id = 'v4.19'

当两个结果集没有重复数据的时候，尽量使用UNION ALL，因为UNION需要将结果合并后再进行唯一性过滤操作，涉及到排序，会增加大量的CPU运算。

6. in 和 not in 要慎用，否则有可能会导致全表扫描
in中包含的值不应过多，in走全表扫描也是分情况的，比例大概在全表的25%~35%的样子。当然比例有时也不一定就走全表，比如MySQL有一个索引扫描，比如:
select id from ttt where id in (...)
select * from ttt where id in (...)
前面的肯定是走主键扫描，即使你in了全表的id也是走主键。后面的就是看百分比的情况了。
对于连续的数值，用between比用in要好。
有些情况下，使用exists代替in也是很好的选择， 如:
select id from A where id in (select id from B)
select a.id from A a where exists (select id from where id=a.id)
in 更适合B表比A表数据小的情况，exists更适合B表比A表数据大的情况。

7. 当查询结果只有一条数据时，使用LIMIT 1
这是为了使EXPLAIN中type列达到const类型

8. 不建议使用%前缀模糊查询
例如like “%name”或者like “%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用like “name%”。
select * from ttt where user_name like '%zhangsan%'
普通的索引是无法满足查询需求的。在MySQL中，可以使用全文索引来查询。
创建全文索引:
alter table ttt add fulltext index idx_user_name (user_name)
使用全文索引:
select * from ttt where match(user_name) against ('zhangsan' in boolean mode)

9. 避免在where字句中对字段使用表达式操作
比如:
select name, sex, age from user where age*2=36
上面的语句对age字段进行了运算，会导致引擎放弃使用索引，建议使用下面的语句进行查询:
select name, sex age from user where age=36/2

10. 对于联合索引来说，要遵守最左前缀法则
举列来说索引含有字段id、name、school，可以直接用id字段，也可以id、name这样的顺序，但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。

11. 索引并不是越多越好
索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。

12. 注意范围查询语句
应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。

13. 尽可能的避免更新 CLUSTERED 索引数据列
因为 CLUSTERED 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 CLUSTERED 索引数据列，那么需要考虑是否应将该索引建为 NONCLUSTERED 索引。

14. 使用合理的分页方式以提高分页的效率
select id,name from ttt limit 1024, 20
使用上述SQL语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。优化的方法是可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是1023。SQL可以采用如下的写法：
select id,name from ttt where id> 1023 limit 20