Mysql索引，聚簇索引，非聚簇索引，回表查询

什么是索引

        数据库索引是为了实现高效数据查询的一种有序的数据数据结构，类似于书的目录，通过目录可以快速的定位到想要的数据，因为一张表中的数据会有很多，如果直接去表中检索数据效率会很低，所以需要为表中的数据建立索引，这样就会提高效率。

索引优势

1. 通过索引可以快速定位到数据，降低IO次数，提升效率
2. 排序列添加索引，也可以提高提高排序的效率，因为索引是有序的。

索引劣势

1. 索引保存也需要占用空间
2. 增删改数据时，数据发生变化，索引也需要做出相应的改变，也是需要时间开销的

索引创建原则

• 什么时候需要创建索引

1. 主键自动建立唯一索引
2. 作为查询条件的字段应该创建索引（where 后面的语句中包含的字段）
3. 尽量使用联合索引，减少单列索引
4. 针对数据量较大，且查询比较频繁的表建立索引
5. 查询中排序的字段，分组中的字段，若通过索引去访问将大大提高排序速度

• 什么时候不需要创建索引

1. 表记录太少，例如类型表，员工职位表等
2. 增删改频率高的表
3. 查询条件中没有
4. 唯一性差，例如性别，只有男和女两种值

索引分类

• 主键索引

        创建表时，设置哪个列为 primary key ，主键列默认自动创建索引

# 创建主键索引方式1, 创建表时直接添加主键索引
create table 表名(id int primary key
);# 创建主键索引方式2, 表创建完成后, 修改表添加主键索引
create table 表名(id int
);
alter table 表名 add primary key 表名(id) ;

# 删除主键索引
alter table 表名 drop primary key;

• 唯一索引

        设置某个列数据唯一性，会创建唯一索引

# 创建唯一索引方式1, 创建表时直接添加唯一索引
create table 表名(id int primary key auto_increment, -- 创建表时直接设置主键account varchar(20) unique
);# 创建唯一索引方式2, 表创建完成后, 修改表添加唯一索引
create table 表名(id int,account varchar(20)
);
create unique index index_unique_account on 表名(account);

# 删除索引, 非主键索引
drop index index_unique_account ON 表名;

• 单值索引

        一个索引中，只包含一个列

# 创建索引
create index index_name on 表名(列名);# 删除索引, 非主键索引
drop index index_name ON 表名;

• 组合索引（复合索引）

        一个索引中包含多个列，节省了索引开支

create table t(a int,b int,c int
);# 创建组合索引
create index index_t_a_b on t(a, b);# 删除索引, 非主键索引
drop index index_t_a_b ON t;

在查询时，如果使用组合索中包含的字段引作为查询条件，必须要包含组合索引中的第一个列，如在上述索引 index_t_a_b ，如果在查询时不使用a作为查询条件会导致索引失效。

通过 explain 可以查看查询时是否是由索引

# 索引生效
explain select * from t where a='' and b='';
explain select * from t where b='' and a='';

# 索引生效
explain select * from t where a='' and c='';

# 索引失效
explain select * from t where b='' and c='';

• 前缀索引

        有些列长度比较大，需要给前面置顶的长度的区间添加索引即可。

create index 索引名 on 表名(列名(长度));

• 全文索引

        模糊查询时，即使有索引也可能出现索引失效的情况

CREATE TABLE t(id INT PRIMARY KEY  AUTO_INCREMENT,title VARCHAR(100)
);INSERT INTO t(title)
VALUES
('小明没考上中学'),
('李华没考上大学'),
('我四级没过')CREATE INDEX title_index_t ON t(title);# 索引生效
EXPLAIN SELECT * FROM t WHERE title LIKE '小明%'

# 索引失效
EXPLAIN SELECT * FROM t WHERE title LIKE '%没考%'

# 创建全文索引
CREATE FULLTEXT INDEX title_index_t ON t(title) WITH PARSER ngram;# 索引生效
EXPLAIN SELECT * FROM t WHERE MATCH(title) AGAINST('没考')

聚簇索引和非聚簇索引

区分方式：找到了索引是否就找对应的数据，找到是聚簇索引，没有找到事非聚簇索引

• 聚簇索引

        找到索引就找到了对应的数据，即索引和数据的存储是在一起的

• 非聚簇索引

        索引的存储和数据的存储是分离的，在myisam引擎中，由于索引和数据分别存储在两个不同的文件中，找到了索引，还需要重新查找一次才能找到数据。

        innodb引擎中，像普通的索引也称为二级索引，他们也是非聚簇索引，例如为名字（name）创建索引（主键索引为以及索引），通过名字查找到id后，需要到主键索引树中找到对应的数据也是非聚簇索引。

回表查询

回表查询指的是查询时查询的次数不止一次

例：现在有表t结构如下：

1. 通过id查询学生的所有信息，这时只需要查询一次即可，因为主键索引是聚簇索引，查询到id就找到了对应行的数据

   SELECT * FROM t WHERE id = 1;

2. 通过学号查询学生所有的信息，此时需要回表查询，因为根据学号找到后没有直接找到对应的其他数据（非聚簇索引）

   SELECT * FROM t WHERE stu_no = '1001';

3. 通过学号查询学生的学号，此时不需要回表查询，因为我们所需要的数据已经在学号的索引树上找到了，此时也是聚簇索引，没有回表查询操作。

   SELECT stu_no FROM t WHERE stu_no = '1001';

   这样的查询方式用于查看数据库中是否包含这个学号。