MySQL 索引

本文详细介绍了MySQL中不同类型的索引(如B+树、哈希索引等),数据结构在查询效率上的影响,以及如何通过索引优化、避免索引失效和最左前缀匹配等方法提高查询性能。涵盖了数据页、文件头、页目录等概念,并讨论了回表与索引覆盖现象以及索引选择和使用策略。
摘要由CSDN通过智能技术生成

定义

  • 帮助MySQL高效获取数据的数据结构
  • 默认都是使用B+树结构组织的索引

分类

  1. 数据结构纬度
    • B+树索引:所有数据存储在叶子节点,复杂度为O(logn),适合范围查询。
    • 哈希索引:适合等值查询,检索效率高,一次到位。
    • 全文索引:MyISAM和InnoDB中都支持使用全文索引,一般在文本类型char,text,varchar类型上创建。
  2. 物理存储纬度
    • 聚簇索引:聚簇索引就是以主键创建的索引,在叶子节点存储的是表中的数据。(Innodb存储引擎)
    • 非聚簇索引:非聚簇索引就是以非主键创建的索引,在叶子节点存储的是主键和索引列。(Innodb存储引擎)
  3. 逻辑纬度
    • 主键索引:一种特殊的唯一索引,不允许有空值。
    • 普通索引:基本索引类型,允许空值和重复值。
    • 联合索引:多个字段创建的索引,使用时遵循最左前缀原则。
    • 唯一索引:索引列中的值必须是唯一的,但是允许为空值。
    • 空间索引:MySQL5.7之后支持空间索引,在空间索引这方面遵循OpenGIS几何数据模型规则。

MsSQL 读取数据格式

数据库的 I/O 操作的最小单位是 **数据页**,InnoDB 默认的大小是 16KB
数据页包括 文件头、用户记录、页目录等
  • 文件头:有两个指针,分别指向上一个数据页和下一个数据页,连接起来相当于一个双向的链表
  • 用户记录:按照主键顺序组成单向链表
  • 页目录:由多个槽按照先后顺序组成
    • 将用户记录划分成几个组,记录包括最小记录(第一条记录)和最大记录(最后一条记录)
    • 组中最后一条记录会存储该组一共有多少条记录
    • 组中最后一条记录的地址偏移量称为槽,相当于分组记录的索引

数据结构

Hash表

  • 优点:
  • 适合单值快速检索数据
  • 缺点:
  • 不支持顺序和范围查询

二叉树

  • 优点:
  • 平衡时查询的时间复杂度为 O(log2(N))
  • 缺点:
  • 不平衡时查询的时间复杂度最大为O(N)

平衡二叉树

  • 优点:
  • 查询的时间复杂度都是 O(log2(N))
  • 缺点:
  • 数据量大时磁盘IO次数多
  • 不支持范围查询

B树

  • 特点:
  • B树的节点中存储这多个元素,每个内节点有多个分叉
  • 节点中的元素包含键值和数据,节点中的键值从小到大排列。
  • 父节点当中的元素不会出现在子节点中。
  • 所有的叶子节点都位于同一层,叶子节点具有相同的深度,叶子节点之间没有指针连接。
  • 优点:
  • 可以很好的提升查询的效率
  • 缺点:
  • 不支持范围快速查询
  • 行记录增加,所占空间变大,树高度变高,磁盘IO次数变大

B+树

  • 特点:
  • 只有叶子节点才会存储数据,非叶子节点只存储键值key
  • 叶子节点之间使用双向指针连接,最底层的叶子节点形成了一个双向有序链表
  • 优点:
  • 可以保证等值和范围查询的快速查找
  • 缺点:
  • 会占用更多的空间

回表与索引覆盖

  • 如果某个查询语句使用了二级索引,但是查询的数据不是主键值,这时在二级索引找到主键值后,需要去聚簇索引中获得数据行,这个过程就叫作「回表」,也就是说要查两个 B+ 树才能查到数据。
  • 当查询的数据是主键值时,因为只在二级索引就能查询到,不用再去聚簇索引查,这个过程就叫作「索引覆盖」,也就是只需要查一个 B+ 树就能找到数据。

合理的库表、索引

合理设计表

  1. 第一范式:确保每列保持原子性

  2. 第二范式:确保表中的每列都和主键相关

  3. 第三范式:确保每列都和主键列直接相关,而不是间接相关,不存在传递依赖

  4. 设计合理的数据表

    • 将数据分解为合适的表,避免将过多的数据存储在单个表中
    • 每个表都应该有清晰定义的目的
  5. 表、字段规范

    • 表名、字段名应该使用小写字母,采用下划线分割
    • 表达是与否概念的字段,必须使用 is _ xxx 的方式命名,数据类型是 unsigned tinyint( 1 表示是,0 表示否 )
    • 表必备三字段: id 主键, gmt _ create创建时间 , gmt _ modified更新时间
    • 不同表之间存储相同数据的列名和列类型必须一致(关联列)
    • 不得使用外键与级联
    • 增加冗余字段
  6. 选择合适的数据类型

    • 优先选择符合存储需要的最小、最简单的数据类型
    • 小数类型为 decimal ,禁止使用 float 和 double
    • 日期使用date、时间使用时间戳
  7. 避免使用过多的NULL值

合理设计索引

  1. 创建合适的索引

    • 为常作为查询条件的字段建立索引
    • 为经常需要排序、分组和联合操作的字段建立索引
    • 业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引
    • 主键索引名为 pk_ 字段名;唯一索引名为 uk _字段名 ; 普通索引名则为 idx _字段名
    • 在 varchar 字段上建立索引时,必须指定索引长度
  2. 选择合适的索引类型

    • 等值查询可以选择hash索引、范围选择B+树
    • 唯一字段选择唯一索引、多个字段建立联合索引
  3. 正确设置索引列的顺序

  4. 使用覆盖索引

索引优化

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句
  1. 限制索引的数目

  2. 数据量小的表最好不要使用索引

  3. 尽量使用数据量少的索引

  4. 避免使用长字符串作为索引、尽量使用前缀来索引

  5. 索引最好设置为 NOT NULL

  6. 关联查询优化

    • 内连接时,mysql 会自动把小结果集选为驱动表,所以大表字段需加上索引
    • 左外连接时,左表(驱动表)会全表扫描,所以右边大表字段需加上索引
    • 右外连接同理,被驱动表上的字段需建立索引
  7. 排序、分组优化

    • 尽量避免使用Using FileSort方式排序
    • 语句满足索引最左匹配
    • 不要出现索引范围查询
  8. 慢查询日志

  9. 定期维护索引

  10. 监控数据库性能

  11. 删除不再使用或者很少使用的索引

索引失效

  • 最左前缀匹配原则
    • 联合索引中只有最左边的字段是有序的
  • 不在索引列上做任何计算、函数操作
  • 不使用:不等于和is not null
  • like不以通配符开头
  • 字符串字段不加单(双)引号
  • 不使用 or 连接

参考文章:MySQL的B+树

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值