高性能MySQL-笔记2-数据类型和奇怪的东西

高性能MySQL-笔记2-数据类型和奇怪的东西

数据类型优化

• 数据范围内最小类型
• 数据不进行二次操作（使用简单的类型）
• 避免索引NULL值（innodb中稀疏数据无所谓）

各种的数据类型

整数

类型：TINYINT ，SMALLINT ，MEDIUMINT ，INT，BIGINT 【数据范围增大】

对于BIGINT UNSIGNED 无符号整型，常用来作为id值；BUG在于：

• 一些处理，对长度超出bigint的数字直接截取了，又不报错
• 语言（Java）没有无符号整数的类型，使用Long，超出最大值会报错，回显出现截取情况。

【在“MySQL-UUID_SHORT使用问题”的笔记中有这个原因引起的问题处理详情】

实数

类型：FLOAT，DOUBLE，DECIMAL【常用于货币金额】

对于精度问题：使用BIGINT存储百万倍的数据，达到需要的进度计算。（大数据情况下DECIMAL开销显得大）

存在的问题：

• 当使用超大字符串数值作为条件查询时，mysql会将他们认为一样大，这个时候需要加索引才可以避免错误，该bug在5.5、5.6，5.7.2都存在目前没有修复.

  -- 栗子：
  select case when '100000000000000000000002' =  convert(100000000000000000000003,DECIMAL(25,0)) then 1 else 0 end 
  -- true
  select case when '100000002' =  convert(100000003,DECIMAL(25,0)) then 1 else 0 end 
  -- false
  

• 正常情况下decimal是不能直接作为分区key的[5.6版本]

字符串

类型：char 和 varchar ，BLOB 和 TEXT ，枚举(不用就好) ，

存在的问题：

• char 和 varchar ：对于' tset '的存储是不一样的，char默认会删除末尾空格。╮(╯_╰)╭
• BLOB和TEXT只能部分数据排序，且不能全长度进行索引。

时间日期

类型：DATETIME 和 TIMESTAMP 。【精度秒】

区别：

DATETIME 保存与时区无关的时间整数值，ANSI标准定义的时间格式。范围-9999年；

TIMESTAMP 1970-2038年，保存的是秒数以及。在多时区的情况下，日期值会与时区有关。

虽然推荐使用的是TIMESTAMP，颗粒小较小。不过为了避免时区的问题，我还是不想用。╮(╯_╰)╭

标示符

数据中的标示符的选用，比较影响性能，因为常作为关联对比索引来使用。

可以是用TINYINT(整形) 作为标示符，避免使用字符串。同时，系统内尽量统一标示符类型，避免处理中的隐式转换。

ipv4 可以使用无符号整数进行存储，提供INE_ATON() 与INET_NTOA() 方法进行数据转换。

---

一些奇怪的建议

• 列设计不要太多，书是说上千的列不好，这谁都知道的啊。【我觉的按页16KB计算，定义的时候一行1~2KB最多了】
• 关联太多，影响mysql解析优化查询的代价。最好在12个表以内。(⊙_⊙)?
• 不使用枚举类型。【这个压根就不知道是啥。】
• 对于NULL的字段，作为索引列或者关联列尽量避免为NULL。【NULL值对比操作起来很奇怪】

一个奇怪的骚操作

高效加载大量数据

加载大量数据到有非唯一索引的表中，由于索引的使用，导致在插入大量数据的时候变得缓慢，尤其是索引还有好几个的时候。

这里的处理方法是，先禁用索引，然后加载数据，再重启索引。【由于大量数据完成插入之后进行索引，可以通过排序来构建索引，减少了索引的碎片】

ALTER TABLE test.tableName DISABLE KEYS;
-- load all data
ALTER TABLE test.tableName ENABLE KEYS;

还有一个做法是，直接删除所有非唯一索引，然后在完成数据导出之后重建索引。

---

感觉用得到的新的语法

• INSERT INTO … ON DUPLICATE KEY UPDATE : 当insert已经存在的记录时，执行Update【主键重复的时候】

• with rollup 的用法：用来在分组统计数据的基础上再进行统计汇总，即用来得到group by的汇总信息；

select merchant_name,count(id) from merchant_trade group by merchant_name  with rollup 
-- 最后一个为（null） 值  的就是汇总信息

---