索引优化

‘’’
键表SQL
create table staffs(
id int primary key auto_increment,
name varchar(24) not null default “”,
age int not null default 0,
pos varchar(20) not null default “”,
add_time timestamp not null default CURRENT_TIMESTAMP
)charset utf8;

create table user(
id int not null auto_increment primary key,
name varchar(20) default null,
age int default null,
email varchar(20) default null
) engine=innodb default charset=utf8;

插入数据

insert into staffs(name,age,pos,add_time) values(‘z3’,22,‘manager’,now());
insert into staffs(name,age,pos,add_time) values(‘July’,23,‘dev’,now());
insert into staffs(name,age,pos,add_time) values(‘2000’,23,‘dev’,now());
insert into user(name,age,email) values(‘1aa1’,21,‘b@163.com’);
insert into user(name,age,email) values(‘2aa2’,22,‘a@163.com’);
insert into user(name,age,email) values(‘3aa3’,23,‘c@163.com’);
insert into user(name,age,email) values(‘4aa4’,25,‘d@163.com’);

建立复合索引

create index idx_staffs_nameAgePos on staffs(name,age,pos);

sql分析：添加索引

查询内容增加key_len长度增加
–全值匹配我最爱：添加的索引和查询的字段一致，效率高。

最左前缀要遵守
带头大哥不能死,中间兄弟不能断

索引列上少计算,范围之后全失效

like百分写最右

覆盖索引不写*星

不等空值还有or,索引失效要少用

口诀:

全值匹配我最爱,最左前缀要遵守
带头大哥不能死,中间兄弟不能断
索引列上少计算,范围之后全失效
like百分写最右,覆盖索引不写星
不等空值还有or,索引失效要少用
varchar引号不可丢,SQL高级也不难

练习:

假设index(a,b,c)

where语句 索引是否被使用到
where a = 3 Y,使用到a
where a = 3 and b = 5 Y,使用到a,b
where a = 3 and b = 5 and c = 4 Y,使用到a,b,c
where b = 3 或 where b = 3 and c = 4 或 where c = 4 N
where a = 3 and c = 5 使用到a,c没有被使用,b中间断了
where a = 3 and b > 4 and c = 5 使用到了a,b
where a = 3 and b like ‘kk%’ and c = 4 使用到了a,b,c
where a = 3 and b like ‘%kk’ and c = 4 使用到了a
where a = 3 and b like ‘%kk%’ and c = 4 使用到了a