SQL优化建议

一：结构优化

1.联合索引

当我们使用组合索引<a,b>时，会按照联合索引的顺序分别进行排序，所以他的一个好处是对第二个键值已经做了排序。这样就可以在进行类似
select * from buy_log where user_id = 2 order by buy_date desc;
的查询时可以使用联合索引的排序功能避免filesort排序。

 

2.建有索引的字段上尽量不要使用函数进行操作 如：

EXPLAIN SELECT * FROM employees.titles WHERE emp_no - 1='10000';//无法使用索引
EXPLAIN SELECT * FROM employees.titles WHERE emp_no ='10001';//不要使用表达式 可以使用索引

3.索引的创建条件：

第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。至于多少条记录才算多，这个个人有个人的看法，我个人的经验是以2000作为分界线，
记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引。

另一种不建议建索引的情况是索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：

Index Selectivity = Cardinality / #T

显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。

4.前缀索引

有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。
前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。

5.InnoDB搜索引擎时的优化

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

6.单条查询最后添加 LIMIT 1，停止全表扫描。

7.选取合适的字段属性

  • 能使用CHAR（18）不使用VARCHAR（18），对于固定长度的例如身份证18位够了就不要写64
  • 另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
  • 对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能

8.使用连接（JOIN）来代替子查询

执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。

优化方式：
可以使用连接查询（JOIN）代替子查询，连接查询不需要建立临时表，因此其速度比子查询快。

9.使用联合(UNION)来代替手动创建的临时表

MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。
使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。下面的例子就演示了一个使用UNION的查询。
SELECT   name,phone  FROM  client UNION
 
SELECT  name,birthdate  FROM  author  UNION
 
SELECT  name,supplier FROM produc

　　

10.事务

尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。
更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。
设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，
甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。
事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。
BEGIN;
  INSERT   INTO   salesinfo   SET   customerid=14;
  UPDATE   inventory   SET   quantity =11   WHERE   item='book';
COMMIT;

　　

11.在业务密集的SQL当中尽量不采用IN操作符，用EXISTS 方案代替。

1. in()适合B表比A表数据小的情况

2. exists()适合B表比A表数据大的情况

3. 当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用.

select * from A
where id in(select id from B)

可以看出,当B表数据较大时不适合使用in(),因为它会B表数据全部遍历一次.
如:A表有10000条记录,B表有1000000条记录,那么最多有可能遍历10000*1000000次,效率很差.
再如:A表有10000条记录,B表有100条记录,那么最多有可能遍历10000*100次,遍历次数大大减少,效率大大提升.

结论:in()适合B表比A表数据小的情况

select a.* from A a 
where exists(select 1 from B b where a.id=b.id)

以上查询使用了exists语句,exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回false.

当B表比A表数据大时适合使用exists(),因为它没有那么遍历操作,只需要再执行一次查询就行.
如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等.
如:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果.
再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.

结论:exists()适合B表比A表数据大的情况

12.二者都能使用尽量使用where （与having比较）where 先过滤（数据就少了）在分组 

13.一下情况导致不使用索引或索引无效

  1. IS NULL 与 IS NOT NULL；不能用null作索引，只要这些列中有一列含有null，该列就会从索引中排除。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。比如部门编码！=null可以写成编码>0
  2. 有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的（字符串拼接会导致不使用索引）
  3. like模糊查询，“%”不要出现在首位置，会导致索引失效，其他位置可以使用索引
  4. 任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。
  5. 索引只会使用匹配项而不会使用不匹配项，如not (a=100) 应替换成 a<100 and a>100
  6. 选择最有效率的表名顺序(只在基于规则的优化器中有效)： ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表. 
  7. 用TRUNCATE替代DELETE： 
    当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)
  8.  尽量多使用COMMIT： 
    只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少: 
  9. 用Where子句替换HAVING子句： HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.
  10.  使用表的别名(Alias)： 
    当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误. 
  11. 用EXISTS替代IN、用NOT EXISTS替代NOT IN： 
  12.  用索引提高效率： 
  13.  sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行 
  14. 避免在索引列上使用NOT 通常，　 
    我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描. 
  15.  避免在索引列上使用计算
  16. 用UNION替换OR (适用于索引列) 
  17.  用UNION-ALL 替换UNION ( 如果有可能的话)

14.需要当心的WHERE子句: 

某些SELECT 语句中的WHERE子句不使用索引. 这里有一些例子.  在下面的例子里, 
(1) ‘!=’ 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. 
(2) ‘ ¦ ¦’是字符连接函数. 就象其他函数那样, 停用了索引. 
(3) ‘+’是数学函数. 就象其他数学函数那样, 停用了索引. 
(4) 相同的索引列不能互相比较,这将会启用全表扫描.

15.用UNION-ALL 替换UNION ( 如果有可能的话)

union all 会输出重复记录，
union     合并重复记录
当SQL 语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 如果用UNION ALL替代UNION, 这样排序就不是必要了.
效率就会因此得到提高. 需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使用UNION ALL的可行性. UNION 将对结果集合排序,
这个操作会使用到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的. 下面的SQL可以用来查询排序的消耗量

 

posted @ 2019-02-28 14:31  隔壁w王叔叔  阅读(435)  评论(0)    收藏  举报