【小白福利—初级DBA入门必看】MySQL常用工具介绍（十）——客户端工具MySQL_slap

知识可以迟到，但是却始终不会缺席哒~Amy今天为大家介绍MySQLslap的相关内容，希望能够给大家带来帮助哦~

1 . 简介

mysqlslap是一个诊断程序，旨在模拟MySQL服务器的客户端负载并报告每个阶段的时间。它等效为多个正在访问服务器的客户端。

shell> mysqlslap [options] #[mysqlslap]和[client]获取默认选项

某些选项，如–create、–query可赋值sql语句集的字符串或包含语句的文件（不能有注释）。

mysqlslap原理：

创立一个管理会话；

对于–iterations的每轮测试，使用管理会话构建环境，然后新建–concurrency个查询会话执行并行查询并关闭会话。

管理会话完成清理过程（如删除库等）并退出。

2 . 选项介绍与部分选项举例说明

注意：这里的默认值，不是使用“程序名 --no-defaults --help”打印的输出，而是指未指定时内部初始值，该值可被自身选项显示指定，也可能会受其他互斥选项、相关选项更改。这里只写出默认启用的布尔型选项，以及有内部值的其他选项。有内部值的选项可以不显示给出。

2.1 所有客户端共有选项

2.1.1 影响选项文件读取的选项

2.1.2 帮助与版本

2.1.3 连接的建立

连接方式参数说明：优先级–protocol＞–pipe＞-h；

linux两种连接方式：若未指定–host和-h，或指定为localhost，或指定为空（–host=或–host=’’），则使用unix套接字；否则使用tcp/ip。

windows三种连接方式：若未指定–host和-h，或指定为localhost，且服务端开启了共享内存，则使用共享内存；若指定为.，或tcp禁用且socket未指定或主机指定为空（–host=），则使用命名管道；否则tcp。

连接方式举例

全平台使用tcp/ip：

mysql --protocol=tcp [-h127.0.0.1] [–port=3306]

mysql -h127.0.0.1 [–port=3306]

unix使用socket：

mysql [–host=localhost] [–socket=/tmp/mysql.sock]

windows使用命名管道：需在服务端开启命名管道支持

mysql --protocol=pipe

mysql --pipe

mysql --host=.

windows使用共享内存：未知，存在问题。理论上应当在服务上开启共享内存后使用

mysql [–host=localhost] --shared-memory-base-name=MYSQL，但是实际上使用的tcp，或者

mysql --protocol=memory --shared-memory-base-name=MYSQL，但是会报错ERROR 2046 (HY000): Can’t open shared memory; cannot send request event to server (5)；

2.1.4 字符集

2.1.5 调试日志

2.2 mysqlslap特定选项

3 . 常见用法举例：省略连接选项

3.1 例子

mysqlslap --concurrency=2 --iterations=3 --delimiter=";" --query=“SELECT * FROM a” --create=“CREATE TABLE a (b int);INSERT INTO a VALUES (23)”

mysqlslap --concurrency=2 --iterations=3 --delimiter=";" --query=query.sql --create=create.sql

mysqlslap --concurrency=2 --iterations=3 --auto-generate-sql --number-int-cols=2 --number-char-cols=3

3.2 非自动参数全解

执行如下命令：两个线程共执行不超过17个语句，故每个线程执行8句。每个线程每3句提交一次。

mysqlslap --concurrency=2 --iterations=3 --number-of-queries=17 --commit=3 --detach=0 --sql-mode="" --create-schema=ztdslap --engine=myisam --pre-system="echo 1" --pre-query="select 2" --post-query="select 3" --post-system="echo 4" --delimiter=";" --query="SELECT * FROM a;select 0;" --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)"
mysqlslap: [Warning] unknown variable 'loose-default-character-set=utf8'
1
4
1
4
1
4
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.000 seconds
        Minimum number of seconds to run all queries: 0.000 seconds
        Maximum number of seconds to run all queries: 0.000 seconds
        Number of clients running queries: 2
        Average number of queries per client: 8

从一般日志可得到：可见语句执行逻辑符合简介中的原理及参数描述

