【高性能Mysql 】读书笔记（三）

墨蓝 2023-07-10 08:43 21阅读 0赞

# 第5章 创建高性能的索引 #

--------------------

> 本文为《高性能Mysql 第三版》第四章读书笔记，Mysql版本为5.5

## 索引基础 ##

索引的重要性：找一本800面的书的某一段内容，没有目录也没有页码（页码也可类比是索引）

### 索引类型 ###

*  B-Tree 索引 ： 可用于全值匹配、最左前缀匹配、列前缀匹配、范围值匹配、精确匹配某一列并范围匹配另外一列、只访问索引的查询 ，原文截图：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgzNDQ2NA_size_16_color_FFFFFF_t_70]
 *  哈希索引 ： 只适用于精确匹配查询，不适用于范围查询
 *  空间数据索引 ： 可以有效地使用任意维度来组合查询
 *  全文索引 ： 做的事情类似于搜索引擎，而不是简单的 where 条件匹配

### 对于Mysql索引使用的歧义 ###

对于很早的版本，一直强调：最左前缀匹配，索引使用顺序，不可跳过索引中的列等等，但高性能Mysql一书之后的版本中对于

mysql优化器进行了很大程度的优化，使得开发者在使用时候可以不用顾虑太多

例如：

CREATE TABLE `mytest`.`student`  (
      `age` int(11) NULL DEFAULT NULL COMMENT 'age',
      `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'name',
      `year` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'year',
      INDEX `demo`(`age`, `name`, `year`) USING BTREE COMMENT 'demo'
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

故意使用不按索引顺序查询：

mysql> explain extended  SELECT * FROM `student` where `name` = 1 and `age` = 1;
    实际: key:demo 使用到了索引

`如何查看Mysql优化之后的SQL`：

# 仅在服务器环境下
    explain extended  SELECT * FROM `student` where `name` = 1 and `age` = 1;
    
    # 再执行
    show warnings;
    
    # 结果如下：
    /* select#1 */ select `mytest`.`student`.`age` AS `age`,`mytest`.`student`.`name` AS `name`,`mytest`.`student`.`year` AS `year` from `mytest`.`student` where ((`mytest`.`student`.`age` = 1) and (`mytest`.`student`.`name` = 1))

可以发现真正执行的SQL是 age在前，name在后

对于仅使用year字段的查询结果呢？依然显示 -> key:demo 使用到了索引

总结：

*  写代码时对于索引顺序不必有`太多顾虑`
 *  优化SQL时候不仅仅只看 key参数是否使用索引，还需要注意type，ref等等
 *  Mysql 优化器`并不是一定改变SQL的参数查询顺序`，而是以它的判定方式选择它认为的最有效的查询

## 索引的优点 ##

正常的索引数据结构可以有其独特的优点，比如哈希索引不支持顺序查询，而B-Tree，或者 B+Tree则可以，总结B-Tree系列索引优

点如下：

*  大大减少了服务器需要扫描的数据量
 *  可以帮助服务器避免排序和临时表
 *  可以将随机 I/O 变为顺序 I/O

## 高性能的索引策略 ##

### 独立的列 ###

`索引在使用中不可嵌入表达式或者方法`错误示范： select \* from where eid + 1 < 10;

### 前缀索引和索引的选择性 ###

如果索引很长的字符串列，会导致索引变得很大并且很慢。一个处理策略是模拟哈希索引，另一个办法是索引开始的一部分字符串的值，即前缀索引。

**索引的选择性**指不重复的索引值和数据表中记录总数(\#T)的比值。取值在为(1/ \#T~1\]。选择性越高则查询效率越高。唯一索引的选择性是1，是最好的索引选择性，性能也是最好的。

前缀索引的**优点**是可以节约索引空间，提高索引效率。**缺点**是降低了索引的选择性，同时也无法使用前缀索引做ORDER BY 或 GROUP BY 操作，无法使用前缀索引做覆盖扫描。

使用前缀索引时，**确定前缀长度的依据**是：计算前缀索引的选择性，使前缀索引选择性接近完整列的选择性

### 多列索引 ###

错误做法：为where字段的每一个列创建独立的索引，或者按照错误的顺序创建多列索引

在explain中，如果发现索引合并，实际上说明了表上的索引建的不够好：

*  当需要进行AND操作时，其实说明了需要建立一个包含所有相关列的多列索引；
 *  当需要进行OR操作时，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上；
 *  优化器不会把这些计算到”查询成本“中，这会使得查询成本被低估，可能还不如全表扫描然后union。

### 选择合适的索引顺序 ###

不考虑排序和分组时：将选择性最高的列放在前面通常是很好的（选择性即有效区分数据）

但，性能不只是依赖于所有索引列的选择性，也和查询条件的具体值有关，也就是和值的分布有关，这就意味着可能需要

根据那些运行频率最高的查询来调整索引列的顺序

例如：一张订单表，需要查询的字段有两个，一个是地区ID，一个是用户ID，简单思考就一定能发现用户ID的区分度应该是比地区

ID要高的多（相同用户ID的数据要远远少于相同地区ID），因此建立索引应该是（用户ID，地区ID）

### **聚簇索引** ###

\*\*聚簇索引：\*\*将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

\*\*非聚簇索引：\*\*将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

**在innodb中**，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。**辅助**

**索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找**

**聚簇索引具有唯一性**，由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引， **聚簇索引默认是主键**，

如果表中没有定义主键，InnoDB 会选择一个**唯一且非空的索引**代替。如果没有这样的索引，InnoDB 会**隐式定义一个主键（类似**

\*\*oracle中的RowId）\*\*来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添

加我们想要的聚簇索引，最后恢复设置主键即可

聚簇的数据的优点：

*  可以把相关的数据保存在一起。如电子邮箱中，根据用户ID来聚簇数据，这样只要读取少数的数据页就可以获取某个用户的全部邮件。如果没有使用聚簇索引，可能每一封邮件导致一次磁盘I/O；
 *  访问数据更快。聚簇索引将索引和数据报错在同一个B-Tree中，因此获取数据通常比非聚簇索引更快；
 *  使用覆盖索引扫描的查询可以直接使用页节点的主键值

聚簇索引的缺点：

*  聚簇索引能够提高I/O的密集程度，但如果所有的数据全都放在内存中，那么访问顺序就没那么重要了，聚簇索引也就没什么优势了
 *  插入速度严重依赖于插入顺序。按照主键顺序插入是最快的。如果不是先找主键顺序加载数据，那么加载完成后最好用OPTIMIZE TABLE命令重新组织一下表
 *  更新聚簇索引列的代价很高
 *  基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行时，可能面临"页分裂"的问题
 *  举措索引可能导致全表扫描变慢，尤其是行比较稀疏，或者页分裂导致数据存储不连续的时候
 *  二级索引（非聚簇索引）可能比想象的大，因为二级索引的叶子节点包含了引用行的主键列
 *  二级索引访问需要两次索引查找，而不是一次（第一次获得主键，第二次根据主键值去聚簇索引中查找对应的行）

#### 在InnoDB表中按主键顺序插入行 ####

如果正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义一个代理健作为主键。最简单的是使用AUTO INCREMENT自增

列，这样可以保证数据行是顺序写入的，对于主键做关联操作也是最好的

最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用，例如UUID

用UUID作为主键的坏处：

*  写入的目标也可能已经疏导磁盘并从缓存中移除，或者还没有被加载到缓存中。InnoDB不得不先找到并且从磁盘读取目标页到内存，这导致了大量的随机I/O
 *  因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，会导致移动大量数据，一次插入最少需要修改三个页而不是一个页
 *  由于频繁的页分裂，页会变的稀疏并被不规则填充，所以最终数据会有碎片
 *  UUID较长，不利于作为索引

`字符类型作为主键的通用替代方案`：[美团分布式ID生成项目][ID]， 雪花ID

### 覆盖索引 ###

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为：覆盖索引

覆盖索引的好处：

*  索引条目远小于数据行大小，能够极大地提高性能，所以如果只需要读取索引，那么MySQL就会极大地减少数据访问量
 *  因为索引是按照值顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘中读取每一行数据的I/O要少的多

如果不覆盖索引，则会产生`回表查询`， 先定位主键值，再定位行记录，它的性能较扫一遍索引树更低

### 使用索引扫描来做排序 ###

MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描

如果EXPLAIN出来的type列的值为index，则说明使用了索引扫描排序

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引无法覆盖所有的列，那就不得不扫描

一条索引记录就回表查询一次对应的行，这基本上属于随机I/O，因此按索引顺序读取数据的速度通常比顺序地全表扫描要慢

### 压缩（前缀压缩）索引 ###

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某种情况下可以极大地提高性能。默认只压缩

字符串，但通过参数设置也可以对整数压缩 ， MyISAM存储引擎不过多深究

### **冗余和重复索引** ###

**重复索引**：

*  MySQL允许在相同列上创建多个索引，但这样需要单独维护重复的索引，并且优化查询的时候也需要逐个进行考虑，会影响性能，应该避免这么做

**冗余索引**

*  如果已经创建了索引（A, B），在创建索引（A），那么就是冗余索引，因为它只是前一个索引的前缀
 *  冗余索引通常发生在表添加新索引的时候。如增加一个新的索引(A, B)，而没有扩展已有索引(A)，导致(A)成为冗余索引。或者将索引扩展为(A, 主键ID)，对InnoDB来说，主键已经包含在二级索引中了，因此也是冗余的

### 索引和锁 ###

索引可以让查询锁定更少的行，如果你的查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好

处：

*  虽然InnoDB的行锁的效率很高，内存使用也很少，但是锁定行的时候依然会带来额外开销
 *  锁定需要的行会增加所争用并减少并发性

InnoDB只有在访问行的时候才会对其加锁。而索引能够减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引

擎能够过滤掉不需要的行时才有效，如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层之后，MySQL服务

器才能应用Where子句，这时已经无法避免锁定行了：InnoDB已经锁住了这些行，到适当的时候才释放。

Explain的Extra列显示“Using Where”，说明MySQL服务器将存储引擎返回行之后再应用where过滤条件。此时where子句以前的数据全都被加锁

InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排它锁（写锁）

**InnoDB的行锁是建立在索引的基础之上的**，行锁锁的是索引，不是数据，所以提高并发写的能力要在查询字段添加索引

## 维护索引和表 ##

使用正确的类型创建了表并加上了合适的索引后，还需要维护表和索引来确保它们正常工作，目的如下：

*  找到并修复损坏的表
 *  维护准确的索引统计信息
 *  减少碎片

### 找到并修复损坏的表 ###

可以通过CHECK TABLE检查是否发生了表错误

可以用REPAIR TABLE或者一个不作任何操作的ALTER操作来修复表

### **更新索引统计信息** ###

*  records\_in\_range()，通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录
 *  info()，返回各种类型的数据，包括索引的基数（每个键值有多少条记录）

### 减少索引和数据的碎片 ###

B-Tree索引可能导致碎片化，会导致查询效率降低。有三类数据碎片

*  行碎片：数据行被存储在多个片段中
 *  行间碎片：逻辑上顺序的页，或者行在磁盘上不是顺序存储的
 *  剩余空间碎片化：数据也中有大量的空余空间

对于MyISAM表，三类碎片都可能发生，InnoDB不会出现短小的碎片行，会移动短小的行并重写到一个片段中

## 总结 ##

选择索引以及利用索引查询时的三个原则：

*  单行访问是很慢的。最好读取的块中包含尽可能多需要的行，使用索引可以创建位置引用以提升效率
 *  按顺序访问范围数据是很快的，原因如下：
    
    1.  顺序I/O不需要多次磁盘寻道，比随机I/O快
    2.  如果服务器能够按顺序读取数据，那么就不再需要额外的排序操作，并且GROUP BY查询也无须再做排序和将行按组进行聚合计算了
 *  索引覆盖查询是很快的

现实使用中，很难做到每一个查询都有完美的索引，这时候需要根据需求有所取舍地创建合适的索引，而非根据惯例一刀切

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgzNDQ2NA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200301170253823.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgzNDQ2NA==,size_16,color_FFFFFF,t_70
[ID]: https://tech.meituan.com/2019/03/07/open-source-project-leaf.html