数据库索引

数据库 专栏收录该内容
23 篇文章 0 订阅

1. 概述

  1. 为什么要使用索引?

    • 快速查询数据
    • (最简单的方式实现数据查询,即全表扫描,将整张表的数据全部或者分批次加载到内存中。存储的最小单位是块或者页,他们是由多行数据组成。将块加载进来,逐个块轮询,找到目标并返回。这种方式普遍比较慢。很多情况下都要避免全表扫描情况的发生,所以数据库引入更高效的机制,即索引。关键信息和查找信息的方式组成索引,通过索引可以大幅提升查询速度。)
  2. 什么数据能成为索引?

  • 主键、唯一键、普通键
  • (把记录限定在一定查找范围内的字段,主键便是一个很好的切入点,其他包括唯一键、普通键等也可以作为索引。)

2. 索引的分类

主键索引(列值唯一,表中只有一个)、唯一索引(列值唯一)、普通索引、全文索引、联合索引

  1. 创建索引
直接创建索引
	-- 创建唯一索引
	CREATE UNIQUE INDEX index_name ON table_name(col_name);
	-- 创建普通索引
	CREATE INDEX index_name ON table_name(col_name);
	-- 创建唯一组合索引
	CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);
	-- 创建普通组合索引
	CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
	 
	通过修改表结构创建索引
	ALTER TABLE table_name ADD INDEX index_name(col_name);
	 
	创建表的时候直接指定
	CREATE TABLE mytable(
	       id INT NOT NULL, 
	       username VARCHAR(16) NOT NULL, 
	       INDEX [indexName] (username(length))
	 );
  1. 删除索引
-- 直接删除索引
DROP INDEX index_name ON table_name;
-- 修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;
  1. 查看索引

#查看:
show index from `表名`;
#或
show keys from `表名`;
  1. 其他命令

-- 查看表结构
desc table_name;
-- 查看生成表的SQL
show create table table_name;

3. 索引的数据结构

生成索引,建立二叉查找树进行二分查找。

生成索引,建立B-Tree结构进行查找。

生成索引,建立B±Tree结构进行查找。

生成索引,建立Hash结构进行查找。

(让查询变得高效的数据结构,如二叉查找树和二叉查找树的变种平衡二叉树、红黑树、BTree、B+Tree以及Hash结构。MySQL数据库索引是通过B+Tree实现。)

4. 索引优化

4.1 利用二叉树优化

  1. 二叉查找树是每个节点最多有两个子树的树结构,通常子树被称为左子树或右子树。左子树节点的值均小于根节点,右子树节点的值均大于根节点(注意索引的存储块和数据库的最小存储单位块或者页并非一一对应,为了方便理解先一一对应起来)。每个存储块存储的是关键字和指向子树的指针。平衡二叉树任意一个节点的左子树和右子树高度差不超过1。
  2. 查询时间复杂度O(logn),查询效率高。极端情况(节点全部在左子树或右子树上)时间复杂度将为O(n)。可以利用树的旋转的特性保持树为平衡二叉树。
  3. 但还有另一个问题,影响程序运行速度的瓶颈是IO。如果假定索引块在磁盘中,找索引会先发生一次IO,将数据读入内存中,之后再发生IO继续查找,直到找到。检索深度每增加1,就发生一次IO。平衡二叉树、红黑树等每个节点只能有两个孩子。为了组织起数据块,树的深度很深,IO的次数也会很多,检索性能没法满足优化查询需求。
  4. 即降低查询的时间复杂度,又降低IO的次数,要让树每个节点能承受的数据多一些,即利用B-Tree、B±Tree。

4.2 利用B树优化

  1. B树,即平衡多路查找树。每个节点最多有m个孩子,这样的树即为m阶B树。每个存储块主要包含关键字和指向孩子的指针,最多能有几个孩子取决于每个存储块的容量和数据库的相关配置(通常情况下m是很大的)。
  2. B树特征:
    1. 根节点至少包括两个孩子。
    2. 树中每个节点最多含有m个孩子(m>=2)。
    3. 除根节点和叶节点外,每个节点至少有ceil(m/2)个孩子。ceil向上取整
    4. 所有叶子节点位于同一层。
    5. 假设每个非终端节点中包含有n个关键字信息,其中:
      1.  Ki(i=1...n)为关键字,且关键字按顺序升序排序K(i-1)<K(i)。
        
      2. 关键字的个数n必须满足:[ceil(m/2)-1]<=n<=m-1。(任意节点的关键字个数上限比它的孩子数上限少一个,且对于非叶子节点来说,任何一个节点的关键字个数比指向孩子的指针数少一个)
      3. 非叶子节点的指针:P[1],P[2]…P[M],其中P[1]指向关键字小于K[1]的子树(某节点最左边孩子节点关键字的值均小于该节点最左边关键字的值),P[M]指向关键字大于K[M-1]的子树(某节点最右边孩子节点的关键字的值均大于该节点里所有关键字的值),其他P[i]指向关键字属于(K[i-1],K[i])的子树(某节点其余孩子节点关键字的值的大小均位于离该孩子节点指针最近的两个关键值之间)。
  3. 查找效率和二叉查找树一样,为O(logn)。B树通过合并、分裂、上移、下移节点保持特征,使树比二叉树矮,数据不断变动后不会变成线性的。
  4. B树示例:在这里插入图片描述

