MySQL索引原理以及SQL优化（二）

目录

1. 索引与约束

1.1 索引是什么

1.2 索引的目的

1.3 索引分类

        1.3.1 数据结构

        1.3.2 物理存储

        1.3.3 列属性

        1.3.4 列的个数

1.4 主键的选择

1.5 索引使用场景

1.6 索引的底层实现

        1.6.1 索引存储

        1.6.2 页

        1.6.3 B+ 树

        1.6.4 B+ 树层高问题

        1.6.5 自增 id 

1.7 innodb 体系结构

        1.7.1 buffer pool

        1.7.2 change buffer

1.8 覆盖索引

1.9 最左匹配规则

1.10 索引下推

1.11 索引失效

1.12 索引原则

2. SQL 语句速度慢处理

2.1 找到 SQL 语句

2.2 分析SQL

---

1. 索引与约束

1.1 索引是什么

• 索引是一种有序的数据结构，MySQL 中主要使用 B+ 树（InnoDB引擎）来组织索引
• 加快数据检索速度来提升数据库的查询效率（例如 "目录" 或是 "页码"）
• 按照单个或者多个进行排序

1.2 索引的目的

        提升搜索效率

1.3 索引分类

        1.3.1 数据结构

                1. B+ 树索引

                所有数据存储在叶子节点，非叶子节点仅存储索引键和指针，支持范围查询和随机访问，适合磁盘存储结构。

                2.hash 索引

                使用哈希函数将键值映射到哈希表，支持 O (1) 的等值查询，存在哈希冲突问题，但不支持范围查询。

                3.全文索引

                采用倒排索引结构，支持文本内容的模糊匹配和语义查询。

        1.3.2 物理存储

                1. 聚集索引（聚簇索引）

                数据行的物理存储顺序与索引顺序完全一致，每个表只能有一个聚集索引。主键默认创建聚集索引。

                2.辅助索引（二级索引）

                索引节点存储主键值而非数据地址，查询时需要先查索引再查主键索引，产生回表操作。可存在多个二级索引。

        1.3.3 列属性

                1.主键索引

                特殊的唯一索引，不允许NULL值。InnoDB引擎中自动成为聚集索引，保证数据物理有序性。

                2.唯一索引

                强制列值的唯一性约束，允许NULL值（但只能有一个NULL）。

                3.普通索引

                无唯一性约束的基础索引类型，允许重复值和NULL值。查询优化器可根据代价模型选择使用。

                4.前缀索引

                对字符类型字段前N个字符建立索引，通过计算选择性 确定最优前缀长度。

        1.3.4 列的个数

                1.单列索引

                在单个字段上建立的索引，查询条件精确匹配该字段时效率最高。

                2.组合索引

                在多个字段上建立的联合索引，遵循最左匹配原则。

1.4 主键的选择

        innodb 中表是索引组织表，每张表有且仅有一个主键；

1. 如果显示设置 PRIMARY KEY ，则该设置的 key 为该表的主键；
2. 如果没有显示设置，则从非空唯一索引中选择；
3. 只有一个非空唯一索引，则选择该索引为主键；
4. 有多个非空唯一索引，则选择声明的第一个为主键；
5. 没有非空唯一索引，则自动生成一个 6 字节的 _rowid 作为主键；

1.5 索引使用场景

        索引常常用在筛选条件的适合使用，例如

        where     |     group by   |  order by 

        在以下场景，不要使用

•         没有 where/group by/ order by 的场景
•         区分度不高的列
•         经常修改的列
•         表数据量少

1.6 索引的底层实现

        1.6.1 索引存储

1. 索引存储的数据结构通常是 B+ 树，而不是哈希表
2. 索引是磁盘上的有序结构，不是存在内存中的

        1.6.2 页

1. innoDB 的数据存储以 页（Page） 为最小单位，每一页大小通常是 16KB。
2. 一棵 B+ 树的每个节点对应一个或多个磁盘页
3. 数据读写以页为单位进行（减少磁盘 I/O 次数）

        1.6.3 B+ 树

1. B+树是数据库默认的索引结构
2. 每个节点存放有序的数据键值+指向子节点的指针
3. 所有数据都存放在叶子节点
4. 叶子节点之间有链表连接（范围查询快）

        1.6.4 B+ 树层高问题

1. 理想状态下，B+树的高度很低，一般在 2-4层
2. 为什么？
   因为一页（16KB）能存很多索引项（假设一项占 16字节，1页能存1024项）；所以即使存百万条数据，只要 2-3 次磁盘IO 就能找到，非常快！

        1.6.5 自增 id 

1. 很多表喜欢用 自增ID（auto_increment） 作为主键。
2. 自增ID的好处：
   插入数据总是追加到B+树的最右边；
   避免频繁分裂、重排；
   插入性能最好
   放心用，根本用不完

        相关问题

         1.为什么采用 " 多路 " 的树结构

        一个节点多条链路，相较于平衡二叉搜索树是一个更加矮胖的结构，树的高度越低，较少的磁盘io次数来检索数据

        2.为什么非叶子节点只存储索引信息

        B+ 树节点映射固定大小的磁盘数据，可以包含更多的索引信息，能快速锁定数据所在叶子节点位置

        3.为什么叶子节点依次相连

        便于范围查询，避免中序遍历回溯去查找下一个节点

