SQL优化&索引失效场景 & 查询是否走索引判断

1 SQL优化

                        

 1.1 避免使用select * from table where id=1;

  select * 不会走覆盖索引，会有大量回表操作，从而导致查询效率低下

1.2 索引优化

 使用explain判断查询是否走索引：explain select name from tableName where age>30;

 如上图示，type:表示查询类型，表示查询性能，查询效率依次system>constant>eq_ref>ref>range>index>all

　　　　key:索引名字，若为null则表示没有使用索引，如果不是null则表示使用对应名字的索引

索引失效场景

• 违反索引最左原则

对于联合索引，如果不符合最左匹配原则则会引起索引失败.比如基于A, B, C创建了联合索引，以下查询都会走联合索引

　　select A,B,C,D from tableName where A=xxx,B=xxx,C=xxx;

　　select A,B,C,D from tableName where A=xxx,C=xxx;

　　select A,B,C,D from tableName where A=xxx,B=xxx;

　　select A,B,C,D from tableName where A=xxx;

　　但select A,B,C,D from tableName where B=xxx,C=xxx; select A,B,C,D from tableName where B=xxx；都不会走索引

如下图中，在student表中创建了sn + name + age的联合索引，不同的where条件会引起联合索引是否生效        

               

• 索引失效场景 范围查询

　　select A,B,C from tableName where A=xxx and B>xxx and C=xxx ； B是范围查询，索引只能到B

• 索引失效场景2 错误模糊查询

     继续以上例为例，在age创建索引，针对age模糊查询会有如下三种

1. 模糊匹配后面任意字符：like '张%'
2. 模糊匹配前面任意字符：like '%张'
3. 模糊匹配前后任意字符：like '%张%

              由下图可见，只有第一种情况才会执行索引查询。但like '张%'这种情况下并非一定走索引，当查询数量超过了总数据的某个百分比，并且查询字段不止id和索引还包含了其他字段也会走全表查询

　　   

•  索引失效场景3 索引列使用了列运算  

　　　　索引列使用了运算也会引起索引失效

             

• 索引失效场景4  索引列使用了函数

　　索引列使用了任何sql函数也会导致索引失效

          

•  　　索引失效场景5 索引列使用了类型转换

　　　　比如某个索引列类型是String，但在查询时设置用int类型会导致索引失效

           

• 索引失效场景6 索引列使用is not null

         

 3. 避免in值太多

   比如select a, b,c from tableName where a in(1,2,...10000)这样会导致返回数据量太多。一般处理方式是在代码中进行分批次处理；或者可以分批用多线程去查询数据。每批只查500条记录，最后把查询到的数据汇总到一起返回。

4. 避免join太多

根据阿里巴巴开发者手册的规定，join表的数量不应该超过3个。

5 用连接查询代替子查询

mysql中从多张表中查询数据，一般通过连接查询或者子查询实现

• 子查询实例

select * from order
where user_id in (select id from user where status=1)

 

mysql子查询缺点是需要创建临时表，执行完成后需要将临时表删除，增加额外性能开销

• 连接查询

select o.* from order o
inner join user u on o.user_id = u.id
where u.status=1