数据库优化之：MySQL查询优化

http://zj86.info/?post=133

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

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

        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是用处理数值型的方法来处理这种字符串的，速度比处理字符串要快得多。

10、 对于那些声明为可变长度的数据列(如varchar型)，时间长了会使得数据表出现很多碎片，减慢查询效率，我们可以用OPTIMIZE TABLE语句对之进行整理。

11、 将数据压缩到BLOB数据列里，就能只通过一次性查询找到数据，或者把BLOB数据列单独存入一个文件。

12、 有时我们可以人为的为一个数据表建立索引，然后查询这个索引达到快速查询的目的。例如：我们先给一个数据表再建一个数据列，根据数据表里其他数据列计算出一个散列值并保存到刚建的数据列里，这样就可以通过搜索散列值的方法去检索想要找到数据行。

        MySQL查询优化程序是尽可能的使用索引，而且是尽可能地使用那些最为挑剔的索引，以便尽可能多和尽可能快的排除那些不符合查询条件的数据行。可以依照以下原则帮助优化程序：

1. 尽量对同类型的数据列进行比较，必要时可以先用ALTER TABLE语句把其中之一的类型修改为与另一个数据列相同。

2. 尽量让已经索引的数据列在比较表达式中单独出现。

3. 尽量减轻模糊匹配查询，例如：不要在LIKE模式的开头使用通配符等。

4. 多试几种查询命令。

5. 不要滥用MySQL的类型自动转换功能，如：数字3就不能写成字符‘3’，加重MySQL的类型转换，会使它的性能下降。

        下面说说加载数据。

        加载数据时要采用批量加载，尽量减少MySQL对索引的刷新率，例如：LOAD DATA 语句要比INSERT 语句效率高，如果必须使用INSERT 语句，请尽量使他们集中在一起，减少对索引的刷新次数。对于支持事务处理机制的数据表类型，应该把这些INSERT 语句放在同一个事务里，对于不支持事务处理机制的数据表类型，应该现对数据表进行写锁定，然后在数据表锁定期间发出这些INSERT语句。对于大量数据，可以先加载数据在建立索引。

        在机器硬件方面：

1. 应为机器安装更多内存。

2. 购置高速磁盘以缩短I/O等待时间。

3. 使用多个物理硬盘，提高并行操作能力。

4. 使用多个处理器。

在一个具体的数据库应用系统中，要根据实际需要来采用各种优化策略，以便使顾客与公司达到双赢的目的。