SQL查询优化

转载 2011年01月19日 15:36:00

MySQL查询优化系列讲座之数据类型与效率

 

这一部分提供了如何选择数据类型来帮助提高查询运行速度的一些指导:

 

 

  在可以使用短数据列的时候就不要用长的。如果你有一个固定长度的CHAR数据列,那么就不要让它的长度超出实际需要。如果你在数据列中存储的最长的值有40个字符,就不要定义成CHAR(255),而应该定义成CHAR(40)。如果你能够用MEDIUMINT代替BIGINT,那么你的数据表就小一些(磁盘I/O少一些),在计算过程中,值的处理速度也快一些。如果数据列被索引了,那么使用较短的值带来的性能提高更加显著。不仅索引可以提高查询速度,而且短的索引值也比长的索引值处理起来要快一些。

 

  如果你可以选择数据行的存储格式,那么应该使用最适合存储引擎的那种。对于MyISAM数据表,最好使用固定长度的数据列代替可变长度的数据列。例如,让所有的字符列用CHAR类型代替VARCHAR类型。权衡得失,我们会发现数据表使用了更多的磁盘空间,但是如果你能够提供额外的空间,那么固定长度的数据行被处理的速度比可变长度的数据行要快一些。对于那些被频繁修改的表来说,这一点尤其突出,因为在那些情况下,性能更容易受到磁盘碎片的影响。

 

  · 在使用可变长度的数据行的时候,由于记录长度不同,在多次执行删除和更新操作之后,数据表的碎片要多一些。你必须使用OPTIMIZE TABLE来定期维护其性能。固定长度的数据行没有这个问题。

 

  · 如果出现数据表崩溃的情况,那么数据行长度固定的表更容易重新构造。使用固定长度数据行的时候,每个记录的开始位置都可以被检测到,因为这些位置都是固定记录长度的倍数,但是使用可变长度数据行的时候就不一定了。这不是与查询处理的性能相关的问题,但是它一定能够加快数据表的修复速度。

 

  尽管把MyISAM数据表转换成使用固定长度的数据列可以提高性能,但是你首先需要考虑下面一些问题:

 

  · 固定长度的数据列速度较快,但是占用的空间也较大。CHAR(n)列的每个值(即使是空值)通常占n个字符,这是因为把它存储到数据表中的时候,会在值的后面添加空格。VARCHAR(n)列占有的空间较小,因为只需要分配必要的字符个数用于存储值,加上一两个字节来存储值的长度。因此,在CHAR和VARCHAR列之间进行选择的时候,实际上是时间与空间的对比。如果速度是主要的考虑因素,那么就使用CHAR数据列获取固定长度列的性能优势。如果空间很重要,那么就使用VARCHAR数据列。总而言之,你可以认为固定长度的数据行可以提高性能,虽然它占用了更大的空间。但是对于某些特殊的应用程序,你可能希望使用两种方式来实现某个数据表,然后运行测试来决定哪种情况符合应用程序的需求。 

 

  · 即使愿意使用固定长度类型,有时候你也没有办法使用。例如,长于255个字符的字符串就无法使用固定长度类型。

 

  MEMORY数据表目前都使用固定长度的数据行存储,因此无论使用CHAR或VARCHAR列都没有关系。两者都是作为CHAR类型处理的。

 

  对于InnoDB数据表,内部的行存储格式没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针),因此在本质上,使用固定长度的CHAR列不一定比使用可变长度VARCHAR列简单。因而,主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR,因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

 

  对于BDB数据表,无论使用固定长度或可变长度的数据列,差别都不大。两种方法你都可用试一下,运行一些实验测试来检测是否存在明显的差别。

 

  把数据列定义成不能为空(NOT NULL)。这会使处理速度更快,需要的存储更少。它有时候还简化了查询,因为在某些情况下你不需要检查值的NULL属性。

 

  考虑使用ENUM数据列。如果你拥有的某个数据列的基数很低(包含的不同的值数量有限),那么可以考虑把它转换为ENUM列。ENUM值可以被更快地处理,因为它们在内部表现为数值。

 

  使用PROCEDURE ANALYSE()。运行PROCEDURE ANALYSE()可以看到数据表中列的情况:

 

