sql优化

sql语句优化

• 避免全表扫描的字段

字段	替换值	栗子
null	设默认值	select id from t where num is null ==> select id from t where num = 0
!= <>
or	union all	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
in not in	between exists	连续数值：where num in(1,2,3) ==>where num between 1 and 3 where num in (select num from b) ==>where exists (select 1 from b where num = a.num)
like ‘%abc%’	like ‘abc%’	注：不是从开始就模糊查询的可以走索引
subString(name,1,3)=‘abc’	like ‘abc%’	注：不是从开始就模糊查询的可以走索引

• 避免全表扫描，考虑在where、order by 涉及的列上建立索引；
• where子句中对表达式进行了操作，会导致全表扫描：where num/2 =100 …
• where子句“=”左边避免函数、算数或其他表达式运算，否则系统无法正确使用索引；
• 一次性插入大量数据时，可以批量插入，避免一条条插入，严重影响数据库IO；
• 修改、删除也可以这么操作，用批量删除替代一条一条删除，避免阻塞数据库读锁；
• 慎用distinct，尽量用在一两个字段上，单表去重用distinct,多表用group by,group by 要放在order by,limit 前面，不然会报错；

索引

• 不是所有索引都有效：索引中大量数据重复。sql查询可能不会使用索引；
• 对where和order by 中常用的字段建索引；使用索引后要避免！= ，<>，前导模糊查询的使用；
• 索引不是越多越好：可以提高查效率，但同时也会降低insert、update效率，所以对于不常使用的列没必要建立索引；一个表中最多6个索引，太多的话对不常用的列考虑是否有必要建索引。

关于数据库表设计

• 只含数值的字段，尽量使用数字型，字符型会降低查询效率，增加存储开销：引擎在处理查询和连接时会逐个比较字符串中的每一个字符，而对于数字型只需比较一次；尽量使用合适的字段属性；
• 尽量使用varchar代替char，节省存储空间；对查询来说，在一个相对较小的字段内搜索效率要高些；
• char(10)在存储4个字节的数据时，会把后面补充成空格，取的时候用trim去空格；