索引定义
排好序的可以快速查找数据的数据结构(单链表,双链表)
索引的数据结构是二叉树的索引方式,没有索引mysql就会一条条遍历,容易造成数据冗余,加大工作量
二叉树缺点:在极端情况下比如初始数据是2,继续插入会形成链表,容易降低效率; 解决这种情况是使用B-Tree结构,他会平衡起来
优势:降低数据排序成本,降低CPU消耗,提高查询速度 缺点:索引需要占用空间,会降低表的更新速度
索引使用场景:
1.主键自动建立
2.频繁查询作为查询条件的字段应创建索引(where)后面
3.外键关系应建立
4.多字段查询向创建组合索引
5.查询排序的字段
6.查询统计或者分组字段
索引分为:主键索引,唯一索引,单值索引,复合索引
explain关键字可以进行分析查询执行过程 访问类型排序:ALL>index>range>ref>eq_ref>const>system>Null
索引失效
-
最佳左前级法则:如果索引多列,要道循最左前级法则,指的是查询从索引的最左前列开始并且不跳过索引中的列。
alter table students add index idx_sname_age_score(sname,age,score); explain select * from students where sname ="小明" and age = 22 and score =100; explain select * from students where sname ="小明" and age = 22; explain select * from students where sname ="小明"; -- 会导致score索引失效,只使用了sname的索引 explain select * from students where sname ="小明" and score =100; -- 索引失效,全表扫描 explain select * from students where age = 22 and score =100;
-
不在索引列上做任何计算、函数操作,会导致索引失效而转向全表扫描。
explain select * from students where sname ="小明"; -- 索引失效 全表扫描 explain select * from students where left(sname,2) ="小明";
-
存储引擎不能使用索引中范围条件右边的列。
-- 索引只会使用sname,age. explain select * from students where sname ="小明" and age > 22 and score =100;
-
MYSQL 在使用不等于时无法使用索引会导致全表扫描。
-- 索引失效,全表扫描 explain select * from students where sname !="小明";
-
is null 可以使用索引,但是 is not null 无法使用索引。
explain select * from students where sname is null; -- 索引失效,全表扫描 explain select * from students where sname is not null;
-
like 以通配符开头会使索引失效导致全表扫描。
-- 索引失效,全表扫描 explain select * from students where sname like "%明"; -- 使用到了索引 explain select * from students where sname like "明%";
-
字符串不加单引号索引会失效。
-- 索引失效 explain select * from students where sname = 123;
-
使用OR连接时索引失效。
-- 索引失效,全表扫描 explain select * from students where sname ="小明" or age = 22;
假设index(a,b,c)复合索引: 注意 or 索引不会生效,and 会自动调整顺序为最左前列
Where语句 | 索引是否被使用 |
---|---|
where a = 3 | Y,使用到了a |
where a=3 and b =5 | Y,使用到了a,b |
where a = 3 and b = 4 and c = 5 | Y,使用了a,b,c |
where b=3 或者 where b =3 and c =4 或者 where c = 4 | N |
where a = 3 and c = 4 | Y,使用到了a,但是c不可以,b中间断了,key_len 是a的 |
where a = 3 and b > 4 and c = 5 | Y,使用到了a和b,C不能使用,它在范围查询之后,b之后就断了 |
where a = 3 and b like ‘xx%’ and c = 4 | Y,使用到了a,b,c |
where a = 3 and b like ‘%xx’ and c = 4 | Y,只使用到了a |
where a = 3 and b like ‘xx%xx%’ and c = 4 | Y,使用到了 a,b,c |
总结:
-
对于单值素引,尽量选择针对当前查询字段过滤性更好的素引。
-
对于组合索引,当前 where 查询中过滤性更好的字段在索引字段顺序中位置越靠前越好。
-
对于组合索引,尽量选择能够包含在当前查询中 where 子句中更多字段的素引。
-
尽可能通过分析统计信息和调整 query 的与法来达到选择合适索引的目的。
排序优化
- 尽量避免使用Using FileSort 方式排序。
- order by 语句使用索引最左前列或者使用where 子句 与 order by 子句条件组合满足索引最左前列。
- where 子句中如果出现索引范围查询会导致order by 索引失效。
KEY a_b_c(a,b,c)
-- order by 能使用索引最左前缀
ORDER BY a
ORDER BY a,b
ORDER BY a,b,c
ORDER BY a DESC,b DESC,c DESC
-- 如果WHERE使用索引的最左前缀定义为常量,则 order BY 能使用索引
WHERE a = const ORDER BY b,c
WHERE a = const and b = const ORDER BY c
WHERE a = const and b > const ORDER BY b,c
-- 不能使用索引进行排序
ORDER BY a ASC,b,DESC,c DESC -- 排序不一致
WHERE g = const ORDER BY b,c -- 丢失索引a
WHERE a = const ORDER BY c -- 丢失索引b
WHERE a = const ORDER BY a,d -- d 不是索引的一部分
WHERE a in (...) ORDER BY b,c -- 对于排序来说,多个相等条件也是范围查询
关联查询优化
内连接时,MYSQL 会自动把小结果集选为驱动表,所以大表的字段最好加上索引。左连接时,左表会全表扫描,索引右边大表字段最好加上索引,右外连接 同理。
我们最好保证被驱动表上的字段建立索引。
评论区