sql-知识

一、mysql

1. mysql 查看sql的执行计划：explain + sql

explain SELECT * FROM teacher t WHERE name = '石老师'

重要列	含义
id	执行的先后顺序，越大越先执行
type	索引级别，从优到差为：system>const>eq_ref>ref>range>index>all
key	使用了哪个索引
ref	哪个列使用了索引
rows	查询出了多少列，越少越好，可以看出条件的过滤性如何
extra	其他信息，从优到差为：using index > using filesort （使用文件排序）> using temporary（建立临时表）

ps:其中，type，各级别解释为：

代码	含义
system 、const	只有一条数据时
eq_ref	查询条件是唯一索引
ref	查询条件是普通索引
range	查询条件是范围索引，如 < > in
index	全索引扫描
all	全表扫描

一般来说，查询达到 range级别即可, ref 级别更佳
extra 里 出现这两个要警惕，极大影响性能，多数情况下是order by 和 group by 没有命中索引导致的
using filesort （使用文件排序）， using temporary（建立临时表）

2.mysql index 优化 注意点

1. 范围查询和order by 不可兼得： 复合索引，range查询后的索引会失效

create index idx_teacher_name_age_courseNum on teacher(name,age,courseNum);

如果使用以下查询：
select * from teacher where name = '1' and age = 1 order by courseNum
可以使用索引
 
如果使用以下查询：
select * from teacher where name = '1' and age > 1 order by courseNum
则索引失效！！！

解决方法：建立索引时，跳过需要范围查询的字段，以求order by 命中索引
create index idx_teacher_name_courseNum on teacher(name,courseNum);

3. 小表驱动大表原则：

规则如下：
1.当使用left join时，左表是驱动表，右表是被驱动表 ;
2.当使用right join时，右表时驱动表，左表是驱动表 ;
3.当使用inner join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表 ;

原理类似循环，小循环在外，大循环在内，以求 通过减少表连接创建的次数，加快查询速度 。

例如：left join 时，应该小表在前，大表在后，同时在大表的联结字段上建立索引。

select * from categary c left join book b on c.id on book.cid;
此时 左表（categary）为小表，右表（book）为大表，小表驱动大表，且应在右表（book）的联结字段（cid）上建立索引

索引优化口诀：

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等null值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！

4. like 百分号在最右（左匹配）的情况下，可以使用索引。如果要使用全匹配（左右都有百分号），则需要查询的列被索引覆盖

不可以使用索引：
explain SELECT * FROM teacher t WHERE name like '%石老师%'

可以使用索引：（索引覆盖）
explain SELECT name FROM teacher t WHERE name like '%石老师%'

可以使用索引：(左匹配)
explain SELECT * FROM teacher t WHERE name like '石老师%'

5. order by 后的想要使用组合索引排序的话，order by 也需要遵循从左往右（中间兄弟不能断）的原则

create table test01(id,t1,t2,t3,t4,t5,t6);
create index idx_test01_t1234 on test01(t1,t2,t3,t4);

命中索引：t1,t2. t3索引用于排序
explain select * FROM  test01 WHERE t1='1' and t2='2' order by t3;

命中索引：t1,t2. 由于没有t3,t4的排序会使用 filesort
explain select * FROM  test01 WHERE t1='1' and t2='2' order by t4;

命中索引：t1,t2. t3,t4 索引用于排序
explain select * FROM  test01 WHERE t1='1' and t2='2' order by t3,t4;

命中索引：t1,t2.  由于t4,t3 顺序反了，无法索引，产生 filesort
explain select * FROM  test01 WHERE t1='1' and t2='2' order by t4,t3;

6.关于 in , exist , not in, not exist

这里简单的给自己做个记录：
exist和in的区别：
select * from a where id in （select id from b） ;
select * from a where id exists （select id from b） ;
对于这样的sql查询同一个库，结果是一样的，但是查询速度对于不同情况，
差别较大；
使用in ,sql语句是先执行子查询，也就是先查询b表，在查a表，
而使用exists是先查主表a ,再查字表b; 对于主表数据较多时，我们
使用in速度比exist更快，反之，从表b较大时，使用exist插叙速度更快（都会使用索引）,
如果使用的是not in与not exists，直接使用not exists，因为not in 会进行全表扫描
不走索引，not exists会走索引。

7. order by 使用索引举例

8. 开启慢sql日志

查询开关和log文件的位置

show variables like '%slow_query_log%'

可以看到slow_query_log的值是OFF，也就是mysql默认是不启用慢查询日志的。

这里还有个long_query_time，默认是10秒，也就是超过了10秒即为慢查询。

开启：

打开
set global slow_query_log='ON';
定义慢sql时间为5秒
set global long_query_time= 5;

ps：这种设置方式是临时的，永久设置需要改配置文件