数据库索引是数据库管理系统(DBMS)中的排序数据结构,可帮助快速查询和更新数据库表中的数据。
数据以文件形式存储在磁盘上,并且每一行数据都有其磁盘地址。如果没有索引,我们必须从500万行数据中检索一条数据,并且我们只能依次遍历该表中的所有数据(循环调用存储引擎的接口以读取下一行)数据),直到找到该数据为止。但是,有了索引后,您只需在索引中检索该数据,因为它是专门用于快速检索的特殊数据结构。找到数据所在的磁盘地址后,存储后,我们就可以获取数据了。
普通索引(Normal):也称为非唯一索引,它是最常见的索引,没有任何限制。
如果是CHAR,VARCHAR类型,则长度可以小于字段的实际长度;如果是BLOB和TEXT类型,则必须指定长度。
唯一索引:唯一索引要求键值不能重复。另外,应该注意的是,主键索引是一个特殊的唯一索引,它也有一个限制,要求键值不能为空。主键索引是用主键创建的。唯一索引的创建方法与普通索引相似,只是在前面添加唯一
全文索引(Fulltext):对于大数据,例如消息的内容,在这种情况下有几个KB的数据,如果要解决查询效率低的问题,可以创建一个全文索引。只有文本类型的字段才能创建全文索引,例如char,varchar和text。 MyISAM和InnoDB支持全文索引。
二叉查找树的左子树的所有节点均小于父节点,而右子树的所有节点均大于父节点。投影到平面上之后,它是一个有序的线性表。
但是二叉搜索树存在问题:
搜索时间与树的深度有关。在最坏的情况下,时间复杂度将降低为O(n)。
如果我们插入的数据碰巧是有序的2、6、11、13、13、17、22。它将成为一个链表(我们称此树为”偏斜树”),在这种情况下,其目的是加快检索速度无法实现,并且顺序搜索的效率之间没有区别。
由于左右子树之间的深度差太大,因此该树的左子树根本没有节点,也就是说,平衡不够。
2.2平衡二叉树(AVL树)
AVL树(平衡二进制搜索树)
平衡二叉树的定义:任何父节点的左右子树之间的深度差的绝对值不能超过1。
按顺序插入1、2、3、4、5、6,它必须像这样:
构建平衡的二叉树时,将调整节点以确保平衡。
我们已经解决了平衡的问题,那么如何通过使用平衡的二叉树作为索引来查询数据呢?
在一个平衡的二叉树中(一个大小为固定单位的节点),应将哪些内容存储为索引?
第一个是索引的键值。例如,我们在id上创建了一个索引。当我使用id = 1的条件时,我会在索引中找到id的键值。
第二个是数据的磁盘地址,因为索引的作用是查找存储数据的地址。
第三,因为它是二叉树,所以它还必须具有对左子节点和右子节点的引用,以便我们可以找到下一个节点。例如,当它大于26时,请移至右侧,移至下一个树节点,然后继续进行判断。
如果这是数据的存储方式,请让我们看看问题出在哪里。
首先,将索引数据放置在硬盘上。
当我们使用树结构存储索引时,由于获得了一条数据,因此需要在服务器层中比较所需的数据。如果没有,我们必须再次读取磁盘。访问节点时,磁盘会出现IO。 InnoDB操作磁盘的最小单位是页面(或磁盘块),大小为16K(16384字节)。
然后,树的节点大小为16K。
如果我们仅为一个节点存储一个键值+数据+引用,例如一个整数字段,则它可能仅使用十几个或几十个字节,这与16K的容量相去甚远,因此请在IO,浪费了很多空间。
因此,如果每个节点存储的数据太少而无法从索引中找到我们需要的数据,则我们必须访问更多的节点,这意味着与磁盘的交互将过多。
每次从磁盘读取数据需要寻址时间,交互次数越多,消耗的时间就越多。
例如,在上图中,我们在一个表中有6个数据。当我们查询id = 66时,要查询两个子节点,我们需要与磁盘交互3次。如果我们拥有数百万个数据怎么办?这个时间更难估计。
那么我们的解决方案是什么?
首先是让每个节点存储更多数据。
其次,节点上关键字的数量越多,我们的指针越多,这意味着可以有更多的派生(我们称之为”方式数量”)。
由于派生的数量越多,树的深度将减小(根节点为0)。
这样,我们的树是否从原来的高矮外观变成了矮胖外观?
此时,我们的树不再是二叉树,而是多分支或多路的。
2.3多路平衡搜索树(B树)
像AVL树一样,B树在分支和叶节点上存储键值,数据地址和节点引用。
它具有一项功能:派生数(方式数)总是比关键字数多1。例如,在我们绘制的树中,每个节点存储两个关键字,然后将有三个指针指向三个子节点。
B树的搜索规则是什么?
例如,我们要在此表中查找15,因为15小于17,请移至左侧,因为15大于12,请移至右侧。在磁盘块7中发现了15个,仅使用了3个IO。
2.4 B +树(增强型多渠道平衡搜索树)
B树的效率已经很高。为什么MySQL需要改进B树并最终使用B +树?通常,此改进的B-Tree版本比B-Tree解决更全面的问题。
让我们看一下InnoDB中B +树的存储结构:
1)其关键字的数量等于频道的数量;
2)B +树的根节点或分支节点都不会存储数据,只有叶节点不会存储数据。
当前的看法:我们要存储什么数据?它是真实数据的地址吗?
搜索到的关键字将不会直接返回,而是会转到最后一层的叶节点。例如,如果我们搜索id = 28,尽管它直接在第一层上命中,但数据地址在叶节点上方,因此我必须继续向下搜索到叶节点。
3)B + Tree的每个叶子节点都添加一个指向相邻叶子节点的指针,其最后一个数据将指向下一个叶子节点的第一个数据,从而形成一个有序的链表结构。
1)它是B树的变体。它可以解决B树可以解决的问题。 B树解决了两个主要问题? (每个节点存储更多关键字;更多渠道)
2)扫描库和扫描表的能力更强(要扫描表的整个表,您只需要遍历叶节点,就不需要遍历整个B + Tree来获取所有数据)
3)B + Tree的磁盘读写能力比B Tree强(根节点和分支节点不保存数据,因此一个节点可以保存更多关键字,并且一次加载更多关键字)
4)排序能力更强(因为在叶节点上有一个指向下一个数据区域的指针,因此数据形成了一个链表)
5)效率更稳定(B + Tree总是在叶节点处获取数据,因此IO数是稳定的)
2.5索引方法:真的是B +树吗?
在Navicat的工具中,创建索引。有两种Hash和B Tree索引方法(最底层是B + Tree)。
HASH:以KV的形式检索数据,即它将根据索引字段生成哈希码和指针,该指针指向该数据。
哈希索引的特征是什么?
首先,其时间复杂度为O(1),并且查询速度相对较快。但是,哈希索引中的数据未按顺序存储,因此不能用于排序。
其次,我们在查询数据时需要根据键值计算哈希码,因此它只能支持等效查询(= IN),而不支持范围查询(\ gt; \ gt; = \ lt ; =和之间)。
第三:如果字段中有很多重复的值,将会有很多哈希冲突(使用zipper方法),并且效率会降低。
应当注意,InnoDB无法显式创建哈希索引(所谓的哈希索引支持指的是AHI)。
内存存储引擎可以使用哈希索引。
不同的存储引擎在磁盘上具有不同的文件。
在MyISAM中,还有另外两个文件
一个是.MYD文件,D代表数据,Data是一个MYISAM数据文件,用于存储表中的所有数据。
一个是.MYI文件,我代表Index,它是MYISAM的索引文件。它存储索引。例如,如果我们在id字段上创建主键索引,则主键索引位于此索引文件中。
换句话说,在MYISAM中,索引和数据是两个单独的文件。
那么我们如何根据索引查找数据?
在MYISAM的B +树中,叶节点存储与数据文件相对应的磁盘地址。因此,在索引文件.MYI中找到键值之后,将在数据文件.MYD中获得相应的数据记录。
如果它是二级索引,有什么区别?
在MYISAM中,辅助索引也位于.MYI文件中。
二级索引和主键索引在存储和检索数据方面没有区别。相同的是在索引文件中找到磁盘地址,然后在数据文件中获取数据。
这是MyISAM中索引登陆的形式。但是在我们的InnoDB中是不同的。
在InnoDB中,它使用主键作为索引来组织数据存储,因此索引文件和数据文件是同一个文件,都位于.ibd文件中。
在InnoDB主键索引的叶节点上,它直接存储我们的数据。
聚集索引(clustered index):索引键值的逻辑顺序与表数据行的物理顺序一致。 (例如,字典的目录按拼音排序,内容也按拼音排序。这种按拼音排序的目录称为聚簇索引)。
在InnoDB中,称为数据的组织方式(聚簇索引组织表),因此主键索引是聚簇索引,非主键都是非聚簇索引(二级索引)。
除主键以外的索引,例如基于名称字段的普通索引,如何存储和检索数据?
二级索引存储二级索引和主键值。如果使用辅助索引查询,则将基于主键值查询主键索引,最后获取数据。
例如,我们使用名称索引来查询name =” Qingshan”,它将在叶节点中找到主键值,即id = 1,然后将数据获取到主键索引的叶节点。
如果表没有主键怎么办?
1)如果我们定义一个主键(Primary Key),那么InnoDB将选择该主键作为聚簇索引。
2)如果不显示已定义的主键,InnoDB将选择不包含空值的第一个唯一索引作为聚簇索引。
3)如果没有这样的唯一索引,InnoDB将选择内置的字节长ROWID作为隐藏的聚集索引,并且他将通过写行记录来增加主键。
如果色谱柱的重复值更多,则分散度较低;重复值越少,分散度越高。如果有很多重复的数据值,则查询时需要扫描的行数会更多,因此在创建索引时,通常选择分散度较高的字段。
4.2联合索引的最左侧匹配项
前面我们讨论了为单个列创建的索引,但是有时我们的多条件查询也会建立联合索引。例如:查询分数时,必须同时输入ID卡和考试编号。单列索引可以视为特殊的联合索引。
例如,用户表为名称和电话建立联合索引。
联合索引是B +树中的一致数据结构。它从左到右构建搜索树(名称在左侧,电话在右侧)。
从这张照片中可以看到,名称是有序的,而电话是无序的。名称相等时,将订购电话。
这时,当我们使用name =” Qingshan”和phone =” 136xx”来查询数据时,B + Tree将首先比较名称以确定下一次搜索的方向(左还是右)。如果名称相同,则比较手机。但是,如果查询条件没有名称,则您不知道第一步应该检查哪个节点,因为名称是构建搜索树时的第一个比较因素,因此不使用索引。
4.3联合索引何时使用
当条件包含a,ab,abc时,将使用联合索引,可以将其视为索引桥。
返回表:非主键索引,我们首先通过索引找到主键索引的键值,然后使用主键值找出不在索引中的数据。它比基于主键索引的查询对索引树的扫描要多。此过程称为”返回表”。
覆盖索引 ,这样就可以避免返回表格。
创建一个包含名称和电话字段的联合索引。以下三个查询语句均使用覆盖索引:
选择*,不使用覆盖索引。
因为索引在提高查询性能中起着巨大作用,所以我们的目标是尽可能使用索引。
1.在(on)字段上创建一个索引,用于确定
-浪费空间,更新缓慢。
3.字段太长,建立前缀索引。
4.区分度低的字段(例如性别)不会建立索引。
-色散太低,导致扫描线过多。
5.经常更新的值不应用作主键或索引。
6.随机和无序值,不建议用作主键索引,例如ID卡,UUID
7.组合索引将具有高散列性(高判别力)的值放在前面
8.创建复合索引,而不是修改单列索引
2.字符串未用引号引起,并出现隐式转换
实际上,无论是否使用索引,最终由优化器拥有最终决定权。
优化器基于什么优化器?
基于成本(CostBaseOptimizer),它不基于规则(Rule-BasedOptimizer),也不基于语义。无论开销如何,它都会来。有使用索引的基本原则,但是没有特定的规则。没有在任何情况下都必须使用索引,并且在所有情况下都不得使用索引。
索引肯定会提高查询性能吗?
通常,按索引查询数据要比全表扫描更快,但我们还必须注意其成本。
索引需要存储空间,并且需要定期维护。只要表中有记录增加或减少或索引列被修改,索引本身也将被修改。这意味着,每条INSERT,DELETE,UPDATE记录将为此付出4.5倍的磁盘I/O。
索引不仅会降低插入和修改的效率,而且在查询时还会有一个查询优化器。索引过多将使优化器混乱。可能找不到正确的查询路径,因此索引变慢。
1.根据一系列搜索,常规查询返回的结果集少于表
2.基于非唯一索引的搜索
3.直接升级以覆盖索引,避免使用多个查找表
在什么情况下设置了索引但不能使用
根本原因是查询优化器决定不使用索引:
SQL语句的查询可以具有不同的执行方案。至于最终选择哪种方案,您需要通过优化器进行选择,以选择最低的执行成本。在实际执行单表查询语句之前,MySQL的查询优化器将找到用于执行该语句的所有可能的解决方案,然后进行比较后,找到成本最低的解决方案。成本最低的解决方案就是所谓的执行计划。优化过程大致如下:
1.根据搜索条件,找到所有可能的索引
2.计算全表扫描的费用
3.计算使用不同索引执行查询的成本
4.比较各种执行方案的成本,找到成本最低的方案
有时查询语句不符合索引的要求也会导致索引无法使用,如下:
- 索引单个字段,其中条件为多个字段。
- 创建一个组合索引,其中condition是单个字段。反之亦然。 INDEX(a,b,c),当条件为a或a,b或a,b,c或a,c时可以使用索引,但当文章
当文件为b,c时,将不使用索引。换句话说,如果不使用第一部分,则不会使用索引。如果它是INDEX(a,b),即使查询在哪里b,由于SQL优化器的优化,a也会用a,b替换b,a,因此您可以转到索引。如果它是索引(a,b,c),则查询为(a,b,c,d)将不会转到索引
- 或在条件中使用,即使存在带有索引的条件,也不会使用索引查询(这是查询不应使用或尝试在其中使用的原因)条件中的每一列进行了索引,因此每列在查询时都会使用自己的索引)
- 模糊查询的模糊词,例如在字符串前面,例如以%或_开头,索引无效。
- 当索引不等于时(索引为null,不为null ,! =,\ gt;),将无法使用索引,将导致全表扫描。
- 类型错误。例如,如果字段类型为varchar,则该数字用于where条件。
- 内部函数应用于索引。在这种情况下,应建立基于功能的索引。
- 索引列不能是表达式的一部分(id + 1 = 5),也不能是函数的参数
- 如果MySQL希望使用全表扫描比使用索引更快,请不要使用索引
在什么情况下需要设置索引,在什么情况下不需要
1)。主键自动创建唯一索引
2)。经常用作查询条件的字段应被索引
3)。与查询中其他表相关联的字段(外键关系已建立索引)
4)。单键/组合索引选择问题(在高并发下创建组合索引的趋势)
5)。查询中的排序字段,如果按索引访问排序字段,则排序速度将大大提高
6)。查询中的统计信息或分组字段
2)。频繁地添加,删除和修改表(不仅是为了保存数据,还为了保存索引文件)索引最初是在写入阶段形成某种数据结构的一种方法,从而使读取阶段更加高效,但是,如果一个字段写入过多而读取较少,则会降低写入速度。
3)。具有重复数据和均匀分布(例如性别)的表字段,因此您应该仅索引最频繁查询和最频繁排序的数据列。
4)。在where条件中未使用的字段未编入索引
参考:MySQL数据库索引
如果有缺陷,请纠正我,谢谢!