MySQL操作系列之查询SQL语句优化(十一)

1.Join语句优化：
尽可能减少Join语句中的NestedLoop的循环次数，"永远是小结果集驱动大的结果集"；
保证Join语句中的被驱动表上Join条件字段已经被索引；

2.最佳左前缀法则(最好不要跳过索引查)：
如：tbl_user表中有字段id,name,age,phone,info字段，id为主键，且建立复合索引idx_nameAgePhone索引；
如：alter table tbl_user add index idx_nameAgePhone (name,age,phone);
那么 select * from tbl_user where age=88;索引失效，因为跳过了火车头name。
    select * from tbl_user where name="xx" and age>14 and phone=yyy;导致phone索引失效；
    select * from tbl_user where name="xx" and phone=yyyy；导致phone这个字段索引失效，因为跳过了age字段。
    select * from tbl_user where phone=xxx and  age=88;导致索引失效,因为跳过了火车头name字段。
    select * from tbl_user where name="xx" and phone=yyy and age>14;mysql的sql语句优化器；
必须在查询条件的前列添加：name="xx"，如：select * from tbl_user where name="yyy" and age=88;
最好的是：select * from tbl_user where name="yyy" and age=88 and phone=yyyy;

3.范围条件(range)右边的所有条件全失效。

4.使用<、<=、>、>=等条件操作导致全表扫描失效。

5.一般最好写like查询的条件是"字符串%",而"%字符串%"与"%字符串"这两种形式是会导致索引失效。
如何解决"%字符串%"导致全表扫描的情况？就是用'覆盖索引:建立的索引与查询的字段最好查询个数与顺序上最好完全一致'解决；
如：tbl_user表中有字段id,name,age,email字段，id为主键，且建立复合索引idx_nameAge索引；
那么:select id,name,age from tbl_user where name like '%xxx%';
     select id,name, from tbl_user where name like '%xxx%';
     select id,age from tbl_user where name like '%xxx%';
     select name,age from tbl_user where name like '%xxx%';
     select name from tbl_user where name like '%xxx%';
     select age from tbl_user where name like '%xxx%';
的索引查询都不会失效，因为建立的索引字段包含查询的字段,且这查询字段的个数不超过索引字段个数。
如果select id,name,age,email from tbl_user where name like '%xxx%';
    select * from tbl_user where name like '%xxx%';
查询个数不对，会全表扫描，索引失效，因为查询的字段的个数超过了索引的字段，email没有用到索引字段，从而失效。

6.查询条件varchar类型的字符串不加单引号会导致索引失效。

7.少用or，用它来链接查询的是否也会导致索引失效。

8.索引列上无计算操作，不然也会到索引失效。

9.order by在两种情况下满足Using Index排序：
  ①最佳左前缀法则，使用的复合索引为最左前列；
  ②使用的where子句与order by子句条件组合满足索引最左前列；

10.group by与order by遵循的原则基本一致。除了：能在where限定的条件就不要在having中限定。

11.group by与order by当无法使用索引列需要增加max_length_for_sort_data与sort_buffer_size参数设置。
(因为是单路与双路排序的原因：当数据量大且多单路排序一次完不成时，就会再次执行，最终将结果合并，这样可能
导致，两路以上的排序。过多的路排序导致IO流操作频繁)