第5章 创建高性能的索引
本文为《高性能Mysql 第三版》第四章读书笔记,Mysql版本为5.5
索引基础
索引的重要性:找一本800面的书的某一段内容,没有目录也没有页码(页码也可类比是索引)
索引类型
B-Tree 索引 : 可用于全值匹配、最左前缀匹配、列前缀匹配、范围值匹配、精确匹配某一列并范围匹配另外一列、只访问索引的查询 ,原文截图:
哈希索引 : 只适用于精确匹配查询,不适用于范围查询
空间数据索引 : 可以有效地使用任意维度来组合查询
全文索引 : 做的事情类似于搜索引擎,而不是简单的 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不会出现短小的碎片行,会移动短小的行并重写到一个片段中
总结
选择索引以及利用索引查询时的三个原则:
单行访问是很慢的。最好读取的块中包含尽可能多需要的行,使用索引可以创建位置引用以提升效率
-
按顺序访问范围数据是很快的,原因如下:
顺序I/O不需要多次磁盘寻道,比随机I/O快
如果服务器能够按顺序读取数据,那么就不再需要额外的排序操作,并且GROUP BY查询也无须再做排序和将行按组进行聚合计算了
索引覆盖查询是很快的
现实使用中,很难做到每一个查询都有完美的索引,这时候需要根据需求有所取舍地创建合适的索引,而非根据惯例一刀切