索引优化规则
1、like语句的前导模糊查询不能使用索引
select * from doc where title like '%XX'; --不能使用索引
select * from doc where title like 'XX%'; --非前导模糊查询,可以使用索引
- 因为页面搜索严禁左模糊或者全模糊,如果需要可以使用搜索引擎来解决。
2、union、in、or 都能够命中索引,建议使用 in
1.union能够命中索引,并且MySQL 耗费的 CPU 最少。
select * from doc where status=1
union all
select * from doc where status=2;
2.in能够命中索引,查询优化耗费的 CPU 比 union all 多,但可以忽略不计,一般情况下建议使用 in。
select * from doc where status in (1, 2);
3.or 新版的 MySQL 能够命中索引,查询优化耗费的 CPU 比 in多,不建议频繁用or。
select * from doc where status = 1 or status = 2
4.补充:有些地方说在where条件中使用or,索引会失效,造成全表扫描,这是个误区:
- ①要求where子句使用的所有字段,都必须建立索引;
- ②如果数据量太少,mysql制定执行计划时发现全表扫描比索引查找更快,所以会不使用索引;
- ③确保mysql版本5.0以上,且查询优化器开启了index_merge_union=on,也就是变量optimizer_switch里存在index_merge_union且为on。
3、负向条件查询不能使用索引
- 负向条件有:!=、<>、not in、not exists、not like 等。
- 例如下面SQL语句:
select * from doc where status != 1 and status != 2;
- 可以优化为 in 查询:
select * from doc where status in (0,3,4);
4、联合索引最左前缀原则
- 如果在(a,b,c)三个字段上建立联合索引,那么他会自动建立 a| (a,b) | (a,b,c)组索引。
- 登录业务需求,SQL语句如下:
select uid, login_time from user where login_name=? andpasswd=?
- 可以建立(login_name, passwd)的联合索引。因为业务上几乎没有passwd 的单条件查询需求,而有很多login_name的单条件查询需求,所以可以建立(login_name, passwd)的联合索引,而不是(passwd, login_name)。
1.建立联合索引的时候,区分度最高的字段在最左边
2.存在非等号和等号混合判断条件时,在建立索引时,把等号条件的列前置。如 where a>? and b=?,那么即使a 的区分度更高,也必须把 b 放在索引的最前列。
3.最左前缀查询时,并不是指SQL语句的where顺序要和联合索引一致。
- 下面的 SQL 语句也可以命中 (login_name, passwd) 这个联合索引:
select uid, login_time from user where passwd=? andlogin_name=?
- 但还是建议 where 后的顺序和联合索引一致,养成好习惯。
4.假如index(a,b,c), where a=3 and b like ‘abc%’ and c=4,a能用,b能用,c不能用。