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性能较差,逻辑复杂。