MySQL索引学习笔记

什么是MySQL索引？

索引是存储在磁盘上的一种有序的数据结构，它包含对数据表记录的引用指针，使用索引可以快速找到满足索引值的数据行。

e.g. 进行以下查询 select stu_name where stu_id = 1，如果不使用索引，就需要对数据库表所有记录依次查找 进行条件比对 即全表扫描。使用索引，可以快速定位到对应记录。

索引的优缺点

优点：

• 使用索引可以快速定位数据，避免全表扫描，提高查询效率。
• 对于排序order by、分组group by，索引可以提供有序数据，避免创建临时表和额外排序操作。

缺点：

• 索引需要额外的磁盘空间。
• 对于增删改更新操作，需要更新索引，影响更新效率。

MySQL 索引结构

• B+树索引
  • 基于B+树（多路平衡搜索树），非叶子节点存储键值和指针，叶子节点存储数据。
  • 叶子节点都在同一层，通过指针形成双向链表。
  • 每个节点通常对应磁盘中的一页（磁盘I/O的最小单位）存储。
• Hash索引
  • 基于哈希表实现，通过哈希函数将键值映射到哈希桶中。
  • 只支持等值查询（=、in），性能非常高。
• R-Tree索引
  • 空间索引，存储多维空间数据（地理坐标、几何图形等）。
• Full-text索引
  • 基于倒排索引，记录关键词到文档的映射。
  • 倒排索引由两部分组成：
    1. 词汇表：存储所有不重复的关键词，通常按字典序排序，方便快速查找。
    2. 倒排列表：每个关键词对应一个列表，记录以下信息（e.g. 1:1,[0]就表示第一个文档中关键词出现1次，位置是第一个词）。
       • 文档ID：哪些文档包含哪些词。
       • 词频：该词在文档中出现的次数。
       • 位置信息：该词在文档中具体位置。

为什么选择B+树索引？而不是红黑树、B树等

• 二叉树极端情况下会退化成链表。
• 红黑树能维持基本平衡，但是还是二叉树 每层2^(n-1)个节点，层深太高，磁盘IO次数多。
• B树多路平衡树（n阶 每个节点最多n-1个键值n个指向子节点的指针），相较于红黑树 层深不会太高。
• B+树索引的优点：
  • 实际数据存储在叶子节点，非叶子节点只存储键值和指向子节点的指针。单个节点存储在磁盘的一个页中，B+树非叶子节点相较于B树就可以存储更多键值，所以B+树高度更低，磁盘IO次数更少。
  • 所有叶子节点都位于同一层，查询路径长度固定，查找效率稳定。
  • B+树索引，叶子节点通过指针构成双向链表，方便进行范围查询。

索引分类

索引分类：

• 主键索引（primary）
  • 唯一标识表中每行数据的索引，每个表只能有一个主键索引。
  • 索引列值唯一且非空。
• 唯一索引（unique）
  • 索引列值唯一可以为空。
• 常规索引
• 全文索引（fulltext）
  • 基于倒排索引实现，关键词和文本内容的匹配。

按照存储方式分类：

• 聚簇索引
  • 索引结构和数据存放在一起，叶子节点存储完整的行数据。
  • InnoDB引擎中，如果表有主键索引，主键索引就是聚簇索引；如果没有主键索引，第一个唯一索引作为聚簇索引；如果没有主键索引也没有合适的唯一索引，InnoDB会生成rowId作为聚簇索引值。
• 二级索引
  • 索引结构和实际数据不存放在一起，叶子节点存储主键值。
  • 回表查询：通过二级索引查到主键值，然后到聚簇索引中寻找完整的行数据。explain的extra显示using index condition就表示使用索引 但是进行了回表。

从应用维度：

• 覆盖索引
  • 索引包含所有需要的字段（索引列值、叶子节点的主键值），无需回表。
  • explain的extra显示using index表示使用覆盖索引。
• 前缀索引
  • 对字符串类型字段的前n个字符简历索引。需要平衡索引大小和区分度。（count(distinct (index_col_name(prefix_length)) ) / count(*)）
  • create index index_name on tb_name (index_col_name(prefix_length))
• 单列索引
  • 对单个字段简历索引。
• 联合索引
  • 多个字段组合建立索引。

索引语法

• 创建索引

  create [ unique | fulltext ] index index_name on table_name (index_col_name,...);

• 查看索引

  show index from table_name

• 删除索引

  drop index index_name on table_name

sql性能分析

查看sql操作统计 执行频次

show [session | global] status like 'com_______

慢查询日志

慢查询日志中记录了所有执行时间超过long_query_time的sql语句。

开启慢查询日志： 在MySQL配置文件 /etc/my.cnf中配置slow_query_log = 1。

查看日志文件： cat /var/log/mysql/mysql-slow.log

show-profile

查看每一条sql语句耗时情况。

select @@hava_profiling

set [session | global] profiling = 1

explain

在select语句前➕explain关键字 分析具体执行情况。

• id
  • 值相同，执行顺序从上向下；值不同，先执行值大的。
• select_type
  • 查询类型，simple / primary / union / subquery
• type 连接类型
  • 性能从好到差为：null、system、const、eq_ref、ref、range、index对所有索引进行遍历、all全表扫描 不使用索引。
• possible_key
  • 可能用到的索引
• key
  • 实际用到的索引
• key_len
  • 索引字段最大可能长度
• rows
  • mysql认为需要执行查询的行数。
• filtered
  • 返回数据行数占读取数据行数的百分比。
• extra
  • 记录其他信息，比如是否进行了回表。

索引使用原则

索引失效的场景

• 联合索引
  • 违背最左前缀法则。查询需要从联合索引最左列开始，不能跳过索引中的列，如果跳过某一列，会导致联合索引部分失效 后面的列索引失效。和查询索引的列位置无关，只需要索引字段存在。
  • 索引列范围查询(>、<)会导致右侧列 索引失效。
• 对索引列进行运算、函数操作。
• 字符串类型字段的索引，不加单引号。
• 头部模糊查询，like '%xx'。
• or两侧不都是索引列。

sql提示

• 建议使用某个索引 use index()
• 忽略某个索引 ignore index()
• 强制使用某个索引 force index()

索引设计原则

• 对 数据量大的、查询频繁的表 建立索引。
• 尽量 对查询条件where、排序order by、分组group by后的字段 建立索引。
• 区分度高的字段，建立唯一索引。
• 字符串类型而且长度很长的字段，如果需要建立索引，平衡索引列长度和区分度，建立前缀索引。
• 尽量建立联合索引
  • 可以减少单列索引的冗余数量。
  • 联合索引字段天然有序，避免额外排序。
  • 结合覆盖索引，避免回表查询。
• 索引不是越多越好，索引占用更多空间、需要维护代价、影响增删改更新效率。