mysql length 失效_mysql学习（五）避免索引失效的一些原则

避免索引失效的原则

1.复合索引

a.复合索引，不要跨列使用或无序使用(遵循最佳左前缀)

b.复合索引尽量使用全索引匹配

2.不要在索引上进行任何操作(计算、函数、类型转换)，否则索引失效

select ..where A.x = .. ;  --假设A.x是索引

不要：select ..where A.x*3 = .. ;

explain select * from book where authorid = 1 and typeid = 2 ;--用到了at2个索引

explain select * from book where authorid = 1 and typeid*2 = 2 ;--用到了a1个索引

explain select * from book where authorid*2 = 1 and typeid*2 = 2 ;----用到了0个索引

explain select * from book where authorid*2 = 1 and typeid = 2 ;----用到了0个索引,原因：对于复合索引，如果左边失效，右侧全部失效。(a,b,c)，例如如果 b失效，则b c同时失效。

3.复合索引不能使用不等于(!=  <>)或is null (is not null)，否则自身以及右侧所有全部失效。复合索引中如果有>，则自身和右侧索引全部失效。

explain select * from book where authorid = 1 and typeid =2 ;

-- SQL优化，是一种概率层面的优化。至于是否实际使用了我们的优化，需要通过explain进行推测。

explain select * from book where authorid != 1 and typeid =2 ;

explain select * from book where authorid != 1 and typeid !=2 ;

体验概率情况(< > =)：原因是服务层中有SQL优化器，可能会影响我们的优化。

drop index idx_typeid on book;

drop index idx_authroid on book;

alter table book add index idx_book_at (authorid,typeid);

explain select * from book where authorid = 1 and typeid =2 ;--复合索引at全部使用

explain select * from book where authorid > 1 and typeid =2 ; --复合索引中如果有>，则自身和右侧索引全部失效。

explain select * from book where authorid = 1 and typeid >2 ;--复合索引at全部使用

----明显的概率问题---

explain select * from book where authorid < 1 and typeid =2 ;--复合索引at只用到了1个索引

explain select * from book where authorid < 4 and typeid =2 ;--复合索引全部失效

--我们学习索引优化 ，是一个大部分情况适用的结论，但由于SQL优化器等原因  该结论不是100%正确。

--一般而言， 范围查询(>

4.尽量使用索引覆盖(using index)

索引(a,b,c) select a,b,c from xx..where a=  .. and b =.. ;

5.like尽量以“常量”开头，不要以'%'开头，否则索引失效

select * from xx where name like '%x%' ; --name索引失效

explain select * from teacher  where tname like '%x%'; --tname索引失效

explain select * from teacher  where tname like 'x%';

explain select tname from teacher  where tname like '%x%'; --如果必须使用like '%x%'进行模糊查询，可以使用索引覆盖 挽救一部分。

6.尽量不要使用类型转换(显示、隐式)，否则索引失效

explain select * from teacher where tname = 'abc' ;

explain select * from teacher where tname = 123 ;//程序底层将 123 -> '123'，即进行了类型转换，因此索引失效

7.尽量不要使用or，否则索引失效

explain select * from teacher where tname ='' or tcid >1 ; --将or左侧的tname 失效。

8.一些其他的优化方法

(1)exist和in选择

select ..from table where exist (子查询) ;

select ..from table where 字段 in  (子查询) ;

如果主查询的数据集大，则使用In   ,效率高。

如果子查询的数据集大，则使用exist,效率高。

exist语法： 将主查询的结果，放到子查需结果中进行条件校验(看子查询是否有数据，如果有数据 则校验成功)， 如果符合校验，则保留数据；

select tname from teacher where exists (select * from teacher) ;

--等价于select tname from teacher

select tname from teacher where exists (select * from teacher where tid =9999) ;

in:

select ..from table where tid in  (1,3,5) ;

(2)order by 优化

using filesort 有两种算法：双路排序、单路排序 (根据IO的次数)

MySQL4.1之前 默认使用 双路排序；双路：扫描2次磁盘(1：从磁盘读取排序字段 ,对排序字段进行排序(在buffer中进行的排序)   2：扫描其他字段 ) --IO较消耗性能

MySQL4.1之后 默认使用 单路排序  ： 只读取一次(全部字段)，在buffer中进行排序。但种单路排序 会有一定的隐患 (不一定真的是“单路|1次IO”，有可能多次IO)。原因：如果数据量特别大，则无法 将所有字段的数据 一次性读取完毕，因此 会进行“分片读取、多次读取”。

注意：单路排序 比双路排序 会占用更多的buffer。

单路排序在使用时，如果数据大，可以考虑调大buffer的容量大小：  set max_length_for_sort_data = 1024  单位byte

如果max_length_for_sort_data值太低，则mysql会自动从 单路->双路   (太低：需要排序的列的总大小超过了max_length_for_sort_data定义的字节数)

提高order by查询的策略：

a.选择使用单路、双路 ；调整buffer的容量大小；

b.避免select * ...

c.复合索引 不要跨列使用 ，避免using filesort

d.保证全部的排序字段 排序的一致性(都是升序 或 降序)