反向索引和自增索引区别_数据库索引复习

数据库索引

帮助MySql高效获取数据的数据结构

B- B+ tree

8da0fc445c3dabac6131188fce4f4a03.png

order 3, max. 3 children, 2 keys

  1. 根节点至少有2个叶子节点

  2. 每个节点最多有m-1个key,升序排列,value也按照键值排列,左边的小于key1,中间的在key1与key2之间,右边的大于key2

  3. 除了跟节点外,其他节点至少有m/2取上整再减1个节点

insert, delete, search O(logN)

self-balancing: insert/delete sometimes split and promote

完美的平衡,即所有叶子节点都在同一层

为什么要用B+ B-树来实现索引

举个例子,当你要查找一个节点的时候,其key是8,那么这个key对应的value只存了一个值假设值为字符串"hello",它被存放在内存中的页号为100的页中,因此内存就会调入页号为100的页。这时,你又查找了key为9的节点,其对应的value是字符串"world",由于红黑树节点的地址是不连续的,所以它有可能被存在离key为8的节点很远的地址,比如它被存放在页号为200的页中,因此内存又要调入页号为200的页。这就会引起大量的IO操作

而B树通过将多个节点的key和value存放在一个节点中,在这一个节点中存放的value的地址是连续的,从而减少IO次数

B+ 树

  1. 非叶子节点的子树指针个数与节点中的元素个数相同

  2. 非叶子节点的子树指针,指向key属于[K[i], K[i + 1]]的子树

  3. 所有叶子节点有一个链指针

  4. 所有关键字都在叶子节点出现

  5. 只有叶子节点有data域,其他节点仅仅只用来索引到叶子节点

B-树与B+树

  1. B+树除了叶子节点,其他节点都只有索引,同样的磁盘页B+树可以容纳更多的节点

  2. B+树的范围查询更加方便,可以先找到范围下限,然后通过叶子节点的链表顺序遍历,直至找到上限即可。而B-树只能先找到下限,通过中序遍历查找上限

B+树索引与hash索引的区别

哈希索引适合等值查询,但是无法进行范围查询,没办法利用索引进行排序

哈希索引不支持联合索引的最左匹配原则

如果有大量重复键值的情况,哈希索引的效率会很低,因为存在哈希碰撞

MyISAM & InnoDB

MyISAM:非聚集,叶子节点存放数据记录的地址

InnoDB:聚集,叶子节点存放了完整的数据记录

InnoDB数据文件本身要按照主键聚集,所以InnoDB要求表必须有主键,MyISAM可以没有。如果我们定义了主键,那么InnoDB会选择其作为聚集索引;如果没有显示定义主键,InnoDB会选择第一个不包含NULL值的唯一索引作为主键索引

聚集索引 & 非聚集索引

根本区别:表记录的排列顺序和索引的排列顺序是否一致

聚集索引:表记录的排列顺序和索引的排列顺序一致。就像书的目录,整本书的内容都会根据目录排序,且只可能有一种顺序

优点是查询速度快,因为一旦具有第一个索引值的记录被找到,具有连续索引值的记录一定紧随其后

缺点是对表进行修改速度较慢,这是为了保持表中的记录的物理顺序与索引的顺序一致,而把记录插入到数据页的相应位置,必须在数据页中进行数据的重排列

场合:

a. 此列包含有限数目的不同值

b. 查询的结果返回一个区间

c. 查询的结果返回与某值相同的大量结果集

非聚集索引:表记录的排列顺序和索引的排列顺序不一致。叶子结点不与实际的数据页相重叠,而采用一个指向表中的记录在数据页中的位置的指针。就像书后的索引目录,根据某个特定的属性,来定位书的某一页。

但非聚集索引并不是越多越好,因为需要额外空间来储存索引

场合:

a. 此列包含了大量数目不同的值

b. 查询的结束返回的是少量的结果集

c. order by子句中使用了该列

InnoDB为什么要用自增主键

