深入理解Mysql系列——索引详解

什么是全表扫描？

一旦我们运行这个查询，在查找名字为Jesus的雇员的过程中，究竟会发生什么？数据库不得不Employee表中的每一行并确定雇员的名字（Employee_Name）是否为 ‘Jesus’。由于我们想要得到每一个名字为Jesus的雇员信息，在查询到第一个符合条件的行后，不能停止查询，因为可能还有其他符合条件的行。所以，必须一行一行的查找直到最后一行-这就意味数据库不得不检查上千行数据才能找到所以名字为Jesus的雇员。这就是所谓的全表扫描。

什么是索引？

在不加索引的数据表中，一条看起来很简单的查询语句可能需要进行全表扫描，这样效率会很低，所以需要索引来缩小一张表中需要查询的记录/行的数目来加快搜索的速度。

一个索引是存储的表中一个特定列的值的数据结构。索引是在表的列上创建。索引是包含一个表中列的值，并且这些值存储到一个数据结构中。请记住：索引是一种数据结构。

索引的类型有哪些：

一、b-tree索引

什么是b树一文详解：什么是B树？ - 知乎

平衡二叉树、b树、b+树的区别平衡二叉树、B树、B+树、B*树 理解其中一种你就都明白了 - 知乎

1、b-tree索引：它使用b-tree数据结构（实际为B+Tree）来存储数据，节点中存放被索引的列值和下一个节点的指针，因为使用b-tree后数据是顺序存储的，所以可以不断的比较，找到所需要的数据，叶子节点的指针指向被索引的数据。通过这样的方式避免了全表扫描的查询方式，加快访问数据的速度。

B-tree是最常用的用于索引的数据结构，因为它时间复杂度低，查找、删除、插入操作都可以在对数时间内完成。另一个重要的原因是存储在B-Tree中的数据是有序的。

2、可使用此索引的查询方式有：全值匹配、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配某一列并范围匹配另外一列、只访问索引的查询

3、另外使用b-tree索引也有一些限制，这些限制都与索引的顺序有关：

• 必须要从索引的最左列开始查找，否则不能使用索引
• 不能跳过索引中的列
• 如果查询语句中有某个列的范围查询的语句，则其右边所有的列都无法使用索引优化查询

二、哈希索引

哈希索引基于哈希表实现，只有精准匹配索引所有列的查询才有效。存储引擎对所有的索引列计算出一个哈希码，将哈希码存储在索引中，同时哈希表中保存每个数据行的指针，这样，对于这样的索引查找操作的速度是非常快的。出现哈希碰撞的话，索引会以链表的形式存放多个记录指针到同一个哈希条目中。

存储引擎会对每一列所有的索引列计算出一个哈希值，将哈希值存储在索引中，在哈希表中存放指向每个数据行的指针。

查找速度快，哈希索引数据无法用于排序，不支持部分索引列匹配查找，只支持等值查询。

为什么使用索引，它的优点：

1、大大减少了服务器需要扫描的数据量（分叉查找）

2、可以帮助服务器避免排序和临时表（b-tree索引按照顺序存储数据）

3、可以将随机I/O变为顺序I/O（索引顺序存储）

顺序I/O和随机I/O的区别，最直接的原因在于寻址时间

顺序IO和随机IO_藏红的博客-CSDN博客_随机io 顺序io

高性能的索引策略

1、独立的列，索引列不能是表达式的一部分，也不能是函数的参数。

2、当某一列数据很长时，可选择更短长度的前缀索引来建立索引。可通过计算不重复的索引值和数据包的记录总数的比值来选择前缀的长度。目的是选择足够长的索引保证较高的选择性。

3、多列索引，索引表并不是越多越好，意思是不要建立很多的单列索引，可以适当合并一些单列索引。

