【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' ;

---