2019-05-06T15:41:53.767498Z    20734 Connect    root@localhost on  using TCP/IP
2019-05-06T15:41:53.767590Z    20734 Query    SET sql_mode = ``
2019-05-06T15:41:53.767654Z    20734 Query    DROP SCHEMA IF EXISTS `ztdslap`
2019-05-06T15:41:53.770852Z    20734 Query    CREATE SCHEMA `ztdslap`
2019-05-06T15:41:53.772732Z    20734 Init DB    ztdslap
2019-05-06T15:41:53.772802Z    20734 Query    set default_storage_engine=`myisam`
2019-05-06T15:41:53.772898Z    20734 Query    CREATE TABLE a (b int)
2019-05-06T15:41:53.776484Z    20734 Query    INSERT INTO a VALUES (23)
2019-05-06T15:41:53.777824Z    20734 Query    SET AUTOCOMMIT=0
2019-05-06T15:41:53.824804Z    20734 Query    select 2
2019-05-06T15:41:53.828195Z    20735 Connect    root@localhost on ztdslap using TCP/IP
2019-05-06T15:41:53.828303Z    20735 Query    SET AUTOCOMMIT=0
2019-05-06T15:41:53.828378Z    20735 Query    SELECT * FROM a
2019-05-06T15:41:53.828640Z    20736 Connect    root@localhost on ztdslap using TCP/IP
2019-05-06T15:41:53.828938Z    20735 Query    select 0
2019-05-06T15:41:53.828969Z    20736 Query    SET AUTOCOMMIT=0
2019-05-06T15:41:53.829018Z    20735 Query    SELECT * FROM a
2019-05-06T15:41:53.829058Z    20736 Query    SELECT * FROM a
2019-05-06T15:41:53.829096Z    20735 Query    COMMIT
2019-05-06T15:41:53.829142Z    20735 Query    select 0
2019-05-06T15:41:53.829194Z    20735 Query    SELECT * FROM a
2019-05-06T15:41:53.829266Z    20735 Query    select 0
2019-05-06T15:41:53.829317Z    20735 Query    COMMIT
2019-05-06T15:41:53.829363Z    20735 Query    SELECT * FROM a
2019-05-06T15:41:53.829440Z    20735 Query    select 0
2019-05-06T15:41:53.829493Z    20735 Query    COMMIT
2019-05-06T15:41:53.829542Z    20736 Query    select 0
2019-05-06T15:41:53.829543Z    20735 Quit    
2019-05-06T15:41:53.829633Z    20736 Query    SELECT * FROM a
2019-05-06T15:41:53.829720Z    20736 Query    COMMIT
2019-05-06T15:41:53.829772Z    20736 Query    select 0
2019-05-06T15:41:53.829835Z    20736 Query    SELECT * FROM a
2019-05-06T15:41:53.829909Z    20736 Query    select 0
2019-05-06T15:41:53.829962Z    20736 Query    COMMIT
2019-05-06T15:41:53.830009Z    20736 Query    SELECT * FROM a
2019-05-06T15:41:53.830159Z    20736 Query    select 0
2019-05-06T15:41:53.830288Z    20736 Query    COMMIT
2019-05-06T15:41:53.830377Z    20736 Quit    
2019-05-06T15:41:53.830497Z    20734 Query    select 3
2019-05-06T15:41:53.873822Z    20734 Query    DROP SCHEMA IF EXISTS `ztdslap`
2019-05-06T15:41:53.878102Z    20734 Query    CREATE SCHEMA `ztdslap`
2019-05-06T15:41:53.880087Z    20734 Init DB    ztdslap
2019-05-06T15:41:53.880232Z    20734 Query    set default_storage_engine=`myisam`
2019-05-06T15:41:53.880332Z    20734 Query    CREATE TABLE a (b int)
2019-05-06T15:41:53.884636Z    20734 Query    INSERT INTO a VALUES (23)
2019-05-06T15:41:53.886295Z    20734 Query    SET AUTOCOMMIT=0
2019-05-06T15:41:53.928081Z    20734 Query    select 2
2019-05-06T15:41:53.931457Z    20737 Connect    root@localhost on ztdslap using TCP/IP
2019-05-06T15:41:53.931567Z    20737 Query    SET AUTOCOMMIT=0
2019-05-06T15:41:53.931645Z    20737 Query    SELECT * FROM a
2019-05-06T15:41:53.932219Z    20737 Query    select 0
2019-05-06T15:41:53.932332Z    20737 Query    SELECT * FROM a
2019-05-06T15:41:53.932446Z    20737 Query    COMMIT
2019-05-06T15:41:53.932529Z    20737 Query    select 0
2019-05-06T15:41:53.932621Z    20737 Query    SELECT * FROM a
2019-05-06T15:41:53.932727Z    20737 Query    select 0
2019-05-06T15:41:53.932790Z    20737 Query    COMMIT
2019-05-06T15:41:53.932841Z    20737 Query    SELECT * FROM a
2019-05-06T15:41:53.932912Z    20737 Query    select 0
2019-05-06T15:41:53.932965Z    20737 Query    COMMIT
2019-05-06T15:41:53.933007Z    20737 Quit    
2019-05-06T15:41:53.933127Z    20738 Connect    root@localhost on ztdslap using TCP/IP
2019-05-06T15:41:53.933235Z    20738 Query    SET AUTOCOMMIT=0
2019-05-06T15:41:53.933298Z    20738 Query    SELECT * FROM a
2019-05-06T15:41:53.933960Z    20738 Query    select 0
2019-05-06T15:41:53.934032Z    20738 Query    SELECT * FROM a
2019-05-06T15:41:53.934110Z    20738 Query    COMMIT
2019-05-06T15:41:53.934156Z    20738 Query    select 0
2019-05-06T15:41:53.934213Z    20738 Query    SELECT * FROM a
2019-05-06T15:41:53.934287Z    20738 Query    select 0
2019-05-06T15:41:53.934336Z    20738 Query    COMMIT
2019-05-06T15:41:53.934383Z    20738 Query    SELECT * FROM a
2019-05-06T15:41:53.934448Z    20738 Query    select 0
2019-05-06T15:41:53.934496Z    20738 Query    COMMIT
2019-05-06T15:41:53.934529Z    20738 Quit    
2019-05-06T15:41:53.934663Z    20734 Query    select 3
2019-05-06T15:41:53.975801Z    20734 Query    DROP SCHEMA IF EXISTS `ztdslap`
2019-05-06T15:41:53.979900Z    20734 Query    CREATE SCHEMA `ztdslap`
2019-05-06T15:41:53.981880Z    20734 Init DB    ztdslap
2019-05-06T15:41:53.982004Z    20734 Query    set default_storage_engine=`myisam`
2019-05-06T15:41:53.982100Z    20734 Query    CREATE TABLE a (b int)
2019-05-06T15:41:53.986457Z    20734 Query    INSERT INTO a VALUES (23)
2019-05-06T15:41:53.988017Z    20734 Query    SET AUTOCOMMIT=0
2019-05-06T15:41:54.028634Z    20734 Query    select 2
2019-05-06T15:41:54.031742Z    20739 Connect    root@localhost on ztdslap using TCP/IP
2019-05-06T15:41:54.031874Z    20739 Query    SET AUTOCOMMIT=0
2019-05-06T15:41:54.031966Z    20739 Query    SELECT * FROM a
2019-05-06T15:41:54.032543Z    20739 Query    select 0
2019-05-06T15:41:54.032625Z    20739 Query    SELECT * FROM a
2019-05-06T15:41:54.032718Z    20739 Query    COMMIT
2019-05-06T15:41:54.032779Z    20739 Query    select 0
2019-05-06T15:41:54.032858Z    20739 Query    SELECT * FROM a
2019-05-06T15:41:54.032946Z    20739 Query    select 0
2019-05-06T15:41:54.033009Z    20739 Query    COMMIT
2019-05-06T15:41:54.033062Z    20739 Query    SELECT * FROM a
2019-05-06T15:41:54.033142Z    20739 Query    select 0
2019-05-06T15:41:54.033204Z    20739 Query    COMMIT
2019-05-06T15:41:54.033244Z    20739 Quit    
2019-05-06T15:41:54.033846Z    20740 Connect    root@localhost on ztdslap using TCP/IP
2019-05-06T15:41:54.033944Z    20740 Query    SET AUTOCOMMIT=0
2019-05-06T15:41:54.034004Z    20740 Query    SELECT * FROM a
2019-05-06T15:41:54.034786Z    20740 Query    select 0
2019-05-06T15:41:54.034927Z    20740 Query    SELECT * FROM a
2019-05-06T15:41:54.035016Z    20740 Query    COMMIT
2019-05-06T15:41:54.035067Z    20740 Query    select 0
2019-05-06T15:41:54.035129Z    20740 Query    SELECT * FROM a
2019-05-06T15:41:54.035200Z    20740 Query    select 0
2019-05-06T15:41:54.035254Z    20740 Query    COMMIT
2019-05-06T15:41:54.035301Z    20740 Query    SELECT * FROM a
2019-05-06T15:41:54.035371Z    20740 Query    select 0
2019-05-06T15:41:54.035471Z    20740 Query    COMMIT
2019-05-06T15:41:54.035521Z    20740 Quit    
2019-05-06T15:41:54.035710Z    20734 Query    select 3
2019-05-06T15:41:54.081056Z    20734 Query    DROP SCHEMA IF EXISTS `ztdslap`
2019-05-06T15:41:54.085739Z    20734 Quit    

如果这篇文章对你有帮助的话别忘了收藏点赞哦~还可以查看更多[[数据库开发相关文章]](http://www.talkwithtrend.com/home/space.php?p=blog&;uid=1228931 "[数据库开发相关文章]")

作者：Amy—go