高性能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) 值 的就是汇总信息