SQL优化 笔记

优化思路

1.   首先从业务角度考虑，是否使用数据库；

2.    统计在一个周期内sql的调用比例+慢日志中的记录

3.   优化方式： 索引、优化参数、加硬件、垂直分表、水平分表

Sql优化

执行顺序

         Where字句选择所有合适的行，Group   By用来分组个统计行，Having字句用来剔除多余的分组。

         这样Group   By   个Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group   BY的目的不包括计算，只是分组，那么用Distinct更快

分析查询语句 

使用explain 对select语句进行分析，如

EXPLAIN SELECT * FROM account  WHERE id=10，得到结果：

         Type代表时间复杂度，此处为常量；key为primary代表是主键索引

         All代表全表扫描，key为null代表没有索引

         对于update的分析，可以将update改成select ，from后边不变

分析慢查询

         在运行环境中，对各种查询进行explain分析不太现实，而且你不知道什么时候去分析

         位置： my.cnf的log-slow-queries  得到慢sql位置

         使用mysqldumpslow、 mysqlsla(第三方统计统计)查看慢查询，

Msyqldumpslow

/path/mysqldumpslow -s c -t 10/database/mysql/slow-log

这会输出记录次数最多的10条SQL语句，其中：

-s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；

-t, 是top n的意思，即为返回前面多少条的数据；

-g, 后边可以写一个正则匹配模式，大小写不敏感的；

Mysqlsla

mysqlsla –lt slow /data/var/mysql_slow.log

Mysqlreport

         第三方的mysql的状态报告工具，对showstatus的结果执行一系列后期处理，以可读性好的方式展现。

操作符优化

in or 和 not in

         子句常会使用临时表使索引失效。 

         IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况

推荐方案：

         在业务密集的SQL当中尽量不采用IN操作符，用EXISTS 方案代替； {}为连续值使用betweenand替换；离散值最频繁的放在前边

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

       select  num from a where exist( select * from b whereb.num=a.num)

       select * from table1 where time between time1 and time2

       select a,b,c, from table1 where a not between 数值1 and 数值2

NOTIN操作符

         此操作是强列不推荐使用的，因为它不能应用表的索引。

推荐方案：

         用NOT EXISTS 方案代替

子查询

       尽量少使用子查询。 方法：

       1）使用联合查询，或者是外连接查询，并对链接字段建立索引。

       2）如果不可避免，在子查询中尽量过滤较多的行

null 和 not null

  不会应用索引，如：

         select * from emp where dept_code is not null  (不使用) 
         select * from emp where dept_code > 0  (使用) 

推荐方案：

         修改 用其它相同功能的操作运算代替，如：a is not null 改为 a>0 或a>’’等。

         对该字段设置默认值替代空值，如 a==0

> <和 >= <=

         如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。

推荐方案：

         使用 <=替换< >=替换>

!=

  不会使用索引，而是全表扫描

select account_name from test where amount != 0  (不使用) 
select account_name from test where amount > 0  (使用) 

LIKE

         如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。

推荐方案：

         第一个字符尽量是确定的    

         实在不行考虑全文检索

UNION 和OR

         OR经常会产生临时表。 考虑把or的每个值改成一条一句，该子句中应该包含索引

         UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：

select * from gc_dfys  union  select* from ls_jg_dfys

         这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

推荐方案：

         采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

Order by

       尽量简化或避免对大型表的排序，以下是不好的： 1）order by 中没有索引中定义的列； 2）group by 或order by列的次序和索引中顺序不一致；3）排序的列

       ORDER BY 子句只在两种严格的条件下使用索引：1）ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序。2）ORDER BY中所有的列必须定义为非空。  

       WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列。

 

字段

1. 优先选择数字型字段，而不是字符型（比较方便）

2. 使用varchar 和nvarchar 替代char和nchar（空间小）

3. 尽量减少事务执行时间，多commit

1）越小越好，如年龄使用tinyint ；

2）简单就好,多用整形、字符等基础的数据类型

整形：取决于范围，如int bigInt smallint tinyint mediumint

实数选择：精度， 如float double decimal

字符串：enum/set char/varchar

时间类型： year/date(很少用)； datetime （有时区，8个字节，有缺省值，更有优势） timestamp（无时区，4个字节）

IP怎么存：转化为整形存储， inet_aton ,inet_ntoa

索引

使用原则

l  在经常进行连接，但是没有指定为外键的列上建立索引

l  在频繁进行排序或分组（即进行group   by或order   by操作）的列上建立索引。

l  在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。

l  数据库表更新大量数据后，删除并重建索引可以提高查询速度。

1.  索引控制在5个以内

2.  如果数据被多次查询，将较大的字段放入另一张表中，分两次获取，类似于列表和详情的感觉；

3.  不要在select 结果上直接做运算

4.  建立索引不光要考虑select，还需要考虑update  insert

where子句

   在子句上创建组合索引：（索引包含多列）；使用最频繁、最大减少结果集的列作为索引前导列；

  where子句第一个字段是索引第一个，后边的不要求

  order by 和group by中也可使用

    如索引 key'normal_key' （key1,key2,key3）对以下查询都能应用索引

    select * from keyt wherekey1=1   ...where key1=1 and key2=2   

    select * from key_t order bykey1,key2,key3

