一.创建索引
1.
要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引
2.(1)
在经常需要进行检索的字段上创建索引,比如要按照表字段username进行检索,那么就应该在姓名字段上创建索引,如果经常要按照员工部门和员工岗位级别进行检索,那么就应该在员工部门和员工岗位级别这两个字段上创建索引。
(2)
创建索引给检索带来的性能提升往往是巨大的,因此在发现检索速度过慢的时候应该首先想到的就是创建索引。
(3)
一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。索引并不是越多越好
,索引固然可以提高相应的 select
效率,但同时也降低了 insert 及 update
的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。
二.尽量将多条SQL语句压缩到一句SQL中
每次执行SQL的时候都要建立网络连接、进行权限校验、进行SQL语句的查询优化、发送执行结果,这个过程是非常耗时的,因此应该尽量避免过多的执行SQL语句,能够压缩到一句SQL执行的语句就不要用多条来执行。
三.使用表的别名
当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减 少哪些友列名歧义引起的语法错误。
四.用varchar/nvarchar 代替 char/nchar
可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。 不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。
五.查询select语句优化
1.
任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段
2.
应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,
select id from t where num is null
可以在num上设置默认值0,确保表中num列没有null值,
然后这样查询:
select id from t where num=0
select id from t where num=10 or num=20
可以这样查询
select id from t where num=10
union all
select id from t where num=20
3.
不能前置百分
select id from t where name like ‘%abc%’
这样会导致全表扫描
若要提高效率,可以考虑全文检索。
select id from t where num in(1,2,3)
4.
对于连续的数值,能用 between 就不要用 in 了:
select id from t where num between 1 and 3
5.
如果查询的两个表大小相当,那么用in和exists差别不大。
例如:表A(小表),表B(大表)
select * from A where cc in (select cc from B) 效率低,用到了A表上cc列的索引;
select * from A where exists(select cc from B where cc=A.cc) 效率高,用到了B表上cc列的索引。
相反的
select * from B where cc in (select cc from A) 效率高,用到了B表上cc列的索引;
select * from B where exists(select cc from A where cc=B.cc) 效率低,用到了A表上cc列的索引。
总结:
当B表的数据小于A表的数据集时,用in
当A表的数据小于B表的数据集时,用exists(对比以上两个效率高的看)
六.更新Update语句优化
如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志
七.删除Delete语句优化语句
最高效的删除重复记录方法 ( 因为使用了ROWID,oracle)例子:
delete from emp e where e.rowid > (select min(x.rowid) from emp x where x.emp_no = e.emp_no);
八.插入Insert语句优化
在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。
九.选择合适的字段属性
一般来说,数据库中的表越小,在它上面执行的查询也就越快。因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度舍得尽可能小。
例如:在定义邮政编码这个字段时,如果将其设置为char(255),显然给数据库增加了不必要的空间,甚至使用varchar这种类型也是多余的,因为char(6)就可以很好地完成了任务。同样的如果可以的话,我们应该是用MEDIUMINT而不是BIGINT来定义整形字段。
十.尽量把字段设置为NOT NULL
在可能的情况下,尽量把字段设置为NOT NULL,这样在将来执行查询的时候,数据库不用去比较NULL值。对于某些文本字段来说,例如“省份”或者“性别”,我们可以将他们定义为ENUM(枚举)类型。因为在MySQL中,ENUM类型被当做数值型数据来处理,而数值型数据被处理起来的速度要比文本类型要快得多。这样我们又可以提高数据库的性能。