高性能索引
高性能的索引策略
在介绍怎么建立高性能索引之前,首先介绍什么是高性能索引?
1.1三星系统
评价索引是否适合与某个查询,可以用三星系统来评价。
如果索引只是把相关记录放在了一起,赋一星
如果索引中的顺序和查找中的排序顺序一致,赋两星
如果索引的列包含了查询中的需要的全部列,赋三星
1.2 建立高性能索引
正确的创建和使用索引,是实现高性能查询的基础,前面的文章介绍了不同索引的实现及其优缺点,下面介绍如何发挥这些索引的优势。
1.2.1 独立的列
通常我们会遇到使用索引不当,导致索引失效的情况。如果MySQL中的索引列不是独立的,那么就无法使用索引。这里的独立索引列指的是它不能是表达式的一部分,也不可以是函数的参数。
例如,如下语句,就无法使用到actor_id这一列的索引。
select actor_id from person where actor_id + 1 = 5;
这里我们可以很清晰的看到,这里的查询条件就是actor_id等于5,但是MySQL程序无法对表达式进行解析。这完全是用户行为,所以我们应该养成简化查询where条件的习惯。把索引列单独放在比较符号的一侧。
select ****** form **** where to_days(current_date) - to_days(data_col) <= 10;
上面也是一个比较容易犯的错误,在where中使用了函数和表达式。
1.2.1 前缀索引和索引选择性
有时候索引列里面的字段是一个很长的字符串,那遍历这个索引就需要花费大量的时间。前面介绍过,可以在B-Tree索引的基础上,添加哈希索引,但是还有更高效的做法。
我们可以只索引该字段的前面部分字符,这样可以大大节省索引空间。但是,迎面而来的问题就是,这样做会减少索引的选择性,通俗来说,就是有更多的索引重复了,每次查找结点页能够过滤掉的数据也就更少了。最终要顺序遍历的条数就会变多。这样性能同样不高。
但是,就具体业务而言,如果只看某个列的前面部分字符,选择性同样可以保障,那么是可以保证查找效率的。
其实问题的关键在于,选取合适的长度,使得索引不至于过长,同时也不能选太短,要保证索引的选择性不会下降太多。从中找到一个平衡,来进行高性能索引。
这里通常需要,先找出在数据表中最常见的那些数据列,然后和它们的前缀列表进行比较,找出一个合适的长度。
前缀索引是一种使得索引变得更小,更快的有效办法,但是使用前缀索引也有一定的缺陷,MySQL无法通过前缀索引来进行order by排序,也无法通过前缀索引进行覆盖扫描
1.2.3 多列索引
很多不了解索引的人会有一个常见的错误,就是给以为给每一列都加索引,就可以完事大吉了,有时候会加错多列索引顺序。
首先来说,给每一列都建立索引,这个方法其实是错误的。因为这样一来,我们所有的查询语句都最多只能是一星查询,它的性能跟真正的高性能索引比会相差好几个数量级。
在多个列上建立索引在大部分情况下并不能提高MySQL的效率,在MySQL5.0版本之后,会有一种“索引合并”的算法,能够提高单列索引的效率,但是在这之前,MySQL只会使用到单列索引,这种的效率并不高。
比如下面这个查询语句:
select film_id,actor_id from person where actor_id = 1 or film_id = 2;
虽然在actor_id和film_id列上都有索引,但是无论使用哪一个索引都不是非常的有效。原因显而易见,因为当搜索完一个索引只会,比如actor_id=1,搜索出来结果之后,还是得全表扫描film_id=2,并且,还要建立临时表,将结果进行缓存,排序,最终将两次扫描的结果进行合并。这之间的花销是很大的。
在MySQL5.0之后,查询可以同时使用这两列的索引,并将最终结果进行合并。
索引的合并策略是一种优化的结果,但同时也是在提醒我们,表上的索引列建立的并不理想。
当服务器出现对多个索引进行相交的操作的时候,通常意味着我们需要建立一个包含多列的索引,而不是多个单独的索引列
当服务器需要对多个索引列做联合操作的时候,通常需要花费大量的时间在对索引结果的缓存,排序和合并上,特别是当有的索引选择度不高,返回大量数据的时候
更重要的是,优化器并不会把这些花费算到“查询成本”中,导致在进行查询优化的时候,走这些单列索引的成本被低估,导致最终的结果甚至不如走全表扫描。不仅浪费大量的cpu,还严重的影响并发
1.2.4 选择合适的索引顺序
建立多行索引,不免遇到的问题就是,如何决定索引列的顺序呢?其实正确的索引顺序是要依赖于查询的。也就是根据我们如何查询来决定索引列的顺序。当然,需要同时考虑好排序和分组的要求。
在一个B-Tree索引中,索引列的顺序意味着建立索引的时候,会按照索引从左往右进行排序,这样才可以根据索引进行升序或者降序排序,以满足精确匹配索引顺序的order by,group by等查询需求。
多列索引的顺序至关重要,在上文提到的三星系统中,一个多列索引的顺序决定了一个索引是否能够成为正真的三星索引。
对于如何选择索引列的顺序,有一个经验法则就是:将选择性最高的列放在前面,这个建议在一些场景下是适用的,但是,我们在建立索引的时候,还是应该根据自己的业务情况,进行全面的判断。(这里需要说明的是,没有一种五湖四海皆通用的索引列顺序排放准则,只能通过业务进行实际判断,不可统一照搬)
当不需要考虑排序和分组的时候,将选择性最高的列放在前面是比较合适的,因为此时的索引只是为了能够更快速的找出符合条件的数据行,选择性越高的字段,可以筛出更多的数据,让查找更快。当然,查询的性能也不仅仅是依赖于数据字段的选择性,还依赖于该字段的值的分布情况。具体情况还是需要具体分析,通过实际验证,得到最优的顺序。
1.3 聚簇索引
聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。具体的细节依赖于它的实现方式。在InnoDB中,聚簇索引其实就是将索引和数据行存储在一起。
当表中有聚簇索引的时候,它的数据行是存放在B-Tree的叶子页中的,聚簇其实值的是数据行和相邻的键值紧密的存储在一起。因为无法将数据行存储在两个地方,所以一个表中只有一个聚簇索引。
并不是所有的存储引擎都支持聚簇索引。InnoDB是支持的。
在聚簇索引中,节点页中存放的是和索引列,而叶子页中存放了所有的数据行
在InnoDB中,会把主键作为聚簇索引。当表中没有主键的时候,会选择一个唯一的非空索引作为替代,如果也没有符合的索引,会使用隐藏的自增id作为索引。
聚簇索引可能会对性能有帮助,也可能会拖慢性能。
1.3.1 聚簇索引的优点:
1.聚簇索引可以把相关的数据保存到一起,这样就能够通过读取少量的数据页,拿到全部需要的数据。
2.数据访问更快,因为数据行就在叶子页中,进行聚簇索引的时候不需要再到磁盘中拿数据。
3.使用覆盖索引的时候,可以直接使用叶子节点中的主键。
1.3.2 聚簇索引的缺点
1.聚簇索引极大限度的提高了IO密集型的性能,但是,如果已经把数据全部放入内存中了,那聚簇索引也就没有优势了。
2.插入的速度严重依赖于插入的顺序,按照递增的顺序插入最快。
3.更新索引的代价很高,InnoDB会强制要求每一个更新的数据行移动到更新之后的位置。
4.基于聚簇索引的表中插入新的数据或者数据更新的时候,可能会导致页分裂的问题,当要插入的这一行数据需要插入到一个以满页中时,会先将这个满页进行分裂,分成两个只装了一半数据的页,然后进行插入,这样会降低空间利用率。
5.二级索引需要访问两次索引,进行回表的操作。
其实以上的2,3,4问题,在我看来不仅是聚簇索引的问题,所有依赖于B-Tree的索引都有这些问题。
有什么办法可以避免两次索引吗?可以使用之前文章介绍的自适应哈希索引来解决。
1.4 InnoDB和MyISAM数据分布对比
聚簇索引和非聚簇索引之间有区别,对应的主键索引和二级索引也会有区别。下面对于同一张表,使用InnoDB和MyISAM进行存储,数据分布的对比。
create table test(
col1 int not null primary key,
col2 int not null,
key(col2)
);
1.4.1 MyISAM数据分布
首先介绍MyISAM,它的做法很简单,就是直接按照数据插入的顺序进行数据存储,将数据保存到磁盘上。当每行数据都是定长的时候,甚至可以通过行号直接跳过某些数据的搜索。这种方式下很容易建立索引。
MyISAM的索引结构是按照索引列进行B-Tree树的建立,在叶子节点上,存放行号(当数据行的长度固定时,也可以理解为磁盘数据行的地址),并且在MyISAM上,不论是主键索引,还是二级索引,它的结构都没有什么区别,只不过主键索引上的索引列非空且唯一罢了。
1.4.2 InnoDB数据分布
在InnoDB中,因为支持聚簇索引,所以会大不相同。
如果能够观察InnoDB索引和MyISAM索引的话,你会发现,InnoDB的结构和MyISAM的结构类似,但是,你会注意到在InnoDB中包含了整张表,甚至你可以理解为在InnoDB中,索引就是数据表。它不需要像MyISAM那样单独去存储数据行。在聚簇索引的叶子节点中,包含了数据行的全部信息。
第二点与MyISAM不同的是,在InnoDB的二级索引中,数据存放的不是行号,不是数据行的磁盘地址,也不是完整数据行,而是主键值,当我们使用到二级索引的时候,会先查找到主键,(当不发生覆盖索引的时候,会通过主键,进行聚簇索引的查找,找到最终的数据行),也就是需要两次查索引的操作。
这里可以注意一下,二级索引存储的不是完整的数据行,可以理解,因为数据行只有一份,如果存储多份的话,修改麻烦,也浪费空间。但是为什么不存储主键指针,而存储主键值呢?如果主键值很大的话,岂不是会浪费很大的空间吗?
是的,存储主键值是会让InnoDB浪费一定的空间,但是,这也同样减少了,在新数据行进行插入的时候,带来的有可能的数据行的移动,或者页分裂带来的数据行的位置变化导致的问题,如果存指针的话,数据行移动之后,所有二级索引都要发生改变,改变指针指向,这是一件很浪费时间的事情。当然,如果数据行删除,或者主键更新了(很少发生),那就一定要对二级索引进行更新,这也是必要举措。
1.5 InnoDB中按序插入的重要性
当我们在使用InnoDB存储引擎的时候,选取索引时,如果没有什么需要特殊聚集的数据,那么可以使用自增id作为主键索引,这样能够保证插入的顺序时递增有序的。
最好要避免不连续并且值分布范围很大的字段作为主键索引,例如,使用uuid作为主键索引是一个很不明智的做法,它使得对于主键索引的插入完全随机,在最坏的情况下,没有任何的聚集性。
原因也很简单,使用uuid这样的随机字段,会导致很多的页分裂和碎片,造成极大的空间浪费,并且插入的效率不高。而使用自增id作为主键,会使得插入时有序的,只有达到页分裂的阈值才会进行页分裂,节省了空间。
1.5.1 随机主键的缺点:
1.在写入目标页的时候,因为插入的索引是随机的,所以不能确定要插入的目标页已经在内存中,很可能还没被拿入内存,后者很久没有被使用,已经被替换出去了,所以需要从磁盘中把数据页拿出来。
2.因为写入是乱序的,那么就会有大量的页分裂的操作,降低了空间利用率,容易产生碎片。并且,页分裂就要有数据行的移动,这部分的时间消耗要远比简单插入一行数据的时间消耗多。
1.5.2 顺序主键一定好吗?
在高并发的环境下,如果按照主键顺序插入,那么当前主键的最大值将会是明显的争用资源,所有的插入都要在这里完成,这里的间隙锁争用会很频繁。
解决方法-。。。。。
1.6 覆盖索引
通常我们都会根据SQL查询语句中的where条件去建立索引,来满足查询需求,不过,这只是索引优化的一部分,真正优秀的索引,应该考虑到整个查询,而不单单是where条件部分。
索引确实是一种查找数据的高效方式,但是MySQL同样允许使用索引来获取要查找的目标,这样就不需要再回表去查找数据行了。如果索引的叶子节点中已经包含了要查询的数据,那么为什么还要进行回表操作呢?如果一个索引包含了(或者说覆盖了)我们要查询的结果,那么我们就称之为“覆盖索引”。
覆盖索引能够极大的提高性能,下面总结如果查询只走索引,而不需要回表,会节省多少效率。
1.6.1 覆盖索引的效率提升
1.首先索引条目一般来说是比数据行的字段要少的,所以,如果只扫描索引的话,就会少扫描很多的数据,减少数据访问量,同时,因为索引比数据更小,所以更容易全部放入内存,速度更快。
2.因为索引是按照列值的顺序存储的,所以对于范围查询,就可以拿取更少的数据页,也能够减少数据访问量。
3.对比MyISAM,在缓存中只缓存地址,使用覆盖索引也不需要通过系统调用,去磁盘中读取数据,速度更快。
4.覆盖索引能够避免回表操作,直接减少了一次索引,速度更快。
并不是所有的索引都可以使用到覆盖索引的,必须要存储索引的索引列值才有可能,所以像哈希索引,全文索引是没有覆盖索引的。
当发生了覆盖索引时,在expain中的extral字段中会显示“using index”