mysql复习笔记二：索引

一、示例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）作为聚簇索引，只要这个唯一索引满足以下条件：

1. 索引必须是唯一索引（UNIQUE）。
2. 索引的每一列都不允许有NULL值。
3. 表中不存在已定义的主键。

如果这样的唯一索引存在，InnoDB会使用它作为聚簇索引。然而，一旦表中定义了主键，无论主键的定义形式如何（即使是单列的INT类型），InnoDB都将使用主键作为聚簇索引，而任何其他唯一索引都将成为二级索引。