Уроки MySQL | Как добавить несколько записей.Insert into. PDO

Опубликовано: 14.08.2017

видео Уроки MySQL | Как добавить несколько записей.Insert into. PDO

Модификация таблиц в MySQL

Да уж, 9 лет занимаюсь веб-разработками, а в оптимизации веб-сайтов и базы данных MySQL а именно, всякий раз нахожу себе много нового. Неувязка в том, что во время разработки трудно оценить все узенькие места, они появляются когда в базе возникают тыщи, 10-ки тыщ либо даже миллионы записей в таблицах.  Тут я собрал различные полезные фишки и советы, связанные с оптимизацией MySQL открытые мной в последнее время.




PROCEDURE ANALYSE()

Стандартная функция, которая анализирует поля таблицы в запросе и выводит советы по хорошей длине/типу полей. К примеру SELECT * FROM table PROCEDURE ANALYSE().

Понятно, что внедрение типа полей занимающего огромную память делает более неспешной работу с таблицами. Enum резвее чем Varchar, varchar резвее чем text, medium int резвее чем int и т.д. Нередко можно улучшить типы зависимо от данных в таблице.


SQL для начинающих (DML): Вставка данных в таблицу (MySql), Урок 3!


Определение не действенных индексов

Индексы в таблицах — большое благо, но не следует забывать что не считая того, что индексы ускоряют подборки из таблицы (SELECT) они замедляют обновление таблиц (UPDATE) и добавление новых полей (INSERT), потому что при каждом обновлении данных все индексы перестраиваются. Также индексы занимают место на диске. В таблицах где данные нередко обновляются/добавляются внедрение индексов должно быть равновесным.


Пишем на SQL без SQL! ► Урок по RedBeanPHP #1 ► Самая простая и мощная ORM для PHP! ► Основы

При помощи этого хитрецкого запроса можно узреть 10 самых малоэффективных индексов во всей базе данных:

SELECT t.TABLE_SCHEMA AS `db`, t.TABLE_NAME AS `table`, s.INDEX_NAME AS `inde name`, s.COLUMN_NAME AS `field name`, s.SEQ_IN_INDEX `seq in index`, s2.max_columns AS `# cols`, s.CARDINALITY AS `card`, t.TABLE_ROWS AS `est rows`, ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `sel %` FROM INFORMATION_SCHEMA.STATISTICS s INNER JOIN INFORMATION_SCHEMA.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, MAX(SEQ_IN_INDEX) AS max_columns FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA != ‘mysql’ GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) AS s2 ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA AND s.TABLE_NAME = s2.TABLE_NAME AND s.INDEX_NAME = s2.INDEX_NAME WHERE t.TABLE_SCHEMA != ‘mysql’ AND t.TABLE_ROWS > 10 AND s.CARDINALITY IS NOT NULL AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 ORDER BY `sel %`, s.TABLE_SCHEMA, s.TABLE_NAME LIMIT 10;

Взято отсюда: http://pastebin.com/f6b1c381c. Уберите LIMIT 10, чтоб узреть статистику по всем индексам.

EXPLAIN – Определение применяемых индексов и строк, которые необходимо обработать MySQL базе данных для выполнения запроса.

До боли просто, добавляем к нашему запросу слово EXPLAIN. К примеру: EXPLAIN SELECT * FROM table WHERE var=value ORDER BY field. Очень полезно для просмотра эффективности индексов.

Полный отказ от ORDER BY rand();

Конструкция ORDER BY rand(); отдаёт поля из таблицы в случайном порядке. Основная засада в том, что при выполнении этой конструкции не употребляются индексы и осуществляется полное сканирование таблицы. Уже при нескольких тыщах записей могут начаться трудности. Уместно переписать код, чтоб все записи брались из базы данных, добавлялись в массив и сортировались в массиве (shuffle в PHP). Если записей сильно много, имеет смысл сделать кеширование — к примеру делать случайную сортировку раз в час.

Отключить InnoDB если он не нужен

Это движки таблиц. MyISAM – резвее и проще, InnoDB – умеет много полезного, к примеру транзакции, отсутствие блокировок. Если все эти фишки не необходимы, то кто-то рекомендует оставаться на MyISAM, кто-то напротив перебегать на InnoDB. Я пока ещё этот вопрос себе не решил и использую MyISAM.

В любом случае если InnoDB не употребляется стоит отключить его поддержку, т.к. сервер ест лишнюю память и работает медленее. Для отключения добавляем skip-innodb в конфиг (/etc/mysql/my.cnf).

Неспешные запросы

Некие запросы производятся очень медлительно. Скорей всего о большинстве их вы и не подозреваете, т.к. вы тестировали когда в таблице было несколько сотен записей, а на данный момент там несколько 10-ов тыщ и т.д. Отыскать такие запросы, на которые стоит направить самое пристальное внимание, можно если включить логгирование Slow Queries. В конфиг файле (/etc/mysql/my.cnf) прописываем:

log_slow_queries = /var/log/mysql/mysql-bin.log
long_query_time = 2

2 – это количество секунд, больше которых выполняемый запрос будет считаться неспешным и добавляться в лог. Если добавить ещё строку log-queries-not-using-indexes в логи будут добавляться все запросы, для которых не применены индексы.

Потом перегружаем MySQL сервер (/etc/init.d/mysql restart) и в /var/log/mysql будут добавлятся файлы логов с неспешными запросами за последние 7 дней (по дефлоту).

Кеширование в MySQL

Стоит естественно писать скрпиты так, чтоб запросов в базу данных было как можно меньше, но часть ваших заморочек на себя может взять MySQL. Верная настройка кеширования приведёт к тому, что повторяющиеся запросы будут браться из кеша и производиться очень стремительно. У меня к примеру 75% запросов производится из кеша. Естественно, этот процент находится в зависимости от типа запросов, от размера самого кеша, от частоты запросов к базе данных и т.д. Обычно стандартная конфигурация не всегда подходит, т.к. для разной нагрузки необходимы различные опции. Чтоб выяснить что поменять, смотрим последующий пункт.

Автоматический тюнинг

При помощи обычный утилитки mysqltuner можно найти самые главные узенькие места в конфигурации MySQL.

Исполняем на сервере:

wget http://mysqltuner.com/mysqltuner.pl
chmod u+x mysqltuner.pl
./mysqltuner.pl

Дальше необходимо ввести root логин и пароль к MySQL серверу, а скрипт проанализирует данные и выведет советы в автоматическом режиме. Я меняю характеристики в конфиге, на которые необходимо направить внимание, а потом через некоторое количество дней смотрю снова.

rss