SQL查询优化——索引设计

MYSQL Jenner 10397℃ 0评论

在上一个章节(SQL查询优化——数据结构设计)中,我们讨论了数据结构的重要性。这一章节,我们将继续讨论SQL查询优化中的索引设计。

良好的查询性能,是建立在上一章节中陈述的几个问题的基础上的,而在这几点中,数据结构以及索引设计是最为重要的两点。良好的索引,必然是基于良好的数据结构之上的。索引设计不能独立于数据结构。糟糕的数据结构不仅仅会影响到索引性能,上文已经列举了几点坏处,这里不再累赘。

什么样的索引才是好的索引?和数据结构一样,这也没有一个通用的标准,具体要看业务场景。如何建立好的索引,并不是一簇而就的过程。大概的过程应该是:

  • 根据业务读写场景,确定数据结构分布
  • 给出数据结构字典
  • 列出大部分主要的读写SQL,根据SQL设计索引
  • 索引合并、优化

1、根据业务读写场景,确定数据结构分布

笔者比较喜欢接近0冗余的数据结构设计,坚持这种观点有以下几个原因:

  • 接近0冗余的数据结构设计,将能给项目带来更高的可扩展性
  • 表的体积更小,索引更小
  • 数据结构分散规范,程序编写会更合理,处理往往更方便
  • 有了接近0冗余的数据,数据汇总更容易,数据清洗更容易

接近0冗余的数据结构设计,并不是一定的,在某些场景,我们需要对数据进行汇总、冗余等操作。把数据按照细粒度分层,对于数据库设计、项目扩展性、数据库性能,从长远来看都是一个不错的方案。如果接近0冗余的数据结构,造成读写瓶颈,我们可以借助汇总和冗余解决这样的问题。orcale提供的物化视图更适合做自动化的数据汇总,mysql本身并没有提供物化视图,但是在github上有一个flexviews的项目可以提供支持。它是从binlog中分析出数据的变化,并实时的反映到物化视图中,不会影响现有数据库的性能。但是该项目貌似很少得到应用,最少目前还没有了解到关于它的更多消息,确实有些遗憾,可能在稳定性健壮性方面还有一些问题。

2、给出数据结构字典

确定了数据结构分布之后,我们要做的就是给出大概的一个表结构。可以借助excel或者EA等工具做图标、画图。例如下面的这张图表

QQ截图20141016214730

这样我们就有了一个大概的表结构,当然SQL语句可以放到最后面再写。

3、列出大部分读写SQL、根据SQL设计索引

好的索引是能够满足业务查询的索引,没有绝对的好索引,只有能尽量满足查询的好索引。我们要考虑具体的业务查询场景,才可以根据查询场景设计出合理的SQL语句。不考虑查询场景而设计出来的索引,往往包含低效甚至没有用的索引,既不能帮助优化查询性能,也会影响表的性能。

我们可能不需要写出所有的SQL,但是至少我们应该知道,我们要从哪些维度(哪些列)去读取数据。然后根据这些列的离散度(可能需要你做下简单的运算),使用概率等因素列举出最适合做索引的几个字段,然后考虑是否需要组合索引,哪些列做组合做引更为合适,能够满足更多的查询场景。组合索引的顺序有时会影响到查询的性能,但大多数情况下,组合索引的顺序是由查询场景决定的。如果查询场景对组合所的顺序没有要求,则离散度更高的列在前面再一定程度上会提升索引的性能。

组合索引是提升查询性能的一大利器,但也是影响写入性能的一个重要因素。过多的组合索引不适合写操作过多的场景,这一条同样适用于单列索引。在很多场景中,我们的查询都会带有时间字段,以时间字段为最左键建立组合索引是更好的选择。组合索引不一定要满足所有字段的条件才会使用,只需要满足连续的左边的列,即可。组合索引的最左列,可以用来做单列索引,这个大多数据人都知道。但是笔者还是见过这样糟糕的设计,我更愿意相信可能是开发者太忙了,没有顾及到这些细节问题。

