[博学谷学习记录]超强总结,用心分享|架构 MySql索引分类

文章内容是学习过程中的知识总结，如有纰漏，欢迎指正

文章目录

前言

一、索引原理

二、索引分类

主键索引

普通索引（单列索引）

复合索引（组合索引）

唯一索引

全文索引

索引的查询和删除

三、索引的优缺点

优点

缺点

总结

---

前言

• 官方上面说索引是帮助MySQL高效获取数据的数据结构，通俗点的说，数据库索引好比是一本书的目录，可以直接根据页码找到对应的内容，目的就是为了加快数据库的查询速度。
• 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
• 一种能帮助mysql提高查询效率的数据结构：索引数据结构。

---

以下是本篇文章正文内容

一、索引原理

索引的存储原理可以概括为一句话：以空间换时间

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

数据库在未添加索引进行查询的时候默认是进行全文搜索，也就是说有多少数据就进行多少次查询，然后找到相应的数据就把它们放到结果集中，直到全文扫描完毕。

二、索引分类

• 主键索引

设定为主键后，数据库自动建立索引，InnoDB为聚簇索引，主键索引列值不能为空（Null）

# (1) 创建表添加主键索引
CREATE TABLE `table_name` (
    [...] ,
    PRIMARY KEY (`col_name`),
)
# (2) 添加主键索引
ALTER TABLE `table_name` ADD PRIMARY KEY (`col_name`);

• 普通索引（单列索引）

普通索引（单列索引）：单列索引是最基本的索引，它没有任何限制

#  (1) 直接创建索引
CREATE INDEX index_name ON table_name(`col_name`);
#  (2) 修改表结构的方式添加索引
ALTER TABLE `table_name` ADD INDEX index_name(`col_name`);
#  (3) 创建表的时候同时创建索引
CREATE TABLE `table_name` (
    [...] ,
    PRIMARY KEY (`id`),
    INDEX index_name (`col_name`)
)
#  (4) 删除索引
DROP INDEX index_name ON table_name;   
alter table `表名` drop index 索引名;

• 复合索引（组合索引）

复合索引：复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要

# （1）创建一个复合索引
create index index_name on table_name(`col_name1`,`col_name2`,...);
# （2）修改表结构的方式添加索引
alter table table_name add index index_name(`col_name1`,`col_name2`,...);

• 唯一索引

唯一索引：唯一索引和普通索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许存在空值（只允许存在一条空值）

如果在已经有数据的表上添加唯一性索引的话：

• 如果添加索引的列的值存在两个或者两个以上的空值，则不能创建唯一性索引会失败。（一般在创建表的时候，要对自动设置唯一性索引，需要在字段上加上 not null）
• 如果添加索引的列的值存在两个或者两个以上的null值，还是可以创建唯一性索引，只是后面创建的数据不能再插入null值 ，并且严格意义上此列并不是唯一的，因为存在多个null值。
• 对于多个字段创建唯一索引规定列值的组合必须唯一

“空值” 和”NULL”的概念： 
1：空值是不占用空间的 .
2: MySQL中的NULL其实是占用空间的.

长度验证：注意空值的之间是没有空格的。

> select length(''),length(null),length(' ');
+------------+--------------+-------------+
| length('') | length(null) | length(' ') |
+------------+--------------+-------------+
|          0 |         NULL |           1 |
+------------+--------------+-------------+

-- （1）创建唯一索引
# 创建单个索引
CREATE UNIQUE INDEX index_name ON table_name(`col_name`);

# 创建多个索引
CREATE UNIQUE INDEX index_name on table_name(`col_name`,...);

-- （2）修改表结构
# 单个
ALTER TABLE table_name ADD UNIQUE index index_name(`col_name`);
# 多个
ALTER TABLE table_name ADD UNIQUE index index_name(`col_name`,...);

-- （3）创建表的时候直接指定索引
CREATE TABLE `table_name` (
    [...] ,
    PRIMARY KEY (`id`),
    UNIQUE index_name_unique(`col_name`)
)

• 全文索引

1. Full Text类型索引（FULLTEXT 索引在 MySQL 5.6 版本之后支持 InnoDB，而之前的版本只支持 MyISAM 表）。
2. 全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较，目前只有char、varchar，text 列上可以创建全文索引。

-- （1）创建表的适合添加全文索引
CREATE TABLE `table_name` (
    [...] ,
    PRIMARY KEY (`id`),
    FULLTEXT (`col_name`)
)

-- （2）修改表结构添加全文索引
ALTER TABLE table_name ADD FULLTEXT index_fulltext_content(`col_name`)

-- （3）直接创建索引
CREATE FULLTEXT INDEX index_fulltext_content ON table_name(`col_name`)

注意：

1. 默认 MySQL 不支持中文全文检索！
2. MySQL 全文搜索只是一个临时方案，对于全文搜索场景，更专业的做法是使用全文搜索引擎，例如 ElasticSearch 或 Solr。

• 索引的查询和删除

#查看:
show indexes from `表名`;
#或
show keys from `表名`;
 
#删除
alter table `表名` drop index 索引名;

---

三、索引的优缺点

• 优点

1. 大大提高数据查询速度。
2. 可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。
3. 通过索引列对数据进行排序，降低数据的排序成本降低了CPU的消耗。
4. 被索引的列会自动进行排序，包括【单例索引】和【组合索引】，只是组合索引的排序需要复杂一些。
5. 如果按照索引列的顺序进行排序，对order 不用语句来说，效率就会提高很多。

• 缺点

1. 索引会占据磁盘空间。
2. 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改查操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。
3. 维护索引需要消耗数据库资源。

---

总结

数据库表中并不是索引越多越好，而是仅为那些常用的搜索字段建立索引效果最佳!