mysql大杂烩

1.mysql设置最大连接数：

show variables like'%max_connections%';
set global max_connections=150;

2.springboot的mybatis（mybatis 3.5版本）默认用的是连接池是hikari，其中最大连接数，默认值是10，一般并发量比较大的场景下，这个数是处理不过来的，需要增加，配置方式在application.properties中增加：

spring.datasource.hikari.maximum-pool-size=100

必须要说的是，这个数不能大于mysql设置的max_connections，不然会报连接数不足之类的异常。

3.在大量的写场景下，会产生大量binlog日志，select这种查询语句并不会产生binlog日志，只有在对数据库做修改的情况下才会产生。binlog日志太大，很快就会写满磁盘，所以需要设置下binlog日志保存的时间。在mysql8.0之前的版本，只有一个expire_logs_days，这个参数的单位是天，默认值是0。在8.0之后，增加了一个参数binlog_expire_logs_seconds，这个参数的单位是秒，默认值是2592000，也就是30天：

show variables like '%expire_logs%';
set global binlog_expire_logs_seconds=3600;

如果发现占满了磁盘，可以进到/var/lib/mysql/目录下，使用命令：

// 查看当前目录下的文件和目录大小
du -h *

查看binlog日志文件的大小。

如果文件过大，那么不要直接删除，直接删除可能会导致数据库启动不起来，报错：Error writing file 'binlog' (errno: 2 - No such file or directory)，因为删除之后再purge一下才行。这里提供一个通过数据库自带的方法删除binlog的指令：

show binary logs;
show master status;//查看当前正在写入的binlog
purge master logs before date_sub(now(),interval 3 day);

如果不小心删除了怎么办呢，找到/var/lib/mysql/目录里的binlog.index，把里面的那条binlog日志删掉就可以启动了。

4.配置外部访问

# 进入到容器
docker exec -it mysql bin/bash
# 登录mysql
mysql -u root -p
# 选择数据库
use mysql;
# 查询用户信息
select host,user from user;
# localhost只允许从本地访问，%允许所有的外部ip访问，如果是localhost，则需要改为%
update mysql.user set host='%' where host='localhost' and user='root';
# 变更root密码，注意这里的'root'@'%'一定要和用户信息对的上，
# 如果用户是localhost类型，那么就用'root'@'localhost'，否则会报错
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123';
# 更新一下
flush privileges;

如果'root'@'%'和用户信息不一致，会报如下错误：

ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'

比如我的mysql查询用户信息结果如下：

如果使用'root'@'%'，就会报错。

值得一提的是，'root'@'%'和'root'@'localhost'是可以同时存在，且密码不一样的，即可以同时存在同样名字的两种不同类型的用户。比如下面的截图显示的：

 host为%的root账号密码是123，host为localhost的root账号密码是123456，在内网情况下，只能使用123456这个密码登录root账户，即登录的是localhost的root账户，在外网情况下，只能使用123这个密码登录root账户，即登录的是%的root账户。

5.创建用户并赋予权限

# 创建用户，指定所有外部ip可以连接sa
create user 'test'@'%' identified by 'sa';
# 赋予权限，赋予出drop以外的其他权限，如果要赋予所有权限，则使用all代替具体的权限
grant select,insert,delete,update,create,alter on *.* to 'test'@'%';
# 刷新权限
flush privileges;

6.使用表达式设置默认值：

CREATE TABLE `mydb`.`new_table` (
  `id` VARCHAR(32) NOT NULL DEFAULT (replace(uuid(),"-","")),
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE);

貌似这个功能只有mysql8才支持，具体哪些版本支持我没有深究，上面的语句是使用replace(uuid(),"-","")这个uuid表达式作为id的默认值。

7.mysql报Too many connections错误。

出现这个报错，是因为mysql的最大连接数太小，而连接休眠时间设置太长，导致连接满了，无法建立新的连接，解决步骤如下：

• 使用命令：mysql -u root -p，进到mysql客户端。
• 使用命令：

  show processlist;

   可以查看到当前有哪些连接，你会发现mysql此时可能存在大量state为sleep的连接，这些sleep连接都是可以关掉的，关掉方法如下：

  # 使用id删除连接
  kill 123456;

• 使用命令：

  show variables like "max_connections";

   可以查看最大连接数。

• 使用命令：

  set GLOBAL max_connections=1000;

   修改最大连接数。

8.查询分组后最大值所在行的全部数据，比如下面的数据：

pro	city	gdp
anhui	hefei	100
anhui	huangshan	90
henan	zhengzhou	105

上面的数据按照省份pro进行分组，找到gdp最高的城市数据，期望结果如下：

pro	city	gdp
anhui	hefei	100
henan	zhengzhou	105

如果你直接用下面的语句查询：

select pro,city,max(gdb) from table group by pro;

在某些数据库可能查出来正确的结果，但是在mysql数据库这个结果是不确定的，因为数据库不知道应该返回哪条数据，同时在5.7版本之后，数据库默认增加了only_full_group_by模式，要求查询字段必须属于group by里面的字段，否则数据库会报错。

这里提供一个方法：

select * from table a where a.gdp=(select max(gdp) from table where pro=a.pro)

上面的语句不是特别好理解，大概过程是这样的：数据库在一行一行轮训数据的时候，同时找到所有等于当前行的pro值的数据行，找出他们的gdp最大值，然后判断当前行的gdp是否与该最大值相等，相等则返回。

另外除了这个方法，还可以通过inner join实现，同时在mysql8.0之后，还可以通过窗口函数row_number()嵌套子查询来获取我们想要的结果。

但是遗憾的是，这些方法效率其实都不太高，理论上来讲，这个查询需求在没有索引的前提下只需要轮训一遍全部数据就可以得到我们想要的结果集，但是，以上提供的三种方法，都不止轮训一遍全部数据。通过inner join和row_number这两种方法，效率都是要比我提供的第一种方法要低的，而第一种方法，也需要轮询两次全部数据。