兰若寺

5.1 Query Processing Basics
5.1.1 Query Cache
高速缓冲这种技术应用得很广泛啦，比如 cpu、文件系统，MySQL 也不能免俗。实际上 MySQL 对任何 SELECT 的查询总是先在缓存中寻找结果。这种寻找非常快，因为是用来对比查询的哈希值，但是也有缺点，就是对大小写和空格比较敏感。不过据说在5.0版本中，此种情况会改善的。

查询缓存设置

Query Cache 为了 mysqld 添加了几个 MySQL 系统变量，它可以在配置文件中被设置，或在启动 mysqld 时的命令行上设置。

querycachesize 为了存储老的查询结果而分配的内存数量 (以字节指定) 。如果设置它为 0 ，查询缓冲将被禁止(缺省值为 0 )。

querycachetype 这个可以被设置为 (只能是数字)
0 (OFF, 不缓存或重新得到结果)
1 (ON, 缓存所有的结果，除了 SELECT SQLNOCACHE … 查询)
2 (DEMAND, 仅缓存 SELECT SQL_CACHE … 查询)

有两个可能的查询缓存相关的参数可以在一个 SELECT 查询中被指定：

SQLCACHE 如果 QUERYCACHETYPE 为 DEMAND，允许该查询被缓存。如果 QUERYCACHE_TYPE 为 ON

，这是缺省的。如果 QUERYCACHETYPE 为 OFF，它不做任何事
SQLNOCACHE 使这个查询不被缓存，不允许这个查询被存储到高速缓存中

查询缓存的状态和维护

使用 FLUSH QUERY CACHE 命令，你可以整理查询缓存，以更好的利用它的内存。这个命令不会从缓存中移除任何查询。FLUSH TABLES 会转储清除查询缓存。

RESET QUERY CACHE 使命从查询缓存中移除所有的查询结果。

你可以检查查询缓存在你的 MySQL 是否被引进：

mysql> SHOW VARIABLES LIKE ‘havequerycache’;
+—————————+———-+
| Variablename | Value |
+—————————+———-+
| havequery_cache | YES |
+—————————+———-+
1 row in set (0.00 sec)

在 SHOW STATUS 中，你可以监视查询缓存的性能：
Qcachequeriesincache 在缓存中已注册的查询数目
Qcacheinserts 被加入到缓存中的查询数目
Qcachehits 缓存采样数数目
Qcachelowmemprunes 因为缺少内存而被从缓存中删除的查询数目
Qcachenotcached 没有被缓存的查询数目 (不能被缓存的，或由于 QUERYCACHETYPE)
Qcachefreememory 查询缓存的空闲内存总数
Qcachefreeblocks 查询缓存中的空闲内存块的数目
Qcachetotal_blocks 查询缓存中的块的总数目

Total number of queries = Qcacheinserts + Qcachehits + Qcachenotcached.

5.1.2 Parsing, Analysis, and Optimization
此刻，MySQL 开始对未被 cache 的查询动手了。首先是解析这个查询，看语法上有没有问题和获取一些有关查询的基本信息，例如是什么类型的查询？涉及到哪些表？WHERE 子句是什么内容？

解析完成以后，查询优化器就开始接手了。查询优化器的目的就是以最有效率的方式完成查询，也就是检测尽可能少的记录。这个好理解，因为服务器的 I/O 是制约 MySQ L性能的重要因素，越少读写，性能就越好。

MySQL 把大量的精力都放在索引和表联合的顺序上了，这是些都是重中之重。

5.1.3 Using EXPLAIN
按照我的理解，EXPLAIN 类似一个 DEBUG 的工具，他能提供必要的信息让你去分析。官方是这样解释的：当你在一条 SELECT 语句前放上关键词 EXPLAIN，MySQL 解释它将如何处理 SELECT ，提供有关表如何联结和以什么次序联结的信息。借助于 EXPLAIN，你可以知道你什么时候必须为表加入索引以得到一个使用索引找到记录的更快的 SELECT。

5.1.3.1 Joins
当查询涉及到多个表时，事情变得有点复杂。这时查询的次序非常重要，可惜这不是 MySQL 的强项。优化器在执行解析之前，会尝试表所有可能的组合。因此，在查询涉及到很多表的时候，MySQL 的性能不尽人意。

