所有分类
  • 所有分类
  • 未分类

MySQL–聚集索引/辅助索引/回表查询/覆盖索引(原理及优化)

简介

本文介绍如下内容:聚集索引;辅助索引;什么是回表查询,如何优化回表查询;什么是覆盖索引,覆盖索引的应用场景等。

表结构和数据

为了便于展示概念,本处先建好一个用户表(t_user),表建好后是这样的:

语句

DROP DATABASE IF EXISTS demo;
CREATE DATABASE demo DEFAULT CHARACTER SET utf8;
USE demo;

DROP TABLE IF EXISTS t_user;

CREATE TABLE `t_user`
(
  `id`   BIGINT(0) AUTO_INCREMENT,
  `user_name` VARCHAR(64),
  `code` VARCHAR(20),
  `age`  INT,
  PRIMARY KEY (`id`),
  KEY index_user_name (`user_name`),
  KEY index_code (`code`)
) ENGINE = InnoDB;

INSERT INTO `t_user` VALUES (1, 'LiLei', 'aa', 21);
INSERT INTO `t_user` VALUES (2, 'HanMeimei', 'bb', 23);
INSERT INTO `t_user` VALUES (3, 'Lucy', 'cc', 25);
INSERT INTO `t_user` VALUES (4, 'Lili', 'dd', 28);
INSERT INTO `t_user` VALUES (5, 'WeiHua', 'ee', 24);
INSERT INTO `t_user` VALUES (6, 'ZhangWei', 'ff', 30);
INSERT INTO `t_user` VALUES (7, 'Anna', 'gg', 26);
INSERT INTO `t_user` VALUES (8, 'Lisa', 'hh', 21);
INSERT INTO `t_user` VALUES (9, 'ZhangWei', 'ii', 24);
INSERT INTO `t_user` VALUES (10, 'Kate', 'jj', 29);

聚集索引和辅助索引

在介绍回表和覆盖索引之前,需要先介绍聚集索引和辅助索引。

InnoDB有两大类索引:聚集索引(Clustered Index)和辅助索引(Secondary Index)。

聚集索引辅助索引
别名聚簇索引二级索引、普通索引、非聚集索引、非聚簇索引
结构叶子页保存了整个行数据。 所以也将聚集索引的叶子节点称为数据页。叶子节点只存储聚集索引的非叶子节点存储的值(一般是主键ID)。 想拿到行数据,要根据主键去聚集索引取行数据。
数量一张表必须有且只有一个聚集索引。一张表可以有任意个普通索引。(没有也可以)
优点基于主键的查询非常快。 因为直接定位行记录。更新代价比聚集索引要小
缺点1. 更新代价大(可忽略此缺点,因为主键一般不变)
2. 依赖于有序的数据
若需要回表,则速度慢。 (若覆盖索引,则速度快。)

聚集索引

简介

对于本文用户表来说,聚集索引是这样的:

聚集索引的生成规则:

  1. 如果表定义了PK(Primary Key,主键),那么PK就是聚集索引。
  2. 如果表没有定义PK,则第一个NOT NULL UNIQUE的列就是聚集索引。
  3. 否则InnoDB会另外创建一个隐藏的ROWID作为聚集索引。

优点

基于主键的查询非常快。因为直接定位行记录。

此内容查看价格为0.8自学币立即购买,VIP免费
0

评论0

请先

显示验证码
没有账号?注册  忘记密码?

社交账号快速登录