快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

数据库学习— 索引、存储引擎

在线QQ客服:1922638

专业的SQL Server、MySQL数据库同步软件

  • 索引
    • B + TREE
    • 哈希索引
    • MySQL索引
      • 主键索引
      • 唯一索引
      • 常规索引
      • 全文索引
  • 存储引擎
    • InnoDB
    • MyISAM

索引的目的

是提高查询效率。

B树仅指平衡树。平衡树是搜索树,所有叶节点都处于同一级别。

B +树是根据B树和叶节点顺序访问指针实现的。它具有B树的平衡,并通过顺序访问指针来提高间隔查询的性能。

B +树是树数据结构,通常用于数据库和操作系统的文件系统中。

B +树的特点是具有保持数据稳定和有序的能力,并且其插入和修改具有更稳定的对数时间复杂度。 B +树元素从下往上插入,这与二叉树相反。详细信息B +树

在上图中,每个整体块都称为一个磁盘块,每个磁盘块包含几个数据项(灰色)和指针(白色区域中的黑点)。例如,顶部磁盘块包含数据项3和5,并包含下面的三个指针。最左边的指针指示小于3的磁盘块,中间的指针指示3到5之间的磁盘块,最右边的指针指示大于5的磁盘块。实际数据存在于叶节点中,即d1,d2,d3,d4,d5,d6,d7。非叶节点不存储实际数据,仅存储指导搜索方向的数据项。数据表中实际上不存在前3位和前5位。

B +树搜索过程

在执行搜索操作时,首先在根节点上执行二进制搜索,找到键所在的指针,然后在指针指向的节点上进行递归搜索。知道已找到叶节点,然后在叶节点上执行二进制搜索以找到与密钥相对应的数据。

插入和删除操作会破坏余额树的余额,因此,在插入和删除操作时,需要对树执行拆分,合并,旋转和其他操作以保持余额。

B +树的性质

平衡树搜索操作的时间复杂度与树的高度h有关,O(h)= O(logdN),d是每个节点的输出程度。

假设当前数据表中的数据为N,并且每个磁盘块中的数据项的数量为m,则当数据量为h时,h = log(m + 1)N [(m + 1)为基数] N是,m越大,h越小。

为了减少磁盘I/O操作,并非严格按需读取磁盘,而是每次读取磁盘。

操作系统通常将内存和磁盘分成固定大小的块。每个块称为页面。内存和磁盘以单位交换数据。

数据库系统将索引的一个节点的大小设置为页面的大小,以便一个I/O可以完全加载一个节点。并且您可以使用预读功能,相邻节点也可以预先已加载。

  • B +树索引是大多数MySQL存储引擎的默认索引类型。因为不需要执行全表扫描,所以只需要搜索对数,因此搜索速度要快得多。
  • 因为B +树的顺序,所以除了搜索外,它还可以用于排序和分组。
  • InnoDB默认情况下支持b +树

可以使用O(1)时间复杂度来搜索哈希索引,但是它会丢失顺序:

  • 不能用于排序和分组
  • 仅支持精确搜索,不支持部分搜索和范围搜索

InnoDB存储引擎具有一个特殊的功能,当非常频繁地使用索引值时,它将在B +树索引上方创建哈希索引,因此B +树索引具有哈希索引。例如快速的哈希查找。

我看到了一些有关InnoDB不支持哈希索引的信息,但是我也看到了如上所述的InnoDB自适应哈希索引。因此,起初有点令人困惑。

我还看到了信息说明:

  • InnoDB用户无法手动创建哈希索引。在这方面,InnoDB不支持哈希索引。
  • InnoDB将自我调整。如果确定建立自适应哈希索引,则可以提高查询效率。 InnoDB将建立一个相关的哈希索引。在这方面,InnoDB支持哈希索引。

索引是在存储引擎层而不是在服务器层实现的,因此不同的存储引擎具有不同的索引类型和实现。

索引分类

  • 主键索引(主键)
  • 唯一索引(唯一)
  • 常规索引(索引)
  • 全文索引(FullText)

主键:某个属性组可以唯一地标识一条记录。

功能:

  • 最常见的索引类型
  • 确保数据记录的唯一性
  • 确定特定数据记录在数据库中的位置

 

功能:避免在同一表的数据列中出现重复的值。

与主键索引的区别:

  • 只能有一个主键索引
  • 可能有多个唯一索引

 

功能:快速查找特定数据。

在创建表时一起创建:

 

创建表后创建:

 

全文索引主要用于文本文件,例如文章,标题,并且全文索引仅对MyISAM有效( InnoDB在mysql5.6 之后也支持全文索引)。

  • 仅可用于CHAR,VARCHAR,TEXT数据列类型
  • 适用于大型数据集

在构建表时一起创建:

 

创建表后,在现有表上创建索引:

 

MySQL的默认事务存储引擎,仅当您需要它不支持的功能时,才考虑使用其他存储引擎。

实现了四个标准隔离级别。默认级别为” REPEATABLE READ”。在可重复读取隔离级别下,多版本并发控制(MVCC)+下一键锁定(InnoDB的锁定机制)可防止幻像读取。

许多内部优化:

  • 从磁盘读取数据时使用可预测的读取
  • 可以加快读取操作并自动创建的自适应哈希索引
  • 可以加快插入操作等速度的插入缓冲区。

设计简单,数据以紧凑格式存储。

提供了大量功能,包括压缩表和空间数据索引。

不支持交易

不支持行级锁。 仅可以锁定整个表。读取时,向所有需要读取的表添加共享锁,写入时,向表添加排他锁。但是,当表具有读取操作时,您也可以将新记录插入表中,这称为并发插入(CONCURRENT INSERT)。

可以手动或自动执行检查和修复操作,但是与事务恢复和崩溃恢复不同,某些数据可能会丢失,并且修复操作非常慢。

如果指定了DELAY_KEY_WRITE选项,则在每次修改执行完成后,修改后的索引数据将不会立即写入磁盘,而是会写入内存中的键缓冲区。仅当清除键缓冲区或关闭表时,才将相应的索引块写入磁盘。该方法可以大大提高写入性能,但是当数据库或主机崩溃时,它将导致索引损坏,需要执行修复操作。

InnoDB和MyISAM的比较

  • 事务:InnoDB是事务性的,您可以使用Commit和Rollback语句。
  • 并发性:MyISAM仅支持表级锁,而InnoDB还支持行级锁。
  • 外键:InnoDB支持外键。
  • 备份:InnoDB支持在线热备份。
  • 崩溃恢复:MyISAM崩溃后损坏的可能性比InnoDB高得多,并且恢复速度也较慢。
  • 其他功能:MyISAM支持压缩表和空间数据索引。

补充:

InnoDB支持事务,支持行级锁定,支持B树和全文本之类的索引,并且不支持Hash索引;

MyISAM不支持事务,支持表级锁定,支持B树,全文本之类的索引,并且不支持Hash索引。

内存不支持事务,支持表级锁定,支持B树和哈希等索引,并且不支持全文本索引;

NDB支持事务,支持行级锁定,支持哈希索引,并且不支持B树和全文索引等索引;

存档不支持事务,不支持表级锁定,也不支持B树,哈希和全文索引。

向:

  • MySQL索引原理和慢速查询优化
  • CYC

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1922638