MySQL 索引

发布于 2024-07-13 13:48:07 字数 5255 浏览 24 评论 0

关于索引的比喻:索引就好比一本书的目录,它会让你更快的找到内容,显然目录(索引) 并不是越多越好,假如这本书 1000 页,有 500 也是目录,它当然效率低,目录是要占纸张的,而索引是要占磁盘空间的。

概念:

MySQL​ 索引两种主要结构:

  • hash : hash 索引在 mysql 比较少用,他以把数据的索引以 hash 形式组织起来,因此当查找某一条记录的时候,速度非常快.但是因为是 hash 结构,每个键只对应一个值,而且是散列的方式分布.所以他并 不支持范围查找和排序 等功能.
  • B+树 : b+tree 是 mysql 使用 最频繁 的一个索引数据结构,数据结构以 平衡树 的形式来组织,因为是树型结构,所以更适合用来 处理排序 范围查找 等功能.相对 hash 索引,B+树在查找单条记录的速度虽然比不上 hash 索引,但是因为更适合排序等操作,所以他更受用户的欢迎.毕竟不可能只对数据库进行单条记录的操作.

常见的索引类型

  • 聚簇索引 主键索引(PRIMARY KEY) : 它是一种特殊的唯一索引, 不允许有空值 ,不能重复,很少改变,经常被检索,不能太长,建议使用 snowflake 生成. ALTER TABLE table_name ADD PRIMARY KEY(column_name)
  • 唯一索引(UNIQUE) : 与 普通索引 类似,不同的就是,索引列的值必须 唯一 ,但 允许有空值 . ALTER TABLE table_name ADD UNIQUE(column_name)
  • 普通索引(INDEX) : 最基本的索引,没有任何限制 可以有重复 ,对数据按照索引的规则进行排序,加快查询速度. ALTER TABLE table_name ADD INDEX index_name(column_name)
  • 全文索引(FULLTEXT) : 仅可用于 MyISAM 表,针对较大的数据,生成全文索引很耗时好空间. ALTER TABLE table_name ADD FULLTEXT(column_name) ,查询的时候应该使用 SELECT * FROM article WHERE MATCH(title, content) AGAINST('查询字符串')
  • 组合索引(INDEX) : 为了更多的提高 mysql 效率可建立组合索引,遵循 最左前缀 原则. ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3)

主键和唯一索引的区别

difference-between-key-primary-key-unique-key-and-index-in-mysql

  • 主键是一种约束,唯一索引是一种索引,两者 本质 上是不同的
  • 主键创建后一定包含一个唯一性索引,称为主键索引(是一个特殊的唯一索引)
  • 主键不 能为空 ,唯一所以可以为空
  • 一个表 最多 只能创建一个主键,但可以创建多个唯一索引
  • 主键更适合那些不容易更改的唯一标识,如自动递增列、身份证号
  • 主键可以被其他表引用为外键,而唯一索引不能

主键的相关问题

  • InnoDB 建表时,可不可以不声明主键
    • 可以不声明主键,但必须要有聚簇索引(主键和聚集索引不是一个东西,不要混淆)
      • 有主键,主键是聚簇索引
      • 没有主键,首个非空唯一列是聚簇索引
      • 没有符合条件的列,内置生成一个 row_id 是聚簇索引
  • InnoDB 建表时,可不可以不声明主键非空
    • 可以不声明主键非空,会自动加上非空限制
    • 如果没有设置自增的话,会使用默认值, int 会是 0
  • InnoDB 建表时,可不可以选择多个字段做主键
    • 可以使用联合主键,组合列唯一即可
  • InnoDB 插入时,可不可以主动插入自增主键
    • 可以指定自增列的值,但可能导致空洞
  • InnoDB 建表时,可不可以使用联合自增主键
    • 可以,但自增 ID 必须在联合主键的第一列

唯一索引相关问题

  • 主键不能为空,唯一索引可以为空
  • 唯一索引允许有多个空值,参考 这里

几个为什么

为什么要存在索引

用于提升数据库的查找速度,如果没有索引就要通过遍历来查找数据,如果有索引可以通过索引缩小查找的范围

