MySQL索引相关整理

矫情吗；* 2023-05-31 02:54 3阅读 0赞

## 4. mysql 索引 ##

**索引按数据结构分**  
可分为哈希表，有序数组，搜索树，跳表。

**按作用分类:**

(1) 主键索引:不解释,都知道。

(2) 普通索引:没有特殊限制,允许重复的值。

(3) 唯一索引:不允许有重复的值,速度比普通索引略快。

(4) 全文索引:用作全文搜索匹配,但基本用不上,只能索引英文单词,而且操作代价很大。

**按数据存储结构分类**  
(1) 聚簇索引

> 定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

主键索引是聚簇索引,数据的存储顺序是和主键的顺序相同的

(2) 非聚簇索引

> 定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

聚簇索引以外的索引都是非聚集索引,细分为普通索引、唯一索引、全文索引,它们也被称为二级索引。

### 4.1. MYSQL索引介绍 ###

(1) 哈希表,**哈希表这种结构适用于只有等值查询的场景**。

(2) 有序数组适用于有等值查询和范围查询的场景，但有序数组索引的更新代价很大，所以最好用于静态数据表。

(3) 搜索树的搜索效率稳定，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。

(4) 跳表可以理解为优化的哈希索引。

**如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。所以，有序数组索引只适用于静态存储引擎。**

innodb 使用了 B+ 树索引模型，而且是多叉树。虽然二叉树是索引效率最高的，但是索引需要写入磁盘，如果使用二叉树磁盘 io 会变得很频繁。在 innodb 索引中分为主键索引（聚簇索引）和非主键索引（二级索引）。主键索引保存了该行数据的全部信息，二级索引保存了该行数据的主键；**所以使用二级索引的时候会先查出主键值，然后回表查询出数据，而使用主键索引则不需要回表**。

对二级索引而言可使用覆盖索引来优化 sql，看下面两条 sql

select * from table where key=1;
    select id from table where key=1;

key 是一个二级索引，第一条 sql 是先查询出 id ，然后根据 id 回表查询出真正的数据。而第二条查询索引后直接返回数据不需要回表。第二条 sql 索引 key 覆盖了我们的查询需求，称作覆盖索引

### 4.2. 普通索引和唯一索引 ###

innoDB 是按数据页来读写数据的，当要读取一条数据的时候是先将本页数据全部读入内存，然后找到对应数据，而不是直接读取，每页数据的默认大小为 16KB。

当一个数据页需要更新的时候，如果内存中有该数据页就直接更新，如果没有该数据页则在不影响数据一致性的前提下将；更新操作先缓存到 change buffer 中，在下次查询需要访问这个数据页的时候再写入更新操作除了查询会将 change buffer 写入磁盘，后台线程线程也会定期将 change buffer 写入到磁盘中。对于唯一索引来说所有的更新操作都要先判断这个操作是否会违反唯一性约束，因此唯一索引的更新无法使用 change buffer 而普通索引可以，唯一索引更新比普通索引更新多一个唯一性校验的过程。

### 4.3. 联合索引 ###

两个或更多个列上的索引被称作联合索引（复合索引）。联合索引可减少索引开销，以联合索引 (a,b,c) 为例，建立这样的索引相当于建立了索引 a、ab、abc 三个索引—— Mysql 从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分，而且当最左侧字段是常量引用时，索引就十分有效，这就是最左前缀原则。由最左前缀原则可知，组合索引是有顺序的，那么哪个索引放在前面就比较有讲究了。对于组合索引还有一个知识点——索引下推，假设有组合索引（a，b，c）有如下 sql:

selet * from table where a=xxx and b=xxx

这个 sql 会进行两次筛选第一次查出 a=xxx 数据 再从 a=xxx 中查出 b=xxx 的数据。使用索引下推和不使用索引下推的区别在于不使用索引下推会先查出 a=xxx 数据的主键然后根据查询出的主键回表查询出全行数据，再在全行数据上查出 b=xxx 的数据；而索引下推的执行过程是先查出 a=xxx 数据的主键，然后在这些主键上二次查询 b=xxx 的主键，然后回表。

索引下推的特点：

*  innodb 引擎的表，索引下推只能用于二级索引
 *  索引下推一般可用于所查询字段不全是联合索引的字段，查询条件为多条件查询且查询条件子句字段全是联合索引。

### 4.4. 优化器与索引 ###

在索引建立之后，一条语句可能会命中多个索引，这时，就会交由优化器来选择合适的索引。优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。那么优化器是怎么去确定索引的呢？优化器会优先选择扫描行数最少的索引，同时还会结合是否使用临时表、是否排序等因素进行综合判断。MySQL 在开始执行 sql 之前，并不知道满足这个条件的记录有多少条，而只能根据 mysql 的统计信息来估计，而统计信息是通过数据采样得出来的。

**优化器的逻辑**  
在第一篇文章中，我们就提到过，选择索引是优化器的工作。

而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。

当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

我们这个简单的查询语句并没有涉及到临时表和排序，所以MySQL选错索引肯定是在判断扫描行数的时候出问题了。

### 4.5. 其他索引知识点 ###

有时候需要索引很长的字符列，这会让索引变得很大很慢还占内存。通常可以以开始的部分字符作为索引，这就是**前缀索引**。这样可以大大节约索引空间，从而提高索引效率，但这样也会降低索引的选择性。

1.  直接创建完整索引，这样可能比较占用空间；
2.  创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
3.  倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
4.  创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

**脏页对数据库的影响：**  
当内存数据页和磁盘的数据不一致的时候我们称这个内存页为脏页，内存数据写入磁盘后数据一致，称为干净页。当要读入数据而数据库没有内存的时候，这个时候需要淘汰内存中的数据页——干净页可以直接淘汰掉，而脏页需要先刷入磁盘再淘汰。如果一个查询要淘汰的脏页太多会导致查询的时间变长。为了减少脏页对数据库性能影响，innodb 会控制脏页的比例和脏页刷新时机。

**平时执行很快的更新操作，其实就是在写内存和日志，而MySQL偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。**

### 4.6. 索引优化 ###

(1) 索引不是越多越好,索引是需要维护成本的

(2) 在连接字段上应该建立索引

(3) 尽量选择区分度高的列作为索引,区分度count(distinct col)/count(\*)表示字段不重复的比例,比例越大扫描的记录数越少，状态值、性别字段等区分度低的字段不适合建索引

(4) 几个字段经常同时以AND方式出现在Where子句中,可以建立复合索引,否则考虑单字段索引

(5) 把计算放到业务层而不是数据库层

(6) 如果有 order by、group by 的场景，请注意利用索引的有序性。

*  order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file\_sort 的情况，影响查询性能。

例如对于语句 where a=? and b=? order by c，可以建立联合索引(a,b,c)。

order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file\_sort(外部排序) 的情况，影响查询性能。

*  例如对于语句 where a=? and b=? order by c，可以建立联合索引(a,b,c)。
 *  如果索引中有范围查找，那么索引有序性无法利用，如 WHERE a>10 ORDER BY b;索引(a,b)无法排序。