mysql常见底层优化操作教程及相关建议
1、硬件层相关优化1.1、CPU相关
在服务器的BIOS设置，下面的配置可以调整执行CPU最大性能或避免经典NUMA的问题：
（1）选择的每瓦性能优化（DAPC）模式以最大限度地提高CPU的性能，和运行数据库，通常需要高流量不考虑节能。
（2）关闭C1E选项和C国的设计也提高了CPU的效率；
（3）内存频率（内存频率）选择最大性能（最佳性能）；
（4）、在内存设置菜单，节点交叉能够避免NUMA的问题。
1.2、磁盘I/O相关
以下是一些可以为磁盘I / O根据IOPS性能提高的幅度优化措施：
（1）使用SSD或PCIe SSD设备至少获得成百上千甚至上万的IOPS提升千倍；
（2）阵列卡采集同时配有高速缓存和BBU模块，可明显提高IOPS（主要指机械硬盘、SSD、PCIe SSD）。同时，要检查卫生的缓存和BBU模块定期以确保数据不失事故是意外。
（3）当阵列卡被安装时，阵列写入策略是WB甚至强制WB（如果有双重电保护，或者数据安全要求不高），严格禁止WT策略，并且关闭数组预取策略，基本上是无用的鸡肋；
（4）选择RAID-10尽可能不是RAID-5；
（5）、机械磁盘的使用，尽可能选择高速度，如15krpm的选择，而不是7.2krpm盘，不少于几钱；
2，系统层相关优化
2.1、文件系统层优化
在文件系统层，以下措施可以显著提高IOPS性能：
（1），使用两种我 / / NOOP调度器的最后期限，不使用CFQ（不适合运行数据库服务）；
（2）、使用XFS文件系统，不使用ext3；ext4勉强可用，但业务量是非常大的，那么我们必须使用XFS；
（3），增加了文件系统挂载参数：noatime，nodiratime，nobarrier几个选项（nobarrier是XFS文件系统特有的）；
2.2、其他核心参数的优化
为关键的内核参数设置适当的值，目的是减少交换的倾向，让内存和磁盘I/O不发生很大的波动，从而产生瞬时峰值负载。
（1）设置vm.swappiness约5-10，甚至将它设置为0（RHEL 7设置为0谨慎，除非你允许OOM杀发生），从而减少机会使用互换。
（2）设置vm.dirty_background_ratio 5-10，集vm.dirty_ratio两倍，从而确保脏数据可以连续刷新到磁盘，避免瞬间我 / O写作和产生严重的等待（类似innodb_max_dirty_pages_pct MySQL）。
（3）、net.ipv4.tcp_tw_recycle，net.ipv4.tcp_tw_reuse都设置为1，减少time_wait，提高TCP的效率；
（4）、read_ahead_kb，nr_requests作为这两个参数，网络传输我测试，OLTP主要是对阅读和写作环境的影响，发现不混合（应该是看现场，更敏感，但没有效果）是我的测试方法有问题，可以考虑是否调整；
3与MySQL层相关优化
3.1。论译本的选择
官方版本叫做Oracle MySQL，这没什么可说的，我相信大多数人会选择它。
就我个人而言，我强烈建议Percona的分支版本选择。它是一个相对成熟和优秀的MySQL分支版本，在性能改进、可靠性和管理方面做了许多改进，基本上与官方版本的MySQL版本兼容，性能提高超过20%。因此，我首先推荐它，从2008以来我一直在关注它。
另一个重要分支的版本是MariaDB，这是不恰当的说，MariaDB是一个分支版本，因为它的目标是取代Oracle的MySQL，它做了很多源级改进原来的MySQL服务器层，也是一个非常可靠的，优秀的分支版本。但是，这是不与官方版本兼容的新功能是由gtid。（MySQL 5.7还支持gtid模式在线动态打开或关闭）。考虑到大多数人会按照官方的说法，MariaDB不推荐。
3.2。关于最重要参数调整的建议
建议对以下关键参数进行调整，以获得更好的性能：
（1）如果你选择Percona或MariaDB的版本，它是强烈建议线程池的功能被启用，使性能不高并发条件下明显降低。此外，还有一个extra_port函数是非常有用的，可以在关键时刻救了。另一个重要特点是query_response_time功能，它也能给我们一个直观的整体SQL响应时间分布的意义。
（2）设置默认存储引擎= InnoDB，这就是说，InnoDB引擎采用默认。这是强烈建议不要使用MyISAM引擎了。InnoDB引擎完全可以满足99%以上的业务场景。
（3）调整innodb_buffer_pool_size大小。如果它是一个单一的实例大部分是InnoDB引擎的表，它可以被认为是50%到70%的物理内存。
（4）根据实际需要设置innodb_flush_log_at_trx_commit和sync_binlog值。如果数据不能丢，然后两个设置为1。如果一个比特的数据不得丢失，可以设置为2和10。如果保健数据不完全丧失（例如，在奴隶，不能重做一次），它可以被设置为0。三设置导致数据库的性能受影响，高、中、低，即第一个将慢的数据库，而最后一个是相反的。
（5）、设置innodb_file_per_table = 1，使用独立的表空间，我真的不想使用共享表空间的好处。
（6）设置innodb_data_file_path = ibdata1:1g：自动，不要使用默认的10M，否则，当你有高并发交易，你会得到很多的影响。
（7）建立innodb_log_file_size = M = 2 innodb_log_files_in_group设置基本能满足以上90%个场景。
（8）、设置long_query_time = 1，而在5.5版本中，它被设置为小于1。推荐的设置是0.05（50毫秒），并且记录那些较慢的SQL进行后续的分析和调查。
（9），根据实际需要，调整max_connection（最大连接数），max_connection_error（错误，是10万以上，最大数量的同时，open_files_limit，innodb_open_files，table_open_cache，table_definition_cache的参数可以设置大小的max_connection约10倍；
（10），一个常见的误解是，tmp_table_size和max_heap_table_size比较大，一旦看到设置为1G，2种选择是每个连接会话将被分配的，所以不要定得太高，否则容易导致OOM的发生；其他一些会话级别的选项如：sort_buffer_size，join_buffer_size，read_buffer_size，read_rnd_buffer_size，还需要注意不要设置太大；
（11）由于建议MyISAM引擎不再使用，key_buffer_size可以设置32m左右，查询缓存功能，强烈推荐。
3.3。关于模式设计规范和SQL使用的建议
下面是几种常见的模式设计规范和SQL建议，它们有助于提高MySQL效率。
（1）、所有InnoDB表设计一个自增列没有商业用途作为主键，这对于大多数的场景是真的。不是有很多的InnoDB表用于真正的纯只读，这不是TokuDB一样好。
（2）当字段长度满足要求时，长度越小越好，并且尽可能地在字段属性中添加非空约束，并在一定程度上提高了性能。
（3）尽可能不要使用文本类型。如果真有必要，建议拆分子表，不要把它与主表放在一起，在选择*时避免性能差。
（4）读取数据时，只选择所需的列，不要总是选择*，避免严重的随机读问题，尤其是阅读一些文本/列。
（5）创建一个VARCHAR（n）列的索引时，通常需要50%（甚至更小）长度为前缀索引，这足以满足80%以上的需求查询。不需要创建整列的完整长度索引。
（6）在一般情况下，在子查询的性能差，并建议将它转变成加入。
（7）当多个表连接查询时，相关字段的类型尽可能一致，必须进行索引。
（8）当多表连接查询时，选择结果集的小表作为驱动表。
（9）在连接和排序多个表时，必须驱动排序字段，否则无法对序列进行索引。
（10）使用复合索引和使用多个独立索引，特别是那些基数太小（基数）（例如，列的总值小于255）的索引，不要创建独立索引。
（11）提出了与分页功能相似的SQL，它首先与主关键字相关联，然后返回结果集，效率将大大提高。
3.4。其他的建议
MySQL的管理和维护的其他建议是：
（1），通常，一个表的物理尺寸小于10GB，单表为数不超过1亿，不超过8kb平均长度，如果机器的性能是不够的，数据量可以通过MySQL的处理是完全的，不必担心性能问题，这样是考虑在线DDL的高成本；
（2），不要过于担心mysqld进程占用太多的内存，只要不发生OOM杀死并用大量的互换；
（3）过去，在一台机器上运行多个实例的目的是最大限度地利用计算资源。如果单个实例用尽了大多数计算资源，就不需要运行更多实例了。
（4）定期使用PT重复键检查器检查和删除重复的索引，定期使用PT索引使用工具检查和删除一个非常低的频率索引。
（5）定期收集慢速查询日志，并使用PT查询摘要工具进行分析。慢速查询管理可以与风速计系统相结合，分析慢查询并进行后续优化。
（6）、PT杀可以用来杀死SQL请求在很长一段时间，有一个选项innodb_kill_idle_transaction在Percona的版本，也可以实现。
（7）铂在线模式的变化来完成大表的在线DDL的使用要求；
（8）定期使用PT表校验和PT表同步检查和修复MySQL主从复制的数据差异；