哈希(hash) 比树(tree) 更快 索引结构为什么要设计成树型

  • 哈希,例如 HashMap, 查询/插入/修改/删除 的平均时间复杂度都是 O(1)
  • 树,例如平衡二叉搜索树, 查询/插入/修改/删除 的平均时间复杂度都是 O(lg(n))

为什么所以不设计成哈希而要设计成树呢?其实和 sql 的需求有关,其本质是 tree 是有序的数据结构 方便范围查询 :

  • 如果所有的查询都是进行单行查询 select * from t where name="shenjian"; hash 时间复杂度是 O(1) ,tree 时间复杂度是 O(lg(n)) ,哈希确实比树快多了( 如果业务仅仅需要查询单行记录,确实可以使用 hash 索引,效率更高 )
  • 但是如果查询中存在 group by, order by, between, <、> 时,hash 时间复杂度会退化为 O(n) ,而有序型的 tree 时间复杂度是 O(log(n)) ,而范围查询在 sql 的时间里是很多的,所以索引选择了使用 tree 而不是 hash 作为索引的数据格式(另外 InnoDB不支持哈希索引 )

这么多树为什么选择 B+ tree

  • 二叉搜索树
    • 当数据量大的时候,树的 高度会比较高 ,数据量大的时候,查询会比较慢
    • 每个节点只存储一个记录,可能导致一次查询有很多次磁盘 IO
  • B 树(被创造出来就是为了解决索引的数据结构)( B for balance )
    • 不再是二叉搜索,而是 m 叉搜索
    • 叶子节点,非叶子节点,都存储数据
    • 中序遍历 ,可以获得所有节点
  • B+树(在 B-tree 基础上做了优化的数据结构)
    • 和 b-tree 一样是 m 叉搜索
    • 仅叶子节点存储数据,非叶子节点不储存数据(仅储存在同一层的叶子节点中)
    • 叶子之间,增加了链表,获取所有节点,不再需要中序遍历

B+ 树比 B 树更优的原因:

  • 范围查找更优: 定位 minmax 之后,链表两端的中间叶子节点,就是结果集,不用中序回溯( 范围查询在 SQL 中用得很多,这是 B+树比 B 树最大的优势 )
  • 储存更加紧密: 叶子节点存储实际记录行,记录行相对比较 紧密的存储 ,适合大数据量磁盘存储
  • 索引更小适合放进内存: 非叶子节点存储记录的 PK,用于查询加速,适合内存存储.不存储实际记录,而只存储记录的 KEY 的话,那么在 相同内存 的情况下,B+树能够存储更多索引

B 树和 B+树的特点

B+ tree 为什么非常适合数据库索引

  • 很适合磁盘存储,能够充分利用局部性原理,磁盘预读
    • 局部性原理: 软件设计要尽量遵循"数据读取集中"与"使用到一个数据,大概率会使用其附近的数据",这样磁盘预读能充分提高磁盘 IO
    • 磁盘预读的思路: 磁盘读写并 不是按需读取 ,而是 按页预读 ,一次会读一页的数据,每次加载更多的数据,以便未来减少磁盘 IO
  • 很低的树高度,能够存储大量数据
  • 索引本身占用的内存很小
  • 能够很好的支持单点查询,范围查询,有序性查询

索引最佳实践

索引名

  • 索引名的作用域是每个表,不能在同一个表中有相同的索引名称,但是可以在不同表中使用相同的索引名
  • 索引名对性能没有影响
  • 索引名对可读性影响很大,建议使用可读性更好的索引名

更新主键和唯一索引

根据 可知,主键和唯一所以都是可以更新的,原则上说 关系型数据库的全部数据都是允许更新的 ,但是这个操作一般是没有意义的(因为大部分的主键是使用无意义的数字作的),另外修改主键会导致聚集索引重建浪费性能。

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。
列表为空,暂无数据

关于作者

JSmiles

生命进入颠沛而奔忙的本质状态,并将以不断告别和相遇的陈旧方式继续下去。

0 文章
0 评论
84961 人气
更多

推荐作者

小瓶盖

文章 0 评论 0

wxsp_Ukbq8xGR

文章 0 评论 0

1638627670

文章 0 评论 0

仅一夜美梦

文章 0 评论 0

夜访吸血鬼

文章 0 评论 0

近卫軍团

文章 0 评论 0

    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
    原文