学习笔记：MySQL基准测试之BENCHMARK()和mysqlslap

     BENCHMARK()函数（ https://dev.mysql.com/doc/refman/5.7/en/select-benchmarking.html ）是MySQL的一个内置函数，用于测试函数或者表达式的执行速度。用法很简单，接受两个参数BENCHMARK(loop_count, expression)：即执行expression一共loop_count次。BENCHMARK()的返回值总是0，但是mysql客户端会打印一行来输出语句的大概执行时间。

mysql> SELECT BENCHMARK(1000000, AES_ENCRYPT('hello','goodbye'));
+----------------------------------------------------+
| BENCHMARK(1000000, AES_ENCRYPT('hello','goodbye')) |
+----------------------------------------------------+
|                                                  0 |
+----------------------------------------------------+
1 row in set (0.28 sec)

     BENCHMARK()是一个用来找出导致查询出现性能问题的方法或表达式的优良公举。参考https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_benchmark，以下是其使用说明。

• BENCHMARK()函数返回的是客户端执行时间，而不是CPU运行时间，建议多运行几次BENCHMARK()然后再解读结果。

mysql> SELECT BENCHMARK(1000000, (SELECT COUNT(*) FROM information_schema.`TABLES` t));
+--------------------------------------------------------------------------+
| BENCHMARK(1000000, (SELECT COUNT(*) FROM information_schema.`TABLES` t)) |
+--------------------------------------------------------------------------+
|                                                                        0 |
+--------------------------------------------------------------------------+
1 row in set (0.05 sec)

mysql> SELECT BENCHMARK(1000000, 'SELECT COUNT(*) FROM information_schema.`TABLES` t');
+--------------------------------------------------------------------------+
| BENCHMARK(1000000, 'SELECT COUNT(*) FROM information_schema.`TABLES` t') |
+--------------------------------------------------------------------------+
|                                                                        0 |
+--------------------------------------------------------------------------+
1 row in set (0.01 sec)

• BENCHMARK()函数只接受返回值为单行单列（single column single row）的常量表达式。

mysql> SELECT BENCHMARK(1000000, (SELECT t.TABLE_NAME FROM information_schema.`TABLES` t));
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> SELECT BENCHMARK(1000000, (SELECT t.TABLE_NAME, t.TABLE_SCHEMA FROM information_schema.`TABLES` t));
ERROR 1241 (21000): Operand should contain 1 column(s)

• 执行SELECT expr 语句N 次和执行SELECT BENCHMARK(N,expr)的消耗并不是一样的，二者有截然不同的执行情况所以不能指望它们花费一样的时间量：前者是计算 N 次包含解析器、优化器、锁以及执行查询在内的时间消耗；后者只计算N 次执行查询时间，其他部分则只计算一次。
  

     相较简单的BENCHMARK()函数，mysqlslap应该才算得上是真正意义上的基准测试工具。“mysqlslap”是MySQL自带的命令行小公举之一（https://dev.mysql.com/doc/refman/5.7/en/mysqlslap.html），设计用于模仿一台MySQL服务器的客户端负载并以此来测试计时。它运行的时候就像多个客户端在并发访问。

     调用mysqlslap如下（可以在终端键入mysqlslap --help查看详细参数信息）：

shell> mysqlslap [options]

mysqlslap --help
mysqlslap: [ERROR] unknown variable 'default-character-set=utf8'
vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysql]
default-character-set=utf8

     我本地的Ubuntu Linux出现了字符报错，去配置文件看了一下，默认字符集设置放到了[client]组下面了，把它移到[mysql]组下面就OK了。

     一些options，比如--create和--query接受一个包含SQL语句的字符串或者文件。如果指定的是文件，默认情况下必须一行一句SQL（即隐式分隔符是换行符）。不过可以通过--delimiter选项另外指定一个分隔符，这样一行就可以放多句SQL了。但是需要注意的是，文件里面不能包含注释语句，mysqlslap程序无法识别。

     mysqlslap运行分为三个步骤：

①创建库、表以及视选项加载存储程序和测试用数据。这一步在单个客户端连接下。

②进行负载测试。这一步可以在多客户端连接下。

③清场（断开连接、如果指定了则删除测试用表）。这一步在单个客户端连接下。

     进行一些测试实例吧。

• 传入自己的DDL和DQL，50并发，每个客户端执行200次查询。

mysqlslap --user=root --password=520 \
--delimiter=";" \
--create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" \
--query="SELECT * FROM a" --concurrency=50 --iterations=200

Benchmark
	Average number of seconds to run all queries: 0.016 seconds
	Minimum number of seconds to run all queries: 0.008 seconds
	Maximum number of seconds to run all queries: 0.093 seconds
	Number of clients running queries: 50
	Average number of queries per client: 1

• 让mysqlslap程序于一张两个INT字段三个VARCHAR字段的表中自己创建查询语句，5并发，每个客户端执行20次查询，不创建表以及插入数据（即使用之前一次的测试用例）。

mysqlslap --user=root --password=520 \
  --concurrency=5 --iterations=20 \
  --number-int-cols=2 --number-char-cols=3 \
  --auto-generate-sql

Benchmark
	Average number of seconds to run all queries: 0.062 seconds
	Minimum number of seconds to run all queries: 0.017 seconds
	Maximum number of seconds to run all queries: 0.185 seconds
	Number of clients running queries: 5
	Average number of queries per client: 0

• DDL和DQL来自文件（先造两个文件吧），SQL语句分隔符指定为“;”。

echo -e 'DROP DATABASE IF EXISTS `benchmarker`;
CREATE DATABASE `benchmarker`;
CREATE TABLE `benchmarker`.`tables` LIKE `information_schema`.`TABLES`;
INSERT INTO `benchmarker`.`tables` SELECT * FROM `information_schema`.`TABLES`;
CREATE TABLE `benchmarker`.`columns` LIKE `information_schema`.`COLUMNS`;
INSERT INTO `benchmarker`.`columns` SELECT * FROM `information_schema`.`COLUMNS`;' > create.sql

echo -e 'SELECT * FROM `benchmarker`.`tables`;
SELECT * FROM `benchmarker`.`columns`;' > query.sql

mysqlslap --user=root --password=520 \
  --concurrency=5 \
  --iterations=5 --query=query.sql --create=create.sql \
  --delimiter=";"

Benchmark
	Average number of seconds to run all queries: 0.058 seconds
	Minimum number of seconds to run all queries: 0.047 seconds
	Maximum number of seconds to run all queries: 0.067 seconds
	Number of clients running queries: 5
	Average number of queries per client: 2

     测试打印结果会以“Benchmark”行开始，分别给出了运行所有查询的平均时间以及相应的极小值和极大值。执行查询的客户端数是由选项--concurrency指定的，最后是每个客户端的平均执行查询次数（执行多少个查询）。可以看出，mysqlslap是一款小巧实用的并发查询性能测试工具，适合于表设计和查询调优的场景。