SELECT * FROM tbl_name PROCEDURE ANALYSE();

SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256); 

 

  输出的每一列信息都会对数据表中的列的数据类型提出优化建议。第二个例子告诉PROCEDURE ANALYSE()不要为那些包含的值多于16个或者256字节的ENUM类型提出建议。如果没有这样的限制,输出信息可能很长;ENUM定义通常很难阅读。

MySQL5.6 如何优化慢查询的SQL语句 -- SQL优化

MySQL5.6 如何优化慢查询的SQL语句 -- SQL查询慢日志分析工具和SQL语句执行计划分析...
  • cloud_ll
  • cloud_ll
  • 2014年10月12日 12:30
  • 4417

SQL Server多表查询优化方案总结

SQL Server多表查询的优化方案是本文我们主要要介绍的内容,本文我们给出了优化方案和具体的优化实例,接下来就让我们一起来了解一下这部分内容。 1.执行路径 ORACLE的这个功能大大地提高了...
  • jiangnengzhuo
  • jiangnengzhuo
  • 2014年03月12日 10:43
  • 2585

优化 SQL SELECT 语句性能的 6 个简单技巧

本文由 伯乐在线 - xianhu 翻译,进林 校稿。未经许可,禁止转载! 英文出处:msiman.ga。欢迎加入翻译小组。 SELECT语句的性能调优有时是一个非常耗时的任务,在我看来它遵循...
  • werm520
  • werm520
  • 2015年05月04日 16:09
  • 10540

SQL优化--逻辑优化--子查询优化(MySQL)

1)子查询概念:当一个查询是另一个查询的子部分时,称之为子查询(查询语句中嵌套有查询语句)。 子查询出现的位置有: a)目标列位置:子查询如果位于目标列,则只能是标量子查询,否则数据库可能返回类...
  • wuxian90
  • wuxian90
  • 2015年02月07日 15:04
  • 1251

一个复杂子查询SQL优化

MySQL中一个复杂子查询的优化。
  • f88520402
  • f88520402
  • 2015年05月02日 09:25
  • 1010

sql优化--in和exists效率

in 和exists in是把外表和内表作hash 连接,而exists 是对外表作loop 循环,每次loop 循环再对内表进行查询。 一直以来认为exists 比in 效率高的说法是不...
  • caomiao2006
  • caomiao2006
  • 2016年08月02日 23:57
  • 3977

Oracle SQL查询优化方法1

系统优化中很重要的方面是SQL语句的优化,对于海量数据,优质的SQL能够有效的提高系统的可用性。 总结的有点罗嗦,列个简单的目录啦~ 目录   第一部分 知识准备       ...
  • truelove12358
  • truelove12358
  • 2017年04月18日 14:51
  • 346

Mysql常用30种SQL查询语句优化方法

1、应尽量避免在 where 子句中使用!=或 2、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 3、应尽量避免在 where 子句中对...
  • youthsunshine
  • youthsunshine
  • 2016年12月05日 15:32
  • 1787

如何写出高性能SQL语句—优化SQL查询

1、 首先要搞明白什么叫执行计划? 执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,比如一条SQL语句如果用来从一个 10万条记录的表中查1...
  • chenleixing
  • chenleixing
  • 2015年01月11日 13:17
  • 3198

【sql查询与优化】1.单表查询

注:以下所有sql案例均取自"oracle查询优化改写技巧与案例"丛书。 EMP表的详细: 1.查询表中所有的行与列 查询所有信息, SQL> select * from emp;      ...
  • u013517797
  • u013517797
  • 2016年03月31日 14:34
  • 1257
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL查询优化
举报原因:
原因补充:

(最多只允许输入30个字)