1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
|
####################################
# SETUP
####################################
CREATE DATABASE statements_digest;
USE statements_digest;
CREATE TABLE t1(a int);
CREATE TABLE t2(a int);
CREATE TABLE t3(a int, b int);
CREATE TABLE t4(a int, b int);
CREATE TABLE t5(a int, b int, c int);
CREATE TABLE t6(a int, b int, c int, d int);
CREATE TABLE t11 (c CHAR(4));
CREATE TABLE t12 (c CHAR(4));
SELECT * FROM performance_schema.setup_consumers;
NAME ENABLED
events_stages_current YES
events_stages_history YES
events_stages_history_long YES
events_statements_current NO
events_statements_history YES
events_statements_history_long YES
events_transactions_current YES
events_transactions_history YES
events_transactions_history_long YES
events_waits_current YES
events_waits_history YES
events_waits_history_long YES
global_instrumentation YES
thread_instrumentation YES
statements_digest YES
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
####################################
# EXECUTION
####################################
SELECT 1 FROM t1;
1
SELECT 1 FROM `t1`;
1
SELECT 1,2 FROM t1;
1 2
SELECT 1, 2, 3, 4 FROM t1;
1 2 3 4
SELECT 1 FROM t2;
1
SELECT 1,2 FROM t2;
1 2
SELECT 1, 2, 3, 4 FROM t2;
1 2 3 4
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
INSERT INTO t3 VALUES (1, 2);
INSERT INTO t4 VALUES (1, 2);
INSERT INTO t5 VALUES (1, 2, 3);
INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t1 VALUES (1), (2), (3), (4);
INSERT INTO t3 VALUES (1, 2), (3, 4), (5, 6);
INSERT INTO t5 VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);
INSERT INTO t1 VALUES (NULL);
INSERT INTO t3 VALUES (NULL,NULL);
INSERT INTO t3 VALUES (1,NULL);
INSERT INTO t3 VALUES (NULL,1);
INSERT INTO t6 VALUES (NULL, NULL, NULL, NULL);
INSERT INTO t6 VALUES (1, NULL, NULL, NULL);
INSERT INTO t6 VALUES (NULL, 2, NULL, NULL);
INSERT INTO t6 VALUES (1, 2, 3, NULL);
INSERT INTO t6 VALUES (1, 2, NULL, 4);
SELECT 1 + 1;
1 + 1
2
SELECT 1;
1
1
SELECT 1 /* This is an inline comment */ + 1;
1 /* This is an inline comment */ + 1
2
SELECT 1+
/*
this is a
multiple-line comment
*/
1;
1+
/*
this is a
multiple-line comment
*/
1
2
CREATE SCHEMA statements_digest_temp;
DROP SCHEMA statements_digest_temp;
CREATE DATABASE statements_digest_temp;
DROP DATABASE statements_digest_temp;
SELECT 1 FROM no_such_table;
ERROR 42S02: Table 'statements_digest.no_such_table' doesn't exist
CREATE TABLE dup_table (c char(4));
CREATE TABLE dup_table (c char(4));
ERROR 42S01: Table 'dup_table' already exists
DROP TABLE dup_table;
INSERT IGNORE INTO t11 VALUES("MySQL");
Warnings:
Warning 1265 Data truncated for column 'c' at row 1
PREPARE stmt FROM "SELECT * FROM t12";
EXECUTE stmt;
c
EXECUTE stmt;
c
DEALLOCATE PREPARE stmt;
CREATE PROCEDURE p1() BEGIN SELECT * FROM t12; END//
CALL p1();
c
CALL p1();
c
DROP PROCEDURE p1;
CREATE FUNCTION `func`(a INT, b INT) RETURNS int(11) RETURN a+b //
select func(3,4);
func(3,4)
7
select func(13,42);
func(13,42)
55
DROP FUNCTION func;
CREATE TRIGGER trg BEFORE INSERT ON t12 FOR EACH ROW SET @a:=1;
INSERT INTO t12 VALUES ("abc");
INSERT INTO t12 VALUES ("def");
DROP TRIGGER trg;
####################################
# QUERYING PS STATEMENT DIGEST
####################################
SELECT schema_name,digest_text, count_star FROM performance_schema.events_statements_summary_by_digest;
schema_name digest_text count_star
statements_digest TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 1
statements_digest SELECT ? FROM `t1` 2
statements_digest SELECT ?, ... FROM `t1` 2
statements_digest SELECT ? FROM `t2` 1
statements_digest SELECT ?, ... FROM `t2` 2
statements_digest INSERT INTO `t1` VALUES (?) 2
statements_digest INSERT INTO `t2` VALUES (?) 1
statements_digest INSERT INTO `t3` VALUES (...) 4
statements_digest INSERT INTO `t4` VALUES (...) 1
statements_digest INSERT INTO `t5` VALUES (...) 1
statements_digest INSERT INTO `t1` VALUES (?) /* , ... */ 2
statements_digest INSERT INTO `t3` VALUES (...) /* , ... */ 1
statements_digest INSERT INTO `t5` VALUES (...) /* , ... */ 1
statements_digest INSERT INTO `t6` VALUES (...) 5
statements_digest SELECT ? + ? 3
statements_digest SELECT ? 1
statements_digest CREATE SCHEMA `statements_digest_temp` 2
statements_digest DROP SCHEMA `statements_digest_temp` 2
statements_digest SELECT ? FROM `no_such_table` 1
statements_digest CREATE TABLE `dup_table` ( `c` CHARACTER (?) ) 2
statements_digest DROP TABLE `dup_table` 1
statements_digest INSERT IGNORE INTO `t11` VALUES (?) 1
statements_digest SHOW WARNINGS 1
statements_digest PREPARE `stmt` FROM ? 1
statements_digest EXECUTE `stmt` 2
statements_digest DEALLOCATE PREPARE `stmt` 1
statements_digest CREATE PROCEDURE `p1` ( ) BEGIN SELECT * FROM `t12` ; END 1
statements_digest CALL `p1` ( ) 2
statements_digest DROP PROCEDURE `p1` 1
statements_digest CREATE FUNCTION `func` ( `a` INTEGER , `b` INTEGER ) RETURNS INTEGER (?) RETURN `a` + `b` 1
statements_digest SELECT `func` (...) 2
statements_digest DROP FUNCTION `func` 1
statements_digest CREATE TRIGGER `trg` BEFORE INSERT ON `t12` FOR EACH ROW SET @? := ? 1
statements_digest INSERT INTO `t12` VALUES (?) 2
statements_digest DROP TRIGGER `trg` 1
SELECT digest_text FROM performance_schema.events_statements_current;
digest_text
####################################
# CLEANUP
####################################
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
DROP TABLE IF EXISTS t4;
DROP TABLE IF EXISTS t5;
DROP TABLE IF EXISTS t6;
DROP TABLE IF EXISTS t11;
DROP TABLE IF EXISTS t12;
DROP DATABASE IF EXISTS statements_digest;
|
