首页 数据库 mysql教程 MySQL 5.0 数据库的新特性的存储过程_MySQL

MySQL 5.0 数据库的新特性的存储过程_MySQL

Jun 01, 2016 pm 02:03 PM
优化 使用 存储 数据 数据库 查询 特性 索引 过程

 

当你提交一个查询的时候,MySQL会分析它,看是否可以做一些优化使处理该查询的速度更快。这一部分将介绍查询优化器是如何工作的。如果你想知道MySQL采用的优化手段,可以查看MySQL参考手册。

 

当然,MySQL查询优化器也利用了索引,但是它也使用了其它一些信息。例如,如果你提交如下所示的查询,那么无论数据表有多大,MySQL执行它的速度都会非常快:

 

SELECT * FROM tbl_name WHERE 0;

 

在这个例子中,MySQL查看WHERE子句,认识到没有符合查询条件的数据行,因此根本就不考虑搜索数据表。你可以通过提供一个EXPLAIN语句看到这种情况,这个语句让MySQL显示自己执行的但实际上没有真正地执行的SELECT查询的一些信息。如果要使用EXPLAIN,只需要在EXPLAIN单词放在SELECT语句的前面:

 

<ccid_code></ccid_code>mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0\G 

*************************** 
1. row *************************** 

id: 1 

select_type: SIMPLE 

table: NULL 

type: NULL 

possible_keys: NULL 

key: NULL 

key_len: NULL 

ref: NULL 

rows: NULL 

Extra: Impossible WHERE
登录后复制

通常情况下,EXPLAIN返回的信息比上面的信息要多一些,还包括用于扫描数据表的索引、使用的联结类型、每张数据表中估计需要检查的数据行数量等非空(NULL)信息。

 

优化器是如何工作的

 

MySQL查询优化器有几个目标,但是其中最主要的目标是尽可能地使用索引,并且使用最严格的索引来消除尽可能多的数据行。你的最终目标是提交SELECT语句查找数据行,而不是排除数据行。优化器试图排除数据行的原因在于它排除数据行的速度越快,那么找到与条件匹配的数据行也就越快。如果能够首先进行最严格的测试,查询就可以执行地更快。假设你的查询检验了两个数据列,每个列上都有索引:

 

 

<ccid_code></ccid_code>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,因为这样做开销更小。

 

你可以通过下面一个指导帮助优化器更好地利用索引:

 

尽量比较数据类型相同的数据列。当你在比较操作中使用索引数据列的时候,请使用数据类型相同的列。相同的数据类型比不同类型的性能要高一些。例如,INT与BIGINT是不同的。CHAR(10)被认为是CHAR(10)或VARCHAR(10),但是与CHAR(12)或VARCHAR(12)不同。如果你所比较的数据列的类型不同,那么可以使用ALTER TABLE来修改其中一个,使它们的类型相匹配。

 

尽可能地让索引列在比较表达式中独立。如果你在函数调用或者更复杂的算术表达式条件中使用了某个数据列,MySQL就不会使用索引,因为它必须计算出每个数据行的表达式值。有时候这种情况无法避免,但是很多情况下你可以重新编写一个查询让索引列独立地出现。

 

下面的WHERE子句显示了这种情况。它们的功能相同,但是对于优化目标来说就有很大差异了:

 

<ccid_code></ccid_code>WHERE mycol 
            
登录后复制

 

对于第一行,优化器把表达式4/2简化为2,接着使用mycol上的索引来快速地查找小于2的值。对于第二个表达式,MySQL必须检索出每个数据行的mycol值,乘以2,接着把结果与4进行比较。在这种情况下,不会使用索引。数据列中的每个值都必须被检索到,这样才能计算出比较表达式左边的值。

 

我们看另外一个例子。假设你对date_col列进行了索引。如果你提交一条如下所示的查询,就不会使用这个索引:

 

SELECT * FROM mytbl WHERE YEAR(date_col)

 

这个表达式不会把1990与索引列进行比较;它会把1990与该数据列计算出来的值比较,而每个数据行都必须计算出这个值。其结果是,没有使用date_col上的索引,因为执行这样的查询需要全表扫描。怎么解决这个问题呢?只需要使用文本日期,接着就可以使用date_col上的索引来查找列中匹配的值了:

 

WHERE date_col

 

但是,假设你没有特定的日期。你可能希望找到一些与今天相隔固定的几天的日期的记录。表达这种类型的比较有很多种方法--它们的效率并不同。下面就有三种:

 

<ccid_code></ccid_code>WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) 
            
登录后复制

 

对于第一行,不会用到索引,因为每个数据行都必须检索以计算出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

 

这种优化不能应用于使用了REGEXP操作符的模式匹配。REGEXP表达式永远不会被优化。

 

帮助优化器更好的判断索引的效率。在默认情况下,当你把索引列的值与常量进行比较的时候,优化器会假设键值在索引内部是均匀分布的。在决定进行常量比较是否使用索引的时候,优化器会快速地检查索引,估计出会用到多少个实体(entry)。对应MyISAM、InnoDB和BDB数据表来说,你可以使用ANALYZE TABLE让服务器执行对键值的分析。它会为优化器提供更好的信息。

 

