MySQL优化之表结构优化

　　殊不知，在N年前被奉为“圣经”的数据库设计3范式早就已经不完全适用了。这里我整理了一些比较常见的数据库表结构方面的优化技巧，希望对大家有用。

　　由于MySQL数据库是基于行(Row)存储的数据库，而数据库操作IO的时候是以page(block)的方式，也就是说，如果我们每条记录所占用的空间量减小，就会使每个page中可存放的数据行数增大，那么每次IO可访问的行数也就增多了。反过来说，处理相同行数的数据，需要访问的page就会减少，也就是IO操作次数降低，直接提升性能。此外，由于我们的内存是有限的，增加每个page中存放的数据行数，就等于增加每个内存块的缓存数据量，同时还会提升内存换中数据命中的几率，也就是缓存命中率。

　　优化表的数据类型

　　表需要使用何种数据类型，是需要根据应用来判断的。虽然应用设计的时候需要考虑字段的长度留有一定的冗余，但是不推荐让很多字段都留有大量的冗余，这样即浪费存储也浪费内存。

　　1.数字类型：非万不得已不要使用DOUBLE，不仅仅只是存储长度的问题，同时还会存在精确性的问题。同样，固定精度的小数，也不建议使用DECIMAL，建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不会带来任何附加维护成本。对于整数的存储，在数据量较大的情况下，建议区分开TINYINT/INT/BIGINT的选择，因为三者所占用的存储空间也有很大的差别，能确定不会使用负数的字段，建议添加unsigned定义。当然，如果数据量较小的数据库，也可以不用严格区分三个整数类型。

　　2.字符类型：非万不得已不要使用TEXT数据类型，其处理方式决定了他的性能要低于char或者是varchar类型的处理。定长字段，建议使用CHAR类型，不定长字段尽量使用VARCHAR，且仅仅设定适当的最大长度，而不是非常随意的给一个很大的最大长度限定，因为不同的长度范围，MySQL也会有不一样的存储处理。

　　3.时间类型：尽量使用TIMESTAMP类型，因为其存储空间只需要DATETIME类型的一半。对于只需要精确到某一天的数据类型，建议使用DATE类型，因为他的存储空间只需要3个字节，比TIMESTAMP还少。不建议通过INT类型类存储一个unixtimestamp的值，因为这太不直观，会给维护带来不必要的麻烦，同时还不会带来任何好处。

　　4.ENUM&SET：对于状态字段，可以尝试使用ENUM来存放，因为可以极大的降低存储空间，而且即使需要增加新的类型，只要增加于末尾，修改结构也不需要重建表数据。如果是存放可预先定义的属性数据呢？可以尝试使用SET类型，即使存在多种属性，同样可以游刃有余，同时还可以节省不小的存储空间。

　　5.LOB类型：强烈反对在数据库中存放LOB类型数据，虽然数据库提供了这样的功能，但这不是他所擅长的，我们更应该让合适的工具做他擅长的事情，才能将其发挥到极致。在数据库中存储LOB数据就像让一个多年前在学校学过一点java的营销专业人员来写Java代码一样。

　　我们可以使用PROCEDUREANALYSE()对当前已有应用的表类型的判断，该函数可以对数据表中的列的数据类型提出优化建议，可以根据应用的实际情况酌情考虑是否实施优化。语法：

　　SELECT*FROMtbl_namePROCEDUREANALYSE();

　　SELECT*FROMtbl_namePROCEDUREANALYSE(16,256);

　　输出的每一列信息都会对数据表中的列的数据类型提出优化建议。第二个例子告诉PROCEDUREANALYSE()不要为那些包含的值多于16个或者256字节的ENUM类型提出建议。如果没有这样的限制，输出信息可能很长；ENUM定义通常很难阅读。

　　在对字段类型进行优化时，可以根据统计信息并结合应用的实际情况对其进行优化。

　　通过拆分，提高表的访问效率

　　有些时候，我们可能会希望将一个完整的对象对应于一张数据库表，这对于应用程序开发来说是很有好的，但是有些时候可能会在性能上带来较大的问题。

　　当我们的表中存在类似于TEXT或者是很大的VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理IO次数，也能大大提高内存中的缓存命中率。

　　这里我们所说的拆分，主要是针对Myisam类型的表，拆分的方法可以分成两种情况：

　　1)纵向拆分：

　　纵向拆分是只按照应用访问的频度，将表中经常访问的字段和不经常访问的字段拆分成两个表，经常访问的字段尽量是定长的，这样可以有效的提高表的查询和更新的效率。

　　2)横向拆分：

　　横向拆分是指按照应用的情况，有目的的将数据横向拆分成几个表或者通过分区分到多个分区中，这样可以有效的避免Myisam表的读取和更新导致的锁问题。

　　逆规范化

　　数据库德规范化设计强调数据的独立性，数据应该尽可能少地冗余，因为存在过多的冗余数据，这就意味着要占用了更多的物理空间，同时也对数据的维护和一致性检查带来了问题。

　　但是对于查询操作很多的应用，一次查询可能需要访问多表进行，如果通过冗余纪录在相同表中，更新的代价增加不多，但是查询操作效率可以有明显提高，这种情况就可以考虑通过冗余数据来提高效率。

　　使用冗余统计表

　　使用createtemporarytable语法，它是基于session的表，表的数据保存在内存里面，当session断掉后，表自然消除。

　　对于大表的统计分析，如果统计的数据量不大，利用insert,select将数据移到临时表中比直接在大表上做统计要效率更高。

　　选择更合适的表类型

　　1、如果应用出现比较严重的锁冲突，请考虑是否更改存储引擎到innodb，行锁机制可以有效的减少锁冲突的出现。

　　2、如果应用查询操作很多，且对事务完整性要求不严格，则可以考虑使用Myisam存储引擎。

　　尽量使用NOTNULL

　　NULL类型比较特殊，SQL难优化。虽然MySQLNULL类型和Oracle的NULL有差异，会进入索引中，但如果是一个组合索引，那么这个NULL类型的字段会极大影响整个索引的效率。此外，NULL在索引中的处理也是特殊的，也会占用额外的存放空间。

　　很多人觉得NULL会节省一些空间，所以尽量让NULL来达到节省IO的目的，但是大部分时候这会适得其反，虽然空间上可能确实有一定节省，倒是带来了很多其他的优化问题，不但没有将IO量省下来，反而加大了SQL的IO量。所以尽量确保DEFAULT值不是NULL，也是一个很好的表结构设计优化习惯。

      小编结语：

      更多内容尽在课课家教育！