`
smartzxy
  • 浏览: 200449 次
  • 性别: Icon_minigender_1
  • 来自: 苏州
社区版块
存档分类
最新评论

三方法优化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查询的三种工具的使用方法,以此提高你的应用程序性能。使用它们快乐地优化吧!

分享到:
评论

相关推荐

    sql查询优化(提高MySQL数据库查询效率的几个技巧)

    SQL 查询优化(提高 MySQL 数据库查询效率的几个技巧) 在 MySQL 数据库中,查询优化是一个非常重要的方面。在实际应用中,高效的查询可以提高整个系统的性能和响应速度。下面我们将介绍几个提高 MySQL 数据库查询...

    优化MySQL数据库查询的三种方法简介

    然而,随着业务的不断扩展和数据量的持续增加,如何优化MySQL数据库的查询性能成为了一个不得不面对的问题。本文将探讨三种行之有效的MySQL查询优化方法:合理使用索引、通过EXPLAIN分析查询以及调整MySQL内部配置。...

    MySQL数据库查询优化

    第5课 查询优化技术理论与MySQL实践(三)------视图重写与等价谓词重写 什么是视图重写?哪些类型的视图可以被优化?MySQL是怎么优化视图的?从而明白在MySQL中怎么写与视图相关的查询语句才能有好的效果? 什么是...

    基于MySQL数据库的查询性能优化研究.pdf

    基于 MySQL 数据库的查询性能优化研究 MySQL 数据库是当前网络环境中最流行的开源关系型数据库之一,随着网络环境中数据总量的急剧攀升,对数据库查询的响应时间提出了更严格的要求。为了提高查询效率,需要研究 ...

    基于MySQL的数据库查询性能优化.pdf

    "基于MySQL的数据库查询性能优化" 在当前的Web应用开发中,数据库的查询操作已经成为整个应用的性能瓶颈, especialmente 对于使用 MySQL 的 Web 应用。因此,数据库查询性能的优化已经成为一个非常重要的课题。...

    深入浅出MySQL数据库开发、优化与管理维护.doc

    本文档旨在深入浅出 MySQL 数据库开发、优化与管理维护的知识点,涵盖了 MySQL 数据库开发的基本概念、优化技巧和管理维护方法。 数据库开发篇 在本篇中,我们将学习 MySQL 数据库开发的基础知识,包括数据类型、...

    MySQL数据库查询优化方案.docx

    MySQL 数据库查询优化方案 MySQL 数据库查询优化是指对数据库中的查询语句进行优化,以提高查询效率和数据库性能。以下是 MySQL 数据库查询优化的一些重要知识点: 一、索引相关 索引是 MySQL 数据库查询优化的...

    MySQL数据库优化

    MySQL数据库优化MySQL数据库优化MySQL数据库优化MySQL数据库优化

    优化MySQL数据库性能的八大妙手.rar

    本文将详细介绍优化MySQL数据库性能的八大妙手,帮助你提升数据库效率,减少延迟,提高用户体验。 一、索引优化 索引是提高查询速度的关键。正确地创建和使用索引可以显著加快数据检索。应为经常用于搜索和排序的列...

    MySQL数据库原理及设计方法.pdf

    总的来说,MySQL数据库原理及设计方法涉及到网络通信、查询优化、并发控制、事务处理等多个层面,理解这些知识对于有效地使用和管理MySQL数据库至关重要。通过合理的设计和配置,可以确保MySQL在高并发环境下提供...

    实验1 MySQL数据库服务器配置.docx

    * 二进制日志、慢查询日志和通用查询日志的作用:记录MySQL服务器的操作信息,用于分析和优化MySQL服务器的性能。 * 启用日志的方法:在初始化配置文件中添加相应的配置信息,启用日志。 实验1-7:查看二进制日志、...

    mysql数据库优化(全)

    查询优化是提升MySQL数据库性能的关键环节之一。在数据库系统中,查询操作是最常见的用户交互形式。一个查询从客户端发起,到达数据库服务器,经过处理后返回结果给客户端。尽管查询过程看似简单,但不同查询方式与...

    MySQL数据库优化SQL篇PPT课件.pptx

    MySQL数据库优化SQL篇PPT课件.pptx ...本PPT课件对MySQL数据库优化的重要知识点进行了详细的讲解,并提供了许多实用的优化技巧和方法。通过学习本PPT课件,可以提高数据库的性能和效率,提高开发效率和质量。

    MySQL数据库性能优化研究.docx

    本文将探讨 MySQL 数据库性能优化的关键因素,包括硬件配置、数据库设计、查询优化等方面,以期提高 MySQL 数据库的性能和响应速度。 一、硬件配置优化 1. 内存:增加服务器内存可以显著提高 MySQL 性能。对于高...

    MySQL数据库目录查询

    除了使用上述命令外,还有其他方法可以查询 MySQL 数据库的目录信息。例如,可以使用 `SELECT @@datadir` 命令查询数据目录,或者使用 `SELECT @@log_error` 命令查询日志目录。 目录结构 MySQL 数据库的目录结构...

    国家开放大学 数据库运维 形考一 MySQL数据库服务器配置

    MySQL数据库服务器配置 MySQL数据库服务器配置是国家开放大学数据库运维 形考一的重要组成部分。通过这个实验,我们将掌握 MySQL 的安装方法,了解 MySQL 服务器的组成,掌握 MySQL 服务器的配置方法。 MySQL安装...

    mysql数据库优化的学习心得

    本文总结了一些 MySQL 数据库优化技巧,包括选择合适的字段属性、使用连接(JOIN)代替子查询、使用联合(UNION)代替手动创建的临时表、事务、锁定表、外键等。 一、选择合适的字段属性 在创建 MySQL 数据库时,...

    MySql数据库性能优化

    MySql数据库性能优化是指通过调整和优化数据库的各种参数、结构和查询语句,提高数据库的运行速度和效率,减少资源占用和系统瓶颈。下面将详细介绍MySql数据库性能优化的相关知识点。 什么是优化? 优化是指通过...

    mysql数据库性能优化

    ### MySQL数据库性能优化 #### 一、概览 在当今高度依赖互联网技术的世界里,数据库作为数据存储的核心组件,其性能直接影响着应用系统的响应速度和用户体验。MySQL作为一款广泛使用的开源关系型数据库管理系统,...

    mysql数据库优化方案(值得学习)

    简单描述数据库优化方案,以及数据库一些常用的操作,包括一些简单的查询语句,函数使用,合适学习mysql的读者。 简单描述数据库优化方案,以及数据库一些常用的操作,包括一些简单的查询语句,函数使用,合适学习...

Global site tag (gtag.js) - Google Analytics