MySQL 调优总结

一. 表设计与数据类型

尽量遵循数据库设计三大范式，一些场景可适当建立冗余字段，减少连接查询，但这样这样会破坏第三范式，视情况而定。

尽量不要设置 null 类型字段，null 类型需要额外的字段来存储，count 不会统计，一些场景也会使索引失效。

使用 UNSIGNED 无符号类型，可提高正数的数量上限，在一些不需要存储负数的情况下，将类型设置无符号数。

没有太大的必要使用 BigDecimal 类型，可用 bigint，将小数方法 10^n 存入，这样可以避免浮点数计算不准确和 DECIMAL 精确计算代价高的问题。

表中的列不要太多，如果列太多而实际使用的列又很少的话，有可能会导致 CPU 占用过高。

把 IP 地址存成 UNSIGNEDINT，IP 地址转换成 int 类型正好是 int 的有符号取整范围

设置固定长度的字段会提高性能，因为 MySQL 搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。但是固定长度的字段会浪费一些空间

url 可以将数据倒序存入数据库,或者字符串进行 crc32 哈希处理

分库分表

二. 索引

创建适当数量的索引，在创建表的时候就创建好索引，尽量避免在表中有大量数据的时候创建索引。

合适的场景创建覆盖索引

在区分度高的字段上建立索引

控制单个索引的长度，如 key(name(8))

尽量使用自增 id

在数据不会重复的列上建立唯一索引

三. SQL

每一条 sql 都尽量使用 explain 查看执行计划，防止慢 sql 出现

遵循符合索引最左匹配原则

尽量不要在索引列上做表达式运算，或使用函数，会使索引失效

尽量不要使用多个范围查询，会使索引失效

查看执行计划，如果索引的使用优化器优化有问题，可以强制使用想要适应的索引 force index

or 查询用 union 优化代替，如果是相同字段的 or，优化为 in 查询

数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串，一定要将字符常量值用引号引起来。

like 查询尽量使用左匹配

join 查询，on 连接的字段上一定要建立索引

避免 select *，避免返回无用字段

insert 批量插入，不要单条插入

当仅需要获取一条数据时，使用 limit 1

四. 程序层面

使用数据库连接池连接数据库

使用缓存对查询数据做缓存，如 MyBatis 的一二级缓存、redis 缓存

五. innodb 参数

max_connections：允许客户端并发连接的最大数量，默认值是 151，一般将该参数设置为 300-1000

max_connect_errors：如果客户端尝试连接的错误数量超过这个参数设置的值，则服务器不再接受新的客户端连接。可以通过清空主机的缓存来解除服务器的这种阻止新连接的状态，通过 FLUSH HOSTS 或 mysqladmin flush-hosts 命令来清空缓存。这个参数的默认值是 100，一般将该参数设置为 100000。

innodb_buffer_pool_size 缓存索引和行数据，在专属 mysql 服务器上，可设置为内存的 80%左右，可减少索引存储在磁盘上，减少 IO

innodb_thread_concurrency（5.7 版本中已废除） InnoDB 内核中允许的线程数，这个值取决于，硬件，应用，OS scheduler properties，设置的太高会线程上下文切换频繁，降低效率，所以不要设置的太高。推荐 4 核 4 线程（4C4T）值设置为 4-8 即可，最大不建议超过线程数*2

innodb_write_io_threads： 限制写相关的线程，缺省值为 4

innodb_read_io_threads： 限制写相关的线程，缺省值为 4

还有很多其他相关参数，可视情况设置。

六. 架构

主从架构，读写分离。