索引——高效查询的关键

2. 索引——高效查询的关键

2.1探索索引的重要性与必要性

• 如果查询的时候，没有用到索引就会全表扫描，这时候查询的时间复杂度是On
• 当数据量很大时例如有1000万条数据甚至更多时，全表扫描将会大大降低查询速率，如何优化呢？就需要给数据建立"目录"。
• 索引类似于书籍的目录，可以减少扫描的数据量，提高查询效率。

2.2深入解析索引底层的数据结构及其特性

    从数据结构的角度来看，MySQL 常见索引有 B+Tree 索引、HASH 索引、Full-Text 索引。

每一种存储引擎支持的索引类型不一定相同，表中总结了 MySQL 常见的存储引擎 InnoDB、MyISAM 和 Memory 分别支持的索引类型。

2.2.1Hash索引的工作原理及应用场景

2.2.1.1Hash索引工作原理

hash索引存储了索引字段的hash值和数据所在磁盘文件指针，如果来了一句sql： select * from tab where id= 1 那么mysql会怎么去执行这条sql语句：
a、将id = 1 做一次hash运算得到hash为123
b、拿到hash为123在索引中去找123的节点
c、节点所对应的数据就是数据内容所在磁盘文件的指针
d、通过一次磁盘I/O得到所有的内容，即id= 1，name=张三。
e、最后返回结果。

2.2.1.2 Hash索引适用场景

• 哈希索引只包含哈希值和行指针，而不存储字段值。
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
• 哈希索引也不支持部分索引列匹配查找。例如，在数据列(A, B)上建立哈希索引，如果查询只有数据列A，则无法使用该索引。
• 哈希索引不支持任何范围查询，包括=、in()、<=>。 由上述劣势可分析，哈希索引一般适用于：不需要做排序和范围查询的需求。

2.2.2 B树索引特点

B树在线生成网站

B树叫做多路平衡查找树，多路就是说一个结点下面可以有多个子节点。(B树理解为多路的搜索树，在调整树结构的时候，中间元素向上分裂)

2.2.3 B+树索引的独特之处与优势分析

B树在线生成网站
B+树就是B树的一种变种，所有的数据都会出现在叶子结点，非叶子结点只是起到索引的作用。

MySQL索引数据结构对经典的B+树做了优化，将叶子节点的单链表修改成了双链表，就形成了带有顺序指针的B+树，提高区间访问的性能。

B树和B+树的区别：

1. B树的所有结点既存放键(key)，也存放数据(data)，但是B+树只有叶子节点存放key和data，非叶子节点只存放key。
2. B树的叶子节点是独立的；B+树的叶子节点有一条链表指向相邻叶子节点。
3. B树的检索过程不稳定，可能还没有到达叶子节点，检索就结束了。但是B+树每次检索数据都要检索到叶子节点，查找比较稳定。
4. B+树的范围查询只需要对链表进行遍历即可。

B+树三层大约能存放多少数据
(ps: 指针存储的是磁盘数据页的位置，一个页大小16k)

show global status like 'Innodb_page_size'; # 16KB

假设我们的一行数据大小是1K，那么我们一个叶子节点就可以存16行数据。

非叶子节点存放的是主键值与指针，所以这里假设主键类型为bigint，占用8Byte，指针可以设置为占用6Byte，总共就为14Byte，这样就可以算出一个节点大概可以存放多少个指针了（指针指向下一层节点），大概为16KB/14Byte=1170个。

由此，可以推算出，2层B+树的话，可以存放117016=18720行数据。3层B+树的话，可以存放11701170*16=21902400行数据，也就差不多2000w条数据了。

2.3 Innodb&MyISAM在存储结构的区别

2.3.1Innodb——聚簇索引

• Innodb存储引擎中，其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分开的，其数据文件本身就是按照B+树组织的一个索引结构，树节点的data域保存了完整的数据记录。这个key就是表的主键，这被称为聚簇索引。
  InnoDB只有一个ibd(Innodb data)文件，数据文件本身就是索引文件。叶子节点直接存放索引和数据，而不是存放索引和地址。
  

2.3.2MyISAM——非聚簇索引

• MyISAM存储引擎中，B+树叶子节点的data存放的是数据记录的地址。在检索数据时，首先按照B+树去检索数据，取出data域的值，然后以data域的值为地址读取相应的数据记录。这里被称为非聚集索引。
  MyISAM存储引擎中索引文件MYI（MyISAM Index)和数据文件MYD（MyISAM Data)是分离的。
  MyISAM存储引擎无论是主键索引还是非主键索引，叶子节点存储的都是索引列+数据的地址值。
  

