MySQL 数据库优化汇总

---

1. SQL 语句优化

尽量避免使用子查询，使用连接 JOIN 来代替子查询
使用联合 UNION 来代替手动创建的临时表
避免函数索引
用 IN 代替 OR
避免数据类型不一致
禁止不必要的 order by 排序
尽量不使用 select * 
可通过开启慢查询日志来找出较慢的 SQL 语句
不做列运算，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
sql 语句尽可能简单，一条 sql 只能在一个 cpu 运算。大语句拆小语句，减少锁时间，一条大 sql 可以堵死整个库
尽量避免在 WHERE 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描

2. 索引优化

应尽量避免在 WHERE 子句中对字段进行 NULL 值判断，否则将导致引擎放弃使用索引而进行全表扫描
值分布很稀少的字段不适合建索引，例如`性别`这种只有两三个值的字段
字符字段只建前缀索引
字符字段最好不要做主键
不用外键，由程序保证约束
尽量不用 UNIQUE，由程序保证约束
使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引

3. 表结构优化

`使得数据库表结构符合三大范式与 BCNF`

设计符合第三范式的表结构
尽量使用数字型字段，提高数据比对效率。
对定长、MD5哈希码、长度较短的字段使用char类型，提高效率；对边长而且可能较长字段使用varchar类型，节约内存。
适当的进行水平分割与垂直分割，比如当表列数过多时，就将一部分列移出到另一张表中。

4. 系统参数配置优化

wait_timeout：数据库连接闲置时间，闲置连接会占用内存资源。可以从默认的8小时减到半小时

max_user_connection:最大连接数，默认为0无上限，最好设一个合理上限thread_concurrency：并发线程数，设为CPU核数的两倍

skip_name_resolve：禁止对外部连接进行DNS解析，消除DNS解析时间，但需要所有远程主机用IP访问

10、key_buffer_size：索引块的缓存大小，增加会提升索引处理速度，对MyISAM表性能影响最大。对于内存4G左右，可设为256M或384M，通过查询show status like'key_read%'，保证key_reads / key_read_requests在0.1%以下最好

innodb_buffer_pool_size：缓存数据块和索引块，对InnoDB表性能影响最大。通过查询show status like 'Innodb_buffer_pool_read%'，保证 (Innodb_buffer_pool_read_requests– Innodb_buffer_pool_reads)/ Innodb_buffer_pool_read_requests越高越好

innodb_additional_mem_pool_size：InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，当数据库对象非常多的时候，适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率，当过小的时候，MySQL会记录Warning信息到数据库的错误日志中，这时就需要该调整这个参数大小

innodb_log_buffer_size：InnoDB存储引擎的事务日志所使用的缓冲区，一般来说不建议超过32MB

5. 硬件优化

根据 MySQL 是 CPU 密集型还是 I/O 密集型，通过提升 CPU 和内存、使用 SSD 等方式能显著提升 MySQL 性能
mysql 是关系型数据库，适合垂直升级

6. 引擎优化

根据实际情况选择合适的引擎

7. 读写分离

搭建读写分离架构，master 只负责写，slave 只负责读

8. 缓存

系统内部调整相关缓存参数
针对前端 web 页面做缓存
mysql 前置缓存服务器（redis）

9. 分库分表

如果表或库数据量实在太大，需要开发进行分库分表