学习笔记：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 rowmysql> 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 --helpmysqlslap: [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

BenchmarkAverage number of seconds to run all queries: 0.016 secondsMinimum number of seconds to run all queries: 0.008 secondsMaximum number of seconds to run all queries: 0.093 secondsNumber of clients running queries: 50Average 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

BenchmarkAverage number of seconds to run all queries: 0.062 secondsMinimum number of seconds to run all queries: 0.017 secondsMaximum number of seconds to run all queries: 0.185 secondsNumber of clients running queries: 5Average 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=";"

BenchmarkAverage number of seconds to run all queries: 0.058 secondsMinimum number of seconds to run all queries: 0.047 secondsMaximum number of seconds to run all queries: 0.067 secondsNumber of clients running queries: 5Average number of queries per client: 2

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