索引（index/key）：一种数据组织方式，用于加速查询。
索引的加速：

• 1、提取key值，key为某字段，value为对应记录。
• 2、按key值生成树形结构（按大小范围）
• 3、从根节点开始查找，找到树枝，最后找到叶子节点数据，只需要几次查询就能找到数据。

一定要搞明白，索引是数据库必问的知识点。

索引需要搞明白的问题

让我们带着以下问题展开对索引的探索
1、为何索引叫key
2、索引是如何加速查询的，它的原理是啥？
索引模型/结构从二叉树-》平衡二叉树-》b树最后到b+树，每种树到底有什么问题最终
演变成到了b+树
3、为何b+树不仅能够加速等值查询，还能加速范围查询
4、什么是聚集索引丨，什么是辅助索引
5、什么情况下叫覆盖了索引
6、什么情况下叫回表操作
7、什么是联合索引l，最左前缀匹配原则
8、索引下推，查询优化
9、如何正确使用索引引?

索引介绍

什么是索引

索引：mysql中的一种数据结构，又称之为key；是数据的一种组织方式。
B+数：索引按树形结构组织，该树叫B+树。

为何用索引

作用；优化查询速度

正确看待索引

错误认知：

• 错误1：网站慢，想到要加索引（软件开发之初就应该加索引）
• 错误2：索引越多越好（索引用于加速查询，会降低写效率建立过多索引树，一个update语句需要同步修改所有索引树。）

储备知识

硬盘IO时间

机械磁盘：硬盘IO时间=寻道时间+旋转延迟+传输时间

转速：7200 r/min = 120 r/s = 0.008s/r
一次IO延迟时间（读取一个磁盘块的时间）
    平均寻道时间（5ms）
    平均延迟时间（4ms）
CPU：9ms 时间可以执行约450万条指令了

磁盘预读

innodb 存储引擎，每一次IO读取的数据称之为一页（16K），而不是单条数据。

索引原理

索引的根本原理：降低硬盘IO次数

• 索引绑定在行上
• 通过索引查询行

索引分类（了解）

种类：B+树、hash索引、full text 全文索引

• hash 索引：等值查询快，范围查询慢
• B+树：等值/范围都快
• 全文索引：通过关键字模糊查询全文

InnoDB：支持事务，支持行级别锁定，支持 B-tree（默认）、Full-text 等索引，不支持 Hash 索引；
MyISAM：不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory：不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引。

注意：innoDB不支持hash索引，所以我们无法创建hash索引，但它自己有一个自适应hash索引，

B+树

创建索引两大步骤

1、提取索引字段值当作key (value就是这一行记录)
2、以key值比大小，生成树形结构

以该字段为查询条件，则会命中该索引。

innodb存储引|擎默认的索引|结构为B+树，而B+树是由二叉树、平衡二叉树、B树再到B+树一路演变过来的

二叉树

组成：二叉树由根、树枝、树叶组成，根和树枝称为非叶子节点，树叶称为叶子节点。
树的高度：二叉树的层数，高度越高查询速度越慢。
二叉树的特点：左节点大于右节点
二叉树的问题：树枝不均衡的情况下，可能生成一条单链，效率跟直接查询一样。

平衡二叉树

描述：解决二叉树的平衡问题，左右树枝的高度一样，不会生成单链。
特点：查找效率比二叉树更稳定
问题：每个节点只放1条数据，海量数据下平衡二叉树会很高，效率变低。

B 树

描述：B树在每个节点存放多条数据，然后通过指针指向子节点。
节点存储单位：innodb 一页 == 操作系统 一个磁盘块 == 二叉树一个节点

特点：以页作为树节点

• 树的高度比平衡二叉树低（因为节点存了一页数据）
• 根节点常驻内存，不用每次都从磁盘查询。
• 每个节点存放多个key，用指针指向子节点

问题：每个树节点都存储key，value，整个树占存储空间很大。

B+树

B+树：

