MySQL 统计信息以及执行计划预估方式初探

   <p>数据库中的统计信息在不同（精确）程度上描述了表中数据的分布情况，执行计划通过统计信息获取符合查询条件的数据大小（行数），来指导执行计划的生成。</p>    <p>在以Oracle和SQLServer为代表的商业数据库，和以开源的PostgreSQL为代表的数据库中，直方图是统计信息的一个重要组成部分。</p>    <p>在生成执行计划的时候，通过统计信息以及统计信息的直方图来预估符合条件的数据行数，从而影响执行计划的生成。</p>    <p>统计信息对执行计划的影响，具体体现在：索引的查找与扫描，多表连接时表之间的驱动顺序，表之间的JOIN方式，以及对sql查询语句的资源分配等等。</p>    <p>但是在MySQL数据库中，执行计划的方式相对简单，表之间的JOIN只有LOOPJOIN一种方式，且没有并行执行计划等，也就说通过预估结果集的行数对执行计划的影响有限。</p>    <p>但是对于某些情况，依旧需要预估的方式来指导执行计划的生成，</p>    <p>比如常见的多表连接时驱动顺序，多数情况下是小表驱动大表（不完全一定）的方式来实现查询的，因此MySQL中一样需要预估来指导执行计划的生成。</p>    <p>不过MySQL中的统计信息相对来说简单很多，只有一个cardinality信息来预估索引的选择性（show index from table），</p>    <p>索引统计信息不包含直方图的信息，非索引列也不会生成直方图，也就是无法通过直方图来预估查询数据的大小，mysql是通过其他方式来实现预估的。</p>    <p>对于有直方图的数据来说，直方图为预估提供了重要的依据，对于没有直方图的MySQL，执行计划是如何预估的？预估的准确性有如何？</p>    <p>笔者在研究这个问题的时候，一开始也遇到不少疑惑的地方，还是看了博客园大神的问题才得以释惑，后面会给出链接。</p>    <p> </p>    <p>首先通过例子，通过一个非常简单的查询来观察一个有意思的现象。</p>    <p>新建测试表，测试表如下：</p>    <pre>  <code class="language-sql">create table test_statistics  (      id int auto_increment primary key,      col2 varchar(200),      col3 varchar(200),      create_date datetime,      index idx_create_date(create_date)  )ENGINE=InnoDB;</code></pre>    <p>存储过程通过循环插入数据，调用存储过程生成100W行数据（100W行的数据，在实际应用中已经是一个非常小的数据量了），create_date字段上生成一个范围之内的随机时间。</p>    <pre>  <code class="language-sql">CREATE DEFINER=`root`@`%` PROCEDURE `p_insert_test_data`(      IN `loop_count` INT  )  BEGIN      declare i int;      while (loop_count>0)       do              insert into test_statistics(col2,col3,create_date) values (uuid(),uuid(), DATE_ADD(sysdate(), INTERVAL  -rand()*2400  hour));          set loop_count = loop_count -1;      end while;  END</code></pre>    <p>写入测试数据完成之后，进行如下两个查询做测试。</p>    <p>简单地使用select count(1)的来做测试</p>    <p>首先看第一个查询：查询的时间范围是: where create_date>’2017-11-01 12:00:00′ and create_date<’2017-11-01 16:00:00′</p>    <p>可以发现：explain预估的行数，与实际行数完全一致。</p>    <p><img src="https://simg.open-open.com/show/e69a35710f2ed24beb5e912b94e8d218.png"></p>    <p>继续第二个查询，扩大查询的时间范围，查询的时间范围是:where create_date>’2017-11-01 12:00:00′ and create_date<’2017-11-03 16:00:00′</p>    <p>可以发现，此时的explain执行计划的预估，与实际行数出现了严重的偏差</p>    <p><img src="https://simg.open-open.com/show/ee85b099d7509108ffee1552194cee3c.png"></p>    <p>为什么第一个查询做到了精确的预估，而第二个查询的预估出现严重的偏差？</p>    <p>这一点要从预估的计算方式入手来说。</p>    <p>首先，第一个查询和第二个查询，唯一的不同是，第二个查询的时间范围放宽了，为什么时间放宽之后，执行计划的预估的准确性就大大下降？</p>    <p>既然是“预估”，就一定是存在误差，只不过是误差大与小的问题，误差的大下与具体的预估的方式有关。</p>    <p>任何预估的实现，都是以一种在不同程度上“以偏概全”的方式进行的，比如SQL Server是以对相关数据page的通过某种百分比来取样，然后存储在直方图中做预估依据的。</p>    <p>当然，这种“以偏概全”的预估方式，是在性能与精确度之间权衡折中的结果.</p>    <p>在考虑收集统计信息对性能和资源影响的前提下，预估策略各种方式或者代价尽可能减少对预估产生误差的因素，关于直方图的生成这里不细说。</p>    <p>对于没有直方图的MySQL，它是是在执行的时候，通过扫描符合查询条件的部分数据页后做预估统计的.</p>    <p>MySQL是在查询的时候，直接对查询条件范围内的数据页，取一定比例样本做统计之后预估的，但是这里取样的数据页面有一定的限制，不会无限制取样做统计预估。</p>    <p>如果符合条件的数据页超出了预定的范围，则会取部分页进行预估，而不是全部页（为什么不是全部样做统计预估，原因就不用说了吧）。</p>    <p>比如下图中，不管是聚集索引还是二级索引（非聚集索引），理论上说都是一颗平衡树，暂不探究其细节。</p>    <p>假如符合条件的数据是一个范围，位于两个矩形框之间。矩形框分别是范围的左右节点，中间可以想象成多个叶子节点</p>    <p><a href="/misc/goto?guid=4959757160528124140" rel="nofollow,noindex">参考zhanlijun大神的文章</a> ，</p>    <p>上述参考链接中得知，MySQL在5.5之后的预估原理如下：</p>    <p>其预估扫描的数据页分别是前后两个数据页，以及从左边开始连续8个数据页，得到平均每个page的行数，根据总的page个数预估出这个范围的数据行数。</p>    <p>具体说，也就是取左右两个叶子节点，以及从左叶子节点开始连续8个页的数据做统计，中间可能有多个数据页，但也会被忽略，这就是上面提到的“以偏概全”的方式。</p>    <p>这里面就存在一个最明显的问题，也就是符合条件的数据页面与预估时候采集的页面的大小关系。</p>    <p>如果符合条件的数据页的分布少于10个，当然在预估的时候，会全部扫描这些page，当然预估是完全精确的，这也是第一个查询执行计划预估的实际行数完全不一致的原因。</p>    <p>如果符合条件的数据页的分布大于10个，当然在预估的时候，会部分扫描这些page，预估的误差情况就此产生，这也是第二个查询执行计划预估的实际行数差异较大的原因。</p>    <p><img src="https://simg.open-open.com/show/e9abd3ad328764cdfb8ddac8bdd7f451.png"></p>    <p>当然MySQL的每个版本可能都有所改进或者差异，笔者并没有从源码中找到具体的算法，当前测试的是5.7.20版本。</p>    <p>但目前仍不清楚，</p>    <p>1，在create_date字段上，时间是按照DATE_ADD(sysdate(), INTERVAL -rand()*2400 hour)生成的，从整体分布看，基本按照时间均匀分布的.</p>    <p>理论上根据这种方式推到，得到的预估结果偏差应该不会很大，但尚不清楚为什么预估与实际存在如此大的差异。</p>    <p>2，尝试找到预估值从精确到产生差异的临界点，通过查询实际行数，根据key_len的值以及B树索引的存储原理（二级索引叶子节点存储的二级索引的key值+聚集索引的key值）.</p>    <p>理论上计算出来当前查询一个大概的取样的page个数，发现这个值预报理论上的10个page差异较大，可能是推到方式有问题，或者是MySQL预估本身有一些不知道的细节问题。</p>    <p>3，没有详细翻MySQL的源码，尚未找到具体的实现细节。</p>    <p>对于有直方图的数据库来说，直方图的信息也不是没有代价，或者是万能的，直方图也有直方图的局限性，这里暂不表述。</p>    <p>对于尚没有直方图的MySQL数据库来说，其预估原理是每次查询的时候进行对相关的数据页面进行采样预估的，而不是从直方图中获取到预估信息的，这是一个很消耗性能的操作。</p>    <p>详情参考： <a href="/misc/goto?guid=4959757160620683594" rel="nofollow,noindex">http://www.orczhou.com/index.php/2013/04/how-mysql-choose-index-in-a-join/</a></p>    <p>这可能会导致MySQL不适合做较大数据量或者较为复杂的JOIN操作，当然这也取决于具体的业务设计方案以及对数据的依赖程度，或者主观上的查询提示操作。</p>    <p>说这句话是冒着被MySQL的大神以及粉丝们怒喷的风险的。</p>    <p>关于MySQL的预估的知识点，搜索到的文章并不是很多，也拘泥于个人的认识有限，也希望对这方面有关注的大神多多指点。</p>    <p>据说MySQL在8.0之后的版本中会加入直方图信息，以及其他JOIN方式（除了LOOP JOIN），这可能对性能上有比较大的帮助。</p>    <h2>参考链接</h2>    <ul>     <li><a href="/misc/goto?guid=4959757160528124140" rel="nofollow,noindex">https://www.cnblogs.com/LBSer/p/3333881.html</a></li>     <li><a href="/misc/goto?guid=4959757160620683594" rel="nofollow,noindex">http://www.orczhou.com/index.php/2013/04/how-mysql-choose-index-in-a-join/</a></li>    </ul>    <p> </p>    <p>来自：http://www.importnew.com/28075.html</p>    <p> </p>