MySql索引

---

前言

以往我们在数据库里查询数据的时候,因为数据量不大,所以我们对查询效率的高低没有一个明显的感觉,试想一下如果我们的数据库中有几百万条数据,我们现在要在数据库中找到学号为1921024987的数据,需要从第一页数据开始逐页查找(mysql采用数据页存储数据,一页数据大小为16K,如果数据量很大会产生很多的数据页),这种查找效率是非常低的。这时候索引的作用就体现出来了,索引能帮助我们快速找到所需要数据的位置,从而提高数据库操作效率。

---

一、索引概述

数据库中的索引与书籍中的目录类似,在一本书中,利用索引可以快速找到所需要的信息。在数据库中,索引使数据库程序不需要对整个表进行扫描(这种复杂度为O(n)的算法在数据量很大时效率非常低),就可以找到所需要的数据。

简单的来说索引可以提高SQL查询效率,让用户更快的得到查询结果

索引是排好序的快速查找的数据结构,在下面的内容中会详细介绍索引所使用的数据结构

---

二、索引优缺点

2.1索引优点

1.有助于加快数据检索,降低数据库I/O成本,这也是创建索引的最主要的原因。
2.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
3.可以加速表和表之间的连接，实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间,降低了 CPU 的消耗。

---

2.2索引缺点

1.创建索引和维护索引需要耗费时间,当数据量增大的时候更加明显
2.索引需要占物理空间
3.表中的数据进行增加、删除和修改的时候，索引也要动态的维护,提高了CPU的消耗

---

三、索引创建原则

从上面我们可以知道索引虽然能给我们带来很多的好处,但同时也会给我们带来一系列的维护和空间问题。所以我们在创建索引的时候需要遵循一定的原则,这样才能把索引利用的恰到好处。

3.1 建议创建索引的情况

1.根据查询要求建立索引:查询频率高、实时要求高的字段应该创建索引,如主键、外键、经常需要连接查询的字段、排序的字段、查询指定范围的字段。

2.数据量大的大表应该创建索引

---

3.2不建议创建索引的情况

1.对数据表查询时很少引用到的、大量重复的字段不应该创建索引。

2.数据量非常小的数据表,索引能够改进其数据访问的效率十分有限,不必创建索引。

3.对于一个基本表不应该建立过多的索引,数据表进行增删改时,索引也随之变化。索引需要占用文件目录和存储空间,而且需要维护,过多会使系统负担加重。

---

四、索引分类

4.1主键索引

设定为主键后数据库会自动建立索引

-- 创建主键索引
ALTER TABLE test ADD PRIMARY KEY test(id);
-- 删除主键索引
ALTER TABLE test DROP PRIMARY KEY

---

4.2单值索引

一个索引只包含单个列,一个表可以有多个单列索引。

-- 创建单值索引
CREATE INDEX idx_name ON test(NAME);
-- 删除索引
DROP INDEX idx_name;

---

4.3唯一索引

索引列的值必须唯一,允许为 null,唯一索引保证了数据的唯一性。

-- 创建唯一性索引
CREATE UNIQUE INDEX idx_sno ON test(sno);
-- 删除索引
DROP INDEX  idx_sno ON test;

关于主键和唯一性索引的联系和区别
1.主键一定是唯一性索引,唯一性索引不一定是主键
2.一个表中只能有一个主键,但可以有多个唯一性索引
3.主键不允许有null值,唯一性索引允许有null值

---

4.4组合索引

组合索引也可以成为复合索引,一个索引包含多个列,组合索引比单值索引开销更小(对于相同的多个列建索引)

-- 创建复合索引
CREATE INDEX idx_grade_class_phone ON test(grade,class,phone);
-- 删除索引
DROP INDEX  idx_grade_class_phone ON test;

组合索引最左前缀原则
在使用组合索引的列作为条件时,必须要出现最左侧列为条件,否则索引不生效

-- 生效
SELECT * FROM test WHERE grade='' AND class='' AND phone=''
SELECT * FROM test WHERE grade='' AND sno=''
-- 不生效
SELECT * FROM test WHERE class='' AND phone=''

---

4.5全文索引

在数据量非常大的时候使用 like 进行模糊匹配效率十分低下(会导致索引失效,导致全表扫描),这时候就要用到全文索引

-- 创建全文索引
CREATE FULLTEXT INDEX idx_address ON test(address) WITH PARSER ngram;

-- 使用全文索引
SELECT * FROM test WHERE MATCH(address) AGAINST('搜索词')

---

4.6聚簇索引和非聚簇索引

聚簇索引: 找到了索引,就找到了数据。
非聚簇索引 : 也叫辅助索引, 找到了索引但没有找到数据, 需要根据索引上的值(主键)再次回表查询

下面我们通过几个场景来理解聚簇索引和非聚簇索引的区别。
1.根据主键查询获取所有字段数据，此时主键是聚簇索引

SELECT * FROM student WHERE id = 1

2.给学号加了唯一索引,但需要查询姓名信息,需要先找到主键(ID),然后通过主键回表查询,这种情况学号不是聚簇索引。

SELECT Sno,NAME FROM student WHERE Sno = 123

3.通过学号查学号(验证是否存在),这种情况直接返回学号,不需要回表查询,是聚簇索引

SELECT Sno FROM student WHERE Sno = 123

MySQL中InnoDB 引擎的索引和文件是存放在一起的,找到索引就可以找到数据,是聚簇式设计
MyISAM 引擎采用的是非聚簇式(即使是主键)设计,索引文件和数据文件不在同一个文件中

---

五、B+树

5.1二分搜索树

如果数据是单边增长的情况 那么出现的就是和链表一样的数据结构了，树高度大

5.2红黑树

在二分搜索树的基础上多了树平衡，也叫平衡二叉树，不像二分搜索树那样极端的情况会往一个方向发展。

5.3B树

在红黑树的基础上，每个节点可以存放多个数据

5.4B+树

B树的变种

5.5mysql索引为什么用B+树

以InnoDB存储引擎为例：
InnoDB 存储引擎就是用 B+Tree 实现其索引结构。B+树只会在叶子节点上面挂载数据，非叶子节点不会存放数据，非叶子节点只存放索引。

通过对数据排序提高查询效率,一个节点中可以存储多个元素，从而可以使得 B+树的高度不会太高,并且叶子节点之间有指针，可以很好的支持全表扫描，范围查找等 SQL 语句。

---

总结

本文介绍了索引的概念、优缺点、创建原则、分类以及索引的数据结构等内容。索引类似书籍的目录可以快速帮助我们找到需要的信息。索引在sql优化中起到了关键的作用,好的索引设计能极大的提高数据库效率,减少数据库的压力。本文对于索引的数据结构只是做了一个简单的概述,读者可以自行阅读相关资料深入了解。

---