笔记：Mysql踩坑及配置优化
3 / 10 / 创建于 3年前 /
Diego_crazy 的个人博客
配置优化#
优化配置与具体的服务器硬件配置以及软件业务设计有关，如有疑问请保持默认配置。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
一些常见的坑#
- 访问控制（User 和主机）;
- 禁止业务系统使用 root，root 应作为保留管理员账号；
- 字符集建议都设置为 utf8mb4;
- 表必须有主键，且设置 id 为自增主键；
- 表字段不得超过 30 个，且单表数据最好不要超过 400w，大数据进行合理分表（水平 / 垂直）;
- 库表字段等要规范且有意义的命名，使用蛇形命名且禁止大写；
- 引擎类型一律为 InnoDB，如无特殊需求。禁止使用
InnoDB/MyISAM/Memory以外的存储引擎； - 表字段禁止
NULL，必须NOT NULL default默认值，NULL值会存在占用额外存储空间、索引失效等其他问题； - 字段最好不要出现
blob、text，实在需要时请使用拓展表； - 索引个数最好不要超过 5 个，唯一索引以
uq_开头，普通索引以idx_开头； - join 必须使用索引，in 查询不要超过 1000，合理使用 join 与 in (笛卡尔积与子查询不用索引);
- 禁止写
select *，同理禁止insert into t1 values(…); UNION ALL替代UNION;- DML 语句
where条件必须用索引，注意表锁问题； - 合理使用索引，注意索引左原则、索引失效，null 值等的影响；
其他分享：
为什么 MySQL 使用 B+ 树
MySQL 和 InnoDB
本作品采用《CC 协议》，转载必须注明作者和本文链接
关于 LearnKu
粤公网安备 44030502004330号
推荐文章：