Mysql 数据库结构及索引类型
作者:??斜月???? 发布时间:2024-01-20 23:40:35
前言
数据库索引是mysql
数据库中重要的组成部分,是数据库查询数据速度提升的关键,本文将介绍数据库索引的一些内容。
数据库索引的数据结构
在数据库中的索引方法中,有TREE
和HASH
两种方法,HASH
是经常使用的,本文中主要介绍TREE
的数据结构。B+Tree
的高度一般是2-4
层,也就是说查找一条数据记录,最多使用 2-4 次 IO,当前一般的机械磁盘每秒至少可以做 100 次 IO, 2-4 词的 IO 操作时间大概耗时 20-40ms。
数据库中的B+Tree
索引分为聚集索引(clustered index
)和辅助索引(secondary index
),聚集索引和还是非聚集索引其内部的数据结构为平衡二叉树,所有的数据都存放在叶子节点,聚集索引存放的是一整行的数据,而辅助索引的叶子节点存放的是主键 id。 需要提一下的是B+Tree
是一个自底向上生长的树,在数据库表中的数据进行增长时,B+Tree
也会进行增长和分裂。
聚集索引
聚集索引就是按照每张表的主键 id 构造出的一个B+Tree
,同时叶子节点存放的是整行表的数据记录,所以聚集索引的叶子节点就是数据页,每个叶子节点所在的数据页通过双向链表来进行链接。
数据页智能按照一个B+Tree
进行排序,因此每张表智能由一个聚集索引。大多数情况下,查询优化器首先采用聚集索引,因为聚集索引上的叶子结点能够直接找到数据记录。由于数据是按照顺序排列的,索引聚集索引对于主键的排序查找和范围值的查询速度非常快。
辅助索引
辅助索引也是非聚集索引,叶子节点并不包含行记录的全部数据。叶子节点除了包含的主键值外,每个叶子结点中的索引行还包含一个书签(bookmark
)。借助这个书签InnoDB
能够很快找到索引对应的行记录。InnoDB 存储引擎是索引组织表,因此辅助索引的书签就是相应行数据的聚集索引。
数据库表中的聚集索引只有一个,辅助索引可以有多个,当通过辅助索引来查找数据时,InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键,然后通过主键索引来找到完整的行记录。如果辅助索引树的高度为 3,聚集索引树的高度同样为 3,那么如果需要找到一个完整的行记录数据,一共需要 6 次逻辑 IO 访问才能拿到数据页。
索引管理
索引的创建和删除方法如下:
# 创建和删除方法如下
alter table table_name index_name
create table table_name index_name
drop table table_name index_name
# 查询数据库表的索引
show index from table_name
如下图所示,查询数据库表中的索引情况:
表中的实际索引如下:
PRIMARY KEY (`id`),
UNIQUE KEY `uk_tb_user_2` (`username`,`id_card`) USING BTREE,
UNIQUE KEY `uk_tb_user_1` (`username`,`cellphone`) USING BTREE
在索引的展示项中,索引的值cardinality
是非常重要的选项,表示的是数据基数即是数据中不同元素的个数,如果数据内容为枚举,则再该列上建立索引就没有多大意义,因为去分部不大。但是这个值不是实时更新的,只能作为参考。
如果需要更新,则执行以下命令:
analyze table table_name;
上面我们讲述了数据库的索引类型,包括聚集索引和辅助索引,一般情况下这两种索引的索引列都是单列,接下来我们来介绍另外两种索引:联合索引和覆盖索引
。
联合索引
联合索引是指对表上的多个列建立索引,联合索引的创建方法与单个索引的创建方法一样,不同之处仅在于有多个索引列。联合索引的数据结构也是一个B+Tree。对于联合索引,其键值对的数量必定是大于 1,多个键值对的排序和单个键值对的排序都是一样的,通过叶子节点可以逻辑上顺序读取所有的数据。
多个键值的B+树:
如有有数据库表的索引建立为 (a,b),那么对于数据库的查询:
# 这两种情况下都可以查询到数据,而且可以用得到这个联合索引
select * from table_name where a = XX and b = XX;
select * from table_name where a = XX;
# 如果查询条件使用 b 列,那么就不会使用索引,因为 b 列位于索引的第二位
select * from table_name where b = XX;
# 这个查询条件的顺序和索引的顺序不同,但是依然可以使用索引,
# 这是因为在数据库查询时会进行sql 的优化
select * from table_name where b = XX and a = XX;
在使用联合索引时,选用性能最高也是区分度最高的列放在索引的前面,这样就可以最大限度的提高查询的新能。
覆盖索引
InnoDB 存储引擎支持覆盖索引(covering index,或称索引覆盖),即从辅助索引中就可以查询到数据记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有记录,故其大小要远小于聚集索引,因此可以减少大量的 IO 操作。
使用覆盖索引,就是查询的条件和查询的内容是一致的,通过覆盖索引则可以避免回表的情况发生,减少了一次 IO 操作。
回表: 通过辅助索引查找数据时,在其的叶子节点上存放的是该表的主键id,通过主键id 在聚集索引上查找数据记录在进行返回,这个操作就称之为回表。
来源:https://juejin.cn/post/7063853864956461093
猜你喜欢
- 1、建立socket建立socket对象需要搞清通信类型和协议家族。通信类型指明了用什么协议来传输数据。协议的例子包括IPv4、IPv6、I
- 本文实例为大家分享了python实现抖音点赞功能的具体代码,供大家参考,具体内容如下#coding=utf-8from time impor
- php遍历一个文件夹内的所有文件和文件夹,并删除所有文件夹和子文件夹下的所有文件的代码,通过递归方式实现达到清空一个目录的效果,代码简单实用
- 新建图像文件后选Channels面板,新建Alpha1通道:输入文字; &nbs
- 环境: Python 3.6.4 + Pycharm Professional 2017.3.3 + PyQt5 + PyQt5-tools
- 1. 换源,sohu的相当好用。 1.1备份CentOS-Base.repo cd /etc/yum.repos.d/ cp CentOS-
- PDO::rollBackPDO::rollBack — 回滚一个事务(PHP 5 >= 5.1.0, PECL pdo >=
- 判断不仅包括电脑浏览器,还包括安卓、ios系统的手机以及平板电脑,游戏系统 var client = function(){ //呈现引擎
- 常用功能 mean(data)mean(data)用于求给定序列或者迭代器的算术平均数。import statisticsexample_l
- Git合并分支后,需要将子分支提交到git仓库,这个时候就需要单独提交子分支,其步骤如下:1.先创建子分支,并包含最新当前分支下的修改数据g
- 视频观看视频敌人精灵这是我们“Shmup”项目的第2部分!在本课中,我们将添加一些敌人的精灵供玩家躲
- 关于Python语言,众说纷纭,但无外乎两种,强大,垃圾。大多数人还是对Python持肯定意见,认为它很强大。前些天和两个的大学同学聊天,一
- 如下所示:from sklearn.datasets import load_bostonboston = load_boston()fro
- 本文实例讲述了Python实现将数据框数据写入mongodb及mysql数据库的方法。分享给大家供大家参考,具体如下:主要内容:1、数据框数
- 本文简单介绍了Python绘图库Matplotlib的安装,简介如下:matplotlib是python最著名的绘图库,它提供了一整套和ma
- Python的from import *和from import *,它们的功能都是将包引入使用,但是它们是怎么执行的以及为什么使用这种语法
- 当程序中包含多个线程时,CPU 不是一直被特定的线程霸占,而是轮流执行各个线程。那么,CPU 在轮换执行线程的过程中,即从创建到消亡的整个过
- Intersection over Union(IOU)是一种测量在特定数据集中检测相应物体准确度的一个标准。IoU是一个简单的测量标准,只
- Python 是一门优雅的语言,简洁的语法,强大的功能。当然丰富的第三方库,更能加速开发。那么问题来了,如何安装这些第三方库(包)呢?安装第
- 简介:记录一下关于 Python 环境软件包的一些安装步骤1、升级 Python 到 2.7.10( 默认 2.6.6 )shell >