问题叙述

上周开始做新系统的功能了、但是在查询mysql的时候遇到了out of sort memory的问题、看了一下sql语句、很简单的一个查询语句、数据库里面也只有一条数据？

问题原因

为什么会出现这个问题呢？因为在mysql升级到8.0.20版本之后、mysql在排序的时候会将json字段加载到内存、于是我查了一下我数据库中json字段的大小、发现达到了300多k、而mysql默认的排序内存只有256k、所以就出现了这个问题。

感兴趣的小伙伴可以使用下面命令查一下数据库的排序内存的大小

SHOW VARIABLES LIKE 'sort_buffer_size';

解决思路

一共有两种解决思路

第一种是排序的时候不要让json数据加载到内存中

第二种是增加排序内存的大小

第一种解决思路

给json字段加个索引、原因是因为索引本身是有序的、给json字段加索引之后、就可以利用索引的有序性来进行排序、就不需要加载到内存中了

需要注意的是

1:索引也会占用一定的存储空间、并影响写操作的性能、如果json字段很大的时候、还是考虑一下

2:索引只有在查询条件或排序顺序与索引一致时才会被使用

3:如果JSON字段中要索引的值选择性不高（比如很多重复值），索引可能效果不佳

第二种解决思路

直接增大排序内存大小（以docker中的mysql为例）

1:修改my.cnf文件

[mysqld]
host-cache-size=0
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
pid-file=/var/run/mysqld/mysqld.pid

# 缓冲区配置
join_buffer_size = 2M
sort_buffer_size = 4M
read_rnd_buffer_size = 2M
tmp_table_size = 64M
max_heap_table_size = 64M
innodb_sort_buffer_size = 8M

[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

2：修改compose文件

在compose文件中加两行代码
    command:
      - --sort_buffer_size=1048576

结果校验

如果使用第二种办法的话、重启一下docker容器

进入mysql、查询一下数据库内存是不是变成你设置的值了、就解决了

SHOW VARIABLES LIKE 'sort_buffer_size';