mysql防坑指南

1. MySQL连接数问题

MySQL里的max_connections参数代表mysql数据库的最大连接数，参数默认是151，显然不适用于生产，如果请求大于默认连接数，就会出现无法连接数据库的错误，会遇到too many connections的报错信息。

Mysql5.5、mysql5.6、mysql5.7：默认的最大连接数都是151，上限为：100000

• max_connections需要设置的一个合理的值，并需要做好监控，避免连接数满引发业务故障（生产环境建议5000-10000左右，没有固定的值，根据各自业务情况来定）。

• 另外如果数据库连接数过高，需要分析业务端是否存在大量刷MySQL连接的情况，比如大量的短连接，连接没有正常关闭，代码逻辑问题等。

2. MySQL文件句柄设置

• 在文件I/O中，要从一个文件读取数据，应用程序首先要调用操作系统函数并传送文件名，并选一个到该文件的路径来打开文件。该函数取回一个顺序号，即文件句柄（file handle），该文件句柄对于打开的文件是唯一的识别依据。要从文件中读取一块数据，应用程序需要调用函数ReadFile，并将文件句柄在内存中的地址和要拷贝的字节数传送给操作系统。当完成任务后，再通过调用系统函数来关闭该文件。

• 在使用MySQL的过程中，有些用户碰到了打开文件句柄数过多的错误，查看用户实例的打开句柄个数，确实超过了系统设置的值，一旦出现了这种错误，将会带来连锁的各种错误(取决于当时正在操作什么类型的文件，以及什么操作)。

• open_file_limits的设置的值，mysqld会通过setrlimit系统调用来初始化本进程可以使用的最大文件句柄数。

• 操作系统范围限制，可以通过编辑/etc/sysctl.conf或编辑指令fs.file-max来增加Linux中打开文件的限制。

3. 注意SQL隐式转换的坑 

在开发规范中，我们往往会要求研发避免在where条件中出现隐式类型转换，什么是隐式转换：即在where语句中条件的值和条件对应的列的数据类型不一致。如 where id=‘123’，而id的类型为bigint，或者where code=100，而code的类型为varchar，隐式转换会产生以下两个问题：

1. 隐式类型转换可能导致索引失效。

2. 隐式类型转换可能产生非预期的结果。

4. SQL为什么一会可以走到索引，一会走不到索引

有些时候开发同学会找到DBA，反馈有一条SQL有索引，之前也能走到索引，查询性能非常的高，突然收到慢查询报警，查了十多秒，这种情况产生的原因一般和数据内容有关。

MySQL如果通过索引选择扫描行数过多（大约20%以上，这个不是确定的），优化器会认为使用全表扫描更佳，从而会走全表扫描，当然很多时候其实还是走索引性能会更好一点，MySQL选择优化器并不是那么智能。

举个例子，微博的用户有一张评论表，查询我们这些小众用户的评论会走索引，查询非常的快，如果正好查询的是一个大V账号，有可能涉及数百万条数据导致没有走到索引。

5. 自增键重启后回溯问题

InnoDB 引擎的自增值，其实是保存在了内存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为 MySQL 重启前的值”。

在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。

举例来说，如果一个表当前数据行里最大的 id 是 10，AUTO_INCREMENT=11。这时候，我们删除 id=10 的行，AUTO_INCREMENT 还是 11。但如果马上重启实例，重启后这个表的 AUTO_INCREMENT 就会变成 10。也就是说，MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值。

6. 自增键用完怎么办

如果你用过或了解过MySQL，那你一定知道自增主键了。每个自增id都是定义了初始值，然后按照指定步长增长（默认步长是1）。

虽然，自然数是没有上限的，但是我们在设计表结构的时候，通常都会指定字段长度，那么，这时候id就有上限了。

在插入数据时有可能唯一主键冲、sql事务回滚、批量插入的时候，批量申请自增值等原因导致自增id是不连续的。

我们一般会将自增键的类型设置为int，数据范围为负21亿到正21亿，对于一个频繁插入删除数据的表来说，21亿是可能会被用完的，可能引发业务无法正常写入。

因此在建表的时候你需要考察你的表是否有可能达到这个上限，如果有可能，就应该创建成 8 个字节的 bigint unsigned。

另外可以将表的自增键做好监控，比如到达使用率的80%，就可以报警出来。

7. 大表删除hang的问题

MySQL里面直接对大表执行drop table删除有可能导致MySQL Hang住，对业务造成影响。删除超大表的前提是该表是独立表空间，然后按照如下步骤删除才能避免引起业务故障。

1)表创建一个硬链接

# du -sh pay_bills.ibd
175G       pay_bills.ibd
# 创建硬链接
# ln pay_bills.ibd pay_bills.ibd_hdlk

 2）执行表删除

在Linux中，每个存储文件都会有指向该文件的Inode Index，多个文件名可以通过相同Inode Index指向相同一个存储文件。

如果该文件名引用的Inode Index上还被其他文件名引用，则只会删除该文件名和Inode Index之间的引用

如果该文件名引用的Inode Index上没有被其他文件名引用，则删除该文件名和Inode Index之间的引用并删除Inode Index指向的存储文件。

实际上只是删除了对 pay_bills.ibd 的一个文件引用，我们 pay_bills.ibd_hdlk 对物理文件的引用还是存在的，就不会执行OS级别的删除操作，IO波动不大，降低对MySQL的影响。