在说明系统数据库之前，先来看下MySQL在数据字典方面的演变历史：
MySQL4.1 提供了information_schema 数据字典。从此可以很简单的用SQL语句来检索需要的系统元数据了。
MySQL5.5 提供了performance_schema 性能字典。 但是这个字典比较专业，一般人可能也就看看就不了了之了。
MySQL5.7 提供了 sys系统数据库。 sys数据库里面包含了一系列的存储过程、自定义函数以及视图来帮助我们快速的了解系统的元数据信息。
sys系统数据库结合了information_schema和performance_schema的相关数据，让我们更加容易的检索元数据。 现在呢，我就示范下几种场景下如何快速的使用。
第一，
比如之前想要知道某个表是否存在与否，可以用以下两种方法：
A， 悲观的方法，写SQL从information_schema中拿信息：
|
1
2
3
4
5
6
7
|
mysql>
SELECT
IF(
COUNT
(*) = 0,
'Not exists!'
,
'Exists!'
)
AS
'result'
FROM
information_schema.tables
WHERE
table_schema =
'new_feature'
AND
table_name =
't1'
;
+
-------------+
| result |
+
-------------+
|
Not
exists! |
+
-------------+
1 row
in
set
(0.00 sec)
|
B，乐观的方法，假设表存在，写一个存储过程：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
DELIMITER $$
USE `new_feature`$$
DROP
PROCEDURE
IF EXISTS `sp_table_exists`$$
CREATE
DEFINER=`root`@`localhost`
PROCEDURE
`sp_table_exists`(
IN
db_name
VARCHAR
(64),
IN
tb_name
VARCHAR
(64),
OUT
is_exists
VARCHAR
(60)
)
BEGIN
DECLARE
no_such_table CONDITION
FOR
1146;
DECLARE
EXIT HANDLER
FOR
no_such_table
BEGIN
SET
is_exists =
'Not exists!'
;
END
;
SET
@stmt = CONCAT(
'select 1 from '
,db_name,
'.'
,tb_name);
PREPARE
s1
FROM
@stmt;
EXECUTE
s1;
DEALLOCATE
PREPARE
s1;
SET
is_exists =
'Exists!'
;
END
$$
DELIMITER ;
|
现在来调用：
|
1
2
3
4
5
6
7
8
9
|
mysql> call sp_table_exists(
'new_feature'
,
't1'
,@result);
Query OK, 0
rows
affected (0.00 sec)
mysql>
select
@result;
+
-------------+
| @result |
+
-------------+
|
Not
exists! |
+
-------------+
1 row
in
set
(0.00 sec)
|
现在我们直接用sys数据库里面现有的存储过程来进行调用，
|
1
2
3
4
5
6
7
8
9
|
mysql> CALL table_exists(
'new_feature'
,
't1'
,@v_is_exists);
Query OK, 0
rows
affected (0.00 sec)
mysql>
SELECT
IF(@v_is_exists =
''
,
'Not exists!'
,@v_is_exists)
AS
'result'
;
+
-------------+
| result |
+
-------------+
|
Not
exists! |
+
-------------+
1 row
in
set
(0.00 sec)
|
第二，获取没有使用过的索引。
|
1
2
3
4
5
6
7
8
|
mysql>
SELECT
*
FROM
schema_unused_indexes;
+
---------------+-------------+--------------+
| object_schema | object_name | index_name |
+
---------------+-------------+--------------+
| new_feature | t1 | idx_log_time |
| new_feature | t1 | idx_rank2 |
+
---------------+-------------+--------------+
2
rows
in
set
(0.00 sec)
|
第三， 检索指定数据库下面的表扫描信息，过滤出执行次数大于10的查询，
|
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
|
mysql>
SELECT
*
FROM
statement_analysis
WHERE
db=
'new_feature'
AND
full_scan =
'*'
AND
exec_count > 10\G
*************************** 1. row ***************************
query: SHOW STATUS
db: new_feature
full_scan: *
exec_count: 26
err_count: 0
warn_count: 0
total_latency: 74.68 ms
max_latency: 3.86 ms
avg_latency: 2.87 ms
lock_latency: 4.50 ms
rows_sent: 9594
rows_sent_avg: 369
rows_examined: 9594
rows_examined_avg: 369
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 0
tmp_disk_tables: 0
rows_sorted: 0
sort_merge_passes: 0
digest: 475fa3ad9d4a846cfa96441050fc9787
first_seen: 2015-11-16 10:51:17
last_seen: 2015-11-16 11:28:13
*************************** 2. row ***************************
query:
SELECT
`state` , `round` (
SUM
... uration (summed)
in
sec`
DESC
db: new_feature
full_scan: *
exec_count: 12
err_count: 0
warn_count: 12
total_latency: 16.43 ms
max_latency: 2.39 ms
avg_latency: 1.37 ms
lock_latency: 3.54 ms
rows_sent: 140
rows_sent_avg: 12
rows_examined: 852
rows_examined_avg: 71
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 24
tmp_disk_tables: 0
rows_sorted: 140
sort_merge_passes: 0
digest: 538e506ee0075e040b076f810ccb5f5c
first_seen: 2015-11-16 10:51:17
last_seen: 2015-11-16 11:28:13
2
rows
in
set
(0.01 sec)
|
第四， 同样继续上面的，过滤出有临时表的查询，
|
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
|
mysql>
SELECT
*
FROM
statement_analysis
WHERE
db=
'new_feature'
AND
tmp_tables > 0
ORDER
BY
tmp_tables
DESC
LIMIT 1\G
*************************** 1. row ***************************
query:
SELECT
`performance_schema` . ...
name
` . `SUM_TIMER_WAIT`
DESC
db: new_feature
full_scan: *
exec_count: 2
err_count: 0
warn_count: 0
total_latency: 87.96 ms
max_latency: 59.50 ms
avg_latency: 43.98 ms
lock_latency: 548.00 us
rows_sent: 101
rows_sent_avg: 51
rows_examined: 201
rows_examined_avg: 101
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 332
tmp_disk_tables: 15
rows_sorted: 0
sort_merge_passes: 0
digest: ff9bdfb7cf3f44b2da4c52dcde7a7352
first_seen: 2015-11-16 10:24:42
last_seen: 2015-11-16 10:24:42
1 row
in
set
(0.01 sec)
|
可以看到上面查询详细的详细，再也不用执行show status 手工去过滤了。
第五， 检索执行次数排名前五的语句，
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql>
SELECT
statement,total
FROM
user_summary_by_statement_type
WHERE
`
user
`=
'root'
ORDER
BY
total
DESC
LIMIT 5;
+
-------------------+-------+
| statement | total |
+
-------------------+-------+
| jump_if_not | 17635 |
| freturn | 3120 |
| show_create_table | 289 |
| Field List | 202 |
| set_option | 190 |
+
-------------------+-------+
5
rows
in
set
(0.01 sec)
|
示例我就写这么多了，详细的去看使用手册并且自己摸索去吧。
本文转自 david_yeung 51CTO博客，原文链接：http://blog.51cto.com/yueliangdao0608/1716616，如需转载请自行联系原作者
