MySQL的索引

什么是索引？
- 索引类似于书本中的目录，与内容一样，需要占用一定的存储空间。
- 索引需要对数据进行排序，从而在查找时可以快速过滤掉一部分数据。
MySQL的索引可以分为两大类：聚簇索引与二级索引
- 聚簇索引
  ①聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点成为数据页。
  
  ②聚簇索引决定了数据的物理顺序，每张表应该有且仅有一个聚簇索引（绝大多数情况下都是主键），表中的所有行记录数据都是按照聚簇索引的顺序存放的，即只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。
- 二级索引（也称为非聚簇索引或辅助索引）
  ①辅助索引也是通过 B+ 树实现的，但是它的叶节点并不包含行记录的全部数据，仅包含索引中的所有键和一个用于查找对应行记录的“书签”，在 InnoDB 中这个书签就是当前记录的主键。
  
  ②非聚集索引指定了表中记录的逻辑顺序，物理顺序和索引的顺序不一致，所以往往需要回表进行二次查询才能定位到真正的记录。
- 总结：在使用聚簇索引的情况下，数据行的实际内容是直接存储在聚簇索引的叶节点中，而不是单独存储在其他位置。也就是说，数据和聚簇索引是不可分割的。
联合索引
- 也称复合索引，指由两个或以上的字段共同构成一个索引。（根据索引字段进行排序，从而过滤掉一些数据）
- 最左前缀匹配：MySQL 建立联合索引的规则是这样的：它会首先根据联合索引中最左边的、也就是第一个字段进行排序，在第一个字段排序的基础上，再对联合索引中后面的第二个字段进行排序，依此类推。
- 建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销。
- 覆盖索引：如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2，那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是DBA主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。（从辅助索引中就可以查询到记录，而不需要查询聚集索引中的记录）
- Cardinality：对于什么时候添加B+树索引，一般的经验是，在访问表中很少一部分时使用B+树索引才有意义。比如对于性别、省份、学历等字段可取值范围很小，称为低选择性。
索引类型与索引方法
- 索引类型（索引的作用和约束）
  
  FULLTEXT：FULLTEXT 索引是一种用于全文搜索的特殊索引类型。它可以在文本类型的列上进行关键字搜索。只有 MyISAM 和 InnoDB
  存储引擎支持 FULLTEXT 索引。
  NORMAL：NORMAL 索引是最常见的索引类型，也称为 B-Tree 索引。它可以对单个或多个列创建，用于加速查询操作。B-Tree 索引是
  MySQL 默认的索引类型。
  SPATIAL：SPATIAL 索引是用于优化空间数据的索引类型，适用于地理信息系统 (GIS) 数据库。它可以在
  GEOMETRY、POINT、LINESTRING、POLYGON 等空间数据类型的列上创建。
  UNIQUE：UNIQUE 索引是一种强制唯一性约束的索引类型。它要求索引列的值保持唯一，不允许重复。通过创建 UNIQUE
  索引，可以确保列的值在表中是唯一的。
- 索引方法（数据库底层存储引擎在实现索引时采用的数据结构和算法）
  
  BTREE：B-Tree 是一种多叉树结构，也被称为平衡树索引。这是 MySQL 默认的索引类型。B-Tree 索引适用于范围查询，它维护一个有序的索引树，能够以
  O(log n) 的时间复杂度进行查找操作。它广泛应用于普通的数据查询，支持单列索引和多列联合索引。
  HASH：哈希索引使用哈希算法将索引值映射到一个哈希表中的位置，适用于等值查询。哈希索引的查询效率非常高，通常为 O(1)
  的常数时间复杂度。哈希索引不支持范围查询，只能进行精确匹配。在 MySQL 中，哈希索引仅适用于内存表和内存临时表。
索引失效的几种情况：
- 非最左匹配：最左匹配原则指的是，以最左边的为起点字段查询可以使用联合索引，否则将不能使用联合索引。
- 不合理的模糊查询条件：like '张%' 、 like '%张' 、 like '%张%' 只有第一种可以使用索引
- 使用 is not null：当在查询中使用了 is not null 也会导致索引失效，而 is null 则会正常触发索引
- 数据分布不均匀：索引的选择性较低，导致索引不起作用，因为它无法快速过滤掉大部分数据。这可能是因为数据倾斜或者数据分布不均匀。
- 索引列上的函数或表达式操作：如果在索引列上进行了函数操作或表达式操作，数据库无法使用索引进行快速查找，从而失效了索引的效果。

- - 列运算：如果索引列使用了运算，那么索引也会失效，例如- *** where id + 1 = 2
  - 函数：查询列如果使用任意 MySQL 提供的函数就会导致索引失，例如- *** where ifNull(id,1) = 1
  - 类型转换：如果索引列存在类型转换，那么也不会走索引，比如 address 为字符串类型，而查询的时候设置了 int 类型的值就会导致索引失效

__EOF__

本文作者：Stdio.Qu
本文链接：https://www.cnblogs.com/czarQ/articles/17693315.html
关于博主：评论和私信会在第一时间回复。或者直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！