如果表使用非自增主键,由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页的中间某个位置。频繁的移动,分页操作会造成大量的碎片,和不够紧凑的索引结构,后续不得不用optimize table来重建表优化填充页面。

如果表使用自增主键,每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟新的一页

MySql索引使用注意事项

MySql索引通常是被用于提高WHERE条件的数据行匹配时的搜索速度

  1. 不要在列上使用函数,这将导致索引失效而进行全表扫描

  2. 尽量避免使用!=, not in, <>等否定操作符

  3. 尽量避免使用or来连接条件

  4. 多个单列索引并不是最佳选择

MySql只能使用一个索引,会从多个索引中选择一个限制最为严格的索引,因此为多列创建单列索引,并不能提高MySql查询性能。可以使用复合索引

      5. 复合索引的最左前缀原则

SELECT * FROM news WHERE news_month = 1news_year_month_idx(news_year, news_month)在查询条件中没有使用复合索引的第一个字段,索引不会被使用

      6. 范围查询对多列查询的影响

select * from news where publish_time >= '2017-01-02' and publish_time <= '2017-01-08' and enable = 1

因为范围查询对多列查询的影响,将导致 news_publish_idx(publish_time, enable) 索引中 publish_time 右边所有列都无法使用索引优化查找。换句话说,news_publish_idx(publish_time, enable) 索引等价于 news_publish_idx(publish_time) 

  1. 使范围查询变成普通查询

  2. 用Redis的SortedSet有序集合来保存时间

      7. 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的

      8. 隐式转换的影响

当查询条件左右两侧类型不匹配的时候会发生隐式转换

      9. LIKE语句的索引失效问题

like的方式进行查询,在like "value%"可以使用索引,但是对于"%value%"这样的方式,执行全表查询

创建索引的时候要注意什么

唯一,不为空,经常被查询的字段

非空字段:应该指定为NOT NULL。或者用0,一个特殊的值或者一个空串来代替空值

取值离散大的字段:变量各个取值之间差异程度大的放到联合索引的前面。可以通过count() 函数来查看字段的差异值

索引字段越小越好

InnoDB与MyISAM的区别

  1.  MyISAM支持事务,InnoDB不支持事务

  2. MyISAM是表级锁,InnoDB是行级锁

MyISAM:表级锁有两种模式:表共享读锁,表独占写锁。MyISAM进行读操作时,它不会阻塞其他用户对同一表的读请求,但阻塞写请求。写操作则阻塞读和写请求

InnoDB:行锁是通过给索引加锁来实现的,即只有通过索引条件检索数据,InnoDB才使用行级锁,否则将使用表锁。在InnoDB两个事务发生死锁的时候,会计算出每个事务影响的行数,然后回滚行数少的那个事务。当锁定的场景中不涉及InnoDb的时候,InnoDB是检测不到的。只能依靠锁定超时来解决

     3. MyISAM存储表的总行数,InnoDB不存储总行数

     4. MyISAM非聚集索引,InnoDB聚集索引

索引的分类

唯一索引:索引列所有值都只能出现一次,再为这个数据列创建索引的时候,就应该用关键字UNIQUE把它定义为一个唯一索引

主键:特殊的唯一索引,在一张表中只能定义一个主键索引

InnoDB数据文件本身要按照主键聚集,所以InnoDB要求表必须有主键,MyISAM可以没有。如果我们定义了主键,那么InnoDB会选择其作为聚集索引;如果没有显示定义主键,InnoDB会选择第一个不包含NULL值的唯一索引作为主键索引

组合索引:索引可以覆盖多个数据列

全文索引:类型为FULLTEXT,可以在VARCHAR或者TEXT类型的列上创建

查看是否使用到索引

可以用Explain

table: 显示这一行数据是关于哪张表的

type:显示连接使用了何种类型。一般保证查询至少达到range,最好能达到ref

possible_keys:可能应用的索引

key:实际的索引

key_len: 索引的长度

ref:索引的哪一列被使用了

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

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2021 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值