too many connection 报错可能原因

  1. 机器负载飙升，导致SQL执行效率下降，导致连接堆积

  2. 业务访问量突增（或者有SQL注入现象），导致连接数打满

  3. 出现“死锁”或者锁竞争严重，导致大量SQL堆积【如事务没有正确关闭、频繁更新导致资源争用等】

大表数据删除方案

原始表：friend_record

新建表：friend_record_2

思路：先将原始表需要保留的数据迁移到新建的表，然后再改表名。这样可以避免锁表，不影响正常业务。

  1. create table friend_record_2 like friend_record;

  2. ALTER TABLE friend_record_2 CHANGE type type TINYINT(3) UNSIGNED NOT NULL DEFAULT ‘0’ COMMENT ‘0陌生添加、1推广推介、2被动添加、3主动添加’;

  3. 这里需要加上旧表当前最大 id insert ignore into friend_record_2(owner_account_id, account_id, remark, add_time, type, created_at, updated_at, deleted_at) (select owner_account_id, account_id, remark, add_time, type, created_at, updated_at, deleted_at from friend_record where id <= ? and created_at >= ‘2020-01-01’ and deleted_at is null);

  4. rename table friend_record to friend_record_202009;

  5. rename table friend_record_2 to friend_record;

  6. 这里需要加上旧表当前最大 id insert ignore into friend_record(owner_account_id, account_id, remark, add_time, type, created_at, updated_at, deleted_at) (select owner_account_id, account_id, remark, add_time, type, created_at, updated_at, deleted_at from friend_record_202009 where id > ? and deleted_at is null);

权重排序

假定表 table 包含 A B C 三个字段，需要按照 50 30 20 的权重排序：

select * from table order by A0.5+B0.3+C*0.2 desc;XX

使用 DELETE JOIN 删除单表重复数据

1
2
3
4
5
6

# 只保留最大 id 的数据，等清理数据后，再把字段 room_id+member_uid 设置为唯一索引
#  tip: 两百万记录的表 delete 很快，两千万的表半个小时还删不完
delete a
from room_user as a
join room_user as b on a.room_id=b.room_id and a.member_uid=b.member_uid # 笛卡尔乘积
where a.id<b.id

选择合适的 MySQL 日期时间类型来存储你的时间

构建数据库写程序避免不了使用日期和时间，对于数据库来说，有多种日期时间字段可供选择，如 timestamp 和 datetime 以及使用 int 来存储 unix timestamp。

datetime(8个字节) 和 timestamp(4个字节)

  1. datetime 更像日历上面的时间和你手表的时间的结合，就是指具体某个时间。
  2. timestamp 更适合来记录时间，比如我在东八区时间现在是 2016-08-02 10:35:52， 你在日本（东九区此时时间为 2016-08-02 11:35:52），我和你在聊天，数据库记录了时间，取出来之后，对于我来说时间是 2016-08-02 10:35:52，对于日本的你来说就是 2016-08-02 11:35:52。所以就不用考虑时区的计算了。
  3. 时间范围是 timestamp 硬伤（1970-2038），当然 datetime （1000-9999）也记录不了刘备什么时候出生（161 年）。

timestamp 和 UNIX timestamp(4个字节)

  1. 显示直观，出问题了便于排错，比好多很长的 int 数字好看多了
  2. int 是从 1970 年开始累加的，但是 int 支持的范围是 1901-12-13 到 2038-01-19 03:14:07，如果需要更大的范围需要设置为 bigInt。但是这个时间不包含毫秒，如果需要毫秒，还需要定义为浮点数。datetime 和 timestamp 原生自带 6 位的微秒。
  3. timestamp 是自带时区转换的，同上面的第 2 项。
  4. 用户前端输入的时间一般都是日期类型，如果存储 int 还需要存前取后处理

总结：

  1. timestamp 记录经常变化的更新 / 创建 / 发布 / 日志时间 / 购买时间 / 登录时间 / 注册时间等，并且是近来的时间，够用，时区自动处理，比如说做海外购或者业务可能拓展到海外
  2. datetime 记录固定时间如服务器执行计划任务时间 / 健身锻炼计划时间等，在任何时区都是需要一个固定的时间要做某个事情。超出 timestamp 的时间，如果需要时区必须记得时区处理
  3. UNIX timestamps 使用起来并不是很方便，至于说比较取范围什么的，timestamp 和 datetime 都能干。
  4. 如果你不考虑时区，或者有自己一套的时区方案，随意了，喜欢哪个上哪个了