函数处理、计算的字段

         进行了显式或隐式的运算的字段不能进行索引，如：

         select * from emp where to_char(hire_date,'yyyymmdd')='20080411' (不使用)  

       elect * from emp where hire_date = to_char('20080411','yyyymmdd') (使用)  

        select   *   from   record  where amount/30 <1000（不使用） 

        select  *   from   record   where amount <1000*30 （使用）

 

         substr(hbs_bh,1,4)=’5400’，优化处理：hbs_bhlike ‘5400%’

         trunc(sk_rq)=trunc(sysdate)， 优化处理：sk_rq>=trunc(sysdate)and sk_rq<trunc(sysdate+1)

         ss_df+20>50，优化处理：ss_df>30

         ‘X’|| hbs_bh>’X5400021452’，优化处理：hbs_bh>’5400021542’

         sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5

索引的权衡

1.      关键是select和更新的比例，在mysqlreport的DMS部分

2.   该列重复值太多

3.   尽可能减少更新clusted index列

聚集索引

       表中只有一个，用于

       1）包含大量非重复值的列。

         2）使用下列运算符返回一个范围值的查询：BETWEEN、>、>=、< 和 <= group by  order by。

         3）返回大型结果集的查询。   如select   date,sum(amount)   from   record  group   by   date 

反范式化设计

         在表中增加冗余信息，违反第三范式，修改时需要考虑冗余信息的一致性：

1.      修改行为比较罕见

2.      影响范围比较小

3.      允许短时间的数据不一致

4.      通过定期的数据整理自动修复不一致的数据

 

使用临时表

把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。例如：   
         SELECT   cust.name，rcvbles.balance，……other  columns   FROM   cust，rcvbles   
WHERE   cust.customer_id   =   rcvlbes.customer_id   AND  rcvblls.balance> 0   AND   cust.postcode> “98000”  ORDER   BY   cust.name   

         如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个临时文件中，并按客户的名字进行排序：   
SELECT   cust.name，rcvbles.balance，……other   columns   FROM   cust，rcvbles  
WHERE   cust.customer_id   =   rcvlbes.customer_id   AND  rcvblls.balance> 0   ORDER   BY   cust.name  INTO   TEMP   cust_with_balance   
         然后以下面的方式在临时表中查询：  
         SELECT   ＊   FROM  cust_with_balance   WHERE   postcode> “98000”  

         注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。

NoSql数据库

         可以用来存储冗余信息

         优点：1.更好的并发性能 2. Key-value格式

配置优化

索引缓存

         将索引缓存到内存中，在mysqlreport的Key部分

         Key_buffer配置 缓存的大小：需要监控通常情况下索引的使用大小，从而进行配置

       Innodb_buffer_pool_size： 配置innoDB数据和索引的内存缓冲池大小

查询缓存

         Mysqlreport的Query Cache部分

         将select结果放在内存中，方便下次使用，配置 query_cache_size  query_cache_typequery_cache_limit

         但是要注意缓存过期策略：更新就会过期。如果是select和update交替就比较惨

表锁定和行锁定

         Mysqlreport的Table Locks 和Line Locks

         锁定策略：读可并发，写必须等待，写锁优先。

         表锁定：适用于大部分查询，少量更新

         行锁定：适用于select和update交替出现

         Mysql为MyISAM提供表锁定，为InnoDB提供行锁定

临时表

         Mysqlreport的Create Temp部分

         将临时表尽量放在内存中，可设置tmp_table_size配置临时表的内存空间大小

线程池

         Mysqlreport的Threads部分

    配置thread_cache_size  线程池大小

常用命令

连接数

         showstatus like '%thread%';

     mysqladmin-uroot --password='password' status like ‘%slow%’ -i 5  //5秒刷新一次

连接详情

         showfull processlist,如果进程过多，就把进程打印下来，然后查看 mysql-e 'show full processlist;' > 111

top20 sql的最佳实践

利用query cache 优化sql

变量不能是动态的、可变的，如 curdate、NOW() andRAND() ；

select 大小写不同也不会启用缓存

// query cache doesNOT work

$r=mysql_query("SELECT username FROM user WHERE signup_date >=CURDATE()");

 

// query cacheworks!

$today=date("Y-m-d");

$r=mysql_query("SELECT username FROM user WHERE signup_date >='$today'");

 

限制个数

判断数据是否存在；或者获取单个实例

// what NOT to do:

$r= mysql_query("SELECT * FROM userWHERE state = 'Alabama'");

if(mysql_num_rows($r) > 0) {

    //...

}

 

 

// much better:

$r= mysql_query("SELECT 1 FROM userWHERE state = 'Alabama' LIMIT 1");

if(mysql_num_rows($r) > 0) {

    //...

}

使用索引和相同的column类型

Join的两个列在各自表中都有索引

相同类型：如都是decimal，而不是一个decimal一个是int；字符串编码也得一样

尽量不适用NULL：

整形default0，非整形default “”

某些情况使用enum代替varchar

对于类似：男/女; active/inactive ;状态等

使用procudure analysis

分析列结构和表中数据，只是给出建议，不一定对

Explain 用于分析sql语句

避免使用 select *:

增加IO

使用 prepared statement

可用性介绍

参考网址

   http://blog.csdn.net/xiaoyi92/article/details/7045201

   http://blog.csdn.net/xiaoyi92/article/details/7046032

   http://www.cnblogs.com/wxj1020/archive/2008/04/27/1173638.html

  http://wenku.baidu.com/view/043eea8a84868762caaed54f.html?re=view