MySQL性能调优 – 你必须了解的15个重要变量

1.DEFAULT_STORAGE_ENGINE

如果你已经在用MySQL 5.6或者5.7，并且你的数据表都是InnoDB，那么表示你已经设置好了。如果没有，确保把你的表转换为InnoDB并且设置default_storage_engine为InnoDB。
为什么？简而言之，因为InnoDB是MySQL(包括Percona Server和MariaDB)最好的存储引擎 – 它支持事务，高并发，有着非常好的性能表现(当配置正确时)。这里有详细的版本介绍为什么

2.INNODB_BUFFER_POOL_SIZE

这个是InnoDB最重要变量。实际上，如果你的主要存储引擎是InnoDB，那么对于你，这个变量对于MySQL是最重要的。
基本上，innodb_buffer_pool_size指定了MySQL应该分配给InnoDB缓冲池多少内存，InnoDB缓冲池用来存储缓存的数据，二级索引，脏数据(已经被更改但没有刷新到硬盘的数据)以及各种内部结构如自适应哈希索引。
根据经验，在一个独立的MySQL服务器应该分配给MySQL整个机器总内存的80%。如果你的MySQL运行在一个共享服务器，或者你想知道InnoDB缓冲池大小是否正确设置，详细请看这里。

3.INNODB_LOG_FILE_SIZE

InnoDB重做日志文件的设置在MySQL社区也叫做事务日志。直到MySQL 5.6.8事务日志默认值innodb_log_file_size=5M是唯一最大的InnoDB性能杀手。从MySQL 5.6.8开始，默认值提升到48M,但对于许多稍繁忙的系统，还远远要低。
根据经验，你应该设置的日志大小能在你服务器繁忙时能存储1-2小时的写入量。如果不想这么麻烦，那么设置1-2G的大小会让你的性能有一个不错的表现。这个变量也相当重要，更详细的介绍请看这里。
在进入下一个变量之前，让我们来快速提及一下innodb_log_buffer_size。“快速提及”是因为它常常不好理解并且往往被过度关注了。事实上大多数情况下你只需要使用小的缓冲 – 在事务被提交并写入到硬盘前足够保存你的小事务更改了。
当然，如果你有大量的大事务更改，那么，更改比默认innodb日志缓冲大小更大的值会对你的性能有一定的提高，但是你使用的是autocommit，或者你的事务更改小于几k，那还是保持默认的值吧。

4.INNODB_FLUSH_LOG_AT_TRX_COMMIT

默认下，innodb_flush_log_at_trx_commit设置为1表示InnoDB在每次事务提交后立即刷新同步数据到硬盘。如果你使用autocommit，那么你的每一个INSERT, UPDATE或DELETE语句都是一个事务提交。
同步是一个昂贵的操作(特别是当你没有写回缓存时)，因为它涉及对硬盘的实际同步物理写入。所以如果可能，并不建议使用默认值。
两个可选的值是0和2:
* 0表示刷新到硬盘，但不同步(提交事务时没有实际的IO操作)
* 2表示不刷新和不同步(也没有实际的IO操作)
所以你如果设置它为0或2，则同步操作每秒执行一次。所以明显的缺点是你可能会丢失上一秒的提交数据。具体来说，你的事务已经提交了，但服务器马上断电了，那么你的提交相当于没有发生过。
显示的，对于金融机构，如银行，这是无法忍受的。不过对于大多数网站，可以设置为innodb_flush_log_at_trx_commit=0|2，即使服务器最终崩溃也没有什么大问题。毕竟，仅仅在几年前有许多网站还是用MyISAM，当崩溃时会丢失30s的数据(更不要提那令人抓狂的慢修复进程)。
那么，0和2之间的实际区别是什么？性能明显的差异是可以忽略不计，因为刷新到操作系统缓存的操作是非常快的。所以很明显应该设置为0，万一MySQL崩溃(不是整个机器)，你不会丢失任何数据，因为数据已经在OS缓存，最终还是会同步到硬盘的。

5.SYNC_BINLOG

已经有大量的文档写到sync_binlog，以及它和innodb_flush_log_at_trx_commit的关系，下面我们来简单的介绍下：
a) 如果你的服务器没有设置从服务器，而且你不做备份，那么设置sync_binlog=0将对性能有好处。
b) 如果你有从服务器并且做备份，但你不介意当主服务器崩溃时在二进制日志丢失一些事件，那么为了更好的性能还是设置为sync_binlog=0.
c) 如果你有从服务器并且备份，你非常在意从服务器的一致性，以及能及时恢复到一个时间点(通过使用最新的一致性备份和二进制日志将数据库恢复到特定时间点的能力)，那么你应该设置innodb_flush_log_at_trx_commit=1，并且需要认真考虑使用sync_binlog=1。
问题是sync_binlog=1代价比较高 – 现在每个事务也要同步一次到硬盘。你可能会想为什么不把两次同步合并成一次，想法正确 – 新版本的MySQL(5.6和5.7，MariaDB和Percona Server)已经能合并提交，那么在这种情况下sync_binlog=1的操作也不是这么昂贵了，但在旧的mysql版本中仍然会对性能有很大影响。

6.INNODB_FLUSH_METHOD

将innodb_flush_method设置为O_DIRECT以避免双重缓冲.唯一一种情况你不应该使用O_DIRECT是当你操作系统不支持时。但如果你运行的是Linux，使用O_DIRECT来激活直接IO。
不用直接IO，双重缓冲将会发生，因为所有的数据库更改首先会写入到OS缓存然后才同步到硬盘 – 所以InnoDB缓冲池和OS缓存会同时持有一份相同的数据。特别是如果你的缓冲池限制为总内存的50%，那意味着在写密集的环境中你可能会浪费高达50%的内存。如果没有限制为50%，服务器可能由于OS缓存的高压力会使用到swap。
简单地说，设置为innodb_flush_method=O_DIRECT。

7.INNODB_BUFFER_POOL_INSTANCES

