04.索引

12a2024/10/3大约 13 分钟

04.索引

4.1 索引功能和相关概念

索引用于提高查询性能，索引的数据结构以某种方式指向数据，并在这些数据结构的基础上实现高级查找算法，索引：
- 可以降低IO搜索次数
- 可以保证数据的唯一性
- 可以提高表连接的查询速度
- 可以显著减少分组和排序时间
- 需要额外的创建和维护时间，在增加更新和删除数据时，需要动态地维护，降低表的更新速度
- 需要占磁盘空间，如果有大量索引，索引可能比数据更大
索引的代价
- 空间代价：索引会占用很大的存储空间，比如InnoDB使用的B+树索引，一个页默认占用16KB空间
- 时间代价：在增删改操作时，都可能需要修改索引，同时进行修改时，可能涉及到记录移位、数据回收等操作，会对性能带来影响

4.1.1 索引类型

索引是在引擎中实现的，因此每个引擎使用的索引方式不一定相同，同时最大索引数和最大索引长度也有所不同，具体索引结构见数据库引擎中的各个引擎的索引结构内容
从索引列的个数上区分，索引的类型有
- 单值索引：仅对单列索引
- 联合索引：对多列索引，依据这些列依次进行排序
  - 索引列的次序会影响索引效果，存在最左前缀原则，也就是查询条件中必须包含最左侧的某些前缀列，才能用上联合索引
  - 如果其中的列被删除，将仅删除该列上的多列索引，其他索引列的次序不变；如果其中所有的列都被删除，则索引被删除

一般情况下有聚簇索引和非聚簇索引两种索引存储结构
聚簇索引：索引和数据存储在一起，也就是数据页中存储完整数据行信息
优点
- 数据页中存储完整数据行信息，比非聚簇索引查询速度更快
- 查询相比非聚簇索引节省大量IO操作
- 排序查找和范围查找速度很快
缺点
- 插入速度严重依赖插入次序，因此一般需要一个递增的主键
- 更新主键的代价很高，一般主键都不更新
- 二级索引寻找非主键值需要两次查找，第一次找到主键值，第二次找到行数据
限制
- 物理存储方式只有一种，因此一般情况下聚簇索引都依赖主键排序存储
  - 如果没有定义主键，InnoDB会使用非空唯一属性索引，如果没有这样的列，会隐式定义主键进行索引
- 为了充分利用聚簇索引的聚簇特性，一般需要设置有序的主键，无序的UUID、HASH等都不建议使用

二级索引(非聚簇索引)：用于对其他列进行快速索引，也就是创建新的对其他列的索引，可以找到对应的主键，再回到聚簇索引中查找对应的数据(回表操作)
- 索引覆盖：索引覆盖指索引中包含查询条件中的字段，索引覆盖的索引，查询时索引会直接返回结果，而不需要到数据表进行回表查询，减少磁盘访问
- 索引下推：索引下推指索引中包含部分查询条件中的字段，这些字段会在索引时筛选，再到数据表进行回表查询，进行其他判断。主要是优化无法使用到的、在索引的字段筛选，减少回表
```
-- 索引下推开启(默认)，off关闭
set optimizer_switch = 'index_condition_pushdown=on';

-- 临时关闭
select /*+ no_icp (t1) */ * from t1 where ...;
```
在构建二级索引时，可以使用多列联合构建，称作联合索引
聚簇索引和二级索引比较
- 二级索引的最终结点存储的是数据的主键
- 二级索引可以创建多个，聚簇索引最多创建一个
- 聚簇索引的修改删除效率低，查询效率高；数据修改，二级索引不一定需要修改

4.2 创建和删除索引

功能逻辑上看，索引的常见类型有
- 普通索引：索引列不附加任何条件，只是用于提高查询效率，可以创建在任何列上
- 唯一索引：声明了UNIQUE约束的字段会自动添加唯一索引，之后添加唯一索引，也会自动添加唯一约束
- 主键索引：主键约束上会自动添加唯一非空索引，一张表中最多有一个主键索引
- 全文索引：全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。全文索引非常适合大型数据集，对于小的数据集，它的用处比较小
  - 使用参数FULLIEXT可以设置索引为全文索引。在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值
  - 全文索引只能创建在文本字符串类型及其系列类型的字段上，查询数据量较大的字符串类型的字段时，使用全文索可以提高查询速度
  - 全文索引有两种典型的类型，自然语言的全文索引和布尔索引，在MySQL5.7.6开始才内置ngram全文解析器，之前不支持对中文分词
  - 现在全文索引已经逐渐被solr和elasticsearch等搜索引擎所代替
