MySQL数据库优化条目建议

## 分区表
建议：
1. 如果要进行分区的表有主键或者唯一键，那么分区键必须包含这个主键或唯一键，否则无法创建分区表
2.

## 索引
1. 最适合索引的列是出现在where子句中的列，或者连接子句中指定的列
2. 使用唯一索引：索引的列的基数越大，索引的效果就越好。如只包含M和F，则索引效果不大
3. 使用短索引：如果对字符串列进行索引，应该制定一个前缀长度（如果前缀长度范围内，多数值是唯一的）
4. 不要过度索引：索引都需要占用磁盘空间，会降低写性能，会降低修改的性能（修改表内容时，索引必须进行更新，有时可能需要重构）
5.

## 配置文件
1. 慢查询日志
2. 使用缓存查询，当表更改（结构和数据）后，查询缓存值的相关条目会被清空
```
have_query_cache    是否已配置高速缓存（默认为true）
query_cache_size    缓存区大小，单位MB，如：16M
query_cache_type    变量值从0~2
                    0或者off（缓存关闭）
                    1或on（缓存打开，使用SQL_NO_CACHE提示的SELECT除外）
                    2或者demand（只有带SQL_CACHE的select语句提供高速缓存）
query_cache_limit   单个查询能够使用的缓存大小，默认1M
```
## InnoDB内存优化
1. innodb_buffer_pool_size

InnoDB 存储引擎表数据和索引数据的最大缓存区大小（同时为数据块和索引块提供数据缓存），此值越大，缓存命中率就越高，访问InnoDB表需要的磁盘IO就越少，性能就越高。在一个专用的数据库服务器上，可以将80%的物理内存分配给innodb_buffer_pool_size，但是不要太大，以免造成页交换
2. innodb_buffer_pool_instances

默认值是1，表示InnoDB缓存池被划分到一个区域。适当地增加该参数（例如将该参数值设置为2），此时InnoDB被划分成为两个区域，可以提升InnoDB的并发性能。如果InnoDB缓存池被划分成多个区域，建议每个区域不小于1GB的空间
3. innodb buffer 刷新，延长数据缓存时间，减缓磁盘IO

* innodb_max_dirty_pages_pct：控制缓存池中脏页的最大比例，默认是75%，如果脏页的数量超过此值，InnoDB的后台线程将开始缓存刷新
    * innodb_io_capacity：代表磁盘系统的IO能力，其值在一定程度上代表磁盘每秒可完成IO的次数。默认值为200，对于转速较低的磁盘，如7200RPM的磁盘，可降低到100，对于固态硬盘和多个磁盘组成的盘阵，可以适当增大。
    
    如果innodb_buffer_pool_wait_free的值增长较快，则说明InnoDB经常在等待空闲缓存也，如果无法增大缓存池，那么应将innodb_max_dirty_pages_pct 调小，或将innodb_io_capacity 的值提高，以加快脏页的刷新。

4. innodb_additional_mem_pool_size

这个参数用来设置 InnoDB 存储的数据目录信息和其它内部数据结构的内存池大小。应用程序里的表越多，你需要在这里分配越多的内存。对于一个相对稳定的应用，这个参数的大小也是相对稳定的，也没有必要预留非常大的值。如果 InnoDB 用光了这个池内的内存， InnoDB 开始从操作系统分配内存，并且往 MySQL 错误日志写警告信息。默认值是 1MB ，当发现错误日志中已经有相关的警告信息时，就应该适当的增加该参数的大小。实际上，是需要根据项目的InnoDB表的数目相应地增加

## 调整MySQL并发相关的参数
1. 调整max_connections，提高并发连接

默认值151，如果状态变量connection_errors_max_connections不为零，且一直在增长，就说明不断有连接请求因数据库连接数已经达到最大允许的值而失败，应该考虑增大max_connections的值
    
2. 调整 back_log

back_log参数控制MySQL监听TCP端口时设置的挤压请求栈大小，5.6.6版本以前默认值为50，以后默认值是50+（max_connections/5),单最大不能超过900，如果需要数据库在较短时间内处理大量连接请求，可以考虑适当增大back_log的值
    
3. 调整 table_open_cache

每一个SQL执行线程至少都要打开1个表缓存，table_open_cache 控制所有SQL执行线程可打开表缓存的数量。这个参数的值应根据最大连接数及每个连接执行关联查询中所涉及表的最大个数（用N表示）来设定：
    max_connections*N
    
    在未执行flush tables 命令的情况下，如果MySQL状态变量opened_tables的值较大，就说明table_open_cache 设置得太小，应适当增大。增大table_open_cache的值，会增加MySQL对文件描述符的使用量，因此，也要注意评估open_files_limit 的设置是否够用。
4. 调整open_files_limit

MySQL打开文件描述符的最大数量，默认是1024
    
    * my.cnf里如果配置了open_files_limit，最后取值为 配置文件 open_files_limit，max_connections*5， wanted_files= 10+max_connections+table_cache_size*2 三者中的最大值。

* 如果my.cnf里如果没配置了open_files_limit，最后取值为max_connections*5，10+max_connections+table_cache_size*2，ulimit -n中的最大者
5. 调整 thread_cache_size
    
    为加快连接数据库的速度，MySQL会缓存一定数量的客户服务线程以备重用，通过 thread_cache_size 参数可控制，通过计算线程cathe的失效率（threads_created/connections）来衡量 thread_cache_size是否合适，该值越接近1，说明线程cache命中率越低，应考虑适当增加 thread_cache_size 的值。
6. innodb_lock_wait_timeout 的设置
    此参数可以控制InnoDB事务等待行锁的时间，默认是50ms。对于需要快速反馈的交互式OLTP应用，可以调小，以避免事务长时间挂起；对于后台运行的批处理操作，可以调大，以避免发生大的回滚操作。

7. read_buffer_size

MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。

8. join_buffer_size

用于完全联接的缓冲区的大小(当不使用索引的时候使用联接操作)。一般情况获得快速联接的最好方法是添加索引。当增加索引时不可能通过增加join_buffer_size值来获得快速完全联接。将为两个表之间的每个完全联接分配联接缓冲区。对于多个表之间不使用索引的复杂联接，需要多联接缓冲区。一般4-8M是一个比较合理的值，对于复杂的连接sql，可以在连接开始前手动指定join_buffer_size大小

## 优化数据库对象
优化表的数据类型

---

> by 刘迎光@萤火虫工作室  
> OpenBI交流群：495266201  
> MicroService 微服务交流群：217722918    
> mail: liuyg#liuyingguang.cn  
> 博主首页（==防止爬虫==）：http://blog.liuyingguang.cn  
> OpenBI问答社区：http://openbi.liuyingguang.cn/

Lighting@刘迎光

Navigation

Recent Posts

友情链接