MYSQL性能调优与架构设计书读书笔记1

《MYSQL性能调优与架构设计书》，是阿里巴巴著名MYSQL DBA简朝阳的大作，最近在读，
觉得十分好，笔记之：

mysql读书笔记1


1 mysqladmin -u xxxx -p密码  -h localhost ping
  用ping命令检查mysqld状态是否正常

2 获取当前mysql的状态值

   mysqladmin -u xxxx -p密码  -h localhost status
3 获取数据库连接线程信息
   mysqladmin -u xxxx -p密码  -h localhost processlist

4 innodb的物理结构
   一种是共享表空间，也就是所有数据，索引数据，各种数据及UNDO都放在同一个表空间（一个
或者多个数据文件），通过innodb_data_file_path来指定，增加数据文件要停机或重启动；
另外一个是独享表空间，每个表的数据和索引放在一个单独的ibd文件中，

5 备份MYISAM的一种方法，先把表加锁的方法：
   flush tables with read lock;
cp -R test /tmp/backup/test
unlock tables;

6 MYSQL表的权限级别有5个：
   global level:全局权限，这个是最高级别的了，对数据库的所有表和字段都有权力管理，会覆盖其他4个的权限，

典型的如：
   grant select,update,delete on *.* to '111'@'11221";

  database level:即比如指定 database.*的这样的授权了
 
  table level:指定某个表的权限，比如 grant index on test.t1 to ........
  column level:只能操作某些列，比如  grant select(id,value) on test.t2 to ......
  routine level:只有excute和alter routine两种，主要是管理procedure和function.

7 MYSQL的锁：
  有三类，行级（MYISAM），表级（INNODB），页级（BERKEYLEYDB）
8 尽量避免复杂的JOIN语句和子查询，因为越复杂的join语句，锁定的资源也就越多，阻塞其他线程也越多。

9 explain中出现的参数全解析：
   MySQL Explain 功能中给我们展示的各种信息的解释：
