配置参数优化最佳实践
###########################################################################
Cache/Buffer######################的设置
query_cache：缓存结果集，极高效，与SQL语句一一对应
binlog_cache_size：缓存binlog数据，影响所有写入操作的性能
table_cache：缓存打开的表信息，MyISAM会占用较多，表多的需注意
thread_cache：缓存连接线程，影响连接建立效率，对短连接影响较大
key_buffer_size：缓存MyISAM索引，对MyISAM表性能影响极大
innodb_db_buffer_pool_size：对InnoDB极大影响，缓存索引及数据
innodb_log_buff_size：缓存InnoDB写入日志，影响写入效率
innodb_max_dirty_pages_pct：设置InnoDB Buffer中脏页占比
#######################最佳实践&建议设置#######################
query_cache：不超过256MB，除非基本静态，InnoDB无效
binlog_cache_size：2MB~4MB，< 32MB
table_cache：1024，具体需要根据实际环境调整
thread_cache：1024，< max_connectios
key_buffer_size：无MyISAM 16MB，否则所有MYI大小之内尽可能大
innodb_db_buffer_pool_size：仅InnoDB，所有文件大小之内尽可能大
innodb_log_buff_size：4MB~8MB，< 32MB
innodb_max_dirty_pages_pct：<1G/innodb_db_buffer_pool_size(G)*100
############################################################################
Connction######################的设置
max_connections：影响能够保持的最大客户端连接数，属于自我保护类
max_connect_errors：某个用户允许最大登录失败次数，类似于防破解
back_log：影响突发连接暴增场景，比如服务器重启后瞬间
skip-name-resolve：取消对客户端的 DNS 反解，影响连接和授权
interactive_timeout和wait_timeout：影响空闲连接最大可空闲时间
#######################最佳实践&建议设置#######################
max_connections：1000~2000，< 10000
max_connect_errors：>1000，尽量大一点吧
back_log：100，< OS网络层设置
skip-name-resolve：建议启用，确保授权都是用IP
interactive_timeout和wait_timeout：86400，24小时基本足矣
############################################################################
IO######################的设置
innodb_flush_method：innodb文件打开方式，linux下文件系统影响较大
innodb_flush_log_at_trx_commit：影响innodb日志事务刷新机制
innodb_file_per_table：影响表存储方式，文件过大会影响性能
sync_binlog：影响binlog日志刷新到磁盘的机制
#######################最佳实践&建议设置#######################
innodb_flush_method：O_DIRECT(Linux)
innodb_flush_log_at_trx_commit：2，特别重要的设置为1，不建议0
innodb_file_per_table：一般建议开启
sync_binlog：4~8，非常频繁的系统可适当增大，但不建议0
############################################################################
MyISAM跟InnoDB的对比跟使用推荐
############################################################################
Myisam
尽量索引，MyISAM只缓存索引不缓存数据
调整读写优先级，根据实际需求，调整读写优先级
延迟插入，使用 insert delay，减少和 select 竞争
数据顺序操作，让insert全部到尾部，减少和select竞争
分解大操作，将大操作分解成多步小操作，防止长时间锁定
降低并发数，表锁会导致竞争激烈，通过排队机制提高效率
充分利用 Query Cache：对于静态数据，尽量使用 Query Cache
不需要事务支持
并发相对较低
数据修改相对较少
以读为主
数据一致性要求较低
###############################
InnoDB
主键尽可能小：所有非主键索引都需要存储主键
索引整合，减少冗余索引，降低数据量
避免全表扫描，因为会导致表锁
尽量自己控制事务，关闭 aotucommit
尽量缓存所有数据和索引
合理设置 innodb_flush_log_at_trx_commit
充分利用索引避开表锁
避免主键更新
需要事务支持
并发较大
数据变更比较频繁
数据一致性要求较高
硬件设备内存较大，远大于索引数据量
############################################################################
语句优化原则
减少表连接，减少复杂 SQL，拆分成简单SQL
减少排序：非必要不排序，利用索引排序，减少参与排序的记录数
尽量避免 select *
尽量用 join 代替子查询
尽量少使用 or，使用 in 或者 union(union all) 代替
尽量用 union all 代替 union
尽量早的将无用数据过滤：选择更优的索引，先分页再Join…
避免类型转换：索引失效
#####################################################################
MyISAM
特性
不支持事务：MyISAM存储引擎不支持事务，所以对事务有要求的业务场景不能使用
表级锁定：其锁定机制是表级索引，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能
读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读
只会缓存索引：MyISAM可以通过key_buffer缓存以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据
适用场景
不需要事务支持（不支持）
并发相对较低（锁定机制问题）
数据修改相对较少（阻塞问题）
以读为主
数据一致性要求不是非常高
最佳实践
尽量索引（缓存机制）
调整读写优先级，根据实际需求确保重要操作更优先
启用延迟插入改善大批量写入性能
尽量顺序操作让insert数据都写入到尾部，减少阻塞
分解大的操作，降低单个操作的阻塞时间
降低并发数，某些高并发场景通过应用来进行排队机制
对于相对静态的数据，充分利用Query Cache可以极大的提高访问效率
MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的数据访问
InnoDB
特性
具有较好的事务支持：支持4个事务隔离级别，支持多版本读
行级锁定：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响
读写阻塞与事务隔离级别相关
具有非常高效的缓存特性：能缓存索引，也能缓存数据
整个表和主键以Cluster方式存储，组成一颗平衡树
所有Secondary Index都会保存主键信息
适用场景
需要事务支持（具有较好的事务特性）
行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成
数据更新较为频繁的场景
数据一致性要求较高
硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘 IO
最佳实践
主键尽可能小，避免给Secondary index带来过大的空间负担
避免全表扫描，因为会使用表锁
尽可能缓存所有的索引和数据，提高响应速度
在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交
合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性
避免主键更新，因为这会带来大量的数据移动
NDBCluster
特性
分布式：分布式存储引擎，可以由多个NDBCluster存储引擎组成集群分别存放整体数据的一部分
支持事务：和Innodb一样，支持事务
可与mysqld不在一台主机：可以和mysqld分开存在于独立的主机上，然后通过网络和mysqld通信交互
内存需求量巨大：新版本索引以及被索引的数据必须存放在内存中，老版本所有数据和索引必须存在与内存中
适用场景
具有非常高的并发需求
对单个请求的响应并不是非常的critical
查询简单，过滤条件较为固定，每次请求数据量较少，又不希望自己进行水平Sharding
最佳实践
尽可能让查询简单，避免数据的跨节点传输
尽可能满足SQL节点的计算性能，大一点的集群SQL节点会明显多余Data节点
在各节点之间尽可能使用万兆网络环境互联，以减少数据在网络层传输过程中的延时
优先优化高并发的 SQL，而不是执行频率低某些“大”SQL
从全局出发优化，而不是片面调整
尽可能对每一条SQL进行 explain