4、选择合适的索引列顺序，一个经验法则是将选择性最高的列放到索引的最前列，这也适用于特定的一些需求下，因为经验法则是考虑全局基数和选择性，而不是某个具体查询，可能对于某一个查询这条法则不对，但基于全局考虑，整体是优化了的。

聚簇索引

聚簇索引不是一种单独的索引类型，而是一种数据的存储方式，在innoDB的聚簇索引实际上在同一个结构上存储的b-tree索引和数据行。叶子节点上存放的就是具体的数据行。

优点：

• 可以把相关数据保存到一起，避免同一个索引，而多次的去磁盘I/O。
• 数据访问更快

缺点：

• 聚簇索引提高了I/O密集型应用的性能，但当数据全部存放到内存中时，访问顺序也就不重要了
• 插入速度严重依赖于插入顺序。如果按照主键插入，则很快，但不是按照主键插入，还需要加载数据到内存后重新组织一下表。
• 更新聚簇索引的代价很高，因为会强制将每个被更新的行移动到新的位置
• 可能有“页分裂”的问题
• 可能导致全表扫描变慢
• 二级索引更大
• 二级索引访问需要两次索引查找，因为二级索引中保存的“行指针”实际不是指向物理位置的指针，而是行的主键值。所以如果通过二级指针去查找行，需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找对应的行。

InnoDB和MyISAM的数据分布对比（聚簇索引和非聚簇索引的区别）

聚簇索引与非聚簇索引（也叫二级索引）--最清楚的一篇讲解 - 腾讯云开发者社区-腾讯云

在MySQL中，创建一张表时会默认为主键创建聚簇索引，B+树将表中所有的数据组织起来，即数据就是索引主键所以在InnoDB里，主键索引也被称为聚簇索引，索引的叶子节点存的是整行数据。而除了聚簇索引以外的所有索引都称为二级索引，二级索引的叶子节点内容是主键的值。

MyISAM中主键索引和其他索引在结构上没有什么不同。而InnoDB使用了聚簇索引，可以说聚簇索引就是表，因为他的叶子节点上存放了数据行的所有数据，另外，InnoDB的二级索引和聚簇索引也有很大不同，二级索引中的叶子节点存放了主键值，这样当出现行移动时减少了二级索引的维护工作。

MySQL · 引擎特性 · 二级索引分析二级索引

MySql每个InnoDB表都有一个聚簇索引,InnoDB创建索引的具体规则如:

1.在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引;

2.如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引;

3.如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

主键索引也叫聚簇索引，非主键索引也是二级索引。

主键索引存储的是主键id和全部数据，二级索引储存的是索引值和主键值，当我们查询的字段不在索引储存的数据中，就会进行回表，即通过普通索引找到主键值，再通过主键值查询主键索引找到要查的数据，这就是回表操作。这种情况可以建立联合索引，也叫覆盖索引。

普通索引如果查询id值，也是覆盖索引，因为它里面就储存主键值的呢。

如果sql执行做了回表操作，那么这条sql就是慢sql了。

为什么不适用uuid作为聚簇索引？

我为什么不建议开发中使用UUID作为MySQL的主键 - SegmentFault 思否

覆盖索引

索引值就已经包括了所有需要的数据，不需要进行回表查询，称之为覆盖查询。

好处：

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，能极大减少数据访问量。
• 因为索引按照顺序存储，对于I/O密集型的范围查询会比从磁盘读取每一行数据的I/O要少的多
• 对于某些存储引擎，依赖于操作系统缓存数据，减少了系统调用，降低了开销
• 对于InnoDB的聚簇索引，如果二级索引能够覆盖查询，就能减少一次查询。

使用索引扫描来做排序

除了使用排序操作来生成有序的结果，还可使用按索引顺序扫描来生成。但这种也是要尽量使索引包含所需要的列的时候才高效，不然不停的进行随机I/O也很慢。并且只有当索引的列顺序和order by子句 的顺序完全一致，所有列的排序方向都一样时，才可使用索引扫描做排序