4.3 利用B+树优化

B+树是B树的变体,其定义基本与B树相同,除了:

  1. 非叶子节点的子树指针与关键字个数相同。(B+树能存储更多的关键字)
  2. 非叶子节点的子树指针P[i],指向关键值(K[i],K[i+1])的子树。(K[i]指向的子树,均小于关键字K[i+1]的值)
  3. 非叶子节点仅用来索引,数据都保存在叶子节点中。(B+树所有的检索都是从根部开始,检索到叶子节点结束,非叶子节点仅存储索引不存储数据,能存储更多的数据。B+树相对B树更矮。B树的搜索可能在任何一个非叶子节点就终结掉了。)
  4. 所有叶子节点均有一个链指针指向下一个叶子节点并按大小顺序链接。(支持范围统计,即定位到某个叶子节点便可以从该叶子节点开始横向跨子树统计。)
  5. B+树示例:在这里插入图片描述
  6. B+树的优势
    1. B+树的磁盘读取代价更低(B+树内部结构没有指向关键字具体信息的指针,不存放数据,只存放索引信息。内部节点相对B树更小。如果把所有内部节点的关键字存放在同一盘块中,盘块能容纳的关键字数量也越多,一次性读入内存查找的关键字也就越多,相对来说IO读写次数低)。
    2. B+树的查询效率更加稳定(内部节点不是指向文件内容的节点,只是叶子节点中关键字的索引,任何节点的查找必须有一条从叶子节点到根节点的路,所有关键字查询的长度相同,每个数据的查询时间相同,O(logn))。
    3. B+树更有利于对数据库的扫描(B+树只需要遍历叶子节点就可以解决对全部数据的扫描)。
      B+树示例:在这里插入图片描述

4.4 利用Hash优化

  1. 优点:根据Hash函数的运算只需1次定位便能找到需要查询数据所在的头。Hash索引的查询效率理论上高于B+树索引。
  2. 缺点:
    1. 仅仅能满足“=”,“IN”,不能使用查询范围。(Hash索引比较的是进行Hash运算后的Hash值,只能用于等值的过滤,不能用于基于范围的查询,因为经过相应的Hash算法处理过的Hash值的大小关系不能保证和Hash运算前的完全一样。)
    2. 无法被用来避免数据的排序操作。
    3. 不能利用部分索引键查询。(对于组合索引,Hash索引在计算Hash值的时候是组合键,将键组合合并后在一起计算Hash值,而不是单独计算Hash值。通过组合索引的前一个或几个索引键进行查询时Hash索引也无法被利用。B+树支持利用组合索引中的部分索引。)
    4. 遇到大量Hash值相等的情况性能并不一定会比B树索引高。在这里插入图片描述

4.5 利用BitMap优化

  1. 优点:
    1. 当表中的某个字段只有几种值的时候,在该字段上实现高效统计用位图索引是最佳的选择。
    2. 目前很少数据库支持位图索引,已知比较主流的是Oracle。
    3. 位图索引的结构类似B+树。在存储方式上会先按照状态值分开,每种值的空间存放每个实际的数据行是否是这个值。因为只需要存放是与否,所以只需要一个Bit位存放。理论上一个叶子块可以存放非常多的Bit位来表示不同的行。
  2. 缺点:
    1.锁的密度非常大,当尝试新增或修改数据时,与它在同一个位图的数据操作都会被锁住。
    1. 因为某行所在的位置顺序会因为数据的添加或者删除而发生改变。不适合高并发的联机事务处理系统,即常见的OLTP系统。而适合并发较少,统计数据较多的OLAP系统。