• 非叶子节点只存放索引-->一页（16k）能存放更多的索引--> 3层能查找10亿数据。
• 叶子节点存放行数据，是按顺序存的（排序快）
• 各个非叶子节点页之间有双向链表链接，到指向叶子节点是单向链接，叶子节点之间也有双向链接。（范围查询快）

特点：

• B+树擅长order by，默认就是排好序的
• 根节点常驻内存，不用IO
• 查询10亿数据，只需三次IO

三大特性：

1、非叶子节点只存放key值，叶子节点存放key，value（非叶子节点能放更多的key，树更宽，IO效率更高）
2、叶子节点之间有双向链表，范围查询更快
3、叶子节点内的key值是单向链表，叶子节点与叶子节点之间是双向链表，即全都排过序了。(排序快)

作用：

• 加速等值查询
• 加速范围查询
• 加速排序

字符串比大小：对应位置的字符去比大小，如abcd < b，越往后的字母越大

聚集索引与非聚集索引

每个索引会生成一棵B+数，索引分为两类。
分类1：聚集索引（聚簇索引、主键索引、一级索引）
分类2：非聚集索引（非聚簇索引、辅助索引、二级索引）

聚集索引

描述：以主键字段值为key构建的索引

• 叶子节点存放key=主键值，value=本行记录
• 即：表中数据都聚集在叶子节点上
• 一张表只能有一个聚集索引

innodb 必须指定主键，不指定也会自动创建主键（但没法调用了）。innodb也称为索引组织表。最好用唯一、占用空间小的字段当主键。

非聚集索引

描述：以非主键字段值为key构建的B+树索引

• key：该字段的值
• value：本行的主键值（即指向本行的主键值）
• 一张表能有多个二级索引
• 所以二级索引构建时，会将所有的主键key值全量复制一份。

覆盖了索引&回表操作

覆盖索引：在命中索引的基础上，只在本索引树的叶子节点就找到了我们想要的数据。

回表操作：在命中辅助索引的基础上，在辅助索引的叶子节点没有找到想要的数据，拿着对应的主键字段值去聚集索引再查找一次。

命中了主键索引：一定覆盖了索引
命中了二级索引：看查询的字段中有没有包含主键索引这一列，包含就覆盖了索引，没有包含则回表操作。

mysql 索引管理

索引分类

聚集索引：primary key 主键，带约束（不为空且唯一）
唯一索引：unique 带约束（不能重复）
普通索引：index 加速查询
联合索引：联合主键索引、联合唯一索引、联合普通索引

索引操作

创建/删除：建表时指定，建表后新增/修改/删除

alter table t1 drop unique index t1;(没有指定名字自动生成)
drop unique index xxx on t1;

tips：300万数据，大概200M
建索引后ibd文件会变大，因为非叶子节点增加了。
删除索引，ibd文件里的数据不会被删除，也没法删除。

如何正确使用索引

索引命中未必会加速，在使用索引时需要注意：

1、索引选择：采用非空、唯一、占用空间小的字段
2、全表查询时：无法使用索引，那就优化开发层面分页查询
3、针对等值查询：

• 在重复程度低的字段建立索引能加速
• 在重复程度低的字段建立索引效果一般（多次判断增加IO）
• 在占用空间大的字段建立索引效果一般（树更高增加IO）

4、针对范围查询：

innodb 存储引擎能加速范围查询，但是查询范围越大，越慢（范围查询相当于多次等值查询）

# 范围查询符号
1、between and 范围
2、like 模糊匹配（范围：%cuc>cuc%>cuckoo%） 
3、!= < > >= <= 范围查询

5、条件字段参与运算

运算会让索引失效。

不要让条件字段参与运算，或者说传递给函数；
因为B+树存的都是key值，每个key都计算耗费了极大时间，降低查询效率。

6、重复字段索引问题

为大量重复字段建立索引，因无法判断key大小，B+树形成了长链，效率变低。

联合索引&最左前缀匹配原则

联合索引：key 是多个字段的组合，但只会创建一棵树。

