mysql8 视图_牛刀小试MySQL8.0之sys视图的剖析

最新推荐文章于 2022-10-17 17:03:48 发布

山同气

最新推荐文章于 2022-10-17 17:03:48 发布

阅读量160

点赞数

文章标签： mysql8 视图

本文链接：https://blog.csdn.net/weixin_35899665/article/details/113297740

版权

本文通过MySQL8.0.11版本展示了如何利用sys schema进行性能监控，包括查看数据访问量、索引冗余、自增ID监控、全表扫描SQL监控、会话与线程状态以及磁盘IO消耗。通过对ztest库的zstudent表进行实例分析，揭示了索引冗余问题，并提供了优化建议。

摘要由CSDN通过智能技术生成

*MySQL环境版本:

(root@localhost) [sys]> select @@version;

+-----------+

| @@version |

+-----------+

| 8.0.11 |

+-----------+

1 row in set (0.01 sec)

**数据准备:

(root@localhost) [ztest]> show create table zstudent;

| Table | Create Table

+------------------------------------+

| zstudent | CREATE TABLE `zstudent` (

`stu_id` int(11) NOT NULL AUTO_INCREMENT,

`stu_name` varchar(20) DEFAULT NULL,

`sex` char(1) DEFAULT NULL,

PRIMARY KEY (`stu_id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |

+-------------------------------------+

1 row in set (0.00 sec)

创建索引:

(root@localhost) [ztest]> create index idx_stu_name on zstudent (stu_name);

(root@localhost) [ztest]> create unique index idx_stu_name2 on zstudent (stu_name);

*sys包含了很多总结performance Schema表的视图，这些视图成对出现，并且有些以x$前缀出现。查看并使用sys的方式:

(root@localhost) [sys]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| ztest |

+--------------------+

5 rows in set (0.01 sec)

(root@localhost) [sys]> use sys

Database changed

(root@localhost) [sys]> show tables;

+-----------------------------------------------+

| Tables_in_sys |

+-----------------------------------------------+

| host_summary |

| host_summary_by_file_io |

| host_summary_by_file_io_type |

| host_summary_by_stages |

| host_summary_by_statement_latency |

| host_summary_by_statement_type |

| innodb_buffer_stats_by_schema |

| innodb_buffer_stats_by_table |

| innodb_lock_waits |

| io_by_thread_by_latency |

...................

1.查看表的数据访问量

(root@localhost) [sys]> select table_schema,table_name,io_read_requests,io_write_requests from schema_table_statistics where table_schema='ztest';

+--------------+------------+------------------+-------------------+

+--------------+------------+------------------+-------------------+

| ztest | zstudent | 0 | 14 |

| ztest | zstudent2 | 0 | 7 |

+--------------+------------+------------------+-------------------+

2 rows in set (0.05 sec)

2.查看索引的冗余

(root@localhost) [sys]> select * from sys.schema_redundant_indexes \G

*************************** 1. row ***************************

table_schema: ztest

table_name: zstudent

redundant_index_name: idx_stu_name

redundant_index_columns: stu_name

redundant_index_non_unique: 1

dominant_index_name: idx_stu_name2

dominant_index_columns: stu_name

dominant_index_non_unique: 0

subpart_exists: 0

sql_drop_index: ALTER TABLE `ztest`.`zstudent` DROP INDEX `idx_stu_name`

1 row in set (0.01 sec)

3.表自增ID监控

(root@localhost) [sys]> select * From schema_auto_increment_columns\G;

*************************** 1. row ***************************

table_schema: ztest

table_name: zstudent

column_name: stu_id

data_type: int

column_type: int(11)

is_signed: 1

is_unsigned: 0

max_value: 2147483647

auto_increment: 6

auto_increment_ratio: 0.0000

1 row in set (0.04 sec)

4.监控全表扫描的SQL语句

(root@localhost) [sys]> select * from statements_with_full_table_scans where db='ztest' \G

*************************** 1. row ***************************

query: SELECT * FROM `zstudent`

db: ztest

exec_count: 2

total_latency: 6.58 m

no_index_used_count: 2

no_good_index_used_count: 0

no_index_used_pct: 100

rows_sent: 10

rows_examined: 10

rows_sent_avg: 5

rows_examined_avg: 5

first_seen: 2018-05-19 17:03:03.306527

last_seen: 2018-05-19 18:36:51.142365

digest: 8800ada0600ed0790d89b6ab22e5bab762c3698d308346bb542c9b2c377c4114

5.查看当前接入的会话

(root@localhost) [sys]> select thd_id,conn_id,user,command,current_statement,current_memory From x$session\G;

*************************** 1. row ***************************

thd_id: 68

conn_id: 28

user: root@localhost

command: Sleep

current_statement: NULL

current_memory: 34950

*************************** 2. row ***************************

thd_id: 67

conn_id: 27

user: root@localhost

command: Query

current_statement: select thd_id,conn_id,user,command,current_statement,current_memory From x$session

current_memory: 1207007

*************************** 3. row ***************************

thd_id: 45

conn_id: 4

user: sql/event_scheduler

command: Sleep

current_statement: NULL

current_memory: 16569

3 rows in set (0.17 sec)

*current_memory ---The number of bytes allocated by the thread(当前这个线程所需要分配的内存，单位为byte)

6.查看当前接入的线程

(root@localhost) [sys]> select thd_id,conn_id,user,db,command,current_statement from x$processlist;

+--------+---------+--------------------------------------+-------+---------+----------------------------------------------------------------------------+

+--------+---------+--------------------------------------+-------+---------+----------------------------------------------------------------------------+