◆ ID：Query Optimizer 所选定的执行计划中查询的序列号；
◆ Select_type：所使用的查询类型，主要有以下这几种查询类型
◇ DEPENDENT SUBQUERY：子查询中内层的第一个SELECT，依赖于外部查询的结果集；
◇ DEPENDENT UNION：子查询中的UNION，且为UNION 中从第二个SELECT 开始的后面所有
SELECT，同样依赖于外部查询的结果集；
◇ PRIMARY：子查询中的最外层查询，注意并不是主键查询；
◇ SIMPLE：除子查询或者UNION 之外的其他查询；
◇ SUBQUERY：子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集；
◇ UNCACHEABLE SUBQUERY：结果集无法缓存的子查询；
◇ UNION：UNION 语句中第二个SELECT 开始的后面所有SELECT，第一个SELECT 为PRIMARY
◇ UNION RESULT：UNION 中的合并结果；
◆ Table：显示这一步所访问的数据库中的表的名称；
◆ Type：告诉我们对表所使用的访问方式，主要包含如下集中类型；
◇ all：全表扫描
◇ const：读常量，且最多只会有一条记录匹配，由于是常量，所以实际上只需要读一次；
◇ eq_ref：最多只会有一条匹配结果，一般是通过主键或者唯一键索引来访问；
◇ fulltext：
◇ index：全索引扫描；
◇ index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行merge 之后再读
取表数据；
◇ index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个
主键或者唯一索引；
◇ rang：索引范围扫描；
◇ ref：Join 语句中被驱动表索引引用查询；
◇ ref_or_null：与ref 的唯一区别就是在使用索引引用查询之外再增加一个空值的查询；
◇ system：系统表，表中只有一行数据；
◇ unique_subquery：子查询中的返回结果字段组合是主键或者唯一约束；
◇
◆ Possible_keys：该查询可以利用的索引. 如果没有任何索引可以使用，就会显示成null，这一
项内容对于优化时候索引的调整非常重要；
◆ Key：MySQL Query Optimizer 从possible_keys 中所选择使用的索引；
◆ Key_len：被选中使用索引的索引键长度；
◆ Ref：列出是通过常量（const），还是某个表的某个字段（如果是join）来过滤（通过key）
的；
◆ Rows：MySQL Query Optimizer 通过系统收集到的统计信息估算出来的结果集记录条数；
◆ Extra：查询中每一步实现的额外细节信息，主要可能会是以下内容：
◇ Distinct：查找distinct 值，所以当mysql 找到了第一条匹配的结果后，将停止该值的查
询而转为后面其他值的查询；
◇ Full scan on NULL key：子查询中的一种优化方式，主要在遇到无法通过索引访问null
值的使用使用；
◇ Impossible WHERE noticed after reading const tables：MySQL Query Optimizer 通过
收集到的统计信息判断出不可能存在结果；
◇ No tables：Query 语句中使用FROM DUAL 或者不包含任何FROM 子句；
◇ Not exists：在某些左连接中MySQL Query Optimizer 所通过改变原有Query 的组成而
使用的优化方法，可以部分减少数据访问次数；
◇ Range checked for each record (index map: N)：通过MySQL 官方手册的描述，当
MySQL Query Optimizer 没有发现好的可以使用的索引的时候，如果发现如果来自前面的
表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL 检查是否可以使
用range 或index_merge 访问方法来索取行。
◇ Select tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段的
时候，MySQL Query Optimizer 会通过索引而直接一次定位到所需的数据行完成整个查
询。当然，前提是在Query 中不能有GROUP BY 操作。如使用MIN()或者MAX（）的时
候；
◇ Using filesort：当我们的Query 中包含ORDER BY 操作，而且无法利用索引完成排序操
作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。
◇ Using index：所需要的数据只需要在Index 即可全部获得而不需要再到表中取数据；
◇ Using index for group-by：数据访问和Using index 一样，所需数据只需要读取索引即
可，而当Query 中使用了GROUP BY 或者DISTINCT 子句的时候，如果分组字段也在索引
中，Extra 中的信息就会是Using index for group-by；
◇ Using temporary：当MySQL 在某些操作中必须使用临时表的时候，在Extra 信息中就会
出现Using temporary 。主要常见于GROUP BY 和ORDER BY 等操作中。
◇ Using where：如果我们不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需
要的数据，则会出现Using where 信息；
◇ Using where with pushed condition：这是一个仅仅在NDBCluster 存储引擎中才会出现
的信息，而且还需要通过打开Condition Pushdown 优化功能才可能会被使用。控制参数
为engine_condition_pushdown 。
  

10 索引的三个好的实践：
  A 频繁作为查询条件的字段应该做索引
  B 当唯一性太差的字段不应该去做索引
  C 更新频繁的字段不适合做索引
11 MYSQL日志设置优化
   MYSQL中，对性能产生影响大的是binlog二进制日志了。

  show variables like '%binlog%';
   “sync_binlog”：这个参数是对于MySQL 系统来说是至关重要的，他不仅影响到Binlog 对MySQL 所
带来的性能损耗，而且还影响到MySQL 中数据的完整性。对于“sync_binlog”参数的各种设置的说明如
下：
● sync_binlog=0，当事务提交之后，MySQL 不做fsync 之类的磁盘同步指令刷新binlog_cache 中
的信息到磁盘，而让Filesystem 自行决定什么时候来做同步，或者cache 满了之后才同步到磁
盘。
● sync_binlog=n，当每进行n 次事务提交之后，MySQL 将进行一次fsync 之类的磁盘同步指令来
将binlog_cache 中的数据强制写入磁盘。
在MySQL 中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性
能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache 中的所有binlog 信息都会被
丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1 的时候，即使系统
Crash，也最多丢失binlog_cache 中未完成的一个事务，对实际数据没有任何实质性影响。从以往经验
和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0 和设置为1 的系统写入性能差
距可能高达5 倍甚至更多。

  MySQL 中Binlog 的产生量是没办法改变的，只要我们的Query 改变了数据库中的数据，那么就必须
