MySQL FAQ 常见问题

Question

使用 mysqldump 时报错 Warning: World-writable config file '/etc/mysql/my.cnf' is ignored ，是使用 docker-compose 运行 mysql 的，在 这里 看到是配置文件的权限不对，对外部卷的配置文件进行修改 sudo chmod 0644 /etc/mysql/my.cnf ，重启 mysql 后正常运行

用 load data infile 导入数据报错： The MySQL server is running with the --secure-file-priv option so it cannot execute this statement ，最简单的方法是增加一个 local 关键字 load data local infile

local data local infile 报错： The used command is not allowed with this MySQL version MySQL: Enable LOAD DATA LOCAL INFILE

查看 mysql 的警告： show warnings;

求表里分组的最大值的记录: SQL select only rows with max value on a column

如果要求的只有两列数据

select id
      , max(rev)
from yourtable
group by id

如果除了分组列和值列还有别的列

select a.id
      , a.rev
      , a.contents
from yourtable a
inner join
(
    select id
          , max(rev) rev
    from yourtable
    group by id
) b
  on a.id = b.id
  and a.rev = b.rev

-- left joining with self, tweaking join conditions and filters
-- 较快
select a.*
from yourtable a
left outer join yourtable b
  on a.id = b.id
  and a.rev < b.rev
where b.id is null

----------------------------------------
-- 额外的方式
----------------------------------------
-- 关联子查询 (较慢,有 max)
select name,course,score
from yourtable a
where rev =
(
  select max(rev)
  from yourtable b
  where a.id = b.id
)
-- no exists
select *
from yourtable a
where not exists
(
  select 1
  from yourtable b
  where a.id = b.id
    and a.score < b.score
);

查看表里的 top n 条记录, MySQL 获取分组后的 TOP 1 和 TOP N 记录

-- 数据分析较小的话直接用 union all 将多个分组的值拼接起来
select name,course,score from test1 where course='语文' order by score desc limit 2
union all
select name,course,score from test1 where course='数学' order by score desc limit 2
union all
select name,course,score from test1 where course='英语' order by score desc limit 2

链接 mysql 时报错 MySQL 无法连接[MySql Host is blocked because of many connection errors] : 同一个 ip 在短时间内产生太多（超过 mysql 数据库 max_connection_errors 的最大值) 中断的数据库连接而导致的阻塞，解决方法 mysqladmin flush-hosts -h192.168.1.1 -P3308 -uroot -ppassword

程序出现了复杂 sql

可以将复杂 sql 拆分成多个简单 sql 进行查询，例如统计里面有部分维度是空导致统计结果缺失，第一次查询可以先查询所有的维度，然后生成一个所有维度为 key，0 为 value 的 Map，第二次查询统计数据库的值，然后更新这个 Map 得到最后的结果.

Map<String, Long> hourStatsMap = new HashMap<>();
for (int i = 0; i <= 23; i++) {
    hourStatsMap.put(Integer.toString(i), 0L);
}

hourStats = appMonitorService.getHourStats(year, month, day, appId);
for (Map m : hourStats) {
    String key = m.get("hour").toString();
    Long count = (Long) m.get("count");
    hourStatsMap.put(key, count);
}

in 和 exists 的区别

in 是把外表和内表作 hash 连接，而 e xists 是对外表作 loop 循环，每次 loop 循环再对内表进行查询.不能认为 exists 比 in 的效率高

• 如果查询的两个表大小相当，那么用 in 和 exists 差别不大
• 如果两个表中一个较小一个较大，则子查询表大的用 exists，子查询表小的用 in

mysql 累加和累减

可以将要累/减的主键自关联，并通过聚合得到累加/减的情况

累加

-- 初始化
drop table if exists cum_demo;
CREATE TABLE cum_demo (id INT,money INT,starttime int,PRIMARY KEY (id));
insert into cum_demo(id,money,starttime) values (1,10, 201901),(2,20, 201902),(3,30, 201903),(4,40, 201904);