需要连表的字段,最好加上索引,这会大大提升连表查询的效率。具体在哪一张表上加索引,要具体分析到SQL语句的执行计划,你需要确认EXPLAIN的结果,哪个表是优先被扫描的,后被扫描的表往往会用到连表字段的索引。这样你只需要在第二张表的连表字段上加索引就可以了;第一张表上加,如果不是业务场景要求,往往这个索引是没有任何用处的。

表的读写属性,也是我们设计索引的一个考量因素。如果表写入频繁,我们索引应该优先适合UPDATE、DELETE后面的WHERE字句,甚至有时为了提高写入性能,我们会牺牲掉一些使用概率小的索引(有一些开源统计工具可以给你提供一些舒服的报表)。如果是读取频繁的,在写入没有瓶颈的情况下,我们可以建立更多的合适的索引。但是多,不意味着你要建立一些使用概率极小、重要度不低,甚至不会被用到的索引。

最后,在表结构建立之后,你可能需要测试下你的SQL是否能够按照你设计的索引来执行,如果不行,可能你的索引并不是最优的。

4、索引合并、优化

表设计完成后,并不意味着你的工作已经结束的。可能在最开始的时候,你的索引是最优的,但随着时间的流逝,你的数据量可能会翻几倍,一些存储引擎的索引基数可能也会有问题,可能会出现一些平时运行良好的查询语句,变得缓慢,甚至成为系统的瓶颈。这时我们需要重新根据表结构、查询场景、数据量、索引基数等多方面因素,对索引进行合并和优化。

如果你的表出现了写瓶颈,往往是因为数据量过多,索引体积增大,数据的写入需要维护过多的索引。可能你需要删掉一些索引,但这往往是很难操作的,因为最初设计的索引是为了满足业务查询,而这些查询的性能也尤为重要。如果你不能删掉一些索引,做索引合并是一个更好的选择。你可以牺牲掉一些单列索引,转换成组合索引,或者牺牲掉组合索引变成单列索引。如果索引合并在你的业务场景中依然做不到,那可能你需要做些数据汇总的工作,这些查询已经不适合在这样一张写入瓶颈的表上运行了。

如果你的表出现的是读瓶颈,原因会有很多。例如:

  • 数据量增多,部分列的离散程度变低
  • 大型连表操作
  • 出现全表扫描
  • 锁表
  • 索引基数异常

上面只是列举了几个常见的问题,还有很多的其他原因。针对这些问题,我们可以通过如下办法解决:

删除一些离散度低的列索引,转而关注离散度更高的查询列,可以解决数据量增多,离散度变低的问题。大型表的连表操作,如果索引设计的不合理,往往会成为性能杀手,但这不是绝对的(连表意味着你可以使用多个索引)。如果连表条件过于复杂,也会造成查询缓慢,例如某些列用了随机数赋值(这是一个比较极端的例子)。针对连表SQL,重新考虑索引设计,是解决这个问题的办法。如果行不通,那么把连表的操作迁移到程序中,在程序中做处理是更好的解决方案(不要什么事情都给数据库去做,数据库君很委屈好么)。你可以针对这样的连表操作,建立一套简单的可复用框架,在程序中完成数据拼接剔除的工作。

全表扫描是因为你的查询不适合任何一个SQL,数据库认为根据它所收集的索引基数,全表扫描是一个更高效的选择。造成这一结果的原因可能是你没有未这类查询建立索引,或者这个SQL写的有问题(后面章节会继续关于编写SQL的讨论)。建立新的索引,或者修改查询条件使之适用现有索引是一个好的解决方案。

索引基数异常的问题的并不是很常见,而且只有mysql的isam引擎会出现这样的问题。针对这样的表,你可能需要定期重新生成索引基数,这样不至于表运行了一段时间后由于收集到的索引基数和实际索引基数差别太大而造成查询效率低下(错误的索引基数导致mysql生成低效的查询计划)。笔者曾经遇到过isam引擎莫名其妙的所有索引基数都变成了NULL,可能是MYSQL的一个BUG,所有的查询都变成了全表扫描。后台通过几个命令解决了这个问题,不过最好写一些定时任务,定期重新生成索引基数,这样能使你的表一直处于一个比较良好的状态。

 

原创文章,转载请注明: 转载自始终不够

本文链接地址: SQL查询优化——索引设计

转载请注明:始终不够 » SQL查询优化——索引设计

喜欢 (1)