MySQL实战——Schema与数据类型优化

西木风落

已于 2022-03-14 09:52:28 修改

阅读量313

点赞数

分类专栏：数据库学习文章标签： Mysql Schema设计范式和反范式 alter table

于 2019-05-26 22:09:33 首次发布

本文链接：https://blog.csdn.net/chenwiehuang/article/details/90578732

版权

15 篇文章 1 订阅

订阅专栏

MySql支持的数据类型非常多，选择正确的数据类型对于获取高性能至关重要。数据类型的选取原则：

1）更小的通常更好。更小的数据类型通常更快，因为占用更小的磁盘空间、内存和CPU缓存，并且处理需要的CPU周期更少；
2）简单就好。简单的数据类型通常需要更少的CPU周期，比如，整型比字符串代价更低，内建的日期类型比字符串存储日期和时间更好；
3）尽量避免NULL。如果查询中包含NULL的列，使得索引、索引统计和值比较都更加复杂；当NULL的列被索引的时候，每个索引记录都需要一个额外的字节。
4）char与varchar。varchar类型可以存储变长的字符串，需要使用1或2个额外的字节来存储字符串长度。varchar虽然可能帮助节省空间，但是当进行update的时候，如果更新后的行比原来长，并且页内没有更多的空间存储，这种情况下，MyISAM会将行拆成不同的存储片段，InnoDB则会分裂页来使行存放到页内。通常，字符串的最大长度比平均长度大很多的时候，并且列的更新很少，或者是使用UTF-8这种每个字符都使用不同的字节数存储时，varchar更实用。
5）blob和text。MySQL把每个blob和text值都当作一个独立的对象处理。当blob和text值太大的时候，InnoDB会使用专门的外部存储区域来进行存储，此时每个值在行内需要1~4个字节存储指针，然后在外部存储实际值。

补充说明：

1）varchar(5)和varchar(200)效果是不同的，更长的列会消耗更多的内存，尤其是使用内存临时表做排序操作时。
2）MySQL对blob和text列进行排序与其它类型不同，只对每个列的max_sort_length字节进行排序，而不是整个列值。
3）因为Memory引擎不支持blob和text，如果查询使用到了blob或者text类型，将不得不使用MyIsam磁盘临时表，即使只有几行数据，这会导致严重的性能开销。最好的解决方式是避免使用blob和text类型，如果实在无法避免，可以在使用到blob字段的地方都使用subString(cloumn, length)，这样就可以使用内存表了。

1）太多的列

MySql的存储引擎API工作时，需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过后的列转换成行数据结构操作代价是非常高的。转换的代价依赖于列的数量。

2）太多的关联

MySQL限制每个关联操作最多只能有61张表。一个粗略的经验法则，如果希望执行快速并且性能好，最好在12个表内做关联。

3）全能枚举

不要为了使用枚举，就对枚举使用一个长度很长的范围，比如现在只可能使用到1-4枚举值，但为了扩展，就直接干到1-32.

4）不能因为NULL的问题，就全部不采用NULL默认值。MySQL会在索引中存储NULL值。

5）范式和反范式结合

范式的优点是：

范式的缺点是通常需要做表关联。

反范式化的优点是：

反范式的缺点是数据冗余，尤其是数据的一致性保证，可能会增加对数据操作的复杂性。

MySQL执行大部分修改表结构操作的方法是用心的结构创建一个空表，从旧表中查出所有的记录插入新表，然后删除旧表。这样的操作可能需要话费很长的时间。常见的做法有两种：

不是所有的alter table都会引起表的重建，如果需要修改某个列的默认值，理论上可以直接修改.frm文件而不需要改动表本身。

关注

专栏目录