当前位置:首页 > 开发 > IT生活 > 正文

Mysql数据库优化 列的选择

发表于: 2013-07-07   作者:coollyj   来源:转载   浏览次数:
摘要: 对一个设计拙劣或者没有做index的schma进行优化,可以很大程度的改善性能。 如果想获得很好的性能,就需要对你运行的特定的queries来设计schema和索引, 你需要估计不同类query的性能需求,和使用的频度,以及需要检索的字段和检索 的条件。优化是一个权衡,索引可以提高检索速度,但是会降低更新速度,一个反 范式的schema可能会在某些查询下加快速度,但在其他情况下可能降低速度
对一个设计拙劣或者没有做index的schma进行优化,可以很大程度的改善性能。
如果想获得很好的性能,就需要对你运行的特定的queries来设计schema和索引,
你需要估计不同类query的性能需求,和使用的频度,以及需要检索的字段和检索
的条件。优化是一个权衡,索引可以提高检索速度,但是会降低更新速度,一个反
范式的schema可能会在某些查询下加快速度,但在其他情况下可能降低速度。添加
冗余字段、计数器、summary表可以优化查询,但是维护起来很困难。

选择最优的数据类型:
Mysql支持大量的各种个样的数据类型,如何正确的选择类型来存储数据是产生高性能
的关键。
1.足够大的越小的类型越好
小的类型通常更高效,因为他们使用了更少的磁盘空间、内存和cpu高速缓存,需要更少
的CPU时钟周期。但是也不要低估的存储值的范围,增大schema多个地方的数据类型也是
一件痛苦和耗时的操作。

2.简单就好:
简单的类型需要更少的时钟周期,整数的比较要比字符串的比较要快。,比如你应该存储
mysql内置的类型而不是字符串来存贮日期和时间,使用整数来存储ip。

3.尽可能避免null字段:
尽可能的在定义的字段上添加NOT NULL,MySQL对于可以为null的列很难优化,
因为它让索引、索引统计以及值的比较变得复杂,nullable的列会使用更多的存储空间,并且需要一些特殊处理,对nullable的列建索引,每个entry需要额外的数据,在MyISAM甚至会使定长索引变成变长的。对于没有值的列,你可以使用一些特殊的值来代表,比如0,空字符串。将null变成not null可能对性能的提高不是很大,但是如果你打算对一个列见索引,那么要避免它是nullable的。

选择一个列的数据类型,首先决定大体上是什么类型的:整数、字符串等等。然后在再这些类型中选择合适的MySQL提供的类型。比如DATETIME和TIMESTAMPE可以存储相同的数据:日期和时间,精度为1秒。但是Timestamp使用了一半的空间,并且是时区敏感和一些特殊的自动更新的功能。但是具有很小的值的范围,到2038年就over了。
MYSQL为了兼容性提供了很多的别名,比如INTEGER,BOOL,NUMERIC,他们只是别名,不影响性能。

数字类型:
数字类型包括整数和浮点数类型,整数类型tinyint,smallint,mediumint,int,
bigint,他们分别占8,16,24,32,64个字节。整数类型可以使用unsigned属性,
tinyint unsigned可以存储0~255。MySQL的整数计算通常使用的是64位的bigint。
MySQL可以指定整数类型的宽度,但是这个宽度并不是限制数据的范围,只是用于指定交互式的工具显示保留的字符个数,在存储上INT(1)和INT(20)是等价的。

浮点类型:
浮点书类型有小数部分,但是他们并不是仅仅针对浮点数的,你可以使用DECIMAL来表示BIGINT存储不下的整数,MySQL支持两类:精确和非精确类型。
FLOAT和DOUBLE支持近似的标准的浮点计算,如果你需要知道计算的精确结果,你需要平台下浮点数的实现。DECIMAL可以存储精确的分数,MySQL5.0以上支持精确的数学计算,但是浮点类型的数学计算效率会更高,存储空间更小。浮点类型和DECIMAL都可以指定精度,比如DECIMAL(18,9).由于空间和计算的效率问题,只有需要精确的结果时,我们才使用DECIMAL。

字符串类型:
MySQL支持很多的字符串类型,从版本4.1开始,支持每个列使用一种字符集,如果这么做,会很大的影响性能。
VARCHAR和CHAR类型:
有两种主要的字符串类型VARCHAR和CHAR:
VARCHAR主要存储变长的字符类型,会比固定长度的类型要节省空间,因为它只需要和实际需要用的空间一样大,VARCHAR指定的长度并不是申请那么大的空间,只是最大可能占用的空间。使用1,2个额外的字节来存储长度,比如VARCHAR(10)最大会占11个字节,VARCHAR(1000)最大会占用1002个字节。
由于VARCHAR能够节省空间,所以有助于提高性能,但是由于行是变长的,如果行大到无法在原来的位置存储,那么在MyISAM会产生行碎片,InnoDB会分页。
MySQL 5.0以上不会自动删除掉结尾的空白。
通常在最长的数据要比平均的长度大很多,不会频繁更新这个字段的情况下使用VARCHAR。

