本文为《Redis运维经验一二三》的姐妹篇，主要阐述使用redis开发标准化的规范，简化运维成本，提升工作效率。

你首先需要了解的东西

在使用redis之前你首先要了解：①redis是单线程作业，所谓的并发是通过epoll实现的并发活跃连接；②redis与memcached相对优点明显，缺点不明显，因此还在犹豫的同学放心的选择redis吧；③在实际生产中因为客户端效率以及各节点通讯开销，redis几乎不可能达到官网上写的10w的qps；④在实际的使用过程中，redis最大的瓶颈一般是CPU，由于它是单线程作业所以很容易跑满一个逻辑CPU，可以使用redis代理或者是分布式方案来提升redis的CPU使用率。

今天MySQL过载保护又立功干掉了一堆堵塞的SQL，到后台一看，发现了一大堆熟悉的”Waiting for table metadata lock”。之前刚好写过一篇关于metadata lock相关问题处理的文章，有兴趣的同学自己点进去看看。

关于metadata lock说明

在正式说明问题之前我们先来回顾下MySQL的metadata lock。metadata lock是MySQL在5.5.3版本以后引入的，在那之前MySQL的元数据锁的颗粒度是statement级别，在5.5.3版本引入metadata lock以后变成是transaction级别，改造的目的是为了解决著名的BUG989，也就是说可能出现slave同步复制失败。简单来说就是因为MySQL二进制日志是顺序写，如果在事务未提交前执行了DDL，则二进制日志先记录了DDL再记录DML的语句就可能导致slave同步应用失败。具体参见MySQL BUG989。关于metadata lock的说明可以看这里。

今天在处理慢查询平台的slow query时发现今天排在第一个的居然是一个简单的SQL，运行了4166次，最长耗时610秒，长期处于“Waiting for table flush”状态。

结果说在前面

排查一番后果然不出意外：一个慢查询堵塞数据库备份进程，备份进程堵塞了后续的查询SQL，最后导致后续的大面积SQL等待。

那么问题来了

1. 一个慢查询是怎么堵塞mysqldump进程
2. mysqldump是怎么堵塞后续的查询请求

本文主要介绍一些列简单的redis运维经验，帮助大家对redis的运维有个初步的了解，条目不分先后，后续还会持续添加。本文档预期读者为：redis使用者。

计算延迟时间

这里的延迟时间指的是指令请求的等待时间（单位为毫秒），包含网络延时以及单线程的命令等待，命令很简单：

1

redis-cli --latency- history --i <interval> -h `host` -p `port`

这里我们来测试下
场景1：本地空闲的redis实例，延时几乎为0

1
2
3
4
5

[root@lxj tmp]# /usr/local/redis/bin/redis-cli --latency-history -i 5 -h 10.59.xx.xx -p 6379
min: 0, max: 1, avg: 0.00 (455 samples) -- 5.01 seconds range
min: 0, max: 0, avg: 0.00 (423 samples) -- 5.00 seconds range
min: 0, max: 1, avg: 0.00 (455 samples) -- 5.01 seconds range
min: 0, max: 1, avg: 0.28 (455 samples) -- 5.01 seconds range

起因

前几天有位同事问我MySQL要怎么返回一张表的几行随机数据。这个问题其实网上随便一搜答案一大把，但是效果都不太理想，当你要获取随机几行（不是1行）时，得到的数据不是全随机的，而是随机区域。那么有没有办法真正的返回全随机的数据呢？

分析

一般来说获取随机数据，我们第一时间想到的应该是rand()函数。最直接的是order by rand() limit n，这完全能够符合我们的需求，但是效率之地令人发指。我们来看看手册里面是怎么对这个函数描述的：

Returns a random floating-point value v in the range 0 <= v < 1.0.
…
You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.
…
RAND() is not meant to be a perfect random generator. It is a fast way to generate random numbers on demand that is portable between platforms for the same MySQL version.

简单说rand()能够随机返回一个从0~1之间的值，直接使用order by rand() limit m可以在表里获取随机样本，但是也会导致order by重复计算，因此效率非常低下，我们看一个例子。