- 空间索引：使用参数SPATIAL可以设置索引I为空间索引。空间索引I只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率
  - MySQL中的空间数据类型包括GEOMETRY、POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值
从索引列的个数上区分，索引的类型有
- 单值索引：仅对单列索引
- 联合索引：对多列索引，依据这些列依次进行排序
  - 索引列的次序会影响索引效果，存在最左前缀原则，也就是查询条件中必须包含最左侧的某些前缀列，才能用上联合索引
  - 如果其中的列被删除，将仅删除该列上的多列索引，其他索引列的次序不变；如果其中所有的列都被删除，则索引被删除

存储引擎对索引的支持情况

存储引擎	支持情况
`InnoDB`	支持`B-tree`、`Full-text`等索引，不支持`Hash`索引
`MyISAM`	支持`B-tree`、`Full-text`等索引，不支持`Hash`索引
`Memory`	支持`B-tree`、`Hash`等索引，不支持`Full-text`索引
`NDB`	支持`Hash`索引，不支持`B-tree`、`Full-text`等索引
`Archive`	不支持`B-tree`、`Hash`、`Full-text`等索引

创建索引
- 隐式方式：在创建表时会自动对唯一列、外键和主键创建索引
- 显式方式：创建表时声明索引或之后使用创建索引语句创建索引
MySQL
MySQL索引选项
- 索引名：可以不提供，对索引操作时需使用
- [UNIQUE|FULLTEXT|SPATIAL]：索引类型，对应唯一、全文、空间索引，不写为普通索引
- [ASC|DESC]：索引顺序，8.0新特性，默认升序，此顺序和查询顺序一致时，效果更好
- [VISIBLE|INVISIBLE]：索引可见性，8.0新特性，默认可见，索引不可见时，不会被优化器使用
  - 在5.7及之前，如果删除索引之后发现有性能或其他问题，需要重新创建，操作成本很高
  - 在8.0之后，可以通过隐藏索引的方式禁止优化器使用该索引(软删除)，然后再考虑删除
  - 主键不能设置为隐藏索引
  - 隐藏可以更好地观察索引对查询的影响，隐藏的索引仍会进行更新，如果长期隐藏可以考虑删除
  - 可以设置SET SESSION optimizer_switch="use_invisible_indexes=on"使优化器使用隐藏索引
```
-- 创建表时附加索引
CREATE TABLE tb_student (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  self_info VARCHAR(255),
  -- [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名(列名 [ASC|DESC], ...) [VISIBLE|INVISIBLE]
  INDEX idx_name (name),
  -- 全文索引，仅对前50个字符进行全文检索
  FULLTEXT INDEX futxt_idx_info (self_info(50))
)

-- 全文索引不使用 LIKE 搜索，使用 MATCH AGAINST 搜索
-- 速度比LIKE搜索要快很多，但存在精度问题
SELECT * FROM tb_student
WHERE MATCH (other) AGAINST ('hello world');


-- 创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名
ON 表名 (列名 [ASC|DESC], ...) [VISIBLE|INVISIBLE];

ALTER TABLE 表名 ADD
[UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名(列名 [ASC|DESC], ...) [VISIBLE|INVISIBLE];
```

删除索引：如果需要大量插入数据可以考虑暂时删除某些索引，然后在添加完成后再次添加索引

MySQL

-- 通过删除主键约束的方法删除索引
-- 注意添加 AUTO_INCREMENT 的列不能删除唯一索引
ALTER TABLE 表名 DROP PRIMARY KEY;

-- 删除索引
ALTER TABLE 表名 DROP INDEX 索引名;
DROP INDEX 索引名 ON 表名;

-- 修改索引可见性
ALTER TABLE 表名 ALTER INDEX 索引名 INVISIBLE;

4.3 索引的设计原则

适合添加索引的情况
- 字段有唯一的限制，甚至可能是有唯一性的多个字段
- 频繁被WHERE查询的字段
- 经常进行GROUP BY分组和ORDER BY排序的字段，如果有多个可以使用联合索引
  - 如果没有包含分组字段和排序字段的联合索引，使用的是分组列的索引
  - 如果是联合索引要求先写分组字段，再写排序字段
- 对需要在大量更新和删除操作中使用的WHERE限制字段添加索引，如果需要更新的是非索引字段，提升效果更明显
- 需要去重DISTINCT()的字段需要创建索引
- 对多表连接条件中的字段添加索引
索引优化规则
- 使用尽量小的数字类型或仅取字符串前缀添加索引，减少索引树的存储空间和查询IO次数
  - 索引设定的字符串前缀长度可以通过区别度COUNT(DISTINCT left(字符串列, 长度))/COUNT(*)确定，选择的长度应该能保证区别度足够高
  - 如果是前缀索引，在使用ORDER BY时无法使用索引，防止索引以外的部分差异影响排序结果
