牛刀小试mysql8.0之sys视图的剖析-凯发app官方网站

*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';
-------------- ------------ ------------------ -------------------
| table_schema | table_name | io_read_requests | io_write_requests |
-------------- ------------ ------------------ -------------------
| 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;


-------- --------- -------------------------------------- ------- --------- ----------------------------------------------------------------------------
| thd_id | conn_id | user                                 | db    | command | current_statement                                                          |
-------- --------- -------------------------------------- ------- --------- ----------------------------------------------------------------------------
|     27 |    null | innodb/srv_master_thread             | null  | null    | null                                                                       |
|     29 |    null | innodb/dict_stats_thread             | null  | null    | null                                                                       |
|     30 |    null | innodb/fts_optimize_thread           | null  | null    | null                                                                       |
|     47 |       6 | sql/compress_gtid_table              | null  | daemon  | null                                                                       |
|     34 |    null | mysqlx/acceptor_network              | null  | null    | null                                                                       |
|     37 |    null | innodb/srv_purge_thread              | null  | null    | null                                                                       |
|     38 |    null | innodb/srv_worker_thread             | null  | null    | null                                                                       |
|     39 |    null | innodb/srv_worker_thread             | null  | null    | null                                                                       |
|     41 |    null | innodb/srv_worker_thread             | null  | null    | null                                                                       |
|      1 |    null | sql/main                             | mysql | null    | null                                                                       |
|     68 |      28 | root@localhost                       | ztest | sleep   | null                                                                       |
|     67 |      27 | root@localhost                       | sys   | query   | select thd_id,conn_id,user,db,command,current_statement from x$processlist |
|     40 |    null | innodb/srv_purge_thread              | null  | null    | null                                                                       |
|     42 |    null | innodb/srv_worker_thread             | null  | null    | null                                                                       |
|     43 |    null | innodb/srv_worker_thread             | null  | null    | null                                                                       |
|     44 |    null | innodb/srv_worker_thread             | null  | null    | null                                                                       |
|     45 |       4 | sql/event_scheduler                  | null  | sleep   | null                                                                       |
|     32 |    null | mysqlx/worker                        | null  | null    | null                                                                       |
|     31 |    null | mysqlx/worker                        | null  | null    | null                                                                       |
|     46 |    null | sql/signal_handler                   | null  | null    | null                                                                       |
|     28 |    null | innodb/buf_dump_thread               | null  | null    | null                                                                       |
........................................


7.查看mysql实例消耗的磁盘io
(root@localhost) [sys]> select file,avg_read avg_write as avg_io from io_global_by_file_by_bytes order by avg_io desc limit 10;
---------------------------------------------- --------
| file                                         | avg_io |
---------------------------------------------- --------
| @@innodb_log_group_home_dir/ib_logfile0      | 642.62 |
| /data/mysqldata/3306/binlog/mysql-bin.000002 |    299 |
| @@innodb_data_home_dir/ibdata1               |  240.8 |
| /data/mysqldata/3306/slow_statement.log      |    212 |
| /data/mysqldata/3306/binlog/mysql-bin.000001 |    119 |
| @@basedir/share/english/errmsg.sys           |  83.65 |
| @@innodb_data_home_dir/ibtmp1                |  61.47 |
| @@datadir/undo_001                           |  32.18 |
| @@datadir/undo_002                           |  32.18 |
| @@datadir/mysql.ibd                          |  32.09 |
---------------------------------------------- --------
10 rows in set (0.49 sec)


解释:由于是一个测试库，所以读写io的负载都没有，基本在innodb_log日志自己的刷新之上。




知识点小注：当页面数据太多的时候，可以使用命令(root@localhost) [sys]> pager more;