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, digest_text, count_star FROM performance_schema.events_statements_summary_by_digest;
schema_name digest digest_text count_star
statements_digest d24da32343f2b799f8a7ba1bdc45f83b TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 1
statements_digest 2470c53bba1de70e17dee0ffecf92c73 SELECT ? FROM `t1` 2
statements_digest 7aaf73121c2dc1961757af496028610b SELECT ?, ... FROM `t1` 2
statements_digest 57175aac3e410f4a0df5900bf998b181 SELECT ? FROM `t2` 1
statements_digest 7595674dce5accccec6d0a9c0918b689 SELECT ?, ... FROM `t2` 2
statements_digest 02b23366d1c2eb07f271b27a92261850 INSERT INTO `t1` VALUES (?) 2
statements_digest d6935b92b93f59d1d8580d71345bf785 INSERT INTO `t2` VALUES (?) 1
statements_digest 0bed9d67f8f0eed70e23d8d652f850f1 INSERT INTO `t3` VALUES (...) 4
statements_digest 5a4288f864ee631ce51c9a2250ee05f4 INSERT INTO `t4` VALUES (...) 1
statements_digest 2f3f80af62a9a0bc3e65ade97b7d9b91 INSERT INTO `t5` VALUES (...) 1
statements_digest 9282eb80534dd560d422f0ad3e2ef79e INSERT INTO `t1` VALUES (?) /* , ... */ 2
statements_digest 84fd494f2106df4a7b68a7d64c38390c INSERT INTO `t3` VALUES (...) /* , ... */ 1
statements_digest 8d8c2006b523c427db1f4a64d2a3071c INSERT INTO `t5` VALUES (...) /* , ... */ 1
statements_digest 119634570de383d4e24bb246f9aa5093 INSERT INTO `t6` VALUES (...) 5
statements_digest d8492e72ce015b008b8a29347b1574f0 SELECT ? + ? 3
statements_digest 3d4fc22e33e10d7235eced3c75a84c2c SELECT ? 1
statements_digest 228bd1e66dfaad7a93a08eec7cfdf840 CREATE SCHEMA `statements_digest_temp` 2
statements_digest a1b8dbf6a71e58af1c93dbdb27f2979c DROP SCHEMA `statements_digest_temp` 2
statements_digest 80ef2cc343a9725239f4d9d88e3ed77b SELECT ? FROM `no_such_table` 1
statements_digest 66bbd419c604f474fe5a769c3d8f091c CREATE TABLE `dup_table` ( `c` CHARACTER (?) ) 2
statements_digest ae27efa5e1da98d54b2bf2af9a492780 DROP TABLE `dup_table` 1
statements_digest 164476310fd2576cb913120166b2f2b0 INSERT IGNORE INTO `t11` VALUES (?) 1
statements_digest feaff321c54a9c8e1e9508628f7a5a05 SHOW WARNINGS 1
statements_digest ba5e1127c88c3b8737000ac83579c15b PREPARE `stmt` FROM ? 1
statements_digest 9e1c48512fdded3c4e64f3ac9a786096 EXECUTE `stmt` 2
statements_digest 26b3d4dd928dd5321d2a0e1e63d926f7 DEALLOCATE PREPARE `stmt` 1
statements_digest 179e0cb3ea4542473dc88fd24b209049 CREATE PROCEDURE `p1` ( ) BEGIN SELECT * FROM `t12` ; END 1
statements_digest cde8f1053b3b4a4d67658c9c8572e765 CALL `p1` ( ) 2
statements_digest ff2582be3f542ec89d41ffc8ef018c2f DROP PROCEDURE `p1` 1
statements_digest bae719145a0d1e65cbbdec759665bd79 CREATE FUNCTION `func` ( `a` INTEGER , `b` INTEGER ) RETURNS INTEGER (?) RETURN `a` + `b` 1
statements_digest 27b8b33b381b70df85aa39ae223baa19 SELECT `func` (...) 2
statements_digest a1686531a64227fc7d1e96f090462d7b DROP FUNCTION `func` 1
statements_digest 3a668f739568e34b3ae67491f7938639 CREATE TRIGGER `trg` BEFORE INSERT ON `t12` FOR EACH ROW SET @? := ? 1
statements_digest 5c24333d11c3e873088d2911f4589883 INSERT INTO `t12` VALUES (?) 2
statements_digest e1ea7222497e0e50f3ee08a17ebd2542 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;
|