使用EXPLAIN验证优化器的操作。EXPLAIN语句可以告诉你是否使用了索引。当你试图用另外的方式编写语句或检查添加索引是否会提高查询执行效率的时候,这些信息对你是有帮助的。

 

在必要的时候给优化器一些提示。正常情况下,MySQL优化器自由地决定扫描数据表的次序来最快地检索数据行。在有些场合中优化器没有作出最佳选择。如果你察觉这种现象发生了,就可以使用STRAIGHT_JOIN关键字来重载优化器的选择。带有STRAIGHT_JOIN的联结类似于交叉联结,但是强迫数据表按照FROM子句中指定的次序来联结。

 

在SELECT语句中有两个地方可以指定STRAIGHT_JOIN。你可以在SELECT关键字和选择列表之间的位置指定,这样会对语句中所有的交叉联结产生影响;你也可以在FROM子句中指定。下面的两个语句功能相同:

 

 

<ccid_code></ccid_code>SELECT STRAIGHT_JOIN ... FROM t1, t2, t3 ... ; 

SELECT ... FROM t1 STRAIGHT_JOIN t2 STRAIGHT_JOIN t3 ... ;
登录后复制

 

分别在带有STRAIGHT_JOIN和不带STRAIGHT_JOIN的情况下运行这个查询;MySQL可能因为什么原因没有按照你认为最好的次序使用索引(你可以使用EXPLAIN来检查MySQL处理每个语句的执行计划)。

 

你还可以使用FORCE INDEX、USE INDEX或IGNORE INDEX来指导服务器如何使用索引。

 

利用优化器更加完善的区域。MySQL可以执行联结和子查询,但是子查询是最近才支持的,是在MySQL 4.1中添加的。因而在很多情况下,优化器对联结操作的调整比对子查询的调整要好一些。当你的子查询执行地很慢的时候,这就是一条实际的提示。有一些子查询可以使用逻辑上相等的联结来重新表达。在可行的情况下,你可以把子查询重新改写为联结,看是否执行地快一些。

 

测试查询的备用形式,多次运行。当你测试查询的备用形式的时候(例如,子查询与等同的联结操作对比),每种方式都应该多次运行。如果两种形式都只运行了一次,那么你通常会发现第二个查询比第一个快,这是因为第一个查询得到的信息仍然保留在缓存中,以至于第二个查询没有真正地从磁盘上读取数据。你还应该在系统负载相对平稳的时候运行查询,以避免系统中其它的事务影响结果。

 

避免过度地使用MySQL自动类型转换。MySQL会执行自动的类型转换,但是如果你能够避免这种转换操作,你得到的性能就更好了。例如,如果num_col是整型数据列,那么下面这些查询将返回相同的结果:

 

 

<ccid_code></ccid_code>SELECT * FROM mytbl WHERE num_col = 4; 

SELECT * FROM mytbl WHERE num_col = ’4’;
登录后复制

 

但是第二个查询涉及到了类型转换。转换操作本身为了把整型和字符串型转换为双精度型进行比较,使性能恶化了。更严重的情况是,如果num_col是索引的,那么涉及到类型转换的比较操作不会使用索引。

本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热门文章

R.E.P.O.能量晶体解释及其做什么(黄色晶体)
2 周前 By 尊渡假赌尊渡假赌尊渡假赌
仓库:如何复兴队友
4 周前 By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒险:如何获得巨型种子
4 周前 By 尊渡假赌尊渡假赌尊渡假赌

热工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

SublimeText3汉化版

SublimeText3汉化版

中文版,非常好用

禅工作室 13.0.1

禅工作室 13.0.1

功能强大的PHP集成开发环境

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

iPhone上的蜂窝数据互联网速度慢:修复 iPhone上的蜂窝数据互联网速度慢:修复 May 03, 2024 pm 09:01 PM

在iPhone上面临滞后,缓慢的移动数据连接?通常,手机上蜂窝互联网的强度取决于几个因素,例如区域、蜂窝网络类型、漫游类型等。您可以采取一些措施来获得更快、更可靠的蜂窝互联网连接。修复1–强制重启iPhone有时,强制重启设备只会重置许多内容,包括蜂窝网络连接。步骤1–只需按一次音量调高键并松开即可。接下来,按降低音量键并再次释放它。步骤2–该过程的下一部分是按住右侧的按钮。让iPhone完成重启。启用蜂窝数据并检查网络速度。再次检查修复2–更改数据模式虽然5G提供了更好的网络速度,但在信号较弱

美国空军高调展示首个AI战斗机!部长亲自试驾全程未干预,10万行代码试飞21次 美国空军高调展示首个AI战斗机!部长亲自试驾全程未干预,10万行代码试飞21次 May 07, 2024 pm 05:00 PM

