1.索引的介绍
- MySQL 索引:是帮助 MySQL 高效获取数据的一种数据结构。所以,索引的本质就是数据结构!
- 在表数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式指向数据, 这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
- 有索引和无索引的情况比较
- 作用:提高查询效率
2.索引的分类
<1>按照功能分类
普通索引:最基本的索引,没有任何限制。
唯一索引:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值组合必须唯一。
主键索引:一种特殊的唯一索引,不允许有空值。在建表时有主键列同时创建主键索引。
联合索引:顾名思义,就是将单列索引进行组合。
外键索引:只有 InnoDB 引擎支持外键索引,用来保证数据的一致性、完整性和实现级联操作。
全文索引:快速匹配全部文档的方式。InnoDB 引擎 5.6 版本后才支持全文索引。MEMORY 引擎不支持。
<2>按照结构分类
BTree 索引:MySQL 使用最频繁的一个索引数据结构,是 InnoDB 和 MyISAM 存储引擎默认的索引类型,
底层基于 B+Tree 数据结构。
Hash 索引:MySQL 中 Memory 存储引擎默认支持的索引类型。
3.索引的创建/查看/删除
<1>主键和外键的创建本质就是索引的创建,会产生对应的索引
查询student表中的索引 (主键列自带主键索引)
查询db4数据库中的product表 (外键列自带外键索引)
<2>创建索引
-- 1. 创建索引的语法:
CREATE [UNIQUE][FULLTEXT] INDEX 索引名称 [USING 索引类型] ON 表名称(列名称....);
-- 2. 案例代码:
-- 为 student 表当中的 name 列创建一个普通的索引。
CREATE INDEX idx_name ON student(name);
-- 为 student 表当中的 age 列创建一个唯一索引。
CREATE UNIQUE INDEX idx_age ON student(age);
<3>查看索引
-- 1. 查看索引的语法:
SHOW INDEX FROM 表名称;
-- 2. 案例代码: 查询学生表当中,拥有的索引
SHOW INDEX FROM student;
<4>添加索引
-- 1. 普通索引
ALTER TABLE 表名称 ADD INDEX 索引名称 (列名);
-- 2. 组合索引
ALTER TABLE 表名称 ADD INDEX 索引名称 (列名1,列名2,列名3....);
-- 3. 主键索引
ALTER TABLE 表名称 ADD PRIMARY KEY (主键列名称);
-- 4. 外键索引
ALTER TABLE 表名称 ADD CONSTRAINT 外键名称 FOREIGN KEY (本表外键列名) REFERENCES 主表名称(主键列名);
-- 5. 唯一索引
ALTER TABLE 表名称 ADD UNIQUE 索引名称(列名);
-- 6. 全文索引
ALTER TABLE 表名称 ADD FULLTEXT 索引名称(列名);
<5>删除索引
-- 1. 基础语法
DROP INDEX 索引名称 ON 表名称;
<6>索引的使用
- 索引创建成功后,再次查询带有索引的列,则它的效率会提高【查询时间变短】
4.索引的原理[面试]
原理: 磁盘存储 B+Tree
<1>磁盘存储特点
- 系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的。
- 位于同一个磁盘块中的数据会被一次性读取出来,而不是需要什么取什么。
- InnoDB 存储引擎中有页(Page)的概念,页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为 16KB。
- InnoDB 引擎将若干个地址连接磁盘块,以此来达到页的大小 16KB,在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置,这将会减少磁盘 I/O 次数,提高查询效率。
<2>BTree数据结构
特点:每个节点中不仅包含 key 值,还有数据。会增加查询数据时磁盘的 IO 次数
<3>B+Tree数据结构
特点:非叶子节点只存储 key 值,所有数据存储在叶子节点,所有叶子节点之间都有连接指针
好处:提高查询速度,减少磁盘的 IO 次数,树型结构较小
<4>原理总结
- BTree树的所有的节点都保存地址和数据,在检索/查询的过程中会读取到各个节点上的数据,从而产生了很多不必要的IO操作。B+Tree树的非叶子节点只存储地址不存储数据,所有的数据都保存在叶子节点上,在检索/查询的过程中对于非叶子节点不会读取数据,减少了IO操作,所以能提高查询效率。
5.创建索引的原则
- 对查询频次较高,并且数据量比较大的表,建立索引。
- 使用唯一索引,区分度越高,使用索引的效率越高。
- 索引字段的选择,最佳候选列应当从 WHERE 子句的条件当中提取
- 如果 WHERE 子句当中的条件组合比较多,那么应当挑选最常用的、过滤效果最好的列组合
- 索引虽然可以有效的提升查询数据的效率,但是并不是多多益善。