Schema与数据类型优化

编程技术  /  houtizong 发布于 3年前   182

目前商城的数据库设计真是一塌糊涂，表堆叠让人不忍直视，无脑的架构师，说了也不听。在数据库设计之初，就应该仔细揣摩可能会有哪些查询，有没有更复杂的查询，而不是仅仅突出很表面的业务需求，这样做会让你的数据库性能成倍提高，当然，丑陋的架构师是不会这样去考虑问题的。选择优化的数据类型 1 更小的通常更好   更小的数据类型通常更快，因为他们占用更少的磁盘、内存和cpu缓存，并且处理时需要的cpu周期也更少。  2 简单就好   简单类型的操作通常需要更少的cpu周期，整形比字符操作代价更低，因为字符集的校对和排序规则更复杂，   使用mysql内建的类型而不是字符串来存储日期和时间，使用整型来存储IP地址。  3 尽量避免NULL   NULL是列的默认属性，如果查询中包含可为null的列，队mysql来说更难优化，因为可为null的列使得索引、索引统计、   和值比较都变得更加复杂。可为null的列会使用更多的存储空间，在mysql理也需要特殊处理，可为null的   列在被索引时，每个索引记录都需要一个额外的字节。   通常把可为null的列改为not null所带累的性能提升比较小，所以调优时没有比亚首先做这样一件事。    4 字符存储是varchar char(这里假设都使用InnoDB存储引擎)   1 字符串的最大长度比平均长度大很多   2 列的更新很少 所以碎片不是问题   3 使用了utf8这样复杂的字符集，每个字符都使用不同的字节数进行存储。      使用varchar(5) 和 varchar(200) 有什么区别吗？   事实证明，前者比后者有更大的优势，更长的列会消耗更多的内存，因为mysql通常会分配固定大小的内存块   来保存内部值，尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时   也同样槽糕。   所以最好的策略是只分配真正需要的空间。   char 是定长的，没什么好说。      blob 和text类型   blob 和 text 分别采用二进制和字符的方式进行存储。mysql 以对象的形式处理这两种类型。   由于blob 和text 不被直接作为索引，所以如果使用了blob或text 列来查询的话将不得不使用   MyISAM磁盘临时表，这会导致严重的性能开销，即使配置mysql将临时表存储在内存块设备上，依然需要许多   昂贵的系统调用。      最好的解决办法是尽量避免使用blob 和 text 类型。   定长的表好像是static的，查询速度会非常快。   永远记住 越小的列查询速度会越快。      如果实在无法避免，有一个技巧是在所有用到blob字段的地方都使用如果实在无法避免，有一个技巧是在所有用到blob字段的地方都使用   substring(column,length)将列值转换为字符串，这样就可以使用内存临时表了。  如果无法避免的使用了blob或者text ，则在sql执行操作完之后，执行optimize table table_name;即可清理mysql数据库碎片。  对数据库碎片的理解，这里只做精简的说明，当你向含有blob或text的表中插入数据之后再删除，查看所在库下的table_name.MDY文件，被删除的数据仍然占用着存储空间，查询执行时仍然会浪费cpu资源或者I/O资源。      如果执行计划explain 的extra 列包含 using temproray 说明这个查询使用了隐式临时表。      尝试使用枚举enum代替某些不变的varchar类型，这会让查询的速度更快。      不要冒险使用bit,不同版本的mysql对待bit的处理完全不同，尽量避免它。      不要相信任何所谓的高性能ORM框架，他们根本不懂得选择最优的存储类型。    5 警惕schema 设计中的陷阱   1 太多的列     mysql 的存储引擎在工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据， 然后在服务器层将缓冲内容解码为各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是 非常高的。虽然mysql 能够单表承受几千个字段，但是我们必须秉承 简单就好的原则。 越简单 就真的 越简单。   2 太多的关联     自己和别人的经验告诉我们，如果希望查询执行的快速且并发性好，单个查询最好把关联控制在 12个以内，当然Mysql限制61张表以内。   3 全能的枚举     防止过度使用枚举，因为在mysql枚举列表中新增一个枚举值就要做一次alter table操作，当然，在 在较新版本中如果再列表的末尾增加可以避免。        6 范式化优缺点    范式：1 字段不可分割 2 有主键，非主键字段依赖主键 3 非主键字段互相不依赖优点：遵循范式的表更新操作通常比反范式化要快。因为范式化之后的表很少有重复数据，所以只需要修改更少的数据。      范式化的表更小，可以更好地放在内存中，所以执行会更快。  很少有冗余数据。    缺点：需要关联。 稍微复杂的查询就需要至少一次的关联。 7 反范式化的优缺点   优点：反范式的schema因为所有数据都在一张表中，可以很好地避免关联。         尤其是当数据比内存大时比关联要快的多，因为避免了随机I/O.  8 混用范式化和反范式化   通常情况下 单一的范式 和 反范式 都只存在与实验室里，在实际业务中多为混用。   适当的冗余、复制、缓存 和索引 可以高效的完成查询操作。    9 缓存表与汇总表   可以考虑将并发性要求非常高的表单独出来重新设计。举例为点击数。   点击数这张表我们可以尽可能的简单设计，每次点击都会更新计数，为了承受高并发的更新可以将表初始化为1000或者更多个槽，   更新计数语句由开始的 update hit set count = count+1 变成了 update hit count = count +1 where slot_id = rand()*1000;   这样就可以很巧妙的避免因为每一条记录上的全局互斥锁(mutex)。如果需要获取结果 select sun(count) from hit;这可以解决这个问题。   10 加快alter table 的操作速度    在alter 一张大表并且内存不足的情况下，尤其是在索引很多的时候，事情会变得非常糟糕，几个小时甚至是几天才能完成。一般而言 处理的方式有两种 一是主副库切换，在不提供服务的副库上alter之后，把主库切换到副库。二是影子拷贝，创建一张和源表无关的新表，具有相同的表结构，然后通过重命名和删除表操作交换两张表。alter操作不当会引起表重建，这样的代价是非常大的，比如说我们需要修改某一列。alter table fuck.tab modify column duration tinyint(3) not null default 5;如果数据量非常大的时候，这个操作会很漫长，他会拷贝整张表，甚至列的类型、大小等默认属性。理论上Mysql 可以跳过重新创建表的步骤，列的默认值实际上存放在.frm文件中，所以直接修改这个文件而无需修改表本身则可以避免重新整张表。但是直到5.5版本中mysql还不支持这种优化方法，modify column 的操作都会重新创建表。另一种方法则是 alter table fuck.tab alter column duration set default 5;这条语句直接修改 .frm文件，会非常快。