关于海量数据表的分页性能问题调试记录（mysql left join的性能分析）
最近开发一个订单系统，其中涉及订单列表显示的页面，在数据量达到10万级以上的时候，页面刷新超级慢，几乎可以打到30秒左右，而且点击某个分页也是反应及其慢，经过一段时间定位，发现问题所在，特此记录下来供大家参考。
一、业务需求简介：
涉及的表及其之间的关系如下：
要显示的表为订单表（大概20万条数据），同时每条记录需要显示客户姓名和供货商姓名，这样就需要通过订单表中的客户ID/供货商ID作为外键分别访问到客户表和供货商表，其中客户表中有2条数据，供货商表中有7条数据。
分页显示的订单表每页显示30条数据，大概需要分6000多页。此时每页显示特别慢。
二、问题分析：
由于订单列表页面是通过GENEXUS工具生成的，生成的每一页的MYSQL查询语句经查看后为类似如下形式：
|
SELECT T1.ID,T1.xxx,T2.name as customer_name,T3.name as supplier_name FROM ((`订单表` T1 LEFT JOIN `客户表` T2 ON T2.id=T1.客户ID) LEFT JOIN `供货商表` T3 ON T3.id=T1.供货商ID) LIMIT begin_row,end_row |
可见这个sql用了两个LEFT JOIN把三张表联合起来，其中订单表（T1)是主表，使用LEFT JOIN的好处是当客户ID或国货商ID没有对应的记录时，该行订单也会显示出来，避免遗漏。
上面这个SQL语句拿到SQL Manangerment Lite 里面执行，并分析执行时间和查询plan，发现这个sql语句执行了将近7-8秒，总查询次数为：T1记录数*T2记录数*T3记录数，即使做了外键或索引，总查询次数也至少为2*T1记录数。
为什么会这样？我们来看上面的LEFT JOIN的执行细节：MYSQL先是将T1与T2进行LEFT JOIN，这样执行了将近20万次(T1记录数),生成一张临时表Temp1（20万条记录），然后这张临时表Temp1再与T3进行LEFT JOIN，生成一张临时表Temp2(20万条记录），然后对这个临时表Temp2进行分页查询（LIMIT语句），这样的速度不慢才怪！
然后又做了一个实验，使用下面能起到同样效果的SQL查询语句进行查询：
|
SELECT T1.ID,T1.xxx,T2.name as customer_name,T3.name as supplier_name FROM (((SELECT ` 订单表` T1 LIMIT begin_row,end_row ) LEFT JOIN `客户表` T2 ON T2.id=T1.客户ID) LEFT JOIN `供货商表` T3 ON T3.id=T1.供货商ID) |
注意LIMIT的位置。
上面这个SQL执行飞快，毫秒级别，迅速返回了同样的结果，上面这个SQL的执行细节为：MYSQL显示将T1进行分页查询（LIMIT语句找到某页的30条记录），查询结果生成临时表Temp1（30条记录)，然后对这个临时表Temp1与T2进行LEFT JOIN，再进而与T3进行LEFT JOIN，总查询次数也不到100次！三、问题解决方案：
找到问题的原因之后，就可以又针对性的给出解决方案，由于使用的是GENEXUS自动生成这部分代码，所以先将GENEXUS中的GRID中删除【客户姓名】和【供货商姓名】这两个外链，同时过滤条件中也删除这两个外链的过滤，然后重新生成代码，检查生成代码中已经不再又LEFT JOIN语句了，仅仅是简单的SELECT FROM T1，此时运行WEB页面，发现翻页飞快。
然后写了两个procedure，分别为GetCustomerNameById(customer_id)，GetSupplierNameById(supplier_id)，从名字上即可知道是根据两个id获得名字的过程，在过程里面分别查询客户表和供货商表，返回姓名即可。然后定义两个变量（客户姓名/供货商姓名）放到Grid里并显示出来，编译运行后，发现在页面上可以正常显示客户姓名和供货商姓名，同时翻页功能依然保持飞快！
至此问题解决！