MYSQL调优课学习笔记

MYSQL调优课学习笔记

一：性能监控

我们在平时练习或者工作当中有时候会发现对应的一条sq语句l很慢，而且不知道怎么办 ，莫慌

且听我细细道来

首先我们要在学习mysql时要知道三个层次，client,server,存储引擎

存储引擎可不能小看，在学习事务，索引或者各种点的时候都是跟存储引擎相关的，不同的存储引擎

它所表现的形式与实现的原理也是不一样的。

我们分开来看

首先用户向mysqlServer发出连接请求，sever内有连接器提供连接来做权限验证比如用户名，密码的验证；第二步，当验证成功后，用户会发送一个sql的请求，但是咱们写的sql语句其实本身就是个字符串，mysql是咱们识别这个字符串的呢，哎~这个就是分析器的工作了 ，分析器会将sql语句字符串按照where，from等等这个关键字进行切分，切分完成之后会进行词法分析，语法分析最终会变成一个AST(AbstractSyntaxTree,抽象语法树)。但是呢，一个sql语句可能会有很多种不同的执行方式，比如A join B,这个语句我们是先读取A表呢？还是先读取B表呢？所以mysql内还有个优化器。优化的方式有两种：一种是RBO(Rule-Based Optimization 基于规则的优化),一种是CBO(Cost-Based Optimization 基于代价的优化),优化器会根据这两种方式进行优化，优化结束之后会有个执行器，这个执行器就是跟存储引擎挂钩了，用来存取数据 ，但也不是随便乱取的，因为sql我们最关心之一就是他的效率，想select * from table这个应该尽量避免，因为硬件层面为了提高效率我们只能减少IO的量或者减少IO的次数，尽量少的从硬件中获取数据。其实mysql8.0之前，在server层还有一个缓存器，但是在之后被废弃了，因为数据的变更是很频繁的，一旦反生了变更我们缓存的数据就没有意义了，除非向那些很久不变的枚举类可以放进缓存，其他经常修改的数据的命中率是很低的。以上就是整个mysql的服务架构。我们的优化操作都是在分析器，优化器的基础上做的。

（1）show profiles 和 show profiles

我们运行一段简单的sql语句时

时间是0s,但并不是指执行时间是0s，而只是去除了后面的小数，如果我们想看到底执行了多长时间，这个时候我们可以在执行之前输入

set profiling =1;

之后运行sql语句，运行完之后输入

show PROFILE;

就会出现一个表 这个表是你最近运行的一条sql

上面各种阶段所用的时间都有显示，可以根据这个了解sql在哪个阶段用时较多。

当输入

show PROFILES;

这个表会显示最近的各条sql语句的单挑运行时间

这时如果你想看其中特定一条具体的时间可以输入

show PROFILE for query 82

后面的数字就是上面表的Query_ID

这个语句只能显示时间，如果想要知道其他属性的话可以加上属性

例如

show profile cpu;

啥都能看，嘻嘻嘻。

但是官网说

这两个未来要被弃用了，用Performance Schema替代。

（2）Performance Schema

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-quick-start.html

mysql官网performance schema

这个详细笔记我会在下一篇上传。仅供参考。

二：schema与数据类型优化

（1）数据类型的优化

a:更小的通常更好

应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少，但是要确保没有低估需要存储的值的范围，如果无法确认哪个数据类型，就选择你认为不会超过范围的最小类型

b:简单就好

简单数据类型的操作通常需要更少的CPU周期，例如，
1、整型比字符操作代价更低，因为字符集和校对规则是字符比较比整型比较更复杂，
2、使用mysql自建类型而不是字符串来存储日期和时间
3、用整型存储IP地址

c:尽量避免null

如果查询中包含可为NULL的列，对mysql来说很难优化，因为可为null的列使得索引、索引统计和值比较都更加复杂，坦白来说，通常情况下null的列改为not null带来的性能提升比较小，所有没有必要将所有的表的schema进行修改，但是应该尽量避免设计成可为null的列

(2) 实际细节

a:整数类型

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

b:字符和字符串类型

1、char长度固定，即每条数据占用等长字节空间；最大长度是255个字符，适合用在身份证号、手机号等定长字符串
2、varchar可变程度，可以设置最大长度；最大空间是65535个字节，适合用在长度可变的属性
3、text不设置长度，当不知道属性的最大长度时，适合用text
按照查询速度：char>varchar>text

