Mysql 查询优化笔记

项目开发初期，数据库数据比较单纯。许多开发人员，只要功能上实现了，就以为大吉。到了大量数据测试时候，才发现太多的致命查询，足以让你崩溃。收集了一些查询优化方面的资料笔记如下：
索引的使用

索引是提高查询速度的最重要工具，没有使用索引的情况下试图使用其他方法提高性能都是在浪费时间。

BENCHMARK(count,expr)
BENCHMARK()函数重复countTimes次执行表达式expr，它可以用于计时MySQL处理表达式有多快。结果值总是0。意欲用于mysql客户，它报告查询的执行时间。

mysql> SELECT BENCHMARK(1000000,1+1)；

报告的时间是客户端的经过时间，不是在服务器端的CPU时间。执行BENCHMARK()若干次可能是明智的，并且注意服务器机器的负载有多重来解释结果。

EXPLAIN语法（获取SELECT相关信息）




explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

EXPLAIN列的解释：
table 显示这一行的数据是关于哪张表的

type  这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL

possible_keys 显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len 使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref 显示索引的哪一列被使用了，如果可能的话，是一个常数

rows MYSQL认为必须检查的用来返回请求数据的行数

Extra 关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

extra 列返回的描述的意义
Distinct 一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

Not exists MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
Range checked for each

Record（index map:#）没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

Using filesort 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

Using index 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

used where  使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题

常看表的信息

EXPLAIN tbl_name
DESCRIBE tbl_name
SHOW COLUMNS FROM tbl_name

总的来说，要想使一个较慢速SELECT ... WHERE更快，应首先检查是否能增加一个索引。

优化器是如何工作的

　　MySQL查询优化器有几个目标，但是其中最主要的目标是尽可能地使用索引，并且使用最严格的索引来消除尽可能多的数据行。你的最终目标是提交SELECT语句查找数据行，而不是排除数据行。优化器试图排除数据行的原因在于它排除数据行的速度越快，那么找到与条件匹配的数据行也就越快。如果能够首先进行最严格的测试，查询就可以执行地更快。假设你的查询检验了两个数据列，每个列上都有索引：

SELECT col3 FROM mytable
WHERE col1 = ’some value’ AND col2 = ’some other value’;

　　假设col1上的测试匹配了900个数据行，col2上的测试匹配了300个数据行，而同时进行的测试只得到了30个数据行。先测试Col1会有900 个数据行，需要检查它们找到其中的30个与col2中的值匹配记录，其中就有870次是失败了。先测试col2会有300个数据行，需要检查它们找到其中的30个与col1中的值匹配的记录，只有270次是失败的，因此需要的计算和磁盘I/O更少。其结果是，优化器会先测试col2，因为这样做开销更小。

　　你可以通过下面一个指导帮助优化器更好地利用索引：

　　尽量比较数据类型相同的数据列。当你在比较操作中使用索引数据列的时候，请使用数据类型相同的列。相同的数据类型比不同类型的性能要高一些。

　　尽可能地让索引列在比较表达式中独立。如果你在函数调用或者更复杂的算术表达式条件中使用了某个数据列，MySQL就不会使用索引，因为它必须计算出每个数据行的表达式值。

　　下面的WHERE子句显示了这种情况。它们的功能相同，但是对于优化目标来说就有很大差异了：

WHERE mycol < 4 / 2
WHERE mycol * 2 < 4

　　对于第一行，优化器把表达式4/2简化为2，接着使用mycol上的索引来快速地查找小于2的值。对于第二个表达式，MySQL必须检索出每个数据行的 mycol值，乘以2，接着把结果与4进行比较。在这种情况下，不会使用索引。数据列中的每个值都必须被检索到，这样才能计算出比较表达式左边的值。

　　我们看另外一个例子。假设你对date_col列进行了索引。如果你提交一条如下所示的查询，就不会使用这个索引：

SELECT * FROM mytbl WHERE YEAR(date_col) < 1990;

　　这个表达式不会把1990与索引列进行比较；它会把1990与该数据列计算出来的值比较，而每个数据行都必须计算出这个值。其结果是，没有使用 date_col上的索引，因为执行这样的查询需要全表扫描。怎么解决这个问题呢？只需要使用文本日期，接着就可以使用date_col上的索引来查找列中匹配的值了：

WHERE date_col < ’1990-01-01’

　　但是，假设你没有特定的日期。你可能希望找到一些与今天相隔固定的几天的日期的记录。表达这种类型的比较有很多种方法--它们的效率并不同。下面就有三种：

WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < cutoff
WHERE TO_DAYS(date_col) < cutoff + TO_DAYS(CURDATE())
WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY)

　　对于第一行，不会用到索引，因为每个数据行都必须检索以计算出TO_DAYS(date_col)的值。第二行要好一些。Cutoff和 TO_DAYS(CURDATE())都是常量，因此在处理查询之前，比较表达式的右边可以被优化器一次性计算出来，而不需要每个数据行都计算一次。但是 date_col列仍然出现在函数调用中，它阻止了索引的使用。第三行是这几个中最好的。同样，在执行查询之前，比较表达式的右边可以作为常量一次性计算出来，但是现在它的值是一个日期。这个值可以直接与date_col值进行比较，再也不需要转换成天数了。在这种情况下，会使用索引。

　　在LIKE模式的开头不要使用通配符。有些字符串搜索使用如下所示的WHERE子句：

WHERE col_name LIKE ’%string%’

　　如果你希望找到那些出现在数据列的任何位置的字符串，这个语句就是对的。但是不要因为习惯而简单地把"%"放在字符串的两边。如果你在查找出现在数据列开头的字符串，就删掉前面的"%"。假设你要查找那些类似MacGregor或MacDougall等以"Mac"开头的名字。在这种情况下，WHERE子句如下所示：

WHERE last_name LIKE ’Mac%’

　　优化器查看该模式中词首的文本，并使用索引找到那些与下面的表达式匹配的数据行。下面的表达式是使用last_name索引的另一种形式：

WHERE last_name >= ’Mac’ AND last_name < ’Mad’

　　这种优化不能应用于使用了REGEXP操作符的模式匹配。REGEXP表达式永远不会被优化。

    以上是网上摘录的，不可能每项都在实践中遇到，以后慢慢修改。
    基础知识：
    http://gsvince.iteye.com/blog/173542