MySQL 5.5引入了缓冲实例作为减小内部锁争用来提高MySQL吞吐量的手段。
在5.5版本这个对提升吞吐量帮助很小，然后在MySQL 5.6版本这个提升就非常大了，所以在MySQL5.5中你可能会保守地设置innodb_buffer_pool_instances=4，在MySQL 5.6和5.7中你可以设置为8-16个缓冲池实例。
你设置后观察会觉得性能提高不大，但在大多数高负载情况下，它应该会有不错的表现。
对了，不要指望这个设置能减少你单个查询的响应时间。这个是在高并发负载的服务器上才看得出区别。比如多个线程同时做许多事情。

8.INNODB_THREAD_CONCURRENCY

你可能会经常听到应该设置innodb_thread_concurrency=0然后就不要管它了。不过这个只在低负载服务器使用时才正确。然后，如果你的服务器的CPU或者IO使用接受饱和，特别是偶尔出现峰值，这时候系统想在超载时能正常处理查询，那么强烈建议关注innodb_thread_concurrency。
InnoDB有一种方法来控制并行执行的线程数 – 我们称为并发控制机制。大部分是由innodb_thread_concurrency值来控制的。如果设置为0，并发控制就关闭了，因此InnoDB会立即处理所有进来的请求(尽可能多的)。
在你有32CPU核心且只有4个请求时会没什么问题。不过想像下你只有4CPU核心和32个请求时 – 如果你让32个请求同时处理，你这个自找麻烦。因为这些32个请求只有4 CPU核心，显然地会比平常慢至少8倍(实际上是大于8倍)，而然这些请求每个都有自己的外部和内部锁，这有很大可能堆积请求。
下面介绍如何更改这个变量，在mysql命令行提示符执行：

  1. SET global innodb_thread_concurrency=X;

对于大多数工作负载和服务器，设置为8是一个好开端，然后你可以根据服务器达到了这个限制而资源使用率利用不足时逐渐增加。可以通过show engine innodb statusG来查看目前查询处理情况，查找类似如下行：

  1. 22 queries inside InnoDB, 104 queries in queue

9.SKIP_NAME_RESOLVE

这一项不得不提及，因为仍然有很多人没有添加这一项。你应该添加skip_name_resolve来避免连接时DNS解析。
大多数情况下你更改这个会没有什么感觉，因为大多数情况下DNS服务器解析会非常快。不过当DNS服务器失败时，它会出现在你服务器上出现“unauthenticated connections” ，而就是为什么所有的请求都突然开始慢下来了。
所以不要等到这种事情发生才更改。现在添加这个变量并且避免基于主机名的授权。

10.INNODB_IO_CAPACITY, INNODB_IO_CAPACITY_MAX

* innodb_io_capacity：用来当刷新脏数据时，控制MySQL每秒执行的写IO量。
* innodb_io_capacity_max: 在压力下，控制当刷新脏数据时MySQL每秒执行的写IO量
首先，这与读取无关 – SELECT查询执行的操作。对于读操作，MySQL会尽最大可能处理并返回结果。至于写操作，MySQL在后台会循环刷新，在每一个循环会检查有多少数据需要刷新，并且不会用超过innodb_io_capacity指定的数来做刷新操作。这也包括更改缓冲区合并（在它们刷新到磁盘之前，更改缓冲区是辅助脏页存储的关键）。
第二，我需要解释一下什么叫“在压力下”，MySQL中称为”紧急情况”，是当MySQL在后台刷新时，它需要刷新一些数据为了让新的写操作进来。然后，MySQL会用到innodb_io_capacity_max。
那么，应该设置innodb_io_capacity和innodb_io_capacity_max为什么呢？
最好的方法是测量你的存储设置的随机写吞吐量，然后给innodb_io_capacity_max设置为你的设备能达到的最大IOPS。innodb_io_capacity就设置为它的50-75%，特别是你的系统主要是写操作时。
通常你可以预测你的系统的IOPS是多少。例如由8 15k硬盘组成的RAID10能做大约每秒1000随机写操作，所以你可以设置innodb_io_capacity=600和innodb_io_capacity_max=1000。许多廉价企业SSD可以做4,000-10,000 IOPS等。
这个值设置得不完美问题不大。但是，要注意默认的200和400会限制你的写吞吐量，因此你可能偶尔会捕捉到刷新进程。如果出现这种情况，可能是已经达到你硬盘的写IO吞吐量，或者这个值设置得太小限制了吞吐量。

11.INNODB_STATS_ON_METADATA

如果你跑的是MySQL 5.6或5.7，你不需要更改innodb_stats_on_metadata的默认值，因为它已经设置正确了。
不过在MySQL 5.5或5.1，强烈建议关闭这个变量 – 如果是开启，像命令show table status会立即查询INFORMATION_SCHEMA而不是等几秒再执行，这会使用到额外的IO操作。
从5.1.32版本开始，这个是动态变量，意味着你不需要重启MySQL服务器来关闭它。

12.INNODB_BUFFER_POOL_DUMP_AT_SHUTDOWN & INNODB_BUFFER_POOL_LOAD_AT_STARTUP

innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup这两个变量与性能无关，不过如果你偶尔重启mysql服务器(如生效配置)，那么就有关。当两个都激活时，MySQL缓冲池的内容(更具体地说，是缓存页)在停止MySQL时存储到一个文件。当你下次启动MySQL时，它会在后台启动一个线程来加载缓冲池的内容以提高预热速度到3-5倍。
两件事：
第一，它实际上没有在关闭时复制缓冲池内容到文件，仅仅是复制表空间ID和页面ID – 足够的信息来定位硬盘上的页面了。然后它就能以大量的顺序读非常快速的加载那些页面，而不是需要成千上万的小随机读。
第二，启动时是在后台加载内容，因为MySQL不需要等到缓冲池内容加载完成再开始接受请求(所以看起来不会有什么影响)。
从MySQL 5.7.7开始，默认只有25%的缓冲池页面在mysql关闭时存储到文件，但是你可以控制这个值 – 使用innodb_buffer_pool_dump_pct，建议75-100。
这个特性从MySQL 5.6才开始支持。

13.INNODB_ADAPTIVE_HASH_INDEX_PARTS

