MySQL数据库技巧MySQL优化之数据类型的使用.docx
- 文档编号:2871197
- 上传时间:2022-11-16
- 格式:DOCX
- 页数:14
- 大小:33.82KB
MySQL数据库技巧MySQL优化之数据类型的使用.docx
《MySQL数据库技巧MySQL优化之数据类型的使用.docx》由会员分享,可在线阅读,更多相关《MySQL数据库技巧MySQL优化之数据类型的使用.docx(14页珍藏版)》请在冰豆网上搜索。
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(-9223372036854775808)~263-1(9223372036854775807),其精度为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.342689时,该列实际存储的是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.287659E-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(-922337203685477.5808)~263-1(922337203685477.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表示的是字符串可达到的最大长度。 v
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MySQL 数据库 技巧 优化 数据类型 使用