Оптимизация таблиц в MySQL

Сегодня расскажу о важном аспекте поддержания базы данных сайта в рабочем состоянии — оптимизация таблиц в базе данных. Как вам скорее всего известно, система управления базами данных MySQL позволяет создавать разные типы таблиц, но среди разработчиков сайтов 2 типа таблиц являются стандартами де-факто — это MyISAM и InnoDB. Об их оптимизации я и расскажу.

Для MyISAM есть стандартное ключевое слово для оптимизации — OPTIMIZE. Команда для оптимизации выглядит как OPTIMIZE `название_таблицы`. Для InnoDB такой команды нет и для такого типа таблиц следует использовать конструкцию ALTER TABLE $table ENGINE=`название_таблицы`

PhpMyAdmin позволяет обрабатывать все таблицы базы данных, но в случае больших таблиц вебсервер может не дождаться обработки и PhpMyAdmin зависнет. На такой случае мы можем составить bash-скрипты.

Для MyISAM он будет такой:

#!/bin/bash

cmd=» mysql -uroot -p$1 «
$cmd -e «show databases;» | sed ‘s/\|//g’ | sed -e ‘1d’ | while read db
do
    $cmd $db -e «show tables;» | sed ‘s/\|//g’ | sed -e ‘1d’ | while read table
    do
        echo «Optimizing table $db.$table\n»
        $cmd $db -e «OPTIMIZE `$table`;»
    done
done

А для InnoDB такой:

#!/bin/bash

cmd=» mysql -uroot -p$1 «
$cmd -e «show databases;» | sed ‘s/\|//g’ | sed -e ‘1d’ | while read db
do
    $cmd $db -e «show tables;» | sed ‘s/\|//g’ | sed -e ‘1d’ | while read table
    do
        echo «Optimizing table $db.$table\n»
        $cmd $db -e «ALTER TABLE `$table` ENGINE=’InnoDB’;»
    done
done

Запускаются скрипты так:

bash ./optimize.sh пароль_рут_пользователя_mysql