-- 自关联及其结果
select *
from cum_demo a
left outer join cum_demo b
  on a.starttime >= b.starttime;

-- 统计的结果
select a.starttime
     , sum(b.money) as asum
from cum_demo a
left outer join cum_demo b
  on a.starttime >= b.starttime
group by a.starttime

累减

ddl 中的 not null default 和想象中的可能不太一样

看看 这个问题 , 详细的解释有 这个 , 但是如果插入多行记录中有 null 值就没问题, 见这里

-- 创建一张有 default value 的表
create table table_name(
  id int,
  name varchar(100) not null,
  sex char(1) not null defalut 'M'
);

-- 下面的 insert 语句会报错 sex 不能为 null 值 因为 ddl 中声明了不能为 null
insert into table_name(id, name, sex) value(1, 'myName', null);
-- 正确的做法应该是用 default 关键字声明使用默认值 或者在 table 后面不加对应的字段
insert into table_name(id, name, sex) value(1, 'myName', default);
insert into table_name(id, name) value(1, 'myName');

-- 不会报错但是有 2 个 warning
-- 这是另外一个问题了
-- 这时 mysqld 的配置问题 sql_mode 没有声明成 strict_all_tables, 声明了之后也会报错 见[21]
insert into table_name(id, name, sex) values(1, 'myName', 'F'), (2, 'yourName', null);

-- 如果想要实现我们预想的情况 可以写一个插入前的触发器 就能处理在插入前转换值了
CREATE TRIGGER `trg_table_name_sex_default_value` BEFORE INSERT ON `table_name`
  FOR EACH ROW
  BEGIN
    IF (NEW.`sex` IS NULL) THEN
      SET NEW.`sex` := (
        SELECT `COLUMN_DEFAULT`
        FROM `information_schema`.`COLUMNS`
        WHERE `TABLE_SCHEMA` = DATABASE()
          AND `TABLE_NAME` = 'table_name'
          AND `COLUMN_NAME` = 'sex'
      );
    END IF;
  END
-- 或者在应用的层名做 每次调用插入的时候先检查插入的字段是否有空值 有的话将其变成设置的默认值（推荐)

正则匹配

• 匹配数字: [0-9] 或者 [[:digit:]]
• 匹配前面多个数字: [0-9]{10} 或者 [[:digit:]]{10}

运行 mysql 的查询结果

• 在 mysql 里面时.使用 INTO OUTFILE 到指定的文件,然后 source 文件,详见 这里

SELECT CONCAT('OPTIMIZE TABLE `', ist.TABLE_SCHEMA,'`.',  ist.TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES ist
WHERE table_schema = 'my_schema'
INTO OUTFILE '/tmp/my_optimization';
SOURCE 'tmp/my_optimization';

• 如果想要直接在 shell 中调用

# 这个会产生很多无用的输入 如果仅想要保存自己需要的文件到 /path/to/results.txt 可以在重定向前增加 grep -E 进行正则过滤
mysql -vv -u usr -p pwd < sql_file_name > /path/to/results.txt

datetime 和 timestamp 的选择

官方文档说明

The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

...

The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in.

• datetime 是一个常量, timestamp 是一个随着 time_zone 变化的变量,是一个确切的时间（相对 UTC) 的时间
• 应该使用 datetime 作为元数据的记录字段,因为范围更加广,且是固定值,不会随着时区变化而变化.如果想要配置插入和更新的默认值,使用 datetime not null default current_timestamp on update current_timestamp
• timestamp 经常用于追踪记录的变化,记录每次更新他也会更新.如果想要储蓄具体的时间请使用 datetime
• datetime 代表日期（如日历所见) 和时间（如时钟所见), timestamp 代表具体的带有时区的时间点
• 对 datetime 查询不会被缓存,但是对 timestamp 查询会被缓存
• 如果想在应用中使用 unixtime ,还是应该使用 datetime 类型,因为可以方便的使用 select unix_timestamp('2019-01-01')

mysql 紧急情况处理