如果你运行着一个大量SELECT查询的MySQL服务器(并且已经尽可能优化)，那么自适应哈希索引将下你的下一个瓶颈。自适应哈希索引是InnoDB内部维护的动态索引，可以提高最常用的查询模式的性能。这个特性可以重启服务器关闭，不过默认下在mysql的所有版本开启。
这个技术非常复杂，在大多数情况下它会对大多数类型的查询直到加速的作用。不过，当你有太多的查询往数据库，在某一个点上它会花过多的时间等待AHI锁和闩锁。
如果你的是MySQL 5.7，没有这个问题 – innodb_adaptive_hash_index_parts默认设置为8，所以自适应哈希索引被切割为8个分区，因为不存在全局互斥。
不过在mysql 5.7前的版本，没有AHI分区数量的控制。换句话说，有一个全局互斥锁来保护AHI，可能导致你的select查询经常撞墙。
所以如果你运行的是5.1或5.6，并且有大量的select查询，最简单的方案就是切换成同一版本的Percona Server来激活AHI分区。

14.QUERY_CACHE_TYPE

如果人认为查询缓存效果很好，肯定应该使用它。好吧，有时候是有用的。不过这个只在你在低负载时有用，特别是在低负载下大多数是读取，小量写或者没有。
如果是那样的情况，设置query_cache_type=ON和query_cache_size=256M就好了。不过记住不能把256M设置更高的值了，否则会由于查询缓存失效时，导致引起严重的服务器停顿。
如果你的MySQL服务器高负载动作，建议设置query_cache_size=0和query_cache_type=OFF，并重启服务器生效。那样Mysql就会停止在所有的查询使用查询缓存互斥锁。

15.TABLE_OPEN_CACHE_INSTANCES

从MySQL 5.6.6开始，表缓存能分割到多个分区。
表缓存用来存放目前已打开表的列表，当每一个表打开或关闭互斥体就被锁定 – 即使这是一个隐式临时表。使用多个分区绝对减少了潜在的争用。
从MySQL 5.7.8开始，table_open_cache_instances=16是默认的配置。

INNODB_LOG_FILE_SIZE:设置MySQL重做日志大小

什么是InnoDB事务日志

你有没有在文本编辑器中使用过撤消或重做的功能，想像一下编辑器在那种场景下的操作？我确信你应该使用过。你相信吗？事务型数据库有同样的功能。可能不完全一样，但原理是相同的。
就像当你编辑文字时始终有能力撤消数步的重要性一样，重做和撤消功能也对事务型数据一样重要。为什么呢？主要有两个原因：
1.回滚事务(那是撤消)
2.在数据库崩溃的情况下回放已提交的事务(那是重做)

撤消

当你使用的是事务存储引擎(假设是InnoDB)，你更改一个记录时，更改并没有马上写入数据文件。
首先，它们被写入到一个硬盘上特定的文件叫做事务日志。同时，它们也更改了内存 – InnoDB缓冲池。现在新InnoDB页面包含了已更改的记录叫脏数据。
被复制到硬盘上特别区域的原始未被更改的页面叫做回滚段。
如果有人在提交之前使用ROLLBACK中断了一个事务，撤消操作就发生了 – 你的记录已经被还原到原始状态。
由于更改还没有被写入到数据文件，这个操作相当简单 – InnoDB仅仅需要从回滚段中提取旧页面，从内存中擦除脏页，并在事务日志中标记那个事务已经回滚。
所以你看，数据文件从没有被更改，因为在执行随机写操作以把脏数据刷新到硬盘之前你已经把所有更改取消了。

重做

当你提交事务，然后InnoDB确认你的提交，更改准备写入到实际的数据文件。
现在你认为它们会被马上写入到硬盘的数据文件，事实上不是这样的。为什么？因为这样做效率非常低。反而，更改仅仅被写入到事务日志(因为是顺序写，速度会很快，称为重做日志记录)，而更改的记录仍然在日志中 – InnoDB缓冲池的脏页，过一定的时间才刷新到硬盘。
这时候MySQL崩溃了!
猜猜MySQL会怎样做?
如果MySQL(实际上是InnoDB)没有重做日志，仅仅是保留了脏页在内存中 – 所有未被刷新到硬盘已提交的事务将会永久丢失。
幸运的是，所以的更改总会写入到事务日志，
所以InnoDB需要做的就是在重做日志中找到上一次的checkpoint(已同步数据到硬盘的位置)，然后重做未同步到硬盘已提交的事务。

日志大小

你可能想知道的一个事就是如何正确设置innodb_log_file_size的大小。规则很简单：
* 小日志文件使写入速度更慢，崩溃恢复速度更快
* 大日志文件使写入更快，崩溃恢复速度更慢
由于事务日志相当于一个写缓冲，而小日志文件会很快的被写满，这时候就需要频繁地刷新到硬盘，速度就慢了。如果产生大量的写操作，MySQL可能就不能足够快地刷新数据，那么写性能将会降低。
大的日志文件，另一方面，在刷新操作发生之前给你足够的空间来使用。反过来允许InnoDB填充更多的页面。
对于崩溃恢复 – 大的重做日志意味着在服务器启动前更多的数据需要读取，更多的更改需要重做，这就是为什么崩溃恢复慢了。

重做日志大小

最后，让我们来谈谈如何找出重做日志的正确大小。
幸运的是，你不需要费力算出正确的大小，这里有一个经验法则：
在服务器繁忙期间，检查重做日志的总大小是否够写入1-2小时。
你如何知道InnoDB写入多少，下面有一个方法：

  1. mysql> pager grep seq
  2. mysql> show engine innodb statusG select sleep(60); show engine innodb statusG
  3. Log sequence number 1777308180429
  4. …
  5. Log sequence number 1777354541591
  6.  
  7. mysql> nopager
  8. mysql> select (1777354541591-1777308180429)*60/1024/1024;
  9. +——————————————–+
  10. | (1777354541591-1777308180429)*60/1024/1024 |
  11. +——————————————–+
  12. |                              2652.80696869 |
  13. +——————————————–+
  14. 1 row in set (0.00 sec)

