MySQL数据库技巧MySQL优化之数据类型的使用.docx
- 文档编号:15812720
- 上传时间:2023-07-08
- 格式:DOCX
- 页数:20
- 大小:33.73KB
MySQL数据库技巧MySQL优化之数据类型的使用.docx
《MySQL数据库技巧MySQL优化之数据类型的使用.docx》由会员分享,可在线阅读,更多相关《MySQL数据库技巧MySQL优化之数据类型的使用.docx(20页珍藏版)》请在冰点文库上搜索。
MySQL数据库技巧MySQL优化之数据类型的使用
MySQL数据库技巧MySQL优化之数据类型的使用
有助于效率的类型选择
1、使你的数据尽可能小
最基本的优化之一是使你的数据(和索引)在磁盘上(并且在内存中)占据的空间尽可能小。
这能给出巨大的改进,因为磁盘读入较快并且通常也用较少的主存储器。
如果在更小的列上做索引,索引也占据较少的资源。
你能用下面的技术使表的性能更好并且使存储空间最小:
·尽可能地使用最有效(最小)的类型。
MySQL有很多节省磁盘空间和内存的专业化类型。
·如果可能使表更小,使用较小的整数类型。
例如,MEDIUMINT经常比INT好一些。
·如果可能,声明列为NOTNULL。
它使任何事情更快而且你为每列节省一位。
注意如果在你的应用程序中你确实需要NULL,你应该毫无疑问使用它,只是避免缺省地在所有列上有它。
2、使用定长列,不使用可变长列
这条准则对被经常修改,从而容易产生碎片的表来说特别重要。
例如,应该选择CHAR列而不选择VARCHAR列。
所要权衡的是使用定长列时,表所占用的空间更多,但如果能够承担这种空间的耗费,使用定长行将比使用可变长的行处理快得多。
3、将列定义为NOTNULL
这样处理更快,所需空间更少。
而且有时还能简化查询,因为不需要检查是否存在特例NULL。
4、考虑使用ENUM列
如果有一个只含有限数目的特定值的列,那么应该考虑将其转换为ENUM列。
ENUM列的值可以更快地处理,因为它们在内部是以数值表示的。
有关BLOB和TEXT类型
1、使用BLOB和TEXT类型的优点
用BLOB存储应用程序中包装或未包装的数据,有可能使原来需要几个检索操作才能完成的数据检索得以在单个检索操作中完成。
而且还对存储标准表结构不易表示的数据或随时间变化的数据有帮助。
2、使用BLOB和TEXT类型的可能弊端
另一方面,BLOB值也有自己的固有问题,特别是在进行大量的DELETE或UPDATE操作时更是如此。
删除BLOB会在表中留下一个大空白,在以后将需用一个记录或可能是不同大小的多个记录来填充。
除非有必要,否则应避免检索较大的BLOB或TEXT值。
例如,除非肯定WHERE子句能够将结果恰好限制在所想要的行上,否则SELECT*查询不是一个好办法。
这样做可能会将非常大的BLOB值无目的地从网络上拖过来。
这是存储在另一列中的BLOB标识信息很有用的另一种情形。
可以搜索该列以确定想要的行,然后从限定的行中检索BLOB值。
3、必要的准则
对容易产生碎片的表使用OPTIMIZETABLE
大量进行修改的表,特别是那些含有可变长列的表,容易产生碎片。
碎片不好,因为它在存储表的磁盘块中产生不使用的空间。
随着时间的增长,必须读取更多的块才能取到有效的行,从而降低了性能。
任意具有可变长行的表都存在这个问题,但这个问题对BLOB列更为突出,因为它们尺寸的变化非常大。
经常使用OPTIMIZETABLE有助于保持性能不下降。
使用多列索引
多列索引列有时很有用。
一种技术是根据其他列建立一个散列值,并将其存储在一个独立的列中,然后可通过搜索散列值找到行。
这只对精确匹配的查询有效。
(散列值对具有诸如“<”或“>=”这样的操作符的范围搜索没有用处)。
在MySQL3.23版及以上版本中,散列值可利用MD5()函数产生。
散列索引对BLOB列特别有用。
有一事要注意,在MySQL3.23.2以前的版本中,不能索引BLOB类型。
甚至是在3.23.2或更新的版本中,利用散列值作为标识值来查找BLOB值也比搜索BLOB列本身更快。
将BLOB值隔离在一个独立的表中
在某些情况下,将BLOB列从表中移出放入另一个副表可能具有一定的意义,条件是移出BLOB列后可将表转换为定长行格式。
这样会减少主表中的碎片,而且能利用定长行的性能优势。
使用ANALYSE过程检查表列
如果使用的是MySQL3.23或更新的版本,应该执行PROCEDUREANALYSE(),查看它所提供的关于表中列的信息
ANALYSE([maxelements,[maxmemory]])
它检验来自你的查询的结果并返回结果的分析。
maxelements(缺省256)是analyse将注意的每列不同值的最大数量。
这被ANALYSE用来检查最佳的列类型是否应该是ENUM类型。
maxmemory(缺省8192)是在analyse尝试寻找所有不同值的时候应该分配给每列的最大内存量。
SELECT...FROM...WHERE...PROCEDUREANALYSE([maxelements,[maxmemory]])
例如:
mysql>SELECT*FROMstudentPROCEDUREANALYSE();
mysql>SELECT*FROMstudentPROCEDUREANALYSE(16,256);
相应输出中有一列是关于表中每列的最佳列类型的建议。
第二个例子要求PROCEDUREANALYSE()不要建议含有多于16个值或取多于256字节的ENUM类型(可根据需要更改这些值)。
如果没有这样的限制,输出可能会很长;ENUM的定义也会很难阅读。
根据PROCEDUREANALYSE()的输出,会发现可以对表进行更改以利用更有效的类型。
如果希望更改值类型,使用ALTERTABLE语句即可。
数据库数据类型
1)整数型
整数包括bigint、int、smallint和tinyint,从标识符的含义就可以看出,它们的表示数范围逐渐缩小。
lbigint:
大整数,数范围为-263(-)~263-1(),其精度为19,小数位数为0,长度为8字节。
lint:
整数,数范围为-231(-2,147,483,648)~231-1(2,147,483,647),其精度为10,小数位数为0,长度为4字节。
lsmallint:
短整数,数范围为-215(-32768)~215-1(32767),其精度为5,小数位数为0,长度为2字节。
ltinyint:
微短整数,数范围为0~255,长度为1字节,其精度为3,小数位数为0,长度为1字节。
(2)精确整数型
精确整数型数据由整数部分和小数部分构成,其所有的数字都是有效位,能够以完整的精度存储十进制数。
精确整数型包括decimal和numeric两类。
从功能上说两者完全等价,两者的唯一区别在于decimal不能用于带有identity关键字的列。
声明精确整数型数据的格式是numeric|decimal(p[,s]),其中p为精度,s为小数位数,s的缺省值为0。
例如指定某列为精确整数型,精度为6,小数位数为3,即decimal(6,3),那么若向某记录的该列赋值56.时,该列实际存储的是56.3427。
decimal和numeric可存储从-1038+1到1038–1的固定精度和小数位的数字数据,它们的存储长度随精度变化而变化,最少为5字节,最多为17字节。
l精度为1~9时,存储字节长度为5;
l精度为10~19时,存储字节长度为9;
l精度为20~28时,存储字节长度为13;
l精度为29~38时,存储字节长度为17。
例如若有声明numeric(8,3),则存储该类型数据需5字节,而若有声明numeric(22,5),则存储该类型数据需13字节。
注意:
声明精确整数型数据时,其小数位数必须小于精度;在给精确整数型数据赋值时,必须使所赋数据的整数部分位数不大于列的整数部分的长度。
(3)浮点型
浮点型也称近似数值型。
顾名思义,这种类型不能提供精确表示数据的精度,使用这种类型来存储某些数值时,有可能会损失一些精度,所以它可用于处理取值范围非常大且对精确度要求不是十分高的数值量,如一些统计量。
有两种近似数值数据类型:
float[(n)]和real,两者通常都使用科学计数法表示数据,即形为:
尾数E阶数,如5.6432E20,-2.98E10,1.E-9等。
lreal:
使用4字节存储数据,表数范围为-3.40E+38到3.40E+38,数据精度为7位有效数字。
lfloat:
定义中的n取值范围是1~53,用于指示其精度和存储大小。
当n在1~24之间时,实际上是定义了一个real型数据,存储长度为4字节,精度为7位有效数字;当n在25~53之间时,存储长度为8字节,精度为15位有效数字。
当缺省n时,代表n在25~53之间。
float型数据的数范围为-1.79E+308到1.79E+308。
(4)货币型
SQLServer提供了两个专门用于处理货币的数据类型:
money和smallmoney,它们用十进制数表示货币值。
lmoney:
数据的数范围为-263(-5477.5808)~263-1(5477.5807),其精度为19,小数位数为4,长度为8字节。
money的数的范围与bigint相同,不同的只是money型有4位小数,实际上,money就是按照整数进行运算的,只是将小数点固定在末4位。
lsmallmoney:
数范围为–231(-2,147,48.3648)~231-1(2,147,48.3647),其精度为10,小数位数为4,长度为4字节。
可见smallmoney与int的关系就如同money与bigint的关系。
当向表中插入money或smallmoney类型的值时,必须在数据前面加上货币表示符号($),并且数据中间不能有逗号(,);若货币值为负数,需要在符号$的后面加上负号(-)。
例如:
$15000.32,$680,$-20000.9088都是正确的货币数据表示形式。
(5)位型
SQLServer中的位(bit)型数据相当于其他语言中的逻辑型数据,它只存储0和1,长度为一个字节。
但要注意,SQLServer对表中bit类型列的存储作了优化:
如果一个表中有不多于8个的bit列,这些列将作为一个字节存储,如果表中有9到16个bit列,这些列将作为两个字节存储,更多列的情况依此类推。
当为bit类型数据赋0时,其值为0,而赋非0(如100)时,其值为1。
若表中某列为bit类型数据,那么该列不允许为空值(有关空值概念本节稍后即做介绍),并且不允许对其建立索引。
(6)字符型
字符型数据用于存储字符串,字符串中可包括字母、数字和其它特殊符号(如#、@、&等等)。
在输入字符串时,需将串中的符号用单引号或双引号括起来,如’abc’、”Abc SQLServer字符型包括两类: 固定长度(char)或可变长度(varchar)字符数据类型。 lchar[(n)] 定长字符数据类型,其中n定义字符型数据的长度,n在1到8000之间,缺省为1。 当表中的列定义为char(n)类型时,若实际要存储的串长度不足n时,则在串的尾部添加空格以达到长度n,所以char(n)的长度为n。 例如某列的数据类型为char(20),而输入的字符串为”ahjm1922”,则存储的是字符ahjm1922和12个空格。 若输入的字符个数超出了n,则超出的部分被截断。 lvarchar[(n)] 变长字符数据类型,其中n的规定与定长字符型char中n完全相同,但这里n表示的是字符串可达到的最大长度。 varchar(n)的长度为输入的字符串的实际字符个数,而不一定是n。 例如,表中某列的数据类型为varchar(100),而输入的字符串为”ahjm1922”,则存储的就是字符ahjm1922,其长度为8字节。 当列中的字符数据值长度接近一致时,例如姓名,此时可使用char;而当列中的数据值长度显著不同时,使用varchar较为恰当,可以节省存储空间。 (7)Unicode字符型 Unicode是“统一字符编码标准”,用于支持国际上非英语语种的字符数据的存储和处理。 SQLServer的Unicode字符型可以存储Unicode标准字符集定义的各种字符。 Unicode字符型包括nchar[(n)]和nvarchar[(n)]两类。 nchar是固定长度Unicode数据的数据类型,nvarchar是可变长度Unicode数据的数据类型,二者均使用UNICODEUCS-2字符集。 lnchar[(n)]: nchar[(n)]为包含n个字符的固定长度Unicode字符型数据,n的值在1与4,000之间,缺省为1。 长度为2n字节。 若输入的字符串长度不足n,将以空白字符补足。 lnvarchar[(n)]: nvarchar[(n)]为最多包含n个字符的可变长度Unicode字符型数据,n的值在1与4,000之间,缺省为1。 长度是所输入字符个数的两倍。 实际上,nchar、nvarchar与char、varchar的使用非常相似,只是字符集不同(前者使用Unicode字符集,后者使用ASCII字符集)。 (8)文本型 当需要存储大量的字符数据,如较长的备注、日志信息等等,字符型数据的最长8000个字符的限制可能使它们不能满足这种应用需求,此时可使用文本型数据。 文本型包括text和ntext两类,分别对应ASCII字符和Unicode字符。 text类型可以表示最大长度为231-1(2,147,483,647)个字符,其数据的存储长度为实际字符数个字节。 ntext可表示最大长度为230-1(1,073,741,823)个Unicode字符,其数据的存储长度是实际字符个数的两倍(以字节为单位)。 (9)二进制型 二进制数据类型表示的是位数据流,包括binary(固定长度)和varbinary(可变长度)两种。 lbinary[(n)]: 固定长度的n个字节二进制数据。 n取值范围为1到8,000,缺省为1。 binary(n)数据的存储长度为n+4字节。 若输入的数据长度小于n,则不足部分用0填充;若输入的数据长度大于n,则多余部分被截断。 输入二进制值时,在数据前面要加上0x,可以用的数字符号为0—9、A—F(字母大小写均可)。 因此,二进制数据有时也被称为十六进制数据。 例如0xFF、0x12A0分别表示值FF和12A0。 因为每字节的数最大为FF,故在“0x”格式的数据每两位占1个字节。 lvarbinary[(n)]: n个字节变长二进制数据。 n取值范围为1到8,000,缺省为1。 varbinary(n)数据的存储长度为实际输入数据长度+4个字节。 (10)日期时间类型 日期时间类型数据用于存储日期和时间信息,包括datetime和smalldatetime两类。 ldatetime: datetime类型可表示的日期范围从1753年1月1日到9999年12月31日的日期和时间数据,精确度为百分之三秒(3.33毫秒或0.00333秒),例如1到3毫秒的值都表示为0毫秒,4到6毫秒的值都表示为4毫秒。 datetime类型数据长度为8字节,日期和时间分别使用4个字节存储。 前4字节用于存储datetime类型数据中距1900年1月1日的天数,为正数表示日期在1900年1月1日之后,为负数则表示日期在1900年1月1日之前。 后4个字节用于存储datetime类型数据中距12: 00(24小时制)的毫秒数。 用户以字符串形式输入datetime类型数据,系统也以字符串形式输出datetime类型数据,将用户输入到系统以及系统输出的datetime类型数据的字符串形式称为datetime类型数据的“外部形式”,而将datetime在系统内的存储形式称为“内部形式”,SQLServer负责datetime类型数据的两种表现形式之间的转换,包括合法性检查。 用户给出datetime类型数据值时,日期部分和时间部分分别给出。 日期部分的表示形式常用的格式如下: 年月日2001Jan20、2001Janary20 年日月200120Jan 月日[,]年Jan202001、Jan20,2001、Jan20,01 月年日Jan200120 日月[,]年20Jan2001、20Jan,2001 日年月202001Jan 年(4位数)2001表示2001年1月1日 年月日、 月/日/年01/20/01、1/20/01、01/20/2001、1/20/2001 月-日-年01-20-01、1-20-01、01-20-2001、1-20-2001 月.日.年01.20.01、1.20.01、01.20.2001、1.20.2001 说明: 年可用4位或2位表示,月和日可用1位或2位表示。 时间部分常用的表示格式如下: 时: 分10: 20、08: 05 时: 分: 秒20: 15: 18、20: 15: 18.2 时: 分: 秒: 毫秒20: 15: 18: 200 时: 分AM|PM10: 10AM、10: 10PM lsmalldatetime: smalldatetime类型数据可表示从1900年1月1日到2079年6月6日的日期和时间,数据精确到分钟,即29.998秒或更低的值向下舍入为最接近的分钟,29.999秒或更高的值向上舍入为最接近的分钟。 Smalldatetime类型数据的存储长度为4字节,前2个字节用来存储smalldatetime类型数据中日期部分距1900年1月1日之后的天数;后2个字节用来存储smalldatetime类型数据中时间部分距中午12点的分钟数。 用户输入smalldatetime类型数据的格式与datetime类型数据完全相同,只是它们的内部存储可能不相同。 (11)时间戳型 标识符是timestamp。 若创建表时定义一个列的数据类型为时间戳类型,那么每当对该表加入新行或修改已有行时,都由系统自动将一个计数器值加到该列,即将原来的时间戳值加上一个增量。 记录timestamp列的值实际上反映了系统对该记录修改的相对(相对于其他记录)顺序。 一个表只能有一个timestamp列。 Timestamp类型数据的值实际上是二进制格式数据,其长度为8字节。 (12)图象数据类型 标识符是image,它用于存储图片、照片等。 实际存储的是可变长度二进制数据,介于0与231-1(2,147,483,647)字节之间。 (13)其它数据类型 除了上面所介绍的常用数据类型外,SQLServer2000还提供了其它几种数据类型: cursor、sql_variant、table和uniqueidentifier。 Cursor: 是游标数据类型,用于创建游标变量或定义存储过程的输出参数。 Sql_variant: 是一种存储SQLServer支持的各种数据类型(除text、ntext、image、timestamp和sql_variant外)值的数据类型。 Sql_variant的最大长度可达8016字节。 Table: 是用于存储结果集的数据类型,结果集可以供后续处理。 Uniqueidentifier: 是唯一标识符类型。 系统将为这种类型的数据产生唯一标识值,它是一个16字节长的二进制数据。 MySQL查询优化系列讲座之数据类型和效率 这一部分提供了怎么选择数据类型来帮助提高查询运行速度的一些指导: 在能使用短数据列的时候就不要用长的。 如果你有一个固定长度的CHAR数据列,那么就不要让他的长度超出实际需要。 如果你在数据列中存储的最长的值有40个字符,就不要定义成CHAR(255),而应该定义成CHAR(40)。 如果你能够用MEDIUMINT代替BIGINT,那么你的数据表就小一些(磁盘I/O少一些),在计算过程中,值的处理速度也快一些。 如果数据列被索引了,那么使用较短的值带来的性能提高更加显著。 不仅索引能提高查询速度,而且短的索引值也比长的索引值处理起来要快一些。 如果你能选择数据行的存储格式,那么应该使用最适合存储引擎的那种。 对于MyISAM数据表,最佳使用固定长度的数据列代替可变长度的数据列。 例如,让所有的字符列用CHAR类型代替VARCHAR类型。 权衡得失,我们会发现数据表使用了更多的磁盘空间,不过如果你能够提供额外的空间,那么固定长度的数据行被处理的速度比可变长度的数据行要快一些。 对于那些被频繁修改的表来说,这一点尤其突出,因为在那些情况下,性能更容易受到磁盘碎片的影响。 ·在使用可变长度的数据行的时候,由于记录长度不同,在多次执行删除和更新操作之后,数据表的碎片要多一些。 你必须使用OPTIMIZETABLE来定期维护其性能。 固定长度的数据行没有这个问题。 ·如果出现数据表崩溃的情况,那么数据行长度固定的表更容易重新构造。 使用固定长度数据行的时候,每个记录的开始位置都能被检测到,因为这些位置都是固定记录长度的倍数,不过使用可变长度数据行的时候就不一定了。 这不是和查询处理的性能相关的问题,不过他一定能够加快数据表的修复速度。 尽管把MyISAM数据表转换成使用固定长度的数据列能提高性能,不过你首先需要考虑下面一些问题: ·固定长度的数据列速度较快,不过占用的空间也较大。 CHAR(n)列的每个值(即使是空值)通常占n个字符,这是因为把他存储到数据表中的时候,会在值的后面添加空格。 VARCHAR(n)列占有的空间较小,因为只需要分配必要的字符个数用于存储值,加上一两个字节来存储值的长度。 因此,在CHAR和VARCHAR列之间进行选择的时候,实际上是时间和空间的对比。 如果速度是主要的考虑因素,那么就使用CHAR数据列获取固定长度列的性能优势。 如果空间非常重要,那么就使用VARCHAR数据列。 总而言之,你能认为固定长度的数据行能提高性能,虽然他占用了更大的空间。 不过对于某些特别的应用程式,你可能希望使用两种方式来实现某个数据表,然后运行测试来决定哪种情况符合应用程式的需求。 ·即使愿意使用固定长度类型,有时候你也没有办法使用。 例如,长于255个字符的字符串就无法使用固定长度类型。 MEMORY数据表目前都使用固定长度的数据行存储,因此无论使用CHAR或VARCHAR列都没有关系。 两者都是作为CHAR类型处理的。 对于InnoDB数据表,内部的行存储格式没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针),因此在本质上,使用固定长度的CHAR列不一定比使用可变长度VARCHAR列简单。 因而,主要的性能因素是数据行使用的存储总量。 由于CHAR平均占用的空间多于VARCHAR,因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。 对于BDB数据表,无论使用固定长度或可变长度的数据列,差别都不大。 两种方法你都可用试一下,运行一些实验测试来检测是否存在明显的差别。 把数据列定义成不能为空(NOTNULL)。 这会使处理速度更快,需要的存储更少。 他有时候还简化了查询,因为在某些情况下你不必检查值的NULL属性。 考虑使用ENUM数据列。 如果你拥有的某个数据列的基数非常低(
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MySQL 数据库 技巧 优化 数据类型 使用