突然的业务办理卡顿,无法进行正常的业务处理

-- 查看进程列表
show processlist
-- 查看执行计划
explain select id ,name from stu where name='clsn';
-- 查看表的索引
show index from table;
-- 通过执行计划判断,索引问题（有没有、合不合理）或者语句本身问题

-- 查看锁状态
show status  like '%lock%';
-- 杀掉有问题的 session
kill SESSION_ID;

not in 遇到 null 时的特殊情况

MySQL NOT IN query giving wrong results : 如果 not in 遇到 null,会返回 0 条结果

kill 了 process id 但是还在运行

通过 show processlist 获取了进程 id 后，通过 kill id 命令删除了进程但是再次运行 show processlist 还能看到 id，不过状态是 killed ，那是因为数据库可能在回滚事务，参考 这里

使用 range to_days(field) 的分区表任何查询都会检索第一个分区

mysql 分区表使用了 range to_days ，如果第一个分区有很多记录，可能会带来性能问题，原因是 这里 ，mysql 的查询优化器无论查询的条件如果都会检索第一个分区，解决办法设置一个条件将第一个分区记录数变成 0，这样即使检索第一个分区也不会影响性能。 相关的链接

mysql 索引使用函数

mysql8.0.13 之前的都不支持，仅有 8.0.13 以及之后的才支持， 这里

如果分区表中创建了非分区字段的索引会怎么样

在《高性能 Mysql》这本书的‘如何使用分区’这一小章中，列举的常见问题中。有以下一个问题：分区列和索引列不匹配如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤。

假设在列 a 上定义了索引，而在列 b 上定义的分区。因为每个分区都有其独立的索引，所以扫描列 b 上的索引就需要扫描每个分区内对应的索引。要避免这个问题，应该避免建立和分区列不匹配的索引，除非查询中还同时包含了可以过滤分区的条件。

MySQL 慢查询解决思路

原因

• 计算资源不足
• 系统层面未进行基本的优化，或不同进程间资源抢占
• MySQL 配置不科学 附神器
• 垃圾 SQL 满天飞

查看系统层面负载手段

• top 查看整体负载情况，快速确认哪个进程系负载高
• free 查看内存情况，是否有内存泄露和用了 swap 等风险
• vmstat/sar 查看当前系统瓶颈到底在哪，如 CPU、IO、网络等
• 终极神器 perf top 查看 cpu 消耗在哪些系统调用函数

查看 MySQL 的整体情况

• 观察 show processlist 输出中是否有临时表、排序、大量逻辑读、锁等待等状态
• 观察 show engine innodb status 输出中是否有大事务、长事务、锁等待等状态

干掉垃圾 SQL，常用手段

• 用 explain 、 desc 观察执行计划
• 用 profiling 定位 sql 执行的瓶颈
• 用 pt-query-digest 分析慢 sql

几个窍门

• mysqld 进程消耗 CPU 长时间超过 90% 的话，99.9% 是因为没用好索引
• cpu 的 %sys 高的话，大概率是 swap 或中断不均衡导致，也可能是有多个索引且超高并发写入(更新)，或者有很严重的锁等待事件
• 最⼤的瓶颈 通常是在磁盘 I/O 上，因此尽量用高速磁盘设备
• 如果物理磁盘无法再升级，则通过增加内存提升性能容量
• 遇到无法诊断的问题时，试试⽤ perf top 来观测跟踪
• SQL 执行慢，有时未必是效率低，也可能是因为锁等待，甚⾄是磁盘满了

对过滤字段进行组合会导致索引失效

EXPLAIN
SELECT *
FROM sszpay_order
-- 这个组合的 in 查询会导致 sku_id 和 user_id 的索引失效
WHERE (sku_id, user_id) IN ((12067, 5), (12583, 5))

Ref

• st-How should I tackle --secure-file-priv in MySQL
• creating a random number using MYSQL
• best way to test if a row exists in a mysql table
• Mysqldump: create column names for inserts when backing up