数据库优化必备知识

最新推荐文章于 2024-08-13 10:55:29 发布

牛年冲冲冲

最新推荐文章于 2024-08-13 10:55:29 发布

阅读量184

点赞数 1

分类专栏：数据库索引优化数据库优化理论知识文章标签： mysql 索引

本文链接：https://blog.csdn.net/qq_44553398/article/details/116496724

版权

数据库同时被 3 个专栏收录

3 篇文章 0 订阅

订阅专栏

索引优化

1 篇文章 0 订阅

订阅专栏

数据库优化理论知识

1 篇文章 0 订阅

订阅专栏

数据库优化实战

概念：

数据库在进行存取的时候是以页为范围来进行读取的，一般情况下读取的大小都是4KB，内存在读取磁盘中的数据的时候，都会使用读取4kB的倍数的数据，这是内存读取的基本单元
内存的占用空间和文件的大小很可能是不同的，通常情况下，内存空间占用的大小都会大于文件实际的大小，因为存储的基本单元是以页来进行存储的，如果一页不够就会重新申请另外一页来进行存储。

1.数据类型优化

更小的数据类型通常更好：保证数据范围够用的情况，越小的取值范围越好，因为数据类型大的话会占用更多的磁盘、cpu、内存空间，增加了cpu的访问次数，因此也影响io效率。比如int类型也细分为几种类型，

通常情况下会使用int来存储整型，int类型可以细分为一下类型，tinyint 、smallint、mediumint int 32 bigint 64，可以根据实际的需求情况来进行使用。
简单就好：简单数据类型的操作通常需要更少的cpu周期，能存储int类型的情况就不要去存储字符串类型，如果我们存储性别，我们可以以1代表男，0代表女来进行存储，这样就可以节省内存空间的分配。
- 字符串的效率跟整型比要慢
- 用int存储ip地址：有一个函数可以将ip转换为整型，读的时候可以转回来
- 用mysql自建类型(datatime/timestamp）而不是字符串来存储时间或日期
尽量避免存储null值:在数据库的设计过程中尽量设计字段不能为空，因为查询数据结果中包含null的列，对mysql来说很难优化，null可以使用索引，但是索引统计和计算都变得复杂，使用is not null来进行过滤对结果的影响也是很有限的，可以设置特殊值来进行避免存储空值的情况。

2.字符和字符串类型

varchar:数据长度是灵活可变的(0-255)
- 使用最小的符合需求的长度
应用场景：
- 存储长度波动比较大的数据，如文章，有时很长，有时很短
- 字符串很少更新的场景，每次更新后就会重新计算长度，并确定是否需要使用额外存储空间保存长度
- 适合保存多字节字符，汉字，特殊字符等。
char：固定长度的字符串
1. 在进行数据读取的时候会将最后的空格去除，相当于java中的trim（）
2. 最大长度255
3. 检索和写效率会比varchar高，以空间换时间
应用场景：
1. 存储长度波动不大的数据
2. 存储短字符串，经常更新的字符串
Text和Blob:一般情况是不会使用的，因为文件太大，

解决方案：使用专门的文件管理系统来存储大文件（OSS、七牛云等），将文件的地址以字符串的情况存储在数据库，然后访问的时候直接根据地址来去第三方文件服务器中访问。
DataTime和timeStamp
- datetime:占用8个字节，与时区无关，可保存到毫秒，保存的时间范围大，不用使用字符串存储日期类型，损失了日期类函数的便捷性。
- TimeStamp(最常用):占用4个字节，时间范围1970年 —2038年，精确到秒，使用整型存储，依赖数据库的时区设置
date：占用字节最少，只需要3个字节，可以利用日期函数进行日期之间的计算
1. date类型用于保存1000-01-01到9999-12-31之间的日期

5.有固定值不变，最好使用枚举类型，这样实际存储使用的是int类型，会增加其存储效率，数据库可设置枚举类型，有兴趣的同学可以去了解一下。

3.数据库的三大范式

第一范式：数据库中的字段不可再分，如果一个数据库的字段包含其他的属性，要么在这个表中将这些字段进行平铺，要么就新建一张表来与这张表做关联关系。
第二范式：确保数据库的每一列都与主键相关，对于联合主键来讲，数据库的每一列也需要与主联合主键相关，如果不符合，就需要对联合主键中的数据进行拆分，或者建立中间表，来处理这种关联关系。（空间换时间）
第三范式：一个实体中的属性不能是其他实体的非主属性，这样就会出现字段的冗余。
真实情况下范式和反范式是结合使用的，冗余字段可能是因为排序的需要，不要严格限制范式，我们需要根据实际的情况来设计。

主键的选择

代理主键：与业务无关，无意义的数字序列
自然主键：事务属性中的自然唯一标识

推荐使用代理主键：不与业务耦合，因此更加容易维护，一个大多数表，通用的键策略能够减少需要编写的源码数量。

字符集的选择（utf8 mb4）

1.如果数据库中只存储拉丁字符，就设置编码格式为拉丁就可以，否则会产生大量的空间浪费

2.如果我们确定不需要存放多种语言，就没必要使用UTF8或者unicode等字符类型，这会造成大量的存储空间浪费

存储引擎

myisam

b+树中存储的整行数据的内存地址
innoDB

只在叶子节点中存储数据
memory

存储引擎对比
表锁和行锁：

在进行增删改查的时候，如果用到建立索引的那个列，就是用的是行锁，否则使用的是表锁

使用like进行模糊查询的时候可能会有问题：如果使用%开头进行模糊查询就不会用到这个字段的索引

适当的数据冗余

1.被频繁使用而且只能通过join2张以上大表的方式才能得到的独立小字段

2.由于每一次的join只是为了取得某个小字段的值，join的结果却很大，因此造成了很多不必要的io操作，这情况完全可以使用空间换时间的方式来进行优化。将那些小字段数据直接写到当前这个表中，就可以解决这个问题，注意：在使用这种方式的情况下，需要确保数据的一致性不会被破坏，确保这些冗余字段也能够被更新。

分库分表：

水平切分：将字段进行拆分

垂直切分：将数据库中的比较大的表进行切分