【MySQL】索引

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可以没有。