MYSQL必知必会笔记：第二十三章索引

是什么

索引是一个单独的、存储在 磁盘 上的 数据库结构 ，包含着对数据表里 所有记录的 引用指针

存储类型

MySQL中索引的存储类型有两种，即 BTree 和 Hash

索引 的优缺点

优点：

• 提高数据的查询的效率（类似于书的目录）

• 可以保证数据库表中每一行数据的唯一性（唯一索引）

• 减少分组和排序的时间（使用分组和排序子句进行数据查询）

• • 被索引的列会自动进行分组和排序

缺点：

• 占用磁盘空间
• 降低更新表的效率（不仅要更新表中的数据，还要更新相对应的索引文件）

索引 的分类

1、普通索引 和 唯一索引

• 普通索引：MySQL 中的基本索引类型，允许在定义索引的列中插入 重复值 和 空值

• 唯一索引：要求索引列的值必须 唯一，但允许 有空值

• • 如果是组合索引，则列值的组合必须 唯一
  • 主键索引是一种特殊的唯一索引，不允许 有空值

2、单列索引 和 组合索引

• 单列索引：一个索引只包含单个列，一个表可以有多个单列索引

• 组合索引：在表的 多个字段 组合上 创建的 索引

• • 只有在查询条件中使用了这些字段的 左边字段 时，索引才会被使用（最左前缀原则）

3、全文索引

• 全文索引 的类型为 fulltext
• 在定义索引的 列上 支持值的全文查找，允许在这些索引列中插入 重复值 和 空值
• 全文索引 可以在 char、varchar 和 text 类型的 列 上创建

4、空间索引

• 空间索引 是对 空间数据类型 的字段 建立的索引
• MySQL中的空间数据类型有4种，分别是 Geometry、Point、Linestring 和 Polygon
• MySQL 使用 Spatial 关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引
• 创建空间索引的列，不允许为空值，且只能在 MyISAM 的表中创建。

5、前缀索引

• 在 char、varchar 和 text 类型的 列 上创建索引时，可以指定索引 列的长度

索引的数据结构

MySQL 索引 的数据结构可以分为 BTree 和 Hash 两种，BTree 又可分为 BTree 和 B+Tree

Hash：使用 Hash 表存储数据，Key 存储索引列，Value 存储行记录或行磁盘地址。

Hash 只支持等值查询（“=”，“IN”，“<=>”），不支持任何范围查询（原因在于 Hash 的每个键之间没有任何的联系），Hash 的查询效率很高，时间复杂度为 O(1)。

BTree：属于多叉树，又名多路平衡查找树。

性质：

• BTree 的节点存储多个元素（ 键值 - 数据 / 子节点 的地址）
• BTree 节点的键值按 非降序 排列
• BTree 所有叶子节点都位于同一层（具有相同的深度）

索引 的使用

MySQL 索引 的基本语法

• 定义 主键约束、外键约束、唯一约束 等约束时 相当于同时在指定列上创建了一个索引

创建表时：

create table table_name(
	[col_name data_type] [unique | fulltext | spatial...],
    [unique...] [index | key] [index_name] (col_name [length], ...)
);

create table user (
    id INT NOT NULL, 
    name CHAR(30) NOT NULL, 
    unique index uniqueIdx(id) 
);

表已存在时：

-- 第一种 
alter table table_name 
	add [unique...] [index | key] [index_name] (col_name [length], ...);
	
alter table user add unique index uniqueIdx(id);

-- 第二种 
create [unique...] index index_name 
on table_name (col_name [length], ...);

create unique index uniqueIdx on user(id);


-- 删除索引
 drop index index_name on table_name;

怎么判断要不要加索引？

加索引：

• 数据本身具有某种的性质，如：唯一性、非空性…
• 频繁进行 分组或排序 的列；如果待排序的列有多个，可以建立 组合索引

不加索引：

• 经常更新的列
• 列 的值类型 很少，如 性别
• where 条件中用不到的列
• 参与计算的列
• 数据量小的表

创建了索引，不一定会生效当使用 组合索引 时，如果没有遵循 最左匹配 原则，索引不生效。

例如，创建 id、name、age 组合索引

• id、（id、name）、（id、name、age）查询，索引生效
• age、（age、name）查询，索引不生效

判断索引是否生效？

使用 explain 关键字。

• possible_keys：MySQL 在搜索数据记录时可选用的各个索引
• key：MySQL 实际选用的索引

explain select * from user where id = 1;

怎么避免索引失效?

• 使用组合索引时，遵循 最左匹配 原则
• 不在索引列上进行任何操作，如：计算、函数、类型转换
• 尽量使用覆盖索引
• 索引列 尽量不使用 不等于（!= / <>）条件、通配符开头的模糊查询（like %abc）、or 作为连接条件
• 字符串加单引号（不加可能会发生索引列的隐式转换，导致索引失效）