【mysql-innodb优化】-schema优化

首先是优化的几大原则。

1、更小的通常更好，使用更小的数据类型进行存储通常可以更快，因为使用更少的cpu，IO，内存。但是要确定好你要存储的值的范围是数据类型能够的存储的范围。

2、简单就好，例如整型的数据比字符操作往往代价会更低。比如使用mysql的内建类型存储日期，date，datetime等，不使用varchar。比如用整型存储IP地址而不是使用varchar之类的。

3、尽量避免使用NULL，NULL对mysql来说很难优化，除非你真的需要存储null，否则尽量避免使用null。尤其是在你准备建立索引的列上。

下面来看看怎么为一个列选择一个数据类型：

1、第一步需要确定需要存储的数据的大类型： 数字，字符串，时间等。这通常很简单。

2、第二部是选择具体的类型，很多mysql的数据类型都可以存储相同的数据。只是遁出的长度和方位不一样，允许的精度不同，或者需要的磁盘和内存空间不同。相同大类型的不同子类型数据有时也有一些特殊的行为和属性。

下面我们来具体看看数据类型的选择：

1、整数类型， tinyint(1字节), smallint(2字节), mediuint(3字节)，int(4字节)，bigint（8字节）。整数类型可选的选项有UNSIGNED,表示不允许负值。

对于存储计算来说int(1)和int(11)是没有区别的，占用的磁盘空间是一样的，只是mysql用来展示的时候的区别而已。

2、实数类型，实数类型是指带有小数部分的数字。个人建议还是存储整数吧，了解计算机存储小数的原理的童鞋应该清楚，小数在计算机中的存储是会丢失精度的。实数类型的存储有double，float，decimal， 你可以使用整数类型存储，展示给前端的时候做一个除法即可。比如价格为11.29元，数据存储中可以使用1129，单位是分。

3、字符串类型。varchar和char类型。是字符串存储的主要存储方式。

     varchar类型，varchar类型是变长的类型，字符串需要多少存储空间，实际就占用多少磁盘空间，但是在磁盘中需要存储额外的长度。varchar类型节省了存储空间对性能也会有帮助，但是如果是一个经常需要update的列，update的字符串长度比之前的字符串长度更长，就会导致一些问题了， 仔细想想其实不难发现，更长的字符串的原本的磁盘上是无法存储的，没有足够的空间。因此可能会造成页的分裂等等复杂的操作。而如果update的字符串长度比之前的长度更短了，那么就会浪费掉一部分存储空间，个人感觉问题不大。在innodb中，过长的varchar会被存储为blob。

     char类型，char类型是变长的类型。在磁盘中，如果存储的字符串长度不够则会在末尾补充空格。，char类型适合存储比较段的字符串或者所有的值都接近一个长度，比较短的字符串不会造成过多的磁盘空间浪费，长度接近的字符串也不会造成磁盘空间的浪费。比如char类型适合存储身份证号码，手机号码，md5格式化的密码等，char类型也不容易产生磁盘碎片，对于经常变更的列，而长度又比较短或者长度接近，使用char作为存储类型比使用varchar要更叫高效。比如，存储Y和N两种字符，使用char只需要一个字节，而使用varchar需要两个字节，因为还需要一个额外的字节存储长度。

     还有一些不常用的二进制字符串存储，比如binary，varbinary，不作讨论.

     blob,text类型，存储较大的字符串使用。text使用的字符类型存储数据，分别有tinytext,smalltext,mediutext,text,bigtext；对应的二进制存储为tinyblob,smallblob,mediublob,blob,bigblob。当字符串的值太大时，此时一个值在一条记录的磁盘存储内需要1-4个字节存储一个文件指针，指向一个外部区域，而这个外部区域就是存储真正的数据的地方。blob和text之间仅有的不同是，blob是存储的二进制数据，而text存储的是字符数据，所以也就是说blob没有字符集的区分。

4、枚举类型     enum

     有时候可以使用枚举类型来优化字符串的存储，因为mysql并不会在行记录磁盘中真正的存储字符串的内容，而是会存储一个映射的值，这就类似于压缩。 比如enum('aaa', 'bbb','ccc'),那么真正存储的可能是1,2,3. 然后做一个映射

     [

          1     =>     'aaa',

          2     =>     'bbb',

          3     =>     'ccc'

     ]

这个值是存储在表结构定义文件中的，就是.frm结尾的文件。

5、日期和时间类型的选择

