SQL优化

约定不等于承诺〃 2021-11-04 22:20 400阅读 0赞

**数据库优化的层次**

*  SQL与索引
 *  存储引擎与表结构
 *  数据库架构/缓存
 *  MySQL/Oracle配置
 *  硬件与操作系统

**引起全表扫描和低效率的SQL**  
1、**select id from t where id** **is null**;/**select id from t where id** **is not null**;  
    分析：where子句进行null值判断，导致引擎放弃使用索引而进行全表扫描  
2、**select id from t where num** **!=** **100**;/**select id from t where num** **<>** **100**;  
    分析：where子句中使用了!或者<>操作符，导致引擎放弃索引而进行全表扫描  
    优化：select id from t where num>100 or num<100;(num字段上有索引)  
3、**select id from t where id='1'****or** **name='admin'**;  
    分析：where子句中使用or来连接条件，如果其中一个字段没有索引，将会导致引擎放弃使用索引而进行全表扫描。可以使用union或union all代替  
    优化：select id from t where id='1' union all select id from t where name='admin'  
4、**select id from t where name like** '**%abc%**';  
    分析：左模糊匹配将会导致全表扫描,而右模糊匹配则不会   
5、**select id from t where** **substring(name,1,3)** **= 'abc'**;/**select id from t where** **datediff(day,createdate,'2005-11-30') = 0**;  
    分析：where子句中对字段进行函数操作将导致引擎放弃使用索引而进行全表扫描,尽量避免在“=”左边使用函数  
    优化：select id from t where name like 'abc%';/select id from t where createdate>='2005-11-30' and createdate <'2005-12-1'  
6、**select count(\*) from t**;  
    分析：不带任何条件的count函数会引起全表扫描，并且没有任何业务意义，一定杜绝  
    优化：select count(id) from t;  
7、**select id, name from a where id** **in (select useid from b)**;  
    分析：in查询效率很低，因为需要对子查询的表进行全表搜索，这时可以考虑使用外连接或exists  
    优化：select id, name from a where exists (select userid from b where b.userid=a.id)  
8、**select id from t where** **num/2** **= 100**;  
    分析：where子句中对字段使用表达式操作将会导致引擎放弃使用索引而全表扫描  
    优化 select id from t where num = 200;

**建议优化：**

**1、对于查询的优化，尽量避免全表扫描，应首先考虑在where及order by涉及的列上建立索引；  
2、对于多张大数据量的表进行join, 尽量先分页后join；join时根据业务需求，能使用inner join尽量不使用left join  
3、尽量使用varchar/nvarchar代替char/nchar  
4、查询时，尽量不要使用“\*”，尽可能的使用具体的字段，数据库解析“\*”是比较耗时耗力的  
5、尽量避免多表连接，不必要的连接一定要去掉  
6、尽量避免在值存在大量重复的列上建立索引(比如“性别”列)，这样的索引对查询的效率有多大的提高  
7、对于连续数值，尽量用between and 代替 in;**

**数据库SQL执行顺序（执行顺序从1-11）**

(8)    **select **  
(9)   ** distinct 需要查询出来的字段列表(如：\*)**  
(1)   ** from tableA a**  
(3)    **left/inner/right join tableB b**  
(2)   ** ****on a.XX = b.YY**  
(4)    **where 条件(如：id='123')**  
(5)    **group by 需要分组的字段列表(如：name,type)**  
(6)    **with CR (as) 子查询部分**  
(7)    **having 条件**  
(10)    **order by 需要排序的字段列表**  
(11)    **limit 需要查询的行数条件(如：num<20)**

*注：本文是作者参考一些博客和文档，结合自己实际的经验写作的，若有侵权，请联系作者。  
    由于作者水平有限，写作之中若有纰漏，还望指出。欢迎交流*