37.MySQL 优化总结

对mysql优化时一个综合性的技术，主要包括 
a: 表的设计合理化(符合3NF)
b: 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]
c: 分表技术(水平分割、垂直分割)
d: 读写[写: update/delete/add]分离
e: 存储过程 [模块化编程，可以提高速度]
f: 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]
g: mysql服务器硬件升级
h: 定时的去清除不需要的数据,定时进行碎片整理(MyISAM) 

——————————————————————————————

1.数据库设计

1NF
2NF
3NF
适当的反范式

———————————————————————————————

2.sql 优化

3.数据库参数配置

    1.1 key_buffer_size

    1.2 table_cache (包含 .frm文件解析结果) // table_definition_cache,table_open_cache 关联值, Opened_tables 关联状态

    1.3 innodb_buffer_pool_size (服务器内存的 75%~80%)

    1.4 innodb_flush_log_at_trx_commit // 0, 把日志缓存写到日志文件，1s 刷新一次, 1 事务提交时刷新(默认，最安全), 2 每次提交时把日志缓存写到日志文件

    1.5 innodb_additional_mem_pool_size

    1.6 innodb_lock_wait_timeout

    1.7 innodb_support_xa

    1.8 innodb_log_buffer_size // 日志缓冲区大小

    1.9 innodb_log_file_size // innodb_log_files_in_group 关联值，事务日志大小

    1.10 thread_cache_size // 缓存的线程数，根据 Threads_connected 状态设置

    1.11 query_cache_size // 查询缓存

    1.12 read_buffer_size 

    1.13 read_rnd_buffer_size

    1.14 sort_buffer_size

    1.16 myisam_sort_buffer_size

    1.17 innodb_flush_method //innodb 如何跟文件系统交互，可能的值，fdatasync,O_DIRECT, ALL_O_DIRECT, O_DSYNC

    1.18 innodb_data_home_dir

   1.19 innodb_data_file_path //ibdata1:12M;ibdata2:12M;ibdata3:12M(3个文件创建了12*3的表空

填满第一个再到第二个) 或 ibdata1:12M:autoextend:max:2G(设置表空间自动增长,自动扩展的文件最多到2G,一旦扩展就不能回收)   

    1.20 innodb_file_per_table //每张表使用一个文件, 表名.ibd

    1.21 sync_binlog //控制MySQL 怎么刷新二进制日志到磁盘，默认为0.如果大于0，表示二次刷新到磁盘中间间隔了多少次二进制日志写操作

    1.22 delay_key_write // 延迟索引写入(myisam)

    1.23 myisam_recover_options //myisam怎么寻找和修复错误

    1.24 innodb_thread_concurrency // 限制一次性可以有多少个线程进入内核，0不限制,  并发值=cpu数量*磁盘数量*2

    1.25 innodb_thread_sleep_delay 

    1.26 innodb_concurrency_tickets

    1.27 concurrent_insert // myisam 并发插入

    1.28 max_length_for_sort_data 

    1.29 tmp_table_size, max_heap_table_size //这2个设置控制Memory引擎的内存临时表能使用多大的内存。
如果实际内存临时表的大小超过这2个设置的值，就会被转为磁盘MyISAM表,所以它的大小可以继续增长.

应该简单的把这2个值设置为同样的值.

    1.30 max_connections //保证服务器不会被应用激增的连接而不堪重负,会被 "太多的连接" 的错误. 

观察 Max_used_connections 状态， 如果超过 max_connections，则说明至少拒绝过一次。

    1.31 innodb_buffer_pool_instances  //可以把缓冲池分为多段


    1.32 innodb_io_capacity


    1.33 innodb_read_io_threads  // 控制多少后台线程可以被IO操作使用


    1.34 innodb_write_io_threads // 控制多少后台线程可以被IO操作使用


    1.35 innodb_strict_mode  // 把某些条件下的警告变成抛错
 
    1.36 innodb_old_blocks_time


    1.37 innodb_autoinc_lock_mode  // 这个选项控制InnoDB 如何生成自增主键,高并发下自增主键可能是个瓶颈

MySQL 安全配置：
expire_logs_days //让服务器在指定的天数之后清理旧的二进制日志,7~14天

max_allowed_packet //防止服务器发送太大的包, > 16M

max_connect_errors // 客户端尝试连接失败的次数，可以抵御一些恶意的攻击

skip_name_resolve // 禁用 DNS 查找

sql_mode // 接收多种多样的值来改变服务器行为

sysdate_is_now 

read_only // 禁止没有特权的用户在备库做变更

skip_slave_start // 阻止MySQL试图自动启动复制

slave_net_timeout // 这个选项控制备库发现跟主库的连接已经失败并且需要重新连接之前等待的时间

sync_master_info, sync_relay_log, sync_relay_log_info //

  

 https://blog.csdn.net/enlyhua/article/details/79706369

https://blog.csdn.net/enlyhua/article/details/78705056

———————————————————————————————

4.适当的硬件资源和操作系统

    1.1 使用磁盘阵列

    1.2 使用 symbolic link 分布 I/O

    1.3 禁止操作系统更新文件的 atime 属性

    1.4 裸设备(Raw device)存放 InnoDB 的共享表空间

1.多核CPU
2.顺序IO,随机IO
3.多次写入，一次刷新
4.IO合并
5.固态硬盘(SSD),用 SSD做RAID

https://blog.csdn.net/enlyhua/article/details/79720912

———————————————————————————————

5.应用层面优化

    1.1 使用连接池

    1.2 减少对 MySQL 的访问

    1.3 使用查询缓存

    1.4 增加 cache 层

    1.5 负载均衡(主从)

    1.6 采用分布式数据架构  

    1.7 对于没有删除操作的 MyISAM 表，可以采用并行插入

    1.8 尽量采用默认值