【MySQL 一 数据库基础】深入解析 MySQL 的索引(3)

索引

索引操作

自动创建

• 当我们为一张表加主键约束(Primary key)，外键约束(Foreign Key)，唯一约束(Unique)时，MySQL会为对应的的列自动创建一个索引；
• 如果表不指定任何约束时，MySQL会自动为每一列生成一个索引并用ROW_ID 进行标识，这个标识我们无法使用，是数据库内部生成的标识；
• 建议为每一张表都定义一个主键，如果没有明确可以使用主键的列，我们可以在表多定义一个 bigint 类型的字段，然后使用自增主键来约束这个字段。

手动创建

(1) 主键索引

# 方式一，创建表时创建主键

create table t_test_pk (
	id bigint primary key auto_increment,
	name varchar(20)
);

# 方式二，创建表时单独指定主键列

create table t_test_pk1 (
	id bigint auto_increment,
	name varchar(20),
	primary key (id)
);

# 方式三，修改表中的列为主键索引

create table t_test_pk2(
	id bigint,
	name varchar(20)
);

# 修改表结构，为 id 列添加主键约束

alter table t_test_pk2 add primary key (id);

# 修改表结构，为 id 列修改为 bigint 类型且自增

alter table t_test_pk2 modify id bigint auto_increment;

(2) 唯一索引

# 方式一，创建表时创建唯一键

create table t_test_uk (
	id bigint primary key auto_increment,
	name varchar(20) unique
);

# 方式二，创建表时单独指定唯一列

create table t_test_uk1 (
	id bigint primary key auto_increment,
	name varchar(20),
	unique (name)
);

# 方式三，修改表中的列为唯一索引

create table t_test_uk2 (
	id bigint primary key auto_increment,
	name varchar(20)
);
alter table t_test_uk2 add unique (name);

(3) 普通索引

# 方式一，创建表时指定索引列

create table t_test_index (
	id bigint primary key auto_increment,
	name varchar(20) unique,
	sno varchar(10),
	index(sno)
);

# 方式二，修改表中的列为普通索引

create table t_test_index1 (
	id bigint primary key auto_increment,
	name varchar(20),
	sno varchar(10)
);
alter table t_test_index1 add index (sno);

# 方式三，单独创建索引并指定索引名(必须要指定名字)，index_name 推荐使用表名+列名

create table t_test_index2(
	id bigint primary key auto_increment,
	name varchar(20),
	sno varchar(10)
);
create index index_name on t_test_index2(sno);

创建复合索引

# 方式一，创建表时指定索引列

create table t_test_index4(
	id bigint primary key auto_increment,
	name varchar(20),
	sno varchar(10),
	class_id bigint,
	index (sno,class_id)
);

# 方式二，修改表中的列为复合索引

create table t_test_index5(
	id bigint primary key auto_increment,
	name varchar(20),
	sno varchar(10),
	class_id bigint
);
alter table t_test_index5 add index (sno,class_id);

# 方式三，单独创建索引并指定索引名

create table t_test_index6 (
	id bigint primary key auto_increment,
	name varchar(20),
	sno varchar(10),
	class_id bigint
);
create index index_name on t_test_index6 (sno,class_id);

查看索引

# 方式一

show keys from table_name ;

# 方式二

show index from table_name ;

# 方式三，简要信息:desc 表名;

desc table_name ;

删除索引

主键索引

# 语法
alter table 表名 drop primary key;

# 示例，删除t_test_index6表中的主键

alter table t_test_index6 drop primary key;   # 删除主键不用指定列，因为一张表只有一个主键

# 如查提示由于自增列的错误，先删除自增属性，把自增列修改为非自增，再删除主键

alter table t_test_index6 modify id bigint;

alter table t_test_index6 drop primary key;

show keys from t_test_index6 ;  # 查看结果

其他索引

#语法

alter table 表名 drop index 索引名;

# 示例，删除t_test_index6表中名为index_name的索引

alter table t_test_index6 drop index index_name;

show keys from t_test_index6 ;

创建索引的注意事项

• 索引应该创建在高频查询的列上

• 索引需要占用额外的存储空间

• 对表进行插入、更新和删除操作时，同时也会修索引，可能会影响性能

• 创建过多或不合理的索引会导致性能下降，需要谨慎选择和规划索引

怎么查看自己写的SQL走没走索引

可以查看执行计划

# 构造测试表

create table student_index(
	student_id bigint primary key auto_increment ,
	sn varchar(6) unique ,
	name varchar(50) not null,
	mail varchar(50) ,
	class_id bigint ,
	index(class_id) 
);

insert into student_index values(1,1,1,1,1);

insert into student_index values(2,2,2,2,2);

# 创建 sn 和 name 的复合索引 

create index idx_student_sn_name on student_index(sn, name);

show index from student_index;

不加条件，查询所有

# 接下来，我们要判断下面这条 SQL 语句走不走索引

select* from student_index;

explain select* from student_index;

使用主键查询

# 加上查询条件

select* from student_index where student_id = 1;

explain select* from student_index where student_id = 1 ;

子查询中使用索引

select * from student_index where 
	student_id = (select student_id from student_index where student_id = 1);
	
explain select * from student_index where 
	student_id = (select student_id from student_index where student_id = 1);

使用普通索引

select * from student_index where sn = '1' ;

explain select * from student_index where sn = '1' ;  # sn 被 unique 约束

使用复合索引

# 删除 sn 列的单列索引 sn ，来演示复合索引 

alter table student_index drop index sn ;

select * from student_index where sn = '1' and name = '1';

explain select * from student_index where sn = '1' and name = '1';

select sn , name from student_index where sn = '1' and name = '1';

explain select sn , name from student_index where sn = '1' and name = '1';

如果条件包含了复合索引中的所有列，那么都会走索引，条件中的先后顺序不影响结果

explain select sn , name from student_index where  name = '1' and sn = '1';

我们创建的复合索引是 sn 在前，name 在后的；如果在查询中，先查 name ，再查 sn ，就类似于先查韵母，再查声母；
这样虽然是不合理的，但是开发数据库的大佬为了保证健壮性，只要查询条件中，使用了索引包含的所有列，就会走索引，和顺序没有关系；

以 sn 为条件查询 name，这样的查询方法是合理的，走索引树查询（索引覆盖）

select name from student_index where sn = '1' ;

explain select name from student_index where sn = '1' ;

以 name 为条件查询 sn，这样的查询方法是不合理的，索引失去了意义，可能就会走全表查询

select sn from student_index where name = '1' ;

explain select sn from student_index where name = '1' ;