三个办法优化MySQL数据库查询[MySQL防范]

　在优化查询中,数据库利用（如MySQL）即意味着对工具的操作与利用.利用索引、利用EXPLAIN解析查询以及调整MySQL的内部配置可到达优化查询的目的.

　任何一位数据库程序员城市有这样的领会:高通信量的数据库驱动程序中,一条糟糕的SQL查询语句可对整个利用程序的运行产生严重的影响,其不但损耗掉更多的数据库时间,且它将对其他利用组件产生影响.

　好像别的学科,优化查询性能很大程度上决意于开辟者的直觉.幸运的是,像MySQL这样的数据库自带有一些帮忙工具.本文扼要谈论诸多工具之三种:利用索引,利用EXPLAIN解析查询以及调整MySQL的内部配置.

#1: 利用索引

　MySQL答应对数据库表举行索引,以此能疾速查找记录,而无需一开始就扫描整个表,由此明显地加快查询速度.每个表最多可以做到16个索引,此外MySQL还支持多列索引及全文检索.

　给表增添一个索引非常简单,只需调用一个CREATE INDEX号令并为索引指定它的域便可.列表A给出了一个例子:

列表 A

mysql> CREATE INDEX idx_username ON users(username);
Query OK, 1 row affected (0.15 sec)
Records: 1  Duplicates: 0  Warnings: 0

　这里,对users表的username域做索引,以确保在WHERE大概HAVING子句中引用这一域的SELECT查询语句运行速度比没有增添索引时要快.通过SHOW INDEX号令可以查看索引已被成立（列表B）.

列表 B

mysql> SHOW INDEX FROM users;
--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users |          1 | idx_username |            1 | username    | A         |      NULL |     NULL | NULL   | YES  | BTREE      |         |
--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

　值得注意的是:索引就像一把双刃剑.对表的每一域做索引普通没有必要,且极大概招致运行速度减慢,因为向表中插入或改正数据时,MySQL不得不每次都为这些额外的工作重新成立索引.另一方面,避免对表的每一域做索引一样不是一个非常好的主张,因为在提高插入记录的速度时,招致查询操作的速度减慢.这就需求找到一个均衡点,比方在计划索引系统时,考虑表的主要功效（数据修复及编辑）不失为一种明智的挑选.

#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 (0.00 sec)这里查询是基于两个表衔接.EXPLAIN关键字描写了MySQL是若何处理衔接这两个表.必须清楚的是,当前计划要求MySQL处理的是country表中的一条记录以及city表中的整个4019条记录.这就意味着,还可以利用其他的优化本领改良其查询办法.比方,给city表增添以下索引（列表D）:

列表 D

mysql> CREATE INDEX idx_ccode ON city(countrycode);
Query OK, 4079 rows affected (0.15 sec)
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 (0.01 sec)

　在这个例子中,MySQL目前只需求扫描city表中的333条记录便可产生一个后果集,其扫描记录数几近削减了90％!自然,数据库资源的查询速度更快,效率更高.

#3: 调整内部变量

　MySQL是如此的开放,所以可轻松地进一步伐整其缺省设置以得到更优的性能及安定性.需求优化的一些关键变量以下:

• 改变索引缓冲区长度(key_buffer)

　普通,该变量掌握缓冲区的长度在处理索引表（读/写操作）时利用.MySQL利用手册指出该变量可以不断增添以确保索引表的最佳性能,并举荐利用与系统内存25％的大小作为该变量的值.这是MySQL非常重要的配置变量之一,假如你对优化和提高系统性能有爱好,可以从改变key_buffer_size变量的值开始.

• 改变表长(read_buffer_size)

　当一个查询不断地扫描某一个表,MySQL会为它分配一段内存缓冲区.read_buffer_size变量掌握这一缓冲区的大小.假如你认为持续扫描举行得太慢,可以通过增添该变量值以及内存缓冲区大小提高其性能.

• 设定翻开表的数目的最大值(table_cache)

　该变量掌握MySQL在任什么时刻候翻开表的最大数目,由此能掌握服务器呼应输入恳求的本领.它跟max_connections变量密切相关,增添table_cache值可以使MySQL翻开更多的表,就如增添max_connections值可增添衔接数一样.当收到大量差别数据库及表的恳求时,可以考虑改变这一值的大小.

• 对缓长查询设定一个时间限制(long_query_time)

　MySQL带有"慢查询日记",它会自动地记录全部的在一个特定的时间范围内还没有完毕的查询.这个日记关于跟踪那些低效率大概行为不端的查询以及探求优化对象都非常有效.long_query_time变量掌握这一最大时间限定,以秒为单位.

　以上谈论并给出用于解析和优化SQL查询的三种工具的利用办法,以此提高你的利用程序性能.利用它们欢愉地优化吧! 　　以上是“三个办法优化MySQL数据库查询[MySQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：