BI系统-从成本的角度看优化SQL_bi系统优化

一、成本意识

      为了方便示意，本文以MySQL数据库作为演示数据库，并准备了下面一个简单表模型关系。

表模型：

                Article学生发表的文章表，数据量最大；

                Student学生信息表，数据量大；

                Teacher 教师信息表，数据量小；

                Student_Teacher学生、教师关系表，数据量大；

                Category文章分类表，数据量适中；

        基于以上表模型，先从一个简单的例子入手。我们开发一个按Article表进行分页展示，并且每页取10条记录。最初的SQL逻辑如下：

        sql : select id,title,content from article limit 10

        但当如果分页数很大，就会有明显的性能问题，因为content内容很大，如过数据页数很多，当获取比较靠后的页数时，导致扫描的数据范围过多，这时sql的性能就会很差。优化也比较简单，也是网上就能找到比较流行的改法，具体如下：

        sql : select id, title, content form article inner join (select id from  article limit 10000000, 10) as tmp on  using(id);

        这里通过二次查询标题、内容会极大的提高查询效率，并没有额外的增加数据构建成本，仅通过sql变形即可提高效率。在对应的场景中也很有效，但很可惜在实际业务场景中，分页查询并不会就这么简单，还需要按标题title、内容content、分类category、教师teacher等条件进行筛选、分组、统计总数，再进一步或许还有按日期进行同环比报表等。

        面对复杂的业务需求，不同的开发人员在产品、测试的鞭策下。都可以编写出结果符合业务期望数据的sql，但sql结构却不一定相同。此时sql执行时间就成为了一个最重要的衡量指标（并不是唯一指标）。这时如何降低运行时间，提高查询效率就成了一件非常有意思的事。本文会分析一些耗时案例，并从成本角度去分析并优化这些查询sql。

二、查询基本概念

        在具体的案例之前，我们先回顾下面这些基本概念：

2.1 基数、选择性 

        基数：一个列中唯一键的个数称为基数，如1千万的表学生表中，性别只有男、女则性别基数为2，文章编号是唯一自增主键则基数为1千万。

        选择性：基数与总行数的比值,如性别的选择性为2/1000W * 100% = 0.00002%, 文章编号为1000W/1000W * 100% = 100%。        

2.2 物理读、逻辑读

         物理读：简单理解为获取所需数据从磁盘上扫描的数据块。

         逻辑读：简单理解为获取所需数据从缓存中扫取的数据块。

        注：所有sql性能优化手段本质上都是为了降低物理读和逻辑读。

2.3 条件：驱动条件、筛选条件

        驱动查询条件：  带有查询条件的SQL语句，通常会有多个查询条件存在，其中一个或者几个条件会作为数据检索的发起者，这些条件称为“驱动查询条件”；

        筛选查询条件： 不作为“驱动查询条件”的其它筛选条件，在整个查询数据的过程中，只会起到过滤由驱动查询条件所决定的数据范围内数据的作用，这些查询条件就称为“过滤查询条件”

        我们把上面的查询语句增加两个查询条件，分别独立建立索引，并取第一页前10条记录。来讲下这两个概念，查询条件分别如下：

        1. 按文章编号取小于1000，因为文章编号是唯一索引，所以选择性 100%；

        2.文章分类Id是1，因为文章分类有固定数量，并且远小于文章总数，这里假设选择性是0.003%。

        当我们以条件1”按文章编号取小于1000“作为驱动查询条件进行筛选时，db会读取小于1000的编号作为临时数据，并在此中筛选文章id为1的前10条记录。整体扫描的数据成本最高为1000条记录，物理读最大为1000行的所覆盖的数据块。因为条件2“文章分类id”的选择性较小，逻辑读最悲观可能跟物理读数据块相同，但依然会获得很高的查询性能。

         但如果我们以条件2“文章分类Id是1”作为驱动条件进行筛选时，一般情况db都会物理读取至少总数千分之三的数据块，在1000W的基数下，将会远远大于条件1的只获取编号小于1000的物理读。虽然在分类Id上也建立了索引，但性能确远不如条件1，由此可见驱动条件一定要尽可能的选择选择性高的条件。 案例中的文章id比较特殊，属于选择性最高的唯一索引，但如常见的“省”、“市”、“县”中如果以作为“省”作为驱动条件，远不如按选择性更高的“县”效果更好。

        到这可以给出两个基本原则：

        1.尽量缩减驱动查询条件的数据范围，以减少总体数据扫描量；

        2.尽量扩大过滤查询条件的数据范围，以增加填满批量数组快速返回查询结果的概率；

