Mysql索引的优点、缺点、分类及使用方法

---

一、前言

1.索引是一种可以帮助 Mysql 高效获取数据的数据结构，目的是为了减少磁盘I/O次数，加快查询速度
2.索引是在存储引擎中实现的，每种存储引擎实现索引的方式可能不通，其中Mysql的InnoDB引擎的索引底层采用B+树算法实现
3.所有存储引擎支持每个表至少16个索引，总索引长度至少为256个字节

---

二、优点

1.降低数据库I/O成本
2.对有依赖关系的子表和父表联合查询时，可以提高查询速度
3.在使用 group by 或者 order by 查询数据时，可以显著减少分组和排序的时间

---

三、缺点

1.创建和维护索引会耗费时间
2.索引存储文件会占用磁盘空间
3.大大提高表查询效率的同时会降低表更新效率【增删改的效率会被降低】

---

四、分类及创建方式

1.功能上分类

<1>普通索引

不附加任何限制条件，只用于提高查询效率，可以在任何字段上创建

# 建表时创建索引
CREATE TABLE book (
    id int NOT NULL AUTO_INCREMENT,
    title varchar(255) DEFAULT NULL,
    author varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
  
    # title字段创建普通索引 ---
    INDEX idx_title(title)
    # ----------------------
  
);


# 为已创建的表添加索引
ALTER TABLE book ADD INDEX idx_title(title);
# 或者
CREATE INDEX idx_title ON book(title);

<2>唯一索引

字段添加 UNIQUE 参数即设置为唯一索引，该索引字段必须唯一，但可以有空值，一张数据表可以有多个唯一索引

# 建表时创建索引
CREATE TABLE book (
    id int NOT NULL AUTO_INCREMENT,
    title varchar(255) DEFAULT NULL,
    author varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
  
    # title字段创建唯一索引 --------
    # 作用于电话号码、身份证号等字段时很实用
    UNIQUE INDEX idx_title(title)
    # ----------------------------
  
);

# 为已创建的表添加索引
ALTER TABLE book ADD UNIQUE INDEX idx_title(title);
# 或者
CREATE UNIQUE INDEX idx_title ON book(title);

<3>主键索引

主键索引是一种特殊的唯一索引，在主键索引的基础上增加不为空的约束，也就是 NOT NULL + UNIQUE ，一张表只能有一个主键索引

# 建表时创建索引
CREATE TABLE book (

	# 创建主键索引 ---------------------
    id int PRIMARY KEY AUTO_INCREMENT,
    # --------------------------------
  
    title varchar(255) DEFAULT NULL,
    author varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE, 
);

<4>全文索引

字段添加 FULLTEXT 可以设置为全文索引，只能创建在 char、vachar 或 text 类型的字段上，查询数据量较大的文本信息时可以提高查询速度【不过当数据量巨大的时候，还是需要用ElasticSearch】

# 建表时创建索引
CREATE TABLE book (
    id int NOT NULL AUTO_INCREMENT,
    title varchar(255) DEFAULT NULL,
    author varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
  
    # title字段创建全文索引 --------------------
    # 使用title字段的前50个字符创建全文索引
	FULLTEXT INDEX futxt_idx_title(title(50))
    # ---------------------------------------
  
);

# 为已创建的表添加索引
ALTER TABLE book ADD FULLTEXT INDEX futxt_idx_title(title(50));
# 或者
CREATE FULLTEXT INDEX futxt_idx_title ON book(title(50));

# 检索方式
SELECT * FROM book WHERE MATCH(title) AGAINST ("白夜行")

# 建表时创建索引
CREATE TABLE book (
    id int NOT NULL AUTO_INCREMENT,
    title varchar(255) DEFAULT NULL,
    author varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
  
    # title字段创建全文索引 ------------------------------
    # 使用title、author字段创建全文索引
	FULLTEXT INDEX futxt_idx_title_author(title, author)
    # --------------------------------------------------
  
);

