索引
索引操作
自动创建
- 当我们为一张表加
主键约束(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' ;
