数据库有哪些索引

简介: 数据库有哪些索引

什么是索引

根据索引类型,可以分成主键索引和二级索引(非主键索引)

image.png

主键索引:主键索引是叶子结点保存主键对应行的全部数据, 在 InnoDB 中,主键索引,也被叫做聚簇索引。二级索引(非主键索引):二级索引的叶子结点保存的是索引值和主键值,当二级索引进行查询时,需要进行回表操作。

  • select * from t_user where id=1 即主键查询方式,则只需要搜索id这棵B+树
  • select * from t_user where name=‘张三’ 即普通索引查询方式,则需要先搜索name索引树,得到id的值为3,再到id索引树搜索一次。这个过程称为回表

可以看到,基于二级索引的查询需要多扫描一颗索引数,因此,尽量使用主键查询。

什么是覆盖索引


场景:10W条数据,我要从其中查出100条不连续的数据,给你id,来查name和password进行展示,如何才能高性能的去使用?

当 SQL 语句所求查询字段 (select 列)和查询条件字段(where) 全都包含在一个索引中(联合索引), 可以直接使用索引而不需要回表,这个就是覆盖索引

联合索引

在某一列上加索引以提升相关语句查询效率,联合索引就是在多个列上加索引。

相对单列来说,就是在多个列上加索引

create table t_user (
id bigint(20) not null auto_increment ,
name varchar(255) not null,
password varchar(255) ,
index(name)
primary key (id)
)engine=innodb default character set=utf8 collate=utf8_general_ci

看个例子:

select id from user_table where name= '张三'

name 是二级索引,索引树的叶子结点存储的保存有 name 和 id 值,所以通过 name 索引树查找到 id 之后,可以直接提供查询结果,不需要回表。这个查询里 索引 name 覆盖了我们的查询需求,我们称为是覆盖索引

select password from user_table where name= '张三'

name 索引树上找到叶子结点, name = "张三“ 对应的主键 id, 通过 id 在主键树上找到满足条件的数据。

主键和索引有什么区别


主键索引

  • 主键是一种约束,唯一索引是一种索引,两者在本质上是不同的。
  • 主键创建后一定包含一个唯一性索引,唯一性索引并不一定就是主键。

唯一索引

  • 唯一性索引列允许空值,而主键列不允许为空值。
  • 主键列在创建时,已经默认为非空值 + 唯一索引了。
  • 主键可以被其他表引用为外键,而唯一索引不能。
  • 一个表最多只能创建一个主键,但可以创建多个唯一索引。

主键更适合那些不容易更改的唯一标识,如自动递增列、身份证号等。

索引下堆
SET optimizer_switch = 'index_condition_pushdown=on';

在MySQL 5.6中 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数

不使用索引下堆:

根据(username,is_del)联合索引查询所有满足名称以“张”开头的索引,然后回表查询出相应的全行数据,然后再筛选出未删除的用户数据。

image.png

每一个虚线表示一次回表操作。

使用索引下堆

InnoDB在(name,is_del)索引内部就判断了数据是否逻辑删除,对于逻辑删除的记录,直接判断并跳过

image.png

虚线表示回表,使用索引下堆,回表次数减少为 2 次。

相关文章
|
2月前
|
存储 关系型数据库 MySQL
MySQL数据库索引的数据结构?
MySQL中默认使用B+tree索引,它是一种多路平衡搜索树,具有树高较低、检索速度快的特点。所有数据存储在叶子节点,非叶子节点仅作索引,且叶子节点形成双向链表,便于区间查询。
85 4
|
3月前
|
存储 算法 关系型数据库
数据库主键与索引详解
本文介绍了主键与索引的核心特性及其区别。主键具有唯一标识、数量限制、存储类型和自动排序等特点,用于确保数据完整性和提升查询效率;而索引通过特殊数据结构(如B+树、哈希)优化查询速度,适用于不同场景。文章分析了主键与索引的优劣、适用场景及工作原理,并对比两者在唯一性、数量限制、功能定位等方面的差异,为数据库设计提供指导。
|
10月前
|
数据库 索引
深入探索数据库索引技术:回表与索引下推解析
【10月更文挑战第15天】在数据库查询优化的领域中,回表和索引下推是两个核心概念,它们对于提高查询性能至关重要。本文将详细解释这两个术语,并探讨它们在数据库操作中的作用和影响。
168 3
|
10月前
|
数据库 索引
深入理解数据库索引技术:回表与索引下推详解
【10月更文挑战第23天】 在数据库查询性能优化中,索引的使用是提升查询效率的关键。然而,并非所有的索引都能直接加速查询。本文将深入探讨两个重要的数据库索引技术:回表和索引下推,解释它们的概念、工作原理以及对性能的影响。
337 3
|
6月前
|
存储 缓存 数据库
数据库索引采用B+树不采用B树的原因?
● B+树更便于遍历:由于B+树的数据都存储在叶子结点中,分支结点均为索引,方便扫库,只需要扫一遍叶子结点即可,但是B树因为其分支结点同样存储着数据,我们要找到具体的数据,需要进行一次中序遍历按序来扫,所以B+树更加适合在区间查询的情况,所以通常B+树用于数据库索引。 ● B+树的磁盘读写代价更低:B+树在内部节点上不包含数据信息,因此在内存页中能够存放更多的key。 数据存放的更加紧密,具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。 ● B+树的查询效率更加稳定:由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条
|
9月前
|
存储 缓存 数据库
数据库索引采用B+树不采用B树的原因?
B+树优化了数据存储和查询效率,数据仅存于叶子节点,便于区间查询和遍历,磁盘读写成本低,查询效率稳定,特别适合数据库索引及范围查询。
117 6
|
11月前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
178 3
Mysql(4)—数据库索引
|
10月前
|
存储 缓存 数据库
数据库索引采用B+树不采用B树的原因
B+树相较于B树,在数据存储、磁盘读写、查询效率及范围查询方面更具优势。数据仅存于叶子节点,便于高效遍历和区间查询;内部节点不含数据,提高缓存命中率;查询路径固定,效率稳定;特别适合数据库索引使用。
123 1
|
10月前
|
数据库 索引
数据库索引
数据库索引 1、索引:建立在表一列或多列的辅助对象,目的是加快访问表的数据。 2、索引的优点: (1)、创建唯一性索引,可以确保数据的唯一性; (2)、大大加快数据检索速度; (3)、加速表与表之间的连接; (4)、在查询过程中,使用优化隐藏器,提高系统性能。 3、索引的缺点: (1)、创建和维护索引需要耗费时间,随数据量增加而增加; (2)、索引占用物理空间; (3)、对表的数据进行增删改时,索引需要动态维护,降低了数据的维护速度。
151 2
|
10月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
1071 1

热门文章

最新文章