Тестирование производительность InnoDB и MyIASM

Наибольший интерес для web-разработчика составляют innodb и myisam. Сейчас мы проведем сравнительный тест производительности этих типов таблиц. Для этого сначала создадим две одинаковые по структуре таблицы, но с разным типом движка хранения:

CREATE TABLE `inno` (`id` BIGINT(20) NOT NULL AUTO_INCREMENT, `data` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB; CREATE TABLE `myisam` (`id` BIGINT(20) NOT NULL AUTO_INCREMENT, `data` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM;

Попробуем вставку данных, выборку по ключу, выборку по не ключевому полю и посмотрим разницу во времени.

Триггер — это хранимая процедура, которая не вызывается непосредственно, а исполняется при наступлении определенного события (вставка, удаление, обновление строки).

Поддержка триггеров в MySQL началась с версии 5.0.2

Применение

Лог

— таблица, за которой мы будем следить
CREATE TABLE `test` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`content` TEXT NOT NULL
) ENGINE = MYISAM
— лог
CREATE TABLE `log` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`msg` VARCHAR(255) NOT NULL,
`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`row_id` INT(11) NOT NULL
) ENGINE = MYISAM
— триггер
DELIMITER |
CREATE TRIGGER `update_test` AFTER INSERT ON `test`
FOR EACH ROW BEGIN
INSERT INTO log Set msg = 'insert', row_id = NEW.id;
END

Теперь добавьте запись в таблицу test. В таблице log тоже появится запись, обратите внимание на поле row_id, в нем хранится id вставленной вами строки.

 

 

Индексы.

Индексы являются хорошим инструментом для оптимизации SQL запросов. Чтобы понять, как они работают, посмотрим на работу с данными без них.

Чтение данных с диска

На жестком диске нет такого понятия, как файл. Есть понятие блок. Один файл обычно занимает несколько блоков. Каждый блок знает, какой блок идет после него. Файл делится на куски и каждый кусок сохраняется в пустой блок.

При чтении файла, мы по очереди проходимся по всем блокам и собираем файл из кусков. Блоки одного файла могут быть раскиданы по диску (фрагментация). Тогда чтение файла замедлится, т.к. понадобится прыгать разным участкам диска.

Когда мы ищем что-то внутри файла, нам понадобится пройтись по всем блокам, в которых он сохранен. Если файл очень большой, то и количество блоков будет значительным. Необходимость перепрыгивать с блока на блок, которые могут находиться в разных местах, сильно замедлит поиск данных.

Поиск данных в MySQL

Таблицы MySQL — это обычные файлы. Выполним запрос такого вида:

SELECT * FROM users WHERE age = 29

MySQL при этом открывает файл, где хранятся данные из таблицы users. А дальше — начинает перебирать весь файл, чтобы найти нужные записи.

Кроме этого, MySQL будет сравнивать данные в каждой строке таблицы со значением в запросе. Допустим работа ведется с таблицей, в которой есть 10 записей. Тогда MySQL прочитает все 10 записей, сравнит колонку age каждой из них со значением 29 и отберет только подходящие данные:

Итак, есть две проблемы при чтении данных:

• Низкая скорость чтения файлов из-за расположения блоков в разных частях диска (фрагментация).
• Большое количество операций сравнения для поиска нужных данных.

Сортировка данных

Представим, что мы отсортировали наши 10 записей по убыванию. Тогда используя алгоритм бинарного поиска, мы могли бы максимум за 4 операции отобрать нужные нам значения:

Кроме меньшего количества операций сравнения, мы сэкономили бы на чтении ненужных записей.

Индекс — это и есть отсортированный набор значений. В MySQL индексы всегда строятся для какой-то конкретной колонки. Например, мы могли бы построить индекс для колонки age из примера.

Выбор индексов в MySQL

В самом простом случае, индекс необходимо создавать для тех колонок, которые присутствуют в условии WHERE.

Рассмотрим запрос из примера:

SELECT * FROM users WHERE age = 29

Нам необходимо создать индекс на колонку age:

CREATE INDEX age ON users(age);

После этой операции MySQL начнет использовать индекс age для выполнения подобных запросов. Индекс будет использоваться и для выборок по диапазонам значений этой колонки:

SELECT * FROM users WHERE age < 29 Сортировка

Для запросов такого вида:

SELECT * FROM users ORDER BY register_date

действует такое же правило — создаем индекс на колонку, по которой происходит сортировка:

CREATE INDEX register_date ON users(register_date); Внутренности хранения индексов

Представим, что наша таблица выглядит так:

id | name | age 1 | Den | 292 | Alyona | 153 | Putin | 894 | Petro | 12

После создания индекса на колонку age, MySQL сохранит все ее значения в отсортированном виде:

age index 12152989

Кроме этого, будет сохранена связь между значением в индексе и записью, которой соответствует это значение. Обычно для этого используется первичный ключ:

age index и связь с записями 12: 415: 229: 189: 3

Уникальные индексы

MySQL поддерживает уникальные индексы. Это удобно для колонок, значения в которых должны быть уникальными по всей таблице. Такие индексы улучшают эффективность выборки для уникальных значений. Например:

SELECT * FROM users WHERE email = '[email protected]';

На колонку email необходимо создать уникальный индекс:

CREATE UNIQUE INDEX email ON users(email)

Тогда при поиске данных, MySQL остановится после обнаружения первого соответствия. В случае обычного индекса будет обязательно проведена еще одна проверка (следующего значения в индексе).

Составные индексы

MySQL может использовать только один индекс для запроса. Поэтому, для запросов, в которых используется несколько колонок, необходимо использовать составные индексы.

Рассмотрим такой запрос:

SELECT * FROM users WHERE age = 29 AND gender = 'male'

Нам следует создать составной индекс на обе колонки:

CREATE INDEX age_gender ON users(age, gender);