zabbix db 优化sql脚本

  1. --先检查
  2. --Count the amount of rows that would be deleted for all orphaned acknowledge entries
  3. SELECT COUNT(*) FROM acknowledges WHERE NOT userid IN (SELECT userid FROM users);
  4. SELECT COUNT(*) FROM acknowledges WHERE NOT eventid IN (SELECT eventid FROM events);
  6. --Count the amount of rows that would be deleted for orphaned alerts entries
  7. SELECT COUNT(*) FROM alerts WHERE NOT actionid IN (SELECT actionid FROM actions);
  8. SELECT COUNT(*) FROM alerts WHERE NOT eventid IN (SELECT eventid FROM events);
  9. SELECT COUNT(*) FROM alerts WHERE NOT userid IN (SELECT userid FROM users);
  10. SELECT COUNT(*) FROM alerts WHERE NOT mediatypeid IN (SELECT mediatypeid FROM media_type);
  12. --Count the amount of rows that would be deleted for orphaned application entries that no longer map back to a host
  13. SELECT COUNT(*) FROM applications WHERE NOT hostid IN (SELECT hostid FROM hosts);
  15. --Count the amount of rows that would be deleted for orphaned auditlog details (such as logins)
  16. SELECT COUNT(*) FROM auditlog_details WHERE NOT auditid IN (SELECT auditid FROM auditlog);
  17. SELECT COUNT(*) FROM auditlog WHERE NOT userid IN (SELECT userid FROM users);
  19. --Count the amount of rows that would be deleted for orphaned conditions
  20. SELECT COUNT(*) FROM conditions WHERE NOT actionid IN (SELECT actionid FROM actions);
  22. --Count the amount of rows that would be deleted for orphaned functions
  23. SELECT COUNT(*) FROM functions WHERE NOT itemid IN (SELECT itemid FROM items);
  24. SELECT COUNT(*) FROM functions WHERE NOT triggerid IN (SELECT triggerid FROM triggers);
  26. --Count the amount of rows that would be deleted for orphaned graph items
  27. SELECT COUNT(*) FROM graphs_items WHERE NOT graphid IN (SELECT graphid FROM graphs);
  28. SELECT COUNT(*) FROM graphs_items WHERE NOT itemid IN (SELECT itemid FROM items);
  30. --Count the amount of rows that would be deleted for orphaned host_profiles
  31. SELECT COUNT(*) FROM hosts_profiles WHERE NOT hostid IN (SELECT hostid FROM hosts);
  32. SELECT COUNT(*) FROM hosts_profiles_ext WHERE NOT hostid IN (SELECT hostid FROM hosts);
  34. --Count the amount of rows that would be deleted for orphaned host macro's
  35. SELECT COUNT(*) FROM hostmacro WHERE NOT hostid IN (SELECT hostid FROM hosts);
  37. -- Count the amount of rows that would be deleted for orphaned item data
  38. SELECT COUNT(*) FROM items WHERE hostid NOT IN (SELECT hostid FROM hosts);
  39. SELECT COUNT(*) FROM items_applications WHERE applicationid NOT IN (SELECT applicationid FROM applications);
  40. SELECT COUNT(*) FROM items_applications WHERE itemid NOT IN (SELECT itemid FROM items);
  42. -- Count the amount of rows that would be deleted for orphaned HTTP check data
  43. SELECT COUNT(*) FROM httpstep WHERE NOT httptestid IN (SELECT httptestid FROM httptest);
  44. SELECT COUNT(*) FROM httpstepitem WHERE NOT httpstepid IN (SELECT httpstepid FROM httpstep);
  45. SELECT COUNT(*) FROM httpstepitem WHERE NOT itemid IN (SELECT itemid FROM items);
  46. SELECT COUNT(*) FROM httptest WHERE applicationid NOT IN (SELECT applicationid FROM applications);
  48. -- Count the amount of rows that would be deleted for orphaned maintenance data
  49. SELECT COUNT(*) FROM maintenances_groups WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
  50. SELECT COUNT(*) FROM maintenances_groups WHERE groupid NOT IN (SELECT groupid FROM groups);
  51. SELECT COUNT(*) FROM maintenances_hosts WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
  52. SELECT COUNT(*) FROM maintenances_hosts WHERE hostid NOT IN (SELECT hostid FROM hosts);
  53. SELECT COUNT(*) FROM maintenances_windows WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
  54. SELECT COUNT(*) FROM maintenances_windows WHERE timeperiodid NOT IN (SELECT timeperiodid FROM timeperiods);
  56. -- Count the amount of rows that would be deleted for orphaned mappings
  57. SELECT COUNT(*) FROM mappings WHERE NOT valuemapid IN (SELECT valuemapid FROM valuemaps);
  59. -- Count the amount of rows that would be deleted for orphaned media / user items
  60. SELECT COUNT(*) FROM media WHERE NOT userid IN (SELECT userid FROM users);
  61. SELECT COUNT(*) FROM media WHERE NOT mediatypeid IN (SELECT mediatypeid FROM media_type);
  62. SELECT COUNT(*) FROM rights WHERE NOT groupid IN (SELECT usrgrpid FROM usrgrp);
  63. SELECT COUNT(*) FROM rights WHERE NOT id IN (SELECT groupid FROM groups);
  64. SELECT COUNT(*) FROM sessions WHERE NOT userid IN (SELECT userid FROM users);
  65. SELECT COUNT(*) FROM user_history WHERE NOT userid IN (SELECT userid FROM users);
  67. -- Count the amount of rows that would be deleted for orphaned screens
  68. SELECT COUNT(*) FROM screens_items WHERE screenid NOT IN (SELECT screenid FROM screens);
  70. -- Count the amount of rows that would be deleted for orphaned events & triggers
  71. SELECT COUNT(*) FROM trigger_depends WHERE triggerid_down NOT IN (SELECT triggerid FROM triggers);
  72. SELECT COUNT(*) FROM trigger_depends WHERE triggerid_up NOT IN (SELECT triggerid FROM triggers);
  74. -- Count the amount of records in the history/trends table for items that no longer exist
  75. SELECT COUNT(itemid) FROM history WHERE itemid NOT IN (SELECT itemid FROM items);
  76. SELECT COUNT(itemid) FROM history_uint WHERE itemid NOT IN (SELECT itemid FROM items);
  77. SELECT COUNT(itemid) FROM history_log WHERE itemid NOT IN (SELECT itemid FROM items);
  78. SELECT COUNT(itemid) FROM history_str WHERE itemid NOT IN (SELECT itemid FROM items);
  79. SELECT COUNT(itemid) FROM history_sync WHERE itemid NOT IN (SELECT itemid FROM items);
  80. SELECT COUNT(itemid) FROM history_text WHERE itemid NOT IN (SELECT itemid FROM items);
  82. SELECT COUNT(itemid) FROM trends WHERE itemid NOT IN (SELECT itemid FROM items);
  83. SELECT COUNT(itemid) FROM trends_uint WHERE itemid NOT IN (SELECT itemid FROM items);
 
  1. --再删除
  2. --Delete all orphaned acknowledge entries
  3. DELETE FROM acknowledges WHERE NOT userid IN (SELECT userid FROM users);
  4. DELETE FROM acknowledges WHERE NOT eventid IN (SELECT eventid FROM events);
  6. --Delete orphaned alerts entries
  7. DELETE FROM alerts WHERE NOT actionid IN (SELECT actionid FROM actions);
  8. DELETE FROM alerts WHERE NOT eventid IN (SELECT eventid FROM events);
  9. DELETE FROM alerts WHERE NOT userid IN (SELECT userid FROM users);
  10. DELETE FROM alerts WHERE NOT mediatypeid IN (SELECT mediatypeid FROM media_type);
  12. --Delete orphaned application entries that no longer map back to a host
  13. DELETE FROM applications WHERE NOT hostid IN (SELECT hostid FROM hosts);
  15. --Delete orphaned auditlog details (such as logins)
  16. DELETE FROM auditlog_details WHERE NOT auditid IN (SELECT auditid FROM auditlog);
  17. DELETE FROM auditlog WHERE NOT userid IN (SELECT userid FROM users);
  19. --Delete orphaned conditions
  20. DELETE FROM conditions WHERE NOT actionid IN (SELECT actionid FROM actions);
  22. --Delete orphaned functions
  23. DELETE FROM functions WHERE NOT itemid IN (SELECT itemid FROM items);
  24. DELETE FROM functions WHERE NOT triggerid IN (SELECT triggerid FROM triggers);
  26. --Delete orphaned graph items
  27. DELETE FROM graphs_items WHERE NOT graphid IN (SELECT graphid FROM graphs);
  28. DELETE FROM graphs_items WHERE NOT itemid IN (SELECT itemid FROM items);
  30. --Delete orphaed host_profiles
  31. DELETE FROM hosts_profiles WHERE NOT hostid IN (SELECT hostid FROM hosts);
  32. DELETE FROM hosts_profiles_ext WHERE NOT hostid IN (SELECT hostid FROM hosts);
  34. --Delete orphaned host macro's
  35. DELETE FROM hostmacro WHERE NOT hostid IN (SELECT hostid FROM hosts);
  37. -- Delete orphaned item data
  38. DELETE FROM items WHERE hostid NOT IN (SELECT hostid FROM hosts);
  39. DELETE FROM items_applications WHERE applicationid NOT IN (SELECT applicationid FROM applications);
  40. DELETE FROM items_applications WHERE itemid NOT IN (SELECT itemid FROM items);
  42. -- Delete orphaned HTTP check data
  43. DELETE FROM httpstep WHERE NOT httptestid IN (SELECT httptestid FROM httptest);
  44. DELETE FROM httpstepitem WHERE NOT httpstepid IN (SELECT httpstepid FROM httpstep);
  45. DELETE FROM httpstepitem WHERE NOT itemid IN (SELECT itemid FROM items);
  46. DELETE FROM httptest WHERE applicationid NOT IN (SELECT applicationid FROM applications);
  48. -- Delete orphaned maintenance data
  49. DELETE FROM maintenances_groups WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
  50. DELETE FROM maintenances_groups WHERE groupid NOT IN (SELECT groupid FROM groups);
  51. DELETE FROM maintenances_hosts WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
  52. DELETE FROM maintenances_hosts WHERE hostid NOT IN (SELECT hostid FROM hosts);
  53. DELETE FROM maintenances_windows WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
  54. DELETE FROM maintenances_windows WHERE timeperiodid NOT IN (SELECT timeperiodid FROM timeperiods);
  56. -- Delete orphaned mappings
  57. DELETE FROM mappings WHERE NOT valuemapid IN (SELECT valuemapid FROM valuemaps);
  59. -- Delete orphaned media / user items
  60. DELETE FROM media WHERE NOT userid IN (SELECT userid FROM users);
  61. DELETE FROM media WHERE NOT mediatypeid IN (SELECT mediatypeid FROM media_type);
  62. DELETE FROM rights WHERE NOT groupid IN (SELECT usrgrpid FROM usrgrp);
  63. DELETE FROM rights WHERE NOT id IN (SELECT groupid FROM groups);
  64. DELETE FROM sessions WHERE NOT userid IN (SELECT userid FROM users);
  65. DELETE FROM user_history WHERE NOT userid IN (SELECT userid FROM users);
  67. -- Screens
  68. DELETE FROM screens_items WHERE screenid NOT IN (SELECT screenid FROM screens);
  70. -- Events & triggers
  71. DELETE FROM trigger_depends WHERE triggerid_down NOT IN (SELECT triggerid FROM triggers);
  72. DELETE FROM trigger_depends WHERE triggerid_up NOT IN (SELECT triggerid FROM triggers);
  74. -- Count the amount of records in the history/trends table for items that no longer exist
  75. DELETE FROM history WHERE itemid NOT IN (SELECT itemid FROM items);
  76. DELETE FROM history_uint WHERE itemid NOT IN (SELECT itemid FROM items);
  77. DELETE FROM history_log WHERE itemid NOT IN (SELECT itemid FROM items);
  78. DELETE FROM history_str WHERE itemid NOT IN (SELECT itemid FROM items);
  79. DELETE FROM history_sync WHERE itemid NOT IN (SELECT itemid FROM items);
  80. DELETE FROM history_text WHERE itemid NOT IN (SELECT itemid FROM items);
  82. DELETE FROM trends WHERE itemid NOT IN (SELECT itemid FROM items);
  83. DELETE FROM trends_uint WHERE itemid NOT IN (SELECT itemid FROM items);
 





posted @ 2016-08-17 18:57  betterman.com  阅读(385)  评论(0)  编辑  收藏  举报