5. 密集索引与稀疏索引的区别

  1. 密集索引文件中的每个搜索码值都对应一个索引值。(叶子节点不仅保存键值,还保存了位于同一行记录里的其他列的信息。密集索引决定了表的物理排列顺序,一个表只能有一个物理排列顺序,所以一个表只能创建一个密集索引。)
  2. 稀疏索引文件只为索引码的某些值建立索引项。(叶子节点仅保存键位信息和该行数据的地址,有的稀疏索引仅保存键位信息及其主键。定位到叶子节点仍需要地址或主键信息进一步定位到数据。)在这里插入图片描述
  3. MySQL常见的两种的存储引擎:
    1. MyISAM:主键索引、唯一键索引、普通索引其索引均属于稀疏索引
    2. InnoDB:必须有且仅有一个密集索引,密集索引的选取规则如下:
      1. 若一个主键被定义,则该主键作为密集索引。
      2. 如果没有主键被定义,该表的第一个唯一非空索引则作为密集索引。
      3. 若不满足以上条件,innodb内部会生成一个隐藏主键(密集索引)。
      4. 非主键索引存储相关键位和其对应的主键值,包含两次查找。(非主键索引即稀疏索引的叶子节点不存储行数据的物理地址,而是存储的该行的主键值,所以非主键索引包含两次查找,一次查找次级索引自身,再查找主键。见下图左)在这里插入图片描述在这里插入图片描述
  4. InnoDB使用密集索引,将主键组织到一棵B+树中,行数据就存储在叶子节点上。因为InnoDB的主键索引和对应的数据是保存在同一个文件,检索时在加载叶子节点的数据进入内存时,也加载了对应的数据。若对稀疏索引进行条件筛选,首先在稀疏索引的B+树中检索该键,获取到主键信息。然后利用主键在密集索引B+树中再执行一遍检索操作,最终到达叶子节点,获取整行数据。
  5. MyISAM均为稀疏索引,稀疏索引的两棵B+树节点结构完全一致,只是存储的内容不一样。主键索引B+树存储主键,辅助键索引B+树存储辅助键,表数据存储在独立的地方,索引和数据是分开存储的。两棵B+树的叶子节点都使用地址指向真正的表数据。对于表数据来说,两个键没有任何差别。通过辅助键检索无需访问主键的索引树。

https://www.cnblogs.com/jiawen010/p/11805241.html
https://www.cnblogs.com/myseries/p/11265849.html

6. 索引模块的面试问题(MySQL)

6.1 如何定位并优化慢查询SQL

  1. 根据日志定位慢查询sql
    1. 慢日志是记录执行的比较慢的SQL。
    2. 执行show variable like '%query%';显示long_query_time为10秒即SQL执行时间超过10秒会被记录在慢日志中
    3. slow_query_log为OFF慢日志为关闭状态,slow_query_log_file慢日志存储地址。通过set global slow_query_log=on;设置打开慢日志。
    4. 该语句只是暂时保存,重启数据库服务会还原成原来的样子。在配置文件中修改可以永久保存
  2. 使用explain等工具分析SQL
    1. 关键字放在select查询语句的前面,用于描述MySQL如何执行查询操作,以及MySQL成功返回结果集需要执行的行数。explain可以分析select语句,知道查询效率低下的原因,从而改进查询。
    2. explain关键字字段
      1. type ,MySQL找到需要数据行的方式,性能从最优到最差排序如下:
        system,const,eq_ref,ref,fulltext,ref_or_null,index_merge,unique_subquery, index_subquery,range,index,all 。index和all查询是全表扫描
      2. extra在这里插入图片描述
    3. 修改SQL或尽快让SQL走索引
      1. 只有DML数据操纵语言才会进慢查询语句中,
      2. DDL数据定义语言不会进入慢SQL 。
      3. DQL数据查询语言 select
      4. DML 数据操纵语言 insert、update、deleteDDL
      5. 数据定义语言 crete、dropDCL
      6. 数据控制语言 grant、revoke

6.2 联合索引的最左匹配原则的成因?

  1. 联合索引:由多列组成的索引
  2. 最左匹配原则:假设有两列A、B,对A设置联合索引,即将A和B都设置为索引,顺序是A、B。在where语句中调用where A=? and B=?,会走这个索引; 调用where A=?也会走这个索引;调用where B=?就不走这个索引了。
    1. 最左匹配非常重要的原则,MySQL会一直向右匹配,直到遇到范围查询(>、<、between、like)就停止匹配。比如a=3 and b=4 and c>5 and d=6,如果建立(a,b,c,d)顺序的索引,d是用不到索引的;如果建立(a,b,d,c)的索引,则都可以用到,a,b,d的顺序都可以任意调整。
    2. 和in可以乱序,比如a=1 and b=2 and c=3建立(a,b,c)索引可以任意顺序,MySQL的查询优化器会帮你优化成索引可以识别的形式。
  3. 联合索引的最左匹配原则的成因:MySQL创建联合索引首先会对复合索引最左边即第一个索引字段的数据进行排序,在第一个排序字段的基础上再对后面第二个索引字段进行排序,类似实现了order by 字段1 order by 字段2,第一个字段绝对有序第二个字段无序。因此MySQL用第二个字段进行条件判断是用不到索引的。

6.3 索引是建立的越多越好吗?

不是

  1. 数据量小的表不需要建立索引,建立会增加额外的索引开销。
  2. 数据变更需要维护索引,因此更多的索引意味着更多的维护成本。
  3. 更多的索引意味着需要更多的空间。

6.4 索引失效

https://blog.csdn.net/junjunba2689/article/details/82020961

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值