索引类型
索引的本质是一种为了高效获取数据的数据结构。
一般以文件的形式存储在磁盘上。
聚簇索引/主键索引
-
聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。 聚簇索引也叫做聚集索引(clustered index)
-
innodb中的主键索引是一种聚簇索引。
-
聚簇索引就是按照每张表的主键构造一个B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。
个人理解,虽然b+tree中的叶子节点存储了数据,但我始终认为存储的是表里数据的引用,如果同样在索引结构中存储一份数据,那么对表的更新同样也需要更新索引中的数据,这难道不是增加了负担吗?
所以我个人认为,索引结构中的数据只是表数据的引用。
问题1:聚集索引中的叶子节点中的数据包含了数据所有的列,那和表中的数据是否重复了呢?
答:在使用了聚集索引的数据库表中,聚集索引中的数据不是与表中数据重复。聚集索引实际上是表数据的一种物理存储形式,他将表中的数据按照聚集索引键值的顺序进行排列。
- 表中的数据行包含了表的所有列及其对应的值,他们按照聚集索引定义的逻辑顺序存储在磁盘上。
- 聚集索引本身是一个索引结构(如B+树),其中的叶节点直接包含了这些按照索引键排序后的数据行。
当查询时,如果查询条件能够利用聚集索引进行高效检索,数据库系统可以直接从聚集索引中找到并返回所需的数据行,无需再访问其他地方,因为这些数据行本身就是索引的一部分,而不是独立于表数据之外的重复数据。
简而言之,聚集索引并没有复制表中的数据,而是以一种有序的方式组织并存储了表数据,使得查询可以直接通过索引来访问实际的数据行,不存在数据的重复存储。
聚簇索引优点
- 数据访问更快,因为聚簇索引将索引和数据保存在同一个B+树中,因为聚簇索引获取数据比非聚簇索引更快
- 聚簇索引对于主键的排序查找和范围查找速度非常快。
缺点
- 插入速度严重依赖插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因为,对于innodb表,我们一般都会定义一个自增的id列作为主键。
- 更新主键的代价很高,因为将会导致被更新的行移动,因为对于innodb表,我们一般(定义主键为不可更新)
- 二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。
非聚簇索引/二级索引/辅助索引
- 在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找。
- 辅助索引叶子节点存储的不在是行的物理位置,而是主键值,通过辅助索引首先找到的是主键值,在通过主键值找到数据行的数据页,在通过数据页中的page Directory找到数据行。
- innodb辅助索引的叶子节点并不包含记录的全部数据,叶子节点除了包含键值外,还包含了相应行数据的聚簇索引键。
- 辅助索引的存在不影响数据在聚簇索引中的组织,所以一张表可以有多个辅助索引,在innodb中有时也称辅助索引为二级索引
联合索引
联合索引也叫多列索引或者复合索引,他是包含多列的索引类型,能够加速多列查询和排序操作。需要遵从最左匹配原则。
唯一索引
普通索引
索引匹配原则
最左匹配原则
最左优先,以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
“select name from table where like ‘aa%’ ” 这种是可以走索引的。
%aa 这种是不走索引的。
索引优化
- 尽可能的使用索引,为常用查询的列和连接条件建立索引。
- 遇到复合索引时,尽量把索引中的常量带上。
- like语句的前导模糊查询不能使用索引
select * from doc where title like ‘%XX’; --不能使用索引
select * from doc where title like ‘XX%’; --非前导模糊查询,可以使用索引 - 不要在索引列上面做任何操作(计算、函数),否则会导致索引失效而转向全表扫描
- 例如下面的 SQL 语句,即使 date 上建立了索引,也会全表扫描:select * from doc where YEAR(create_time) <= ‘2016’;
- 比如下面的 SQL 语句可以正常使用索引:
select * from order where date < = 2018-01-2412:00:00;
- 利用覆盖索引来进行查询操作,避免回表,减少select * 的使用
- 如果有order by、group by的场景,请注意利用索引的有序性
- 如果明确知道只有一条结果返回,limit 1 能够提高效率