MySQL索引

目录

[TOC]

1 什么是索引？

1.1 官方定义

帮助MYSQL提高查询效率的数据结构(B+树）

1.2 优点&作用

大大加快数据查询速度

1.3 缺点

• 索引需要占据磁盘空间、数据库资源
• 对数据进行增删改的时候，同时也需要维护索引，速度会受到影响

2 索引的分类（重点）

InnoDB引擎支持的索引有以下几种：

• 主键索引：设定为主键后数据库会自动建立索引（如果这表没有主键，那么Innodb为默认建立一个聚簇索引）
• 普通索引：即一个索引只包含单个列，一个表可以有多个普通索引（也称为 单列索引、单值索引）
• 唯一索引：索引列的值必须唯一。允许有且只有一个NULL值
• 复合索引：即一个索引包含多个列（也称为 联合索引、组合索引）

Full Text全文索引 （MySQL5.7版本之前 只能用于 MyISAM引擎）

全文索引类型为FULL TEXT，再定义索引的列上支持值的全文查找，允许在这些索引中插入重复值和空值。全文索引可以再Char、Varchar、text、类型列上创建。【使用概率不大】

3 索引的基本操作

3.1 创建主键索引

-- 先创建个user表，id设为主键。供我们练习使用
CREATE TABLE t_user(id VARCHAR(20) PRIMARY KEY,name VARCHAR(20));

-- 查看索引
show index from t_user

最后我们发现设为主键后，mysql会自动帮我们把它设为主键索引。虽然是常识了..截图一下

3.2 创建普通索引

-- 给name添加索引
create index name_idx on t_user(name) 

语法结构是：
create index 索引名（随便起） on 表名（列名）

再次查看索引 show index from t_user

或者也可以随着创建表的时候创建索引，效果是一样的

--又创建了user2表
CREATE TABLE t_user2 (
	id VARCHAR (20) PRIMARY KEY,
	NAME VARCHAR (20),
	age VARCHAR (20),
	KEY (age) --为age字段创建普通索引，默认索引名就是列名
)

3.3 创建唯一索引

-- 给name添加唯一索引
create unique index name_idx on t_user2(name) 

语法结构是：
create unique index 索引名（随便起） on 表名（列名）

或者也可以随着创建表的时候创建索引，效果是一样的

CREATE TABLE t_user3 (
	id VARCHAR (20) PRIMARY KEY,
	NAME VARCHAR (20),
	age VARCHAR (20),
	unique (age) -- 唯一索引 age列
)

发现在 show index 的结果中和普通索引没看出什么区别

3.4 创建复合索引

-- 给name age添加复合索引
create index name_age on t_user5(name,age) 

语法结构是：（和创建普通索引很像，列是多个就可以了）
create index 索引名（随便起） on 表名（列名1，列名2，列名3...)  

或者也可以随着创建表的时候创建索引，效果是一样的

CREATE TABLE t_user4(
	id VARCHAR (20) PRIMARY KEY,
	name VARCHAR (20),
	age VARCHAR (20),
	key (name,age) -- 组合索引
)

复合索引的结果是这样显示的

最左匹配原则

也称最左前缀匹配原则，都一样

指的是在创建的时候，比如创建了该复合索引

-- 给 name age hobby 添加复合索引
create index name_age on t_user5(name,age,hobby) 

那么以下语句在查询时会用到该复合索引吗 ？

-- 正常查询，可以走索引
select ... where name = 'xx' ,age = 'xx', hobby = 'xx' 

-- 调换一下顺序呢？ 也可以 优化器会自动调整顺序
select ... where hobby = 'xx' ,name = 'xx' , age = 'xx' 

-- 少了一个 hobby 或者 hobby和age 呢？ 也可以走这个复合索引
select ... where name = 'xx' ,age = 'xx'
select ... where name = 'xx'

这种情况呢？

-- 少了一个 name 呢？ 不可以
select ... where age = 'xx', hobby = 'xx'

-- 少了一个 name和age 呢？ 也不可以
select ... where  hobby = 'xx'

但是这两个sql再执行计划里type为index，近似于全表扫描

总结

在创建(name,age,hobby) 类似的复合索引时，需要符合最左原则：

也就是说

• name
• name,age
• name,age,hobby

最左优先，以最左边的为起点任何连续的索引都能匹配上。
但是, 同时遇到范围查询(>、<、or、between、like)就会停止匹配。

再给出几个参考链接：

• Mysql最左匹配原则
• MySQL最左匹配原则，道儿上兄弟都得知道的原则
• mysql中explain的type的解释

4 聚簇索引、非聚簇索引

上面说了索引的分类，那么其实索引也可以分为这两大种

聚簇索引

将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

（或者简单说：聚簇索引的叶节点就是数据节点。）

因为聚集索引决定了表的存储时的排序，一个表只能有一个物理存储排序,所以只能有一个聚集索引

非聚簇索引

将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

（或者简单说：非聚簇索引的叶节点仍然是索引节点）

注意：

在Innodb中，在聚簇索引之上创建的索引称为辅助索引，非聚簇索引都是辅助索引（复合索引、唯一索引、组合索引）。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找，这就是我们常说的回表。

1.InnoDB中

• InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用“where id=14”这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
• 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引8+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最姿到达叶子节点即可获取整行数据。(重点在于通过其他键需要建立辅助索引)
• 聚簇索引默认是主键，如果表中没有定义主键，InnoDB会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键(类似oracle中的Rowld)来作为聚簇索引。如果已经设置了主键为聚簇素引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇素引，最后恢复设置主键即可。

2 MYISAM
MyISAM使用的是非聚簇索引，非聚簇索引的两裸B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。