mysql主键索引,非主键索引,覆盖索引,联合索引

·  阅读 436

什么是主键索引,非主键索引

从定义来说,主键索引就是聚簇索引,非主键索引就是二级索引,一般情况都是B+树结构,InnoDb表必然有一个主键索引,如果没有主动设置主键,innodb会自动添加一个字段来充当主键

介绍主键,非主键索引

建立一个User表来说举例说明

CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `author` varchar(20) DEFAULT NULL,
  `published` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_author_name` (`author`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

INSERT book VALUES 
(1,'book1','author1','2018-01-01'),
(2,'book2','author1','2018-01-01'),
(3,'book3','author2','2018-01-01'),
(4,'book4','author2','2019-01-01');
复制代码

主键索引Id的存储结构长啥样

id.png

非主键索引idx_name长啥样

idx_namename.png

联合索引idx_author_name长啥样

2021-09-29_110507.png
由以上可见,主键索引叶子节点存储了所有的字段信息;非主键索引叶子节点存储的是索引字段和主键值。
如果我们要查询如下sql,会用什么索引呢

select * from book where id = 1; //语句1
select * from book where name = 'book1'; 语句2
select id,author,name from book where author = 'author1' //语句3
select * from book where author='author1' //语句4

复制代码

语句1 用到了主键索引,直接一次查出数据
语句2 用到了非主键索引,先从idx_name中找到name为book1的数据,在取出id值,再到主键索引中查找其他数据,这个过程称为回表
语句3 用到了联合索引idx_author_name,由于查询的字段都在联合索引中,所以不需要回表查询,由此得出,不是所有的非主键索引都要回表,这种索引叫做 覆盖索引
语句4 用到了联合索引,但是需要查询 * ,所以也要回表

唯一索引

unique index 是为了防止数据了出现重复而建立的索引,保证数据的唯一性

分类:
后端
标签: