MySQL查询优化 2013-5-27

         今天主要学习了下查询优化的相关东西。MySQL的查询优化程序要充分利用索引，但是它也同时使用其他的信息。MySQL进行分析，看是否能够对它进行优化，使它执行更快。

比如说：SELECT * FROM tbl_name  WHERE FALSE;这个执行结果非常快，因为它根本就没有再去搜索数据表。

            mysql> explain select * from member where false;           

            +----+-------------+-------+------+---------------+------+---------+------+------+------------------+           

            | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra |

            +----+-------------+-------+------+---------------+------+---------+------+------+------------------+

            | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL| Impossible WHERE |

            +----+-------------+-------+------+---------------+------+---------+------+------+------------------+

            1 row in set (0.00 sec)

            对上面的输出项进行说明:      id                 : SELECT识别符。这是SELECT的查询序列号。

                                                   select_type  :SELECT类型(类型详见《MySQL参考指南》优化章节)

                                                   table           ：输出的行所引用的表

                                                   type            ：联接类型(类型详见《MySQL参考指南》优化章节)

                                                   possible_keys：possible_keys列指出MySQL能使用哪个索引在该表中找到行。  

                                                    key             ：  key列显示MySQL实际决定使用的键（索引）。

                                                    key_len       ：key_len列显示MySQL决定使用的键长度。

                                                    ref             ：ref列显示使用哪个列或常数与key一起从表中选择行。                                                  

                                                    rows           ：rows列显示MySQL认为它执行查询时必须检查的行数。

                                                    Extra          ：该列包含MySQL解决查询的详细信息。

               查询优化程序主要的目的是只要可能就要使用索引。想要帮助优化器充分利用索引主要有：

                       1.对数据表进行分析

                       2.使用EXPLAIN语句来验证优化器操作。Explain语句可以告诉你某给定查询有没有使用索引。当你在尝试用不同的方法来编写同一条语句，或者你想知道增加某个索引能否改善查询命名的执行效率。

                       3.向优化器提供提示或者在必要时常闭。在联接操作中，你可以在数据列表中的某个数据表名字的后面利用force index、use index| 或者是ignore index限定词告诉服务器你想使用哪些索引。

                          原则是：安排数据表的顺序是为了让限制性最强的选取操作最先执行。

                       4.劲量使用数据类型相同的数据列进行比较。在对比带有索引的数据列进行比较时，如果它们的数据类型相同，查询效率可能会高一点。

                       5.使带有索引的数据列在比较表达式中单独出现。

                          例： where a*2<4 对比 where a<4/2

                          说明：a列上有索引。这两个表达式在效果是一样的，但是从优化角度看是不同的。表达式1中的要乘以2后与4比较，即：每个带所以的数据行都要被检索以计算出结果。

                          这样索引是不能被使用的。于表达式 2就是a 列值与2进行比较，使用索引，很快能找到所有小于2的数值。

                     6.在LIKE 模式的起始处不要使用通配符。

                     7.利用优化器的长处，MySQL对于子查询支持是从MySQL4.1版开始才增加功能。所以一般来说，优化器对联结的优化效果要比对子查询的优化效果更好一些。

                     8.试验各种查询的变化格式，而且要多次运行它们。 假如你对一个查询的两种方式都只运行一次，你会发现第二种查询方式要快一些。这主要是因为第一次执行

                       的结果信息任然保留在磁盘的高速缓存内，不需要从磁盘读取。

                    9.避免过多的数据类型的自动转换。 如果在某列上有索引，对数据类型进行自动转化的时候后可能会阻止索引得到使用。

         为了提高查询效率而挑选数据类型有几点（可能很多需要在实践工作中才能体会到）：      

                    1.尽量使用树值操作，少使用字符串操作。数值操作也需要一次比较久可以完成，而字符串的操作需要一般需要进行多次字节与字节或字符与字符的比较才能完成，而且字符越长，

                      比较次数就越多。（enum，set是在MySQL中是使用数值的形式保存）

                    2.如果小类型够用，不要使用大类型 。选用“小”类型的另一个好处是可以让整个数据表变得更小，从而减少在磁盘读写方面的开销。

                    3.如果你能选择数据行的存储格式，就应该尽量选择最适合你的存储引擎的格式。

                      其实选择varchar或者是char 是你在时间和空间上做出最优选择。对于一些比较关键的应用，你需要多进行测试进行选择。目前还没有一种固定长度的数据行长度超过255个字符。

                      Memory数据表目前使用固定长度的格式来存储数据行，所以选用char 还是varchar都无关，他们都被当做char类型来对待。

                   4.尽量把数据列声明为not null

                   5.考虑使用enum数据列。如果字符串数据列的不同值的个数是有限的。在上面也提到了，在MySQL内部是把enum类型看做字符型处理。

              　　6.利用procedure analyse（）语句。可以来分析数据表，看它会对数据列的声明提出哪些意见。

              　　7.对容易产生碎片的数据表进行整理。定期使用optiomiz table语句有助于防止数据表查询性能的降低。可以用来清理MyISAM数据表里的碎片。

              　　8.把数据压缩在blob或者text数据列里面。这个方便很适合很难用标准语言便是的数据或者是那些随时间变化的数据。

              　　9.把blob或者是text数据列剥离到单独的一个数据表里面。        

         下面说说加载数据：1.加载数据时要采用批量加载，尽量减少MySQL对索引的刷新率，例如：LOAD DATA 语句要比INSERT 语句效率高，如果必须使用INSERT 语句，

                                  请尽量使他们集中在一起，减少对索引的刷新次数。对于支持事务处理机制的数据表类型，应该把这些INSERT 语句放在同一个事务里，对于不支持

                                  事务处理机制的数据表类型，应该现对数据表进行写锁定，然后在数据表锁定期间发出这些INSERT语句。对于大量数据，可以先加载数据在建立索引。

                                  2.多条insert语句，越多越好，效率越高。因为在插入数据时，对索引的刷新少得多。对于MyISAM数据表，减少索引的刷新次数的另一个策略是使用

                                    delay_key_write。数据表选项。数据行是同样写入，但是键缓存将只在必要时才刷新而不是每一次插入一个新索引后就立刻刷新一次。

         接下来是查询缓存，查询缓存可以加快重复执行的select语句的过程，主要有几个特点：

　　　　　　　1.一个给定的select语句第一次执行时，服务器记下了它查询的文本和返回的结果。

　　　　　　　2.服务器下一次看到这个查询时就不会再执行它了，而是直接从查询缓存中将查询结果取出来并返回给客户程序。

　　　　　　　3.查询缓存以服务器接受的那些查询字符串的文字文本为基础。

              　　4.如果某个查询命令返回的结果不确定，那么这个查询就不会被返回。

 　　　　　　  5.当一个数据表被更新时，所有的查询缓存就会全部失效。

         使用show variables like 'have_query_cache';查看开启查询缓存，查询缓存操作会受3个系统环境变量：

      　　　　　Query_cache_limit 查询缓存能够缓存的最大结果集的大小。比这个值大的查询结果不能被缓存。

      　　　　　Query_cache_type 决定查询缓存的操作方式。

      　　　　　Query_cache_size 查询缓存空间的大小。

                     可以使用set query_cache_type =val 来改变查询默认的缓存方式。但是对于那些不断变化的数据表中检索信息的查询来说，禁止缓存是比较有用的。

        最后就是硬件方面的优化：1.在机器安装更多的内存

                                          2.添加更快的磁盘来改善I/O等待时间。

                                         3.在物理设备之间分散磁盘读写活动，提高并行度

                                         4.使用多处理器。