- 区分度高的列适合添加索引
- 将最常用的索引列放在联合索引的最左侧
- 联合索引应该考虑把需要进行范围查询的列放在最后
- 如果确定是需要对多个字段进行索引时，建立联合索引效果优于单值索引
- 单表索引最好不超过6个，索引会占用空间并影响插入、删除性能。优化器最多只会使用一个索引，过多的索引也会增加优化器选择合适索引的开销
- 索引指定了数据存储的顺序，对使用聚簇索引的引擎创建的表设置主键自增AUTO_INCREMENT，避免主键插入忽小忽大导致插入需要额外的调整时间，影响性能，如果有主键定义要求的话，可以考虑使用算法生成主键
不适合添加索引的情况
- 列不在WHERE中使用的字段不要设置索引
- 数据量小的表(比如不到1000行)，不需要添加索引
- 有大量重复数据的列(比如数据重复度高于 $10\%$ )，不要添加索引
- 避免对经常更新或删除的表添加大量索引
- 不建议用无序的值作为索引(比如UUID)
- 不需要定义冗余的索引，联合索引可以替代对应的仅包含某些前缀索引，对一个列上的进行多种类型索引(比如普通索引和唯一索引)也是没有必要的

4.4 索引选择规则

是否使用索引是cost优化器决定的，和查询开销有关。同时，语包是否使用索引跟数据库版本、数据量、数据选择度都有关系
每次查询只能使用一个索引结构，优化器会决定使用哪个索引
- 对于查找的情况，尽可能选择包含最多需要查找列的索引，同时要注意被选择的索引必须满足最左前缀原则
```
-- 将选择第三个索引，因为第四个索引中第一个列不在查询中
CREATE INDEX idx_age ON student (age);
CREATE INDEX idx_ager_classid ON student (age,classId);
CREATE INDEX idx_age_classid_sex ON student (age,classId,sex);
CREATE INDEX idx_name_age_classid_sex ON student (name,age,classId,sex);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classId=1 and sex='M' and age=19;
```
- 对于有多个单列索引可以选择的情况，优化器会选择过滤效果最好的字段

4.5 索引失效原因

索引失效规则：下列是常见的索引失效情况

索引未能覆盖查询，包括索引列不在查询中、聚合索引最左侧列不在查询中
优化器认为索引成本过高，查询条件可能匹配大约15-30%的行，索引的成本可能大于扫描全表的成本
- 数据量大时，由于有列不在索引中需要大量回表操作，导致索引失效
- like '%'通配符开头，可匹配的行过多，导致索引失效，如果有相关需求，最好使用搜索引擎完成
- 在某个索引列使用范围查询(>,<,between等)后，对应索引中右侧索引列失效
```
-- 有索引(age, classId, name)，最后的name列不被使用，索引长度key_len为10
EXPLAIN SELECT SOL NO CACHE FROM student
WHERE student.age=30 AND student.classId>20 AND student.name 'abc';
```
- 不等于(!=,<>)查询，无法使用索引覆盖，回表成本过高，导致索引失效
- is NULL一般可以使用索引，is not NULL一般不可以。如果数据分布标明，有很多为空的数据时，情况可能相反
- or连接了非索引列，导致索引失效
```
-- 有age索引，无classId索引，索引失效
-- 有两个单独索引，两个索引都使用，额外信息中显示using union(idx_age,idx_classId)
-- 有聚合索引(age,classId)，也相当于只有age索引，索引失效
EXPLAIN SELECT * FROM student WHERE student.age=30 OR student.classId>20;
```

对字段使用函数、计算、类型转换，参与比较的值不是列原值，导致索引失效

-- like 不以通配符开头，可以正常使用索引
EXPLAIN SELECT FROM student WHERE student.name LIKE 'abc';
-- 使用 left 函数导致无法使用索引
EXPLAIN SELECT FROM student WHERE LEFT(student.name,3)='abc';

-- 对字段进行计算导致无法使用索引，如果条件为 stuno = 900000 - 1 可以使用索引
EXPLAIN SELECT id,stuno,NAME FROM student WHERE stuno + 1 = 900000;

-- 字符串和数字比较，隐式类型转换，无法使用索引
EXPLAIN SELECT id,stuno,NAME FROM student WHERE stuno = '900000';

字符集转换导致索引失效