sql优化

1.检查索引，关键字建立索引

在SQL语句的WHERE和JOIN部分中用到的所有字段上，都应该加上索引。

2.限制工作数据集的大小

检查那些SELECT语句中用到的表，看看你是否可以应用WHERE子句进行过滤。一个典型的例子是，当表中只有几千行记录时，一个查询能够很好地执行。但随着应用程序的成长，查询慢了下来。解决方案或许非常简单，限制查询来查看当前月的数据即可。

当你的查询语句带有子查询时，注意在子查询的内部语句上使用过滤，而不是在外部语句上。

3.只选择你需要的字段

额外的字段通常会增加返回数据的纹理，从而导致更多的数据被返回到SQL客户端。

4.移除不必要的表

6.SELECT子句中避免使用 ‘ * ‘

  当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法.但这是一个非常低效的方法. 实际上,在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.

7.用Where子句替换HAVING子句

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

8.通过内部函数提高SQL效率

9.使用表的别名(Alias)
   当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

10.用EXISTS替代IN，用NOT EXISTS替代NOT IN

需要注意的是exists 与NOT EXISTS用法与in not in 并不相同

  例如

select TASTID from tablea where  exits（1,2,3）   如果  tablea中的takid存在1,2,3,则会返回所有tablea中的taskid，不存在则返回null

select TASTID from tablea where not exits（1,2,3） 如果  tablea中的takid不存在1,2,3,则会返回所有tablea中的taskid，存在则返回null

而我们的not in 与 in的用法则是

select TASTID from tablea where TASTID in（1,2,3）  查询taskid是1,2,3的数据

select TASTID from tablea where TASTID in（1,2,3）  查询taskid不是1,2,3的数据

11.避免在索引列上使用计算

WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描

举例:
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;
高效:
SELECT …
FROM DEPT
WHERE SAL > 25000/12;

12.避免在索引列上使用NOT
通常，　我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响.

13.用UNION替换OR (适用于索引列)
通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.

14.用IN来替换OR

低效:
SELECT….
FROM LOCATION
WHERE LOC_ID = 10
OR LOC_ID = 20
OR LOC_ID = 30
高效
SELECT…
FROM LOCATION
WHERE LOC_IN IN (10,20,30);

15.避免在索引列上使用IS NULL和IS NOT NULL
避免在索引中使用任何可以为空的列，

16.用UNION-ALL 替换UNION ( 如果有可能的话)
当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序.
如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高.

UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存

union:如果查询出来的结果中有重复记录，那么就去重 ，英文称之为"distinct"
union all:就显示所有的符合条件的记录，重复也保留

17.避免使用耗费资源的操作
带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎
执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序

18.禁止使用不含字段列表的 INSERT 语句

如：
insert into values ('a','b','c');

应使用：
insert into t(c1,c2,c3) values ('a','b','c');

 

19.避免使用子查询，可以把子查询优化为 join 操作

通常子查询在 in 子句中，且子查询中为简单 SQL(不包含 union、group by、order by、limit 从句) 时,才可以把子查询转化为关联查询进行优化。

**子查询性能差的原因：**

子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

由于子查询会产生大量的临时表也没有索引，所以会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。

 

20.大表优化（这个是扩展知识  嘻嘻嘻）

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

1. 限定数据的范围

务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；

 2. 读/写分离

经典的数据库拆分方案，主库负责写，从库负责读；

 3. 垂直分区

 **根据数据库里面数据表的相关性进行拆分。** 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。** 如下图所示，这样来说大家应该就更容易理解了。

- **垂直拆分的优点：** 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
- **垂直拆分的缺点：** 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

 4. 水平分区

**保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。** 

 水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

缺点：水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 **水平拆分最好分库** 。

水平拆分能够 **支持非常大的数据量存储，应用端改造也少**，但 **分片事务难以解决**  ，跨节点Join性能较差，逻辑复杂。