MySQL优化（表结构和系统配置优化）

左手的ㄟ右手 2023-06-12 05:27 19阅读 0赞

### 文章目录 ###

*  MySQL优化（表结构和系统配置优化）
 *   *  一，表结构优化
     *   *  1.选择合适的数据类型
         *  2.表的垂直拆分
         *  3.表的水平拆分
     *  二，系统配置优化
     *   *  1.开启数据库缓存
         *  2.操作系统配置优化

# MySQL优化（表结构和系统配置优化） #

在前一篇文章中，我总结了关于mysql优化中的sql以及索引的优化，文章见[sql以及索引的优化][sql]

## 一，表结构优化 ##

对于数据库表的设计，通常需要根据具体的业务流程画出E-R图，确定每个表中基本的字段，然后确定表和表之间的关系，下面是对表的设计优化的一些简单总结：

### 1.选择合适的数据类型 ###

**表设计的第一步肯定是设计出这张表的基本的字段，关于表字段设计的基本规约如下：**

*  一张表必须有一个唯一主键（一般是无符号数自增，或者是经过特定算法生成的唯一数或字符串）
 *  记录的创建时间，字段名：gmt\_create，类型：datetime
 *  记录的更新时间，字段名：gmt\_modify，类型：datetime

**下边是具体业务字段的定义，我们需要为这些字段选择合适的数据类型：**

*  使用可存下数据的最小的数据类型
 *  使用简单的数据类型，int要比varchar在mysql处理上更简单
 *  尽可能使用not null定义字段，这是innodb的特性决定的，因为非 not null的数据可能需要一些额外的字段进行存储，这样会增加一些IO
 *  尽量少用text，非用不可最好分表，将text字段存放在另一张表，在需要的时候在使用联合查询，这样可以提高查询主表的效率

示例1：

使用int 存储日期时间，利用下边两个函数完成转换

select FROM_UNIXTIME(1392178320);#将int类型时间戳转换为时间格式
    SELECT UNIX_TIMESTAMP(2014-02-12 12:12:00);#将时间格式转换为int

结果：

![在这里插入图片描述][20191031165227234.png]

示例2：

使用bigint存储ip地址，利用下边两个函数完成转换

select INET_ATON('192.168.1.1');
    select INET_NTOA('3232235777');

![在这里插入图片描述][20191031165639637.png]

### 2.表的垂直拆分 ###

> 所谓表的垂直拆分就是把原来一个有很多列的表拆分成多个表来解决表的宽度问题

拆分原则：

*  把不常用的字段单独放在一个表中
 *  把大字段独立放在一个表中
 *  把经常一起使用的字段放到一起

比如：现在有一张学生表tb\_stu已经确定需要11个字段：

学生号，姓名，年龄，性别，所在年级，所在班级，兴趣爱好，档案简历，创建时间，修改时间

*  **这些字段中，经常需要被查询的数据有**
    
    学生号，姓名，年龄，性别，所在年级，所在班级
 *  **不常用字段有**
    
    兴趣爱好，档案简历
    
    并且这两个字段很可能要采用blog或者text类型，每次查询都需要去获取这些数据的话会给查询带来效率问题

把常用和不常用字段分出来后，我们开始将tb\_stu进行切分

我们新建一张表tb\_stu\_info，字段有：学生号，兴趣爱好，档案简历，创建时间，修改时间

原来的tb\_stu保留这些字段： 学生号，姓名，年龄，性别，所在年级，所在班级，创建时间，修改时间

当需要查询完整的学生信息时可以使用关联查询：

select * from tb_stu s1 left outer join tb_info s2 on s1.id = s2.id

### 3.表的水平拆分 ###

> 为什么进行水平拆分？

1.  表的垂直拆分:将表中的不常用的列和大字段的列拆分到另外一个表或者多个表中,减少表的宽度;
2.  表的水平拆分:主要是解决数据量过大的问题,水平拆分每个表的表结构都是完全一致的(当单表的数据大于一亿时,尽管加了索引,还是会比较慢);

表的水平拆分：

常用的方法是：hash取模拆分

1、对大表的主键id值进行hash运算，比如要拆分为5张表，可以使用mod(主键id，5）取出0-4个值

2、针对不同的hashID把数据存到不同的表中。

## 二，系统配置优化 ##

### 1.开启数据库缓存 ###

先看看缓存是否开启

SELECT @@QUERY_CACHE_TYPE

![在这里插入图片描述][20191031172346693.png]

现在开启缓存

SET @@QUERY_CACHE_TYPE = ON;

注意：**写入频繁的数据库，不要开查询缓存**

### 2.操作系统配置优化 ###

引用： https://blog.csdn.net/liuxuli232/article/details/81354665

#默认为256KB
    # Sort_Buffer_Size 是一个connection级参数，在每个connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存。
    #Sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。例如：500个连接将会消耗 500*sort_buffer_size(8M)=4G内存
    #Sort_Buffer_Size 超过2KB的时候，就会使用mmap() 而不是 malloc() 来进行内存分配，导致效率降低。
    sort_buffer_size = 4M
    
    #默认为256K
    #用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
    #如果应用中，很少出现join语句，则可以不用太在乎join_buffer_size参数的设置大小。
    #如果join语句不是很少的话，个人建议可以适当增大join_buffer_size到1MB左右，如果内存充足可以设置为2MB
    join_buffer_size = 2M
    
    
    #默认为18
    thread_cache_size = 512
    
    #默认值为1M
    query_cache_size = 32M
    
    #默认1M
    query_cache_limit = 2M
    
    #默认是8M
    #当自动扩展表空间被填满之时，为扩展而增加的尺寸（MB为单位）
    # mysql 5.6.5版本之前默认值是8Mb，从5.6.6版本之后默认为64Mb，最小值为1Mb最大值为1000Mb。
    #这个参数受到innodb_file_per_table参数的影响
    innodb_autoextend_increment = 256
    
    #默认4M
    max_allowed_packet = 100M
    
    #使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层──MySQL Server负责的，仅当autocommit=0、innodb_table_locks=1（默认设置）InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。
    #如果autocommit=1,Innodb的内部表锁可能会导致deadlock,可以通过设置innodb_table_locks=0来解决这个问题
    innodb_table_locks = 0
    
    
    #默认为8M
    #InnoDB 用来高速缓冲数据和索引内存缓冲大小
    #InnoDB最重要的设置，对InnoDB性能有决定性的影响
    #可以设置60-80%的内存
    innodb_buffer_pool_size = 16G（此参数为解决问题的关键，最初有的统计报表打开需要十几秒，此参数调整后，最多两三秒就打开了）
    
    
    innodb_read_io_threads = 6		#读线程数
    
    
    innodb_write_io_threads = 6 	#写线程数
    
    
    innodb_log_buffer_size = 48M	#默认为16M 
    							  #当事务提交时，保存脏数据到内存中，后续再刷新保存到磁盘
    				  			  #适当调整此参数大小，可以减少磁盘I/O

[sql]: https://blog.csdn.net/weixin_41922289/article/details/102829475
[20191031165227234.png]: /images/20230531/1bf15192c51b4016825ed21d0ffd363c.png
[20191031165639637.png]: /images/20230531/bdbabca6c8494844b59051f8b06f4e9f.png
[20191031172346693.png]: /images/20230531/541d87ee720d44e1af4ff469bfab9edd.png