高性能MYSQL（二）

性能优化的着手点

• CPU的利用率：
• 吞吐量：
• 任务之间也可能因为争用磁盘或者CPU资源相互影响
• 性能剖析： 测量任务所花费的时间，然后对结果进行统计和排序，将重要的任务放到前面

Scheme与数据类型优化

• 字段的属性应尽量简单
• 尽量避免在索引和主键的列上面出现NULL值，NULL值会使得索引和值比较都变得复杂!
• InnoDB 利用一个单独的位来存储NULL值,所以对于稀疏数据具有很高的利用率
• TimeStamp和DATATIME都可以存储相同的数据，并且精确到秒，不同的是：
  • TIMESTAMP只需要占用DATETIME一般的存储空间，并且会根据时区的变化而变化，
• TIMESTAMP可存储时间的范围比较小，这个属性决定了其特殊功能方面的限制

整型

• TINYINT（8），SMALLINT（16），MEDIUMINT（24），INT（32），BIGINT（64），他们都具有可选的UNSIGNED属性，可以根据需求选择
• MYSQL也可以为INT值指定位数的宽度：INT（11）.这种操作没什么意义，因为这只是限定了客户端显示的字符的长度，并不是在限定底层存储数据的字节长度
• 例如利用无符号整型来存储IP地址，varchar(15)，不足 对一个每一个不足三位的，自动补0.。。

MYSQL scheme设计中的陷阱

• 不要设计太多的列
• 不要设计太多的关联：在单个查询下面，最好不要超过 12个关联，包括自关联

缓存表和汇总表

• 缓存表：标示存储那些从其他表中较为容易获取的数据
• 汇总表：利用GROUP BY 聚合数据生成的表
  ＋总的来说，其实就是使用临时表来存储一些热点数据。。。来提高性能

实数类型

• FLOAT类型和DOUBLE类型支持使用标准的浮点运算进行近似计算。
• DECIMAL类型支持用于存储精确的小数，其可以限定小数点后的位数，制定长度会影响对应列在MYSQL中存储时的空间消耗（存储其实是打包成了字符串）。DECIMAL（18,9）表示小数点两边各存储9位输，一共需要 9各字节(小数点占一个字节)

字符串

对于不同的存储引擎，其底部对字符串的存储有不同的实现方式

varchar的劣势：

• varchar需要 1-2个字节来记录字符串长度。
• 在对varchar对应的数据列进行update时，改变数据的存储长度会带来额外的工作。对于不同的存储引擎，InnoDB会分裂页使行可以放到页内，MyISAM会将行拆成不同的存储片段
• 对于字符串的长度标准差大（片段多）的场景
• 数据更新少的场景
• 对于定长的值，例如MD5，最好是用CHAR型

枚举

对于类型可知的字符串（例如男女。可以利用枚举来代替使用字符串）

BLOG和TEXT

• BLOG ： 二进制，无排序规则，TEXT： 字符串，有字符集，有排序规则
• MYSQL会将BLOG和TEXT当做一个独立的对象来处理
• 当BLOG或者TEXT的值太大，MYSQL会使用专门的外部存储来存储这些值，此时每个列上面的值对应一个1 - 4字节的指针
• 如果你想在这两个类型的列上面建立索引，那么你必须指定索引的前缀长度
  CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)))

位(BIT)

对于大部分应用这玩意几乎没有用

• 在MYSQL5.0之前，ＢＩＴ和 TINYINT是同义词
• 对于BIT(5),意思是分配5个位来存储，BIT列的最大长度是64位。
• 对于不同的存储引擎，MyISAM会将BIT列进行打包，INNODB使用一个足够小的整型来存储。例如对于 17个BIT（1）的列来说，MyISAM需要 3个字节 (3 * 8 = 24)来存储，而innoDB需要 17个TinyINT 类型来单独存储各个列。所以在INNODB下使用BIT来存储并不会节省空间

选择标示符

• 整数： 一般为最好的选择，还有AUTO_INCREMENT这种操作
• 尽量避免使用字符串作为标识符，选择字符串作为标识符时，注意MYISAM默认对字符串使用压缩算法，这样会导致查询慢的多

其他

数据库中的范式与反范式设计

• 第一范式：关系型数据库，自动满足第一范式：

  
  image.png
• 第二范式：简单的讲就是在第一范式的基础上每列数据有一个自己的主键
• 第三范式：要求一个表中不包含一个已在其他表中已经包含了的非主键字段，显然目的是节省存储空间
• 反第三范式： 利用空间换时间的典型！

总结

• 尽量避免过度设计，例如会导致极其复杂查询的scheme设计，或者很多列表的设计
• 使用小而简单的数据类型，除非真实数据模型中有确切需要，否则尽量避免ＮＵＬＬ值
• 尽量使用相同的数据类型存储相似或者相关的值，尤其是要在关联条件中使用的列
• 注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存
• 尽量使用整型定义标识列
• 避免使用MYSQL已经遗弃的特性，例如指定浮点的精度，或者整型的显示宽度