聚集索引 - 叶子结点包含了完整的数据记录,比如Innodb的主键索引就是聚集索引
非聚集索引 - 叶子节点只包含索引和数据地址，比如MyISAM的主键索引就是非聚集索引

2.4全面了解索引的分类与利弊

2.4.1索引的分类

• 主键索引

• 定义：建立在表的主键列上的索引，每个表只能有一个主键索引 当我们没有定义主键索引时，MYSQL会指定从左到右的第一个加了唯一索引和非空约束的列建立聚集索引，用它来代替主键索引。
• 特性：
  • 强制唯一性约束，不允许NULL值
  • 通常与表的物理存储顺序相关（如InnoDB的聚簇索引）
  • 是表的主标识符，外键关系的基础
• 应用场景：
  • 作为表的行标识符
  • 需要快速通过主键值检索单行数据时
  • 建立表间关系时

• 唯一索引

• 定义：确保索引列或列组合的值唯一的索引
• 特性：
  • 允许NULL值（但通常只能有一个NULL值）
  • 可以创建在多个列上（复合唯一索引）
  • 不同于主键索引，一个表可以有多个唯一索引
• 应用场景：
  • 需要确保业务数据唯一性的列（如身份证号、邮箱等）
  • 替代主键索引（当主键是复合键或非业务字段时）

• 普通索引

• 定义：最基本的索引类型，没有唯一性约束
• 特性：
  • 仅用于加速查询，不强制数据唯一性
  • 可以包含NULL值
  • 创建和维护成本相对较低
• 应用场景：
  • 常用于WHERE条件、JOIN条件和ORDER BY子句中的列
  • 查询频繁但不需要唯一约束的列

• 联合索引

• 定义：建立在多个列上的索引，也称为复合索引，其本质是一个二级索引
• 特性：
  • 索引顺序很重要（遵循最左前缀原则）
  • 可以是唯一索引或普通索引
  • 比单列索引能覆盖更复杂的查询条件
• 应用场景：
  • 多列组合查询频繁的情况
  • 需要覆盖索引优化查询性能时
  • 示例：INDEX idx_name_age (name, age)

• 全文索引
  • 用 like + %对于大量的文本数据关键字检索，是不可想象的,通过关键字的匹配来进行查询过滤,就是全文索引
  • 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
    create fulltext index content_tag_fulltext
    on fulltext_test(content,tag);
  • 和常用的模糊匹配使用 like + % 不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如
    select * from fulltext_test
    where match(content,tag) against(‘xxx xxx’);

• 应用场景：
  • 文本内容的模糊匹配和关键词搜索
  • 替代低效的LIKE '%keyword%'查询
  • 搜索引擎类应用

2.4.2索引的利弊

• 索引的优点

① 建立索引的列可以保证行的唯一性，生成唯一的rowId ② 有效缩短数据的检索时间 ③ 加快表与表之间的连接 ④
为用来排序或者是分组的字段添加索引可以加快分组和排序顺序

• 索引的缺点

① 创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大 ②
创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间） ③
会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长

• 什么情况下需要建立索引

① 数据量大的，经常进行查询操作的表要建立索引。 ② 用于排序的字段可以添加索引，用于分组的字段应当视情况看是否需要添加索引。 ③
表与表连接用于多表联合查询的约束条件的字段应当建立索引。

2.5 常见疑问与概念澄清

2.5.1 回表查询详解

当查询语句中需要返回的列不在索引列上时，即使通过索引定位了相关行，仍然需要回表获取其他列的值。

回表的代价：
组合索引idx_name_birthday_phone_number执行如下SQL：

CREATE TABLE person_info(
    id INT NOT NULL auto_increment,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);

                        
select * from person_info where name > 'Asa' and name < 'Barlow';

因为查询列是全部sql显然也是需要回表操作，不过这里需要提到两个概念随机IO和顺序IO。

根据条件name > ‘Asa’ and name < 'Barlow’取这中间的数据大概率是一片连续的空间，所以我们可以通过获取一个数据页或者几个数据页就能很快得到数据，这种读取磁盘数据的过程称为顺序IO。

而我们的二级索引不包含country列，所以需要根据在name > ‘Asa’ and name < 'Barlow’范围内的所有记录id，去聚簇索引中再次查询完整记录，而id可能是随机分配到不同的数据页，这个查询难度远高于name列值的查询，这种读取方式被称为随机IO。

大多数情况下顺序IO性能远高于随机IO。

