Schema与数据优化（高性能mysql读书笔记一）

 1、数据类型的选择

1）尽量使用能够存储数据的最小类型

2）尽量使用Mysql提供的数据类型存储相应的结构，例如用date存储时间，用 int unsigned来存储ip

e.g.

SELECT  INET_ATON  ( '192.168.23.4'  ) ;

得到这个IP的值为：  3232241412

SELECT  INET_NTOA  ( 3232241412 ) ;

得到这个值对应的IP是：  192.168 . 23  .4

3)尽量避免null值(not null)

可为null的列，存储值需要更多的存储空间

当可为null的列建立索引的时候，每个索引需要一个额外的字节

4）TIMESTAMP存储空间只占DATETIME的一半，而且会根据时区做自动的更新；但是TIMESTAMP允许的时间范围小的多

2、数据类型

1）TINYINT，SMATINT，MEDIUMINT，INT，BIGINT分别使用8,16,24,32,64位来存储

2）DECIMAL（精确计算）、DOUBLE、FLOAT

3）CHAR、VARCHAR

VARCHAR：存储可变长字符串

VARCHAR需要1或2个额外的字节记录字符串的长度：如果字符串的长度大于255个字节，那么长度用一个1字节记录，否则，用2个字节记录

VARCHAR节省空间，对性能有帮助。但是在update的时候可能需要做额外的工作。

如果一个VARCHAR类型的字段数据需要增长，在Myisam中会将其拆分为不同的片段存储，但Innodb会分裂页使行能够放进页内

适用场景：该列的最长串比平均长度长很多，列更新比较少

CHAR：定长字符串存储

CHAR型并不会存储字符串的长度，而且会删除该字段末尾的空格

适用场景：存储比较短的字符串，或者所有的长度都比较接近，比如md5码，mac地址，手机的udid

BLOB、TEXT

BLOB存储二进制数据，TEXT存储字符串，有字符集和排序规则，只针对TEXT的最前N位进行排序

4）enum

create  table  enum_test(

  e enum ( 'fish'  , 'cat'  , 'dog'  )  not  null

)

select  e +  0 ,e  from  enum_test

适用场景：固定的集合值，因为表中存储的都是enum表中的id，类似字典表

5）DATETIME，TIMESTAMP

DATETIME：范围广（1001-9999），精度为妙,8个字节存储

TIMESTAMP：从1970年1月1日午夜开始（格林尼治）到2038年，4个字节存储，默认为not null-一般情况推荐使用TIMESTMAP（存储空间小）

6）BIT、SET

3、选择标示符

1）整数是标识列最好的选择（快，可用auto_increment）

2) 避免使用string作为标识列（慢，尤其在myisam中，默认对字符串使用压缩索引，会导致查询慢得多）

4、新老表的切换

create  table  auth_user_new  like  auth_user

select  *  from  auth_user

insert  into  auth_user_new   select  *  from  auth_user

#insert data

rename  table  auth_user  to  auth_user_old,auth_user_new  to  auth_user

5、物化视图

物化视图，它是用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，从而快速的得到结果。物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响 应用程序 中SQL 语句的正确性和有效性；物化视图需要占用 存储空间 ；当 基表 发生变化时，物化视图也应当刷新。