1、良好的逻辑设计和物理设计师高性能的基石
2、反范式的设计可以加快某些类型的查询
3、应该尽量使用可以正确存储数据的最小数据类型
4、整型字符操作代价更低
5、尽量避免用null,通常情况下最好指定列为not null,并设置默认值,因为可为null的列是得索引、索引统计和值比较都变的更复杂。特别是计划在列上建索引就更应该避免设置为null
6、timestamp只使用datetime一半的存储空间,当然允许的时间范围也要小得多,并且timestamp会根据时区变化,具有特殊的自动更新能力
7、类型(1个字节(byte)等于8位(bit),位上面存的是二进制0或1,有符号类型会用一个字节来存符号,变长类型会拿一个或多个字节来存长度)
1、整数类型:tinyint(1个字节,最大不超过255),smallint(2个字节,最大不超过65535),mediumint(3个字节,7位以下),int(4个字节,10位以下),bigint(8个字节,19位)
2、实数类型:因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用decimal(例如存储财务数据),但在数据量比较大的时候,可以考虑用bigint代替decimal,将需要存储的货币单位根据小数的位数乘相应的倍数即可,例如微信支付以分为单位,这样可以同时避免浮点存储计算不精确和decimal精确计算代价高的问题
3、varchar和char类型:varchar存储可变长字符串,它比定长类型更节省空间,需要使用一个到两个字节记录字符串长度,所以varchar最长不超过2个字节所能存储的整数,即smallint最大值65535;char类型是定长,适合存储很短的字符串,或所有制都接近同一个长度,非常适合存储密码,对于经常变更的数据char也比varchar更好,因为定长的char类型不容易产生碎片,对非常短的列,char比varchar在存储空间上也更有效率,因为varchar要一个记录长度的额外字节
4、blob和text类型:都是存储很大的数据的,分别采用二进制和字符方式存储;mysql把每个blob和text值当做一个独立的对象处理,当值太大时,innodb会使用专门的‘外部‘存储区域来进行存储,此时每个值在行内需要1-4个字节存储一个指针;blob没有排序规则和字符集,而text有字符集和排序规则;只对每个列的最前面max_sort_length字节而不是整个字符串做排序
5、enum枚举类型:mysql在存储枚举时非常紧凑,会根据列表值的数量压缩到一个或两个字节中
6、日期和时间类型:datetime能保存大范围的值,从1001年到9999年,精度为妙,与时区无关,使用8个字节的存储空间;timestamp保存了1970年1月1日以来的秒数,只是用了4个字节的存储空间,即int的最大存储值,只能表示1970到2038年,显示跟时区有关系
7、位数据类型:bit:对于大部分应用最好避免使用这种类型;set:集合,和枚举不一样的是枚举时单选,集合是多选,缺点是改变列的定义代价较高
8、减小主键的大小会使的非主键索引额变的更小
9、标识列(identifier colum),又称自增列,不一定要与主键搭配,一个表至多有一个标识列,只能是数字类型,可以通过set auto_increment_increment设置步长,即每次自增的大小,整数通常是标识列最好的选择
10、一旦选定了一种类型,要确保所有的关联表都使用同样的类型,混用不同的数据类型会导致性能问题
11、mysql schema设计应该注意的:不应该有太多的列;不应该有太多的关联,特别对于数据量大的表应该尽量避免关联查询,单个查询最好在12个表以内做关联(个人觉得12个太多了!!!);注意防止过度使用枚举
12、全文搜索引擎:Lucene或者Sphinx,最近发现如果要求不高5.7内置的ngram也是可以用的
13、物化视图:我所理解的物化视图就是每次当值有改变的时候去更新统计表,而不是每次需要展示统计数据的时候用count去查询;用触发器和存储过程可以实现物化视图
14、计数器表:比如记录网站的点击次数就可以用计数器表来实现(可以预先增加100行数据,然后选择随机行来进行更新最后进行汇总,这样来解决并发问题)
15、on duplicate key update 如果存在则更新
16、modify column 操作都将导致表重建,操作会很慢,alter column 语句会直接修改.frm文件而不涉及表数据,这个操作是非常快的,alter column 只能对默认值做修改