数据库索引

索引

1、概述

1. 索引是帮助用户高效获取数据的数据结构（有序）。
2. 索引本质上是一个排序的列表，存储着索引字段的值以及对应行记录的数据所在的物理位置（或逻辑指针）。
3. 索引用来快速寻找具有特定值的记录，如果没有索引，一般来说执行查询就是遍历整张表。

2、基本原理

索引可以将无序的数据变成有序的查询

• 把创建了索引的列的内容进行排序

• 对排序结果生成到倒排表，并在倒排表内容上拼上数据地址链

• 在查询的时候，先拿到倒排表的内容，再取出数据地址链，从而拿到数据。

优势：
a. 提高数据检索的效率，降低数据库的IO成本。

​b. 通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势：
a. 索引需要占用空间。

​b. 索引大大提高了查询效率，同时降低了更新表的速度，如对表进行insert、update、delete的效率降低。

问题：InnoDB存储引擎采用B+树为索引的原因？

• 相比于红黑树来说，B+树层级更少，搜索效率更高。
• 相比于B树来说，B树无论是叶子结点还是非叶子结点都会保存数据，这样会导致一页存储的键值减少，存储大量数据时只能增加树的高度，从而会磁盘IO的次数也会变多，导致性能降低。

---

索引分类（InnoDB引擎）：

分类	含义	特点
聚集索引	将数据存储与索引存储在一起，索引结构的叶子结点保存了行数据	必须有，而且只能有一个
二级索引	将数据与索引分开存储，索引结构的叶子结点关联的是对应的主键	可以存在多个

表示如下图所示：

聚集索引的选取规则：

• 如果存在主键，主键索引就是聚集索引；
• 如果不存在主键，将使用第一个唯一索引作为聚集索引；
• 若果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowId作为隐藏的聚集索引。

3、索引的使用场景

• 经常需要搜索的列上创建：可以加快搜索的速度，提高查询效率。
• 经常用作连接条件的列上创建：这样可以加速表与表之间的连接操作。
• 经常需要排序的列上创建：这样可以利用索引的排序特性，加快排序查询的时间。
• 经常出现在WHERE子句中的列上创建：这样可以加快条件的判断速度，提高查询效率。

4、索引语法

分类	含义	特点	关键字
主键索引	针对表中的主键创建索引	默认创建，唯一	primary
唯一索引	避免同一个表中某数据列中的值重复	可以存在多个	unique
常规索引	快读定位特定的数据	可以存在多个
全文索引	全文索引查找的是文本中的关键词	可以存在多个	fulltext

（1）索引的创建、查看、删除

-- 创建索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name(index_col_name,...);
-- 查看索引
SHOW INDEX FROM table_name;
-- 删除索引
DROP INDEX index_name ON table_name;

（2）前缀索引

当数据库字段类型为字符串时，有的时候需要索引很长的字符串，这会造成空间的大量浪费，影响效率，此时可以将字符串的一部分前缀，建立索引，这样可以大大节省索引空间，提高索引效率。

-- 取字符串的前n个字符建立索引
create index index_name on table_name(column(n));
-- 计算选择度, 选择度为1说明不重复
select count(distinct substring(email,1,10))/count(*)) from table_name;

（3）联合索引和单列索引的使用

索引的使用：涉及到多个字段时，推荐使用联合索引，因为多个单列索引只有一个会被采用。

联合索引结构：树节点存储联合的字段，叶子结点存储主键，类似于一个二级索引。

注意：使用联合索引要注意最左前缀法则