5.1.4 Execution
执行时，MySQL 会把结果暂存临时表。此外，MySQL 在执行的过程中会收集一些信息：谁发布的查询？查询耗费了多少时间？返回了多少行？

5.2 Optimizer Features and Oddities
通常 MySQL 都会使用索引而且它会认为这更富有效率。在两种情况下，这也会导致虚假的结果。

5.2.1 Too Little Diversity
当查询涉及到超过 30% 的行时，优化器将不会使用索引，它会进行全表搜索，这样其实更快。因为索引是按某种顺序排列的和实际存储中的行排列不一样地，这样使用索引时，磁盘是随机搜索的；如果全表搜索，磁盘是按顺序读取的，所以此时全表搜索更有效率。同理，当表非常小的时候，也无需使用索引。

5.2.2 Index-Based Ordering
排序也不是 MySQL 的强项，不过也有解决的办法，也就是多列索引啦。

5.2.3 Impossible Queries
MySQL 会对 WHERE 子句做基本的逻辑检查，它不会在错误的逻辑查询上浪费时间，例如下面这个。
mysql> SELECT * FROM mytable WHERE id < 5000 and id > 30000

5.2.4 Full-Text Instead of LIKE
全文索引很显然比 LIKE 子句有效率。

5.3 Identifying Slow Queries
启用 slow log query，可在 my.cnf 中 server 段添加一句：
logslowqueries=/path/to/log

有许多查询在某一时刻特别慢，而在别的时刻却正常。这种现象有以下几个原因：
1. 查询的表被锁定。 Lock_time 会显示表要被锁定多少时间。
2. 没有任何数据和索引被告诉缓冲过。
3. 后台运行的一个进程，使磁盘进行大量读写。
4. 服务器处理其他很多的并发进程， CPU 能力不足。

mysqldumpslow ， 这个脚本可以对日志进行分析，让我们了解慢速查询执行的频率。

5.4 Influencing MySQL with Hints
这一节主要是来关注一些能影响查询进程的技巧。

5.4.1 Join Order
MySQL 对于多表联合查询做得不是够好，因为它对表联合的次序不能很好的优化。所以如果你觉得你对表联合

次序比 MySQL 还有把握的话，你可以用 STRAIGHTJOIN 来替代查询中的逗号和关键词 JOIN：
SELECT * FROM table1 STRAIGHTJOIN table2 WHERE …

5.4.2 Index Usage
如果你想让 MySQL 用指定的索引，忽略别的话，考虑用这个技巧 USE INDEX ：
SELECT * FROM mytable USE INDEX (mod_time, name) ...

如果你想让 MySQL 忽略指定的索引，IGNORE INDEX ：
SELECT * FROM mytale IGNORE INDEX (priority) ...

强迫 MySQL 使用特殊的索引，在查询中使用 FORCE INDEX ：
SELECT * FROM mytable FORCE INDEX (mod_time) ...
如果你指定的索引不能满足查询， MySQL 将会使用能够满足查询的索引。

5.4.3 Result Sizes
就像大多数技巧一样，如果你不清楚技巧的真正作用请不要使用它们。滥用这些技巧，迟早会带来性能上的问题。

在处理大量的行数据时，当然处理大量数据会消耗客户端一些时间，这时我们不妨考虑使用SQLBUFFERRESULT 。这样的话， MySQL 会将结果存储在临时表里，从而很快释放相关的表锁定。

SQLBIGRESULT 技巧会告诉 MySQL ：它马上将要处理大量的行数据。当 MySQL 看到这个信息的话，它将主动使用基于磁盘的临时表。这样做的不利之处在于不能建立基于排序为目的临时表索引。

5.4.4 Query Cache
使用 SQLCACHE 时，你要求 MySQL 缓冲查询的结果。如果 querycachetype 设为1，这个技巧就会失效，因为所有的 SELECT 查询默认被缓冲。如果 querycache_type 设为2，缓冲被启用，但是查询只有有要求时才会被缓冲。

相反，SQLNOCACHE 要求 MySQL 不要缓冲查询的结果。因为这是个 opt-out 要求，所以这个技巧在 querycachetype 设为1或者2的情况下都可以用。

5.5 Stupid Query Tricks
这一节会介绍一些提高查询性能的通用技巧。

5.5.1 Two Is Better Than One
有时候将查询拆分为两步将会大大提供性能。

5.5.2 Unions Instead of ORs