- 浏览: 203375 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
coderlu:
嗯,在CentOS里试验成功。
Linux 防火墙端口设置 -
hiberatejss:
不错,挺方便的
MyEclipse 代码提示快捷键设置 -
xlyang0505:
只能用html吗,貌似我还是没搞定
Dojo 入门 -
yangpeihai:
...
Linux 防火墙端口设置 -
dingherry:
请问MSJAVA.dll该放在什么路径下?
java.lang.UnsatisfiedLinkError: Unable to load library
项目开发初期,数据库数据比较单纯。许多开发人员,只要功能上实现了,就以为大吉。到了大量数据测试时候,才发现太多的致命查询,足以让你崩溃。收集了一些查询优化方面的资料笔记如下:
索引的使用
索引是提高查询速度的最重要工具,没有使用索引的情况下试图使用其他方法提高性能都是在浪费时间。
BENCHMARK(count,expr)
BENCHMARK()函数重复countTimes次执行表达式expr,它可以用于计时MySQL处理表达式有多快。结果值总是0。意欲用于mysql客户,它报告查询的执行时间。
报告的时间是客户端的经过时间,不是在服务器端的CPU时间。执行BENCHMARK()若干次可能是明智的,并且注意服务器机器的负载有多重来解释结果。
EXPLAIN语法(获取SELECT相关信息)
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
常看表的信息
总的来说,要想使一个较慢速SELECT ... WHERE更快,应首先检查是否能增加一个索引。
优化器是如何工作的
MySQL查询优化器有几个目标,但是其中最主要的目标是尽可能地使用索引,并且使用最严格的索引来消除尽可能多的数据行。你的最终目标是提交SELECT语句查找数据行,而不是排除数据行。优化器试图排除数据行的原因在于它排除数据行的速度越快,那么找到与条件匹配的数据行也就越快。如果能够首先进行最严格的测试,查询就可以执行地更快。假设你的查询检验了两个数据列,每个列上都有索引:
假设col1上的测试匹配了900个数据行,col2上的测试匹配了300个数据行,而同时进行的测试只得到了30个数据行。先测试Col1会有900 个数据行,需要检查它们找到其中的30个与col2中的值匹配记录,其中就有870次是失败了。先测试col2会有300个数据行,需要检查它们找到其中的30个与col1中的值匹配的记录,只有270次是失败的,因此需要的计算和磁盘I/O更少。其结果是,优化器会先测试col2,因为这样做开销更小。
你可以通过下面一个指导帮助优化器更好地利用索引:
尽量比较数据类型相同的数据列。当你在比较操作中使用索引数据列的时候,请使用数据类型相同的列。相同的数据类型比不同类型的性能要高一些。
尽可能地让索引列在比较表达式中独立。如果你在函数调用或者更复杂的算术表达式条件中使用了某个数据列,MySQL就不会使用索引,因为它必须计算出每个数据行的表达式值。
下面的WHERE子句显示了这种情况。它们的功能相同,但是对于优化目标来说就有很大差异了:
对于第一行,优化器把表达式4/2简化为2,接着使用mycol上的索引来快速地查找小于2的值。对于第二个表达式,MySQL必须检索出每个数据行的 mycol值,乘以2,接着把结果与4进行比较。在这种情况下,不会使用索引。数据列中的每个值都必须被检索到,这样才能计算出比较表达式左边的值。
我们看另外一个例子。假设你对date_col列进行了索引。如果你提交一条如下所示的查询,就不会使用这个索引:
这个表达式不会把1990与索引列进行比较;它会把1990与该数据列计算出来的值比较,而每个数据行都必须计算出这个值。其结果是,没有使用 date_col上的索引,因为执行这样的查询需要全表扫描。怎么解决这个问题呢?只需要使用文本日期,接着就可以使用date_col上的索引来查找列中匹配的值了:
但是,假设你没有特定的日期。你可能希望找到一些与今天相隔固定的几天的日期的记录。表达这种类型的比较有很多种方法--它们的效率并不同。下面就有三种:
对于第一行,不会用到索引,因为每个数据行都必须检索以计算出TO_DAYS(date_col)的值。第二行要好一些。Cutoff和 TO_DAYS(CURDATE())都是常量,因此在处理查询之前,比较表达式的右边可以被优化器一次性计算出来,而不需要每个数据行都计算一次。但是 date_col列仍然出现在函数调用中,它阻止了索引的使用。第三行是这几个中最好的。同样,在执行查询之前,比较表达式的右边可以作为常量一次性计算出来,但是现在它的值是一个日期。这个值可以直接与date_col值进行比较,再也不需要转换成天数了。在这种情况下,会使用索引。
在LIKE模式的开头不要使用通配符。有些字符串搜索使用如下所示的WHERE子句:
如果你希望找到那些出现在数据列的任何位置的字符串,这个语句就是对的。但是不要因为习惯而简单地把"%"放在字符串的两边。如果你在查找出现在数据列开头的字符串,就删掉前面的"%"。假设你要查找那些类似MacGregor或MacDougall等以"Mac"开头的名字。在这种情况下,WHERE子句如下所示:
优化器查看该模式中词首的文本,并使用索引找到那些与下面的表达式匹配的数据行。下面的表达式是使用last_name索引的另一种形式:
这种优化不能应用于使用了REGEXP操作符的模式匹配。REGEXP表达式永远不会被优化。
以上是网上摘录的,不可能每项都在实践中遇到,以后慢慢修改。
基础知识:
http://gsvince.iteye.com/blog/173542
索引的使用
索引是提高查询速度的最重要工具,没有使用索引的情况下试图使用其他方法提高性能都是在浪费时间。
BENCHMARK(count,expr)
BENCHMARK()函数重复countTimes次执行表达式expr,它可以用于计时MySQL处理表达式有多快。结果值总是0。意欲用于mysql客户,它报告查询的执行时间。
mysql> SELECT BENCHMARK(1000000,1+1);
报告的时间是客户端的经过时间,不是在服务器端的CPU时间。执行BENCHMARK()若干次可能是明智的,并且注意服务器机器的负载有多重来解释结果。
EXPLAIN语法(获取SELECT相关信息)
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
EXPLAIN列的解释: table 显示这一行的数据是关于哪张表的 type 这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL possible_keys 显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句 key 实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引 key_len 使用的索引的长度。在不损失精确性的情况下,长度越短越好 ref 显示索引的哪一列被使用了,如果可能的话,是一个常数 rows MYSQL认为必须检查的用来返回请求数据的行数 Extra 关于MYSQL如何解析查询的额外信息。将在表4.3中讨论,但这里可以看到的坏的例子是Using temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢 extra 列返回的描述的意义 Distinct 一旦MYSQL找到了与行相联合匹配的行,就不再搜索了 Not exists MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了 Range checked for each Record(index map:#)没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一 Using filesort 看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行 Using index 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候 Using temporary 看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上 used where 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题
常看表的信息
EXPLAIN tbl_name DESCRIBE tbl_name SHOW COLUMNS FROM tbl_name
总的来说,要想使一个较慢速SELECT ... WHERE更快,应首先检查是否能增加一个索引。
优化器是如何工作的
MySQL查询优化器有几个目标,但是其中最主要的目标是尽可能地使用索引,并且使用最严格的索引来消除尽可能多的数据行。你的最终目标是提交SELECT语句查找数据行,而不是排除数据行。优化器试图排除数据行的原因在于它排除数据行的速度越快,那么找到与条件匹配的数据行也就越快。如果能够首先进行最严格的测试,查询就可以执行地更快。假设你的查询检验了两个数据列,每个列上都有索引:
SELECT col3 FROM mytable WHERE col1 = ’some value’ AND col2 = ’some other value’;
假设col1上的测试匹配了900个数据行,col2上的测试匹配了300个数据行,而同时进行的测试只得到了30个数据行。先测试Col1会有900 个数据行,需要检查它们找到其中的30个与col2中的值匹配记录,其中就有870次是失败了。先测试col2会有300个数据行,需要检查它们找到其中的30个与col1中的值匹配的记录,只有270次是失败的,因此需要的计算和磁盘I/O更少。其结果是,优化器会先测试col2,因为这样做开销更小。
你可以通过下面一个指导帮助优化器更好地利用索引:
尽量比较数据类型相同的数据列。当你在比较操作中使用索引数据列的时候,请使用数据类型相同的列。相同的数据类型比不同类型的性能要高一些。
尽可能地让索引列在比较表达式中独立。如果你在函数调用或者更复杂的算术表达式条件中使用了某个数据列,MySQL就不会使用索引,因为它必须计算出每个数据行的表达式值。
下面的WHERE子句显示了这种情况。它们的功能相同,但是对于优化目标来说就有很大差异了:
WHERE mycol < 4 / 2 WHERE mycol * 2 < 4
对于第一行,优化器把表达式4/2简化为2,接着使用mycol上的索引来快速地查找小于2的值。对于第二个表达式,MySQL必须检索出每个数据行的 mycol值,乘以2,接着把结果与4进行比较。在这种情况下,不会使用索引。数据列中的每个值都必须被检索到,这样才能计算出比较表达式左边的值。
我们看另外一个例子。假设你对date_col列进行了索引。如果你提交一条如下所示的查询,就不会使用这个索引:
SELECT * FROM mytbl WHERE YEAR(date_col) < 1990;
这个表达式不会把1990与索引列进行比较;它会把1990与该数据列计算出来的值比较,而每个数据行都必须计算出这个值。其结果是,没有使用 date_col上的索引,因为执行这样的查询需要全表扫描。怎么解决这个问题呢?只需要使用文本日期,接着就可以使用date_col上的索引来查找列中匹配的值了:
WHERE date_col < ’1990-01-01’
但是,假设你没有特定的日期。你可能希望找到一些与今天相隔固定的几天的日期的记录。表达这种类型的比较有很多种方法--它们的效率并不同。下面就有三种:
WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < cutoff WHERE TO_DAYS(date_col) < cutoff + TO_DAYS(CURDATE()) WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY)
对于第一行,不会用到索引,因为每个数据行都必须检索以计算出TO_DAYS(date_col)的值。第二行要好一些。Cutoff和 TO_DAYS(CURDATE())都是常量,因此在处理查询之前,比较表达式的右边可以被优化器一次性计算出来,而不需要每个数据行都计算一次。但是 date_col列仍然出现在函数调用中,它阻止了索引的使用。第三行是这几个中最好的。同样,在执行查询之前,比较表达式的右边可以作为常量一次性计算出来,但是现在它的值是一个日期。这个值可以直接与date_col值进行比较,再也不需要转换成天数了。在这种情况下,会使用索引。
在LIKE模式的开头不要使用通配符。有些字符串搜索使用如下所示的WHERE子句:
WHERE col_name LIKE ’%string%’
如果你希望找到那些出现在数据列的任何位置的字符串,这个语句就是对的。但是不要因为习惯而简单地把"%"放在字符串的两边。如果你在查找出现在数据列开头的字符串,就删掉前面的"%"。假设你要查找那些类似MacGregor或MacDougall等以"Mac"开头的名字。在这种情况下,WHERE子句如下所示:
WHERE last_name LIKE ’Mac%’
优化器查看该模式中词首的文本,并使用索引找到那些与下面的表达式匹配的数据行。下面的表达式是使用last_name索引的另一种形式:
WHERE last_name >= ’Mac’ AND last_name < ’Mad’
这种优化不能应用于使用了REGEXP操作符的模式匹配。REGEXP表达式永远不会被优化。
以上是网上摘录的,不可能每项都在实践中遇到,以后慢慢修改。
基础知识:
http://gsvince.iteye.com/blog/173542
发表评论
-
a 链接控制打开新窗口 无地址栏
2009-11-05 09:10 4351通过JavaScript跳转 <a href=&quo ... -
Mysql 查询优化笔记续
2009-09-08 11:11 1192上一篇笔记的粒度比较 ... -
windows下mysql备份
2009-09-01 14:48 3092一、单机备份 1. =================== ... -
Mysql 存储过程 与触发器
2009-09-01 14:43 3116两天前同事的一个日志模块事件备份老是报内存溢出错误,正好有点时 ... -
java.lang.UnsatisfiedLinkError: Unable to load library
2009-08-26 15:34 13795项目中要引用到一个动态库,在本机MyEclipse下调 ... -
开发人员 收藏夹的网站
2009-08-20 13:07 868http://www.gotapi.com/ 语言 ... -
解决MySQL8小时自动断开连接的问题
2009-07-30 13:30 4081mysql在启动后,如果一段时间内没有活动,那么将自动 ... -
mysql 权限控制笔记
2009-07-17 10:50 1567MySQL 存取控制包含2个阶段: * 阶 ... -
项目中常遇到的问题
2009-07-17 10:22 0经历几个项目后,我们会发现需求是不断变化的,但总有一些 ... -
HTTPS 和HTTP
2009-06-04 10:12 1043HTTPS(Secure Hypertext Transf ... -
MyEclipse优化
2009-05-26 10:39 0http://hi.baidu.com/linming_08/ ... -
JDK、Tomcat和MySQL的绿色安装方法
2009-05-21 14:23 2498要制作web系统setup一键安装程序,必须先解决JDK ... -
windows 安装多个MysQL服务器
2009-05-21 14:20 1744Windows上安装多个MySQL服务器 网络上查到两个版本可 ... -
同一台Windows下安装多个Tomcat服务
2009-05-19 18:06 3036本文以apache-tomcat-6.0.18 ... -
Java EE 的异常框架
2009-05-11 10:56 0一个模拟的例子来说明异常框架的设计过程,比如我们要对外 ... -
捕捉 404/500 错误,自定义Tomcat错误页面
2009-05-11 10:49 10216当服务器出现404、500错误时候希望能够给用户友好的现实界面 ... -
JSP/Servlet/JSF--对标签库的深入研究
2009-03-31 14:40 1043标签库Taglib 标签 ... -
框架中常用的jar包作用
2009-03-31 10:43 1484commons-digester.jar Digester基 ... -
spring包中文件及各种jar包详解
2009-03-31 10:31 1415aspectj目录下是在Spring框架下使用aspectj的 ... -
spring的jar各包作用
2009-03-31 10:29 1727spring.jar是包含有完整发布的单个jar 包,spri ...
相关推荐
详细介绍了mysql的优化方法,资料包里包含word文档,ppt和图片
对mysql优化时一个综合性的技术,主要包括 a: 表的设计合理化(符合3NF) b: 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引] c: 分表技术(水平分割、垂直分割) d: 读写[写: update/...
MySQL性能优化笔记,
里面结合了我整理的笔记希望对大家有用,请大家多多支持
MySQL优化学习思维笔记.xmind
MySql优化笔记包括网盘视频教程,此份资源对于想在mysql优化知识方面加深研究的伙伴是无价的
对mysql优化时一个综合性的技术,主要包括 a: 表的设计合理化(符合3NF) b: 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引] c: 分表技术(水平分割、垂直分割) d: 读写[写: update/...
MySQL圆你大厂高薪梦,技术终面官亲授offer收割术 msql面试 笔记 黑马程序员mysql笔记
里面有十八哥的mysql优化笔记 还有我自己整理的大牛讲课的mysql优化方式和技巧(有图片和文档 ) 个人认为比较全面 可以为面试提问mysql优化问题做准备
韩顺平mysql优化笔记.doc Mysql数据库的优化技术 对mysql优化时一个综合性的技术,主要包括 a: 表的设计合理化(符合3NF) b: 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引] c: 分表...
工作一些mysql优化笔记
MySQL Server中的核心组件有 连接器:负责对接客户端提交的连接请求 分析器:对发送过来的语句进行词法分析 和语法分析 优化器: 基于CBO的方式来对语句进行基础优化,并规定执行路径 执行器: 用来根据优化后的语句...
mysql优化笔记参考,用来优化数据库的。
MySQL性能优化课程笔记
mysql 优化 一点点个人总结
韩顺平_MySQL优化笔记.doc
数据库 MySQL 学习笔记高级篇.md
mysql优化笔记——相关图片
Q: 为什么别人问你MySQL优化的知识 总是没有底气. A: 因为你只是回答一些大而化之的调优原则, 比如:”建立合理索引”(什么样的索引合理?) “分表分库”(用什么策略分表分库?) “主从分离”(用什么中间件?) 并没有从...