SQL优化小结

说到SQL优化

一、首先想到的肯定是加索引，但加索引也有很多注意点，我就分享一下我自己的一点索引心得，如有不对的地方，还望指正。

1、索引项最好选用一些选择性好的字段，这样查询起来亦比较方便快捷，比如身份信息表，选择性别还是身份证号添加索引的优劣就显而易见了。

2、索引字段上不能使用表达式，不然会影响索引的生效，例如where to_char(tm,’yyyy-mm-dd’)='2015-03-01' 应该改为where tm >= ‘2015-03-01’and tm <‘2015-03-02’
where valu + 1 > 200 -> where valu > 200 - 1

3、只能使用前缀索引，• 比如有索引包含 a、b两个字段，那么条件可以是
a = X and b = X
        a = x
 对于单独的 b = x ，这个索引是无效的，所以上面的2个情况，建立一个复合索引就可以了。

4、对于like操作，like ‘xxx%’是可以使用索引的，但是 like‘%xxx%’, like ‘%XXX’ 是不可以使用索引的。

5、索引虽好，也不能过度使用，过度使用会极大地影响增删改。

二、SQL语句中不要使用数学运算以及函数运算，尽量把这些逻辑交给代码处理

三、不要使用没有条件的SQL、使用负向查询，例如 not in、!=、not like，避免全表扫描

四、SQL关联条件不要使用text类型（text类型不能作为表关联使用、可以作为条件使用）

五、SQL语句中IN包含的值不应过多，200以内

六、不要使用隐式转换。数值类型禁止加引号、字符串类型必须加引号

七、不要使用大事务查询，可以拆分成多个小SQL查询

SQL调优对于每条sql语句都不同，觉得自己写的SQL可能有问题最好explain一下

explain各个字段的含义大体如下：

• ID：序号，表示了执行顺序，执行的时候，是从大到小的顺序执行，如果ID相同，则从上到下依次执行。

• SELECT TYPE：表示查询中每个select子句的类型（简单 OR复杂），有以下一些：
SIMPLE ：查询中不包含子查询或者UNION
PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为primary
SUBQUERY：在SELECT或WHERE列表中包含了子查询。

• Type：表示MySQL在表中找到所需行的方式，又称“访问类型”，常见类型如下，由左至右，由最差到最好：
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index，Full Index Scan，index与ALL区别为index类型只遍历索引树
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询

• Key：显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL

• TIPS：查询中若使用了覆盖索引，则该索引仅出现在key列表中

• key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

• Ref:：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

• Rows:表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

• Extra:包含不适合在其他列中显示但十分重要的额外信息，有以下一些常见信息：

1、Using where：表示MySQL服务器在存储引擎受到记录后进行“后过滤”（Postfilter）,如果查询未能使用索引，Using where的作用只是提醒我们
  MySQL将用where子句来过滤结果集
2、using temporary  ：表 示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
3、Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”

以上仅为个人一些学习的看法，如有不正确之处还望指正。