MySQL优化一
1. 索引优化
索引的分类
分类角度 | 索引名称 |
---|---|
数据结构 | B+树,Hash索引,B-Tree等 |
存储层面 | 聚簇索引,非聚簇索引 |
逻辑层面 | 主键索引,普通索引,复合索引(MySQL 组合索引和联合索引和复合索引都是一个东西),唯一索引,空间索引等 |
回表
假设我们执行一条查询语句
select * from person where ID = 6,因为直接使用的是主键ID查询,所以就会用主键索引,由于主键索引直接关联了整行所有数据,所以,引擎只要执行一次就能查询出结果。
如果执行的sql语句是非主键索引
select * from person where age = 18
上述语句会走age的普通索引,索引先根据age搜索等于18的索引记录,找到ID=10的记录,然后再到主键索引搜索一次,然后拿出需要查询的数据。
从普通索引查出主键索引,然后查询出数据的过程叫做回表。由于回表需要多执行一次查询,这也是为什么主键索引要比普通索引要快的原因,所以,我们要尽量使用主键查询。
覆盖索引
我们通常创建索引的依据都是根据查询的where条件,但是这只是我们通常的做法,我们根据上面的分析可以知道,如果要想查询效率高,第一,使用主键索引,第二,避免回表,也就是尽可能的在索引中就能获取想要的数据。如果一个索引包含了需要查询的字段,那么我们就叫做"覆盖索引"
键表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;
插入数据
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());
建立复合索引
create index idx_staffs_nameAgePos on staffs(name,age,pos);
口诀
全值匹配我最爱,最左前缀要遵守
带头大哥不能死,中间兄弟不能断
索引列上少计算,范围之后全失效
like百分写最右,覆盖索引不写星
不等空值还有or,索引失效要少用
varchar引号不可丢,SQL高级也不难
断层
带头
索引少计算
范围之后全失效
like百分写最右
覆盖索引不写星
不等空值还有or
varchar引号不可丢
练习
假设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 |