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的影响。