湖南建设门户网站,建网站拿到广告,蒙晟建设有限公司官方网站,微信群发软件1、什么是索引#xff1f;为什么要用索引#xff1f; 1.1、索引的含义 数据库索引#xff0c;是数据库管理系统中一个排序的数据结构#xff0c;以协助快速查询#xff0c;更新数据库中表的数据。索引的实现通常使用B树和变种的B树#xff08;MySQL常用的索引就是B树为什么要用索引 1.1、索引的含义 数据库索引是数据库管理系统中一个排序的数据结构以协助快速查询更新数据库中表的数据。索引的实现通常使用B树和变种的B树MySQL常用的索引就是B树。除了数据之外数据库系统还维护为满足特定查找算法的数据结构这些数据结构以某种方式引用数据这种数据结构就是索引。简言之索引就类似于书本字典的目录。 1.2、为什么用索引 打个比方如果正确合理设计使用索引的MySQL是一辆兰博基尼的话那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表单表查询可能几十万数据就是瓶颈二通常大型网站单日就可能产生几十万甚至几百万的数据没有索引查询会变得非常缓慢。一言以蔽之合理使用索引可以加快数据库的查询效率和提升程序性能
2、索引的作用与缺点 2.1、作用
通过创建索引可以再查询的过程中提高系统的性能 通过创建唯一性索引可以保持数据库表中每一行数据的唯一性 在使用分组和排序子句进行数据检索时可以减少查询中分组和排序的时间 2.2、缺点 创建索引和维护索引要耗费时间而且时间随着数据量的增加而增大 索引需要占用物理空间如果要建立聚簇索引所需要的空间会更大 在对表中的数据进行增删改时需要耗费较多的时间因为索引也要动态地维护 ———————————————— 版权声明本文为CSDN博主「Iobliviate」的原创文章遵循CC 4.0 BY-SA版权协议转载请附上原文出处链接及本声明。
3、索引的使用场景 3.1、应创建索引的场景
经常需要搜索的列上 作为主键的列上 经常用在连接的列上这些列主要是一些外键可以加快连接的速度 经常需要根据范围进行搜索的列上 经常需要排序的列上 经常使用在where子句上面的列上 3.2、不应该创建索引的场景
查询中很少用到的列 对于那些具有很少数据值的列比如数据表中的性别列bit数据类型的列 对于那些定义为textimage的列因为这些列的数据量相当大 当对修改性能的要求远远大于搜索性能时因为当增加索引时会提高搜索性能但是会降低修改性能
4、索引的分类与说明
4.1、主键索引 设定为主键后数据库会自动建立索引innodb为聚簇索引
#随表一起建索引
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id)
);
#使用AUTO_INCREMENT关键字的列必须有索引(只要有索引就行)。
CREATE TABLE customer2 (id INT(10) UNSIGNED,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id)
);
#单独建主键索引
ALTER TABLE customer add PRIMARY KEY customer(customer_no);
#删除建主键索引
ALTER TABLE customer drop PRIMARY KEY ;
#修改建主键索引
#必须先删除掉(drop)原索引再新建(add)索引4.2、单列索引 一个索引只包含单个列一个表可以有多个单列索引
#随表一起建索引
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id),KEY (customer_name)
);
#随表一起建立的索引 索引名同 列名(customer_name)
#单独建单值索引
CREATE INDEX idx_customer_name ON customer(customer_name);
#删除索引
DROP INDEX idx_customer_name ;4.3、唯一索引 索引列的值必须唯一但允许有空值
#随表一起建索引
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id),KEY (customer_name),UNIQUE (customer_no)
);
#建立 唯一索引时必须保证所有的值是唯一的除了null若有重复数据会报错。
#单独建唯一索引
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);
#删除索引
DROP INDEX idx_customer_no on customer ;4.4、复合索引 一个索引包含多个列在数据库操作期间复合索引比单值索引所需要的开销更小对于相同的多个列建索引 如果一个表中的数据在查询时有多个字段总是同时出现则这些字段就可以作为复合索引形成索引覆盖可以提高查询的效率
#随表一起建索引
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id),KEY (customer_name),UNIQUE (customer_name),KEY (customer_no,customer_name)
);
#单独建索引
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);
#删除索引
DROP INDEX idx_no_name on customer ;4.5、聚集索引与非聚集索引 4.5.1、聚集索引 聚集索引指索引项的排序方式和表中数据记录排序方式一致的索引。它会根据聚集索引键的顺序来存储表中的数据即对表的数据按索引键的顺序进行排序然后重新存储到磁盘上。因为数据在物理存放只能有一种排列方式所以一个表只能有一个聚集索引。比如字典中用拼音查汉字就是聚集索引。因为正文中字都是按照拼音排序的。而用偏旁部首查汉字就是非聚集索引因为正文中的字并不是按照偏旁部首排序的我们通过检字表得到正文中的字在索引中的映射然后通过映射找到所需要的字。
聚集索引的使用场合为 查询命令的回传结果是以该字段为排序依据的 查询的结果返回一个区间的值 查询的结果返回某值相同的大量结果集
聚集索引会降低insert和update操作的性能所以是否使用聚集索引要全面衡量。
4.5.2、非聚集索引 非聚集索引与聚集索引相反索引顺序与物理存储顺序不一致 非聚集索引的使用场合为 查询所获数据量较少时 某字段中的数据的唯一性比较高时
非聚集索引必须是稠密索引
4.5.3、使用及语法
create [unique] [clustered] [nonclustered] index index_name on {tabel/view} (column[dese/asc][....n])4.5.4、使用场景对比
动作描述使用聚集索引使用非聚集索引列经常被分组排序使用使用返回某范围内的数据使用不使用一个或极少不同值不使用不使用小数目的不同值使用不使用大数目的不同值不使用使用频繁更新的列不使用使用外键列使用使用主键列使用使用频繁修改索引列不使用使用 聚簇索引与非聚簇索引
稠密索引与稀疏索引
索引的底层原理 抛开其他的数据库索引实现主讲MySQL的索引底层实现其底层是通过B树来实现的数据结构存储。 数据结构存储决定了数据查找和操作时的效率包括时间复杂度和空间复杂度而在取舍的时候也无非就是时间换空间空间换时间的权衡罢了所以这就很好的解释了为什么MySQL在索引的底层设计上选用了B树而没有选用B-树或是红黑树AVL树等等其他数据结构。总之就是使用B树作为索引的结构存储能在I/O性能上得到一个较大的优势。
那么具体优势在哪里呢以B-树与B树的对比来阐述具体差异和B树的优势。
5.1、B-Tree B-树是一种多路自平衡的搜索树它类似普通的平衡二叉树不同的一点是B-树允许每个节点有更多的子节点。B-Tree相对于AVLTree缩减了节点个数使每次磁盘I/O取到内存的数据都发挥了作用从而提高了查询效率。 注B-Tree就是我们常说的B树 那么m阶B-Tree是满足下列条件的数据结构 所有键值分布在整棵树中 搜索有可能在非叶子节点结束在关键字全集内做一次查找性能逼近二分查找 每个节点最多拥有m个子树 根节点至少有2个子树 分支节点至少拥有m/2颗子树除根节点和叶子节点外都是分支节点 所有叶子节点都在同一层每个节点最多可以有m-1个key并且以升序排列
每个节点占用一个磁盘块一个节点上有两个升序排序的关键字和三个指向子树根节点的指针指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例关键字为17和35P1指针指向的子树的数据范围小于17P2指针指向的子树的数据范围为17~35P3指针指向的子树的数据范围大于35.
模拟查找关键字29的过程 1、根据根节点找到磁盘块1读入内存。【磁盘I/O操作第1次】 2、比较关键字29在区间17,35找到磁盘块1的指针P2。 3、根据P2指针找到磁盘块3读入内存。【磁盘I/O操作第2次】 4、比较关键字29在区间26,30找到磁盘块3的指针P2。 5、根据P2指针找到磁盘块8读入内存。【磁盘I/O操作第3次】 6、在磁盘块8中的关键字列表中找到关键字29。 7、分析上面过程发现需要3次磁盘I/O操作和3次内存查找操作。由于内存中的关键字是一个有序表结构可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。
但同时B-Tree也存在问题 每个节点中有key也有data而每一个页的存储空间是有限的如果data数据较大时将会导致每个节点即一个页能存储的key的数量很小。 当存储的数据量很大时同样会导致B-Tree的深度较大增大查询时的磁盘I/O次数进而影响查询效率
5.2、BTree BTree是在B-Tree基础上的一种优化InnoDB存储引擎就是用BTree实现其索引结构。它带来的变化点 B树每个节点可以包含更多的节点这样做有两个原因一个是降低树的高度。另外一个是将数据范围变为多个区间区间越多数据检索越快 非叶子节点存储key叶子节点存储key和数据 叶子节点两两指针相互链接符合磁盘的预读特性顺序查询性能更高
注MySQL的InnoDB存储引擎在设计时是将根节点常驻内存因此力求达到树的深度不超过3也就是说I/O不需要超过3次。
通常在BTree上有两个头指针一个指向根节点另一个指向关键字最小的叶子节点而且所有叶子节点即数据节点之间是一种链式环结构因此可以对BTree进行两种查找运算一种是对于主键的范围查找的分页查找另一种是从根节点开始进行随机查找 5.3、B-树和B树的区别 B树内节点不存储数据所有数据存储在叶节点导致查询时间复杂度固定为log n B-树查询时间复杂度不固定与Key在树中的位置有关最好为O(1) B树叶节点两两相连可大大增加区间访问性可使用在范围查询等 B树更适合外部存储存储磁盘数据。由于内节点无data域每个节点能索引的范围更大更精确。 原文链接https://blog.csdn.net/qq_44483424/article/details/121385545
