sys系统库的11个小应用
1.查看慢SQL语句慢在哪里
# 首先需要启用与等待事件相关的 instruments 和 consumers, 否则last wait 宇段值可能为 NULL
call sys.ps_setup_enable_consumer('wait');
call sys.ps_setup_enable_instrument('wait');执行查看语句,这里只查command = ‘query’ 的线程信息
select * from session where command = 'query' and conn_id != connection_id();(因为本地数据的问题,所以没查出来,就不贴展示文本了)
2.查看是否有事务锁等待
select * from innodb_lock_waits;(因为本地数据的问题,所以没查出来,就不贴展示文本了)
3.查看是否有MDL锁等待
select * from schema_table_lock_waits;(因为本地数据的问题,所以没查出来,就不贴展示文本了)
4.查看InnoDB缓冲池中的热点数据
select * from innodb_buffer_stats_by_schema;展示文本
object_schema | allocated | data | pages | pages_hashed | pages_old | rows_cached |
|---|---|---|---|---|---|---|
shlz-cloud | 134.31 MiB | 122.53 MiB | 8596 | 635 | 4650 | 160672 |
scrm_junyi | 124.33 MiB | 106.27 MiB | 7957 | 0 | 2797 | 543521 |
xchatv | 44.03 MiB | 39.38 MiB | 2818 | 0 | 1994 | 58768 |
shlz-app | 30.42 MiB | 25.11 MiB | 1947 | 485 | 746 | 8421 |
mysql | 4.12 MiB | 2.63 MiB | 264 | 187 | 49 | 1763 |
shlz-config | 208.00 KiB | 32.70 KiB | 13 | 1 | 4 | 6 |
eaf_spbs | 80.00 KiB | 23.39 KiB | 5 | 0 | 5 | 246 |
sys | 16.00 KiB | 338 bytes | 1 | 0 | 0 | 6 |
5.查看冗余索引
select * from schema_redundant_indexes;展示文本(内容太多,我利用table_name做了分组)
table_schema | table_name | redundant_index_name | redundant_index_columns | redundant_index_non_unique | dominant_index_name | dominant_index_columns | dominant_index_non_unique | subpart_exists | sql_drop_index |
|---|---|---|---|---|---|---|---|---|---|
eaf_spbs | eaf_net_worth | unw_index_2 | account | 1 | unw_index_1 | account,date,is_valid | 1 | 0 | ALTER TABLE `eaf_spbs`.`eaf_net_worth` DROP INDEX `unw_index_2` |
shlz-app | im_message | index_im_4 | to_id | 1 | index_im_2 | to_id,type,state,send_time | 1 | 0 | ALTER TABLE `shlz-app`.`im_message` DROP INDEX `index_im_4` |
shlz-app | news_flash | index_nf_4 | displore_class_id | 1 | index_nf_6 | displore_class_id,is_show | 1 | 0 | ALTER TABLE `shlz-app`.`news_flash` DROP INDEX `index_nf_4` |
shlz-app | room_message | rm_index2 | room_id | 1 | rm_index4 | room_id,is_auth | 1 | 0 | ALTER TABLE `shlz-app`.`room_message` DROP INDEX `rm_index2` |
shlz-app | temp_date_day | index_tdd_1 | day,type | 1 | PRIMARY | day | 0 | 0 | ALTER TABLE `shlz-app`.`temp_date_day` DROP INDEX `index_tdd_1` |
shlz-app | user_custom_authorize | uca_index_3 | user_id | 1 | index_uca_7 | user_id,add_time | 1 | 0 | ALTER TABLE `shlz-app`.`user_custom_authorize` DROP INDEX `uca_index_3` |
shlz-app | user_login_day | index_uld_3 | user_id | 1 | index_uld_1 | user_id,login_day | 1 | 0 | ALTER TABLE `shlz-app`.`user_login_day` DROP INDEX `index_uld_3` |
shlz-app | user_login_log | index_ull_1 | user_id,status | 1 | index_ull_3 | user_id,status,add_time | 1 | 0 | ALTER TABLE `shlz-app`.`user_login_log` DROP INDEX `index_ull_1` |
shlz-app | users | u_index_2 | user_role | 1 | u_index_1 | user_role | 1 | 0 | ALTER TABLE `shlz-app`.`users` DROP INDEX `u_index_2` |
shlz-app | wx_mp_user | index_wmu_1 | unionid | 1 | index_wmu_3 | unionid,openid | 1 | 0 | ALTER TABLE `shlz-app`.`wx_mp_user` DROP INDEX `index_wmu_1` |
6.查看未使用的索引
select * from schema_unused_indexes;展示文本(内容太多,我利用object_schema做了分组)
object_schema | object_name | index_name |
|---|---|---|
shlz-app | user_access_log | ual_index_1 |
performance_schema | file_summary_by_instance | EVENT_NAME |
xchatv | user_access_log | ual_index_1 |
scrm_junyi | qrtz_triggers | sched_name |
shlz-seata | lock_table | idx_branch_id |
shlz-config | config_tags_relation | idx_tenant_id |
eaf_spbs | eaf_net_worth | unw_index_3 |
shlz-cloud | sys_oper_log | index_sol_1 |
7.查询表的增、 删、 改、 查数据量和I/O耗时统计信息
select * from schema_table_statistics_with_buffer;展示文本(内容太多,我利用table_schema做了分组)
table_schema | table_name | rows_fetched | fetch_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency | io_read_requests | io_read | io_read_latency | io_write_requests | io_write | io_write_latency | io_misc_requests | io_misc_latency | innodb_buffer_allocated | innodb_buffer_data | innodb_buffer_free | innodb_buffer_pages | innodb_buffer_pages_hashed | innodb_buffer_pages_old | innodb_buffer_rows_cached |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
shlz-cloud | sys_job_log | 24870251 | 1.63 min | 121310 | 8.16 s | 0 | 0 ps | 0 | 0 ps | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 108.48 MiB | 99.69 MiB | 8.80 MiB | 6943 | 534 | 4493 | 736639 |
shlz-app | im_message | 78808943 | 1.24 min | 180 | 32.74 ms | 113 | 13.76 ms | 0 | 0 ps | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 5.08 MiB | 4.37 MiB | 722.24 KiB | 325 | 237 | 0 | 10050 |
scrm_junyi | sys_oper_log | 888272 | 1.04 min | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 73325 | 1.12 GiB | 2.40 s | 0 | 0 bytes | 0 ps | 5 | 26.36 us | 41.73 MiB | 35.18 MiB | 6.55 MiB | 2671 | 0 | 434 | 31630 |
xchatv | news_flash | 2927820 | 52.73 s | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 17259 | 269.67 MiB | 926.45 ms | 0 | 0 bytes | 0 ps | 5 | 90.31 us | 28.44 MiB | 25.94 MiB | 2.50 MiB | 1820 | 0 | 1820 | 81988 |
shlz-config | config_info_beta | 242690 | 6.10 s | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 16.00 KiB | 0 bytes | 16.00 KiB | 1 | 0 | 0 | 0 |
eaf_spbs | eaf_net_worth | 8712 | 27.95 ms | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 36 | 576.00 KiB | 8.71 ms | 0 | 0 bytes | 0 ps | 5 | 29.71 us | 64.00 KiB | 23.34 KiB | 40.66 KiB | 4 | 0 | 4 | 245 |
sys | sys_config | 2 | 1.73 ms | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 3 | 48.00 KiB | 1.55 ms | 0 | 0 bytes | 0 ps | 5 | 90.85 us | 16.00 KiB | 338 bytes | 15.67 KiB | 1 | 0 | 0 | 6 |
mysql | dd_properties | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
performance_schema | session_variables | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
shlz-seata | branch_table | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
8.查看MySQL磁盘文件产生的磁盘流量与读写比例
select * from io_global_by_file_by_bytes;展示文本(内容太多,只截取了五条)
file | count_read | total_read | avg_read | count_write | total_written | avg_write | total | write_pct |
|---|---|---|---|---|---|---|---|---|
@@datadir/#ib_16384_0.dblwr | 1 | 96.00 KiB | 96.00 KiB | 2675099 | 77.74 GiB | 30.47 KiB | 77.74 GiB | 100.00 |
@@datadir/undo_002 | 585 | 9.19 MiB | 16.08 KiB | 1770756 | 27.02 GiB | 16.00 KiB | 27.03 GiB | 99.97 |
@@datadir/mysql.ibd | 1628 | 25.48 MiB | 16.03 KiB | 373672 | 5.70 GiB | 16.00 KiB | 5.73 GiB | 99.57 |
@@datadir/shlz@002dcloud/sys_job_log.ibd | 58880 | 920.00 MiB | 16.00 KiB | 127829 | 1.95 GiB | 16.00 KiB | 2.85 GiB | 68.46 |
@@datadir/shlz@002dapp/news_flash.ibd | 3248 | 50.75 MiB | 16.00 KiB | 139572 | 2.13 GiB | 16.00 KiB | 2.18 GiB | 97.73 |
9.查看哪些语句使用了全表扫描
select * from statements_with_full_table_scans;展示文本(内容太多,利用exec_count排序,取了前五条)
query | db | exec_count | total_latency | no_index_used_count | no_good_index_used_count | no_index_used_pct | rows_sent | rows_examined | rows_sent_avg | rows_examined_avg | first_seen | last_seen | digest |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
SELECT `username` , PASSWORD F … sers` WHERE ? = ? LIMIT ?, … | shlz-config | 161770 | 15.43 s | 161770 | 0 | 100 | 161770 | 161770 | 1 | 1 | 2022-06-15 11:50:03.309689 | 2022-07-13 13:57:07.094295 | 448ad42db6d4123f06c133085aea54acb8d8cd8f7d2fd75652950c0a4e67ba6b |
SELECT `rp` . `id` , `rp` . `u … DER BY `add_time` DESC LIMIT ? | shlz-app | 12005 | 24.52 s | 12005 | 0 | 100 | 120050 | 10445697 | 10 | 870 | 2022-06-20 14:45:35.970063 | 2022-07-07 17:08:29.639712 | 48e85b8149f59d2521982a1d21aec5cec377f9ef6ffb45443609be32dbddae7f |
SELECT COUNT ( * ) FROM `news_ … ` = ? AND `nc` . `deleted` = ? | shlz-app | 7630 | 6.30 s | 7629 | 0 | 100 | 7630 | 506103 | 1 | 66 | 2022-06-15 11:50:44.988513 | 2022-07-13 13:54:20.885428 | 0610a998821f02453b35e3c55d0fe9231054d798373157998076c414204454d0 |
SELECT `ni` . `id` , `ni` . `c … ` . `create_time` DESC LIMIT ? | shlz-app | 7588 | 6.98 s | 7588 | 0 | 100 | 60899 | 564248 | 8 | 74 | 2022-06-15 11:50:45.006153 | 2022-07-13 13:54:20.886305 | d0d17ca537b7b9cb90d13d69c7f50c9e7fe6f2269a001fa3db73b4268e69a88d |
SELECT `ni` . `id` , `ni` . `c … ` . `create_time` DESC LIMIT ? | shlz-app | 4147 | 4.01 s | 4147 | 0 | 100 | 124575 | 382923 | 30 | 92 | 2022-06-15 12:30:00.404331 | 2022-07-13 13:30:00.670492 | b040c11144ef50acd784b1a784c1c0002c09ba523e229489dcdd957774cb973b |
10.查看哪些语句使用了文件排序
select * from statements_with_sorting;展示文本(内容太多,利用exec_count排序,取了前五条)
query | db | exec_count | total_latency | sort_merge_passes | avg_sort_merges | sorts_using_scans | sort_using_range | rows_sorted | avg_rows_sorted | first_seen | last_seen | digest |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
SELECT `TRIGGER_NAME` , `TRIGG … RE_TIME` ASC , `PRIORITY` DESC | shlz-cloud | 242644 | 1.87 min | 0 | 0 | 242644 | 0 | 121772 | 1 | 2022-06-15 11:54:19.109312 | 2022-07-13 13:57:20.124521 | 7dfc7e7319ff2c9b2de56bad13807b68ed000f24224a465d8ccf0f21c2a5bd91 |
SELECT `rp` . `id` , `rp` . `u … DER BY `add_time` DESC LIMIT ? | shlz-app | 12005 | 24.52 s | 0 | 0 | 12005 | 0 | 120050 | 10 | 2022-06-20 14:45:35.970063 | 2022-07-07 17:08:29.639712 | 48e85b8149f59d2521982a1d21aec5cec377f9ef6ffb45443609be32dbddae7f |
SELECT `im` . `id` , `im` . `u … im` . `send_time` DESC LIMIT ? | shlz-app | 1400 | 21.06 s | 0 | 0 | 1400 | 0 | 14000 | 10 | 2022-06-15 15:04:32.351428 | 2022-07-04 10:44:33.198227 | c858eb38dd1f2943d05e5b90837de189d910a5d5bcbb049208aaef209e9a5774 |
SELECT `ni` . `id` , `ni` . `c … ` . `create_time` DESC LIMIT ? | shlz-app | 7588 | 6.98 s | 0 | 0 | 7588 | 0 | 60899 | 8 | 2022-06-15 11:50:45.006153 | 2022-07-13 13:54:20.886305 | d0d17ca537b7b9cb90d13d69c7f50c9e7fe6f2269a001fa3db73b4268e69a88d |
SELECT ( `cat` . `name` COLLAT … ) AS `NUMERIC_SCALE` , `col` . | xchatv | 3248 | 5.54 s | 0 | 0 | 3248 | 0 | 38528 | 12 | 2022-06-16 01:30:01.973607 | 2022-07-13 01:30:05.112628 | d729cc74b9ca9bf3f11493ec73f93430c3a0d05a09b1141a6180d4753d0e08ad |
11.查看哪些语句使用了临时表
select * from statements_with_temp_tables;展示文本(内容太多,利用exec_count排序,取了前五条)
query | db | exec_count | total_latency | memory_tmp_tables | disk_tmp_tables | avg_tmp_tables_per_query | tmp_tables_to_disk_pct | first_seen | last_seen | digest |
|---|---|---|---|---|---|---|---|---|---|---|
SELECT ( `cat` . `name` COLLAT … ) AS `NUMERIC_SCALE` , `col` . | xchatv | 3248 | 5.54 s | 3248 | 0 | 1 | 0 | 2022-06-16 01:30:01.973607 | 2022-07-13 01:30:05.112628 | d729cc74b9ca9bf3f11493ec73f93430c3a0d05a09b1141a6180d4753d0e08ad |
SELECT ( `cat` . `name` COLLAT … ) AS `NUMERIC_SCALE` , `col` . | scrm_junyi | 2464 | 3.76 s | 2464 | 0 | 1 | 0 | 2022-06-16 01:30:01.889884 | 2022-07-13 01:30:07.889022 | d729cc74b9ca9bf3f11493ec73f93430c3a0d05a09b1141a6180d4753d0e08ad |
SELECT ( `cat` . `name` COLLAT … ) AS `NUMERIC_SCALE` , `col` . | shlz-cloud | 1767 | 2.62 s | 1767 | 0 | 1 | 0 | 2022-06-16 01:30:01.669053 | 2022-07-13 01:30:05.967098 | d729cc74b9ca9bf3f11493ec73f93430c3a0d05a09b1141a6180d4753d0e08ad |
SELECT ( `cat` . `name` COLLAT … ` AS `DATABASE_COLLATION` FROM | xchatv | 1624 | 2.66 s | 1624 | 0 | 1 | 0 | 2022-06-16 01:30:01.980483 | 2022-07-13 01:30:05.114094 | 03a295ce56600f76e3e3fa79531d369b18dd573ee511f80982193430b4bf4c54 |
SELECT ( `cat` . `name` COLLAT … ` AS `DATABASE_COLLATION` FROM | scrm_junyi | 1232 | 1.73 s | 1232 | 0 | 1 | 0 | 2022-06-16 01:30:01.910130 | 2022-07-13 01:30:07.889862 | 03a295ce56600f76e3e3fa79531d369b18dd573ee511f80982193430b4bf4c54 |