sql性能优化

1.通过explain可以知道mysql是如何处理语句的，并分析出查询或是表结构的性能瓶颈，其实就是在干查询优化器的事，通过expalin可以得到：

a. 表的读取顺序

b.表的读取操作的操作类型

c.哪些索引可以使用

d. 哪些索引被实际使用

e.表之间的引用

f.每张表有多少行被优化器查询

 

索引type从优到差：

System→const→eq_ref→ref→ref_or_null→index_merge→unique_subquery→index_subquery→range→index→all.

 

EXTRA:

Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据连接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。

Using temporary：使用了临时表保存中间结果，性能特别差，需要重点优化

Using index：表示相应的 select 操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！如果同时出现 using where，意味着无法直接通过索引查找来查询到符合条件的数据。

Using index condition：MySQL5.6之后新增的ICP，using index condtion就是使用了ICP（索引下推），在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。

 

Select_type: 一共有9中类型,只介绍常用的4种:

SIMPLE: 简单的 select 查询,不使用 union 及子查询

PRIMARY: 最外层的 select 查询

UNION: UNION 中的第二个或随后的 select 查询,不 依赖于外部查询的结果集

DERIVED: 用于 from 子句里有子查询的情况。MySQL 会 递归执行这些子查询, 把结果放在临时表里。

 

 

小表驱动大表，即小的数据集驱动大的数据集：

当B表的数据集比A表小时，用in优化 exist两表执行顺序是先查B表再查A表查询语句：SELECT * FROM tb_dept WHERE id in (SELECT id FROM tb_dept) ;

当A表的数据集比B表小时，用exist优化in ，两表执行顺序是先查A表，再查B表，查询语句：SELECT * FROM A WHERE EXISTS (SELECT id FROM B WHERE A.id = B.ID) ;

尽量使用连接代替子查询，因为使用 join 时，MySQL 不会在内存中创建临时表。

尽量避免在 where 子句中使用 != 或者 <> 操作符，查询引用会放弃索引而进行全表扫描.

避免在索引列上使用 is null 和 is not null。

 

with as 定义

with A as (select * from class)

也就是将重复用到的大批量 的SQL语句，放到with as 中，加一个别名，在后面用到的时候就可以直接用。对于大批量的SQL数据，起到优化的作用。