在这个60s的采样情况下，InnoDB每小时写入2.6GB数据。所以如果innodb_log_files_in_group没有更改(默认是2，是InnoDB重复日志的最小数字)，然后设置innodb_log_file_size为2560M，那么你实际上两个日志文件加起来有5GB，够你写两小时数据了。

更改重做日志大小

更改innodb_log_file_size的难易程度和能设置多大取决于你现在使用的MySQL版本。
特别地，如果你使用的是5.6之前的版本，你不能仅仅的更改变量，期望服务器会自动重启。
好了，下面是步骤：
1.在my.cnf更改innodb_log_file_size
2.停止mysql服务器
3.删除旧的日志,通过执行命令rm -f /var/lib/mysql/ib_logfile*
4.启动mysql服务器 – 应该需要比之前长点的时间，因为需要创建新的事务日志。
最后，需要注意的是，有些mysql版本(比如5.6.2)限制了重做日志大小为4GB。所以在你设置innodb_log_file_size为2G或者更多时，请先检查一下MySQL的版本这方面的限制。

INNODB_BUFFER_POOL_SIZE:设置最佳内存值

什么是INNODB BUFFER POOL

计算机使用它们的大部分内存来提升对经常访问的数据的性能。这就是我们所知的缓存,是系统的一个非常重要的组成部分，因为访问硬盘的数据可能会慢到100到100000倍，这取决你访问的数据量。
MyISAM是使用操作系统的文件系统缓存来缓存那些经常被查询的数据。然而InnoDB使用的是一种非常不同的方法。
不依赖操作系统的缓存，InnoDB自己在InnoDB Buffer Pool处理缓存。经过这篇文章你会学到它是如何工作的，为什么以那种方式来实施是一个不错的想法。

InnoDB缓冲池不仅仅是一个缓存

InnoDB缓冲池实际上用于多个目的，它用来：
* 数据缓存 – 这绝对是它的最重要的目的
* 索引缓存 – 这使用是的同一个缓冲池
* 缓冲 – 更改的数据(通常称为脏数据)在被刷新到硬盘之前先存放到缓冲
* 存储内部结构 – 一些结构如自适应哈希索引或者行锁也都存储在InnoDB缓冲池
下面是一个经典的把innodb-buffer-pool-size设置为62G的InnoDB缓冲池页的分布情况:
MySQL
正如你所看到的，Buffer Pool大多是用于普通的InnoDB页面，但大约10%用作其它目的。
这张表的单位是InnoDB页。单个页面大小实际上是16K，所以你可以乘以16,384来得到以字节为单位更直观的使用情况。

InnoDB缓冲池的大小

那么innodb-buffer-pool-size的大小应该设置为什么呢？下面我们就开始谈到这个。

独立服务器

在一个独立的只使用InnoDB引擎的MySQL服务器中，根据经验，推荐设置innodb-buffer-pool-size为服务器总可用内存的80%。
为什么不是90%或者100%呢？
因为其它的东西也需要内存：
* 每个查询至少需要几K的内存(有时候是几M)
* 有各种其它内部的MySQL结构和缓存
* InnoDB有一些结构是不用缓冲池的内存的(字典缓存，文件系统，锁系统和页哈希表等)
* 也有一些MySQL文件是在OS缓存里的(binary日志，relay日志,innodb事务日志等)
* 此处，你也必须为操作系统留出些内存

共享服务器

如果你的MySQL服务器与其它应用共享资源，那么上面80%的经验就不那么适用了。
在这样的环境下，设置一个对的数字有点难度。
首先让我们来统计一下InnoDB表的实际占用大小。执行如下查询：

  1. SELECT engine,
  2.   count(*) as TABLES,
  3.   concat(round(sum(table_rows)/1000000,2),’M’) rows,
  4.   concat(round(sum(data_length)/(1024*1024*1024),2),’G’) DATA,
  5.   concat(round(sum(index_length)/(1024*1024*1024),2),’G’) idx,
  6.   concat(round(sum(data_length+index_length)/(1024*1024*1024),2),’G’) total_size,
  7.   round(sum(index_length)/sum(data_length),2) idxfrac
  8. FROM information_schema.TABLES
  9. WHERE table_schema not in (‘mysql’, ‘performance_schema’, ‘information_schema’)
  10. GROUP BY engine
  11. ORDER BY sum(data_length+index_length) DESC LIMIT 10;

这会给出一个参考，让你知道如果你想缓存整个数据集应该为InnoDB缓冲池设置多少内存合适。
不过大多数情况你不需要那样做，你只需要缓存你经常使用的数据集。
设置好之后，我们来看看如何检查InnoDB缓冲池大小是否设置足够。
在终端中，执行如下命令：

  1. $ mysqladmin ext -ri1 | grep Innodb_buffer_pool_reads
  2. | Innodb_buffer_pool_reads                 | 1832098003     |
  3. | Innodb_buffer_pool_reads                 | 595            |
  4. | Innodb_buffer_pool_reads                 | 915            |
  5. | Innodb_buffer_pool_reads                 | 734            |
  6. | Innodb_buffer_pool_reads                 | 622            |
  7. | Innodb_buffer_pool_reads                 | 710            |
  8. | Innodb_buffer_pool_reads                 | 664            |
  9. | Innodb_buffer_pool_reads                 | 987            |
  10. | Innodb_buffer_pool_reads                 | 1287           |
  11. | Innodb_buffer_pool_reads                 | 967            |
  12. | Innodb_buffer_pool_reads                 | 1181           |
  13. | Innodb_buffer_pool_reads                 | 949            |

你所看到的是从硬盘读取数据到缓冲池的次数(每秒)。上面的数据已经相当高了(幸运的是，这个服务器的IO设备能处理每秒4000的IO操作)，如果这个是OLTP系统，我建议提高innodb缓冲池的大小和如果必要增加服务器内存。

更改InnoDB缓冲池

最后，介绍如何更改innodb-buffer-pool-size。
如果你运行的是MySQL 5.7，那么非常幸运，你可以在线更改这个变量，只需要以root身份执行如下查询：

  1. mysql> SET GLOBAL innodb_buffer_pool_size=size_in_bytes;

