数据库不同层次的优化策略

 本文参考了网上的几个主流博客，我将他们整理后分成了3个层次，方便入门者理解；

语句级优化：where子句 避免全表扫描

         1.避免在where字句中使用！=操作符，这将导致引擎放弃使用索引，而进行全表扫描；

 

         2.尽量避免在where子句中对字段进行null判断；

                   优化例子如下：     方案1：

                                                        selectid from t where num is null

                                                        可以在num上设置默认值0，然后这样查询：

                                                        selectid from t where num=0                  

                                                        方案2：

                                                        selectid from t where num is null

                        讲索引优化为：

                                                        createindex ss on t(num,1); 

                                                        selectid from t where num=0         

                                                   这种办法是：建立和常量的联合索引；

 

         3.避免在where子句中使用or来连接，这将导致引擎放弃使用索引，而进行全表扫描；

                   优化例子如下：     select id from t where num=10 or num=20

                                                        可以这样查询：

                                                        selectid from t where num=10

                                                        unionall

                                                        selectid from t where num=20

 

       4.避免在where子句中错误使用 like，这将导致引擎放弃使用索引，而进行全表扫描；

                   优化例子如下：   select id from t where name like '%abc%'

                                                        若要提高效率，可以考虑全文检索。

                                                        selectid from t where name like 'abc%' 才用到索引

        

   5. in 和 not in 也要慎用，否则会导致全表扫描，如：

                            selectid from t where num in(1,2,3)

                            对于连续的数值，能用 between 就不要用 in 了：

                            selectid from t where num between 1 and 3

 

         6.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。         如下面语句将进行全表扫描：

                                              select idfrom t where num=@num

                                              可以改为强制查询使用索引：

                                              select idfrom t with(index(索引名)) where num=@num

 

        

 

 

 

         7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

                                               selectid from t where num/2=100

                                               应改为:

                                               selectid from t where num=100*2

 

         8.避免函数操作

 

   9.避免在"="的左边使用表达式

 

         10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

 

         11.很多时候用 exists 代替 in 是一个好的选择：

                   selectnum from a where num in(select num from b)

                   用下面的语句替换：

                   selectnum from a where exists(select 1 from b where num=a.num)

 

表级优化：

 

         1.在经常出现在where和orderby的字段上建立索引

 

         2.建立索引会加速查询语句，但是回减慢插入、更新、删除语句的速度；

 

         3.尽量使用数字型字段，能不用字符串就不用；字符串比较得慢；

 

         4.select子句中不要写用不到的字段

 

         5.尽量少创建临时表，使用变量来代替；

 

         6.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert

 

         7.避免是大事务操作，提高并发；

库级优化：

         1.使用存储过程，它使SQL变得更加灵活和高效。

         2.备份数据库和清除垃圾数据。

         3.适当地清理删除日志

 

（吸收了前人的大量经验感激不尽！）