学做电商网站,网站制作加盟,腾讯云网站备案吗,建筑人才培训网什么是索引 数据库索引是为了实现高效数据查询的一种有序的数据数据结构#xff0c;类似于书的目录#xff0c;通过目录可以快速的定位到想要的数据#xff0c;因为一张表中的数据会有很多#xff0c;如果直接去表中检索数据效率会很低#xff0c;所以需要为表中的数据建立…什么是索引 数据库索引是为了实现高效数据查询的一种有序的数据数据结构类似于书的目录通过目录可以快速的定位到想要的数据因为一张表中的数据会有很多如果直接去表中检索数据效率会很低所以需要为表中的数据建立索引这样就会提高效率。
索引优势
通过索引可以快速定位到数据降低IO次数提升效率排序列添加索引也可以提高提高排序的效率因为索引是有序的。
索引劣势
索引保存也需要占用空间增删改数据时数据发生变化索引也需要做出相应的改变也是需要时间开销的
索引创建原则 什么时候需要创建索引
主键自动建立唯一索引作为查询条件的字段应该创建索引where 后面的语句中包含的字段尽量使用联合索引减少单列索引针对数据量较大且查询比较频繁的表建立索引查询中排序的字段分组中的字段若通过索引去访问将大大提高排序速度 什么时候不需要创建索引
表记录太少例如类型表员工职位表等增删改频率高的表查询条件中没有唯一性差例如性别只有男和女两种值
索引分类 主键索引 创建表时设置哪个列为 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结构如下 通过id查询学生的所有信息这时只需要查询一次即可因为主键索引是聚簇索引查询到id就找到了对应行的数据 SELECT * FROM t WHERE id 1; 通过学号查询学生所有的信息此时需要回表查询因为根据学号找到后没有直接找到对应的其他数据非聚簇索引 SELECT * FROM t WHERE stu_no 1001; 通过学号查询学生的学号此时不需要回表查询因为我们所需要的数据已经在学号的索引树上找到了此时也是聚簇索引没有回表查询操作。 SELECT stu_no FROM t WHERE stu_no 1001; 这样的查询方式用于查看数据库中是否包含这个学号。