最近,军事圈被这个消息刷屏了:美军的战斗机,已经能由AI完成全自动空战了。是的,就在最近,美军的AI战斗机首次公开,揭开了神秘面纱。这架战斗机的全名是可变稳定性飞行模拟器测试飞机(VISTA),由美空军部长亲自搭乘,模拟了一对一的空战。5月2日,美国空军部长FrankKendall在Edwards空军基地驾驶X-62AVISTA升空注意,在一小时的飞行中,所有飞行动作都由AI自主完成!Kendall表示——在过去的几十年中,我们一直在思考自主空对空作战的无限潜力,但它始终显得遥不可及。然而如今,

特斯拉机器人进厂打工,马斯克:手的自由度今年将达到22个! 特斯拉机器人进厂打工,马斯克:手的自由度今年将达到22个! May 06, 2024 pm 04:13 PM

特斯拉机器人Optimus最新视频出炉,已经可以在厂子里打工了。正常速度下,它分拣电池(特斯拉的4680电池)是这样的:官方还放出了20倍速下的样子——在小小的“工位”上,拣啊拣啊拣:这次放出的视频亮点之一在于Optimus在厂子里完成这项工作,是完全自主的,全程没有人为的干预。并且在Optimus的视角之下,它还可以把放歪了的电池重新捡起来放置,主打一个自动纠错:对于Optimus的手,英伟达科学家JimFan给出了高度的评价:Optimus的手是全球五指机器人里最灵巧的之一。它的手不仅有触觉

70B模型秒出1000token,代码重写超越GPT-4o,来自OpenAI投资的代码神器Cursor团队 70B模型秒出1000token,代码重写超越GPT-4o,来自OpenAI投资的代码神器Cursor团队 Jun 13, 2024 pm 03:47 PM

70B模型,秒出1000token,换算成字符接近4000!研究人员将Llama3进行了微调并引入加速算法,和原生版本相比,速度足足快出了快了13倍!不仅是快,在代码重写任务上的表现甚至超越了GPT-4o。这项成果,来自爆火的AI编程神器Cursor背后团队anysphere,OpenAI也参与过投资。要知道在以快着称的推理加速框架Groq上,70BLlama3的推理速度也不过每秒300多token。 Cursor这样的速度,可以说是实现了近乎即时的完整代码文件编辑。有人直呼好家伙,如果把Curs

C++ 程序优化:时间复杂度降低技巧 C++ 程序优化:时间复杂度降低技巧 Jun 01, 2024 am 11:19 AM

时间复杂度衡量算法执行时间与输入规模的关系。降低C++程序时间复杂度的技巧包括:选择合适的容器(如vector、list)以优化数据存储和管理。利用高效算法(如快速排序)以减少计算时间。消除多重运算以减少重复计算。利用条件分支以避免不必要的计算。通过使用更快的算法(如二分搜索)来优化线性搜索。

AI初创集体跳槽OpenAI,Ilya出走后安全团队重整旗鼓! AI初创集体跳槽OpenAI,Ilya出走后安全团队重整旗鼓! Jun 08, 2024 pm 01:00 PM

上周,在内部的离职潮和外部的口诛笔伐之下,OpenAI可谓是内忧外患:-侵权寡姐引发全球热议-员工签署「霸王条款」被接连曝出-网友细数奥特曼「七宗罪」辟谣:根据Vox获取的泄露信息和文件,OpenAI的高级领导层,包括Altman在内,非常了解这些股权回收条款,并且签署了它们。除此之外,还有一个严峻而紧迫的问题摆在OpenAI面前——AI安全。最近,五名与安全相关的员工离职,其中包括两名最著名的员工,“超级对齐”团队的解散让OpenAI的安全问题再次被置于聚光灯下。《财富》杂志报道称,OpenA

在PHP中使用MySQLi建立数据库连接的详尽教程 在PHP中使用MySQLi建立数据库连接的详尽教程 Jun 04, 2024 pm 01:42 PM

如何在PHP中使用MySQLi建立数据库连接:包含MySQLi扩展(require_once)创建连接函数(functionconnect_to_db)调用连接函数($conn=connect_to_db())执行查询($result=$conn->query())关闭连接($conn->close())

iOS 18 新增'已恢复”相册功能 可找回丢失或损坏的照片 iOS 18 新增'已恢复”相册功能 可找回丢失或损坏的照片 Jul 18, 2024 am 05:48 AM

苹果公司最新发布的iOS18、iPadOS18以及macOSSequoia系统为Photos应用增添了一项重要功能,旨在帮助用户轻松恢复因各种原因丢失或损坏的照片和视频。这项新功能在Photos应用的"工具"部分引入了一个名为"已恢复"的相册,当用户设备中存在未纳入其照片库的图片或视频时,该相册将自动显示。"已恢复"相册的出现为因数据库损坏、相机应用未正确保存至照片库或第三方应用管理照片库时照片和视频丢失提供了解决方案。用户只需简单几步

See all articles