使用延迟的join来优化count(*)和limit语句（翻译）

在很多运用中，常有这样的结构，一个主表和一个附加表，附加表用来存一些附加信息，取数据的时候需要和主表连接。

如果你在这样的结构上执行count(*)，那么即使使用了left join，MySQL仍然会做连接。同样的如果使用了limit，MySQL也会连接一些最终会扔掉的行。当limit的偏移量很大的时候，这样的连接是很浪费时间的。

对于这样的情况，你可以帮助MySQL不要对count(*)做连接或者做了limit之后再连接。

请看下面的例子：

   1      CREATE TABLE `fact` (
   2        `i` int(10) UNSIGNED NOT NULL,
   3        `val` int(10) UNSIGNED NOT NULL,
   4        KEY `i` (`i`,`val`)
   5      )
   6       
   7      CREATE TABLE `dim` (
   8        `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   9        `pad` varchar(100) NOT NULL,
  10        PRIMARY KEY  (`id`)
  11      )
  12       
  13      mysql> SELECT count(*) FROM dim;
  14      +----------+
  15      | count(*) |
  16      +----------+
  17      |    30720 |
  18      +----------+
  19      1 row IN SET (0.00 sec)
  20       
  21      mysql> SELECT count(*) FROM fact;
  22      +----------+
  23      | count(*) |
  24      +----------+
  25      |  7340032 |
  26      +----------+
  27      1 row IN SET (0.00 sec)
  28       
  29      mysql> SELECT count(*) FROM fact WHERE i<10000;
  30      +----------+
  31      | count(*) |
  32      +----------+
  33      |   733444 |
  34      +----------+
  35      1 row IN SET (0.44 sec)
  36       
  37       
  38      mysql> SELECT count(*) FROM fact LEFT JOIN dim ON val=id WHERE i<10000;
  39      +----------+
  40      | count(*) |
  41      +----------+
  42      |   733444 |
  43      +----------+
  44      1 row IN SET (2.15 sec)
  45       
  46       
  47      mysql> SELECT i,pad FROM fact LEFT JOIN dim ON val=id WHERE i<10000 LIMIT 500000,10;
  48      +------+------------------------------------------+
  49      | i    | pad                                      |
  50      +------+------------------------------------------+
  51      | 6811 | 06bfea523be29a6070488ee66e874dffa170de76 |
  52      | 6811 | 3baf40c2d76998270f8954bedda386b5021e0624 |
  53      | 6811 | 35ad5c3a9d0763acc305992327864bed1af34167 |
  54      | 6811 | 81de98a3ef74ddc0fa4f7c95a27e3dbebca8df0d |
  55      | 6811 | 11cde5d0bd8ffe1eda86b39d05a58c525e8fac8f |
  56      | 6811 | 25c474b380388c23b1de730c4255612e1233e14e |
  57      | 6811 | 1d32b5ba28a513097fc88f3efd91155b2697aeec |
  58      | 6811 | bdc9a39cdfafda26fc2f48a48abd3bc5f051a4ea |
  59      | 6811 | d2e6cb9ca5aa9dd2bc3d033de45579a76ccdafdf |
  60      | 6811 | 0130c708083d77377255bd8f5e0daa15fbb24212 |
  61      +------+------------------------------------------+
  62      10 rows IN SET (3.88 sec)
  63       
  64      mysql> SELECT i,pad FROM (SELECT i,val FROM fact WHERE i<10000 LIMIT 500000,10) res LEFT JOIN dim ON val=id;
  65      +------+------------------------------------------+
  66      | i    | pad                                      |
  67      +------+------------------------------------------+
  68      | 6811 | 06bfea523be29a6070488ee66e874dffa170de76 |
  69      | 6811 | 3baf40c2d76998270f8954bedda386b5021e0624 |
  70      | 6811 | 35ad5c3a9d0763acc305992327864bed1af34167 |
  71      | 6811 | 81de98a3ef74ddc0fa4f7c95a27e3dbebca8df0d |
  72      | 6811 | 11cde5d0bd8ffe1eda86b39d05a58c525e8fac8f |
  73      | 6811 | 25c474b380388c23b1de730c4255612e1233e14e |
  74      | 6811 | 1d32b5ba28a513097fc88f3efd91155b2697aeec |
  75      | 6811 | bdc9a39cdfafda26fc2f48a48abd3bc5f051a4ea |
  76      | 6811 | d2e6cb9ca5aa9dd2bc3d033de45579a76ccdafdf |
  77      | 6811 | 0130c708083d77377255bd8f5e0daa15fbb24212 |
  78      +------+------------------------------------------+
  79      10 rows IN SET (0.30 sec)

可以看到使用的小技巧改善了速度。