回表的数据量大那么使用组合索引的效率越低，如果在上面 name > ‘Asa’ and name < ‘Barlow’ 范围内的数据占据整个表数据的90%以上那么还不如采用全表扫描，那么什么时候采用全表扫描，什么时候采用索引查询呢？这取决于优化器，而优化器参考的就是回表的记录数。

总结：回表查询是随机IO，性能慢，若回表查询条数过多，还不如全表扫描

2.5.2 索引覆盖的实际意义和示例展示

查询的字段都在某个索引上，并且没有超过最大索引长度限制，MySQL可以直接从索引中获取所需数据，而无需回表。
如下图：表中有id,name,age,sex,给name和age建立联合索引的索引结构如下：若执行Select name，age from test where name='张三'；会索引覆盖，因为name,age在该索引上都能查到，不需回表，若执行Select * from test where name='张三'；不会索引覆盖，因为sex不在该索引上都能查到，需回表。

2.5.3 索引下推的概念及场景演示

什么是索引下推（Index Condition Pushdown，ICP）呢？我们通过例子来了解下。

一开始创建的表中建立name和age联合索引，查询 name 以 ‘L’ 开头，并且 age 为 17 的人员信息。

select * from t_user where name like 'L%' and age = 17;

在不用索引下推的情况下，根据前边"最左匹配原则"描述的那样，该查询在联合索引中只有 name 列可以使用到索引，age 列是用不到索引的。在扫描 (‘name’, age) 索引树时，根据 name like ‘L%’ 这个条件，可以查找到 LiLei、Lili、Lisa、Lucy 四条索引数据，接下来，再根据这四条索引数据中的 id 值，逐一进行回表扫描，从聚簇索引中找到相应的行数据，将找到的行数据返回给 server 层。server 层中，再根据 age = 17 这个条件进行筛选，最终只留下 Lucy 用户的数据信息。

在使用索引下推的情况下，存储引擎层还是先根据 name like ‘L%’ 这个条件，查找到 LiLei、Lili、Lisa、Lucy 四条索引数据，不过接下来不是直接进行回表操作，而是根据 age = 17 这个条件，对四条索引数据进行判断筛选，将符合条件的索引对应的 id 进行回表扫描，最终将找到的行数据返回给 server 层。（也就是我们把本应该在 server 层进行筛选的条件，下推到存储引擎层来进行筛选判断了。这个下推的前提是索引中有 age 列信息）
使用索引下推的过程，如下图示：

2.5.4 索引跳跃的技术解读及案例

索引跳跃是一种优化技术，用于在多列索引中查找数据，即使查询不满足最左匹配原则，也能达到走联合索引的效果。

假设我们有给性别（sex）和姓名（name）建立联合索引，执行以下查询：Select name form table where name ='小明'

正常情况下因不满足最左匹配原则会导致索引失效，但因sex的选择性很差，开启优化后该sql被转化为Select name form table where sex='男' and name ='小明'union Select name form table where sex='女' and name ='小明'，（当然走不走索引跳跃还是看优化器的选择，若sex字段重复度很低，优化器会选择走全表扫描）索引跳跃会列出所有sex的可能情况，最后用union合并，达到走索引的目的

2.5识别导致索引失效的场景

索引失效的常见场景

1. 不满足最左匹配原则：对于联合索引，如果查询条件不包含索引的最左列，索引将不会被使用。例如，如果有一个联合索引是(A, B, C)，那么查询条件必须以A开头才能利用该索引(1)。
2. 使用了SELECT *：在查询时使用SELECT *可能会导致无法使用覆盖索引，因为它会检索所有列，包括非索引列。指定具体的索引列可以提高查询效率。
3. 索引列参与运算：当索引列在查询中参与运算时，如WHERE id + 1 = 2，索引将不会被使用，因为数据库需要对每一行的id进行计算。
4. 索引列使用了函数：如果在查询条件中对索引列使用了函数，如WHERE SUBSTR(column, 1, 3) = ‘abc’，索引也将不生效。
5. 使用了错误的LIKE语句：当LIKE语句的模糊匹配符%位于模式的开始时，如WHERE column LIKE ‘%abc%’，索引将不会被使用。
6. 类型隐式转换：如果查询条件中的数据类型与索引列的数据类型不匹配，如将数字与字符串比较，索引可能不会被使用。
7. 使用OR操作：如果查询条件使用了OR，而其中一个条件没有索引，那么整个查询可能不会使用索引(1)。
8. 不等于比较：使用<>或!=进行比较时，索引可能不会被使用，尤其是当查询结果集占比较大时。
9. ORDER BY导致索引失效：如果ORDER BY的列不是索引列，或者排序的方向与索引定义的方向不一致，索引可能不会被使用。

2.6 Explain详解

