Sql的一些优化经验

Sql的一些优化经验

1.设计表时

1. 尽可能的使用 varchar/nvarchar 代替 char/nchar，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
2. 定长类型（var）比可变长（varchar）效率高，因为可变长类型在底层存储中容易产生碎片需要维护。所以说，定长类型时间效率快，空间消耗大；变长空间节省，时间效率快，根据时间情况选择。所以用不用定长，需要根据实际情况来定。
3. 不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。
4. 当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减 少哪些友列名歧义引起的语法错误。
5. 在建表时，如果一次性插入数据量很大，那么可以使用 select into代替 create table，避免造成大量 log ，以提高速度；
6. 如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

2.索引字段

不要在建立的索引的数据列上进行下列操作:
◆避免对索引字段进行计算操作
◆避免在索引字段上使用not，<>，!=
◆避免在索引列上使用IS NULL和IS NOT NULL
◆避免在索引列上出现数据类型转换
◆避免在索引字段上使用函数，可能会导致全表扫描。
◆避免建立索引的列中使用空值。

最主要的：

1. 在创建表时，要充分考虑这个表以后查询时可能用到的常用字段，并创建索引，如在 where 及 order by 涉及的列上建立索引
2. 创建索引时，要避免选择字段中的值为重复的创建索引时，要避免选择字段中的值为重复的
   如：sex字段有只有两个类型：男女，如果在这个字段上创建索引，其实是没有意义的。
3. 创建索引时，要避免选择字段中的值出现null的情况
4. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率
   因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
5. 一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
6. 使用索引的好处：查询快
   不好的地方是进行insert、update、delete的时候慢，因为索引占磁盘空间。
7. 多个单列索引在多条件查询时只会生效第一个索引！所以多条件联合查询时最好建联合索引！

如：创建了索引(username,age)，在查询数据的时候：select username , age from user where username = ‘Joe’ and age = 20
这样就直接走了索引

8. 数据量少的字段不需要加索引；因为建索引有一定开销，如果数据量小则没必要建索引（速度反而慢）,联合索引比对每个列分别建索引更有优势
9. 聚集索引，是以主键创建的索引，在叶子节点存储的是表中的数据
10. 非聚集索引，是以非主键创建的索引，在叶子节点存储的是主键和索引列，所以使用非聚集索引查询出数据时，拿到叶子上的主键再去查到想要查找的数据。(拿到主键再查找这个过程叫做回表)，如果用联合索引，而要查询的列都在叶子节点上，这样就不用回表，提高了查询速度。
11. 尽量选择区分度高的列作为索引，区分度的公式是

COUNT(DISTINCT col) / COUNT()。表示字段不重复的比率，比率越大我们扫描的记录数就越少。
–
如果对创建的是联合索引，需要group by 后count来求分子，如下：
select count() from (select count(*) cols from table group by col1, col2) t1

12. 尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可

---

最左前缀原则：

顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上，

13. 指where中包含组合中的第一个字段，如：
    组合索引为IDX(Column_A,Column_B,Column_C)，则在where查询中：
    a)、只有Column_A，会走这个组合索引；
    b)、只有Column_B，不会走这个组合索引；
    c)、Column_A和Column_B，不论这两个顺序怎么排，会走这个组合索引；
    d)、Column_A和Column_D，不论这两个顺序怎么排，会走这个组合索引；
    e)、Column_B和Column_D，不论这两个顺序怎么排，不会走这个组合索引；
    f)、Column_A、Column_B、Column_C和Column_D，不论这三个顺序怎么排，会走这个组合索引；
    g)、Column_A=1、Column_B>2、Column_C=3，只走A索引

总结：最左前缀，其实是指where条件中有组合索引中的第一个！
或者说where条件中的所有字段，在组合索引中分别能对应上，且首先能对应第一个字段，然后能对应上第二个字段…

14. 如果第一个字段是范围查询需要单独建一个索引
15. 在创建联合索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

---

3.索引失效的情况

示例为：组合索引(id, name)

1. 使用like语句查询时失效，索引命中只能是相等的情况，不能是范围匹配
2. 顺序颠倒时候失效，如查询为where name=value and id = value这是顺序颠倒(Sql server好像没有失效)
3. 如果查询时只使用id查询索引不失效，只使用name查询会失效

4.临时表

1. 但是要避免频繁创建和删除临时表，以减少系统表资源的消耗
2. 临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。
3. 但是，对于一次性事件，最好使用导出表。
4. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

5.使用预编译查询

程序中通常是根据用户的输入来动态执行SQL，这时应该尽量使用参数化SQL，
这样不仅可以避免SQL注入漏洞 攻击，最重要数据库会对这些参数化SQL进行预编译，这样第一次执行的时候DBMS会为这个SQL语句进行查询优化 并且执行预编译，这样以后再执行这个SQL的时候就直接使用预编译的结果，这样可以大大提高执行的速度。

