说到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中无法利用索引完成的排序操作称为“文件排序”
以上仅为个人一些学习的看法,如有不正确之处还望指正。