# 为已创建的表添加索引
ALTER TABLE book ADD FULLTEXT INDEX futxt_idx_title_author(title, author);
# 或者
CREATE FULLTEXT INDEX futxt_idx_title_author ON book(title, author);

# 检索方式
SELECT * FROM book WHERE MATCH(title, author) AGAINST ("白夜行")

<5>空间索引

InnoDB不支持，本人水平有限暂时没用到，就不做阐述了

---

2.物理实现上分类

InnoDB的索引分为两种

<1>聚簇索引

基于主键id搭建的B+树，索引和数据不分家，所有用户记录都保存在了叶子结点，可以说是 索引即数据，数据即索引
聚簇索引不需要用户显式的创建，而是插入数据的时候有数据引擎自动创建，正是因此，当id为查询条件时，查询速度会得到显著的提升

<2>非聚簇索引【二级索引、辅助索引】

基于指定字段搭建的B+树，仅有指定字段和主键，没有其他数据，索引和数据是分开的，当mysql根据指定字段的非聚簇索引找到对应数据后，需要通过非聚簇索引对应的主键在聚簇索引中查出数据【这个操作叫做回表】，可以说是 索引是索引，数据是数据【旺柴】
非聚簇索引需要用户显式创建

ps：非聚簇索引还有个特殊情况就是联合索引，是基于多个指定字段搭建的B+树，可提高多条件查询的效率

<3>二者区别

1.聚簇索引叶子结点存储的是数据，非聚簇索引叶子结点存储的是数据位置
2.一个表只能有一个聚簇索引【因为只能有一种排序存储方式】，但是可以有多个非聚簇索引
3.聚簇索引在查询时效率高【不需要进行回表查询】，非聚簇索引在增删改时效率高【不需要更新数据】

---

3.字段个数上分类

<1>单列索引

一个索引只对应一个字段【包括普通索引、唯一索引等】

# 建表时创建索引
CREATE TABLE book (
    id int NOT NULL AUTO_INCREMENT,
    title varchar(255) DEFAULT NULL,
    author varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
  
    # title字段创建单列索引 ---
    INDEX idx_title(title)
    # ----------------------
  
);

# 为已创建的表添加索引
ALTER TABLE book ADD INDEX idx_title(title);
# 或者
CREATE INDEX idx_title ON book(title);

<2>联合索引

一个索引对应多个字段

# 建表时创建索引
CREATE TABLE book (
    id int NOT NULL AUTO_INCREMENT,
    title varchar(255) DEFAULT NULL,
    author varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
  
    # title、author字段创建联合索引 -------
    INDEX mul_idx_title_author(title, author)
    # -----------------------------------
  
);

# 为已创建的表添加索引
ALTER TABLE book ADD INDEX mul_idx_title_author(title, author);
# 或者
CREATE INDEX mul_idx_title_author ON book(title, author);

重点：联合索引的多个字段在查询时一定要注意sql中的查询字段，比如上面那个联合索引，如果使用下面这种查询方法，实际上是用不到索引的

SELECT * FROM book WHERE author="东野圭吾";

但是下面这两条却可以用到索引

SELECT * FROM book WHERE title="白夜行";
SELECT * FROM book WHERE title="白夜行" and author="东野圭吾";

也就是说，创建联合索引时，是按照你给定的字段顺序创建B+树的，查询的时候后面的字段依赖于前面的字段【称为最左前缀原则】，如果只有后面的字段没有前面的字段，那么查询时将不会使用索引

---

五、其他操作

1.删除索引

# 删除 book 表的 idx_title 索引
DROP INDEX idx_title ON book

2.查看索引

# 查看 book 表已创建的索引
SHOW INDEX FROM book;

3.查看某条sql是否用到了索引

# 性能分析工具，可以查看这条sql是否用到了索引
EXPLAIN SELECT * FROM book WHERE title = "高等数学";

---