“良好的逻辑设计和物理设计是高性能的基石，应该根据将要执行的查询语句来设计schema，这往往需要权衡各种因素”。
适合的数据类型
MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。
- 更小的通常更好：更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
- 简单就好：简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和排序规则使字符比整型更复杂。
- 尽量避免NULL:可为NULL的列使得索引、索引统计和值比较都更复杂，会使用更过的存储空间。
在为列选择数据类型时，第一步需要确定合适的大类型：数字、字符串、时间等。第二步是选择具体类型，很多数据类型可以存储相同类型的数据，但是存储的长度和范围不一样、允许的精度不同。
整数类型
TINYINT、SMAILLINT、MEDIUMINT、INT、BIGINT，分别使用8、16、24、32、64位存储空间，可以存储的值的范围-2（n-1）~2（n-1） -1（n代表位数）。
整数类型有可选的UNSIGNED属性，表示不允许负值，例如TINYINT UNSIGNED的存储范围是0255，而TINYINT的存储范围是-128127。
可以为整数类型指定宽度，例如INT(11)，对大多数应用这是没有意义的，它不会限制值的合法范围，只是规定了MySQL的一些交互工具用来显示字符的个数，对于存储和计算来说，INT(1)和INT(20)是相同的。
实数类型
实数是带有小数部分的数字。FLOAT（4个字节）和DOUBLE（8个字节）类型支持使用标准的浮点运算进行近似计算。DECIMAL类型用于存储精确的小数，支持精确计算（MySQL自己实现了DECIMAL的高精度计算，CPU直接支持原生浮点计算，所以浮点计算更快）。
浮点和DECIMAL类型都可以指定精度。对于DECIMAL可以指定小数点前后所允许的最大位数，将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如，DECIMAL(18,9)小数点两边各存储9个数字，一共占9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身用1个字节。
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL，例如存储财务数据。但是数据量比较大的时候，可以考虑使用BIGINT代替，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。
字符串类型
VARCHAR类型用于存储可变长字符串，需要使用1或2个额外字节记录字符串的长度，最大长度不能超过65535字节。当字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储适合使用VARCHAR。
CHAR类型是定长的，适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR不容易产生碎片，对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率，例如用CHAR(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)取需要两个字节，因为还有一个记录长度的额外字节。
注：使用VARCHAR(5)和VARCHAR(200)存储"hello"的空间开销是一样的，但是更长的列会消耗更多的内存，因为可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。所以最好的策略是只分配真正需要的空间。
BLOB和TEXT类型
为存储很大的数据而设计的可变长字符串数据类型，分别采用二进制和字符方式存储。字符类型是TINYTEXT、SMALLTEXT(TEXT)、MEDIUMTEXT、LONGTEXT，对应的二进制类型是TINYBLOB、SMALLBLOB(BLOB)、MEDUIMBLOB、LONGBLOB。通过最大字符数来限制，TEXT的最大长度为65535(216-1)个字符。
日期和时间类型
DATETIME从1001年到9999年，精度为秒。把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节的存储空间。例如，"2018-01-01 08:00:00"。
TIMESTAMP保存了从格林威治时间1970年01月01日00时00分00秒起至现在的秒数，只使用4个字节的存储空间，只能表示从1970年到2038年。
如果在多个时区存储或访问数据，TIMESTAMP和DATETIME的行为将很不一样，前者提供的值与时区有关，后者则保留文本表示的日期和时间。TIMESTAMP可以在插入时自动生成当前时间戳，以及在修改时根据当前时间戳自动更新。
除了特殊行为外，通常应该尽量使用TIMESTAMP，因为它比DATETIME空间效率更高。用整数保存时间戳的格式通常不方便处理，不推荐这样做。
BIT、SET、ENUM
BIT的最大长度是64位。在MyISAM中，BIT的存储空间很小，是真正的实现了通过bit来存储，但是在其他的一些存储引擎中就不一样了，因为它们是转换为最小的INT类型存储的，所以占用的空间也没有节省，还不如直接使用INT类的数据类型存放来得直观。
对于SET和ENUM类型，主要内容基本处于较少变化状态且值比较少的字段。虽然这两个字段所占用的存储空间都较少，但是由于在使用方面较其他的数据类型要略为复杂一些，所以在实际环境中一般使用还是较少。
选择标识符
选择合适的标识符是非常重要。选择时不仅应该考虑存储类型，而且应该考虑MySQL是怎样进行运算和比较的。一旦选定数据类型，要确保保证所有关联表中都使用相同的数据类型。
在可以满足值的范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型。
整数类型：整数通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT。
字符串类型：尽量避免使用字符串类型作为标识符，因为它们很消耗空间，并且通常比数字类型慢。而且，对于随机的字符串，它们在索引中的位置也是随机的，这会导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引分裂 。
优化Schema设计
混用范式和反范式，适度冗余
范式的优点就是让数据库中尽量的去除数据的冗余，保持数据的一致，使数据的修改简单，然而它的缺点是通常需要关联，这不但代价昂贵，也可能使一些索引策略无效。反范式因为所有数据都在一张表中，可以很好地避免关联。
虽然冗余字段的更新成本增加了，但是查询效率提高了，而且大多冗余字段的选取是查询频率远大于更新频率的字段。
虽然范式化的数据库中的表一般都较小，使表中相关列最少，在某些情况下增强了数据库的可维护性，但在系统要完成一些数据查询时，可能要用复杂的Join才能实现，这势必会造成查询性能的低下，如果通过拆分Join，通过多次简单的查询来在应用中实现Join逻辑，将会带来巨大的网络开销。
大字段垂直拆分
适度冗余策略是将别的表中的字段拿过来在自己身上也存一份数据，而大字段垂直拆分简单来说就是将自己身上的字段拆分出去放在另外（单独）的表里面。自相矛盾？不，将别表的字段拿过来，是因为很多时候查询需要使用该字段，为了减少Join带来的性能消耗。而将大字段拿出去，是在大部分查询不需要使用该字段的时候才会拿出去。首先大字段（文章的内容、帖子的内容、产品的介绍等），其次是和表中其他字段相比访问频率明显要少的字段很多适合拆分出去。
大字段存放的内容较多，占整条记录的80%以上，而数据库中数据在数据文件中的格式一般都是以一条一条记录为单位来存放，也就是说，要查询某些记录的某几个字段，数据库并不是只需要访问这几个字段，而是需要读取其它所有字段（可以在索引中完成整个查询的情况除外），这就不得不读取包括大字段在内的很多并不相干的数据，而由于大字段所占的空间比例非常大，自然所浪费的IO资源也就非常大了。故将该大字段从原表中拆分出来，通过单独的表进行存放，这样在访问其它数据时大大降低IO访问，从而使查询性能得到较大的改善。
实际上，不一定非要大字段才能进行垂直拆分，某些场景下，有的表中大部分字段平时都很少访问，而其中的某几个字段却是访问频率非常高，对于这种表，也非常适合通过垂直拆分来达到优化性能的目的。
大表水平拆分
举个例子，某论坛系统有张post表存放帖子数据，现在有个需求，系统管理员能够发布系统消息，并且在贴子的每一页置顶显示。第一反应肯定是通过在post表中增加一个标识列，用来存放帖子的类型，标识出是用户的普通贴还是管理员的置顶贴，然后在每个列表展示页面都通过对post表的两次查询（一次置顶贴，一个普通贴）并在应用程序中合并再展示。这样可能造成的结果是由于整个post表的数据较大，查询置顶的Query成本会相对有些高。
置顶信息和普通帖完全不会产生任何关联交互、置顶信息的变化相对于普通帖来说变化很少、置顶消息的访问频率非常高、置顶信息的量和普通帖子来比非常之少。经过这几个分析，可以将置顶消息单独存放在普通贴之外的其它表里面。由于访问频率非常高，这种方案使得每次检索置顶消息的成本下降，数量少而且变化不怎么频繁的特点则非常适合使用MySQL的Query Cache，而如果和普通贴存放在一起会由于普通贴的频繁变化带来post表相关Query Cache失效。
统计表准实时优化
通过定时统计数据来替代实时统计查询。 因为实时统计的性能消耗成本太高，每一次展示都需要进行统计计算，带来大量的重复资源浪费。对于准确性要求并不是特别严格、对时间并不是太敏感、访问非常频繁，重复执行较多、参与统计数据量较大这类统计数据可以准实时统计表来优化。例如，系统当前在线人数，论坛系统当前总帖数、回帖数，虚拟积分排名等。
这些统计的计算都会涉及到大量数据，同时也需要大量的计算资源，访问频率也都非常的高。如果都通过实时统计，只要数据量稍微大些，都会带来非常大的硬件资源开销。但在短时间内的不够精确，并不会带来太大用户体验的降低，所以完全可以通过定时任务程序，每隔一定时间段进行一次统计后存放在专门设计的统计表中。
参考
- 《高性能MySQL》
- 《MySQL性能调优与架构设计》
网友评论