Open zqhong opened 7 years ago
MySQL 在数据字典方面的演变历史
information_schema
performance_schema
sys
sys schema
MySQL 官方 BLOG 介绍：
For Linux users I like to compare performance_schema to /proc, and SYS to vmstat.
对于每一个试图，都有两种形式，一种便于人类阅读，一种便于工作处理（以 x$ 开头）
x$
mysql> select * from host_summary_by_file_io; +------------+-------+------------+ | host | ios | io_latency | +------------+-------+------------+ | localhost | 15416 | 1.58 s | | background | 3723 | 1.12 s | | 10.0.2.2 | 589 | 31.31 ms | +------------+-------+------------+ 3 rows in set (0.02 sec) mysql> select * from x$host_summary_by_file_io; +------------+-------+---------------+ | host | ios | io_latency | +------------+-------+---------------+ | localhost | 15416 | 1583845311152 | | background | 3726 | 1118816307764 | | 10.0.2.2 | 589 | 31312339748 | +------------+-------+---------------+ 3 rows in set (0.01 sec)
SQL
buffer pool
user_summary user_summary user_summary_by_file_io user_summary_by_file_io_type user_summary_by_stages user_summary_by_statement_latency user_summary_by_statement_type 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
Example：
mysql> select * from user_summary limit 1\G *************************** 1. row *************************** user: homestead statements: 9444 statement_latency: 5.69 s statement_avg_latency: 602.53 us table_scans: 682 file_ios: 32010 file_io_latency: 3.23 s current_connections: 1 total_connections: 106 unique_hosts: 2 current_memory: 0 bytes total_memory_allocated: 0 bytes 1 row in set (0.01 sec)
mysql> select * from host_summary \G *************************** 1. row *************************** host: 10.0.2.2 statements: 147 statement_latency: 162.00 ms statement_avg_latency: 1.10 ms table_scans: 84 file_ios: 589 file_io_latency: 31.31 ms current_connections: 0 total_connections: 15 unique_users: 1 current_memory: 0 bytes total_memory_allocated: 0 bytes *************************** 2. row *************************** host: localhost statements: 4806 statement_latency: 2.74 s statement_avg_latency: 569.92 us table_scans: 260 file_ios: 15416 file_io_latency: 1.58 s current_connections: 1 total_connections: 91 unique_users: 1 current_memory: 0 bytes total_memory_allocated: 0 bytes 2 rows in set (0.02 sec)
io_by_thread_by_latency io_global_by_file_by_bytes io_global_by_file_by_latency io_global_by_file_by_bytes io_global_by_wait_by_bytes io_global_by_wait_by_latency latest_file_io memory_by_host_by_current_bytes memory_by_thread_by_current_bytes memory_by_user_by_current_bytes memory_global_by_current_bytes memory_global_total
Example： 在哪个文件产生了最多的IO
mysql> select * from io_global_by_file_by_bytes limit 3 \G *************************** 1. row *************************** file: @@datadir/ibdata1 count_read: 162 total_read: 4.55 MiB avg_read: 28.74 KiB count_write: 809 total_written: 30.42 MiB avg_write: 38.51 KiB total: 34.97 MiB write_pct: 87.00 *************************** 2. row *************************** file: @@datadir/ibtmp1 count_read: 0 total_read: 0 bytes avg_read: 0 bytes count_write: 148 total_written: 14.12 MiB avg_write: 97.73 KiB total: 14.12 MiB write_pct: 100.00 *************************** 3. row *************************** file: @@datadir/ib_logfile0 count_read: 7 total_read: 68.50 KiB avg_read: 9.79 KiB count_write: 1026 total_written: 2.81 MiB avg_write: 2.81 KiB total: 2.88 MiB write_pct: 97.68 3 rows in set (0.01 sec)
schema_auto_increment_columns schema_index_statistics schema_object_overview schema_redundant_indexes schema_table_lock_waits schema_table_statistics schema_table_statistics_with_buffer schema_table_with_full_table_scans schema_unused_indexes
查看索引统计信息
mysql> select table_schema, table_name, index_name, rows_selected, rows_inserted, rows_updated, rows_deleted from schema_index_statistics; +--------------+------------+------------+---------------+---------------+--------------+--------------+ | table_schema | table_name | index_name | rows_selected | rows_inserted | rows_updated | rows_deleted | +--------------+------------+------------+---------------+---------------+--------------+--------------+ | test | sbtest2 | PRIMARY | 1440833595 | 0 | 5994464 | 1998083 | | test | sbtest1 | PRIMARY | 1442705405 | 0 | 6002248 | 2000660 | | sys | sys_config | PRIMARY | 3 | 0 | 0 | 0 | | test | person | PRIMARY | 0 | 0 | 0 | 0 | | test | sbtest1 | k_1 | 0 | 0 | 0 | 0 | | test | sbtest2 | k_2 | 0 | 0 | 0 | 0 | | test | t1 | PRIMARY | 0 | 0 | 0 | 0 | | test | t1 | idx_a_b | 0 | 0 | 0 | 0 | | test | t1 | idx_a_b_d | 0 | 0 | 0 | 0 | | test | t1 | idx_b_c | 0 | 0 | 0 | 0 | | test | t1 | idx_b_c_d | 0 | 0 | 0 | 0 | +--------------+------------+------------+---------------+---------------+--------------+--------------+
查看自增字段情况
mysql> select data_type, column_type, is_unsigned, max_value, auto_increment_ratio from schema_auto_increment_columns; +-----------+------------------+-------------+------------+----------------------+ | data_type | column_type | is_unsigned | max_value | auto_increment_ratio | +-----------+------------------+-------------+------------+----------------------+ | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | | int | int(10) unsigned | 1 | 4294967295 | 0.0000 | +-----------+------------------+-------------+------------+----------------------+ 38 rows in set (0.05 sec)
statement_analysis statements_with_errors_or_warnings statements_with_full_table_scans statements)with_runtimes_in_95th_percentile statements_with_sorting statements_with_temp_tables
mysql> select * from statement_analysis limit 2 \G 1. row query: SELECT sys . format_path ( ... ) ) , ? ) ) AS avg_write , db: sys full_scan: * exec_count: 7 err_count: 0 warn_count: 0 total_latency: 586.44 ms max_latency: 279.03 ms avg_latency: 83.78 ms lock_latency: 2.76 ms rows_sent: 749 rows_sent_avg: 107 rows_examined: 370328 rows_examined_avg: 52904 rows_affected: 0 rows_affected_avg: 0 tmp_tables: 0 tmp_disk_tables: 0 rows_sorted: 749 sort_merge_passes: 0 digest: 24b079601859386b8e421d066b259805 first_seen: 2016-11-11 15:28:44 last_seen: 2016-11-12 08:07:02 2. row query: SELECT performance_schema . ... name.SUM_TIMER_WAIT` DESC db: sys full_scan: * exec_count: 2 err_count: 0 warn_count: 0 total_latency: 136.38 ms max_latency: 81.04 ms avg_latency: 68.19 ms lock_latency: 1.48 ms rows_sent: 76 rows_sent_avg: 38 rows_examined: 1436 rows_examined_avg: 718 rows_affected: 0 rows_affected_avg: 0 tmp_tables: 458 tmp_disk_tables: 38 rows_sorted: 76 sort_merge_passes: 0 digest: 30066488887c2b0b51a14219e663b4ca first_seen: 2016-11-12 08:19:44 last_seen: 2016-11-12 08:22:48 2 rows in set (0.00 sec)
format_path
avg_write
.
2. 哪些 `SQL` 语句使用了磁盘临时表
mysql> select * from statements_with_temp_tables limit 2 \G 1. row query: SELECT performance_schema . ... name.SUM_TIMER_WAITDESC db: sys exec_count: 2 total_latency: 136.38 ms memory_tmp_tables: 458 disk_tmp_tables: 38 avg_tmp_tables_per_query: 229 tmp_tables_to_disk_pct: 8 first_seen: 2016-11-12 08:19:44 last_seen: 2016-11-12 08:22:48 digest: 30066488887c2b0b51a14219e663b4ca *************************** 2. row *************************** query: SHOW FIELDS FROMhifone.threads` db: hifone exec_count: 12 total_latency: 6.85 ms memory_tmp_tables: 12 disk_tmp_tables: 12 avg_tmp_tables_per_query: 1 tmp_tables_to_disk_pct: 100 first_seen: 2016-10-29 04:34:12 last_seen: 2016-10-29 04:40:58 digest: acb06bb463da7794797c64245e97cb82 2 rows in set (0.00 sec)
DESC db: sys exec_count: 2 total_latency: 136.38 ms memory_tmp_tables: 458 disk_tmp_tables: 38 avg_tmp_tables_per_query: 229 tmp_tables_to_disk_pct: 8 first_seen: 2016-11-12 08:19:44 last_seen: 2016-11-12 08:22:48 digest: 30066488887c2b0b51a14219e663b4ca *************************** 2. row *************************** query: SHOW FIELDS FROM
## Views：其他 1. Buffer pool 2. 锁等待 3. 会话 4. 延迟
innodb_buffer_starts_by_schema innodb_buffer_starts_by_table innodb_lock_waits wait_classes_global_by_avg_latency wait_classes_global_by_latency waits_by_host_by_latency waits_by_user_by_latency processlist session
Example：哪张表占用了最多的 `buffer pool` 空间
mysql> select * from innodb_buffer_stats_by_table limit 3; +---------------+---------------------------+------------+------------+-------+--------------+-----------+-------------+ | object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached | +---------------+---------------------------+------------+------------+-------+--------------+-----------+-------------+ | InnoDB System | SYS_TABLES | 3.06 MiB | 2.74 MiB | 196 | 196 | 196 | 9417 | | mysql | time_zone_transition | 800.00 KiB | 715.16 KiB | 50 | 50 | 50 | 21644 | | mysql | time_zone_transition_type | 272.00 KiB | 224.70 KiB | 17 | 17 | 17 | 5766 | +---------------+---------------------------+------------+------------+-------+--------------+-----------+-------------+
3 rows in set (0.03 sec)
--- # 案例：索引 ## 索引的好处 * 通过索引过滤，减少需要扫描的记录数量； * 索引可以帮助服务器避免排序和临时表； * 索引可以将随机 IO 变为顺序 IO； * 通过索引覆盖，加快查询； ## 索引的坏处 * 占用磁盘空间； * 增加了记录修改（插入、删除、修改）的 ## 查看索引统计
mysql> select table_schema, table_name, index_name, rows_selected, rows_inserted from schema_index_statistics limit 3; +--------------+------------+----------------------+---------------+---------------+ | table_schema | table_name | index_name | rows_selected | rows_inserted | +--------------+------------+----------------------+---------------+---------------+ | hifone | users | PRIMARY | 79 | 0 | | hifone | threads | PRIMARY | 15 | 0 | | hifone | adblocks | adblocks_slug_unique | 85 | 0 | +--------------+------------+----------------------+---------------+---------------+ 3 rows in set (0.00 sec)
## 查找重复索引
mysql> select table_name, redundant_index_name, sql_drop_index from -> schema_redundant_indexes limit 2; +------------+--------------------------+-------------------------------------------------------------------------+ | table_name | redundant_index_name | sql_drop_index | +------------+--------------------------+-------------------------------------------------------------------------+ | identities | identities_user_id_index | ALTER TABLE hifone.identities DROP INDEX identities_user_id_index | +------------+--------------------------+-------------------------------------------------------------------------+ 1 row in set (0.02 sec)
hifone
identities
identities_user_id_index
## 查找无用索引
mysql> SELECT * FROM schema_unused_indexes limit 2; +---------------+-------------+---------------------------+ | object_schema | object_name | index_name | +---------------+-------------+---------------------------+ | hifone | adspaces | adspaces_position_unique | | hifone | adspaces | adspaces_adblock_id_index | +---------------+-------------+---------------------------+ 2 rows in set (0.00 sec)
--- # 参考 [Sys-Schema--赖明星@PHPCon2016 - youku](http://v.youku.com/v_show/id_XMTYzNDI5MDIyNA==.html?&f=27589422&from=y1.2-3.4.2) [Sys-Schema--赖明星@PHPCon2016.pdf](https://github.com/ThinkDevelopers/PHPConChina/blob/master/PHPCON2016/Sys-Schema--%E8%B5%96%E6%98%8E%E6%98%9F%40PHPCon2016.pdf)
MySQL 在数据字典方面的演变历史
information_schema数据字典performance_schema性能字典performance_schemasys系统数据库sys schema 的组成和作用
sys schema包含了一些视图、函数和存储过程sys schema用于帮助 DBA 和开发者分析定位问题MySQL 官方 BLOG 介绍：
Sys views
views 的两种形式
对于每一个试图，都有两种形式，一种便于人类阅读，一种便于工作处理（以
x$开头）Views：sys schema 如何帮助使用
SQL语句使用了磁盘临时表；buffer pool空间；views：从“使用者”角度看代价
Example：
Views：从资源角度看使用情况
Example： 在哪个文件产生了最多的IO
Views：schema 级别的统计信息
Example：
查看索引统计信息
查看自增字段情况
Views：statement 级别的统计信息
Example：
mysql> select * from statement_analysis limit 2 \G 1. row query: SELECT
sys.format_path( ... ) ) , ? ) ) ASavg_write, db: sys full_scan: * exec_count: 7 err_count: 0 warn_count: 0 total_latency: 586.44 ms max_latency: 279.03 ms avg_latency: 83.78 ms lock_latency: 2.76 ms rows_sent: 749 rows_sent_avg: 107 rows_examined: 370328 rows_examined_avg: 52904 rows_affected: 0 rows_affected_avg: 0 tmp_tables: 0 tmp_disk_tables: 0 rows_sorted: 749 sort_merge_passes: 0 digest: 24b079601859386b8e421d066b259805 first_seen: 2016-11-11 15:28:44 last_seen: 2016-11-12 08:07:02 2. row query: SELECTperformance_schema. ... name.SUM_TIMER_WAIT` DESC db: sys full_scan: * exec_count: 2 err_count: 0 warn_count: 0 total_latency: 136.38 ms max_latency: 81.04 ms avg_latency: 68.19 ms lock_latency: 1.48 ms rows_sent: 76 rows_sent_avg: 38 rows_examined: 1436 rows_examined_avg: 718 rows_affected: 0 rows_affected_avg: 0 tmp_tables: 458 tmp_disk_tables: 38 rows_sorted: 76 sort_merge_passes: 0 digest: 30066488887c2b0b51a14219e663b4ca first_seen: 2016-11-12 08:19:44 last_seen: 2016-11-12 08:22:48 2 rows in set (0.00 sec)mysql> select * from statements_with_temp_tables limit 2 \G 1. row query: SELECT
performance_schema. ... name.SUM_TIMER_WAITDESC db: sys exec_count: 2 total_latency: 136.38 ms memory_tmp_tables: 458 disk_tmp_tables: 38 avg_tmp_tables_per_query: 229 tmp_tables_to_disk_pct: 8 first_seen: 2016-11-12 08:19:44 last_seen: 2016-11-12 08:22:48 digest: 30066488887c2b0b51a14219e663b4ca *************************** 2. row *************************** query: SHOW FIELDS FROMhifone.threads` db: hifone exec_count: 12 total_latency: 6.85 ms memory_tmp_tables: 12 disk_tmp_tables: 12 avg_tmp_tables_per_query: 1 tmp_tables_to_disk_pct: 100 first_seen: 2016-10-29 04:34:12 last_seen: 2016-10-29 04:40:58 digest: acb06bb463da7794797c64245e97cb82 2 rows in set (0.00 sec)innodb_buffer_starts_by_schema innodb_buffer_starts_by_table innodb_lock_waits wait_classes_global_by_avg_latency wait_classes_global_by_latency waits_by_host_by_latency waits_by_user_by_latency processlist session
mysql> select * from innodb_buffer_stats_by_table limit 3; +---------------+---------------------------+------------+------------+-------+--------------+-----------+-------------+ | object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached | +---------------+---------------------------+------------+------------+-------+--------------+-----------+-------------+ | InnoDB System | SYS_TABLES | 3.06 MiB | 2.74 MiB | 196 | 196 | 196 | 9417 | | mysql | time_zone_transition | 800.00 KiB | 715.16 KiB | 50 | 50 | 50 | 21644 | | mysql | time_zone_transition_type | 272.00 KiB | 224.70 KiB | 17 | 17 | 17 | 5766 | +---------------+---------------------------+------------+------------+-------+--------------+-----------+-------------+
3 rows in set (0.03 sec)
mysql> select table_schema, table_name, index_name, rows_selected, rows_inserted from schema_index_statistics limit 3; +--------------+------------+----------------------+---------------+---------------+ | table_schema | table_name | index_name | rows_selected | rows_inserted | +--------------+------------+----------------------+---------------+---------------+ | hifone | users | PRIMARY | 79 | 0 | | hifone | threads | PRIMARY | 15 | 0 | | hifone | adblocks | adblocks_slug_unique | 85 | 0 | +--------------+------------+----------------------+---------------+---------------+ 3 rows in set (0.00 sec)
mysql> select table_name, redundant_index_name, sql_drop_index from -> schema_redundant_indexes limit 2; +------------+--------------------------+-------------------------------------------------------------------------+ | table_name | redundant_index_name | sql_drop_index | +------------+--------------------------+-------------------------------------------------------------------------+ | identities | identities_user_id_index | ALTER TABLE
hifone.identitiesDROP INDEXidentities_user_id_index| +------------+--------------------------+-------------------------------------------------------------------------+ 1 row in set (0.02 sec)mysql> SELECT * FROM schema_unused_indexes limit 2; +---------------+-------------+---------------------------+ | object_schema | object_name | index_name | +---------------+-------------+---------------------------+ | hifone | adspaces | adspaces_position_unique | | hifone | adspaces | adspaces_adblock_id_index | +---------------+-------------+---------------------------+ 2 rows in set (0.00 sec)