varchar根据实际内容长度保存数据:

1、使用最小的符合需求的长度。

2、varchar(n) n小于等于255使用额外一个字节保存长度，n>255使用额外两个字节保存长度。

3、varchar(5)与varchar(255)保存同样的内容，硬盘存储空间相同，但内存空间占用不同，是指定的大小 。

4、varchar在mysql5.6之前变更长度，或者从255一下变更到255以上时时，都会导致锁表。

应用场景：

1、存储长度波动较大的数据，如：文章，有的会很短有的会很长

2、字符串很少更新的场景，每次更新后都会重算并使用额外存储空间保存长度

3、适合保存多字节字符，如：汉字，特殊字符等

char固定长度的字符串

1、最大长度：255

2、会自动删除末尾的空格

3、检索效率、写效率 会比varchar高，以空间换时间

应用场景：

1、存储长度波动不大的数据，如：md5摘要

2、存储短字符串、经常更新的字符串

c:BLOB和TEXT类型

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

d:datetime和timestamp

1、不要使用字符串类型来存储日期时间数据
2、日期时间类型通常比字符串占用的存储空间小
3、日期时间类型在进行查找过滤时可以利用日期来进行比对
4、日期时间类型还有着丰富的处理函数，可以方便的对时间类型进行日期计算
5、使用int存储日期时间不如使用timestamp类型

datetime:

占用8个字节

与时区无关，数据库底层时区配置，对datetime无效

可保存到毫秒

可保存时间范围大

不要使用字符串存储日期类型，占用空间大，损失日期类型函数的便捷性

timestamp

占用4个字节

时间范围：1970-01-01到2038-01-19

精确到秒

采用整形存储

依赖数据库设置的时区

自动更新timestamp列的值

date

占用的字节数比使用字符串、datetime、int存储要少，使用date类型只需要3个字节

使用date类型还可以利用日期时间函数进行日期之间的计算

date类型用于保存1000-01-01到9999-12-31之间的日期

e:使用枚举代替字符串类型

有时可以使用枚举类代替常用的字符串类型，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;

f:特殊类型数据

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

（3）合理使用范式和反范式

a:范式

优点：范式化的更新通常比反范式要快

​ 当数据较好的范式化后，很少或者没有重复的数据

​ 范式化的数据比较小，可以放在内存中，操作比较快

缺点：通常需要进行关联

b:反范式

优点：所有的数据都在同一张表中，可以避免关联

​ 可以设计有效的索引；

缺点：表格内的冗余较多，删除数据时候会造成表有些有用的信息丢失

c:注意

在企业中很好能做到严格意义上的范式或者反范式，一般需要混合使用

在一个网站实例中，这个网站，允许用户发送消息，并且一些用户是付费用户。现在想查看付费用户最近的10条信息。 在user表和message表中都存储用户类型(account_type)而不用完全的反范式化。这避免了完全反范式化的插入和删除问题，因为即使没有消息的时候也绝不会丢失用户的信息。这样也不会把user_message表搞得太大，有利于高效地获取数据。

另一个从父表冗余一些数据到子表的理由是排序的需要。

缓存衍生值也是有用的。如果需要显示每个用户发了多少消息（类似论坛的），可以每次执行一个昂贵的自查询来计算并显示它；也可以在user表中建一个num_messages列，每当用户发新消息时更新这个值。

范式设计

反范式设计

（4）主键的选择

a:代理主键

与业务无关的，无意义的数字序列

b:自然主键

事物属性中的自然唯一标识

c:推荐使用代理主键

它们不与业务耦合，因此更容易维护

一个大多数表，最好是全部表，通用的键策略能够减少需要编写的源码数量，减少系统的总体拥有成本

（5）字符集的选择

1.纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间。

2.如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型，这回造成大量的存储空间浪费。

3.MySQL的数据类型可以精确到字段，所以当我们需要大型数据库中存放多字节数据的时候，可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量，进而降低 IO 操作次数并提高缓存命中率。

（6）存储引擎的选择

存储引擎的对比

（7）适当的数据冗余

1.被频繁引用且只能通过 Join 2张(或者更多)大表的方式才能得到的独立小字段。

2.这样的场景由于每次Join仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的 IO，完全可以通过空间换取时间的方式来优化。不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新。

（8）适当拆分

当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。