这还没完，你仍然需要更改my.cnf文件，不过至少你不需要重启服务器让它生效。从mysql的错误日志中我们可以看到它生效的过程：

  1. [Note] InnoDB: Resizing buffer pool from 134217728 to 21474836480. (unit=134217728)
  2. [Note] InnoDB: disabled adaptive hash index.
  3. [Note] InnoDB: buffer pool 0 : 159 chunks (1302369 blocks) were added.
  4. [Note] InnoDB: buffer pool 0 : hash tables were resized.
  5. [Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
  6. [Note] InnoDB: Completed to resize buffer pool from 134217728 to 21474836480.
  7. [Note] InnoDB: Re-enabled adaptive hash index.

在更早的mysql版本就需要重启了，所以：
1. 在my.cnf中设置一个innodb_buffer_pool_size合适的值
2.重启mysql服务器

DEFAULT_STORAGE_ENGINE:选择正确的MySQL存储引擎

现在让我们把MySQL存储引擎的问题放在一边。如果你的MySQL表都是用的InnoDB而你不需要关心InnoDB是如何运作的，你已经设置了，但不确定是否生效。这些问题将在下面会提到。

关于存储引擎

MySQL自20多年前成立以来一直支持可插拔存储引擎，但在一段相当长的时间里MyISAM一直是默认的存储引擎，许多人运行MySQL甚至对底层存储引擎一点都不了解。毕竟，MySQL刚开始是为小型网站的小型数据库设计的，许多应用已经习惯使用MyISAM存储引擎。

刚开始没什么问题，一切正常，但现在的问题是：MyISAM没有考虑到应用到高并发高负载，多核CPU和RAID阵列的场景，也不能弹性扩展。所以网站流量越来越多后，他们不能扩展，因为MySQL查询会在表级锁上等待数秒(MyISAM只支持这种锁机制)。他们不想每次MySQL崩溃时损坏他们的业务数据。

INNODB存储引擎

许多人并不知道，自MySQL存在以来MyISAM存储引擎就有一个兄弟叫InnoDB。并且高并发负载，性能和弹性(也包括原子性，一致性和隔离)正是它的特长。
当然，在InnoDB发展过程中也有过一些问题(尤其是2006年5.0.30之前的版本的性能问题)，但在这之后的10年时间里，InnoDB已经在你能想到的领域(或者没有)得到了证明，而MyISAM已经很少被关注了。
因此，从MySQL 5.5.5开始，InnoDB成为默认的存储引擎，现在你几乎找不到大型MySQL数据库的安装使用MyISAM而不是InnoDB。
下面让我来告诉你如何快速地统计和列出在你系统的所有MyISAM表，方便你开始计划迁移。

你使用的存储引擎

下面的查询展示你所用的存储引擎以及它们的一些统计信息，包括表数量，大小等。

  1. mysql> SELECT engine,
  2.   count(*) as TABLES,
  3.   concat(round(sum(table_rows)/1000000,2),’M’) rows,
  4.   concat(round(sum(data_length)/(1024*1024*1024),2),’G’) DATA,
  5.   concat(round(sum(index_length)/(1024*1024*1024),2),’G’) idx,
  6.   concat(round(sum(data_length+index_length)/(1024*1024*1024),2),’G’) total_size,
  7.   round(sum(index_length)/sum(data_length),2) idxfrac
  8.  FROM information_schema.TABLES
  9. WHERE table_schema not in (‘mysql’, ‘performance_schema’, ‘information_schema’)
  10. GROUP BY engine
  11. ORDER BY sum(data_length+index_length) DESC LIMIT 10;
  12. +——–+——–+———+——–+——–+————+———+
  13. | engine | TABLES | rows    | DATA   | idx    | total_size | idxfrac |
  14. +——–+——–+———+——–+——–+————+———+
  15. | InnoDB |    181 | 457.58M | 92.34G | 54.58G | 146.92G    |    0.59 |
  16. | MyISAM |     13 | 22.91M  | 7.85G  | 2.12G  | 9.97G      |    0.27 |
  17. +——–+——–+———+——–+——–+————+———+
  18. 2 rows in set (0.22 sec)

获取以大小排序的MyISAM表列表，执行如下查询：

  1. SELECT
  2.     concat(table_schema, ‘.’, table_name) tbl,
  3.     engine,
  4.     concat(round(table_rows/1000000,2),’M’) rows,
  5.     concat(round(data_length/(1024*1024*1024),2),’G’) DATA,
  6.     concat(round(index_length/(1024*1024*1024),2),’G’) idx,
  7.     concat(round((data_length+index_length)/(1024*1024*1024),2),’G’) total_size,
  8.     round(index_length/data_length,2) idxfrac
  9.  FROM information_schema.TABLES
  10. WHERE table_schema not in (‘mysql’, ‘performance_schema’, ‘information_schema’)
  11.   AND engine = ‘MyISAM’
  12. ORDER BY data_length+index_length DESC;

需要记住的是，更改默认的存储引擎为InnoDB或者升级MySQL并不会自动把你的表转换为InnoDB。目前为止，你需要一个表一个表地转换，或者使用脚本。
需要注意的是，小的MyISAM表也一样需要转换，因为只要有一个MyISAM用在join语句里，那么整个查询都是用表级锁，所以这将对并发有很大影响。所以确保你把所有的MyISAM表转为InnoDB表。

转换为INNODB

建议在你着手转换引擎为InnoDB之前，最好先熟悉理解一下InnoDB的配置。准备好后，执行如下查询来转换：

  1. SET @DB_NAME = ‘your_database’;
  2.  
  3. SELECT  CONCAT(‘ALTER TABLE `’, table_name, ‘` ENGINE=InnoDB;’) AS sql_statements
  4. FROM    information_schema.tables AS tb
  5. WHERE   table_schema = @DB_NAME
  6. AND     `ENGINE` = ‘MyISAM’
  7. AND     `TABLE_TYPE` = ‘BASE TABLE’
  8. ORDER BY table_name DESC;

Bash使用示例(5) – 条件表达式

文件类型测试

-e条件运算符用来测试一个文件是否存在(包括所有文件类型,目录等)

  1. if [[ -e $filename ]]; then
  2.   echo "$filename exists"
  3. fi

也可以测试指定类型的文件

  1. if [[ -f $filename ]]; then
  2.   echo "$filename is a regular file"
  3. elif [[ -d $filename ]]; then
  4.   echo "$filename is a directory"
  5. elif [[ -p $filename ]]; then
  6.   echo "$filename is a named pipe"
  7. elif [[ -S $filename ]]; then
  8.   echo "$filename is a named socket"
  9. elif [[ -b $filename ]]; then
  10.   echo "$filename is a block device"
  11. elif [[ -c $filename ]]; then
  12.   echo "$filename is a character device"
  13. fi
  14. if [[ -L $filename ]]; then
  15.   echo "$filename is a symbolic link (to any file type)"
  16. fi

对于软链接，使用-L测试时，当链接指向的目标文件不存在时会返回false

  1. if [[ -L $filename || -e $filename ]]; then
  2.   echo "$filename exists (but may be a broken symbolic link)"
  3. fi
  4.  
  5. if [[ -L $filename && ! -e $filename ]]; then
  6.   echo "$filename is a broken symbolic link"
  7. fi

字符串比较和匹配

在两个带引号的字符串之间比较时使用==操作符。!=操作符则是否则的比较

  1. if [[ "$string1" == "$string2" ]]; then
  2.   echo "$string1 and $string2 are identical"
  3. fi
  4. if [[ "$string1" == "$string2" ]]; then
  5.   echo "$string1 and $string2 are identical"
  6. fi

如果右则没有引号，那么它是以通配符的模式作比较。

  1. string1=’abc’
  2. pattern=’a*’
  3. if [[ "$string1" == $pattern ]]; then
  4.   # the test is true
  5.   echo "The string $string1 matches the pattern $pattern"
  6. fi
  7. if [[ "$string1" != "$pattern" ]]; then
  8.   echo "The string $string1 is not equal to the string $pattern"
  9. fi

操作符以字典顺序来比较字符串(它们没有小或等于或大于的说法)
可以测试空字符串

  1. if [[ -n $string ]]; then
  2.   echo "$string is non-empty"
  3. fi
  4. if [[ -z "${string// }" ]]; then
  5.   echo "$string is empty or contains only spaces"
  6. fi
  7. if [[ -z $string ]]; then
  8.   echo "$string is empty"
  9. fi

以上的-z检查可能表示$string没有被设置或者已经设置了但为空字符。为了区分空字符和未设置，使用：

  1. if [[ -n ${string+x} ]]; then
  2.     echo "$string is set, possibly to the empty string"
  3. fi
  4. if [[ -n ${string-x} ]]; then
  5.     echo "$string is either unset or set to a non-empty string"
  6. fi
  7. if [[ -z ${string+x} ]]; then
  8.     echo "$string is unset"
  9. fi
  10. if [[ -z ${string-x} ]]; then
  11.     echo "$string is set to an empty string"
  12. fi

其中x是任意的，以表格形式如下：

  1. +——-+——-+———–+
  2.             $string is: | unset | empty | non-empty |
  3. +———————–+——-+——-+———–+
  4. | [[ -z ${string} ]]    | true  | true  | false     |
  5. | [[ -z ${string+x} ]]  | true  | false | false     |
  6. | [[ -z ${string-x} ]]  | false | true  | false     |
  7. | [[ -n ${string} ]]    | false | false | true      |
  8. | [[ -n ${string+x} ]]  | false | true  | true      |
  9. | [[ -n ${string-x} ]]  | true  | false | true      |
  10. +———————–+——-+——-+———–+

文件权限测试

  1. if [[ -r $filename ]]; then
  2.   echo "$filename is a readable file"
  3. fi
  4. if [[ -w $filename ]]; then
  5.   echo "$filename is a writable file"
  6. fi
  7. if [[ -x $filename ]]; then
  8.   echo "$filename is an executable file"
  9. fi

Bash使用示例(4) – 重定向输出

重定向标准输出

> 重定向当前命令的标准输出(STDOUT)到一个文件或者一个描述符。
下面的例子把ls命令的输出存到file.txt文件

  1. ls >file.txt
  2. > file.txt ls

目标文件如果不存在就会被创建，或者文件被截断。
如果不指定，默认重定向描述符是标准输出或1。下面的命令等同于上面的例子：

  1. ls 1>file.txt

追加 vs 截断

截断 >
1.如果文件不存在则创建
2.截断(删除文件内容)
3.写入文件

  1. $ echo "first line" > /tmp/lines
  2. $ echo "second line" > /tmp/lines
  3.  
  4. $ cat /tmp/lines
  5. second line

追加 >>
1.如果文件不存在则创建
2.追加文件(在文件底部写)

  1. # Overwrite existing file
  2. $ echo "first line" > /tmp/lines
  3.  
  4. # Append a second line
  5. $ echo "second line" >> /tmp/lines
  6.  
  7. $ cat /tmp/lines
  8. first line
  9. second line

重定向标准输出和标准错误

文件描述符像0和1都是指针。我们更改的是文件描述符的指向。
>/dev/null意思是1指向/dev/null。
首先我们把1(STDOUT)指向/dev/null，然后2指向1(不管1指向什么)。

  1. echo_to_stdout_and_stderr >/dev/null 2>&1

可以更短点：
echo_to_stdout_and_stderr &> /dev/null

使用命名管道

有时候你想把一个程序的标准输出作为其它多个程序的标准输入，这时候就不能用标准管道了，不过你可以写入一个临时文件，如：

  1. touch tempFile.txt
  2. ls -l > tempFile.txt &
  3. grep ".log" < tempFile.txt

这个方法可以在大多数情况下有效，但谁都不知道tempFile.txt会被哪个程序删除或者修改里面的内容。这时候命名管道就可以用上场了。

  1. mkfifo myPipe
  2. ls -l > myPipe
  3. grep ".log" < myPipe

myPipe在技术上是一个文件，所以我们来用ls -l看下当前创建管道的目录

  1. mkdir pipeFolder
  2. cd pipeFolder
  3. mkfifo myPipe
  4. ls -l

输出为：
prw-r–r– 1 root root 0 Jul 25 11:20 myPipe
注意权限的第一个字符，显示是pipe，不是文件。
现在我们做了有意思的。
打开一个终端，在一个空目录创建管道:

  1. mkfifo myPipe

现在我们输入点东西到管道：

  1. echo "Hello from the other side" > myPipe

你会注意到这个命令被挂起了，让我们打开一个新的终端，输入：

  1. cat < myPipe

你会发现当”hello from the other side”输出后，终端1就完成了，终端2也一样。
现在我们反向运行程序，先执行cat < myPipe，然后再输入点东西到myPipe，它仍然按预期工作，因为一个程序会一起等待直到管道中被输入一些东西。
命名管道在终端间或程序间传递信息时会非常有用。

输出错误信息到标准错误

错误信息通常为了调度会包含在脚本里。简单的输出错误信息如下：

  1. cmd || echo ‘cmd failed’

可能会在简单的场景工作，但不是通常的做法。在这个例子中，错误信息会会污染脚本实际的输出。简单来说，错误信息应该输出到标准错误而不是标准输出，如：

  1. cmd || echo ‘cmd failed’ >/dev/stderr

其它例子：

  1. if cmd; then
  2.     echo ‘success’
  3. else
  4.     echo ‘cmd failed’ >/dev/stderr
  5. fi

可以封装成一个函数：

  1. err(){
  2.     echo "E: $*" >>/dev/stderr
  3. }
  4. err "My error message"

Bash使用示例(3) – 使用trap处理信号

清理临时文件

你可以使用trap命令来捕获信号；shell中的trap捕获信号等同于C语言或大多数其它语言中的signal或者sigaction。
trap最常用的场景之一是在预期退出和意外退出时清理临时文件。
遗憾的是没有多少shell脚本这样做。

  1. #!/bin/sh
  2.  
  3. # Make a cleanup function
  4. cleanup() {
  5.   rm –force — "${tmp}"
  6. }
  7.  
  8. # Trap the special "EXIT" group, which is always run when the shell exits.
  9. trap cleanup EXIT
  10.  
  11. # Create a temporary file
  12. tmp="$(mktemp -p /tmp tmpfileXXXXXXX)"
  13.  
  14. echo "Hello, world!" >> "${tmp}"
  15.  
  16. # No rm -f "$tmp" needed. The advantage of using EXIT is that it still works
  17. # even if there was an error or if you used exit.

捕获SIGINT或Ctrl+C信号

当有子shell时，trap会被重置，所以sleep继续作用于由^C发送的SIGINT信号，而父进程(即shell脚本)就不会了。所以下面的例子只是退出了sleep，没有直接退出shell脚本，而是继续往下执行。

  1. #!/bin/sh
  2.  
  3. # Run a command on signal 2 (SIGINT, which is what ^C sends)
  4. sigint() {
  5.     echo "Killed subshell!"
  6. }
  7. trap sigint INT
  8.  
  9. # Or use the no-op command for no output
  10. #trap : INT
  11.  
  12. # This will be killed on the first ^C
  13. echo "Sleeping…"
  14. sleep 500
  15.  
  16. echo "Sleeping…"
  17. sleep 500

通过些许更改可以允许你按两个^C才退出程序：

  1. last=0
  2. allow_quit() {
  3.     [ $(date +%s) -lt $(( $last + 1 )) ] && exit
  4.     echo "Press ^C twice in a row to quit"
  5.     last=$(date +%s)
  6. }
  7. trap allow_quit INT

统计维护退出任务

你有没有在退出时忘记添加trap来清理临时文件或者其它事情？
有没有设置了一个trap而导致取消了另一个？
下面的代码能让你非常容易地在一个地方就把所有需要在退出时做的工作都添加进去，而不是需要一大段的trap声明，这个很容易忘记的。

  1. # on_exit and add_on_exit
  2. # Usage:
  3. #   add_on_exit rm -f /tmp/foo
  4. #   add_on_exit echo "I am exiting"
  5. #   tempfile=$(mktemp)
  6. #   add_on_exit rm -f "$tempfile"
  7. # Based on http://www.linuxjournal.com/content/use-bash-trap-statement-cleanup-temporary-files
  8. function on_exit()
  9. {
  10.     for i in "${on_exit_items[@]}"
  11.     do
  12.         eval $i
  13.     done
  14. }
  15. function add_on_exit()
  16. {
  17.     local n=${#on_exit_items[*]}
  18.     on_exit_items[$n]="$*"
  19.     if [[ $n -eq 0 ]]; then
  20.         trap on_exit EXIT
  21.     fi
  22. }

退出时杀掉子进程

Trap表达式不一定需要单独的函数或者程序，它也可以直接使用复杂的表达式，如：

  1. trap ‘jobs -p | xargs kill’ EXIT

Bash使用示例(2) – 内部变量

$@

“$@”把所有的命令行参数作为一个数组返回。与”$*”不一样，它是作为一个字符串来返回。
“$@”可以通过循环来遍历所有元素，如下脚本：

  1. #!/bin/bash
  2. for var in "$*"; do
  3.     echo $var
  4. done

因为$*只把参数作为一个字符串返回，echo就只被调用一次：

  1. ~> $ ./testscript.sh firstarg secondarg thirdarg
  2. firstarg secondarg thirdarg

使用$@时：

  1. #!/bin/bash
  2. for var in "$@"; do
  3.     echo $var
  4. done

以数组返回所有参数，使用你能够单独地访问每一个参数：

  1. ~> $ ./testscript.sh firstarg secondarg thirdarg
  2. firstarg
  3. secondarg
  4. thirdarg

$#

获取命令行参数个数，键入：

  1. #!/bin/bash
  2. echo "$#"

如带3个参数运行脚本，输出如下：

  1. ~> $ ./testscript.sh firstarg secondarg thirdarg
  2. 3

$!

返回上一个程序执行的进程ID:

  1. ~> $ ls &
  2. testfile1 testfile2
  3. [1]+  Done                    ls
  4. ~> $ echo $!
  5. 21715

$$

当前进程的pid，如果在bash命令行下执行，相当于是bash进程的pid:

  1. ~> $ echo $$
  2. 13246

$*

以单个字符串返回所有命令行参数。
testscript.sh:

  1. #!/bin/bash
  2. echo "$*"

带几个参数运行脚本：

  1. ./testscript.sh firstarg secondarg thirdarg

输出：

  1. firstarg secondarg thirdarg

$?

返回上一次函数或命令执行的退出状态。通过0表示执行成功，其它的则表示执行失败：

  1. ~> $ ls *.blah;echo $?
  2. ls: cannot access *.blah: No such file or directory
  3. 2
  4. ~> $ ls;echo $?
  5. testfile1 testfile2
  6. 0

$1 $2 $3等

从命令行传递给脚本或者一个函数的位置参数：

  1. #!/bin/bash
  2. # $n is the n’th positional parameter
  3. echo "$1"
  4. echo "$2"
  5. echo "$3"

输出如下：
~> $ ./testscript.sh firstarg secondarg thirdarg

  1. firstarg
  2. secondarg
  3. thirdarg

如果位置参数的数量大于9，需要使用大括号：

  1. #  "set — " sets positional parameters
  2. set — 1 2 3 4 5 6 7 8 nine ten eleven twelfe
  3. echo $10   # outputs 1
  4. echo ${10} # outputs ten

$FUNCNAME

获取当前函数的名称：

  1. my_function()
  2. {
  3.     echo "This function is $FUNCNAME"    # This will output "This function is my_function"
  4. }

如果在函数外打印此变量：

  1. my_function
  2.  
  3. echo "This function is $FUNCNAME"    # This will output "This function is"

$HOME

用户的主目录

  1. ~> $ echo $HOME
  2. /home/user

$IFS

此变量包含用于循环中bash拆分字符串的内部字段分隔符。默认是空白字符n(newline),t(tab)或者空格。更改它的话使你能够使用不同分隔符拆分字符串：

  1. IFS=","
  2. INPUTSTR="a,b,c,d"
  3. for field in ${INPUTSTR}; do
  4.     echo $field
  5. done

输出：
a
b
c
d

$PWD

输出当前工作目录

  1. ~> $ echo $PWD
  2. /home/user
  3. ~> $ cd directory
  4. directory> $ echo $PWD
  5. /home/user/directory

$HOSTNAME

系统启动时分配的主机名

  1. ~> $ echo $HOSTNAME
  2. mybox.mydomain.com

$LINENO

输出脚本的当前行号。在调试脚本时可能会用到。

  1. #!/bin/bash
  2. # this is line 2
  3. echo something  # this is line 3
  4. echo $LINENO # Will output 4

Bash使用示例(1) – 数组

数组赋值

列表赋值

用新元素创建数组

  1. array=(‘first element’ ‘second element’ ‘third element’)

下标赋值

显式指定元素索引创建数组：

  1. array=([3]=’fourth element’ [4]=’fifth element’)

按索引赋值

  1. array[0]=’first element’
  2. array[1]=’second element’

按名称赋值（关联数组）

  1. declare -A array
  2. array[first]=’First element’
  3. array[second]=’Second element’

动态赋值

以其它命令的输出创建一个数组，例如使用seq创建一个从1到10的数组：

  1. array=(`seq 1 10`)

从脚本参数创建数组

  1. array=("$@")

循环内赋值

  1. while read -r; do
  2.     #array+=("$REPLY")     # Array append
  3.     array[$i]="$REPLY"     # Assignment by index
  4.     let i++                # Increment index
  5. done < <(seq 1 10)  # command substitution
  6. echo ${array[@]}    # output: 1 2 3 4 5 6 7 8 9 10

访问数组元素

打印索引为0的元素

  1. echo "${array[0]}"

打印最后一个元素(从Bash 4.3可用)

  1. echo "${array[-1]}"

打印从索引1开始的元素

  1. echo "${array[@]:1}"

打印从索引1开始的3个元素

  1. echo "${array[@]:1:3}"

数组更改

按索引更改

初始化或者更新数组中的一个特定元素

  1. array[10]="elevenths element"    # because it’s starting with 0

追回

修改数组，追加元素到数组结尾

  1. array+=(‘fourth element’ ‘fifth element’)

添加元素到数组开头

  1. array=("new element" "${array[@]}")

插入

给定索引值插入一个元素

  1. arr=(a b c d)
  2. # insert an element at index 2
  3. i=2
  4. arr=("${arr[@]:0:$i}" ‘new’ "${arr[@]:$i}")
  5. echo "${arr[2]}" #output: new

删除

使用uset删除指定索引元素

  1. arr=(a b c)
  2. echo "${arr[@]}"   # outputs: a b c
  3. echo "${!arr[@]}"  # outputs: 0 1 2
  4. unset -v ‘arr[1]’
  5. echo "${arr[@]}"   # outputs: a c
  6. echo "${!arr[@]}"  # outputs: 0 2

重排索引

当有一些元素从数组被删除时，可以使用下面方法重排索引，或者你不知道索引是否存在时隙时会有用。

  1. array=("${array[@]}")

数组长度

${#array[@]}可以得到${array[@]}数组的长度

  1. array=(‘first element’ ‘second element’ ‘third element’)
  2. echo "${#array[@]}" # gives out a length of 3

迭代数组元素

  1. fileList=( file1.txt file2.txt file3.txt )
  2.  
  3. # Within the for loop, $file is the current file
  4. for file in "${fileList[@]}"
  5. do
  6.   echo "$file"
  7. done

Docker使用示例(5) – 查看容器信息

查看容器信息

  1. docker inspect -f ‘<format>’ <container>

查看网络设置

  1. docker inspect -f ‘{{ .NetworkSettings }}’ <container>

以json格式输出：

  1. docker inspect -f ‘{{ json.NetworkSettings }}’ <container>

获取IP地址

  1. docker inspect -f ‘{{ .NetworkSettings.IPAddress }}’ <container>
  2. <container>

当docker inspect输出多个同类的元素时，我们可以获取指定次序的元素，如获取Config.Env第一个元素：

  1. docker inspect –format ‘{{ index (index .Config.Env) 0 }}’ <container>

也可以获取元素的数量：

  1. docker inspect –format ‘{{ len .Config.Env }}’ <container>