5.5 几个笨窍门

li3p

　　在本章结束之前，我们必须要讨论一些惯用技巧，这些技巧常被用来优化某些查询的性能。尽管这些技巧都有相当的针对性，但是在一些之外的情形下也会发现类似的窍门。
5.5.1 两个好过一个（Two Is Better Than One）
　　有些时候MySQL并不会像你所期望的那样去优化看起来很简单的查询。有一个很好的例子能够说明这种行为，比如一个用于记录历史股价的数据库，其中包含了2张表：SymbolHistory 和 Symbols。
　　目前我们所关注的，Symbols表中包含两个重要的字段Id 和 Symbol。其中Id 是一个 auto_increment 类型的主键，然后就是PriceHistory表：
mysql> DESCRIBE PriceHistory;+----------+---------+------+-----+------------+-------+| Field    | Type    | Null | Key | Default    | Extra |+----------+---------+------+-----+------------+-------+| SymbolID | int(11) |      | PRI | 0          |       || Date     | date    |      | PRI | 0000-00-00 |       || Open     | float   |      |     | 0          |       || High     | float   |      |     | 0          |       || Low      | float   |      |     | 0          |       || Close    | float   |      |     | 0          |       || Volume   | float   |      |     | 0          |       |+----------+---------+------+-----+------------+-------+8 rows in set (0.01 sec)
　　表中有一个复合索引(SymbolID, Date)。
　　Symbols 表将股票行情自动收录器映射到数值标识符。同时此表还包含其他的一些安全相关的元数据（metadata）。PriceHistory 表包含历史的股价信息。在这些数据上经常被执行的查询之一：“按照时间倒序显示某支股票的停牌价格”
　　比如为了获取IBM的历史股价信息，查询语句如下：
mysql> EXPLAIN SELECT date_format(Date,'%Y%m%d') as Day, Close    -> FROM Symbols, PriceHistory    -> WHERE Symbols.ID=PriceHistory.SymbolID AND Symbols.Symbol = 'ibm'    -> ORDER BY Date DESC \G*************************** 1. row ***************************        table: Symbols         type: constpossible_keys: PRIMARY,Symbols_SymbolIDX          key: Symbols_SymbolIDX      key_len: 20          ref: const         rows: 1        Extra: Using filesort*************************** 2. row ***************************        table: PriceHistory         type: refpossible_keys: PriceHistory_IDX          key: PriceHistory_IDX      key_len: 4          ref: const         rows: 471        Extra: Using where2 rows in set (0.01 sec)
　　注意在EXPLAIN 输出中的Using filesort，这表示MySQL需要对全部记录针对日期进行排序。这说明Date列尽管在索引中，但是MySQL并不会直接用它来排序，因为Date不是复合索引的第一个部分。结果集在以正确的顺序返回之前经过了两个阶段。这个排序的过程当在各种各样的股票上每分钟执行上百次的时候会比较慢，尤其在某些股票有上千条记录时。
　　为了提升性能，我们需要安排MySQL能够查询PriceHistory并利用Date列上的索引。最简单的办法就是使用临时变量将其分为两个查询，就像之前我们解决不支持子查询的问题时那样：
mysql> SELECT @sid := Id FROM Symbols WHERE Symbol = 'ibm';+------------+| @sid := Id |+------------+|     459378 |+------------+1 row in set (0.02 sec) mysql> EXPLAIN SELECT date_format(Date,'%Y%m%d') as Day, Close    -> FROM PriceHistory WHERE SymbolID = @sid ORDER BY Date DESC \G*************************** 1. row ***************************        table: PriceHistory         type: refpossible_keys: PriceHistory_IDX          key: PriceHistory_IDX      key_len: 4          ref: const         rows: 7234        Extra: Using where1 row in set (0.00 sec)
　　这个提升通常意味着一个每秒处理200个查询且CPU居高不下的服务器，现在是一个每秒处理700个查询且CPU还部分空闲的服务器。执行两个查询而不是一个所带来的开销，通常还是要小于额外的排序阶段所带来的开销。
5.5.2 使用Unions 代替 OR条件
　　之前我们使用了一个与下面类似的查询，来演示MySQL有效处理情况的能力：
mysql> EXPLAIN SELECT COUNT(*) FROM Headline    -> WHERE ExpireTime >= 1112201600 AND Id *************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: Headline         type: rangepossible_keys: PRIMARY,ExpireTime          key: ExpireTime      key_len: 4          ref: NULL         rows: 12009        Extra: Using where1 row in set (0.00 sec)
　　本例中，MySQL使用ExpireTime索引来检索一组记录，然后又用WHERE子句的其它部分，来消除ID的值小于等于5,000,000的记录。
　　但是如果将条件逻辑由AND改为OR，并且将COUNT(*)改为其它更有意义的数据，会发生什么？
mysql> EXPLAIN SELECT * FROM Headline    -> WHERE ExpireTime >= 1012201600 OR Id     -> ORDER BY ExpireTime ASC LIMIT 10\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: Headline         type: ALLpossible_keys: PRIMARY,ExpireTime          key: NULL      key_len: NULL          ref: NULL         rows: 302116        Extra: Using where1 row in set (0.00 sec)
　　喔喔，MySQL决定执行一次完整的表扫描。实际上执行整个查询（而不是只是EXPLAIN一下）大概执行了3秒钟。让我们探究一下MySQL为什么这么决定？
　　我们知道MySQL每个查询只会为每张表之用一个索引，Headline表在Id列和ExpireTime列上各有一个索引，为什么它一个也不用？
　　无论MySQL用那个索引来检索记录，它不得不执行一次表扫描来满足其它的条件。使用OR条件的查询阻止了MySQL去简单的排除候选记录。所以与其使用一个索引找到一些记录之后再进行一次表扫描，MySQL决定还不如直接进行一次表扫描。这个在MySQL 5.0中必须修复的内容。
　　在范化良好的数据库中，像上面的那个查询不是很常见。但是当它们出现了，就会成为真正的性能杀手。幸运的是某些情况下我们可以用UNION来重写查询。
　　为了这么做，我们将查询分为两个，每个都使用一个独立的索引，然后我们将对结果集进行合并和排序，最终的结果看起来是这样：
(SELECT * FROM Headline WHERE ExpireTime >= 1081020749ORDER BY ExpireTime ASC LIMIT 10) UNION (SELECT * FROM Headline WHERE Id ORDER BY ExpireTime ASC LIMIT 10) ORDER BY ExpireTime ASC LIMIT 10
　　第一个查询应该能够使用ExpireTime索引，而第二个查询使用Id索引。我们必须确保在两个索引中指定要返回的记录数为10条。后面的ORDER BY 和 LIMIT子句会操心最后的排序和计数。
　　结果这个基于UNION的查询0.02秒就执行完了。这比之前的那条要快多了。为了确定我们对MySQL的行为理解无误，我们EXPLAIN一下：
mysql> EXPLAIN (SELECT * FROM Headline WHERE ExpireTime >= 1081020749    -> ORDER BY ExpireTime ASC LIMIT 10)    -> UNION    -> (SELECT * FROM Headline WHERE Id     -> ORDER BY ExpireTime ASC LIMIT 10)    -> ORDER BY ExpireTime ASC LIMIT 10 \G*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: Headline         type: rangepossible_keys: ExpireTime          key: ExpireTime      key_len: 4          ref: NULL         rows: 40306        Extra: Using where*************************** 2. row ***************************           id: 2  select_type: UNION        table: Headline         type: rangepossible_keys: PRIMARY          key: PRIMARY      key_len: 4          ref: NULL         rows: 1        Extra: Using where; Using filesort2 rows in set (0.00 sec)

　　非常不错，第二个查询需要一个文件排序操作，但是至少它使用了一个索引来检索所需的记录行。


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/7114/showart_276531.html