MySQL查询优化

在实际工作中，无论是对数据库系统(DBMS)，还是对数据库应用系统(DBAS)，查询优化一直是一个热门话题。一个成功的数据库应用系统的开发，肯定会在查询优化上付出很多心血。对查询优化的处理，不仅会影响到数据库工作效率，而且会给公司带来实实在在的效益问题。MySQL是一个开源软件，但它的性能丝毫不逊于商业数据库，它的速度已经相当快。但要想做好查询优化，还要考虑一些技巧上的东西。下面是我学习和使用MySQL以来的一些对优化查询的感受。

首先，我们应该考虑哪些因素能影响到查询优化?可以分为：机器硬件、索引、系统参数、查询技巧等。对于一个查询优化问题，我们应该首先考虑哪各方面呢?索引的使用应该再考虑的首条，毋庸置疑的是，在没有使用索引的前提下，想用其它招数来大幅度改善性能的做法往往收获甚微，纯粹是在浪费时间，可事实无绝对，有些场合反而需要去抑制优化程序的行为。

我们先来说说索引。索引能提高查询效率的原因之一是它可以让我们知道最后一个符合条件的数据行出现在什么位置，此后的数据行都用不着在检查了，另一个原因是人们已经发明了很多中定位算法来迅速查出第一个符合条件的数据行出现在什么位置，用不着从索引的开头通过线性扫描法去定位一个匹配项。

MySQL在建立索引时，对不同的数据表有不同的细节：MyISAM数据表，数据行将被保存在数据文件里，其索引值将被保存在索引文件里，而BDB处理程序把同一个BDB数据表的数据值和索引值保存在同一个文件里，InnoDB处理程序则是把所有InnoDB数据表的数据值和索引值都保存在同一个表空间里。这些看似与建立索引无关，其实是很重要的，待会我后说道。还有，索引不仅能给单据表带来好处，然而，索引给涉及多个数据表的关联查询带来的好处就更打了。

建立索引也要考虑它的缺点。首先，索引需要消耗磁盘空间(现在磁盘已经很便宜了)，索引越多，消耗的空间也就有多。对于MyISAM数据表，过多的索引会使索引文件优先与数据文件达到尺寸上限。对于BDB数据表，因为它把数据值和索引值都保存在同一个文件里，所以增加索引必然会使他更快地达到BDB数据文件的尺寸上限。InnoDB数据表共同分享着InnoDB表空间里的存储空间，所以增加索引必然会加快InnoDB表空间的消耗速度。不过，只要还能增加磁盘空间，就能通过给InnoDB表空间增加组件的办法扩充之。

其次，索引过多，会减慢被索引的数据列上的插入、删除和修改操作的速度。因为在写入记录时，MySQL还必须修改与之有关的所有索引。

下面我们考虑应该选择什么样的数据列来创建索引?怎样才能创建出最使用的索引?怎样处理数据类型给索引带来的影响?原则是：

1、 引应该创建在搜索、排序、归组等操作所涉及的数据列上，只在输出报告里出现的数据列不是好的候选。

2、 尽量使用唯一化索引。因为如果数据列里有很多彼此重复的值，建立在其上的索引就不会有好的效果。

3、 尽量对比较短的值进行索引。

4、 对于复合索引，MySQL会先匹配它的第一个索引列，如果匹配不上它就不会使用这个索引了。

5、 不要建立过多的索引。

6、 考虑将在这个数据列上进行怎样的比较操作。对于HEAP数据表来说，它只会用“=”来比较，其他比较操作就帮不上忙了。

7、 利用慢查询日志来找出那些性能低劣的查询。不过不要认为这是绝对的，因为MySQL会将凡是没使用索引的查询一并写入这个日志里，要有选择的查看这个日志哦。

8、 要尽量把数据列声明为NOT NULL，这样可以排除在对空值的查询，在有些时候，能起到很大的作用。

9、 对于字符串数据列，如果其取值是有限的，应优先考虑使用ENUM类型，因为MySQL是用处理数值型的方法来处理这种字符串的，速度比处理字符串要快得多。