CHAR是固定长度的类型,它会申请指定长度的空间。存储的时候默认会删除结尾的空白,
如果存储短的,并且长度差不多的字符串,使用CHAR是非常好的,比如存储MD5的用户密码。
对于频繁跟新的字段,使用CHAR也是比较好的选择,可以使用CHAR(1)来代表Y和N的值,这比VARCHAR(1)少用一个字节。

BINARY和VARBINARY类型用于存储二进制的字符串,和传统的字符串类似,只是存储的是字节而不是字母。BINARY字符串的比较要比普通字符串要快。

BLOB和TEXT类型:
BLOB和TEXT是用来存储大规模的数据的,分别用于二进制和字符串类型。还有其他同族的类型:TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT,和二进制类型TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB.
BLOB和TEXT的区别是一个用来存储二进制的,一个用来存储文本的他有字符集和编码。

BLOB和TEXT类型和其他类型排序不同,他不会将全部长度的字符串排序,他只会排序前
max_sort_length字节。如果你需要排序跟少的字符你可以减少max_sort_length,或者使用order by substring(column,length)。MySQL不能对这些类型的全部长度建索引,所以不能够使用索引来排序。

内存存储引擎不支持BLOB和TEXT类型,所以使用BLOB和TEXT列的查询,隐含的使用了On-disk临时表。即使你配置MySQL临时表在内存中,仍然需要很昂贵的系统调用。最佳的实践是不要使用BLOB和TEXT类型除非你真的需要,如果不可避免,那么可以使substring(column,length)类转换成字符串类型,这样可以使用in-memory表。需要确认使用足有短的substring,临时表的大小没有超出max_head_table_size或者tmp_table_size,否则会使用on-disk MyISAM表。

EXPLAIN的Extra列包含“Using temporary"表明这个查询使用了隐式的临时表。

使用ENUM来代替字符串类型
有时候我们可以使用ENUM列来代替传统的字符串类型,ENUM列可以存储65535个不同的字符串值。MySQL存储他们非常的紧凑,压缩为1~2个字节。每个值的内部存储都是一个整数,在.frm文件保存了数字到字符串的一个映射。

引用

mysql> CREATE TABLE enum_test(
->
e ENUM('fish', 'apple', 'dog') NOT NULL
-> );
mysql> INSERT INTO enum_test(e) VALUES('fish'), ('dog'), ('apple');

mysql> SELECT e + 0 FROM enum_test;
+-------+
| e + 0 |
+-------+
|
1 |
|
3 |
|
2 |
+-------+


ENUM字段排序是按照内部存储的整数排的,你可以显示的使用FIELD来指定排序的方式。
引用

mysql> SELECT e FROM enum_test ORDER BY FIELD(e, 'apple', 'dog', 'fish');
+-------+
| e
|
+-------+
| apple |
| dog
|
| fish |
+-------+

ENUM最大的缺点是字符串列表是固定大小的,添加和删除一个字符串需要使用ALTER TABLE,所以如果你表示的字符串有可能在将来改变,那么使用ENUM并不是一个好的主意。
如果ENUM需要和char,varchar进行join操作,那么会慢一些,这时候最好同一类型,
但是使用ENUM能够比使用char,varchar节省不少空间的。

日期和时间类型:
MySQL有各种日期和时间的类型,比如YEAR,DATE,MySQL能存储的最小时间粒度是秒,但是可以在微妙级别做临时的运算。

DATETIME:
这个类型能够保存更大范围的值,从1001到9999,精度为1秒,他将日期和时间存储为整数格式为YYYYMMDDHHMMSS,和时区无关,使用了8个字节。

TIMESTAMP:
TIMESTAMP按照字面意思,存储了从1970.1.1流失了多少秒,和Unix的timestamp一样。
TIMESTAMP仅使用了4个自己,所以要比DATETIME小很多:从1970到2038. MySQL提供了
FROM_UNIXTIME()和UNIX_TIMESTAMP()函数来将Unix时间戳和日期转换。
TIMESTAMP是和时区相关的。
时间戳有一些DATETIME没有的功能,他没在插入数据的时候,如果该列为空,则自动将当前时间插入。当更新一行数据时,这行数据的TIMESTAMP列默认会跟新为当前时间,除非显式的指定值,TIMESTAMP列默认是NOT NULL的。

一般来说你应该使用TIMESTAMP,因为他比DATETIME占用更少的空间。有一些人喜欢存储
Unix时间戳为一个整数,但常常没有给你带来任何好处,因为这种格式不方便处理,我们并不推荐这么用。