将该Query 所对应的Event 记录到Binlog 中。那我们是不是就没有办法优化复制了呢？当然不是，在
MySQL 复制环境中，实际上是是有8 个参数可以让我们控制需要复制或者需要忽略而不进行复制的DB 或
者Table 的，分别为：
● Binlog_Do_DB：设定哪些数据库（Schema）需要记录Binlog；
● Binlog_Ignore_DB：设定哪些数据库（Schema）不要记录Binlog；
● Replicate_Do_DB：设定需要复制的数据库（Schema），多个DB 用逗号（“,”）分隔；
● Replicate_Ignore_DB：设定可以忽略的数据库（Schema）；
● Replicate_Do_Table：设定需要复制的Table；
● Replicate_Ignore_Table：设定可以忽略的Table；
● Replicate_Wild_Do_Table：功能同Replicate_Do_Table，但可以带通配符来进行设置；
● Replicate_Wild_Ignore_Table：功能同Replicate_Ignore_Table，可带通配符设置；

12 慢查询日志的设置：
   查看是否打开；
show variables like 'log_slow%';
查看时间设置：
  show variables like 'long_query%';

13 query cache相关
Query Cache 实现原理实际上并不是特别的复杂，简单的来说就是将客户端请求的Query
语句（当然仅限于SELECT 类型的Query）通过一定的hash 算法进行一个计算而得到一个hash 值，存放
在一个hash 桶中。同时将该Query 的结果集（Result Set）也存放在一个内存Cache 中的。存放Query
hash 值的链表中的每一个hash 值所在的节点中同时还存放了该Query 所对应的Result Set 的Cache 所
在的内存地址，以及该Query 所涉及到的所有Table 的标识等其他一些相关信息。系统接受到任何一个
SELECT 类型的Query 的时候，首先计算出其hash 值，然后通过该hash 值到Query Cache 中去匹配，如
果找到了完全相同的Query，则直接将之前所Cache 的Result Set 返回给客户端而完全不需要进行后面
的任何步骤即可完成这次请求。而后端的任何一个表的任何一条数据发生变化之后，也会通知Query
Cache，需要将所有与该Table 有关的Query 的Cache 全部失效，并释放出之前占用的内存地址，以便后
面其他的Query 能够使用。
   所以说，querycachae对于频繁变化的表和数据其实是作用不大的，而且要耗费hash计算。
   MySQL 中针对Query Cache 有两个专
用的SQL Hint（提示）：SQL_NO_CACHE 和SQL_CACHE，分别代表强制不使用Query Cache 和强制使用
Query Cache  

  show variables like '%query_cache%';
  “have_query_cache”：该MySQL 是否支持Query Cache；
● “query_cache_limit”：Query Cache 存放的单条Query 最大Result Set ，默认1M；
● “query_cache_min_res_unit”：Query Cache 每个Result Set 存放的最小内存大小，默认
4k；
● “query_cache_size”：系统中用于Query Cache 内存的大小；
● “query_cache_type”：系统是否打开了Query Cache 功能；
● “query_cache_wlock_invalidate”：针对于MyISAM 存储引擎，设置当有WRITE LOCK 在某个
Table 上面的时候，读请求是要等待WRITE LOCK 释放资源之后再查询还是允许直接从Query
Cache 中读取结果，默认为FALSE（可以直接从Query Cache 中取得结果）。
    如果我们要了解Query Cache 的使用情况，则可以通过Query Cache 相关的状态变量来获取，如通过
