1、为什么使用索引
通过索引查找相关数据,避免全表扫描。主要目的是为了减少磁盘IO次数,加快查询效率。
2、索引及其优缺点
索引是在存储引擎中实现的帮助MySQL高效获取数据的数据结构,可以在其基础上实现高级查找算法。
优点:
1、提高数据检索的效率,降低数据库的IO成本。
2、通过创建唯一索引,可以保证数据库表中每一行的数据唯一性。
3、对于有依赖关系的子表和父表联合查询,可以提高查询速度。
4、使用分组和排序子句进行数据查询时,可以显著减少查询中分组和排序的时间,减少CPU消耗。
缺点:
1、创建索引和维护索引要耗费时间
2、索引需要占磁盘空间
3、虽然索引大大提高了查询速度,同时也会降低更新表的速度。
3、常见索引概念
3.1、聚簇索引
聚簇索引是一种数据存储方式,即所有的用户数据都存储在了叶子结点上,也就是索引即数据,数据即索引。
特点:
- 1、使用记录主键值的大小进行记录和页的排序,其中
页内记录
按照主键大小顺序排成单向链表
存放用户数据的页
按照主键大小排成双向链表
目录项记录的页
按照主键大小排成双向链表
- 2、B+树的叶子结点存储完整的用户记录
拥有这两种特性的B+树称为聚簇索引。InnoDB引擎会自动的为我们创建聚簇索引,不需要显示创建。
优点:
- 数据访问更快
- 聚簇索引对于主键的排序查找和范围查找速度非常快
- 节省了大量io操作。
缺点:
- 插入速度严重依赖于插入顺序,一般定义一个自增的ID列为主键
- 更新主键的代价很高,一般定义主键为不可更新。
- 二级索引访问需要两次索引查找。
限制:
- 对于MySQL只有InnoDB数据引擎支持聚簇索引,而MyISAM不支持聚簇索引
- 由于数据物理存储排序方式只能有一种,所以每个MySQL的表只能有一个聚簇索引。一般是该表的主键。
- 如果没有定义主键,InnoDB会选择非空的唯一索引代替。如果没有这样的索引,InnoDB会隐式定义一个这样的主键
- InnoDB表尽量选择有序的顺序id,不建议用无序的id。
3.2、二级索引(辅助索引、非聚簇索引)
概念:回表
如果想要根据非主键列的值查找到完整的用户记录的话,仍然需要到聚簇索引中再查一遍,这个过程称为回表。
回表操作的意义:为什么不把完整用户数据放到二级索引的叶子结点
由于这种按照非主键列所建立的B+树需要一次回表操作才可以定位到完整的用户记录,所以这种B+树也被称为二级索引。如果把完整的用户数据放到叶子结点是可以不用回表,但是这就相当于每建立一颗 B+树都需要把所有的数据都拷贝一遍,过于浪费存储空间。
聚簇索引与非聚簇索引的区别:
1、聚簇索引的叶子结点存储的是数据记录,非聚簇索引的叶子结点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序。
2、一个表只能有一个聚簇索引,即只能有一种排列存储方式,但可以有多个非聚簇索引,也就是多个索引目录提供数据检索。
3、使用聚簇索引时数据的查询效率高,但是插入、删除、更新操作,效率比非聚簇索引低。
3.3、联合索引
以多个非主键列为排序规则建立的B+树称为联合索引,本质上也是一个二级索引。
4、MyISAM索引方案
4.1、MyISAM索引原理
MyISAM中将索引和数据分开存储,
- 1、将表中的数据按照记录的插入顺序单独存储在一个 文件中,称之为
数据文件
。这个文件无序,所以不能使用二分法进行查找。 - 2、使用MyISAN存储引擎的表会把索引信息存储到另一个
索引文件
中。以主键值+数据记录地址
的组合方式存储
4.2、MyISAM与InnoDB对比
- 在InnoDB中,根据主键值对进行一次查找就能找到对应记录。在MyISAM中,由于是二级索引,需要进行一次回表才能找到。
- InnoDB索引即数据,MyISAM索引文件与数据文件分离。
- InnoDB的所有非聚簇索引都引用主键作为data域,而MyISAM索引记录的是地址。
- MyISAM使用地址偏移量直接到文件中取数据,十分快速。InnoDB在获取主键后再去聚簇索引中找记录,速度比不上直接用地址访问。
- InnoDB要求表必须要有主键,MyISAM可以没有。