mysql索引总结

本文深入探讨了SQL中的B-Tree和哈希索引的工作原理及其使用限制,包括索引的选择性、覆盖索引、聚簇索引的概念及如何优化查询性能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

索引类型

1.B-Tree索引

B-Tree索引在InnoDB引擎中既存储数据行的地址又存储列数据本身

B-Tree索引的适用范围

1)全值匹配。指的是与索引中的所有列全部匹配
2)匹配最左前缀。指的是对于多列索引,可以只是用第一列。
3)匹配列前缀。指的是只匹配某一列的开头部分字母,这里需要注意的是对于多列索引,如果后面的列要使用列前缀,则其前面列都必须匹配,不能跳过索引中的列。
4)匹配范围值。指的是搜索列中的某一范围而不是固定值是,索引也能匹配,对于多列索引只会用到第一列。
5)精确匹配某一列并范围匹配另外一列
6)只访问索引的查询。即查询的列索引本身可以提供,不用再访问数据行。

B-Tree索引的限制

1)不是按照索引的最左列开始查找,则无法使用索引。即对于多列的索引,如果第一列未匹配,尽管匹配后续的列,然而索引不起作用。
2)不能跳过索引中的列。假设三个列组成的多列索引,查询条件只指定第一列和第三列,不指定第二列,则mysql只能使用索引的第一列。
3)如果查询中有某个列的范围查询,则其右边的所有列无法使用索引优化查找。假设三个列的多列索引,查询指定全部三个列,然而第二个列是范围查询,如使用like,则索引的第三列不发挥作用。

2.哈希索引

基于哈希表实现,只有精准匹配索引的所有列的查询才有效,哈希索引只存储对应的哈希值,索引结构十分紧凑,查找速度非常快。在mysql中,只有Memory引擎支持哈希索引。

InnDB引擎有个特殊功能叫“自适应哈希索引”,当某些索引被使用的很频繁,它会在内存之中基于B-Tree索引之上再建一个哈希索引,让B-Tree索引也具有哈希索引的一些优点,比如快速的哈希查找。这是一个完全自动的、内部的行为。

其他类型的索引暂不总结。

三星系统(three-star system)

索引的三星系统:索引将相关的记录放到一起则获得一星;如果索引中的数据顺序和查找中的排序顺序一致则获得二星;如果索引中的列包含了查询中需要的全部列则获得三星。

索引策略

必须是独立的列

“独立的列”指的是索引列不能是表达式的一部分,也不能是函数的参数,如果查询中的列不是独立的,则mysql不会使用索引。

索引的选择性

选择性指的是,不重复的索引值(也称基数)和数据表记录总数的比值,比值越大则查询效率越高,因为高选择性的索引可以在查询时过滤掉更多的行,比如唯一索引的选择性是1,这是最好的。
对于前缀索引要选择足够长的前缀以保证较高的选择性,同时不能太长以节约空间。

选择合适的索引列顺序

对于多列的B-Tree索引,通常可以将选择性最高的列放在索引的最前列。

聚簇索引

1)聚簇索引不是一种单独的索引,而是一种数据存储方式,InnoDB的聚簇索引在同一个结构中保存了B-Tree索引和数据行。
2)聚簇索引叶子页包含了行的全部数据,节点页只包含索引列。
3)InnoDB一般选择主键作为聚簇索引,如果没有主键,会选择一个唯一的非空索引,如果也没有这样的索引,就会隐式定义一个主键作为聚簇索引。
4)解释下InnoDB二级索引为什么比主键索引慢。二级索引的叶子节点保存的是引用行的主键列,意味着二级索引查找行时,存储引擎现在二级索引叶子节点获得对应的主键值,然后去聚簇索引中查找对应的行,相当于做了两次B-Tree查询。然而对于MyISAM索引,由于数据不是聚簇索引方式存储的,所以主键索引和其他索引没什么不同。
5)最好避免随机的聚簇索引,如以UUID作为聚簇索引,使得聚簇索引的插入变得完全随机,会造成页分裂和碎片,导致插入操作效率降低。应该尽可能按主键顺序插入,并尽可能使用单调增加的聚簇索引的值来插入。

覆盖索引

  1. 一个索引如果包含需要查询的字段值,就是“覆盖索引”
  2. 索引条目通常远小于数据行大小,如果只需要读取索引,mysql会极大减少数据访问。
  3. 覆盖索引对于InnoDB特别有用,二级索引如果能覆盖查询,则可以避免对主键索引的二次查询。
  4. 不是所有索引都可以作为覆盖索引,覆盖索引必须存储索引列的值,而哈希索引、空间索引、全文索引等都不存储索引列的值,所以mysql只能使用B-Tree索引做覆盖索引。
  5. InnoDB的二级索引的叶子节点都包含了主键的值,意味着InnoDB的二级索引可以有效利用这些额外的主键列来覆盖查询。

使用索引扫描排序

  1. MySQL生成有序结果的两种方式:排序操作和按索引顺序扫描。
  2. 当索引的列顺序和ORDER BY子句顺序完全一致,并且所有列的排序方向(倒序或正序)都一样时,MySQL才能够使用索引对结果做排序。若查询关联了多张表,则只有当ORDER BY子句引用的字段全部为第一个表时,才能使用索引做排序。
  3. ORDER BY子句和查找型查询的限制是一样的,需要满足索引的最左前缀的要求。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值