一、索引是什么?
索引就是一个数据结构,我们把表中的记录用一个适合高效查找的数据结构来表示,目的就是让查询变得更高效。
二、哪些情况需要创建索引?
1.主键自动建立唯一索引。
2.频繁作为查询条件的字段应该创建索引。
3.多表关联查询中,关联字段应该创建索引 ,on两边都要创建索引。
4.查询中排序的字段,应该创建索引 B + tree 有顺序
5.统计或者分组字段,应该创建索引
三、哪些情况不需要创建索引?
1.表记录太少,索引是要有存储的开销。
2.频繁更新的,索引需要维护。
3.查询字段使用频率不高。
四、为什么使用组合索引
由多个字段组成的索引就是组合索引。使用顺序就是创建的顺序。
优势:效率高、省空间、容易形成覆盖索引
使用:遵循最左前缀原则
1.前缀索引
like 常量%使用索引 like %常量 不使用索引
2.最左前缀
从左向右匹配直到遇到范围查询> < between 索引失效
五、索引失效分析
-- 首先我们新建以下表和索引。
-- 用户表
create table tuser(
id int primary key,
loginname varchar(100),
name varchar(100), age int,
sex char(1),
dep int,
address varchar(100)
);
-- 部门表
create table tdep(
id int primary key,
name varchar(100)
);
-- 地址表
create table taddr(
id int primary key,
addr varchar(100) );
-- 创建普通索引
alter table tuser add index idx_dep(dep); -- 创建唯一索引
alter table tuser add unique index idx_loginname(loginname); -- 创建组合索引
alter table tuser add index idx_name_age_sex(name,age,sex); -- 创建全文索引
alter table taddr add fulltext ft_addr(addr);
1.全局匹配我最爱
explain select * from tuser where name='lx' and age=1 and sex='1';
条件与索引一一对应。
2.最佳左前缀法则
组合索引
带头索引不能死,中间索引不能断。如果索引了多个列,要遵守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。正确的例子如1中的图所示。
错误的:
带头索引死:
explain select * from tuser where age=23;
中间索引断:
explain select * from tuser where name='abc' and sex='1';
3.不在索引列上做任何操作(计算、函数、类型转换),会导致索引失效而转向全表扫描。
explain select * from tuser where loginname='zyy';
4.存储引擎不能使用索引中范围条件右边的列
explain select * from tuser where name='abc' and age>20 and sex='1';
5.尽量使用覆盖索引,减少select *
explain select * from tuser ;
explain select name,loginname from tuser ;
explain select name,age,sex from tuser ;
explain select loginname from tuser ;
6.mysql 在使用不等于(!= 或<>)的时候无法使用索引会导致全表扫描
注意:主键索引会使用范围索引,辅助索引会失效
explain select * from tuser where loginname='lx';
explain select * from tuser where loginname!='lx';
7.主键索引字段上不可以判断null
主键索引字段上不可以判断null,索引字段上使用is null判断时,可使用索引。
explain select * from tuser where name is null;
explain select * from tuser where loginname is null;
explain select * from tuser where id is not null;
8.like 以通配符开头,mysql 索引失效会变成全表扫描的操作。
explain select * from tuser where name like 'a%';
explain select * from tuser where name like '%a';
由此可见,like 以通配符结尾相当于范围查找,索引不会失效。与范围条件不同的是:不会导致右边的索引失效。
解决like ‘%字符串%’时,索引失效的解决办法是使用覆盖索引。
explain select name , age,sex from tuser where name like '%a%';
9.字符串不加单引号索引失效。
索引字段是字符串,但查询时不加单引号,会导致索引失效而转向全表扫描。
explain select * from tuser where name=123;
10.少用or,用它来连接时会索引失效。
explain select * from tuser where name='abc' or age=23;