记得在很久以前我在公司内部做过一次MySQL优化分享里面说到一个使用延迟关联实现排序分页类型SQL的优化，这个案例在《高性能MySQL》的第二版还是第三版也有提及。

延迟关联：通过覆盖索引返回所需数据行的主键，再通过主键获取所需数据。

这里我们来看一个使用延迟关联优化排序分页SQL的案例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql> select sql_no_cache * from t_user_log where appname = '发号中心' order by logintime limit 1000000,1;
+---------+-----------+--------------------------+--------------+-----------------+------------------------------------+------------+-----------+-----------+
| id      | uid       | username                 | appname      | loginip         | loginlocation                      | logintime  | logintype | useragent |
+---------+-----------+--------------------------+--------------+-----------------+------------------------------------+------------+-----------+-----------+
| 2246895 | 114790028 | xxxxxxxxxxxxxx@17173.com | 发号中心     | 220.172.xxx.xxx | 贵州省黔南州都匀市 电信            | 1407055334 |         1 |           |
+---------+-----------+--------------------------+--------------+-----------------+------------------------------------+------------+-----------+-----------+
1 row in set (2.05 sec)
mysql> select sql_no_cache a.* from t_user_log a inner join (select id from t_user_log where appname = '发号中心' order by logintime limit 1000000,1) b on a.id = b.id; 
+---------+-----------+--------------------------+--------------+-----------------+------------------------------------+------------+-----------+-----------+
| id      | uid       | username                 | appname      | loginip         | loginlocation                      | logintime  | logintype | useragent |
+---------+-----------+--------------------------+--------------+-----------------+------------------------------------+------------+-----------+-----------+
| 2246895 | 114790028 | xxxxxxxxxxxxxx@17173.com | 发号中心     | 220.172.xxx.xxx | 贵州省黔南州都匀市 电信            | 1407055334 |         1 |           |
+---------+-----------+--------------------------+--------------+-----------------+------------------------------------+------------+-----------+-----------+
1 row in set (0.87 sec)

确实使用延迟关联以后SQL效率提升了约135%左右。

那么问题来了

1. 延迟关联的用法在InnoDB跟MyISAM两种引擎上是否有区别？
2. 延迟关联是否优化排序分页SQL的最优方法？

MySQL的连接查询包括内连接（inner join）、外连接（left/right join，下文一律以left join代替）、交叉连接（在MySQL中等价于内连接，但是在标准SQL中是不等价的）、全连接（MySQL不支持full join，但是可以通过union构造），本文主要讲解一般我们在写SQL中最常用的：内连接以及外连接两种连接查询，通过一些案例来说明我们在使用关联查询中需要注意什么问题，要怎么做才能做到最优查询。

重点说在前面

1. MySQL对表连接至今只支持nested loop join，而不支持hash join，这个是MySQL不建议执行复杂关联查询的根源（MariaDB已经实现hash join）

   通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。

2. 优化的目标是尽可能减少关联查询中nested loop的循环次数，也就是说尽量缩小驱动表的结果集
3. inner join驱动顺序由优化器自己指定，如果优化器选择有误可以使用straight_join自己指定驱动顺序以达到优化的目的
4. left join驱动顺序是固定的，left join左边的表为驱动表，右边为匹配表，RIGHT JOIN则刚好相反

   其实这也不是绝对的，当left join跟inner join等价的时候，MySQL优化器就会自己选择驱动表，需求请见下文“容易混淆的地方”部分

5. 存在group by或者order by子句的关联查询中，如果引用的字段是驱动表的字段那么分组或者排序是可以使用索引的，如果引用的是其他匹配表的字段，那边分组或者排序动作则无法使用索引
6. 在MySQL5.6以前的版本关联子查询可以用关联查询来替代优化（MySQL5.6或者更新的版本或者MariaDB等就无需替代，优化器会自动帮你优化处理）
7. 在MySQL5.6以及以后的版本中，缩小匹配表的结果集也能达到优化的效果

背景

