sql语句优化方案

1. 为查询缓存优化你的查询

    NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。

    所以，你所需要的就是用一个变量来代替MySQL的函数，从而 开启缓存。

    // 查询缓存不开启
    $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
     // 开启查询缓存
    $today = date("Y-m-d");
    $r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

2. 当只要一行数据时使用 LIMIT 1
    // 没有效率的：
    $r = mysql_query("SELECT * FROM user WHERE country = 'China'");
     if (mysql_num_rows($r) > 0) {  
     }
     // 有效率的：
    $r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");
     if (mysql_num_rows($r) > 0) {
     }

3. 为搜索字段建索引（很多方式的sql会导致sql语句不走索引，
    例如：
        1、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

         2、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

         3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，

        　　如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0

         4、尽量避免在 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    UNION去重且排序 UNION ALL不去重不排序

         5、下面的查询也将导致全表扫描：(不能前置百分号)select id from t where name like ‘%c%’若要提高效率，可以考虑全文检索。

         6、in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3

         7、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；

        　　它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。

       　　 如下面语句将进行全表扫描：select id from t where num=@num可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num

         8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:select id from t where num=100*2

         9、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)=’abc’–name以abc开头的id

        　　select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′

        　　生成的id应改为:select id from t where name like ‘abc%’select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

         10、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

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

         12、不要写一些没有意义的查询，如需要生成一个空表结构：select col1,col2 into #t from t where 1=0这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：create table #t(…)

         13、很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)

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

         15、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。

        　　一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

         16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，

       　　 一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

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

         18、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。）

4. 千万不要 ORDER BY RAND()

5. 使用 ENUM 而不是 VARCHAR

    ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。

    如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。

6. 尽可能的使用 NOT NULL（不要使用NULL）

    除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。
    
7. 把IP地址存成 UNSIGNED INT

    很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放，只需要4个字节，并且你可以有定长的字段。

    而且，这会为你带来查询上的优势，尤其是当 你需要使用这样的WHERE条件：IP between ip1 and ip2。

    我们必需要使用UNSIGNED INT，因为 IP地址会使用整个32位的无符号整形。

8 固定长度的表会更快

    如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。

    例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，

    这样，MySQL 引擎会用另一种方法来处理。
    
9. 拆分大的 DELETE 或 INSERT 语句
    while (1) {
        //每次只做1000条
        mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
        if (mysql_affected_rows() == 0) {
            // 没得可删了，退出！
            break;
        }
        // 每次都要休息一会儿
        usleep(50000);
     }

10. WHERE子句中的连接顺序

    ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理, 当在WHERE子句中有多个表联接时，WHERE子句中排在最后的表应当是返回行数可能最少的表，

    有过滤条件的子句应放在WHERE子句中的最后。如：设从emp表查到的数据比较少或该表的过滤条件比较确定，能大大缩小查询范围，则将最具有选择性部分放在WHERE子句中的最后：

    select * from emp e,dept d where d.deptno >10 and e.deptno =30 ;

    如果dept表返回的记录数较多的话，上面的查询语句会比下面的查询语句响应快得多。

    select * from emp e,dept d where e.deptno =30 and d.deptno >10 ;

11. 用EXPLAIN PLAN 分析SQL语句

12.使用批量插入节省交互 （当如如果使用存储过程来处理批量的sql 各种逻辑是更好的选择）

　　原来语句：insert into admin(admin_name,admin_password) values (‘test1′,'pass1′);

　　insert into admin(admin_name,admin_password) values (‘test2′,'pass2′);

　　insert into admin(admin_name,admin_password) values (‘test3′,'pass3′)

　　优化为： insert into admin(admin_name,admin_password) values(‘test1′,'pass1′),(‘test2′,'pass2′),(‘test3′,'pass3′)

 

 13.limit 的基数比较大时使用between

　　原来语句：select * from admin order by admin_id limit 100000,10

　　优化为：  select * from admin where admin_id between 100000 admin 100010 order by admin_id