首先我们要熟悉sql的执行顺序(优化动作越靠前越好):
1、from
2、on
3、join
4、where
5、group by
6、聚合函数avgsum...
7、 having
8、select
9、distinct
10、order by
11、limit
on和where的区别:
![f22dee2835691163526426d708247cf2.png](https://i-blog.csdnimg.cn/blog_migrate/da278d1be3c15b4b47c5a349ef327e45.png)
![dfbb8be81dcdaae7acc50d528e95dec5.png](https://i-blog.csdnimg.cn/blog_migrate/00446228245ed26ac81dc0a4938ff6b0.png)
![2f3ea113a1755637696c9af620935eb1.png](https://i-blog.csdnimg.cn/blog_migrate/b61180b949588216797cd76196252d7b.jpeg)
![e001275a061ec29ff02f0b04ff0a281e.png](https://i-blog.csdnimg.cn/blog_migrate/9d44dc2392d6ab8d162292352b94aee5.jpeg)
on条件过滤后,不管条件为真还是假都会返回左表记录
where条件过滤后,返回条件为真数据记录
in与exists:
EXPLAIN SELECT * from tab2 t2 WHERE EXISTS (SELECT 1 FROM tab1 t1 WHERE t2.a = t1.a);
EXPLAIN SELECT * from tab2 t2 WHERE t2.a in (SELECT t1.a FROM tab1 t1);
通过EXPLAIN 两个SQL,执行计划都是一致的(使用的索引一样)。
in适合于外表大而子查询结果集小的情况。
exists适合于外表小而子查询结果集大的情况。
主要与exists的Loop原理有关
PS: in的一个小问题
SELECT * FROM tab1 WHERE d in('as',NULL); -- 只能查到 =as的
SELECT * FROM tab1 WHERE d not in('as',NULL); -- 一个也查不到
为什么呢
首先你可以写一个 SELECT (d=null) a, (d is null) b FROM tab1;
再写一个 SELECT (TRUE or null) a, (FALSE or null) b FROM DUAL;
然后你再想一想 你可以把in或者not in转换为 or 例如:a in(1,2)--> a=1 or a=2
所以你有点思路了吗?
varchar和char
CHAR的长度是固定的,而VARCHAR则处可变长度但他要在总长度上加1字节。
当char(4),varchar(4)
同时存'a' char使用4个字节,varchar使用2个字节
同时存'abcd' char使用4个字节,varchar使用5个字节
-- 我在网上看到的一个两者的区别 c为char类型,d为varchar类型
INSERT INTO 'test'.'tab1'('c', 'd') VALUES ('ha ', 'ha '); -- ha后面有个空格
SELECT CONCAT(c,'+'),CONCAT(d,'+') FROM tab1 WHERE a = 'g';
-- 结果 ha+ ha +
CHAR有固定长度,所以在处理速度上要比VARCHAR快很多,但是相对浪费存储空间,所以对存储不大,但在速度上有要求的可以使用CHAR类型,反之可以用VARCHAR类型来实现。
例如我们在身份证号设计时,可以用定长的CHAR
索引生效
通过EXPLAIN 可以查看索引使用情况。其中type字段最为关键,执行效率const>eq_ref>ref>range>index>all
大家要多尝试,别网上说什么就听什么。实践出真知。
![99e8937900422ff0839bdb683db53da7.png](https://i-blog.csdnimg.cn/blog_migrate/208c84cf6e7ab190c90626520576f158.png)
以下优化方式是使用组合索引测试的,索引为index_bcd(a,b,c,d)
1、 顺序执行索引
where条件同时包含a,b,c,d,索引列全部生效。
只包含a,b,只有索引列a,b生效。
如果为a,c,d,则索引列a生效。
(SQL尽量按照索引的顺序写,不过例如我把条件写成 a,d,c,b,索引列也都会生效,是因为优化器做了优化)
2、is null 与 is not null
is null 没啥问题
is not null 会使整个索引失效
3、like查询
c like '%a' 或 c like '%a%' 时,只有索引列a,b生效,其余失效
d like 'a%' 时,索引列b,c,d,e全部失效
4、!= 或者<>
索引列全部失效
5、or
索引列全部失效
6、in 与 not in
索引列全部失效
7、在等号左边对字段运算、函数
where b/2=10,只有索引列a生效
where SUBSTR(c,1,2) = 'wa' 只有索引列a生效
8、转译
b = '1' 全部生效
c = 2,索引列a,b生效
有空再补充一些其他SQL的可以get的小知识点
例如 sum()over()