如下命令：
mysql> show status like 'Qcache%';
“Qcache_free_blocks”：Query Cache 中目前还有多少剩余的blocks。如果该值显示较大，
则说明Query Cache 中的内存碎片较多了，可能需要寻找合适的机会进行整理（）。
● “Qcache_free_memory”：Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准
确的观察出当前系统中的Query Cache 内存大小是否足够，是需要增加还是过多了；
● “Qcache_hits”：多少次命中。通过这个参数我们可以查看到Query Cache 的基本效果；
● “Qcache_inserts”：多少次未命中然后插入。通过“Qcache_hits”和“Qcache_inserts”两
个参数我们就可以算出Query Cache 的命中率了：
Query Cache 命中率= Qcache_hits / ( Qcache_hits + Qcache_inserts )；
● “Qcache_lowmem_prunes”：多少条Query 因为内存不足而被清除出Query Cache。通过
“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解到我们系
统中Query Cache 的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query 被换
出
● “Qcache_not_cached”：因为query_cache_type 的设置或者不能被cache 的Query 的数量；
● “Qcache_queries_in_cache”：当前Query Cache 中cache 的Query 数量；
● “Qcache_total_blocks”：当前Query Cache 中的block 数量；

14 网络设置参数
  max_conecctions：整个MySQL 允许的最大连接数；
这个参数主要影响的是整个MySQL 应用的并发处理能力，当系统中实际需要的连接量大于
max_conecctions 的情况下，由于MySQL 的设置限制，那么应用中必然会产生连接请求的等待，
从而限制了相应的并发量。所以一般来说，只要MySQL 主机性能允许，都是将该参数设置的尽
可能大一点。一般来说500 到800 左右是一个比较合适的参考值
● max_user_connections：每个用户允许的最大连接数；
上面的参数是限制了整个MySQL 的连接数，而max_user_connections 则是针对于单个用户的连
接限制。在一般情况下我们可能都较少使用这个限制，只有在一些专门提供MySQL 数据存储服
务，或者是提供虚拟主机服务的应用中可能需要用到。除了限制的对象区别之外，其他方面和
max_connections 一样。这个参数的设置完全依赖于应用程序的连接用户数，对于普通的应用来
说，完全没有做太多的限制，可以尽量放开一些。
● net_buffer_length：网络包传输中，传输消息之前的net buffer 初始化大小；
这个参数主要可能影响的是网络传输的效率，由于该参数所设置的只是消息缓冲区的初始化大
小，所以造成的影响主要是当我们的每次消息都很大的时候MySQL 总是需要多次申请扩展该缓
冲区大小。系统默认大小为16KB，一般来说可以满足大多数场景，当然如果我们的查询都是非
常小，每次网络传输量都很少，而且系统内存又比较紧缺的情况下，也可以适当将该值降低到
8KB。
● max_allowed_packet：在网络传输中，一次传消息输量的最大值；
这个参数与net_buffer_length 相对应，只不过是net buffer 的最大值。当我们的消息传输量
大于net_buffer_length 的设置时，MySQL 会自动增大net buffer 的大小，直到缓冲区大小达
到max_allowed_packet 所设置的值。系统默认值为1MB，最大值是1GB，必须设定为1024 的倍
数，单位为字节。
     back_log：在MySQL 的连接请求等待队列中允许存放的最大连接请求数。
连接请求等待队列，实际上是指当某一时刻客户端的连接请求数量过大的时候，MySQL 主线程没
办法及时给每一个新的连接请求分配（或者创建）连接线程的时候，还没有分配到连接线程的
所有请求将存放在一个等待队列中，这个队列就是MySQL 的连接请求队列。当我们的系统存在
瞬时的大量连接请求的时候，则应该注意back_log 参数的设置。系统默认值为50，最大可以设
置为65535。当我们增大back_log 的设置的时候，同时还需要主义OS 级别对网络监听队列的限
制，因为如果OS 的网络监听设置小于MySQL 的back_log 设置的时候，我们加大“back_log”设
置是没有意义的。


15 在MySQL 中，为了尽可提高客户端请求创建连接这个过程的性能，实现了一个Thread Cache 池，将
空闲的连接线程存放在其中，而不是完成请求后就销毁。这样，当有新的连接请求的时候，MySQL 首先会
检查Thread Cache 池中是否存在空闲连接线程，如果存在则取出来直接使用，如果没有空闲连接线程，
才创建新的连接线程。在MySQL 中与连接线程相关的系统参数及状态变量说明如下：
  我们现看看连接线程相关的系统变量的设置值：