• 步骤1：提取key值
• 步骤2：比大小构建索引树（从左变开始一个字段一个字段对比大小）

创建语句：create index on t1(id,name,age)

最左前缀匹配：

• 命中条件：id name age
• 命中条件：id name
• 命中条件：id age
• 命中条件：id

即：查询条件中涉及联合索引的多个字段值，则必须带联合索引的第一个字段。

好处：在联合索引组织时，如果第一键相同，已经对第二个键进行了排序处理，同理处理第三个键，在查询需要排序时效率很高。

索引下推技术

索引下推：索引下推是一种有效的数据库优化技术，通过在索引扫描阶段应用更多的过滤条件，可以显著减少数据页访问次数，提升查询性能。在多个过滤条件中，优先选择区分度高的字段进行筛选，减少访问数据页的访问次数提高查询性能。

索引下推技术只在and条件下生效。

and

连续的and条件，所有条件都满足才会成立；

• 先判断哪个没区别，最后都要成立才行
• mysql优化器分析了所有的执行计划，找到哪个条件查询IO最少
• 然后先用该字段进行匹配筛选出最小的记录数，再用其他条件筛选。

or

连续的or条件，只需要一个条件成立最终结果就成立，范围很大。
mysql只能按条件的顺序，从左到右依次判断了。

索引下推技术有时候跟联合索引的最左前缀匹配会出现冲突：

• 最左前缀的第一个字段，不是索引下推的优先字段，此时索引下推的执行导致联合索引失效，查询很慢。
• 应该按索引下推的逻辑创建联合索引，让查询执行时用到联合索引，查询效率高。

优点：

• 减少IO操作：在索引层过滤掉更多的记录，减少页的访问次数
• 提高了查询效率：减少了不必要的数据访问和处理

适用场景：联合索引+多条件查询的场景（尤其是查询条件包含非索引列），能极大提升性能。

索引失效的情况

• 有查询条件，或者查询条件没有用到索引列
• 询结果集是原表中的大部分数据，应该是25%以上
• 引本身失效，统计数据不真实
• 询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)
• 式转换,会导致索引失效（引号包裹字符串）
• <>和 not in，or 也不走索引
• 模糊查询%在最前面,不走索引
• 独引用联合索引里非第一位置的索引列,不走索引
• 排序条件为索引，则select字段必须也是索引字段，否则无法命中

索引的使用原则

1. 在创建索引的时候，会把该列所有的数据按照btree的方式进行排序
2. 为常作为查询条件的字段建立索引
3. 限制索引的数目，不要每列都创建索引
   每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
   修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
4. 在同一列上，尽量避免创建多个索引，可以创建多个但是它们是有优先级的，先走一个就不会
   alter table student add index idx_name(name);
   alter table country add unique key uni_name(name);
5. 避免对大列建索引，在数据很长的列上创建前缀索引
6. 如果可以创建唯一索引,就创建唯一索引(该列的数据不重复)，查询速度快
7. 不要对重复度高的字段创建索引
8. 索引不要参与计算
9. 为经常要排序,分组,联合操作的列,创建联合索引
   经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。
   如果为其建立索引，可以有效地避免排序操作
10. 尽量使用前缀来索引
    创建索引的时候,可以给该列所有数据进行排序
    create index xxxx on tb(title(19)）# text类型，必须制定长度
11. 删除不再使用或者很少使用的索引
    表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管
12. 避免使用select
13. count(1)或count(列）代替 count()，ps：mysql中没有差别了
14. 创建表时尽量时 varchar 代替 char
15. 表的字段顺序固定长度的字段优先
16. 使用连接（JoIN）来代替子查询(Sub-Queries)
17. 连表时注意条件类型需一致

查询优化 explain

用于查看语句的执行计划，可以根据执行计划进行sql优化，找到最高的执行效率。

慢查询优化步骤

0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE
1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2.explain查看执行计划，是否与1预期一致(从锁定记录较少的表开始查询)
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果，不符合预期继续从0分析