MySQL索引以及创建索引的注意事项

echola_mendes

已于 2024-09-10 17:14:10 修改

阅读量1.7k

点赞数 32

分类专栏： # MySQL 文章标签： mysql 数据库

于 2024-09-10 17:11:05 首次发布

本文链接：https://blog.youkuaiyun.com/qq_36451127/article/details/141433271

版权

在上一篇文章SQL慢查询优化方式-优快云博客，有提到几个概念：索引（覆盖索引、复合索引）和回表，那就需要深入了解一下MySQL的索引以及数据结构

还是以User表为例：

CREATE TABLE User (
    user_id INT PRIMARY KEY,
    name VARCHAR(255),
    phone VARCHAR(20),
    email VARCHAR(255),
    role VARCHAR(50),
    address VARCHAR(255),
    birthday DATETIME
);

一、索引结构

MySQL 中的索引是为了加速数据检索而设计的数据结构，索引可以帮助数据库系统减少查找数据所需的 I/O 操作次数，从而提高查询性能

不同的存储引擎可能支持不同类型的索引，但最常用的索引类型通常基于 B+ 树（B-Tree）

此处索引idx_userid_birthday采用的是B Tree

Hash索引：
哈希索引使用哈希算法将键值转换成固定长度的哈希码。
适用于等值查询，但对于范围查询则不太适用，因为哈希索引不保存键值的顺序

❓那为什么是B+ Tree，而不是B Tree、二叉查找树、平衡二叉树、红黑树呢

这里先不考虑Hash索引结构，那以最普通的二叉查找树开始，逐步说明各种树解决的问题以及面临的新问题，从而说明MySQL为什么选择B+树作为索引结构

1、二叉查找树(BST):树太高

特点：

每个节点最多有两个子节点
左子树中的所有节点的值小于根节点的值
右子树中的所有节点的值大于根节点的值

优点：插入、删除和查找的时间复杂度在平均情况下为 O(log n)

缺点：最坏情况下，如果插入的数据是递增或递减序列，树会退化成链表，时间复杂度退化为 O(n)

树太高，就会导致查询时间会变慢

2、平衡二叉树(AVL):旋转耗时

为了避免二叉查找树的过高，平衡二叉树通过旋转，使得所有节点的左右子树高度差不能超过1

🌰：树中节点的值依次为：1, 2, 3, 4, 5, 6

BTS按照递增数据插入：

AVL按照递增顺序插入：

可以看出

二叉查找树的高度为6，完全不平衡
平衡二叉树的高度为 3，通过旋转操作保持树的平衡，这是一个更平衡的状态

有兴趣的话，可以看下平衡二叉树的插入过程：

//插入2
   1     
    \
     2

//插入 3

   2
  / \
 1   3
   
//插入4
//此时，节点 3 的左子树高度为 1，右子树高度为 1，不需要旋转
   2
  / \
 1   3
      \
       4

//插入5
//此时，节点 4 的左子树高度为 0，右子树高度为 1，不需要旋转
//但是，节点 3 的左子树高度为 1，右子树高度为 2，需要进行旋转

//旋转前
   2
  / \
 1   3
      \
       4
        \
         5
//旋转后
   2
  / \
 1   4
    / \
   3   5

//插入 6
//此时，节点 5 的左子树高度为 0，右子树高度为 1，不需要旋转
   2
  / \
 1   4
    / \
   3   5
        \
         6

最低0.47元/天解锁文章