6.where字段

1. 避免在索引字段上使用函数，而是在传入的参数上，如：
   where createDate>=to_date(#{date})
2. where中的字段最好都有索引
3. 避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，（ 可以通过设置默认值来取消对null的判断 ）
4. 避免在 where 子句中有索引的字段使用!=或<>操作符，否则将放弃使用索引而进行全表扫描
5. 避免在 where 子句中使用 or 来连接条件

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

6. 尽量使用 exist 和not exist代替 in和not in
7. HAVING是在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前
   刷选记录，所以优先使用where
8. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算
9. 尽可能的让字段顺序与索引顺序相一致
10. 对于连续的数值，能用 between 就不要用 in，如：

select id from t where num in(1,2,3)
--建议使用：
select id from t where num between 1 and 3

exists做为where 条件时，是先对where 前的主查询询进行查询，
然后用主查询的结果一个一个的代入exists的查询进行判断，
如果为真则输出当前这一条主查询的结果，否则不输出

• exist会针对子查询的表使用索引.
• not exist会对主子查询都会使用索引.
• in与子查询一起使用的时候,只能针对主查询使用索引.
• not in则不会使用任何索引. 注意,一直以来认为exists比in效率高的说法是不准确的。
• in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。
• 如果查询的两个表大小相当，那么用in和exists差别不大。
• 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in
• 外大内小用in效率极低,用exists效率很高
• 外小内大时使用in比exists效率更高
• exits适合内小外大的查询，in适合内大外小的查询

例如：表A（小表），表B（大表）
1：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列的索引。
2：相反的 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列的索引。

• 如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；
• 而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

7.like模糊匹配

1. 取消like，用具体的值，因为like会使索引失效，导致全表扫描。
2. 任何地方都不要使用 select * from t
3. 根据like先创建一个临时表，然后通过临时表关联查询。
4. 多级嵌套的查询或者更新，可以拆成多步、或者用临时表
5. 如果必须要用到where colum1 like ‘%value%’，同时colum1 是索引字段时，尽量去掉前缀%，如’value%’

8.传递参数时

不要以字符格式声明数字，要以数字格式声明字符值
如：

SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 7369;
--不要使用：SELECT emp.ename, emp.job FROM emp WHERE emp.empno = ‘7369’

尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

9.分析执行计划

使用方法：explain select…
       执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案，这个方案是由查询优化器自动分析产生的，比如一条SQL语句如果用来从一个 10万条记录的表中查1条记录，那查询优化器会选择“索引查找”方式，如果该表进行了归档，当前只剩下5000条记录了，那查询优化器就会改变方案，采用 “全表扫描”方式。
可见，执行计划并不是固定的，它是“带有相当个性的”。
执行计划是可以被重用的，越简单的SQL语句被重用的可能性越高。

通过Explain可以得到
1、表的读取顺序
2、表的读取操作的操作类型
3、哪些索引可以使用
4、哪些索引被实际使用
5、表之间的引用
6、每张表有多少行被优化器查询
7、需要说的是EXPLAIN只对SELECT查询作解释,INSERT,UPDATE,DELETE不会。

10.绑定变量窥测

事物都存在两面性，绑定变量对大多数OLTP处理是适用的，但是也有例外。比如在where条件中的字段是“倾斜字段”的时候。

“倾斜字段”指该列中的绝大多数的值都是相同的，比如一张人口调查表，其中“民族”这列，90%以上都是汉族。那么如果一个SQL语句要查询30岁的汉族人口有多少，那“民族”这列必然要被放在where条件中。这个时候如果采用绑定变量@nation会存在很大问题。

试想如果@nation传入的第一个值是“汉族”，那整个执行计划必然会选择表扫描。然后，第二个值传入的是“布依族”，按理说“布依族”占的比例可能只有万分之一，应该采用索引查找。但是，由于重用了第一次解析的“汉族”的那个执行计划，那么第二次也将采用表扫描方式。这个问题就是著名的“绑定变量窥测”，建议对于“倾斜字段”不要采用绑定变量。

提高查询的其它方法

1.大表拆小表：纵向拆分

大表拆小表,比如文章表字段有id/autor/title/content,content是一个大字段,可以单独拆到一张表里.而且content用的时候比较少，所以拆到另外一张表里可以增快查询速度。

如：用户表的id/name/nick_name/age/phone是常用信息在一张表,爱好/描述/地址是不常用信息,拆到另外一张表里

2.加冗余字段

不能所有的字段都加成冗余字段,一定是不常变化的字段为冗余字段

3.不创建外键,靠代码和sql控制

安全性要求不是很高，并且数据量很大的时候，可以不需要外键。

4.理论上，尽量使用多表连接（join）查询（避免子查询）

链接