Bit压缩类型(Bit-packed Data Types)
MySQL提供了一些存储类型,他每一个bit存储一个值来的更紧凑的存储数据方式。所有的
这些类型都是字符串类型,不管底层的存储格式和操作:
BIT 在MySQL5.0之前,BIT是TINYINT的同义词。但是在5.0之后,它是一个完全不同的类型,具有特殊的特性:你可以使用BIT列来存储true/false值。BIT(1)定义了包含了单个比特,BIT(2)存储了两个bit,BIT字段的最大值是64bit。
BIT类型的行为在不同的存储引擎中不同,MyISAM将这些列一起压缩存储,所以17个BIT列只需要17bit的存储空间(没有列是NULL的)。其他的存储引擎比如Memory和InnoDB,使用足够大的integer类型来存储,所以使用BIT并没有节省多少空间。
MySQL将BIT类型视为字符串类型而不是数字类型。如果使用BIT(1),查询出来的是一个字符串,内容是二进制的0或者1,但不是ASCCII码的‘0’,’1‘的值。
但是在数字的场景下使用,其值是字符串二进制表示的值,比如b'00111001'为57.
这可能会比较让人费解,所以我们建议谨慎的使用BIT,在大多数的应用中,避免使用。
如果想存储true/false中使用单个bit的存储空间,那么另外的选择是使用varchar(0),
这个列可以存储NULL或者空字符串。

SET
如果你想存储很多的true/false值,那么考虑将很多列组合成1列,使用SET数据类型,内部表示为压缩的bit集合。MySQL有函数FIND_IN_SET()和FIELD(),让在查询中使用
更容易。主要的缺点是,修改需要ALTER TABLE.

在整数列使用bit操作
另一种对于SET的选择是使用整数作为压缩的比特集合,比如你可以在TINYINT类型使用
8个bit,然后通过bit操作。这种方式相对于SET的最大有点是修改这个字段不需要ALTER TABLE。缺点是比较拿写和理解,位操作,有人喜欢有人不喜欢,所以完全看你的口味了。

选择主键:
选择一个主键的正确类型是非常重要的。你经常使用这些列来和其他的值比较,或者使用它来查找,在其他的表中作为外键。选择主键不仅仅药考虑存储的类型,还要考虑比较和计算的性能。比如MySQL存储ENUM和SET类型的内部形式是整数,但是在字符串的环境下会转化为字符串。
一旦选择好一个类型,要确保所有关联的表对应的外键具有相同的类型。类型要精确的匹配比较好,除了UNSIGNED。混用不同的类型可能导致性能问题,并且隐式的类型转换可能会带来错误。

选择能够包含数据区间的最小的类型,比如你想用state_id字段来存储美国的州,那么TINYINT已经足够。
整数类型:
通常是最佳的主键类型的选择,因为它速度快并且可以自增。
ENUM和SET:
通常是一个比较糟糕的选择,他们比较适合作为状态,类型的值。
String类型:
尽可能避免使用字符串类型的主键,因为占用较多的空间并且比整数慢。使用MyISAM的表更应该特别谨慎使用字符串类型的主键,因为他会默认压缩字符串索引,这会导致查询非常慢。
使用packed索引大约要慢六倍。
使用完全随机的字符串,比如MD5(),SHA1(),UUID(),要特别小心,每一个新生成的值都会
在一个很大空间的一个随机的值,这将会降低插入和部分查询语句。
INSERT慢的原因是插入的值在索引的随机位置,这会导致页的分裂或者磁盘的随机访问,对于聚集存储引擎会引起聚集索引碎片。
SELECT慢的原因逻辑相邻的行将会在磁盘和内存分散。
随机的值也会导致所有类型查询的缓存表现很差。

特殊类型数据:
比如很多人使用VARCHAR(15)来表示IP,其实IP是一个无符号的32为整数,所以存储IP地址为整数是一个比较好的方法。MySQL提供了INET_ATON()和INET_NTOA函数来转换这两种表示。

参考《高性能MySQL》

Mysql数据库优化 列的选择

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
思维导图 点击图片,可查看大图。 介绍 情况:如果你的表结构设计不良或你的索引设计不佳,那么请你
开启定时任务 SHOW VARIABLES LIKE 'event%'; -- 查看定时任务是否开启(off:关闭 on:开启) set
0. 为查询缓存优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而
MySql支持多种不同的数据类型,选择正确的类型对于获得高性能至关重要,下面原则有助于做出选择: 1
11.5.2 MySQL数据库的优化 很多接触过MySQL数据库的用户都知道MySQL数据库的优化可以通过修改配置参
MYSQL数据库优化方向 sql及索引优化,存储优化(程序) 数据库表结构优化 系统配置 硬件 一、SQL语
本篇文章是对mysql数据库优化进行了详细的总结与介绍,需要的朋友参考一下 1. 优化你的MySQL查询缓
1. 优化你的MySQL查询缓存 在MySQL服务器上进行查询,可以启用高速查询缓存。让数据库引擎在后台悄
对于一个以数据为中心的应用,数据库的好坏直接影响到程序的性能,因此数据库性能至关重要。一般来
Mysql数据库的优化技术 对mysql优化时一个综合性的技术,主要包括 a: 表的设计合理化(符合3NF) b:
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号