2.4 连接：过滤连接、分组连接、排序连接、拼接连接

        任何表连接都会有其目的性，本文主要讲以下四种基本类型。

        1.拼接连接：为显示关联表中的数据必须添加的连接；

        2.过滤连接：为筛选数据过滤必须的连接如示例中按分类名称筛选文章数据；

        3.分组连接：为分组必须添加的连接；

        4.排序连接：为排序必须添加的连接。

        以上列举了主要四种连接可能单独出现，也可能组合同时出现。如分类Category表与文章Article表即可以进行分组也可以同时按名称进行筛选，并且按Category排序。

2.5 排序：默认顺序、指定顺序

        默认顺序： 数据表写入时保存的顺序，即直接select查询出不需要增加任何order时所返回的顺序；

        指定顺序： select中明确order desc 或 asc

2.6 驱动表、被驱动表

        驱动表：连接中用来最先获得数据，并以此表的数据为基础，逐步获得其它表的数据，直至最终查询到所有满足条件的数据的第一个表，如果以循环嵌套连接为例则可以理解为外层循环；

         被驱动表：需要不断的拿外层循环传进来的每条记录去匹配数据，如果以循环嵌套连接为例则可以理解为里层循环；

三、示例

         我们这里讲sql耗费比较重的三个常见操作分别为连接、模糊检索‘%key%’、分组聚合。

3.1 模糊检索 

        模糊检索走DB内全文检索方案，细节后续例子给出。

