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
|
####################################
# 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_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 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, digest_text, count_star FROM performance_schema.events_statements_summary_by_digest;
schema_name digest digest_text count_star
statements_digest 0e98ee6a98e296530ec59c12dbc08dfe TRUNCATE TABLE performance_schema . events_statements_summary_by_digest 1
statements_digest 954f43425c3234acc8e194afd97e8a0a SELECT ? FROM t1 1
statements_digest fc365a54bc19d746bd24c27aba46b990 SELECT ? FROM `t1` 1
statements_digest 27ba28f6252e4ae0e9b14b36da536fbe SELECT ?, ... FROM t1 2
statements_digest 81d03922612900032ec4b81934ab4841 SELECT ? FROM t2 1
statements_digest adce8aec12b6b5046cd4bf55951014c7 SELECT ?, ... FROM t2 2
statements_digest 59a1bd93c424b10802fe66bb6dcd94d2 INSERT INTO t1 VALUES (?) 1
statements_digest 91b2da58b0eb49c35a38fbc49f5e491d INSERT INTO t2 VALUES (?) 1
statements_digest 967114adbf91d8a4a99ec5e49e909ff4 INSERT INTO t3 VALUES (...) 4
statements_digest 8f25e7a48487e0aa7377e816816bb658 INSERT INTO t4 VALUES (...) 1
statements_digest 4e51253af793867fba66166de1f314f7 INSERT INTO t5 VALUES (...) 1
statements_digest fa47b3109e117216cd10209690d28596 INSERT INTO t1 VALUES (?) /* , ... */ 2
statements_digest 72409f84bc236e6fe9f2f7b4d727f2d3 INSERT INTO t3 VALUES (...) /* , ... */ 1
statements_digest d40aaddb41ed794d65dd8273f0c75700 INSERT INTO t5 VALUES (...) /* , ... */ 1
statements_digest 57a82b28388e52e99fc64339dd30edde INSERT INTO t1 VALUES ( NULL ) 1
statements_digest 6a56b694106442474cb0e5fb7575c8b9 INSERT INTO t6 VALUES (...) 5
statements_digest c9abf55e296c4317dbaf2d14ef907ad7 SELECT ? + ? 3
statements_digest 156304a0851a3e3626b39fb3da841a82 SELECT ? 1
statements_digest 3b085ab0d2063dfca1a39212e3ea1831 CREATE SCHEMA statements_digest_temp 2
statements_digest 09f9fabef2feb9a54ba01455e5ae83b9 DROP SCHEMA statements_digest_temp 2
statements_digest 7910a63ffd31cbcb742e15270c8958c8 SELECT ? FROM no_such_table 1
statements_digest fa34540a438bc672478b1162505ee28c CREATE TABLE dup_table ( c CHARACTER (?) ) 2
statements_digest 2c720f176bb7c8510ff8aca8921b9945 DROP TABLE dup_table 1
statements_digest 0c7d9fd8c27ab067511da41ca3bcdff3 INSERT INTO t11 VALUES (?) 1
statements_digest 81681ff345065ed72bcd1e9407ab85e4 SHOW WARNINGS 1
statements_digest d766f5823ae5d8e4cf4602b8e7a3fb80 PREPARE stmt FROM ? 1
statements_digest 3ab1e87eabd9688edf919754cce6348b EXECUTE stmt 2
statements_digest 470094469d250b9f45cab45bf610efe8 DEALLOCATE PREPARE stmt 1
statements_digest 1b4d25358e08b35ad54e49dfe5eaf3e4 CREATE PROCEDURE p1 ( ) BEGIN SELECT * FROM t12 ; END 1
statements_digest 84554971243e91106214dcb8f4eaa89b CALL p1 ( ) 2
statements_digest 77206e4bf30979c56752a7ed9150213a DROP PROCEDURE p1 1
statements_digest 03b91dcdba6b0e29f7fb240ae4bcd97f CREATE FUNCTION `func` ( a INTEGER , b INTEGER ) RETURNS INTEGER (?) RETURN a + b 1
statements_digest 72bc532f308f2dca62f5291df8c50e6f SELECT func (...) 2
statements_digest 0b5a5297689c5036def6af8e8a8ce113 DROP FUNCTION func 1
statements_digest d08331e42c67555ece50e46eef0f2b47 CREATE TRIGGER trg BEFORE INSERT ON t12 FOR EACH ROW SET @ ? := ? 1
statements_digest 754a49a4de995c5a729e9ab52f135f59 INSERT INTO t12 VALUES (?) 2
statements_digest 68df17752bca7c2c8ee2a6a19a0674e7 DROP TRIGGER trg 1
SELECT digest, digest_text FROM performance_schema.events_statements_current;
digest 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;
|
