关于SQL优化的建议

2018-03-12 本文已影响177人千淘萬漉

一、建索引

在做性能跟踪分析过程中，经常发现有不少后台程序的性能问题是因为缺少合适索引造成的，有些表甚至一个索引都没有。这种情况往往都是因为在设计表时，没去定义索引，而开发初期，由于表记录很少，索引创建与否，可能对性能没啥影响，开发人员因此也未多加重视。然一旦程序发布到生产环境，随着时间的推移，表记录越来越多，这时缺少索引，对性能的影响便会越来越大了。

1.大量重复的不要建索引

并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

2.索引不是越多越好，增改操作会因重建索引而变慢

索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

3.避免对索引进行以下操作

避免对索引字段进行计算操作
避免在索引字段上使用not，<>，!=
避免在索引列上使用IS NULL和IS NOT NULL
避免在索引列上出现数据类型转换
避免在索引字段上使用函数
避免建立索引的列中使用空值。

二、where之后的优化

对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。就是说索引最好能建在where之后要用到的字段上面，但同时也要注意避免上述索引的问题。所以在此重复强调一遍，主要就是避免在WHERE子句中使用in，not in，or 或者having，还有不要任何计算和函数。

1.应尽量避免在 where 子句中使用 != 或 < > 操作符

否则引擎将放弃使用索引而进行全表扫描。

2.应尽量避免在 where 子句中使用 or 来连接条件

否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20

可以这样查询，将 or 用 union all 来替换：

select id from t where num=10 union all select id from t where num=20

3.慎用 in 和 not in，改用 exists 和 between

否则会导致全表扫描，如：
select id from t where num in(1,2,3)对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3。
很多时候用 exists代替 in 是一个好的选择：select num from a where num in(select num from b)

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

反正不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
如：select id from t where num/2=100应改为:select id from t where num=100*2

三、select 优化

１.任何地方都不要使用 select * from t

不要使用select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

2.推荐使用UNION ALL，尽量避免UNION

UNION 因为会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。一般来说，如果使用UNION ALL能满足要求的话，务必使用UNION ALL。还有一种情况大家可能会忽略掉，就是虽然要求几个子集的并集需要过滤掉重复记录，但由于脚本的特殊性，不可能存在重复记录，这时便应该使用UNION ALL，如xx模块的某个查询程序就曾经存在这种情况，见，由于语句的特殊性，在这个脚本中几个子集的记录绝对不可能重复，故可以改用UNION ALL）

《10条Sql性能优化建议》
《提高mysql千万级大数据SQL查询优化30条经验》

下面再补充一些题外话，Sql除了优化的大命题外，还有一些细小的知识点也需要掌握或者了解，

1.SQL关键字的执行顺序

查询中用到的关键词主要包含六个，并且他们的书写顺序为

select--from--where--group by--having--order by

其中select和from是必须的，其他关键词是可选的，这六个关键词的执行顺序与书写顺序并不是一样的，执行顺序为

from--where--group by--having--select--order by

2.SQL有哪几种索引？

四种索引( 主键索引 / 普通索引 / 全文索引 / 唯一索引 )

2.1主键索引

当一张表，把某个列设为主键的时候，则该列就是主键索引。可以在创建时添加：

create table a(  
id int primary key auto_increment
...
);

也可以在创建表后在添加索引：

alter table table_name add primary key (column name);

2.2普通索引

普通索引一般是在建表后再添加的，

create index 索引名 on table_name(column1,column2);
alter table table_name add index 索引名(column1,column2);

1.3全文索引

首先，全文索引主要针对文本文件，比如文章，标题，全文索引只有MyISAM有效(mysql5.6之后InnoDB也支持了全文索引)

create table c(  
   id int primary key auto_increment ,  
   title varchar(20),  
   content text,  
   fulltext(title,content)  
)engine=myisam charset utf8;

1.4唯一索引

create table d(id int primary key auto_increment , name varchar(32) unique)

d表中name就是唯一索引，唯一索引可以有多个null,不能是重复的内容
相比主键索引，主键字段不能为null，也不能重复。

3.Mysql索引的数据结构（或者问，为什么索引可以优化查询性能）

传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，mysql需要将表的数据从头到尾遍历一遍。在我们添加完索引之后，mysql一般通过BTREE算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历(折半查找大幅查询效率)，找到相应的键从而获取数据

但是注意索引也是有代价的：

创建索引是为产生索引文件的，占用磁盘空间
索引文件是一个二叉树类型的文件，可想而知我们的dml操作同样也会对索引文件进行修改，所以性能会下降

适合索引的地方：在where条经常使用 b: 该字段的内容不是唯一的几个值 c: 字段内容不是频繁变化。，那么不适应索引的字段也是违背了上述的几个原因。

《Mysql常见四种索引的使用》，通俗易懂的入讲解
《MySQL索引背后的数据结构及算法原理》，深入原理的剖析，有时间可以看看。

总的来说，Sql方面必备的技能和知识点总结如下：

手写SQL。
有没有SQL优化经验。
Mysql索引的数据结构。
SQL怎么进行优化。
SQL关键字的执行顺序。
有哪几种索引。
什么时候该（不该）建索引。
Explain包含哪些列。
Explain的Type列有哪几种值。