3.2 连接 

        一般说数据库连接实现方式都是指从实现算法上的三种基本形式hash join、merge join、 nested loop这是连接算法。本文主要从sql结构上来讲连接，还是以查询分页为例，逐个分析上面说的四种常见的连接类型。

        1、拼接连接：需求为文章标题、分类名称两个字段，并取第一页数据。

          直观思路sql：select article.title, category.name from Article as article left join Category as category on article.categoryId=category.id limit 10

        连接只是为了满足数据投影连接，并不需要参与任何过滤、分组、排序操作，但却在分页之前进行了连接操作，将不在当页的数据也进行了连结操作，查询成本有了额外的开销。此时只需将sql调整为：

        select  article1.title,  category.name  from (select article.title, article.categoryId from Article as article  limit 10) as article1 left join Category as category on article1.categoryId=category.id

        还是将Article作为驱动表但提前筛选出了最终10条数据，就会极大的减少了整体数据连接成本，提高查询效率。“这里的指导方针就是尽可能在需要的时候才去获取数据，从成本的角度上看，避免扫描那些会被筛选条件过滤或者不在当前查询页中的行就至关重要”。

    注：对于这种将结果数据分页提前到驱动表数据分页的优化方式本文后续统称为“内分页”。

       2、过滤连接： 需求为显示文章标题、分类名称两个字段、分类名称以字符“key”开头的分类并取第一页数据。

        直观思路sql：

        select article.title, category.name from Article as article left join Category as category on article.categoryId=category.id where category.name like ‘key%’   limit 10

        因为category.name并没有在Article中存储，无法仅从Article表中提前获取足够的条件进行分页判定，也就没办法采用上面所讲的内分页进行优化，但可以从sql结构变化的角度优化连接sql具体如下：

        select article.title, category.name from Article as article left join Category as category on article.categoryId=category.id and category.name like ‘key%’ limit 10

        虽然只是简单调整了下分类名称过滤sql出现的位置，但从成本的角度看，却从拼接连接后的结果数据中筛选，变成了在拼接连接过程中就进行数据筛选。会减少中间临时表的数据总量，从而减少逻辑读。但本人并不推荐这种在sql上解决过滤连接的需求，更好的方式应该是从产品交互体验上先引导用户完成Category的筛选，并将筛选后的category.id作为动态参数来构建Article的sql，从而尽量避免过滤连接出现。也就可以采用内分页优化sql，生成后的sql如下：

        select  article1.title,  category.name  from (select article.title, article.categoryId from Article as article where  article.categoryId in (categoryIds) limit 10)  as  article1 left join Category as category on article1.categoryId=category.id

        3.分组连接：需求为按分类名称汇总文章总数，并获取第一页数据。

        直观思路sql：

        select category.name, count(1) from Article as article left join Category as category on article.categoryId=category.id group by category.name limit 10

        直接按内分页的思路去给出优化sql：

        select tmp.id, tmp.name, count(aid)  from (select category.id, category.name, article.id  as aid from (select category.id, category.name from Category as category  group by category.id limit 10) as category left join Article as article on category.id=article.id) as tmp group by tmp.id, tmp.name;

        4.排序连接：基本和分组连接类似，此处就不再讨论。

        如果能提前加工宽表，就不存在连接，这样连接开销为零。当不能形成宽表时，选择较小的驱动表，减少不必要的数据连接开销，从而大幅缩减需要扫描的数据块，提高查询效率。当然大家可能都已经发现，类似“内分页”的优化方法修改后，通常SQL几乎都比直观思路的sql层次结构多，也更复杂。在项目初期数据量较少的时候查询性能本身不是瓶颈，随着项目的发展数据量增多，查询效率下降时系统中又存在大量的“直观思路sql”需要优化，逐个修复开发成本会很高。而且实际生产环境中可能还存在多个表进行桥连的情况，也无法简单的照搬上面所讲的优化手段。从而寻找一种SQL结构简单，相对更通用的方案就成为了一件真正务实的事。

        还是从驱动表、驱动条件这两个核心概念入手。通常的检索更关注内容本身，例如本文的案例Article，所有的筛选、分组、聚合都是以内容表本身，也就是说对应的操作大多数也落在内容表本身上，只有少量的关联、筛选条件需要表连接操作。所以通常情况以内容（Article）表本身作为驱动表就是一个非常正确的选择，同时把需要连接后获得的操作（筛选、排序、分组）数据提前存储到内容表中，这样就可以在内容表中完成所有操作。

        再说驱动条件，因为筛选项多数是由用户在界面中输入临时决定，通常的做法是对用户使用过的筛选项进行统计或由业务方提供出经常使用的筛选组合，再由开发进行构建索引或组合索引。虽然组合索引可以极大的提高查询效率，也是高效的优化手段。尤其在BI系统下的多维分析场景中，维度（列）的灵活组合并对业务的探索是强需求。所以在没有形成固定的报表查询之前，也无法对所有维度都构建联合索引，另外因为前导列问题导致索引失效（有些商业数据库支持跳跃索引可缓解此问题，我司目前应该大多都是mysql为主）。如果将所有查询可能的列都放入一个索引中进行筛选，这样就可以规避掉联合索引的缺陷，达到快速查询的效果。还是选定Article表作为查询业务的事实驱动表，这里给出一个基本实现,整体的思路是将表中所有的外键、外表关键数据、行内数据进行压缩并合并成一个新的查询辅助列search_code，并基于此字段构建全文索引，当有检索条件时，根据输入的所有查询条件，构建以此字段的索引作为驱动查询条件搜索项。正常输入的所有查询条件只作为过滤筛选条件对驱动条件筛选出的结果集进行二次过滤，以保证最终结果正确性。

        具体内容如下

        1.分析Article表上所有的外键进行处理，并加工成全局唯一标识。 主键如果是UUID的可以考虑进行适当的压缩，主键如果是自增流水的则考虑以“表名+流水”组合方式进行二次编码形成全局唯一标识。当形成标识后则将所有标识以分号分割，合并成一个字段存储并将名称命名为search_code。如search_code = C001;S001;T001,其中C001是列categoryId主键编码后的值其原值为001，S001是列studentId值为001进行编码后的结果、T001是列teacherId值为001进行编码后的结果。

        2.除连接外，另外一个查询耗时的操作就是like ‘%key%’操作。将Article中的描述列信息title、content信息进行二次编码，对title这种字符长度不会太长的字段，可以用column列信息缩写+title内容进行合并编码，而对于content内容字段可以单独编码。如content内容为“北京市长春节讲话“来进行举例，如果按中文词表分词法结果为 “北京 n、市长 n、春节 n、讲话 v”如果按此结果构建全文索引的话，当用户搜索“长春”时，驱动条件就无法命中，虽然本例中查不到数据似乎也可以说的过去，但sql语句检索like ‘%长春%’逻辑上确实可以命中这句“北京市长春节讲话”，但性能上看实在差强人意，改进方案也很简单。这时我们可以采用一元或二元分词法来进行分词，然后再构建索引，这样当用户输入长春时全文索引也可以作为驱动查询条件覆盖到。但同时也带来一个问题，如果产品形态上有按最小一个字作为输入查询的场景（如：小学语文课文《春》），从而采用一元分词的话，如果用户输入“京话”则“北京市长春节讲话“也可以作为驱动查询条件命中。所以具体说采用词表分词还是二元（一元）分词需要根据业务灵活把握。个人建议对业务上要求反复审核并确保无误的内容可以采用二元（一元）分词法，如教案、学案、课件等。因为不是由用户产生，生产效率相对较低，总数量可以预期。但对于如用户评论、考试、答疑内容等，就需要酌情评估下场景是否适用于二元分词法，主要评估二元分词相比词表分词会带来索引文件膨胀的问题是否可以接受。

        经过外键的二次编码，文本内容的分词并统一合并到新追加的字段search_code，因为mysql老版本的全文索引对中文支持不好，可以考虑将中文转换为unicode。此时search_code内容如下C001;S001;T001; u5317;u4eac;u5e02;u957f;u6625;u8282;u8bb2;u8bdd，并在search_code上构建全文索引同时调整ft_min_word_len=1。

        当用户需要检索 Category 为 C001, content 内容含有北京两个汉字时，构建的sql如下：

