Zufällige Einfügungen in oder Löschungen aus einem sekundären Index können dazu führen, dass der Index fragmentiert wird. Fragmentierung bedeutet, dass die physische Reihenfolge der Indexseiten auf der Platte nicht der Indexreihenfolge der Datensätze auf den Seiten entspricht oder dass viele ungenutzte Seiten in den 64-Seiten-Blöcken, die dem Index zugewiesen wurden, vorhanden sind.

Ein Symptom der Fragmentierung ist, dass eine Tabelle mehr Platz benötigt, als sie "sollte". Wie viel das genau ist, ist schwer zu bestimmen. Alle InnoDB-Daten und -Indizes werden in B-Trees gespeichert und ihr Füllfaktor kann von 50 % bis 100 % variieren. Ein weiteres Symptom der Fragmentierung ist, dass ein Tabellenscan wie dieser mehr Zeit in Anspruch nimmt, als er "sollte".

Speicherbelegung von MySQL-DB auf der Platte

• https://dev.mysql.com/doc/refman/5.7/en/innodb-file-defragmenting.html
  • ALTER TABLE tabellen_name ENGINE=INNODB
  • ALTER TABLE tabellen_name FORCE

alle Tabellen in diesem DBMS optimieren (u.a. defragmentieren) → OPTIMIZE TABLE:

/root/bin/OPTIMIZE_TABLE_aller_Tabellen_in_allen_Datenbanken.sh

#!/bin/bash
 
#------------------------------------------------------------------------------#
#
# "OPTIMIZE TABLE" auf allen Tabellen, in allen Datenbanken, dieses DBMS
#
#------------------------------------------------------------------------------#
 
#VERSION="v2021110100"
VERSION="v2022052400"
 
DATENBANKEN="$(echo "SHOW DATABASES;" | mysql -N | grep -Ev '^information_schema$|^sys$')"
DB_ANZAHL="$(echo "${DATENBANKEN}" | wc -l | awk '{print $1}')"
 
 
echo "${DATENBANKEN}" | nl | while read NR DB_NAME
do
        for TAB in $(echo "SHOW TABLES;" | mysql -N ${DB_NAME})
        do
                echo "# ${NR}/${DB_ANZAHL} '${DB_NAME}'.'${TAB}'" | tee /tmp/OPTIMIZE_TABLE_-_alle_Tabellen_aus_allen_Datenbanken.status
                echo "OPTIMIZE TABLE ${TAB};" | mysql -t ${DB_NAME}
        done
done

nur die fragmentierten Tabellen optimieren:

/root/bin/OPTIMIZE_TABLE.sh

#!/bin/bash
 
#------------------------------------------------------------------------------#
#
# nur die fragmentierten Tabellen in diesem DBMS optimieren
#
#------------------------------------------------------------------------------#
 
#VERSION="v2017040500"
VERSION="v2021112000"
 
#MYSQL_PROG="mysql --defaults-file=/etc/mysql/debian.cnf -N"
#MYSQL_PROG="mysql --defaults-file=/root/.my.cnf -N"
MYSQL_PROG="mysql -N"
 
for DB_TAB in $(echo "SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') and Data_free > 0;" | ${MYSQL_PROG} | sed "s/^[ \t][ \t]*//;s/[ \t][ \t]*/./")
do
        DB="$(echo "${DB_TAB}" | awk -F'.' '{print $1}')"
        TAB="$(echo "${DB_TAB}" | awk -F'.' '{print $2}')"
        echo "### echo 'OPTIMIZE TABLE ${TAB};' | ${MYSQL_PROG} ${DB}"
        echo "OPTIMIZE TABLE ${TAB};" | ${MYSQL_PROG} ${DB}
done

Quelle: https://www.thomas-krenn.com/de/wiki/MySQL_Optimize_Table

Wenn eine InnoDB-Tabelle (innodb_file_per_table) einmal sehr groß war, wird sie auch nicht wieder kleiner, wenn man die Daten rauslöscht.
Um den leeren Speicherplatz wieder frei zu geben, muss folgendes gemacht werden:

> ls -rtlha /var/lib/mysql/datenbank/tabelle.ibd
-rw-rw---- 1 mysql mysql 72G Mär 10 16:15 /var/lib/mysql/datenbank/tabelle.ibd

> mysqlcheck -B datenbank
> echo "OPTIMIZE TABLE log;" | mysql -t datenbank

> ls -rtlha /var/lib/mysql/datenbank/tabelle.ibd
-rw-rw---- 1 mysql mysql 28G Mär 11 08:29 /var/lib/mysql/datenbank/tabelle.ibd

ACHTUNG!
Bis Version 5.6.17 wird die Tabelle, von OPTIMIZE TABLE, komplett gelockt.

Sollte das Vorgehen wegen eines Fehlers abbrechen, dann bleibt nur noch, die Tabelle in einen Bump zu schreiben, zu löschen und wieder zurück zu spielen:

> echo "STOP SLAVE;" | mysql -hdbhost01 datenbank
> mysqldump --set-gtid-purged=OFF -QEc --opt --triggers --routines -hdbhost01 datenbank log | gzip -1 > /var/tmp/dbhost01_datenbank_tabelle_2016-03-10_1716.sql.gz
> zcat /var/tmp/vdbhost01_datenbank_tabelle_2016-03-10_1716.sql.gz | mysql -hdbhost01 datenbank
> mysqlshow -hdbhost01 datenbank tabelle
> echo "START SLAVE;" | mysql -hdbhost01 datenbank