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
|
####################################
# 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 52e3729216b72a67a671ac3b93a1f1d3 TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 1
statements_digest a76073841b59a83de0fcdb6a0158b94a SELECT ? FROM `t1` 2
statements_digest d91813c4d7a128822624a55b43bab7b2 SELECT ?, ... FROM `t1` 2
statements_digest 8d1d0319e2ce41e1c41455a06b8905f8 SELECT ? FROM `t2` 1
statements_digest 704f1e85525022d18028b3493bf61e65 SELECT ?, ... FROM `t2` 2
statements_digest 7f60599ab03830f5571b306d71e47ba3 INSERT INTO `t1` VALUES (?) 2
statements_digest 103d388f122df6a6a2c9f7fa01d90d7d INSERT INTO `t2` VALUES (?) 1
statements_digest f1f56fda9303c1e2555bd67d431398ab INSERT INTO `t3` VALUES (...) 4
statements_digest 08fc8813613c3cd44736a4abbb0cd095 INSERT INTO `t4` VALUES (...) 1
statements_digest ab209b79451b94d03d8e20374ec18795 INSERT INTO `t5` VALUES (...) 1
statements_digest 4729eb58cad3b77435bcd17864cfe322 INSERT INTO `t1` VALUES (?) /* , ... */ 2
statements_digest 8e543c7785feeeb3e9a1957397a1033f INSERT INTO `t3` VALUES (...) /* , ... */ 1
statements_digest 3dd587a1c42991bb323cbaa4c6fb61d0 INSERT INTO `t5` VALUES (...) /* , ... */ 1
statements_digest 6f2f9f471f739d16b4ff4faf256e839e INSERT INTO `t6` VALUES (...) 5
statements_digest 9701bfa1fb64563334f1a52953e065f3 SELECT ? + ? 3
statements_digest b0785a540ffc1743c4e0879d193a4b10 SELECT ? 1
statements_digest bee0eebfc340dbd233ee8c86270ac6ea CREATE SCHEMA `statements_digest_temp` 2
statements_digest a35fd3ac67e64b9ac41a53781a7f5662 DROP SCHEMA `statements_digest_temp` 2
statements_digest 52ec0213cba551f38d069c94a50cd2c7 SELECT ? FROM `no_such_table` 1
statements_digest 27d4298be49de7a7606fcc8122ce7cd6 CREATE TABLE `dup_table` ( `c` CHARACTER (?) ) 2
statements_digest 8a9b185842f12475c7ffa350ade45408 DROP TABLE `dup_table` 1
statements_digest bda68c0a1eca7b625a5158da41ebbcf9 INSERT INTO `t11` VALUES (?) 1
statements_digest 196c9f451360b5e24e03aa82f86006ae SHOW WARNINGS 1
statements_digest 3413dd64a34c2148e669e3283ca41ff5 PREPARE `stmt` FROM ? 1
statements_digest fcec9dcf45c26dabade2c7a4ab818543 EXECUTE `stmt` 2
statements_digest 9e5e4f78f8226cc853fa1ce62ae61f9d DEALLOCATE PREPARE `stmt` 1
statements_digest c92f30dceb52f470a6c36400bdb372c6 CREATE PROCEDURE `p1` ( ) BEGIN SELECT * FROM `t12` ; END 1
statements_digest db338b4f4a13d74acda7a7b9dae2b0b4 CALL `p1` ( ) 2
statements_digest c2c92e9e7ac73741622d1f264e08c384 DROP PROCEDURE `p1` 1
statements_digest c99aad5579088b31cdd53be4bfbc2b6e CREATE FUNCTION `func` ( `a` INTEGER , `b` INTEGER ) RETURNS INTEGER (?) RETURN `a` + `b` 1
statements_digest 1f4ce8758787f5aa5f51f1ee7f3b8119 SELECT `func` (...) 2
statements_digest ab76a0821015fa000a1df9c684072e37 DROP FUNCTION `func` 1
statements_digest ce5db6554a357045978a5572c84a7655 CREATE TRIGGER `trg` BEFORE INSERT ON `t12` FOR EACH ROW SET @? := ? 1
statements_digest 801f02819c67e56fe3f22cc7dda99707 INSERT INTO `t12` VALUES (?) 2
statements_digest dc3b07fe8e4d5fa91b383605f18512b0 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;
|