总之：索引信息和数据信息的分层管理，便于高效的组织磁盘数据，快速实现单点和范围查询

1.7 innodb 体系结构

        1.7.1 buffer pool

Buffer Pool 是 InnoDB 把磁盘上的数据页、索引页、插入缓冲（Change Buffer）、自适应哈希索引等缓存到内存中的区域。
目的是：减少磁盘 I/O，提高数据库访问速度。

        特点：

1. 查询数据时优先从 Buffer Pool 取（命中则速度很快）
2. 如果没有命中，才从磁盘读入，并加入 Buffer Pool（可能引发淘汰机制，比如 LRU）
3. 包括脏页管理（数据被修改但未刷盘）机制

        1.7.2 change buffer

 Change Buffer 是 InnoDB 中专门为二级索引的插入、更新、删除操作设置的缓存区域，延迟将二级索引变更写入磁盘，从而减少磁盘 I/O。

        free list 组织 buffer pool 中未使用的缓存页；flush list 组织 buffer pool 中脏页，也就是待刷盘的 页；lru list 组织 buffer pool 中冷热数据，当 buffer pool 没有空闲页，将从 lru list 中最久未使用的 数据进行淘汰。

        原理：

1. 对于二级索引的插入/修改，不直接去磁盘更新，而是先记录到 Change Buffer。
2. 之后在一定条件（比如页被读取进内存，或系统空闲时）才真正合并到磁盘上的二级索引页。

---

为什么只针对二级索引（非主键索引）？

1. 因为主键索引（聚集索引）必须保证实时一致性。
2. 二级索引允许延迟一致，所以可以先缓存在 Change Buffer。

一级索引（主索引）：叶子节点存整行

二级索引（辅助索引）：叶子节点存主键id，查询时需要回主键索引再拿数据

CREATE TABLE user (id INT PRIMARY KEY,       -- 主键，主索引name VARCHAR(50),age INT,email VARCHAR(50),INDEX idx_name (name)      -- 二级索引
);
--id 是 主索引（一级索引）：--叶子节点：存的是整行数据，比如 {id=1, name="张三", age=20, email="[e-mail]"}--name 是 辅助索引（二级索引）：--叶子节点：只存 {name="张三", id=1}--如果通过 name 查找，还需要根据 id 再去主索引回表拿到完整那一行。

1.8 覆盖索引

查询的数据只需要索引里的字段，不用回表到原表，因此速度更快

CREATE INDEX idx_name_age ON users(name, age);SELECT name, age FROM users WHERE name = 'Tom'; -- 覆盖索引
--因为 name 和 age 都在 idx_name_age 这个索引里，不需要回表SELECT name FROM users WHERE name = 'Tom';     -- 回表了

1.9 最左匹配规则

组合索引在查询时，会优先用最左边的列开始匹配，从左到右连续匹配才能用上索引

CREATE INDEX idx_user_name_age ON users(name, age);SELECT * FROM users WHERE name = 'Tom';      --  用上索引
SELECT * FROM users WHERE name = 'Tom' AND age = 18; --  用上索引
SELECT * FROM users WHERE age = 18;           -- 用不了索引（跳过了最左的 name）

1.10 索引下推

        目的：减少回表次数，减少server层和引擎层的交互次数，从而提升查询次数

        对象：辅助索引（二级索引），普通索引和联合索引场景居多

        流程：将索引添加判断推到存储引擎中过滤数据，最终由存储引擎进行数据汇总返回给server层，减少了server层和引擎层的交互次数。

1.11 索引失效

        一些不合理的 SQL 写法，会导致原本可以使用的索引失效，导致全表扫描

• select ... where A and B 若 A 和 B 中有一个不包含索引，则索引失效；
• 索引字段参与运算，则索引失效；
• 索引字段发生隐式转换，则索引失效；
• LIKE 模糊查询，通配符 % 开头，则索引失效；
• 在索引字段上使用 select * from user where name like NOT <> != 索引失效；如果判断 id <> 0 则修改为
• 组合索引中，没使用第一列索引（最左匹配规则），索引失效；

1.12 索引原则

• 查询频次较高且数据量大的表建立索引；索引选择使用频次较高，过滤效果好的列或者组合；
• 使用短索引；节点包含的信息多，较少磁盘 IO 操作；比如： smallint ， tinyint ；
• 对于很长的动态字符串，考虑使用前缀索引；
• 对于组合索引，考虑最左侧匹配原则、覆盖索引；
• 尽量选择区分度高的列作为索引；
• 尽量扩展索引，在现有索引的基础上，添加复合索引；最多 6 个索引
• 不要 s elect *； 尽量只列出需要的列字段；方便使用覆盖索引；
• 可选：开启自适应 hash 索引或者调整 change buffer；

2. SQL 语句速度慢处理

2.1 找到 SQL 语句

•         show processlist
•         开启慢查询日志

2.2 分析SQL

•         索引     优化  where/group by/order by
•         SQL 语句        将 in 和 not in 优化成联合查询  --->   减少联合查询