MySQL基础—03：索引

一、MySQL 索引

“索引”：是存储引擎快速查询记录的一种数据结构。如书本里的目录。索引是在存储引擎层实现的，所以不同存储引擎具有不同的索引类型和实现。

操作系统从磁盘读取数据到内存是以磁盘块（block）为基本单位，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

读取的长度一般为页（page）的整数倍。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（大小通常为4k）。

---

1、创建索引的好处（为什么要创建索引）：

1. 提高数据库的检索速度，降低数据库的IO成本。
2. 降低数据排序的成本，降低CPU消耗。

2、创建索引的缺点：

1. 占用存储空间。
2. 降低更新表的速度，插入和修改数据时要花费较多的时间维护索引。
3. 优质索引创建难。

 

---

二、索引分类：

1.主键索引（primary  key）：特殊的唯一索引，不允许有空值，一般在建表的时候同时创建主键索引。

2.唯一索引（unique）：索引列的值必须唯一，允许有空值。创建唯一索引：

ALTER TABLE 表名 ADD UNIQUE(列名)

3.普通索引（index）：最基本索引，没有任何限制。创建普通索引：

ALTER TABLE 表名 ADD INDEX 索引名 (列名);

4.组合索引（index）：一个索引包含多个列。创建组合索引：

ALTER TABLE 表名 ADD INDEX 索引名 (列名1，列名2...);

 

---

5.B+ Tree 索引

是 MySQL的 InnoDB存储引擎的默认索引类型。

除了用于查找，还可以用于排序和分组。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

 

6.哈希索引

哈希索引能以 O(1) 时间进行查找，但是失去了有序性：

• 无法用于排序与分组；
• 只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

 

7.全文索引（FULLTEXT）

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。是目前搜索引擎使用的一种关键技术。

查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

创建全文索引：

ALTER TABLE 表名 ADD FULLTEXT (列名);

索引一经创建，不能修改，只能删除重建。删除索引：

DROP INDEX 索引名 ON 表名;

 

8.空间数据索引

MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

必须使用 GIS 相关的函数来维护数据。

 

---

三、索引的基本语法：

1.创建

create [unique] index 索引名 on 表名(列1，列2...);

alter table 表名 add [unique] index 索引名 on(列1，列2...);

2.删除

drop index 索引名 on 表名;

3.查看

show index from 表名;

 

---

四、是否需要创建索引？

那些情况需要创建索引？

1. 频繁作为查询的字段
2. 与其他表关联的外键字段
3. 查询语句中要排序的字段
4. 查询语句中统计或者要分组的字段

那些情况不需要创建索引？

1. 表记录太少
2. 经常增删改的表或字段
3. where条件里用不到的字段
4. 过滤性不好的字段，比如性别字段

 

---

如有错误，欢迎留言指正  * _ *