一、示例sql:
CREATE TABLE test_index(
id BIGINT auto_increment COMMENT '主键',
PRIMARY key(id) COMMENT '主键',
user_name VARCHAR(64) not null COMMENT '用户名',
nick_name VARCHAR(64) not null COMMENT '姓名',
INDEX name(user_name,nick_name) COMMENT '用户名称',
age INT(3) not null COMMENT '年龄',
`code` VARCHAR(21) not null COMMENT '唯一代码',
UNIQUE(`code`) COMMENT '唯一代码',
phone VARCHAR(11) COMMENT '联系电话',
create_by BIGINT(64) not null COMMENT '创建者',
create_time datetime not null COMMENT '创建时间'
)COMMENT = '测试索引表',ENGINE = InnoDB;
二、索引介绍:
1、这里我总共建了一个一级索引(聚簇索引)、一个联合索引、一个二级唯一索引(辅助索引)
PRIMARY key(id) COMMENT '主键',
INDEX name(user_name,nick_name) COMMENT '用户名称',
UNIQUE(`code`) COMMENT '唯一代码',
1.1聚簇索引(一级索引):id
作为主键,它的索引结构包含了所有数据行的所有列。数据行本身存储在树形结构的叶节点上,而非仅仅是一个索引条目。查询时,如果使用id
进行查找,可以直接从索引中获取数据,不需要额外的“回表”操作。
1.2二级索引(辅助索引):code
作为唯一键,它的索引结构只包含了code
的值和指向id
的指针。当使用code
进行查询时,InnoDB首先会通过code
的辅助索引找到对应的id
值,然后根据id
回表到聚簇索引中获取完整的数据行。所以这里增加了一个知识点:mysql查询,什么情况下会触发回表查询?
1.3联合索引:联合索引(也称为复合索引或多列索引)是一种索引类型,它允许你基于多个列来对数据进行排序和快速查找。联合索引的效率取决于索引列的选择和顺序。
1.3.1:联合索引列的顺序:联合索引的列顺序非常重要。当查询使用联合索引时,MySQL会根据索引列的顺序从左到右进行匹配。这意味着索引的最左侧列(即第一个列)应该是最常用于过滤或排序的列。
例如,如果你的查询经常使用user_name
和nick_name
进行搜索,但很少用到id
,那么将id
放在索引的最后可能会更有效。
1.3.2联合索引的使用:有联合索引的情况下,where子句后面的条件如若使用上了联合索引的字段,尽量保证联合索引中最左索引字段不采用范围查询,否则可能导致索引不会生效,如图:
**精准条件查询**
图:1.3.2-1
从图:1.3.2-1可看出,在精准查询情况下,索引走的是完整的,从possible_keys,key,ref三个字段可看出,走的是我们上面定义的name索引,然后两个索引字段的查询速度都为const。
**范围查询**
图:1.3.2-2
从图:1.3.2-2可看出,在user_name使用范围查询时,查询走了全表扫描,因为type为ALL,possible_keys,key,ref三个字段都为空。
图:1.3.2-3
从图:1.3.2-3可看出,只对nick_name采用范围查询时,查询只是没走完整的联合索引,因为从图中可看出:type为ref,possible_keys,key,ref三个字段分别为name,name,const。
三、唯一索引变聚簇索引:
如果一个表没有定义主键,InnoDB会尝试使用一个满足条件的唯一索引(UNIQUE INDEX
)作为聚簇索引,只要这个唯一索引满足以下条件:
- 索引必须是唯一索引(
UNIQUE
)。 - 索引的每一列都不允许有
NULL
值。 - 表中不存在已定义的主键。
如果这样的唯一索引存在,InnoDB会使用它作为聚簇索引。然而,一旦表中定义了主键,无论主键的定义形式如何(即使是单列的INT
类型),InnoDB都将使用主键作为聚簇索引,而任何其他唯一索引都将成为二级索引。