MySQL索引详解

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL索引详解

什么是索引?

索引是一种用于快速查询和检索数据的数据结构,其本质可以看成是一种排序好的数据结构。索引是存储引擎实现的,不同索引的存储引擎不一定相同。

优点:

  • 查询效率高,磁盘I/O次数低(B+树IO次数只有3~4次);
  • 每行数据索引唯一;
  • 联合、分组、排序查询效率高;

缺点:

  • 创建索引耗费时间;
  • 维护索引耗费时间,每次增删改时要维护索引;
  • 索引占用磁盘空间,索引文件可能比数据文件更占空间。例如innoDB的1个聚簇索引和多个非聚簇索引加起来肯定比原数据文件占的内存多。索引存在磁盘,可以分段加载数据页到内层。
  • 联合索引查询时,没有遵循最左前缀原则将会导致索引不起作用,从而出现严重的性能问题。

索引的底层数据结构

Hash表

哈希表是键值对的集合,通过键(key)即可快速取出对应的值(value),因此哈希表可以快速检索数据(接近 O(1))。

为何能够通过 key 快速取出 value 呢? 原因在于 哈希算法(也叫散列算法)。通过哈希算法,我们可以快速找到 key 对应的 index,找到了 index 也就找到了对应的 value。

1. hash = hashfunc(key)
2. index = hash % array_size

但是!哈希算法有个 Hash 冲突 问题,也就是说多个不同的 key 最后得到的 index 相同。通常情况下,我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过,JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。

二叉树

当二叉查找树是平衡的时候,也就是树的每个节点的左右子树深度相差不超过 1 的时候,查询的时间复杂度为 O(log2(N)),具有比较高的效率。然而,当二叉查找树不平衡时,例如在最坏情况下(有序插入节点),树会退化成线性链表(也被称为斜树),导致查询效率急剧下降,时间复杂退化为 O(N)。

红黑树

红黑树是一种自平衡二叉查找树,通过在插入和删除节点时进行颜色变换和旋转操作,使得树始终保持平衡状态。但是索引树层数会很高,查询次数和IO不如B+树

B树

多路平衡查找树,非叶节点也存数据,左小右大,一般层数比B+树深,查询速度和IO次数也就不如B+树;可以分段加载节点数据

B+树

多路平衡查找树,非叶节点存目录,叶节点存记录。查询效率更高(比B树矮胖,层数很难超过4层),IO次数也少(很难超过3次),更稳定,范围查询效率高(因为叶节点之间由双向链表链接)。可以分段加载节点数据。

索引类型

主键索引

一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引。

普通索引

最基本的索引,它没有任何限制。仅加速查询

唯一索引

唯一索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。可以在创建表的时候指定,也可以修改表结构

覆盖索引

一个索引包含(或者说覆盖)所有需要查询的字段的值。

联合索引

多列值组成一个索引,专门用于组合搜索,其效率大于索引合并。

示例:

ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3)

全文索引

全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果。一般不会使用,效率较低,通常使用搜索引擎如 ElasticSearch 代替。


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
7天前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(2)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
7天前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(1)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
1天前
|
存储 关系型数据库 MySQL
MySQL索引18连问,谁能顶住
MySQL索引18问概览: 1. 索引是提升查询速度的数据结构,如书的目录。 2. 索引类型包括B+tree、Hash、Full-text、R-Tree等,B+tree擅长范围查询,Hash擅长等值比较。 3. 主键索引唯一且不可为空,每表只能一个;唯一索引允许唯一值,可为空。 4. 聚簇索引数据与索引顺序一致,非聚簇索引存储指针。
156 0
MySQL索引18连问,谁能顶住
|
2天前
|
关系型数据库 MySQL 数据库
mysql索引优化
【6月更文挑战第16天】mysql索引优化
5 2
|
3天前
|
存储 关系型数据库 MySQL
MySQL索引的限制
【6月更文挑战第15天】MySQL索引的限制
6 3
|
3天前
|
监控 关系型数据库 MySQL
MySQL索引的创建步骤是什么?
【6月更文挑战第15天】MySQL索引的创建步骤是什么?
10 5
|
3天前
|
存储 关系型数据库 MySQL
MySQL索引
【6月更文挑战第15天】MySQL索引
8 3
|
4天前
|
SQL 存储 关系型数据库
MySQL存储过程——Baidu Comate智能代码助手添加20条DML语句——测试索引效果
MySQL存储过程——Baidu Comate智能代码助手添加20条DML语句——测试索引效果
5 0
|
5天前
|
SQL 关系型数据库 MySQL
【MySQL技术内幕】5.6-B+树索引的使用
【MySQL技术内幕】5.6-B+树索引的使用
16 4
|
5天前
|
存储 SQL 关系型数据库
【MySQL技术内幕】5.4-B+树索引
【MySQL技术内幕】5.4-B+树索引
13 0