datetime，保存大范围的值，从1001年-9999年，精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节存储。

timestamp，保存了从1970年1月1日午夜以来的秒数，事实上这就是unix的时间戳，但是由于它的存储空间只有4个字节，因此在mysql中它只能存储到2038年。有时候人们会使用整型数据保存时间，但是这不会带来任何收益，比如你用一个int类型存储时间，消耗了4个字节，跟timestamp一样，而且也不会方便你的处理。通常我们都说，能用lib库函数的就不要自己写了，不要以为自己写的会比别人的更高效，应该就是这个道理吧。如果有时间我们的时间要存储到微妙的话，那就只能用bigint了。

6、位数据类型 bit set

总的来说，位数据类型都是为了节省空间，使得数据的存储更加的紧凑，但是就像我们经常说的互联网一直在追求一个IO/CPU之间的平衡。这种类型用的比较少，通常存在于只有两种值的场景，比如存储性别，对错等等。使用这种类型需要谨慎或者尽量避免，毕竟现在的磁盘不需要你这么节约的去使用。下面看看一个例子，比如说我们存储一个数字 57 进入，使用的是bit(8)，那么当你读取的时候读取来的是 57对应的ASCII码字符 ‘9’，这是因为innodb默认认为存储的是字符串而不是数字.所以要存储57这个数字的话，只能先把57转换成ASCII码的形式。set我没用到过，不讲。

接下来我们看看表结构设计中的一些陷阱

     1、太多的字段。mysql的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲的格式拷贝数据，然后在服务器层将缓冲内容解码成各个字段的内容，这个转换的代码相对来说是比较高的，如果仅仅只是定长结构还好，但是对于变长结构来说往往意味着多几次IO和CPU操作。所以，如果你的表中有几千个字段，不要以为你只查询了几个字段就没有问题了，因为在实际转换中仍然是转换了几千个字段，只是有一些你没用到。

     2、太多的关联。

     3、枚举的数量太多

     4、变相的枚举，使用set

     5、用一些很奇怪的值表示null。

          比如产品类型， 用1=》电器，2=》生活用品，3=》其他品。 用-1表示未知的类型，那么这个-1就可能导致代码更加复杂， 用0表示未知显然更好。

最后我们来看看一些表结构的常用优化手段

     1、适当的冗余信息，

     2、非精确的查询(定时收集数据)而不是每次全表扫描。

     总得来说常用的优化手段就是保证mysql能够快速读取，比如牺牲写的速度(添加索引就会降低写的速度)

alter table操作。

alter table操作的性能对大表来说是个大问题，mysql执行大部分alter操作都是创建一个新的表，从旧表中查出所有的数据插入新表，然后删除旧表，这样的操作可能需要花费很长的时间，如果内存不足而表又很大，而且还有索引的情况下，可能需要花费很长的时间，几个小时甚至几天。一般而言，大部分的alter table操作将会导致mysql服务终端，因为它会锁表。

     对于alter table操作的优化只有以下几种。

          1、先在一台不提供服务的机器上执行alter table操作，然后进行主从切换。

          2、另外一种是 “影子拷贝”，影子拷贝是手动创建一张新表，然后通过重命名和删表操作进行切换(这里数据实时同步的问题我不太清楚是如何解决的)。

          3、对于一些对现有数据没有影响的操作可以直接修改.frm文件中的表结构，比如  not null default 3 改成 not null default 5,那么就无需使用alter进行整个表的重建。比如alter column修改列的名称而不修改数据等操作。具体的替换操作举例如下：     

               a、创建一张具有相同表结构的空表，并进行所有的修改。

               b、执行flush table with read lock，给表上锁，停止服务。

               c、交换.frm文件。

               d、执行UNLOCK TABLES释放锁。

总结：

     1、尽量避免复杂的表结构设计

     2、根据你需要存储的数据选择正确的数据类型

     3、尽量避免使用null

     4、在关联中尽量使用相同的数据类型，比如varchar和enum的关联就尽量避免。

     5、尽量使用整型标识一个列，比如ID 为int类型。

     6、尽量不要使用mysql已经遗弃的特性，比如double，float等

     7、小心使用enmu，虽然很方便，但是有时候是陷阱。

     8、尽量避免使用bit，除非你的磁盘空间真的不够用了。

     7、注意可变的长字符串，比如text类型的字符串，他们在排序时读入内存可能会导致悲观的按照最大长度分配内存。