MYSQL查询优化:数据类型与效率

标签: mysql mysql优化 数据类型 | 发表时间:2012-09-29 14:05 | 作者:admin
出处:http://blog.haohtml.com

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

在可以使用短数据列的时候就不要用长的。如果你有一个固定长度的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定义通常很难阅读。

转:http://dev.yesky.com/103/2114603.shtml?412

相关 [mysql 优化 数据类型] 推荐:

高性能MySql进化论(二):数据类型的优化_下

- - CSDN博客数据库推荐文章
在实际的应用程序中往往需要存储两种体积较大的数据,一种是较大的Binary数据,e.g. 一张10M的图片,另外一种是 较大的文本 e.g.一篇几万字的文章. 在Oracle中有BOLB和CLOB来应对这两种数据,而在MySQL中对应的是BLOB以及TEXT. 鉴于这两种数据类型的特殊性,在MySQL中对BLOB以及TEXT的存储和操作做了特殊的处理:.

MYSQL查询优化:数据类型与效率

- - haohtml's blog
这一部分提供了如何选择数据类型来帮助提高查询运行速度的一些指导:. 在可以使用短数据列的时候就不要用长的. 如果你有一个固定长度的CHAR数据列,那么就不要让它的长度超出实际需要. 如果你在数据列中存储的最长的值有 40个字符,就不要定义成CHAR(255),而应该定义成CHAR(40). 如果你能够用MEDIUMINT代替BIGINT,那么你的数据表就小一些 (磁盘I/O少一些),在计算过程中,值的处理速度也快一些.

高性能MySql进化论(一):数据类型的优化_上

- - CSDN博客数据库推荐文章
在数据库的性能调优的过程中会涉及到很多的知识,包括字段的属性设置是否合适,索引的建立是否恰当,表结构涉及是否合理,数据库/操作系统 的设置是否正确…..其中每个topic可能都是一个领域. 在我看来,在数据库性能提升关键技术中,对字段的优化难度相对较低且对性能的影响也非常的大. 由于Mysql支持的数据类型比较多,且每个类型都有其独特的特性,但是有时候在选择一个具体的数据类型时,往往都是随意的选择一个能用的类型,而不会考虑到这个类型是否是最优的.

MySQL datetime数据类型设置当前时间为默认值

- - CSDN博客推荐文章
        环境:MySQL Sever 5.1 + MySQL命令行工具.         问题:MySQL datetime数据类型设置当前时间为默认值.          由于MySQL目前字段的默认值不支持函数,所以以create_time datetime default now() 的形式设置默认值是不可能的.

mysql优化

- - 数据库 - ITeye博客
公司网站访问量越来越大,MySQL自然成为瓶颈,因此最近我一直在研究 MySQL  的优化,第一步自然想到的是 MySQL 系统参数的优化,作为一个访问量很大的网站(日20万人次以上)的数据库系统,不可能指望 MySQL  默认的系统参数能够让 MySQL运行得非常顺畅. 在Apache, PHP,  MySQL的体系架构中,MySQL对于性能的影响最大,也是关键的核心部分.

mysql优化

- - 数据库 - ITeye博客
      1.通过 show (session 或者 global) status 来查看( 当前连接 或者 数据库上次开机以来 )的服务器状态信息,默认是session.         例如:show status like '%com_%' : com_XXX表示XXX语句执行的总次数,这总次数是针对所有引擎的总和.

MySQL性能优化

- sun - IT程序员面试网
在笔试面试中,尤其是像百度,淘宝这些数据量非常大,而且用LAMP架构的公司,数据库优化方面就显得特别重要了. 此外,除了数据库索引之外,在LAMP结果如此流行的今天,数据库(尤其是MySQL)性能优化也是海量数据处理的一个热点. 下面就结合自己的经验,聊一聊MySQL数据库优化的几个方面. 首先,在数据库设计的时候,要能够充分的利用索引带来的性能提升,至于如何建立索引,建立什么样的索引,在哪些字段上建立索引,上面已经讲的很清楚了,这里不在赘述.

mysql 引擎优化

- - CSDN博客推荐文章
MySQL数 据库引擎取决于MySQL在安装的时候是如何被编译的. 要添加一个新的引擎,就必须重新编译MYSQL. 在缺省情况下,MYSQL支持三个引擎:ISAM、MYISAM和HEAP. 另外两种类型INNODB和BERKLEY(BDB),也常常可以使用. 如果技术高超,还可以使用MySQL++ API自己做一个引擎.

mysql参数优化

- - CSDN博客推荐文章
### 用来存放InnoDB的内部目录,对于大数据设置16M足够用. ### InnoDB 缓存总大小设置,一般设置为系统内存的70%-80%. ### 指定所有InnoDB数据文件的路径和大小分配. ### 文件读写io数设置:. ### InnoDB内核的并发线程数设置. ### 设置日值的大小.

Zabbix 的 MySQL 优化

- - SegmentFault 最新的文章
为 Zabbix 优化 MySQL. 标签(空格分隔): Zabbix MySQL Optimizing 优化. Aurimas Mikalauskas,原文是. Zabbix 和 MySQL. 在大型的 Zabbix 环境中,遇到的挑战大部分是 MySQL 以及更具体的说是 MySQL 磁盘 IO.