- 浏览: 380036 次
- 性别:
- 来自: 上海
文章分类
- 全部博客 (215)
- ubuntu (27)
- 虚拟机 (13)
- 数据库 (29)
- JAVA (40)
- 搜索 (23)
- 开发工具 (2)
- 产品 (2)
- 工具 (1)
- 应用服务器 (5)
- linux (17)
- log (1)
- 多线程 (3)
- 异常 (1)
- shell (6)
- 12306 ;互联网 (1)
- 持续集成&Hudson (4)
- js (1)
- JAVA OR-Map (1)
- 漏洞 (1)
- svn (1)
- MAVEN (3)
- 架构 (1)
- windows (1)
- json (1)
- web (3)
- jenkins (2)
- iptables (2)
- JAVA ssh (0)
- 项目管理 (1)
- tomcat (1)
- 安全 (1)
- 数据库 mysql (1)
- 性能 (1)
最新评论
-
sbwfgihc:
怎么用的啊,
<转>mysql 树查询语句 -
panghaoyu:
实现一个智能提示功能需要ajax、数据库、jsp/php、算法 ...
<转>Solr Suggest实现搜索智能提示 -
songsove:
请问您qq多少
solr 对拼音搜索和拼音首字母搜索的支持 -
panghaoyu:
实现一个智能提示功能需要ajax、数据库、jsp/php、算法 ...
<转>Solr Suggest实现搜索智能提示 -
norain1988:
这样就可以实现相关搜索了吗
solr 百度相关搜索类似功能的实现
原链接:http://www.bitscn.com/pdb/mysql/200710/116880.html
在优化查询中,数据库应用(如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)即意味着对工具的操作与使用。使用索引、使用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--表分区、查看分区(转)
2014-12-15 21:50 690原文地址:http://blog.csdn ... -
<转>Mysql 慢查询和慢查询日志分析
2014-08-30 15:09 1170原链接:http://www.cnblogs.com/wrmf ... -
<转>MySQL锁定状态查看命令
2014-08-30 14:45 737原链接:http://blog.csdn.net/dc_726 ... -
<转>MySQL 事务没有提交导致 锁等待 Lock wait timeout exceeded
2014-08-30 14:39 965原链接:http://my.oschina.net/jiaoy ... -
mysql函数
2014-05-27 11:20 526select curdate(); ... -
<转>从mysql到内存数据库的一些碎碎念
2014-04-24 15:30 1087原链接:http://blog.csdn.net/ty1921 ... -
修改mysql自增主键的初始值
2014-04-15 17:45 1021默认情况下ID是从1开始递增,如果要修改递增初始值,可在Mys ... -
<转> MySQL的经典用法----临时表与内存表
2014-04-09 15:47 807原链接:http://blog.csdn.net/feihon ... -
<转>Oracle中的软解析和硬解析
2014-04-02 14:10 906原链接:http://soft.chinabyte.com/d ... -
<转>mybatis防止sql注入
2014-03-28 10:19 848原链接:http://cs-css.iteye.com/blo ... -
mysql变相实现split
2014-03-10 18:01 792--按split取第2个字符分 ... -
mysql按拼音排序
2013-11-08 14:59 737select * from ac_operator order ... -
<转>深入mysql慢查询设置的详解
2013-10-12 10:22 617原链接:http://www.jb51.net/article ... -
<转>mysql对于大表(千万级),要怎么优化呢?
2013-10-11 18:01 702原链接:http://www.zhihu.com/questi ... -
<转>详解MySQL中EXPLAIN解释命令
2013-10-11 16:56 718原链接:http://database.51c ... -
<转>mysql导入导出包括函数或者存储过程
2013-09-23 14:33 926mysql导入导出包括函数或者存储过程 1.mysql导出 ... -
mybatis技巧
2013-09-11 17:12 1234集合和in的表示 <delete id="d ... -
<转>MySQL中进行树状所有子节点的查询(写函数)
2013-09-11 09:38 856原链接:http://blog.csdn.net/acmain ... -
<转>mysql 树查询语句
2013-09-10 15:38 6502原链接:http://skudy.iteye.com/blog ... -
关于Oracle中split函数的实现以及函数递归的举例
2013-07-06 17:05 951比如说把一个字符串A,B,C,D,E ...
相关推荐
优化MySQL服务器<br>7.5.1. 系统因素和启动参数的调节<br>7.5.2. 调节服务器参数<br>7.5.3. 控制查询优化器的性能<br>7.5.4. 编译和链接怎样影响MySQL的速度<br>7.5.5. MySQL如何使用内存<br>7.5.6. MySQL如何使用...
数据库的备份与恢复 114<br>5.1 数据库目录 115<br>5.1.1 数据目录的位置 115<br>5.1.2 数据库的表示法 116<br>5.1.3 数据库表的表示法 117<br>5.1.4 MySQL的状态文件 118<br>5.1.5 总结 120<br>5.2 重定位数据库...
数据库的备份与恢复 114<br>5.1 数据库目录 115<br>5.1.1 数据目录的位置 115<br>5.1.2 数据库的表示法 116<br>5.1.3 数据库表的表示法 117<br>5.1.4 MySQL的状态文件 118<br>5.1.5 总结 120<br>5.2 重定位数据库...
第5课 查询优化技术理论与MySQL实践(三)------视图重写与等价谓词重写 什么是视图重写?哪些类型的视图可以被优化?MySQL是怎么优化视图的?从而明白在MySQL中怎么写与视图相关的查询语句才能有好的效果? 什么是...
<br> <br>操作系统:Windows XP <br> <br> <br>服务器:APACHE1.3.31 <br>PHP版本:4.3.8 <br>Zend Optimizer:2.5.3 <br>数据库:MYSQL 4.0.20 <br> <br>二、安装说明: <br> <br>将siyu.rar解压后放在您网站的根...
NT2.5新增六大创新:<br>创新一:支持最新Windows Server2008、IIS7、Silverlight 2.0 beta2 <br><br>创新二:新增商品交易插件,社区系统自带应用插件化<br><br>创新三:优化用户在线处理,提高负载能力,减少等待...
创建测试数据库,并在数据库中创建一个t_user 表,其中包含三个字段: <br>. id(int) <br>. name(varchar) <br>. sex(int) 。<br>3. 为了在开发过程更加直观,我们需要将ibatis 日志打开以便观察ibatis 运作的细节...
使在mysql5.0.xx 环境正常安装使用<br>1) 解决索引过长问题,把4个 acl 表的字段 section_value 和 value 统一为 varchar(100),字段 name 统一为 varchar(255)<br>2) 优化表,清除没用的或重复的索引<br>3) 改进...
内 容 提 要<br>本书详细介绍了如何安装、管理、备份、维护和优化一个MySQL系统。对于每一件服务器操作都提出了多种的解决方案。对于每一种的方法,虽然不一定都是非常实用的方法,读者能通过这些方法,熟悉MySQL的...
MySQL是一个可用于各种流行操作系统平台的关系数据库系统,它具有客户机/服务器体系结构的分布式数据库管理系统。<br> 本书分为三部分,分别介绍了MySQL的基础知识、配置和管理。...本书也适合MySQL数据库管理员使用。
提高MySQL 查询效率的三个技巧小结MySQL由于它本身的小巧和操作的高效, 在数据库应用中越来越多的被采用.我在开发一个P2P应用的时候曾经使用MySQL来保存P2P节点,由于P2P的应用中,结点数动辄上万个,而且节点变化...
<项目介绍> 实验一:MySQL关系数据库管理系统及SQL语言的使用 实验二:使用高级语言操作MySQL数据库 实验三:数据库系统开发 实验四:查询处理算法的实现 实验五:查询优化算法的实现 - 不懂运行,下载完可以私聊问...
通过规范化数据库结构,可以提高数据一致性、减少冗余数据,并优化数据存储和查询效率。以下是一些规范化的基本原则: 此外,还可以使用索引和外键来加强数据库结构的完整性和性能。索引可以加速数据检索,而外键...
第一:mysql服务的启动和停止 ...如,增加一个用户user1密码为password1,让其可以在本机上登录, 并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入mysql,然后键入以下命令:
30-增加MySQL数据库第三个实例实战讲解.avi 31-MySQL数据库多实例的其他故障问题说明.avi 第三部:MySQL应用管理及进阶实战操作(29节) 01-MySQL启动与关闭深入知识及原理.avi 02-登录MySQL知识深入讲解.avi 03-...
本书通过两个实例,详细介绍了MySQL的基本概念、基本技巧、编程方法、管理特点,以及第三方工具(如PHP和Perl)的使用方法。本书还提供了大量与MySQL有关的因特网站点,以及获得MySQL的技术支持方法。 本书适合...
在缺省情况下,MySQL支持三个引擎:ISAM、MyISAM和HEAP。另外两种类型InnoDB和Berkley(BDB),也常常可以使用。可以肯定的是,MyISAM的确快,但是如果你的逻辑设计需要事务处理,你就可以自 由使用支持事务处理的...
本书通过两个实例,详细介绍了MySQL的基本概念、基本技巧、编程方法、管理特点,以及第三方工具(如PHP和Perl)的使用方法。 本书还提供了大量与MySQL有关的因特网站点,以及获得MySQL的技术支持方法。 本书适合...
以下是对MySQL数据库的详细介绍: 核心特性与优势 开源与跨平台 MySQL遵循GPL开源协议,这意味着任何人都可以免费下载、使用和修改其源代码。这种开放性促进了广泛的社区支持和第三方插件、工具的发展。此外,MySQL...