【高性能Mysql 】读书笔记(三)

第5章 创建高性能的索引


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

索引基础

索引的重要性:找一本800面的书的某一段内容,没有目录也没有页码(页码也可类比是索引)

索引类型

  • B-Tree 索引 : 可用于全值匹配、最左前缀匹配、列前缀匹配、范围值匹配、精确匹配某一列并范围匹配另外一列、只访问索引的查询 ,原文截图:

    【高性能Mysql 】读书笔记(三)

  • 哈希索引 : 只适用于精确匹配查询,不适用于范围查询

  • 空间数据索引 : 可以有效地使用任意维度来组合查询

  • 全文索引 : 做的事情类似于搜索引擎,而不是简单的 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

覆盖索引

如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为:覆盖索引

覆盖索引的好处:

  • 索引条目远小于数据行大小,能够极大地提高性能,所以如果只需要读取索引,那么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查询也无须再做排序和将行按组进行聚合计算了

  • 索引覆盖查询是很快的

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

上一篇:python flask (一)


下一篇:《数据结构与算法分析》学习笔记(三)——链表ADT