explain查看执行计划的字段：

explain分析sql底层执行计划

2.6.1. id

id表示表的执行顺序，有几个select就有几个id。
id列越大，执行优先级越高，id相同就从上到下执行。

2.6.2. select_type

1. simple：简单查询，查询不包括子查询和union
   explain select * from actor where id = 1;
   

2. primary：复杂查询中最外层的select的select_type为primary

3. subquery：包含在select中的子查询(比如：在select关键词后面的查询)，不在from语句中

4. derived：包含在from字句中的子查询，MySQL会将结果存放在一个临时表，也叫做派生表

# 关闭mysql5.7新特性对衍生表的合并操作
set session optimizer_switch = 'derived_merge=off';

# 查看执行计划
explain 
select (select 1 from actor where id = 1)
from (select * from film where id = 1) der;

2.6.3. table列

表示explain的一行正在访问哪个表。
当from子句中有子查询时，table列是<derivedN>，表示当前查询依赖 id = N的查询，于是先执行id = N的查询。

2.6.4. type列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > All

1. const
   const表示表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键索引或唯一索引的字段作为查询条件。
2. system
   system：system是const的特例，表中只有一行记录的情况下为system。
3. eq_ref
   当连表查询时，前一张表的行在当前这张表中只有一行数据与之对应。是除了system和const之外最好的join方式，一般用于使用主键或者唯一索引的字段作为连表条件。

explain
select * from film_actor
left join film on film_actor.film_id = film.id;

4. ref
   ref：不是使用主键或者唯一索引，而是使用普通索引作为所有条件，查询结果可能会找到多条符合条件的行。

explain select * from film where name = 'film1';

5. range
   range表示对索引列范围查询，比如出现在in()，between,<,>=等操作中，使用一个索引检索给定范围的值。

explain select * from actor where id > 1;

6. index
   如果主键索引和二级索引都有全部数据，那么查询全部数据的时候，会走二级索引，因为主键索引叶子节点数据比较多，树太大了。但是如果只有主键索引有全部数据，就会走主键索引
   index表示遍历了整颗索引树，一般是扫描整个二级索引，这种扫描不会从索引树的根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度相对还是比较慢的，这种查询一般会发生索引覆盖，二级索引一般比较小，索引这种通常比ALL快一些

explain select * from film;

7. ALL
   ALL：即走全表扫描，扫描整个聚簇索引的所有叶子节点，通常情况下这需要增加索引来进行优化了。

explain select * from actor;

2.6.5. possible_keys

possible_key 列表示MySQL执行查询计划时可能用到的索引，如果这一列为NULL，则表示没有可能用到的索引；这种情况下，需要检查WHERE语句中所使用的列，看是否可以给这些列中某个或者多个添加索引的方法来提高查询性能。

2.6.6. key

key列表示MySQL中实际用到的索引。如果为NULL，则表示未用到索引。

2.6.7. key_len

key_len列表示MySQL实际使用的索引的最大长度(实际使用的索引长度)，当使用联合索引时，有可能是多个列的长度和。在满足需求的前提下越短越好。如果key列显示NULL，则key_len也显示NULL。

explain select * from actor where id  = 1; -- key_len长度为4

explain 
select * from film_actor 
where film_id = 1 and actor_id = 1; -- key_len长度为8

2.6.8. extra列

extra展示的是额外信息，常见的值如下：

1. Using Index
   Using Index：表示进行了索引覆盖，不需要回表，查询效率较高。
   覆盖索引: 覆盖索引一般针对的是辅助索引，整个查询结果通过辅助索引就可以直接拿到结果，不需要通过辅助索引树找出主键，再通过主键去主键索引树里获取其他值。

explain 
select film_id from film_actor where film_id = 1;

2. Using where
   表明查询使用了WHERE字句进行条件过滤，一般在没有使用到索引的时候会出现。

explain select * from actor where name = 'a'

3. Using Index Condition
   Using Index Condition表示查询优化器选择了使用索引下推这个特性
4. Using Temporary
   MySQL需要创建临时表来存储查询的结果，常见于ORDER BY和GROUP BY

explain select distinct name from actor -- Using Temporary
explain select distinct name from film -- Using Index

5. Using filesort
   ORDER BY使用的列上没有建立索引，或者虽然有索引但是查询条件不能有效地利用这个索引来满足ORDER BY的需求。

如果ORDER BY子句中的列顺序与索引定义的顺序不一致，或者部分列的排序方向（ASC或DESC）与索引定义不符，MySQL可能无法使用该索引来避免文件排序

这种情况下也是需要考虑使用索引来进行优化。