全栈，数据库技能不可或缺，关系数据库还是nosql，内存数据库还是偏盘存储数据库，对象存储数据库还是图数据库……有那么多，但是***所需技能也应该是MySQL。从LAMP的兴起，到Mariadb的出现，乃至PG的到来，熟练的MySQL技能都是非常有用的。MySQL数据库技术也有很多方面。这里只涉及必要的性能调优，推荐自下而上的性能调优，主要包括运行环境、配置参数、SQL性能、系统架构设计和调优。运行环境调优这就是Linux的世界。MySQL运行环境的调优往往与Linux内核的调优一起完成。当然也可以作为云服务RDS的参考。调整Linux默认的IO调度算法。IO调度器的总体目标是让磁头一直往一个方向移动，移动到最后再往反方向走。这正是现实生活中的电梯模型，所以IO调度器也称为电梯，对应的算法也称为电梯算法。Linux中IO调度的电梯算法有几种，一种叫做Anticipatory（预期），一种叫做cfq（CompleteFairnessQueuing），还有一种叫做deadline，还有一种叫做noop（NoOperation）。IO对数据库的影响比较大。linux默认的IO调度算法是cfq，需要改成deadline。如果是SSD或者PCIe-SSD设备，需要改成noop。可以使用以下两种修改方法。1.在线动态修改，重启无效。echo"deadline">/sys/block/sda/queue/scheduler2，修改/etc/grub.conf，***生效。修改/etc/grub.conf配置文件，在kernel行添加一个配置，例如：elevator=deadline主要关注电梯参数，如果设置了内核，需要重启系统才能生效。禁用numa功能。新一代架构的NUMA不适合运行数据库。NUMA是为了提高内存利用率，但是可能会导致一个CPU剩余内存，另一个CPU不够用，出现swap的问题。因此，一般建议将其关闭。或者修改NUMA的时间表。1、修改/etc/grub.conf，禁用NUMA，重启后生效。numa=off2，修改/etc/init.d/mysql或mysqld_safe脚本，设置启动mysqld进程时的NUMA调度机制，如numactl–interleave=all。修改swappiness设置swappiness是linux的一个内核参数，用来控制交换物理内存的策略。它允许一个百分比值，最小为0，最高为100，默认值为60。这个设置值到底有什么影响呢？将vm.swappiness设置为0意味着尽可能少地使用swap，而100意味着将不活动的内存页交换为swap或尽可能释放缓存。Inactive内存是指程序被映射了，但是“长时间”没有使用的内存。我们可以使用vmstat查看系统中有多少非活动内存。#vmstat-a1的值建议设置为1，设置方法如下，在/etc/sysctl.conf文件中添加一行。vm.swappiness=1扩展文件描述符这是一个经常修改的参数，高并发程序会修改。1、动态修改，重启无效，只能用root，当前session有效。ulimit-n512002，修改配置文件，使其生效。将*hardnofile51200*softnofile51200添加到/etc/security/limits.conf配置文件。面向会话的进程文件描述符的修改略有不同，在云端的修改也略有不同。可以参考同样的《打开太多文件》优化文件系统挂载参数对于文件系统，如果没有特殊要求，最好使用ext4。文件系统挂载参数在/etc/fstab文件中修改，重启后生效。noatime表示不记录访问时间，nodiratime不记录目录的访问时间。barrier=0，表示关闭屏障功能。屏障的主要目的是保证写入磁盘的数据安全，但是会降低性能。如果有BBU之类的电池后备电源保证控制卡不会瞬间掉电，那么这个功能就可以放心大胆的关闭了。配置参数调优my.cnf中的配置参数调优取决于业务、负载或硬件，在内存慢磁盘快、高并发、写入密集型负载的情况下需要特殊调整。基本配置query_cache_size查询缓存是众所周知的瓶颈，即使在并发不多的情况下也是如此。***是从一开始就禁用它，设置query_cache_size=0，并使用其他方法来加速查询：优化索引，增加副本负载分布，或者启用额外的缓存（例如memcache或redis）。如果您启用了查询缓存并且没有发现任何问题，那么查询缓存可能会有用。如果要禁用它，请小心。innodb_buffer_pool_size缓冲池是数据和索引缓存所在的地方：值越大越好，这样可以确保你的大部分读操作使用内存而不是硬盘。典型值为5-6GB（8GB内存）、20-25GB（32GB内存）、100-120GB（128GB内存）。innodb_log_file_sizeredo日志用于确保快速可靠的写入操作和崩溃恢复。MySQL5.5之后，crashrecovery的性能有了很大的提升，可以同时拥有高写性能和crashrecovery性能。在MySQL5.6中可以增加到4GB以上。如果应用需要频繁写入数据，可以从一开始就转换成4G。将max_connection值设置得很高（例如1000或更高）的max_connections的一个主要缺点是当运行1000个或更高的活动事务时服务器变得无响应。在您的应用程序中使用连接池或在MySQL中使用进程池可以帮助解决这个问题。back_log需要mysql可以拥有的连接数。当主mysql线程在短时间内收到大量连接请求时，这会起作用，然后主线程需要一些时间来检查连接并启动一个新线程。back_log表示在mysql暂时停止响应新请求之前，堆栈中可以存储多少个请求。只有当你期望在短时间内有很多连接时，你才需要增加它，换句话说，这个值是传入tcp/ip连接的监听队列的大小。Innodb配置innodb_file_per_table这个设置告诉InnoDB是将所有表数据和索引存储在一个共享表空间中（innodb_file_per_table=OFF）还是为每个表数据放置一个单独的.ibd文件（innodb_file_per_table=ON）。每个表一个文件允许您在删除、截断或重建表时回收磁盘空间。这对于某些高级功能也是必需的，例如数据压缩。但它不会带来任何性能提升。在MySQL5.6中，该属性的默认值为ON。innodb_flush_log_at_trx_commit的默认值为1，表示InnoDB完全支持ACID特性。当关注数据安全时，例如在主节点上，此值最合适。但是对于磁盘（读写）速度较慢的系统来说，会带来巨大的开销，因为每次将变化刷新到redolog时，都需要额外的fsync。如果值为0，速度更快，但系统崩溃时可能会丢失部分数据，所以onepass只适用于备份节点。innodb_flush_method配置决定了数据和日志如何写入硬盘。一般来说，如果你有一个硬件RAID控制器，它的独立缓存采用回写机制，并且有电池掉电保护，你应该将配置设置为O_DIRECT；否则，在大多数情况下应将其设置为fdatasync（默认值）。sysbench是一个很棒的工具，可以帮助您决定这个选项。innodb_log_buffer_size配置决定了为尚未执行的事务分配的缓存。但如果事务中包含二进制大对象或大文本字段，请查看Innodb_log_waits状态变量，如果不为0，则增加innodb_log_buffer_size。其他配置log_bin如果数据库服务器作为主节点的备份节点，需要开启二进制日志。即使只有一台服务器，如果您想进行时间点数据恢复，这也很有用。二进制日志一旦创建就永久保存。如果不想用完磁盘空间，可以使用PURGEBINARYLOGS清除旧文件，或设置expire_logs_days指定天数后日志将自动清除。记录二进制日志不是免费的，所以如果你在非主副本节点上不需要它，建议关闭这个选项。interactive_timeout服务器在关闭交互式连接之前等待对交互式连接执行操作的秒数。交互式客户端定义为使用mysql_real_connect()的client_interactive选项的客户端。默认值为28800，建议改为7200。table_open_cache每次MySQL打开一个表时，MySQL都会读取一些数据到table_open_cache缓存中。当MySQL在这个缓存中找不到对应的信息时，就会从磁盘中读取。假设系统有200个并发连接，需要将此参数设置为200*N（N为每个连接需要的文件描述符个数）；当table_open_cache设置的很大时，如果系统无法处理如此多的文件描述字符，那么客户端就会失败，无法连接。max_allowed_pa??cket接受的数据包大小；增加这个变量的值是安全的，因为额外的内存只在需要时分配。例如，只有当您发出长查询或MySQLd必须返回大结果行时，MySQLd才会分配更多内存。之所以这个变量取小的默认值，是为了预防客户端和服务器之间捕获错误包，保证客户端连接数据库服务器时不会因为误用大包skip_name_resolve而导致内存溢出,当DNS很慢时，建立连接也很慢。因此，建议在没有DNS查找的情况下启动服务器时关闭skip_name_resolve选项。SQL语句调优在应用层，通过pt工具和慢查询日志的配合，可以轻松识别出全表扫描的语句。基本原则避免全表扫描和建立索引尽量避免向客户端返回大量数据。如果数据量过大，应该考虑相应的要求是否合理在使用基于游标的方法或临时表方法之前，尽量避免大事务操作和提高系统并发性，应该先寻找基于集合的方案来解决的问题，这通常更有效率。尽量避免使用游标，因为它们效率较低。小技巧关于where后的条件尽量避免在where子句中使用!=或<>运算符，否则引擎将放弃使用索引并进行全表扫描。尽量避免在where子句中使用or来连接条件。可以考虑用union代替in和notin，谨慎使用。对于连续值，能用between就不要用in，用exists代替in。尽量避免在where子句中对字段中的字段进行表达式操作和函数操作。至于数据类型，尽量使用数字字段。如果字段只包含数值信息，尽量不要设计成字符类型，这样会降低查询和连接的性能，增加存储开销。尽量使用varchar/nvarchar而不是char/nchar，因为变长字段的存储空间小，对于查询来说，在比较小的字段中查找效率明显更高。***数据库不要留NULL，数据库尽量填NOTNULL。备注、描述、评论等可以设置为NULL，其他的，***不要使用NULL。不要在任何地方使用select*fromt，将“*”替换为特定的字段列表，并且不要返回任何您不使用的字段。关于临时表避免频繁创建和删除临时表，以减少系统表资源的消耗。对于一次性事件，***使用导出表。创建临时表时，如果一次插入的数据量较大，可以使用selectinto代替createtable，避免创建大量日志，提高速度；如果数据量不大，为了缓解系统表的资源，应该先建表，再插入。如果使用临时表，显式删除所有临时表时，先truncatetable，再droptable，避免系统表长期锁定。关于索引，首先要考虑对where和orderby涉及的列建立索引。使用索引字段作为条件时，如果索引是复合索引，系统必须以索引中的第一个字段作为条件，保证索引被使用，否则索引不会被使用，应该被使用尽可能使字段顺序与索引顺序相匹配。索引越多越好，索引可以提高相应select的效率，但是也会降低insert和update的效率，因为insert或者update的时候可能会重建索引，所以要看具体情况。一个表中的索引数不要超过7个，如果过多，就要考虑是否需要在一些不常用的列上建索引。数据库架构调优从底层到应用层，最后到架构层。但是，没有业务逻辑就谈架构，就是耍流氓。数据库架构也依赖于业务系统，稳定灵活地为业务系统服务是关键。架构调优方向为：分区分表业务分库主从同步读写分离数据缓存主从热备HA双活...专栏作家老曹，作者微信♂：哦家ArchiSelf，id：wrieless-com]
