Mysql数据优化选择合适的数据类型

一、MySQL数据类型

MySQL支持多种类型的SQL数据类型：数值，日期和时间类型，字符串（字符和字节）类型，空间类型和 JSON数据类型等
数据类型描述使用以下约定：
在MySQL中常用数据类型主要分为以下几类
1. 数值类型

2. 字符串类型

3. 日期时间类型

二、选择数据类型的原则

MYSQL支持的数据类型非常多,选择正确的数类型对于获得高性能至关重要不管存储哪种类型的数据,遵循以下几个原则
◆更小的通常更好
一般情況下,应该尽量选择使用可以正确存储数据的最小数据类型。例如:只需要存储-128-~127, tinyint更好。更小的数据类型通常更快,因为它们占用更少的磁盘,内存和CPU存,并目处理时需要的CPU周期也更少
◆简单就好
简单数据类型的操作通常需要更少的CPU周期。例如,比字符操作代价更低,因为字符集和校对规则(排序规则)使字符比整形更复杂。例妙如:使用date/time/ datetime而不是字符串来存储日期和时间,使用整型存储IP地址。
◆尽量避免NULL
如果查询中包含可为NUL的列,对 MYSQL来说更难优化,因为可为NUL的列使得索引、索引统计和值比较都更复杂。
可为NULL的列会使用更多的存储空间,在 MYSQL中也需要特殊处理。当可为NUL的列被索引时,每个索引记录需要一个额外的字节。如果计划在列上建素引,尽量避免设计为可为NUL的列。当然也有例外,例如 InnoDB使用单独的位(bit)存储NULL值,所以对于稀疏数据有很好的空司效率(这一列大部分的值为NULL，只有少数的有值时，虽然会在有值的值这一列上加是否为Null标识，但是带来的好处是，那些为null的时候，只要储存单独的位(bit))。

三、mysql中使用整形储存ip

前面我们提到过，整型操作代价比字符串更低,而且转换成整型后明显的在磁盘中储存的位数更少

#整型存储IP：15位字符  192.168.110.113
select INET_ATON('192.168.110.113');
#整型转ip
select INET_NTOA(3232263793);

四、尽量避免NULL

1. 空值不一定为空 空值是一个比较特殊的字段，在MySQL数据库中,在不同的情形下,空值往往代表不同的含义。这是MySQL数据库的一种特性
2. mysql 会将可为NULL的列储存时，添加一个标记，所以会消耗更多的储存空间。
3. 当可为NUL的列被索引时,每个索引记录需要一个额外的字节（在为null的列上加索引，执行时是否会走索引需要看使用的哪种引擎）
4. 之前写了避免使用NULL的好处，并且建议尽可能的考虑替代方案。即使需要存储一个空值到表中时，也不一定使用NULL。也许可以使用0、某个特殊的值或者空字符串。但是遵循这个原则也不要走极端。当确实需要表示未知值时也不要害怕使用NULL。从特定类型的领域中选择一个不可能的值可能导致代码复杂很多，并容易引入bug。处理NULL确实不容易，但有时候会比它的替代方案更好。
   现在有一张t_demo表，列name 和name1除了name2允许为Null外，其他属性都一直，并加上索引
   
   
   EXPLAIN查看执行性能分析
   
   可以看到2个执行结果的 key_len 分别为42和43（使用的时utf8mb4，一个中文占4个字节，varchar （10）j所有就为10 * 4，还有2个字节是用来记录varchar在储存中实际的长度，这一点char就不需要，如果是手机号定长的属性，推荐使用char。结果2 多的一个字节就是因为Name2 是可以为null的标识记录）
   特别注意：
   在给字段分配长度时，切记要避免慷概似分配。在储存引擎中，将数据写入磁盘，列入varchar(10)会悲观的将这一列分配10个字节的大小，而不会去管实际。并且我们在上面的结果中可以很明显的看到，索引的长度是42（104 +2)，如果修改为varchar(100)，那么索引的长度就会变成402(4100+2)。增加了索引的开销
   问题：
   既然varhcar是可变长度的，为什么索引的长度确实定义的最大长度呢？
   回答：
   因为在组合索引当中，会将组合列的每个值拼接起来，如果不使用列的最大长度，就无法去区分下一列的值对应的是哪一部分。如果不使用最大的长度，新建一个组合索引的列为 name1和name2 , 在id = 1 这一行数据name1 = “张三”，name2=“李四”，索引目录中对应的就是 “张三李四”。在id = 2 这一行数据name1 = “张三111”，name2=“李四222”，索引目录中对应的就是 “张三111李四222”，就无法去检索索引了。

五、schemas设计

1. 避免太多的列
MySQL的存储引擎API工作时需要在服务器层和存储引擎之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列换成行数据结构的操作代价是非常高的。如果使用了非常宽的表，然而只有一小部分列会实际用到，这时转换的代价就非常高。
2. 避免太多的关联
所谓的“实体-属性-值”设计模式是一个常见的糟糕的设计模式，尤其是在MySQL下不能靠谱的工作。MySQL限制了每个关联操作只能有61张表，事实上单个查询超过12个表的关联MySQL解析和优化查询的代价就会成为问题。
3. 过度使用枚举（Enum）
Enum增加一个值时，会执行alter table操作，对于大表，会更新很慢