唯一复合索引有顺序要求吗 唯一索引和组合索引

文章目录

• 一、索引的分类
• 二、索引操作

• 0.数据准备
• 1.创建索引
• 2.查看索引
• 3.添加索引
• 4.删除索引

• 三、索引实现方式
• 四、索引的设计原则

• 1.创建索引时的原则
• 2.适用于组合索引的最左匹配原则

一、索引的分类

• 功能分类

1. 普通索引： 最基本的索引，它没有任何限制。
2. 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。
3. 主键索引：一种特殊的唯一索引，不允许有空值。一般在建表时同时创建主键索引。
4. 组合索引：顾名思义，就是将单列索引进行组合。
5. 外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。
6. 全文索引：快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。

• 结构分类

7. B+Tree索引 ：MySQL使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎默认的索引类型。
8. Hash索引 : MySQL中Memory存储引擎默认支持的索引类型。

二、索引操作

0.数据准备

-- 创建db11数据库
CREATE DATABASE db11;

-- 使用db11数据库
USE db11;

-- 创建student表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(10),
	age INT,
	score INT
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,98),
                           (NULL,'李四',24,95),
                           (NULL,'王五',25,96),
                           (NULL,'赵六',26,94),
                           (NULL,'周七',27,99);

1.创建索引

-- 标准语法
CREATE [UNIQUE|FULLTEXT] INDEX 索引名称
[USING 索引类型]  -- 默认是B+TREE
ON 表名(列名...);

-- 创建索引
CREATE INDEX idx_name ON student(name); -- 为name列创建普通索引
CREATE UNIQUE INDEX idx_age ON student(age); -- 为age列创建唯一索引

2.查看索引

-- 查看索引
SHOW INDEX FROM student;

显示如下：

• 注意：主键列自带主键索引，外键列自带外键索引。

3.添加索引

-- 1.普通索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名);

-- 2.组合索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);

-- 3.主键索引(主键列自带主键索引)
ALTER TABLE 表名 ADD PRIMARY KEY(主键列名); 

-- 4.外键索引(添加外键约束，就是外键索引)
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

-- 5.唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);

-- 6.全文索引(mysql只支持文本类型)
ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);

-- 给score列添加唯一索引
ALTER TABLE student ADD UNIQUE idx_score(score);

4.删除索引

-- 标准语法
DROP INDEX 索引名称 ON 表名;

-- 删除score列的唯一索引
DROP INDEX idx_score ON student;

三、索引实现方式

存储引擎主要使用的索引方式有三种：磁盘存储、B树、B+树。InnoDB引擎使用B+树索引。

1. 磁盘存储
   系统从磁盘中读数据时以磁盘块为单位，位于同一个磁盘块中的数据会同时被取出。
3. B树
   B树的每一个节点同事存储索引和数据，每个节点都占用一个磁盘块，并且有三个指向子树节点的指针，指针中存储子节点磁盘块的地址。查找索引时也会将整个节点所在磁盘块的数据取出。



5. B+树
   B+树中所有的数据都存在最底层的叶子节点上，非叶节点只存储索引信息，这样非叶结点不会进行磁盘的IO。叶子节点之间彼此通过指针相连，方便进行范围查询，例如一次性查询编号为26~30的数据。

四、索引的设计原则

1.创建索引时的原则

1. 对查询频次高且数据量大的表建立索引；
2. 最好使用唯一索引，区分度越高索引效率越高；
3. 最好从where子句中选择索引字段；
4. 索引不宜过多，会起反作用。

2.适用于组合索引的最左匹配原则

• 最左匹配原则只适用于组合索引
• 最左匹配原则即最左有限原则，检索数据时先从组合索引的最左边开始匹配。

例如，为name、address、phone建立组合索引idx_nap:

ALTER TABLE user ADD INDEX idx_nap(name,address,phone);

此时，实际上是建立了3个索引：(name)、(name,address)、(name,address,phone)。
下面3条语句都可以命中索引：

SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三'; -- 索引的字段顺序无关紧要
SELECT * FROM user WHERE name = '张三' AND address = '北京';
SELECT * FROM user WHERE name = '张三';

这3条语句执行时按照最左匹配原则，分别使用如下3个索引：

(name,address,phone)
(name,address)
(name)

若创建组合索引时最左边的列（此处为name）不在查询语句中，根据最左匹配原则则不会命中索引。例如下面3条语句都不会命中索引：

SELECT * FROM user WHERE address = '北京' AND phone = '12345';
SELECT * FROM user WHERE address = '北京';
SELECT * FROM user WHERE phone = '12345';