Synology NAS上MariaDB LIKE查询性能问题及配置疑问

针对SELECT * FROM jobs WHERE job_number LIKE '%Y2506195%'这类前缀带%的查询，BTree索引无法生效，可尝试以下方案：

• 使用全文索引替代BTree索引

  1. 确保表引擎为InnoDB（MariaDB 10.2+支持InnoDB全文索引），给job_number创建全文索引：

     CREATE FULLTEXT INDEX idx_ft_job_number ON jobs(job_number);
     

  2. 修改查询语句为全文检索语法（布尔模式支持精确匹配）：

     SELECT * FROM jobs WHERE MATCH(job_number) AGAINST('Y2506195' IN BOOLEAN MODE);
     

  注意：若匹配串长度过短（比如小于默认的4字符），需调整ft_min_word_len配置参数后重建索引。

• 反向字段+前缀索引

  1. 添加存储反向job_number的生成字段：

     ALTER TABLE jobs ADD COLUMN reverse_job_number VARCHAR(255) AS (REVERSE(job_number)) STORED;
     

  2. 给反向字段建BTree索引：

     CREATE INDEX idx_reverse_job ON jobs(reverse_job_number);
     

  3. 查询时反转匹配串，利用前缀索引：

     SELECT * FROM jobs WHERE reverse_job_number LIKE REVERSE('%Y2506195%');
     

  此方法将原查询的%xxx%转为xxx反转%，符合BTree索引的前缀匹配规则。

• 业务维度拆分字段
  若job_number有固定格式（如Y+年份+编号），可拆分出独立字段（如job_year、job_seq）并单独建索引，通过组合字段查询替代模糊匹配，比如：

  SELECT * FROM jobs WHERE job_year = '25' AND job_seq LIKE '%06195%';
  

修改配置后缓冲池大小仍为16MB，可从以下方面排查：

• 确认配置文件路径正确性
  Synology上的MariaDB可能未使用你修改的/usr/local/mariadb10/etc/my.cnf，执行以下命令查看加载的配置文件顺序：

  mysqld --help --verbose | grep -A 1 "Default options"
  

  通常正确路径可能是/var/packages/MariaDB10/etc/my.cnf，需找到优先级最高的配置文件进行修改。

• 检查配置项位置与格式

  1. 确保innodb_buffer_pool_size配置在[mysqld]段内，否则不会被加载；
  2. 配置值需用正确单位：直接写字节数（如150MB对应157286400），或用M（大写）作为单位，如innodb_buffer_pool_size=150M，避免使用MB（部分版本不识别）。

• 排查配置覆盖问题
  检查是否有其他配置文件（如my.cnf.d下的子文件）包含相同配置项，优先级更高的配置会覆盖你的修改。可执行mysqld --print-defaults查看最终生效的配置参数。

• 重启方式是否正确
  Synology套件中心安装的MariaDB，建议通过DSM控制面板的「套件中心」找到MariaDB，点击「重启」，而非直接用sudo命令重启进程——套件可能有自定义启动脚本，直接重启进程可能未加载新配置。

• 内存限制与错误日志

  1. 若NAS总内存不足（如小于150MB），MariaDB会自动调低缓冲池大小，优先保证系统运行；
  2. 查看MariaDB错误日志（通常路径为/var/log/mariadb/mariadb.log），搜索innodb_buffer_pool_size相关报错，确认是否有分配失败的提示。

• 权限检查
  确保修改后的配置文件权限为mysql用户可读，可执行：

  chown mysql:mysql /path/to/your/my.cnf
  chmod 644 /path/to/your/my.cnf
  

内容的提问来源于stack exchange，提问作者kreya