四爷yueliangdao0608.blog.chinaunix.net

逆境,是上帝帮你淘汰竞争者的地方!

  • 博客访问: 3543845
  • 博文数量: 253
  • 博客积分: 11504
  • 博客等级: 上将
  • 技术积分: 4307
  • 用 户 组: 普通用户
  • 注册时间: 2006-12-28 14:24
文章分类

全部博文(253)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: Mysql/postgreSQL

在说明系统数据库之前,先来看下MySQL在数据字典方面的演变历史:
MySQL4.1 提供了information_schema 数据字典。从此可以很简单的用SQL语句来检索需要的系统元数据了。
MySQL5.5 提供了performance_schema 性能字典。 但是这个字典比较专业,一般人可能也就看看就不了了之了。
MySQL5.7 提供了 sys系统数据库。 sys数据库里面包含了一系列的存储过程、自定义函数以及视图来帮助我们快速的了解系统的元数据信息。


sys系统数据库结合了information_schema和performance_schema的相关数据,让我们更加容易的检索元数据。 现在呢,我就示范下几种场景下如何快速的使用。


第一,
比如之前想要知道某个表是否存在与否,可以用以下两种方法:
A, 悲观的方法,写SQL从information_schema中拿信息:



点击(此处)折叠或打开

  1. mysql> SELECT IF(COUNT(*) = 0,'Not exists!','Exists!') AS 'result' FROM information_schema.tables WHERE table_schema = 'new_feature' AND table_name = 't1';
  2. +-------------+
  3. | result |
  4. +-------------+
  5. | Not exists! |
  6. +-------------+
  7. 1 row in set (0.00 sec)



B,乐观的方法,假设表存在,写一个存储过程:


点击(此处)折叠或打开

  1. DELIMITER $$


  2. USE `new_feature`$$


  3. DROP PROCEDURE IF EXISTS `sp_table_exists`$$


  4. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_table_exists`(
  5.     IN db_name VARCHAR(64),
  6.     IN tb_name VARCHAR(64),
  7.     OUT is_exists VARCHAR(60)
  8.     )
  9. BEGIN
  10.       DECLARE no_such_table CONDITION FOR 1146;
  11.       DECLARE EXIT HANDLER FOR no_such_table
  12.       BEGIN
  13.         SET is_exists = 'Not exists!';
  14.       END;
  15.       
  16.       SET @stmt = CONCAT('select 1 from ',db_name,'.',tb_name);
  17.       PREPARE s1 FROM @stmt;
  18.       EXECUTE s1;
  19.       DEALLOCATE PREPARE s1;
  20.       SET is_exists = 'Exists!';
  21.     END$$


  22. DELIMITER ;



现在来调用:

点击(此处)折叠或打开

  1. mysql> call sp_table_exists('new_feature','t1',@result);
  2. Query OK, 0 rows affected (0.00 sec)


  3. mysql> select @result;
  4. +-------------+
  5. | @result |
  6. +-------------+
  7. | Not exists! |
  8. +-------------+
  9. 1 row in set (0.00 sec)



现在我们直接用sys数据库里面现有的存储过程来进行调用,

点击(此处)折叠或打开

  1. mysql> CALL table_exists('new_feature','t1',@v_is_exists);
  2. Query OK, 0 rows affected (0.00 sec)


  3. mysql> SELECT IF(@v_is_exists = '','Not exists!',@v_is_exists) AS 'result';
  4. +-------------+
  5. | result |
  6. +-------------+
  7. | Not exists! |
  8. +-------------+
  9. 1 row in set (0.00 sec)




第二,获取没有使用过的索引。



点击(此处)折叠或打开

  1. mysql> SELECT * FROM schema_unused_indexes;
  2. +---------------+-------------+--------------+
  3. | object_schema | object_name | index_name |
  4. +---------------+-------------+--------------+
  5. | new_feature | t1 | idx_log_time |
  6. | new_feature | t1 | idx_rank2 |
  7. +---------------+-------------+--------------+
  8. 2 rows in set (0.00 sec)




第三, 检索指定数据库下面的表扫描信息,过滤出执行次数大于10的查询,

点击(此处)折叠或打开

  1. mysql> SELECT * FROM statement_analysis WHERE db='new_feature' AND full_scan = '*' AND exec_count > 10\G
  2. *************************** 1. row ***************************
  3.             query: SHOW STATUS
  4.                db: new_feature
  5.         full_scan: *
  6.        exec_count: 26
  7.         err_count: 0
  8.        warn_count: 0
  9.     total_latency: 74.68 ms
  10.       max_latency: 3.86 ms
  11.       avg_latency: 2.87 ms
  12.      lock_latency: 4.50 ms
  13.         rows_sent: 9594
  14.     rows_sent_avg: 369
  15.     rows_examined: 9594
  16. rows_examined_avg: 369
  17.     rows_affected: 0
  18. rows_affected_avg: 0
  19.        tmp_tables: 0
  20.   tmp_disk_tables: 0
  21.       rows_sorted: 0
  22. sort_merge_passes: 0
  23.            digest: 475fa3ad9d4a846cfa96441050fc9787
  24.        first_seen: 2015-11-16 10:51:17
  25.         last_seen: 2015-11-16 11:28:13
  26. *************************** 2. row ***************************
  27.             query: SELECT `state` , `round` ( SUM ... uration (summed) in sec



第四, 同样继续上面的,过滤出有临时表的查询,



点击(此处)折叠或打开

  1. mysql> SELECT * FROM statement_analysis WHERE db='new_feature' AND tmp_tables > 0 ORDER BY tmp_tables DESC LIMIT 1\G
  2. *************************** 1. row ***************************
  3.             query: SELECT `performance_schema` . ... name` . `SUM_TIMER_WAIT` DESC
  4.                db: new_feature
  5.         full_scan: *
  6.        exec_count: 2
  7.         err_count: 0
  8.        warn_count: 0
  9.     total_latency: 87.96 ms
  10.       max_latency: 59.50 ms
  11.       avg_latency: 43.98 ms
  12.      lock_latency: 548.00 us
  13.         rows_sent: 101
  14.     rows_sent_avg: 51
  15.     rows_examined: 201
  16. rows_examined_avg: 101
  17.     rows_affected: 0
  18. rows_affected_avg: 0
  19.        tmp_tables: 332
  20.   tmp_disk_tables: 15
  21.       rows_sorted: 0
  22. sort_merge_passes: 0
  23.            digest: ff9bdfb7cf3f44b2da4c52dcde7a7352
  24.        first_seen: 2015-11-16 10:24:42
  25.         last_seen: 2015-11-16 10:24:42
  26. 1 row in set (0.01 sec)


可以看到上面查询详细的详细,再也不用执行show status 手工去过滤了。




第五, 检索执行次数排名前五的语句,

点击(此处)折叠或打开

  1. mysql> SELECT statement,total FROM user_summary_by_statement_type WHERE `user`='root' ORDER BY total DESC LIMIT 5;
  2. +-------------------+-------+
  3. | statement | total |
  4. +-------------------+-------+
  5. | jump_if_not | 17635 |
  6. | freturn | 3120 |
  7. | show_create_table | 289 |
  8. | Field List | 202 |
  9. | set_option | 190 |
  10. +-------------------+-------+
  11. 5 rows in set (0.01 sec)


示例我就写这么多了,详细的去看使用手册并且自己摸索去吧。
阅读(5652) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册