笔记：Mysql踩坑及配置优化

配置优化

优化配置与具体的服务器硬件配置以及软件业务设计有关，如有疑问请保持默认配置。
mysql.cnf 摘自网络，仅供参考！

[mysqld]
#######设置连接、工作线程相关#########
#连接、进程相关参数，受限于系统内核的单进程最大打开文件数
#最大连接数，此值的设定，需要在具体服务器上大量尝试，并根据其压力测试结果决定
max_connections=3000
#当连接已满时，等待队列的大小
back_log=500
#空闲连接超时时长(秒)，当空闲连接超过该时间，会被关闭
wait_timeout=1800
#处理线程缓存数，相当于空闲线程的最大值，能避免线程频繁创建与销毁的花销
thread_cache_size=512
#SQL语句解析后，允许同时使用InnoDB数据引擎的线程数，一般是核心数的2倍
innodb_thread_concurrency=8

#########设置缓存相关##########
#设置缓存可减少磁盘I/O操作，使得读取数据的操作更快
#设置缓冲池的大小，一般是服务器内存的60%-70%。缓冲池是保存索引和原始数据的地方，可减少磁盘I/O；缓冲池是针对InnoDB数据引整(默认引擎)的设置
innodb_buffer_pool_size=10240M
#设置每个表一个独立的数据文件，一定程度上避免文件读写竞争，但数据文件总大小会变大
innodb_file_per_table=1
#mysqld上所有session中prepared语句的上限，加快SQL语句解析速度
max_prepared_stmtcount=100000
#表示在每次事务提交的时候会把log_buffer刷到文件系统中去，但并不会立即刷写到磁盘
#数据库崩溃可能会丢失数据。如果数据需要确保不丢失，把此设置去掉或设置成1
innodb_flush_log_at_trx_commit=2
##########写入优化相关###########
#设置redo日志的大小，最佳为业务峰值1小时的业务日志大小
innodb_log_file_size=1500MB
#决定innodb数据文件及redo log的打开、刷新方式
#此设置会跳过系统的io缓存，从磁盘上读取。此方式不一定性能最好，但能保证数据稳定写入
innodb_flush_method=O_DIRECT

一些常见的坑

1. 访问控制（User和主机）;
2. 禁止业务系统使用root，root应作为保留管理员账号;
3. 字符集建议都设置为utf8mb4;
4. 表必须有主键，且设置id为自增主键;
5. 表字段不得超过30个，且单表数据最好不要超过400w，大数据进行合理分表（水平/垂直）;
6. 库表字段等要规范且有意义的命名，使用蛇形命名且禁止大写;
7. 引擎类型一律为InnoDB，如无特殊需求。禁止使用InnoDB/MyISAM/Memory以外的存储引擎;
8. 表字段禁止NULL，必须NOT NULL default 默认值，NULL值会存在占用额外存储空间、索引失效等其他问题;
9. 字段最好不要出现blob、text，实在需要时请使用拓展表;
10. 索引个数最好不要超过5个,唯一索引以uq_开头，普通索引以idx_开头;
11. join必须使用索引，in查询不要超过1000，合理使用join与in(笛卡尔积与子查询不用索引);
12. 禁止写select *，同理禁止insert into t1 values(…);
13. UNION ALL替代UNION;
14. DML语句where条件必须用索引，注意表锁问题;
15. 合理使用索引，注意索引左原则、索引失效，null值等的影响;

其他分享：
为什么 MySQL 使用 B+ 树
MySQL 和 InnoDB

本作品采用《CC 协议》，转载必须注明作者和本文链接