数据库设计及日常使用规范-高效SQL

尽量不使用select *

• 会使用更多的资源(CPU、IO、网络带宽)
• 取出了无用的列，如果表上有过多的列，相当的慢
• 用不了索引覆盖数据

不在索引上做运算

• 无法使用索引，导致全表扫描

例：
        Select col1,col2 From table Where id + 1 = 1234;

        Select col1,col2 from table where date(create_dt) = ‘2015-11-11’;

避免%前缀模糊查询

• 使用不了索引
• 使用全表扫描

例：
        Select col1,col2 from table where name like ‘%xxx%’;
        改为：’xxx%’

相同数据类型的列值进行比较

• 比如字符字段进行等值查询

例：mobile 字段为字符类型

limit分页

• 传统分页

             Select col1,col2 from table limit 0,10

• limit offset,rows

             分页越大，sql查询越耗时

• 推荐

1. select col1 from table where id > 10000 limit 10;
2. select col1 from table where id > (select id from  table limit 100000,1) limit 10;
3. 先根据分页条件取出需要的ID，再根据ID查找出本页需要的数据字段信息。

         select a.col1,a.col2 from table a inner join (select id from table where xxxx order by xxx desc limit 100000,10) b on (a.id = b.id)
        注：经测试，性能大概可以提升20-30 %

使用union all而非union

• 如果无需对结果进行去重，就使用union all
• union 会对记录进行去重，额外开销

少用order by，group by, distinct

• 如果程序没有要求，就不用这些操作。
• 排序是相当浪费资源的

尽量不用子查询in

• 大部分情况下性能低下
• 使用join操作

例：
Select col1,col2 From table1 Where id in (select id from table2 where xxx);
改用：
Select a.col1,a.col2 From table1 a Inner join b on (a.id = b.id) Where xxxx;

避免使用insert xx select **

• 会对select 中出现的表的记录进行锁定

　改成：
             select *** into outfile xxxx;
             再
             load data infile xx into table ;

更新表中记录时

在对表中的记录进行更新时，尽量用到主建列或索引列（行级锁）。如果没有使用索引，会对全表进行锁定（表锁）。影响其它程序的更新。

关于索引

前提是innodb表

1. 如果有自增列做主键，比没有索引的情况，写入速度约提升3.11%。
2. 如果有自增列做主键，并且有其它索引，相对完全没有索引的情况，写入速度约降低27.37%。
3. 加快查询速度。
4. 为什么有时候不使用索引，如果扫描的行数大于表中的30%时，可能会使用到全表扫描。
5. 选择合适的索引。

避免大的SQL

• 一条大的SQL可能堵死数据库。
• 特别是大量的更新的时候，拆成多个小事务。
• 少用多张表join，分解成多个select。
• 避免大SQL,大事务，大批量。
• 尽可能避免大的SQL,无法避免可以分解成多个小SQL。
• 避免返回结果中有太多的行或太多的列。
• 避免分析大量的行，比如排序