前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >数据库有哪些索引

数据库有哪些索引

作者头像
王小明_HIT
发布2021-05-20 10:41:45
2.1K0
发布2021-05-20 10:41:45
举报
文章被收录于专栏:程序员奇点

什么是索引

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

主键索引:主键索引是叶子结点保存主键对应行的全部数据, 在 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) 全都包含在一个索引中(联合索引), 可以直接使用索引而不需要回表,这个就是覆盖索引

联合索引

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

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

代码语言:javascript
复制
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

看个例子:

代码语言:javascript
复制
select id from user_table where name= '张三'

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

代码语言:javascript
复制
select password from user_table where name= '张三'

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

主键和索引有什么区别

主键索引
  • 主键是一种约束,唯一索引是一种索引,两者在本质上是不同的。
  • 主键创建后一定包含一个唯一性索引,唯一性索引并不一定就是主键。
唯一索引
  • 唯一性索引列允许空值,而主键列不允许为空值。
  • 主键列在创建时,已经默认为非空值 + 唯一索引了。
  • 主键可以被其他表引用为外键,而唯一索引不能。
  • 一个表最多只能创建一个主键,但可以创建多个唯一索引。

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

索引下堆
代码语言:javascript
复制
SET optimizer_switch = 'index_condition_pushdown=on';

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

不使用索引下堆:

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

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

使用索引下堆

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

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

微信号:程序员开发者社区

博客:CSDN 王小明

关注我们,了解更多

参考资料

  • https://www.cnblogs.com/Leo_wl/p/13093705.html
  • https://www.jianshu.com/p/bdc9e57ccf8b
本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2021-05-18,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 程序员奇点 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 什么是索引
  • 什么是覆盖索引
    • 联合索引
    • 主键和索引有什么区别
      • 主键索引
        • 唯一索引
          • 索引下堆
          • 不使用索引下堆:
          • 使用索引下堆
      • 参考资料
      相关产品与服务
      云数据库 SQL Server
      腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
      领券
      问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档