MySQL调优（一）性能监控与数据类型优化

写在前面

作者才疏学浅，以下笔记为作者学习之余写的笔记，参杂了MySQL5.6到8.0的版本，部分没提到版本的可能会有版本不一致问题。如果看完有所收获、发现了问题抑或是愿意指点作者的，欢迎大家联系作者并一起探讨交流。

喜欢别人说的一句话：”不要为了技术而技术。”不只是MySQL优化，其他技术在我看来也是同样的道理，这些技术与规范不是硬性规定必须怎么样，而是在最适应业务的情况下做到更好，在做到这种情况下做到规范！

性能监控

performance schema更容易监控MySQL，但也比较难,所以这里先用show profile。（后期不一定会加）

PS:性能监控程序都会消耗性能

在MySQL命令行下打开此工具：set prifiling=1;
设置完成后，在服务器执行的所有语句，都会测量其耗费的时间和其他一些查询执行状态变更相关的数据。
执行任意查询：select * from emp;
查看具体执行时间：show profiles;（命令行模式下只能显示两位小数的时间）

all：显示所有性能信息 
show profile all for query n

block io：显示块io操作的次数
	show  profile block io for query n

context switches：显示上下文切换次数，被动和主动
	show profile context switches for query n

cpu：显示用户cpu时间、系统cpu时间
	show profile cpu for query n

IPC：显示发送和接受的消息数量
	show profile ipc for query n

Memory：暂未实现

page faults：显示页错误数量
	show profile page faults for query n

source：显示源码中的函数名称与位置
	show profile source for query n

swaps：显示swap的次数
	show profile swaps for query n

使用show processlist查看连接的线程个数、状态等，但有druid等工具控制，所以这里就不多讲了。

数据类型优化

数据类型通常最小的通常最好，占用的磁盘、内存和CPU缓存更少，处理周期更短。所以选择不超过范围的最小类型。

1. 简单数据类型的操作需要更少的CPU周期：

1. 整型比字符操作代价更低，因为字符集和校对规则使得字符比较比整形比较更复杂。
2. 使用mysql自建类型而不是字符串来存储日期和时间。
3. 用整型存储IP地址。

2. 尽量避免null

查询含有null的列使得索引、索引统计和值比较都更加复杂，但通常情况下null的列改为not null带来的性能提升比较小，所以没必要将所有的表的的schema进行修改，但在设计之初尽量避免设计成可能为nul的列。

3. 整形类型

TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。尽量使用满足需求的最小数据类型。

数据类型优化实际细则

1. 字符和字符串类型

1. char长度固定，每条数据占等长字节空间，最大占255个字符，适合身份证、手机号等固定长度字符串。

   • 最大长度：255
   • 自动删除末尾的空格
   • 检索效率、写效率 比varchar高，以空间换时间
   • 应用场景
     存储长度波动不大，如md5
     存储短字符串、经常更新字符串

2. varchar可变长度，可以设置最大长度；最大空间是65535个字节，适合长度可变的属性。

   • 使用最小的符合需求的长度。
   • varchar(n) n小于等于255使用额外一个字节保存长度，n>255使用额外两个字节保存长度。
   • varchar(5)与varchar(255)保存同样的内容，硬盘存储空间相同，但内存空间占用不同，是指定的大小 。
   • varchar在mysql5.6之前变更长度，或者从255一下变更到255以上时时，都会导致锁表。
   • 使用场景
     • 存储长度波动大
     • 字符串很少更新的场景（每次更新需重新计算并使用额外存储空间保存长度）
     • 保存多字节字符，如汉字

3. text不设置长度，当不知道属性的最大长度时，适合用text。

4. 查询速度：char>varchar>text

2. BLOB与TEXT

MySQL把每个 BLOB 和 TEXT 值当作一个独立的对象处理。两者都是为了存储很大数据而设计的字符串类型，分别采用二进制和字符方式存储。

3. 日期类型

1. 不要使用字符串类型来存储日期时间数据

2. 日期时间类型通常比字符串占用的存储空间小

3. 日期时间类型在进行查找过滤时可以利用日期来进行对比

4. 日期时间类型有丰富的处理函数来进行计算

5. 使用int存储日期时间不如使用timestamp类型

6. 从MySQL5.6.4之后这两者都可以包含秒后的小数部分，精度最高为微妙（6位），在这之前都表示不了小数。

7. MySQL 8.0TIMESTAMP和DATETIME的自动初始化和更新

    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP：初始化为创建时间，并在修改时自动更新时间
   

8. MySQL 8.0.19后，可以指定时区偏移量，设置time_zone系统变量相同格式，以下情况除外：

   • 小于10的小时值，需要导零
   • -00:00拒绝
   • 时区名称EET、Asia/Shanghai、SYSTEM
   • 关于偏移量问题如有疑问，请查看官网的说明与代码，或下载MySQL 8.0.19以上的版本进行试验，如有进一步理解，欢迎各位与本人交流。

4. datetime、timestamp、date

1. datetime
   • 5.6.4之前占8字节，5.6.4之后，由于允许有小数，所以非小数部分需要5字节而不是8字节，小数部分占0到3个字节
   • 时间范围：1000-00-00 00:00:00.00~9999-12-31 23:59:59.00
   • 与时区无关，数据库底层时配置，对datetime无效 (还没仔细看)
   • 自动初始化默认为NULL，属性定义NOT NULL默认值为0
2. timestamp
   • 5.6.4之前占4字节，5.6.4之后，timestamp的非小数部分是需要4个字节，小数部分占0到3个字节
   • 时间范围：1970-01-01 00:00:01UTC到2038-01-19 03:14:07UTC
   • 采用整形存储
   • 依赖数据库设置的时区（serverTimezone=Asia/Shanghai）
     • datetime.fromtimestamp(t) ：本地时间
     • datetime.utcfromtimestamp(t) ：UTC时间
     • 用mybatis操作的读取，需要配置下再加上8小时
     • 自动初始化默认为NOT NULL(0)，属性定义为NULL后默认值为NULL
3. date
   • 占用的字节数比使用字符串、datetime、int存储要少，使用date类型只需要3个字节
   • 使用date类型还可以利用日期时间函数进行日期之间的计算
   • date类型用于保存1000-01-01到9999-12-31之间的日期

5. 枚举类

有时可以使用枚举类代替常用的字符串类型，mysql存储枚举类型会非常紧凑，会根据列表值的数据压缩到一个或两个字节中，mysql在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的查找表

实例：

create table enum_test(e enum('fish','apple','dog') not null);
insert into enum_test(e) values('fish'),('dog'),('apple');
select e+0 from enum_test;

6. IP存储

人们经常使用varchar(15)来存储ip地址，然而，它的本质是32位无符号整数不是字符串，可以使用INET_ATON()和INET_NTOA函数在这两种表示方法之间转换

select inet_aton('1.1.1.1')
select inet_ntoa(16843009)