RandomQueryGenerator 介绍

来源：互联网发布：上海家博会数据编辑：程序博客网时间：2024/05/20 11:20

最近用 RandomQueryGenerator 来生成一定规律的随机 SQL 进行测试。本文主要记录下对RandomQueryGenerator 的学习。

首先，简介：

RandomQueryGenerator 是 MySQL 使用的 QA 测试工具，它是一个开源的 PERL程序。它可以按照一定的规则自动生成表以及数据，根据指定的语法文件随机生成 SQL 并以一定的并发压力发给指定的一个或几个 MySQL执行，最后比较各个 MySQL 返回的结果集，如果不一致的话会报错。

资料获取：

https://github.com/RQG/RQG-Documentation/wiki/Category:RandomQueryGenerator

执行：

perl gentest.pl    --gendata=conf/example.zz    --grammar=conf/example.yy  --dsn=dbi:mysql:host=127.0.0.1:port=9306:user=root:database=test
--dsn=dbi:mysql:host=127.0.0.1:port=9309:user=root:database=test
--queries=1000 --threads=10

原理：

RandomQueryGenerator 按照指定的zz文件在每个MySQL上生成表并插入数据，生成表和插入数据的代码在
lib/GenTest/App/Gendata.pm.

RandomQueryGenerator会根据用户命令启动1个或多个工作线程，每个工作线程按照指定的语法描述文件（yy文件），生成指定数目的sql语句。每生成一条sql语句，将它按顺序发给后端的MySQL 实例，并比较每一个实例返回的执行结果。具体代码入口： lib/GenTest/App/GenTest.pm (subworkerProcess 函数）。

注意：

如果 threads > 1的话并且有2个或以上的 dsn时， RandomQueryGenerator可能会出现结果集不匹配，即同一条语句在不同的 MySQL 上执行结果不一样（因为有并发）。

数据与语法文件：

总的来说写法非常的灵活和随意，不像 lex 和bison 那样要求唯一性。因为它本身就是要随机生成 sql 语句。
如果你想增大一条规则的出现概率，那么就简单的多重复几次就好了，具体的可以在下面的示例中看到。

示例数据生成文件：

-----------------------
$tables = {
       rows => [10, 100, 1000],
       partitions => [ undef , 'KEY (pk) PARTITIONS 2' ],
       engines => [ 'MyISAM', 'InnoDB' ]
};

$fields = {
       types => [ 'int', 'bigint', 'varchar(5)', 'varchar(100)','double(20,4)' ],
       indexes => [undef, 'key' ],
       null => [undef, 'not null'],
       default => [undef, 'default null'],
       sign => [undef, 'unsigned'],
       charsets => ['utf8']
};

$data = {
       numbers => [ 'digit', 'double(20,4)', 'null', undef ],
       strings => [ 'letter', 'english', 'varchar(5)', 'varchar(100)'],
}
-----------------------

实例语法文件：

------------------------
query:
show |select|select|select|select|select|select
# | transaction | update | delete
| set_var;

#
# define show stmt
#

show:
SHOW FULL TABLES WHERE Table_type !='VIEW'
|
SHOW TABLE STATUS
|
SHOW COLUMNS FROM _table
|
SHOW VARIABLES LIKE 'lower_case_%'
|
SHOW DATABASES
|
SHOW CREATE TABLE _table
|
SHOW INDEX FROM _table;
#
# define set var stmt
#

char_set_values:
BINARY
|
UTF8;

set_var:
SET @@character_set_database =char_set_values
|
SET NAMES utf8
|
SET SESSION TRANSACTION ISOLATION LEVEL READCOMMITTED;

#
# define select
#

select:
simple_select
|
SELECT COUNT(1) FROM (simple_select) T1
|
SELECT * FROM (simple_select) T1 opt_groupbyopt_orderby opt_limit
|
select_for_union;

union_or_all:
UNION
|
UNION ALL;
simple_select:
select_from_big | select_from_big |select_from_normal | select_from_normal| select_from_global
| select_big_join_global |select_big_join_global | select_normal_join_global |select_normal_join_global;

select_big_join_global:
SELECT field_list_join FROM big_table T1 LEFTOUTER JOIN global_table_or_subselect T2 on_exprwhere_and_opts_join;

select_normal_join_global:
SELECT field_list_join FROM normal_table T1 LEFTOUTER JOIN global_table_or_subselect T2 on_exprwhere_and_opts_join;

global_table_or_subselect:
global_table | global_table | global_table |(select_from_global);

select_from_global:
SELECT opt_distinct field_list FROM global_tableT1 where_and_opts;

select_from_normal:
SELECT opt_distinct field_list From normal_tableT1 where_and_opts;

select_from_big:
SELECT opt_distinct field_list From big_table T1where_and_opts;

select_for_union:
(SELECT * from_one_table) union_or_all (SELECT *from_one_table)
|
(SELECT * from_join_table) union_or_all (SELECT* from_join_table);

from_one_table:
From _table T1 where_and_opts;

from_join_table:
From distribution_table T1 LEFT OUTER JOINglobal_table_or_subselect T2 on_expr where_and_opts_join;

where_and_opts:
WHERE condition_list opt_groupby opt_orderbyopt_limit;

where_and_opts_join:
WHERE condition_list_join opt_groupby_joinopt_orderby_join opt_limit;

on_expr:
ON T1.int_field_name = T2.int_field_name
|
ON T1.char_field_name =T2.char_field_name;

#
# define field list
#

opt_alias:
|
AS _quid;

opt_distinct:
| | | | | | | | | | | DISTINCT;

field_list:
field_list , T1._field
| field_list , T1._field
| field_list , T1._field
|
field_list , func_or_aggr
|
T1._field
|
func_or_aggr;

field_list_join:
field_list_join , field_join
|
field_join;

field_join:
T1._field opt_alias | T2._field opt_alias
| T1._field opt_alias | T2._fieldopt_alias
| func_or_aggr_join opt_alias;

func_or_aggr:
MIN(T1.int_field_name) | MAX(T1.int_field_name)| CONCAT(T1.char_field_name,T1.char_field_name) |AVG(T1.int_field_name)
| COUNT(T1._field) |IF(T1.int_field_name=_digit, (T1.int_field_name +T1.int_field_name)/1024/1024,0)
|
IF(T1._field[invariant] IS NULL,IF(T1.int_field_name=_digit, (T1.int_field_name +T1.int_field_name)/1024/1024,0), T1._field[invariant]);

func_or_aggr_join:
MIN(T1.int_field_name) | MAX(T2.int_field_name)| CONCAT(T1.char_field_name,T2.char_field_name)
| AVG(T1.int_field_name) |COUNT(T2.int_field_name)
| IF(T1.int_field_name=_digit,(T1.int_field_name+T2.int_field_name)/1024/1024,0);

#
# define limit, order by, group by
#

opt_limit:
| | | LIMIT _digit;

opt_desc:
| | | DESC;

field_group: T1._field opt_desc;

field_list_group:
field_list_group , field_group
|
field_group;

opt_orderby:
| |
ORDER BY field_list_group;

opt_groupby:
| |
GROUP BY field_list_group;

field_group_join: T1._field opt_desc | T2._field opt_desc;

field_list_group_join:
field_list_group_join , field_group_join
|
field_group_join;

opt_orderby_join:
|
ORDER BY field_list_group_join;

opt_groupby_join:
|
GROUP BY field_list_group_join;

#
# define the condition
#

in_digit_list:
in_digit_list
|
_digit;

condition_list:
condition_list and_or condition |condition;

and_or: AND | AND| AND| AND| OR;

condition:
T1.int_field_name < digit | T1.int_field_name= _digit
| T1.int_field_name = _digit | T1.int_field_name= _digit | T1.int_field_name = _digit | T1.int_field_name =_digit
| T1.char_field_name = char_value |T1.char_field_name = char_value
| T1.int_field_name IN (in_digit_list);

condition_list_join:
condition_list_join and_or condition_join |condition_join;

condition_join:
T1.int_field_name < _digit |T2.int_field_name < _digit | T1.int_field_name = _digit |T2.int_field_name = _digit
| T1.int_field_name = _digit | T2.int_field_name= _digit
| T1.int_field_name = _digit | T2.int_field_name= _digit
| T1.char_field_name = char_value |T2.char_field_name = char_value
| T1.char_field_name = char_value |T2.char_field_name = char_value
| T1.int_field_name IN (in_digit_list);

#
# define table, field, and value
#

_table:
big_table | normal_table | global_table;

distribution_table:
big_table | normal_table;

big_table:
table1000_innodb_key_pk_parts_2_int_autoinc |table1000_innodb_int_autoinc;

normal_table:
table100_innodb_key_pk_parts_2_int_autoinc |table100_innodb_int_autoinc;

global_table:
table10_innodb_int_autoinc |table10_innodb_key_pk_parts_2_int_autoinc;

_digit:
    1 | 2 | 3 |4 | 5 | 6 | 7 | 8 | 9 | _tinyint_unsigned ;

char_value:
    _char(2)|_char(2) | _char(2)| _char(3) | _char(4);

int_field_name:
    `pk` |`col_int_key` | `col_int` |
    `col_bigint`| `col_bigint_key` |
   `col_int_not_null` | `col_int_not_null_key` ;

char_field_name:
       `col_char_10` | `col_char_10_key` | `col_text_not_null` |`col_text_not_null_key` |
       `col_text_key` | `col_text` | `col_char_1int_field_name:
    `pk` |`col_int_key` | `col_int` |
    `col_bigint`| `col_bigint_key` |
   `col_int_not_null` | `col_int_not_null_key` ;

char_field_name:
       `col_char_10` | `col_char_10_key` | `col_text_not_null` |`col_text_not_null_key` |
       `col_text_key` | `col_text` | `col_char_10_not_null_key` |`col_char_10_not_null` |
       `col_char_1024` | `col_char_1024_key` | `col_char_1024_not_null` |`col_char_1024_not_null_key` ;0_not_null_key` |`col_char_10_not_null` |
       `col_char_1024` | `col_char_1024_key` | `col_char_1024_not_null` |`col_char_1024_not_null_key` ;

int_field_name:
    `pk` |`col_int_key` | `col_int` |
    `col_bigint`| `col_bigint_key` |
   `col_int_not_null` | `col_int_not_null_key` ;

char_field_name:
       `col_char_10` | `col_char_10_key` | `col_text_not_null` |`col_text_not_null_key` |
       `col_text_key` | `col_text` | `col_char_10_not_null_key` |`col_char_10_not_null` |
       `col_char_1024` | `col_char_1024_key` | `col_char_1024_not_null` |`col_char_1024_not_null_key` ;

--------------------------------------------------

转载请注明转自高孝鑫的博客

0 0

RandomQueryGenerator&nbsp;介绍

RandomQueryGenerator 介绍