2015年计算机四级指导:优化MySQL数据库查询的三个方法
发布时间：2010/3/10 17:38:22 来源：城市学习网 编辑：MOON
在优化查询中，数据库应用不失为一种明智的选择。
[next]
#2: 优化查询性能
在分析查询性能时，考虑EXPLAIN关键字同样很管用。EXPLAIN关键字一般放在SELECT查询语句的前面，用于描述MySQL如何执行查询操作、以及MySQL成功返回结果集需要执行的行数。下面的一个简单例子可以说明（列表C）这一过程：
列表 C
mysql EXPLAIN SELECT city.name， city.district FROM city， country WHERE city.countrycode = country.code AND country.code = ‘IND‘;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
id select_type table type possible_keys key key_len ref rows Extra
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
1 SIMPLE country const PRIMARY PRIMARY 3 const 1 Using index
1 SIMPLE city ALL NULL NULL NULL NULL 4079 Using where
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set 这里查询是基于两个表连接。EXPLAIN关键字描述了MySQL是如何处理连接这两个表。必须清楚的是，当前设计要求MySQL处理的是country表中的一条记录以及city表中的整个4019条记录。这就意味着，还可使用其他的优化技巧改进其查询方法。例如，给city表添加如下索引（列表D）：
列表 D
mysql CREATE INDEX idx_ccode ON city;
Query OK， 4079 rows affected
Records: 4079 Duplicates: 0 Warnings: 0
现在，当我们重新使用EXPLAIN关键字进行查询时，我们可以看到一个显著的改进（列表E）：
列表 E
mysql EXPLAIN SELECT city.name， city.district FROM city， country WHERE city.countrycode = country.code AND country.code = ‘IND‘;
+----+-------------+---------+-------+---------------+-----------+---------+-------+------+-------------+
id select_type table type possible_keys key key_len ref rows Extra
+----+-------------+---------+-------+---------------+-----------+---------+-------+------+-------------+
1 SIMPLE country const PRIMARY PRIMARY 3 const 1 Using index
1 SIMPLE city ref idx_ccode idx_ccode 3 const 333 Using where
+----+-------------+---------+-------+---------------+-----------+---------+-------+------+-------------+
2 rows in set
在这个例子中，MySQL现在只需要扫描city表中的333条记录就可产生一个结果集，其扫描记录数几乎减少了90％！自然，数据库资源的查询速度更快，效率更高。
#3: 调整内部变量
MySQL是如此的开放，所以可轻松地进一步调整其缺省设置以获得更优的性能及稳定性。需要优化的一些关键变量如下：
改变索引缓冲区长度
一般，该变量控制缓冲区的长度在处理索引表（读/写操作）时使用。MySQL使用手册指出该变量可以不断增加以确保索引表的最佳性能，并推荐使用与系统内存25％的大小作为该变量的值。这是MySQL十分重要的配置变量之一，如果你对优化和提高系统性能有兴趣，可以从改变key_buffer_size变量的值开始。
改变表长
当一个查询不断地扫描某一个表，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果你认为连续扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。
设定打开表的数目的最大值
该变量控制MySQL在任何时候打开表的最大数目，由此能控制服务器响应输入请求的能力。它跟max_connections变量密切相关，增加table_cache值可使MySQL打开更多的表，就如增加max_connections值可增加连接数一样。当收到大量不同数据库及表的请求时，可以考虑改变这一值的大小。
对缓长查询设定一个时间限制
MySQL带有“慢查询日志”，它会自动地记录所有的在一个特定的时间范围内尚未结束的查询。这个日志对于跟踪那些低效率或者行为不端的查询以及寻找优化对象都非常有用。long_query_time变量控制这一最大时间限定，以秒为单位。 以上讨论并给出用于分析和优化SQL查询的三种工具的使用方法，以此提高你的应用程序性能。使用它们快乐地优化吧！
