高性能Mysql笔记 (4) --库表结构优化

选择正确的数据类型

为标识列(如userID)选择合适的数据类型非常重要。

1使用内置类型而非字符串存储日期和时间
2使用整形存储ip地址 （INET_ATON）
3除非真的需要存储NULL值（如稀疏表），否则指定列为NOT NULL
可使用 0 “” 等特殊字符代替NULL

4对于存储和计算 INT(1) 和 INT(20)是相同的，这种指定只是规定了MYSQL和一些交互工具用来显示字符的个数。因此这种指定大部分情况无实际意义。

5FLOAT 和DOUBLE 存储小数只是近似计算，DECIMAL用于存储精确的小数。 CPU不支持对DECIMAL的直接计算。 基于DECIMAL的运算，要比浮点数慢。所以应该尽量只在对小数进行精确计算时才使用DECIMAL。实际使用中，大家选择总会出现各种问题，因此执行
【强制】小数类型为decimal，禁止使用float和double。

6【强制】如果存储的字符串长度几乎相等，使用CHAR定长字符串类型。
如存储密码的MD5值
VARCHAR需要使用1或2个额外字节记录字符串的长度，但由于行是可变长度的，在UPDATE时就可能变得比原来长，这可能导致分页。
使用了utf-8 这样的字符集后，每个字符使用的字节数可能不同，因此建议使用VARCHAR

7【强制】varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度大于此值，定义字段类型为TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

8 日期尽量使用TIMESTAPM

schema 设计中的陷阱

太多的列？？
太多的关联， 单个查询不要关联太多(超过10个)的表。 实际使用时最好没有join
【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
说明：（概念解释）学生表中的student_id是主键，那么成绩表中的student_id则为外键。如果更新学生表中的student_id，同时触发成绩表中的student_id更新，则为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

范式与反范式

范式化的表通常更小，可以更好的放入到内存， 没有或者很少有数据冗余。
但稍微复杂一点的select操作就需要join

【推荐】字段允许适当冗余，以提高性能，但是必须考虑数据同步的情况。冗余字段应遵循：
 1）不是频繁修改的字段。
 2）不是varchar超长字段，更不能是text字段。
正例：各业务线经常冗余存储商品名称，避免查询时需要调用IC服务获取。

缓存表和汇总表

缓存表：可以较简单的从其他表获取的数据表， 但该获取过程速度比较慢。
汇总表：保存了使用group by 语句聚合的数据表。 如前24小时内发送的消息数。

alter table

mysql 执行大部分修改表结构操作的方法是
用新结构创建一个空表 从旧表中查出所有数据插入到新表 删除旧表