数据库索引基本介绍

ohiyou
289 阅读3分钟

我正在参加「掘金·启航计划」

1.索引

索引是帮助Mysql高效获取数据的数据结构(有序),在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方法引用数据,这样就可以在这些数据结构上实现高级查找算法,这种数据局结构就是索引

1662518071246.png

索引的优缺点

优势劣势
提高数据检索的效率,降低数据库的IO版本索引列也是要占用空间的
通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗索引大大提高了搜索效率,同时也降低更新表的速度

2.索引的结构

索引结构描述
B+Tree索引最常见的索引类型,大部分引擎都支持B+数索引
Hash索引底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询
R-tree(空间索引)空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少
Full-text(全文索引)是一种通过简历倒排索引,快速匹配文档的方式,类似于Lucene,ES等

各存储引擎支持索引的情况

1662518722213.png

1. B+树结构

1.1 二叉树

1662520376124.png

二叉树缺点: 顺序插入时,会形成一个链表,查询性能大大降低.大数据量的情况下,层级较深,查询慢

红黑树缺点: 大数据量的情况下,层级较深,检索速度慢

1.2 B树(多路平衡查找树)

1662520532040.png

以一颗最大度数为5的B-tree为例(每个节点最多存储四个key,五个指针)

树的度数指的是一个节点的子节点个数

B树演示地址:www.cs.usfca.edu/~galles/vis…

1.3 B+树

1662521583287.png

  • 所有的数据都会出现在叶子节点
  • 叶子结点形成一个单向链表

1.4Mysql中的B+树

1662521917551.png

Mysql索引数据结构对经典的B+Tree进行了优化,在原B+Tree的基础上,增加了一个指向相邻叶子节点的指针,就形成了一个带有顺序的指针的B+Tree,提高区间访问的性能

2. Hash索引结构

  • Hash索引只能用于对等比较(=、in),不支持范围查询(between,>,<)
  • 无法利用索引完成排序操作
  • 查询效率高,通常只需要一次索引就可以了,效率通常要高于B+Tree索引
  • 在Mysql中,支持hash索引的是Memory引擎,而InnoDB中具有自适应hash功能,Hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的

3. 为什么InnoDB使用B+树,而不是用B树

  • 相对于二叉树,层级更少,搜索效率高
  • 对于B-Tree,无论是叶子结点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要同样保存大量数据,只能增加数的高度,导致性能降低
  • 相对于Hash索引,B+Tree支持范围匹配及排序操作

4. 索引的分类

分类含义特点关键字
主键索引针对于表中主键创建的索引默认自动创建,只能有一个PRIMARY
唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE
常规索引快速定位特定数据可以有多个
全文索引全文索引查找的是文本中的关键字,而不是比较索引中的值可以有多个FULLTEXT

在InnoDB存储引擎中,根据索引的存储形式,又可以分为一下两种

分类含义特点
聚簇索引(Clustered Index)将数据存储与索引放到了一块,索引结构的叶子结点保存了行数据必须有而且只有一个
二级索引(Secondary Index)将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键可以存在多个

聚簇索引选取规则

  • 如果存在主键,主键索引就是聚集索引
  • 如果不存在主键,将选取一个唯一且非空的索引来作为聚簇索引
  • 如果都不存在,InnoDB会隐式定义一个主键来作为聚簇索引

思考题

  1. 以下SQL 那个执行效率高,为什么![1662536775980]
    1. 上边的执行效率高,因为id为主键,表示该字段是聚集索引可以直接查询出数据
    2. 下面的name是二级索引,会先根据 name 查询出对应的id,再拿着id回表查询聚集索引,才能拿到那一行的数据

1662537162021.png 1662536775980.png

5. 索引的操作语法

  • 创建索引

    CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, ...)

  • 查看索引

    show index from table_name

  • 删除索引

    drop index index_name on table_name

  • 案例:

    -- name字段为姓名字段，该字段的值可能会重复，为该字段创建索引
create index idx_user_name on tb_user(name);

-- phone手机号字段的值非空，且唯一，为该字段创建唯一索引
create unique index idx_user_phone on tb_user (phone);

-- 为profession, age, status创建联合索引
create index idx_user_pro_age_stat on tb_user(profession, age, status);

-- 为email建立合适的索引来提升查询效率
create index idx_user_email on tb_user(email);

-- 删除索引
drop index idx_user_email on tb_user;

6. SQL性能分析

  • SQL执行频率

    show GLOBAL|SESSION STATUS LIKE 'Com_______'  -- 七个下划线;可以查看全局(GLOBAL)或者当前会话(SESSION)
    • 通过以上命令可以查看当前数据库 CURD 的访问次数

  • 慢查询日志

    慢查询日志记录了所有执行时间超过指定参数(默认10秒)的所有sql语句

    Mysql的慢查询默认没有开启,需要在mysql中的配置文件中配置一下信息

    # 开启 Mysql 慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒,sql语句执行超过两秒则会被视为慢日志
long_query_time=2

  • profile详情

    SELECT @@have_profiling -- 查询当前数据库是否支持 profile

1662540592589.png

  • explain执行计划

1662551812915.png

  • EXPLAIN 执行计划各字段含义

1662553108731.png

  • img

  • 从左至右，性能由差到好

  1. ALL: 扫描全表
  2. index: 扫描全部索引树
  3. range: 扫描部分索引，索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<=、>=等的查询
  4. ref: 使用非唯一索引或非唯一索引前缀进行的查找
  5. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
  6. const, system: 单表中最多有一个匹配行，查询起来非常迅速，例如根据主键或唯一索引查询。system是const类型的特例，当查询的表只有一行的情况下， 使用system。
  7. NULL: 不用访问表或者索引，直接就能得到结果，如select 1 from test where 1
avatar
文章
阅读
粉丝