MySQL优化

MySQL优化框架

1. SQL语句优化：是否使用索引，避免全表扫描，避免联合查询
2. 索引优化：了解索引的使用场景，选择合适的索引。
3. 数据库结构优化：数据库结构的设计，避免多表查询等。分表分库
4. InnoDB表优化：引擎是表级别概念，考虑哪个表使用哪个引擎。
5. MyISAM表优化
6. 理解查询执行计划：SQL语句优化的前提。explain语句
7. 缓冲和缓存：上述优化之后还慢，考虑缓冲和缓存。二八原则缓存，或者表结构文件的缓存，innodb(.frm,.ibd)，myism(.myd,.myi,.frm)
8. 锁优化：更细力度的锁，表锁，支持更多的并发请求。
9. MySQL服务器优化：mysql自身的缓存。

三个层面上优化

• MySQL级别
• OS级别
• 硬件级别

MySQL级别：

1. 每个表字段的字段类型是否是最佳。如char 定长 和varchar可变的
2. 表数据量足够大的时候，进行分表
3. 创建合适的索引，如何hash索引不支持查询年龄大于30小于多少范围性的。场景不一样。
4. 读多写少，myism性能要好，但是现在innodb的速度也不会慢myisam多少。
5. 是否使用了合适的锁策略。
6. 是否为InnoDB的缓冲池、MyISAM的键缓存以及MySQL查询缓存设定了合适大小的内存空间，以便能够存储频繁访问的数据且又不会引起页面换出。

操作系统和硬件级别的优化着眼点：

1. 是否为实际的工作负载选定了合适的CPU，Mysql是CPU密集型的应用程序，因此选择更快的CPU，多核，支持超线程。
2. 是否有合适大小的物理内存，避免用到swap分区。
3. 优化网络性能，减小网络延迟。
   4.是否基于操作系统选择了合适的文件系统。
   5.mysql线程池。

主索引（聚集索引）：索引和数据放在一起。innodb使用的就i是聚集索引, .ibd文件存放。
辅助索引（非聚集索引）：索引和数据不存放在一起，索引中存放着指向数据的数据块地址，myisam使用的是非聚集索引，.myd,.myi 文件
辅助索引还需要通过主索引来查找，所以需要进行两次索引，对表字段创建一个合适的主键，如果没有合适的主键也最好使用AUTOINCREMENT类型的某字段作主键。

生产环境 my.cnf 配置案例

配置文件生成工具参考链接：https://imysql.com/my_cnf_generator
参考硬件：内存 32G

#打开独立表空间
innodb_file_per_table = 1
#MySQL 服务所允许的同时会话数的上限，经常出现Too Many Connections的错误提示，则需要增大此值
max_connections = 8000
#所有线程所打开表的数量
open_files_limit = 10240
#back_log 是操作系统在监听队列中所能保持的连接数
back_log = 300
#每个客户端连接最大的错误允许数量，当超过该次数，MYSQL服务器将禁止此主机的连接请求，直到MYSQL服
#务器重启或通过flush hosts命令清空此主机的相关信息
max_connect_errors = 1000
#每个连接传输数据大小.最大1G，须是1024的倍数，一般设为最大的BLOB的值
max_allowed_packet = 32M
#指定一个请求的最大连接时间
wait_timeout = 10
# 排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序
sort_buffer_size = 16M
#不带索引的全表扫描.使用的buffer的最小值
join_buffer_size = 16M
#查询缓冲大小
query_cache_size = 128M
#指定单个查询能够使用的缓冲区大小，缺省为1M
query_cache_limit = 4M  
# 设定默认的事务隔离级别
transaction_isolation = REPEATABLE-READ
# 线程使用的堆大小. 此值限制内存中能处理的存储过程的递归深度和SQL语句复杂性，此容量的内存在每次连接时被预留.
thread_stack = 512K
# 二进制日志功能
log-bin=/data/mysqlbinlogs/
#二进制日志格式
binlog_format=row
#InnoDB使用一个缓冲池来保存索引和原始数据, 可设置这个变量到物理内存大小的80%
innodb_buffer_pool_size = 24G
#用来同步IO操作的IO线程的数量
innodb_file_io_threads = 4
#在InnoDb核心内的允许线程数量，建议的设置是CPU数量加上磁盘数量的两倍
innodb_thread_concurrency = 16
# 用来缓冲日志数据的缓冲区的大小
innodb_log_buffer_size = 16M
#在日志组中每个日志文件的大小
innodb_log_file_size = 512M
# 在日志组中的文件总数
innodb_log_files_in_group = 3
# SQL语句在被回滚前,InnoDB事务等待InnoDB行锁的时间
innodb_lock_wait_timeout = 120
#慢查询时长
long_query_time = 2
#将没有使用索引的查询也记录下来
log-queries-not-using-indexes

MySQL配置最佳实践

具体细则参考：
阿里巴巴Java开发手册：https://developer.aliyun.com/topic/java2020
58到家数据库30条军规解读：http://zhuanlan.51cto.com/art/201702/531364.htm