相信很多用discuz搭建论坛服务的同学都有这个体验，整个数据库表都是MyISAM引擎，一旦帖子数量到一定量级的时候神马操作都是巨慢无比（MyISAM的各种缺点这里暂且不提）。以此为背景，我们打算对discuz论坛进行改版，简单说来就是彻底抛弃原有的归档功能直接分100张表，按照帖子ID取模分发数据，然后把新表都改成InnoDB引擎。

以这件事为背景，对这次升级过程中数据库的操作做一个记录以及简单分析。抛开前期准备，后期处理等等步骤，这里我们就说下中间的重要环节。

1.在帖子表里面新增一个字段，且加一条索引
2.更新新增字段的值为：mod(tid,100)+1
3.创建一个tempfs分区（需要比帖子表.MYD文件大）
4.使用select into oufile并发导出数据
5.增大innodb_buffer_pool_size,max_allowed_packet，缩小key_buffer_size
6.使用load data infile并发导入数据

中间的数据分表的核心步骤大概就是这么几步，下来我们逐一来讲。

首先我们来说下in()这种方式的查询。在《高性能MySQL》里面提及用in这种方式可以有效的替代一定的range查询，提升查询效率，因为在一条索引里面，range字段后面的部分是不生效的。使用in这种方式其实MySQL优化器是转化成了n*m种组合方式来进行查询，最终将返回值合并，有点类似union但是更高效。同时它存在这一些问题：

老版本的MySQL在IN()组合条件过多的时候会发生很多问题。查询优化可能需要花很多时间，并消耗大量内存。新版本MySQL在组合数超过一定的数量就不进行计划评估了，这可能导致MySQL不能很好的利用索引。

这里的“一定数量”在MySQL5.6.5以及以后的版本中是由eq_range_index_dive_limit这个参数控制（感谢@叶金荣同学的指点）。默认设置是10，一直到5.7以后的版本默认会修改成200，当然我们是可以手动设置的。我们看下5.6手册中的说明：

The eq_range_index_dive_limit system variable enables you to configure the number of values at which the optimizer switches from one row estimation strategy to the other. To disable use of statistics and always use index dives, set eq_range_index_dive_limit to 0. To permit use of index dives for comparisons of up to N equality ranges, set eq_range_index_dive_limit to N + 1.
eq_range_index_dive_limit is available as of MySQL 5.6.5. Before 5.6.5, the optimizer uses index dives, which is equivalent to eq_range_index_dive_limit=0.

也就是说：

1. eq_range_index_dive_limit = 0 只能使用index dive
2. 0 < eq_range_index_dive_limit <= N 使用index statistics
3. eq_range_index_dive_limit > N 只能使用index dive

index dive与index statistics是MySQL优化器对开销代价的估算方法，前者统计速度慢但是能得到精准的值，后者统计速度快但是数据未必精准。

the optimizer can estimate the row count for each range using dives into the index or index statistics.

在MySQL5.7版本中将默认值从10修改成200目的是为了尽可能的保证范围等值运算（IN()）执行计划尽量精准，因为IN()list的数量很多时候都是超过10的。

说在前面

今天文章的主题有两个：

1. range查询与索引使用
2. eq_range_index_dive_limit的说明

今天上班就发现一起数据库例行恢复作业失败，失败提示为：“数据库恢复失败”，也就是说是在执行mysql < dumpfile的时候失败了。

1
2

[ root@localhost ]#/usr/local/mysql56/bin/mysql -S ./mysql.sock bi_monitor < /home/mysql/backup/2014-09-21_bi_monitor_3346.sql 
ERROR 2006 (HY000) at line 294: MySQL server has gone away

MySQL server has gone away是指客户端与MySQL服务端之间的连接段开，一般来说原因有这么几个：

1. MySQL crash：MySQL Server宕机
2. connection timeout：客户端连接超时
3. kill connection：连接进程被杀，与connection timeout差不多，区别在于一个是MySQL Server主动，一个是被动
4. max_allowed_packet too small：返回结果集大于max_allowed_packet限制