SELECT * FROM Article WHERE MATCH(`search_code`) AGAINST(‘+北 +京 +C001‘ in boolean mode) and categoryId=‘001’ and content like ‘%北京%’;

        相比于常规sql多出了MATCH(`search_code`) AGAINST(‘+北 +京 +C001' in boolean mode)这句核心代码，此sql会以search_code上的全文索引作为驱动查询条件，减少查询范围。但中间结果中也会包含“北方去南京“这样的非预期数据，但通过categoryId=‘001’ and content like ‘%北京%’;这样的筛选条件进行二次过滤，最终结果的正确性是可以保证的。

        虽然此方法在查询上获得了极大的收益，但需要提前构建查询辅助列search_code，至于如何构建查询辅助列尤其解决桥连（A join B join C)的方法和技巧大家如果有兴趣，可以再单独开贴讨论。很多团队也直接采用搜索引擎来解决此问题，此时同步搜索引擎的方案本质上跟构建查询辅助列大同小异。

3.2 聚合

        第一优先选用列存储的数据库支持分组聚合操作，因为数据结构的特点，尤其对分组列和指标列较少的话，将会起到显著的效果。因为分组聚合函数所扫描的数据量通常都很大，sql的建议主要依赖列存储引擎本身的情况。例如按天统计文章总数、分类为C001总数、分类为C002总数三个指标都在单表的时，可以用case when 方式筛选结果，也可以先通过where条件进行驱动、过滤条件分别筛选结果，再通过join关联起来。这两种方式最终结果相同，但查询效率在不同的存储引擎上效果还有差异的，主要取决于具体的存储引擎对sql优化情况。遇到具体问题可有根据实际情况对比下。如果指标需要在多表关联求结果时，对不同的事实表先分组聚合，然后再对连接纬度进行关联。其执行效率会高于先对表进行拼接，再分组聚合的方式。

常见问题：

Q：如何确定（是否）/（引导）驱动查询条件？ 

答：RBO、CBO、最左原则、INDEX SKIP SCAN。

Q：如何查看选择性？

答：select count(distinct column)/count(*) from Table

Q：连接最好的优化手段？

答：提前物化宽表，或为加速查询构建索引、物化视图、大宽表等，虽然可以显著的提高查询速度，但会降低数据表写入的速度。

Q：order by优化？

答：force index 指定索引，去除filesort. 将需要排序的值提前清洗成默认顺序。

Q：原则是一定要坚守的吗？

答：原则不是法则，具体视情况。

Q：分页是否需要真实的总页数？

答：个人觉得不需要，现在就有分析数据库就限制返回总行数。

Q： 对like查询为什么不统一都走搜索引擎？

答：有很多成熟且优秀的团队选型就是ES，这里优先选择DB主要还是因为DB支持很多函数，搜索引擎目前支持的并不是很好。

Q：构建查询辅助列会不会拉低业务系统TPS？

答：采用异步或延迟的构建方法，不会影响tps。