Nreal
2023-11-15
目录

索引篇

# 何为索引？

索引：数据的目录

存储引擎：存储数据，为存储数据建立索引和更新、查询数据等技术的实现方法；

# 如何创建索引？

索引常用于等值查询，范围查询，可以提高查询速度；

创建索引语法：

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... ) ;
1

联合索引语法：

CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);
1

# 索引分类

  • 按数据结构分：

    B+Tree，Hash，Full-Text；InnoDB不支持hash索引，但内存结构中有一个自适应hash索引；

  • 物理存储分：

    聚簇索引，非聚簇索引；

  • 字段特性分：

    主键索引：主键字段上的索引，一张表最多只能一个主键索引，索引列不允许空；

    CREATE TABLE table_name  (
  ....
  PRIMARY KEY (index_column_1) USING BTREE
);
    1
    2
    3
    4

    唯一索引：建立在UNIQUE字段上，一张表可以有多个唯一索引，索引列值必须唯一，允许有空值；

    CREATE TABLE table_name  (
  ....
  UNIQUE KEY(index_column_1,index_column_2,...) 
);
    1
    2
    3
    4
    CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);
    1
    2

    普通索引：建立在普通字段上；

    CREATE TABLE table_name  (
  ....
  INDEX(index_column_1,index_column_2,...) 
);
    1
    2
    3
    4
    CREATE INDEX index_name
ON table_name(index_column_1,index_column_2,...);
    1
    2

    前缀索引：字符类型字段前几个字符建立的索引，减少索引占用额存储空间，提升查询效率；

    CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
);
    1
    2
    3
    4
    CREATE INDEX index_name
ON table_name(column_name(length));
    1
    2

  • 字段个数分：

    单列索引，联合索引；

# 如何查看是否使用了索引？

通过explain执行计划；

img

参数有：

  • possible_keys 字段表示可能用到的索引；
  • key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
  • key_len 表示索引的长度；
  • rows 表示扫描的数据行数；
  • type 表示数据扫描类型；

type至少要range(索引范围扫描);

# 索引使用时机？

何时适用索引？

  • 字段有唯一性；
  • 经常用于where查询条件的字段；
  • 经常用于group by 和 order by 的字段：

何时不需要创建索引？

  • 起不到定位作用的字段，索引价值就是快速定位，像where,gorupby,orderby ...
  • 区分度小的字段；
  • 表数据量太少时；
  • 经常更新的字段；

# 索引优化

  • 覆盖索引

    查询的所有字段都可以在B+Tree的叶子节点上找到，从二级索引中查询得到的记录，不需要通过聚簇索引查询获得，避免回表；

    聚簇索引和非聚簇索引区别？非聚簇索引一定要回表？

    这是按照物理存储角度分类的，区别在于：

    1. 聚簇索引中的叶子节点存放的是完整数据；
    2. 非聚簇索引叶子节点存放的是主键值；

    如果查询使用了非聚簇索引，如果查询的数据能从非聚簇索引里查询的到，就不需要回表，也就是覆盖索引；

  • 前缀索引

  • 主键索引最好自增

    InnoDB下索引的数据结构是B+Tree，是顺序存储的，如果主键非自增，插入数据时，很有可能产生页分裂，会造成大量的内存碎片，导致索引结构不紧凑，影响查询效率；

  • 防止索引失效

    1. 模糊匹配时，%开头的查询；
    2. 查询条件中对索引做了计算，函数，类型转换；
    3. 联合索引遵循最左匹配原则；
    4. where子句中，OR一半是索引列，一半不是；

# 索引的底层数据结构

适合Mysql索引的数据结构，需要满足：

  1. 尽可能少的磁盘I/O操作中完成查询；
  2. 能高效地查询某一个记录，也能高效范围查找；

InnoDB&MyISAM存储引擎底层：B+Tree；

  • 非叶子节点存放索引，叶子节点存放实际数据(索引+记录)；
  • 叶子节点之间构成一个有序链表

B+树叶子节点形成有序链表，可以方便范围查询，对于磁盘存储，顺序读取效率高于随机读取，因为它可以充分利用磁盘预读和缓存机制，减少磁盘 I/O 的次数；

B+树的节点大小是固定的，因此可以很好地利用磁盘预读特性，一次性读取多个节点到内存中，这样可以减少IO操作次数，提高查询效率；

B+树的叶子节点都存储数据，而非数据和指针混合，所以叶子节点的大小是固定的，而且节点的大小一般都会设置为一页的大小，这就使得节点分裂和合并时，IO操作很少，只需读取和写入一页；

为什么不用跳表？

跳表的索引节点通过跳跃指针连接，形成多级索引结构。这导致了跳表的索引节点在磁盘上存储时会出现数据分散的情况，即索引节点之间的物理距离可能较远。对于磁盘存储来说，随机访问分散的数据会增加磁头的寻道时间，导致磁盘 I/O 的性能下降；

# 为什么不用B树组织索引表？

  • B树的数据分散在叶子节点和非叶子节点上，B+树只在叶子节点，非叶子节点起索引作用；
  • InnoDB存储引擎中表空间中存储是以段，区，页划分，一个页的大小只有16k,所以数据量一定的情况下，b+树的非叶子节点中可以存放更多，这样树的层级也就会低一些，检索效率也会高一些！且检索的稳定度也高！
  • b树中的叶子节点是单向链表，而b+树中的叶子节点是双向链表，更加利于”范围搜索"和“排序”；

# 联合索引

使用建议：

频繁用于过滤条件的列放在索引前面；

查询过程 联合索引遵循最左匹配原则；

局部有序

# 最左前缀匹配原则

使用联合索引时，根据联合索引中字段顺序，从左到右依次到查询条件中匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，会先用该字段过滤一批数据(5.6之后的索引下推)，直到联合索引全部字段全部匹配完成，或许遇到开区间范围查询(>,<)也会停止匹配；

在使用联合索引，将区分度高的字段放在最左侧；

索引下推原理：

截断的字段不会在 Server 层进行条件判断，而是会被下推到「存储引擎层」进行条件判断，然后过滤出符合条件的数据后再返回给 Server 层。由于在引擎层就过滤掉大量的数据，无需再回表读取数据来进行判断，减少回表次数，从而提升了性能；

# 索引失效

  • 联合索引没有遵循最左前缀匹配原则；

  • 在查询条件中对索引列做了计算、函数、类型转换操作；

    个人理解：索引说到底为了定位，参与函数计算在执行期间无法定位；

  • 模糊匹配时，使用like %xx 或者 like %xx%这两种方式；

  • 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列；

  • 对索引隐式类型转换；

    如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型，会全表扫描；

    如果索引字段是整型类型，查询条件中的输入参数即使字符串，是不会导致索引失效；

如何查询索引是否失效？

查看执行计划，type字段对应值ALL，就是没有使用索引，且全表扫描；

type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

  • All（全表扫描）；
  • index（全索引扫描）；
  • range（索引范围扫描）；
  • ref（非唯一索引扫描）；
  • eq_ref（唯一索引扫描）；
  • const（结果只有一条的主键或唯一索引扫描）；
基础篇
事务篇

← 事务篇→

Theme by Vdoing | Copyright © 2021-2024