MySQL索引性能优化

索引的本质

• 索引是帮助MySQL高效获取数据的排好序的数据结构

• 索引数据结构

  • 二叉树

    • 早期使用，现在已经淘汰。原因：插入递增元素，会褪变成一个链表，添加的索引和没加之前是一样的
      

  • 红黑树

    • 也就是平衡二叉树，MySQL没有选择该数据结构。原因：在表有百万行记录以上时，树的高度会非常高，当查找的数据是叶子节点时，也需要经过很多次磁盘io的查找，效率比较低下
      

  • Hash表，是现在可以选择的索引数据结构

    • 对索引的key进行一次hash计算就可以定位出数据存储的位置
    • 很多时候Hash索引要比B+树索引更高效
    • 仅能满足“=”，“IN”，不支持范围查找（是现在几乎不选择的一个原因）
    • hash冲突问题
      

  • B-Tree

    • 叶节点具有相同的深度，叶节点的指针为空
    • 所有索引元素不重复，并且节点都携带data
    • 节点中的数据索引从左到右递增排序，有序也是B-Tree支持范围查找的主要能力
    • 没有区间指针，查找范围需要从根节点开始查找
      

  • B+Tree(B-Tree变种)，可以保证树的高度，就算记录数多的时候查找的次数也比较少，效率高，所有是现在默认使用的索引数据结构

    • 非叶子节点不存储data（data是mysql存储索引所在行的磁盘文件地址），只存储索引(冗余)，可以放更多的索引（B-Tree的每一个节点都有数据，固然在同样的记录数的情况下，B+Tree的高度必然比B-Tree的高度小，查找也更加快速）。每一个配置叶的大小可以通过SHOW GLOBAL STATUS LIKE 'Innodb_page_size’去查询
    • 叶子节点包含所有索引字段
    • 叶子节点用指针连接，提高区域间访问的性能，查找范围不需要从根节点开始查找（和B-Tree的主要区别）
    • 节点中的数据索引从左到右递增排序，有序也是B-Tree支持范围查找的主要能力
      

• 在联合索引的情况下，都在遵循最左前缀法制（这个也是因为节点的数据索引是由第一个索引先通过索引最左前缀原理（A比B在前）先确定位置，在依次到后面的索引）。例如有索引（name，age，position），在查询时where之后的条件使用索引时不能跳过前面的索引条件，否则无法使用索引查找，where name = ‘Bill’ and age = 30;(可以走索引)，where age = ‘30’ and position = ‘dev’;(不可以走索引)，where position = ‘dev’;(不可以走索引)
  

存储引擎索引实现

• MySQL中的表数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且提供不同的功能和能力，这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。
• 存储引擎的核心功能分为四类:支持的字段和数据类型、锁定类型、索引和事务处理。
• MyISAM存储引擎索引实现
  • MyISAM索引文件(MYI文件)和数据文件(MYD文件)是分离的(非聚集)
  • 底层实现：先通过索引文件（MYI文件）中的B+Tree找到叶子节点的data文件，然后通过data文件中的磁盘文件地址去数据文件（MYD文件）中找到需要查找的对应行的一个数据
• InnoDB存储引擎索引实现
  • InnoDB的索引和数据都存储在表数据文件(IBD文件)中，是非分离的(聚集)
  • 表数据文件本身就是按B+Tree组织的一个索引结构文件
  • 聚集索引：叶节点包含了完整的数据记录
  • 建议InnoDB表尽量建主键，并且推荐使用整型的自增主键，原因：如果有主键就会使用主键索引，如果没有主键，MySQL就会逐列的去找数据完全不同的列去组织B+Tree的一个索引文件，如果找不到就会维护一个隐藏列去组织B+Tree的一个索引文件，所以如果我们自己建主键就不需要MySQL每次去判断主键，节省了MySQL的资源；自增主键可以维持B+Tree的高度，不需要分裂，而是直接往水平拓展元素即可
  • 非主键索引结构叶子节点存储的是主键值（一致性和节省存储空间）
    

几个常用存储引擎的特点

常用存储引擎的适用环境

• MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一
• InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持。
• Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。
• Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。

本文地址：https://blog.csdn.net/qq_39294770/article/details/108972894