吉林建设集团网站,做食品网站需要什么资质吗,网站后台登陆地址,北京和君网站建设Schema与数据类型优化
BLOB和TEXT类型
BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型#xff0c;分别采用二进制和字符方式存储。 实际上它们分别属于两组不同的数据类型家族:字符类型是TINYTEXT#xff0c;SMALLTEXT,TEXT#xff0c;MEDIUMTEXT#xff0c;LONG…Schema与数据类型优化
BLOB和TEXT类型
BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型分别采用二进制和字符方式存储。 实际上它们分别属于两组不同的数据类型家族:字符类型是TINYTEXTSMALLTEXT,TEXTMEDIUMTEXTLONGTEXT;对应的二进制类型是TINYBLOBSMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB.BLOB是SMALLBLOB的同义词TEXT是SMALLTEXT的同义词。 与其他类型不同MySQL把每个BLOB和TEXT值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当BLOB和TEXT值太大时InnoDB会使用专门的外部存储区域来进行存储此时每个值在行内需要1~4个字节存储一个指针然后再外部存储区域存储实际的值。 BLOB和TEXT家族之间仅有的不同时BLOB类型存储的是二进制数据没有排序规则或字符集而TEXT类型有字符集和排序规则。 MySQL对BLOB和TEXT列进行排序与其他类型是不同的:它只对每个列的最前max_sort_length字节而不是整个字符串做排序。如果只需要排序前面一小部分字符则可以减小max_sort_length的配置或者使用ORDER BY SUBSTRING(column, length). MySQL不能将BLOB和TEXT列全部长度的字符串进行索引也不能使用这些索引消除排序
磁盘临时表和文件排序
因为Memory引擎不支持BLOB和TEXT类型所以如果查询使用了BLOB或TEXT列并且需要使用隐式临时表将不得不使用MyISAM磁盘临时表。即使只有几行数据也是如此(Percona Server的Memory引擎支持BLOB和TEXT类型同样的场景下还是需要使用磁盘临时表)。这会导致严重的性能开销。即使配置MySQL将临时表存储再内存块设备上(RAM Disk)依然需要许多昂贵的系统调用。最好的解决方案是尽量避免使用BLOB和TEXT类型。如果实在无法避免有一个技巧是在所有用到BLOB字段的地方都使用SUBSTRING(column, length)将列值转换为字符串(在ORDER BY 子句中也适用)这样就可以使用内存临时表了。但是要确保截取的子字符串足够短不会使临时表的大小超过max_heap_table_size或tmp_table_size超过以后MySQL会将内存临时表转换为MyISAM磁盘临时表。 最坏情况下的长度分配对于排序的时候也是一样的所以这一招对于内存中创建大临时表和文件排序以及在磁盘上创建大临时表和文件排序这两种情况都很有帮助。
例如假设有一个1000万行的表占用几个GB的磁盘空间。其中有一个uft8字符集的VARCHAR(1000)的列每个字符最多使用3个字节最坏情况下需要3000字节的空间。如果在ORDER BY 中用到这个列并且查询扫描整个表为了排序就需要超过30GB的临时表。 如果EXPLAIN执行计划的Extra列包含了Using temporary则说明这个查询使用了隐式临时表
使用枚举类型(ENUM)代替字符串类型
有时候可以使用枚举列代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑会根据列表值得数量压缩到一个或者两个字节中。MySQL会在内部将每个值在列表中得为止保存为整数并且在表的.frm文件中保存数字-字符串映射关系的查找表 例如
mysql CREATE TABLE enum_test(e ENUM(fish, apple, dog) NOT NULL);
Query OK, 0 rows affected (0.03 sec)
mysql INSERT INTO enum_test(e) VALUES(fish), (dog),(apple);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0这三行数据实际存储为整数而不是字符串。可以通过在数字上下问环境检索看到这个双重属性:
mysql SELECT e0 FROM enum_test;
-----
| e0 |
-----
| 1 |
| 3 |
| 2 |
-----
3 rows in set (0.05 sec)如果使用数字作为ENUM枚举常量这种双重性很容易导致混乱例如ENUM(‘1’,‘2’,‘3’).建议尽量避免这么做。另外一个让人吃惊的地方时枚举字段是按照内部存储的整数而不是定义的字符串进行排序的:
mysql SELECT e FROM enum_test ORDER BY e;
-------
| e |
-------
| fish |
| apple |
| dog |
-------
3 rows in set (0.05 sec)一种绕过这种限制的方式是按照需要的顺序来定义枚举列。另外也可以在查询中使用FIELD()函数显式地指定排序顺序但这会导致MySQL无法利用索引消除排序。 mysql SELECT e FROM enum_test ORDER BY FIELD(e, apple, dog,fish);
-------
| e |
-------
| apple |
| dog |
| fish |
-------
3 rows in set (0.07 sec)如果在定义时就是按照字母的顺序就没有必要这么做了。枚举最不好的地方是字符串列表是固定的添加或删除字符串必须使用ALTER TABLE,因此对于一系列未来可能会改变的字符串使用枚举不是一个好主意除非能接受只在列表末尾添加元素这样在MySQL5.1中就可以不用重建整个表来完成修改。 由于MySQL把每个枚举值保存为整数并且必须进行查找才能转换为字符串所以枚举列有一些开销。通常枚举的列表都比较小所以开销还可以控制但也不能保证一直如此。在特定情况下把CHAR/VARCHAR列与枚举列进行关联可能会比直接关联(CHAR/VARCHAR)列更慢。 为了说明这个情况读一个应用中的一张表进行了基准测试看看在MySQL中执行上面说的关联的速度如何。该表有一个很大的主键:
CREATE TABLE webservicecalls(
day date NOT NULL,
account smallint NOT NULL,
service varchar(10) NOT NULL,
method varchar(50) NOT NULL,
calls int NOT NULL,
items int NOT NULL,
time float NOT NULL,
cost decimal(9,5) NOT NULL,
updated datetime,
PRIMARY KEY(day,account, service, method)
) ENGINEInnoDB;这个表有11万行数据只有10MB大小所以可以完全载入内存。service列包含了5个不同的值平均长度为4个字符method列包含了71个值平均产犊为20个字符。 复制一下这个表但是把service和method字段换成枚举类型表结构如下: CREATE TABLE webservicecalls_enum(
...omitted...
service ENUM(... VALUES omitted ...) NOT NULL,
method ENUM(... VALUES omitted ...) NOT NULL,
...omitted...
) ENGINEInnoDB;然后我们用主键列关联这两个表下面是所使用的查询语句:
mysql SELECT SQL_NO_CACHE COUNT(*) FROM webservicecalls JOIN webservicecalls USING(day, account,service,method);用VARCHAR和ENUM分别测试了这个语句结果如表所示
从上面的结果可以看到当把列都转换成ENUM以后关联变得很快。但是当VARCHAR列和ENUM列进行关联时则慢很多。在本例中如果不是必须和VARCHAR列进行关联那么转换这些列为ENUM就是个好主意。这是一个通用的设计时间在查找表时采用整数主键而避免采用基于字符串的值进行关联。然而转换列为枚举型还有另外一个好处。根据SHOW TABLE STATUS命令输出结果中Data_length列的值把这两列转换为ENUM可以让表的大小缩小1/3.在某些情况下即使可能出现ENUM和VARCHAR进行关联的情况这也是值得的(这很可能可以节省IO)。同样转换后主键也只有原来的一半大小了因为这是InnoDB表如果表上有其他索引减小主键大小会使得非主键索引也变得更小。 (该图只是查询Data_length与上面的例子无关)
