MySQL语句优化常见误区和优化技巧

MySQL语句优化常见误区与优化技巧

 

原文链接：http://www.2cto.com/database/201303/195666.html

1  常见误区

误区1：count(1)和count(primary_key)优于count(*)

误区2：count(*)和count(column)是一样的

count(*)表示整个结果集有多少记录

count(column)表示结果集中有多少个column字段不为空的记录

误区3：select a,b from …比select a,b,c from …可以让数据库访问更少的数据量

大多数关系型数据库都是按照行方式存储，而数据存取操作都是以一个固定大小的IO单元为单位，大多数时候每个IO单元存储了多少行，每行都是存储了该行的所有字段(lob等特殊类型字段除外)。例外情况是我们的这个查询在索引中就可以完成，也就是当只取a，b两个字段的时候不需要回表，而c这个字段不在使用的索引中，需要回表取得数据，二者的IO量会有较大差异(覆盖索引)。

误区4：order by 一定要做排序操作

索引数据时有序的，如果所需要的数据和某个索引顺序一致，而且查询又通过该索引执行，那么数据库一般会省略排序操作。

误区5：执行计划中有filesort就会进行磁盘文件排序

 

2  语句优化技巧

1)尽量少join

2)尽量少排序

减少排序的方法：

l  通过利用索引来排序

l  减少参与排序的记录条数

l  非必要不对数据进行排序

l  ……

3)尽量避免select *

当查询结果仅仅只需要在索引中就能找到的时候，会极大减少IO量的。

4)尽量用join代替子查询

MySQL的子查询执行计划一直存在较大问题

5)尽量少or

6)尽量用union all代替union

union 和union all的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗延迟。

7)尽量早过滤

8)避免类型转换

本处所述的类型转换是指where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换。人为进行此种类型转换，直接导致MySQL无法使用索引。

9)优先优化高并发的SQL，而不是执行频率低某些“大”SQL

10)从全局出发，而不是片面调整

尽可能对每一条运行在数据库中的SQL进行explain

 

原文链接：http://blog.sina.com.cn/s/blog_95ee143401013gzf.html

11)避免使用不兼容的数据类型

尽量减少对数据库的访问次数

尽量减少对表的访问行数，最小化结果集，从而减轻网络负担

在数据窗口使用SQL时，尽量把使用的索引放在选择的首列

12)索引字段上进行运算会使索引失效

尽量避免在where子句中对字段进行函数或表达式操作

SELECT * FROM T1 WHERE F1/2=100应改为

SELECT * FROM T1 WHERE F1=100*2

13)避免使用！=或<>、IS NULL或IS NOT NULL、IN、NOT IN等这样的操作符

14)尽量使用数字型字段

15)合理使用EXISTS、NOT EXISTS子句

1、SELECT SUM(T1,C1) FROM T1 WHERE (SELECT COUNT(*) FROM T2 WHERET2.C2=T1.C2>0)

2、SELECT SUM(T1,C1) FROM T1 WHERE EXISTS(SELECT * FROM T2 WHERET2.C2=T1.C2)

两者产生相同的结果，但是后者的效率显然高于前者，因为后者不会产生大量锁定的表扫描或是索引扫描。如果想校验表里是否存在某条记录，不要用count(*)，可以用EXISTS代替。

16)能够用BETWEEN的就不用IN

17)能够用DISTINCT的就不用GROUP BY

18)尽量不要用SELECT INTO语句，SELECT INTO语句会导致表锁定，阻止其他用户访问该表

19)必要时强制查询优化器使用某个索引

SELECT * FROM T1 WHERE nextprocess=1 AND processidIN (8,32,45)改成

SELECT * FROM T1 (INDEX = IX_ProcessID)WHERE nextprocess =1 AND processid IN (8,32,45)

则查询优化器将会强行利用索引IX_ProcessID执行查询

20)消除对大型表行数据的顺序存取

21)尽量避免在索引过的字符数据中，使用非打头字母搜索。这也使得引擎无法利用索引

SELECT * FROM T1 WHERE NAME LIKE ‘%L%’

SELECT * FROM T1 WHERE SUBSTRING(NAME,2,1)=’L’

SELECT * FROM T1 WHERE NAME LIKE ‘L%’

即使NAME字段建有索引，前两个查询依然无法利用索引完成加快操作，引擎不得不对全表所有数据逐条操作来完成任务，而第三个查询能够使用索引来加快操作，不要习惯性的使用’%L%’这种方式(会导致全表扫描)，如果可以使用’L%’相对来说更好

22)关于update语句的建议

l  尽量不要修改主键字段

l  当修改varchar型字段时，尽量使用相同长度内容的值代替

l  尽量最小化对于含有update触发器的表的update操作

l  避免update将要复制到其他数据库的列

l  避免update建有很多索引的列

l  避免update在where子句条件中的列

23)程序中如果一次性对同一个表插入多条数据时，可以将其拼成一条语句执行效率会更高

insert into student(sid,sname) values (11,’zhangsan’);

insert into student(sid,sname) values (12,’lisi’);

insert into student(sid,sname) values (13,’liqin’);

 

insert into student(sid,sname) values (11,’zhangsan’),(12,’lisi’),(13,’liqin’);

 

24)不要在选择的栏位上放置索引，应该在条件选择的语句上合理的放置索引

SELECT id,title,content,cat_id FROM articleWHERE cat_id=1;

上面这个语句，在id/title/content上放置索引是毫无意义的，对这个语句没有任何优化作用，但是如果在外键cat_id上放置一个索引，作用就相当大。

 

转载请注明出处：MySQL语句优化常见误区与优化技巧