mysql> show variables like 'thread%';
+-------------------+--------+
| Variable_name | Value |
+-------------------+--------+
| thread_cache_size | 64 |
| thread_stack | 196608 |
+-------------------+--------+
再来看一下系统被连接的次数以及当前系统中连接线程的状态值：
mysql> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections | 127 |
+---------------+-------+
mysql> show status like '%thread%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Delayed_insert_threads | 0 |
| Slow_launch_threads | 0 |
| Threads_cached | 4 |
| Threads_connected | 7 |
| Threads_created | 11 |
| Threads_running | 1 |
+------------------------+-------+
通过上面的命令，我们可以看出，系统设置了Thread Cache 池最多将缓存32 个连接线程，每个连接
线程创建之初，系统分配192KB 的内存堆栈空给他。系统启动到现在共接收到客户端的连接127 次，共创
建了11 个连接线程，但前有7 个连接线程处于和客户端连接的状态，而7 个连接状态的线程中只有一个
是active 状态，也就是说只有一个正在处理客户端提交的俄请求。而在Thread Cache 池中当共Cache 了
4 个连接线程。
   通过系统设置和当前状态的分析，我们可以发现，thread_cache_size 的设置已经足够了，甚至还远
大于系统的需要。所以我们可以适当减少thread_cache_size 的设置，比如设置为8 或者16。根据
Connections 和Threads_created 这两个系统状态值，我们还可以计算出系统新建连接连接的Thread
Cache 命中率，也就是通过Thread Cache 池中取得连接线程的次数与系统接收的总连接次数的比率，如
下：
Threads_Cache_Hit = (Connections - Threads_created) / Connections * 100%
我们可以通过上面的这个运算公式计算一下上面环境中的Thread Cache 命中率：Thread_Cache_Hit
= (127 - 12) / 127 * 100% = 90.55%
一般来说，当系统稳定运行一段时间之后，我们的Thread Cache 命中率应该保持在90%左右甚至更
高的比率才算正常。


16 SORT BUFFER和JOIN BUFFER
   show variables like '%buffer%';
  join_buffer_size ：当我们的Join 是ALL ， index ，rang 或者index_merge 的时候使用的
Buffer；
实际上这种Join 被称为Full Join。实际上参与Join 的每一个表都需要一个Join Buffer，所以在
Join 出现的时候，至少是两个。Join Buffer 的设置在MySQL 5.1.23 版本之前最大为4GB，但是从
5.1.23 版本开始，在除了Windows 之外的64 位的平台上可以超出4BG 的限制。系统默认是128KB。
● sort_buffer_size：系统中对数据进行排序的时候使用的Buffer；
Sort Buffer 同样是针对单个Thread 的，所以当多个Thread 同时进行排序的时候，系统中就会出现
多个Sort Buffer。一般我们可以通过增大Sort Buffer 的大小来提高ORDER BY 或者是GROUP BY
的处理性能。系统默认大小为2MB，最大限制和Join Buffer 一样，在MySQL 5.1.23 版本之前最大
为4GB，从5.1.23 版本开始，在除了Windows 之外的64 位的平台上可以超出4GB 的限制。
如果应用系统中很少有Join 语句出现，则可以不用太在乎join_buffer_size 参数的大小设置，但是
如果Join 语句不是很少的话，个人建议可以适当增大join_buffer_size 的设置到1MB 左右，如果内存充
足甚至可以设置为2MB。对于sort_buffer_size 参数来说，一般设置为2MB 到4MB 之间可以满足大多数
应用的需求。当然，如果应用系统中的排序都比较大，内存充足且并发量不是特别的大的时候，也可以
继续增大sort_buffer_size 的设置。在这两个Buffer 设置的时候，最需要注意的就是不要忘记是每个
Thread 都会创建自己独立的Buffer，而不是整个系统共享的Buffer，不要因为设置过大而造成系统内存
不足。
10.5 小