MySQL 服务器配置类问题

一、分析 Group By 语句查询结果产生异常的原因

1、题目描述
DROP TABLE IF EXISTS `inventory`;
CREATE TABLE `inventory` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int unsigned DEFAULT NULL COMMENT '商品ID',
  `warehouse_id` int unsigned DEFAULT NULL COMMENT '仓库ID',
  `num` int unsigned DEFAULT NULL COMMENT '库存数量',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of inventory
-- ----------------------------
BEGIN;
INSERT INTO `inventory` VALUES (1, 1001, 1, 15);
INSERT INTO `inventory` VALUES (2, 1001, 2, 20);
INSERT INTO `inventory` VALUES (3, 1001, 3, 30);
INSERT INTO `inventory` VALUES (4, 1002, 1, 20);
INSERT INTO `inventory` VALUES (5, 1002, 3, 40);
COMMIT;

库存数据表数据：

product_id warehouse_id num
1001 1 15
1001 2 20
1001 3 30
1002 1 20
1002 2 40 
# 查询SQL语句：
SELECT product_id,warehouse_id,sum(num) as count  FROM inventory GROUP BY product_id;

从给出的SQL语句不难看出SQL语句写的有问题，因为既然使用了聚合函数分组统计，那查询的是几个字段，就应该group by 几个字段，所以应该是会报错的。正确应该是 GROUP BY product_id,warehouse_id;

但实际情况是并没有报错，而且还查询出了结果，只是查询出的结果并不是正确的，得到的查询结果是这个样子。请你分析一下原因，为什么会产生这种情况。

product_id warehouse_id count
1001 1 65
1002 1 60
2、解答：

可能是由于有人将SQL_MODE参数中的ONLY_FULL_GROUP_BY 去掉了，导致MySQL不强制要求groupby 字段了。

最终导致的结果就是上面一样，明明是SQL语句语法错误，MySQL还是给出了结果，只不过是错误的结果而已。

出现这种修改的原因可能是由于开发同学自己写的SQL不规范，导致MySQL报错，为了解决报错就把ONLY_FULL_GROUP_BY 这个值去掉了。

这种做法可以称得上是欲盖弥彰，欺骗自己，非常可恨，请各位开发同学一定要杜绝。

这里强烈建议，若非必要，不要去尝试修改SQL_MODE这个参数的值。

二、如何比较系统运行配置和配置文件中的配置之间的差异

出现差异的原因可能是：

线上环境突然用户量暴增，max_connection小了，为了不影响服务，暂时使用set global 修改配置参数，等服务不忙时，再真正修改配置文件。

可是有的时候，忙一忙就忘了，这时候就需要找到一种方法来比较系统运行配置和配置文件中的配置之间的差异。

这里可以使用pt-config-diff工具来比较配置文件。

如果数据库是8.0版本，可以使用8.0版本新增的set persist 来做修改。使用这个来修改参数的好处是，即使后面数据库服务重启了，配置仍然生效，因为使用set persist 时，会在数据目录生成一份配置文件，重启后会加载该文件。

# 比较ip为192.168.78.102，端口号为3308的MySQL，运行的配置和配置文件的配置之间的差异
pt-config-diff u=root,p=123456,P=3308,h=192.168.78.102 /etc/my.cnf

三、请列举MySQL中你认为的比较关键的几个性能参数

1、服务器相关配置
参数 说明
max_connections 设置MySQL允许访问的最大连接数量
interactive_timeout 通过程序语言连接的连接超时，比如php、java
wait_timeout 通过MySQL客户端连接的连接超时，比如navicat
max_allowed_packet MySQL可以接受的数据包的大小
sync_binlog 表示每写多少次缓冲，会向磁盘同步一次binlog
sort_buffer_size 每个会话使用的排序缓存区的大小
join_buffer_size 每个会话所使用的连接缓存的大小
read_buffer_size 当对一个myisam进行表扫描时所分配的读缓冲池的大小
read_rnd_buffer_size 设置控制索引缓冲区的大小
binlog_cache_size 每个会话用于缓存未提交的事务缓存大小
2、存储引擎相关参数
参数 说明
innodb_flush_log_at_trx_commit 0-每秒进行一次重做日志的磁盘刷新操作 1-每次提交事务都会刷新事务日志到磁盘 2-每次事务提交写入系统缓存，每秒想磁盘刷新一次
innodb_buffer_poor_size 设置innodb缓冲池的大小，应为系统可用内存的75%
innodb_buffer_poor_instances innodb缓冲池的实例个数
innodb_file_per_table 设置每个表独立使用一个表空间文件
打赏微海报分享