一、索引的好处
- 索引用于快速查找具有特定列值的行
- 如果没有索引,MySQL 必须从第一行开始,然后通读整个表以查找相关行
- 如果该表具有相关列的索引,MySQL 可以快速确定要在数据文件中间查找的位置,而无需查看所有数据。这比顺序读取每一行要快得多。
二、哪些操作使用索引
- WHERE查找
- join操作 :如果将列声明为相同的类型和大小,MySQL 可以更有效地使用列上的索引
- 组合索引:如果表具有多列索引,则优化器可以使用索引的任何最左边的前缀来查找行;举例来说,如果你有一个三列的索引 (col1, col2, col3),你有索引的搜索功能(col1), (col1, col2)以及(col1, col2, col3)
- 聚合函数:查找特定索引列的MIN()或 MAX()值key_col
- order by,group by
- 覆盖索引:如果查询仅使用某个索引中包含的表中的列,则可以从索引树中检索所选值以提高速度
三、索引的数据结构
3.1 B+ 树
适用范围:
1、范围查询:=、 >、 >=、 <、 <=、 或BETWEEN运算符的表达式中进行列比较
2、前缀查询:LIKE
3、排序和分页:limit offset,count
3.2 哈希索引
- 它们仅用于使用=or<=> 运算符的相等比较 (但速度非常快)
- 只能使用整个键来搜索一行
四、聚集索引和二级索引
4.1 聚集索引
InnoDB 作为存储引擎的表都有一个特殊的索引称为聚集索引,它保存着每一行的数据,通常,聚集索引就是主键索引
- 如果你的表定义了一个主键,InnoDB 就使用它作为聚集索
- 如果你的表没有定义主键,InnoDB 会选择第一个唯一非空索引来作为聚集索引。
- 如果你的表既没有主键,又没有合适的唯一索引,InnoDB 内部会生成一个隐式聚集索引 ——GEN_CLUST_INDEX,该索引建立在由 rowid 组成的合成列上。数据行根据 InnoDB 分配的 rowid排序,rowid 是一个 6 字节的字段,随着数据插入而单调递增
4.2 二级索引
聚集索引外的其他索引类型都属于二级索引。在 InnoDB 中,二级索引中的每个记录都包含该行的主键列,以及二级索引指定的列;聚集索引中,InnoDB 通过主键值来查询数据行。
表1 非主键索引字段值重复
如图2所示,首先,每个叶子节点存储了主键的值;对于非叶子节点,不仅存储了索引字段的值,同时也存储了对应的主键的最小值。