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

一、成本意识

      为了方便示意，本文以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语句，通常会有多个查询条件存在，其中一个或者几个条件会作为数据检索的发起者，这些条件