Процедурка для анализа/оптимизации всех таблиц в одной или во всех базах данных в пределах mySQL-сервера:

CREATE PROCEDURE `mysql_maintenance_all_tables`(
        IN db_name VARCHAR(64),
        IN do_optimize TINYINT(1),
        IN do_analyze TINYINT(1)
    )
   NOT DETERMINISTIC
   CONTAINS SQL
   SQL SECURITY DEFINER
BEGIN
  DECLARE l_loop_end INT DEFAULT 0;
  DECLARE IN_TABLE_SCHEMA VARCHAR(100);
  DECLARE IN_TABLE_NAME VARCHAR(100);
  DECLARE cur1 CURSOR FOR
    SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.TABLES WHERE
      /* if db_name is not provided, it will analyze/optimize all tables except the system ones */
      IF(db_name IS NULL OR db_name='', TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql'), TABLE_SCHEMA=db_name);

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET l_loop_end = 1;
  OPEN cur1;
  REPEAT
    FETCH cur1 INTO IN_TABLE_SCHEMA,IN_TABLE_NAME;
    IF NOT l_loop_end THEN
      IF (do_optimize) THEN
        SET @qry = CONCAT('OPTIMIZE TABLE ', IN_TABLE_SCHEMA, '.', IN_TABLE_NAME);
        PREPARE stmt FROM @qry;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
      END IF;
      IF (do_analyze) THEN
        SET @qry = CONCAT('ANALYZE TABLE ', IN_TABLE_SCHEMA, '.', IN_TABLE_NAME);
        PREPARE stmt FROM @qry;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
      END IF;
    END IF;
  UNTIL l_loop_end END REPEAT;
  CLOSE cur1;
END;

Я редко использую эту процедуру при помощи прямого вызова, предпочитая оптимизировать всё автоматически, через определённые промежутки времени.

Вешаем вызов процедуры оптимизации на планировщик событий:

CREATE EVENT `scheduler_daily_optimize`
  ON SCHEDULE EVERY 1 DAY STARTS '2012-05-07 05:15:15'
  ON COMPLETION NOT PRESERVE
  ENABLE DO
BEGIN
  /* и анализ и оптимизация для всех таблиц базы "my_db_name"*/
  CALL mysql_maintenance_all_tables('my_db_name', 1, 1);
END;

Таким образом процесс оптимизации будет запускаться ежедневно, в 5 утра 15 минут 15 секунд.

Если кто-то захочет поюзать описанный функционал, но ещё никогда не использовал Scheduled Events, не забываем включить его в конфигурации mySQL. Прописываем следующую строку в my.cnf (my.ini в Windows):

event_scheduler = ON

и перезапускаем mysql

/etc/init.d/mysql restart

Кто поюзает вышеописанное — ставьте лайк:)

————————————

This document has been copied from FAVOR.com.ua (https://favor.com.ua/en/blogs/8544.html).
All rights reserved by author of the material. In case of re-publication, the link to the source of the material is strongly required!
Document date: May 7, 2012