MySQL高级——索引原理及使用

1、什么是索引？

MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。所以索引是一种数据结构，它将数据进行排序所以它可以快速查找数据。所以索引的主要功能是用来查找和排序。

对表中字段建立索引实际上就是：将该表每一条数据的该字段都提取出来，按照特定的规律排序后存入特定的数据结构中。这样好处就是查询该字段时速度更快，但缺点是耗费更多空间且存入该字段数据时更加耗费性能。(原因后面就懂了)

2、索引的分类

单值索引：一个索引只包含单个字段。一个表里可以有多个单值索引，但一次查询只能用一个索引。

复合索引：一个索引包含多个字段。一次查询中包括多个字段时可以使用复合索引。(使用规则后面讲)

唯一索引：不能有两行相同的索引值。值可以为空，Innodb引擎允许多个空。

非空索引：索引列的值必须是非空的。

前缀索引：对字段前几个字符建立索引。

主键索引：又叫聚簇索引，存储结构相关，后面讲。

二级索引：就是非聚簇索引，不是主键上的索引。

3、索引的结构

MySQL索引常见的数据存储结构有B+Tree、Hash、R-Tree等。InnoDB引擎采用B+Tree结构，这里只讲B+Tree原理。

这是一张数据库表：

对于InnoDB存储引擎来说，最小的存储单位就是页。存放原始数据的页就是数据页。数据页内部会对主键进行排序，所以我们通过主键查找时很容易找到数据。

每页大小是有限的，当数据量庞大时就需要多个数据页来存储数据。

当有大量数据页时我们需要一页一页查找，效率还是很低，所以接下来对数据页结构进行优化：将所有数据页的页码和最小主键抽取出来，保存在目录页中。比如我要找主键为20的数据，比11大但不到21，很容易就定位到页码为2的数据页。

目录页大小也是有限的，当数据量及其庞大时我们就需要多个目录页共同保存，此时我们就需要给目录页再次设置目录。最终形成一种树结构，其实这就是 B+Tree。

在这个例子里是以主键为索引形成的B+Tree，主键索引也就是聚簇索引。聚簇索引不仅对主键进行了排序，也保存了原始数据，所以聚簇索引也是数据表本身。或者说数据表本身就是以主键为索引按照B+Tree组建起来的。

还有一种非主键索引，也就是非聚簇索引。当使用非主键创建索引时，会再创建一颗B+Tree，此时的数据页不再存储原始数据，而是由创建索引的字段和主键组成。例如使用emp_age创建索引：

有时单个字段创建的索引不能满足需求，就需要使用多个字段创建索引，这就是组合索引。

由于非聚簇索引的数据页没有保存原始数据，当使用非聚簇索引查询原始数据时要通过回表操作。即通过非聚簇索引查询到主键后再通过聚簇索引查询到原始数据。

总结：MySQL数据库在使用InnoDB引擎时，数据表本身是以主键为索引以B+Tree结构存储，最小存储单元是页，每个页就是B+Tree的一个节点。创建非主键索引需要额外创建B+Tree，所以耗费空间，添加数据时要对B+Tree重新排序，所以耗费性能。使用非主键索引查询时由于没有直接保存原始数据，所以需要回表操作，所以要查两颗B+Tree。

4、创建索引

索引创建语句

-- 创建普通索引
CREATE INDEX index_name ON table_name (column_name)
-- 创建联合索引
CREATE INDEX index_name ON table_name (column_name01,column_name02)
-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column_name)
-- 删除普通索引
DROP INDEX index_name ON table_name
-- 创建主键索引
ALTER TABLE table_name ADD PRIMARY KEY (column_name)
-- 删除主键索引
ALTER TABLE table_name DROP PRIMARY KEY
-- 降序创建索引
CREATE INDEX index_name ON table_name (column_name DESC)
-- 查询所有索引
SHOW INDEX FROM table_name

索引创建环境

哪些情况需要建立索引：

1. 主键自动建立唯一索引；
2. 频繁作为查询条件的字段应该创建索引；
3. 查询中与其它表关联的字段，外键关系建立索引；
4. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度；
5. 查询中统计或者分组字段；

哪些情况不要建立索引：

1. 表记录太少，比如300万条以下即使创建索引也不会让搜索速度有明显提升；
2. 频繁更新的字段不适合创建索引 ，因为每次更新不单单是更新了记录还会更新索引；
3. 数据大量重复的字段，比如男女，此时建立索引也没任何意义；
4. where条件、排序、分组都用不到的字段；
5. 无法排序的字段；

单键/组合索引的选择问题：

1. 不管怎么优化，即使创建了多个索引，一次查询只能用到一个索引。所以当需要使用多个字段时建议使用组合索引。

5、总结

索引就是一种数据结构，它将指定的字段按照规律排序，进一步提高查询效率。合理建立索引能极大提高数据库性能，但乱使用索引也会对数据库照成压力，比如占用更多内存、增删改时重建索引照成性能消耗。所有一定要根据业务具体需求分析后建立索引。