索引

索引是帮助 MySQL 高效获取数据的一种数据结构

• 普通索引：普通索引即无任何限制条件的索引，该索引可以在任何数据类型中创建。
• 唯一索引：索引的值必须唯一，通过唯一索引，用户可以快速定位某条记录，主键是一种特殊的唯一索引。
• 全文索引：使用 FULLTEXT 参数可以设置索引为全文索引。全文索引只能创建在 CHAR 、 VARCHAR 或 TEXT 类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度，InnoDB 从 MySQL 5.6 版本开始提供对全文索引的支持。
• 单列索引：单列索引是对应一个字段的索引，它包括前 3 种索引方式。
• 多列索引：多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，用户可以通 过这几个字段进行查询。要想应用该索引，用户必须使用这些字段中的第一个字段。
• 空间索引：使用 SPATIAL 参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提 高系统获取空间数据的效率。 MySQL 中只有 MyISAM 存储引擎支持空间检索，而且索引的字段不能为空值，主要用于地理空间数据类型，通常使用较少
• 外键索引：只有 InnoDB 引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。

索引操作

创建索引是指在某个表的至少一列中建立索引，以提高表的访问速度和数据库性能。

create [unique | fulltext | spatial] index 索引名称
[using 索引类型] -- 默认为 btree
on 表名(列名 [( 长度 )],... [ASC|DESC]);
-- 长度：可选项，指索引长度，必须是字符串类型才可以使用

-- 创建表示同时创建索引
CREATE TABLE 表名 (
字段 1 数据类型 [ 约束条件 ],
...,
[UNIQUE|FULLTEXT|SPATIAL] INDEX [ 别名 ] ( 字段 1 [( 长度 )],... [ASC|
DESC])
);

-- 查看索引
show index from 表名;

修改已经存在表上的索引，可以通过 ALTER TABLE 语句为数据表添加索引

ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ( 字段名 [( 长度 )],... [ASC|DESC]);

-- 普通索引
alter table 表名 add index 索引名称(列名);
-- 组合索引
alter table 表名 add index 索引名称(列名,列名);
-- 主键索引
alter table 表名 add primary key (主键列名);
-- 外键索引
alter table 表名 add constraint 外键名 foreign key (外键列名) references 主表名(主键列名);
-- 唯一索引
alter table 表名 add unique 索引名称(列名);
-- 全文索引
alter table 表名 add fulltext 索引名称(列名);
-- 空间索引
ALTER TABLE 表名 ADD SPATIAL INDEX 索引名 (字段名);

删除索引可以通过 DROP 语句来实现

DROP INDEX 索引名 ON 数据表名 ;

原理

索引是在存储引擎中实现的

BTree 索引类型是基于 B+Tree 数据结构的，而 B+Tree 是 Btree 的变种，通常在数据库 和 系统中的文件系统中 特点是能够 保持数据稳定有序

1. 磁盘存储

2. BTree

由于每次读取到一个磁盘块中的数据 深度 导致效率低

每个节点中包含 key 值 和 数据 会增加查询时 磁盘 IO 次数

3. B+Tree

数据只会保持在叶子节点上 并且叶子节点 之间指针相连 方便进行范围查询

非叶子节点只存储 key 值 减少磁盘 IO 的次数 树形结构较小

索引原则

1. 对查询次数较高 并且数据量比较大的表 建立索引
2. 建议使用唯一索引 区分度高 索引的效率高
3. 索引字段的选择 应当从 where 子句的条件中提取
4. 索引虽然提供效率 但过多不便于维护
5. 最左匹配原则 (只适合于组合索引)
   • 当我们为表中 name age height 添加组合索引 实质上建立了 name 列索引 (name、age) 索引 （name、age、height）索引 使用这三个索引时不区分顺序