Устройство составного индекса
Чтобы правильно использовать составные индексы, необходимо понять структуру их хранения. Все работает точно так же, как и для обычного индекса. Но для значений используются значений всех входящих колонок сразу. Для таблицы с такими данными:
id | name | age | gender1 | Den | 29 | male2 | Alyona | 15 | female3 | Putin | 89 | tsar4 | Petro | 12 | maleзначения составного индекса будут такими:
age_gender12male15female29male89tsarЭто означает, что очередность колонок в индексе будет играть большую роль. Обычно колонки, которые используются в условиях WHERE, следует ставить в начало индекса. Колонки из ORDER BY — в конец.
Поиск по диапазону
Представим, что наш запрос будет использовать не сравнение, а поиск по диапазону:
SELECT * FROM users WHERE age <= 29 AND gender = 'male'Тогда MySQL не сможет использовать полный индекс, т.к. значения gender будут отличаться для разных значений колонки age. В этом случае база данных попытается использовать часть индекса (только age), чтобы выполнить этот запрос:
Сначала будут отфильтрованы все данные, которые подходят под условие age <= 29. Затем, поиск по значению "male" будет произведен без использования индекса.
Сортировка
Составные индексы также можно использовать, если выполняется сортировка:
SELECT * FROM users WHERE gender = 'male' ORDER BY ageВ этом случае нам нужно будет создать индекс в другом порядке, т.к. сортировка (ORDER) происходит после фильтрации (WHERE):
CREATE INDEX gender_age ON users(gender, age);Такой порядок колонок в индексе позволит выполнить фильтрацию по первой части индекса, а затем отсортировать результат по второй.
Колонок в индексе может быть больше, если требуется:
SELECT * FROM users WHERE gender = 'male' AND country = 'UA' ORDER BY age, register_timeВ этом случае следует создать такой индекс:
CREATE INDEX gender_country_age_register ON users(gender, country, age, register_time);Использование EXPLAIN для анализа индексов
Инструкция EXPLAIN покажет данные об использовании индексов для конкретного запроса. Например:
mysql> EXPLAIN SELECT * FROM users WHERE email = '[email protected]';+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 336 | Using where |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+Колонка key показывает используемый индекс. Колонка possible_keysпоказывает все индексы, которые могут быть использованы для этого запроса. Колонка rows показывает число записей, которые пришлось прочитать базе данных для выполнения этого запроса (в таблице всего 336 записей).
Как видим, в примере не используется ни один индекс. После создания индекса:
mysql> EXPLAIN SELECT * FROM users WHERE email = '[email protected]';+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------+| 1 | SIMPLE | users | const | email | email | 386 | const | 1 | |+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------+
Прочитана всего одна запись, т.к. был использован индекс.
Когда создавать индексы?
- Индексы следует создавать по мере обнаружения медленных запросов. В этом поможет slow log в MySQL. Запросы, которые выполняются более 1 секунды являются первыми кандидатами на оптимизацию.
- Начинайте создание индексов с самых частых запросов. Запрос, выполняющийся секунду, но 1000 раз в день наносит больше ущерба, чем 10-секундный запрос, который выполняется несколько раз в день.
- Не создавайте индексы на таблицах, число записей в которых меньше нескольких тысяч. Для таких размеров выигрыш от использования индекса будет почти незаметен.
- Не создавайте индексы заранее, например, в среде разработки. Индексы должны устанавливаться исключительно под форму и тип нагрузки работающей системы.
- Удаляйте неиспользуемые индексы.
Администрирование сервера.
1. Резервное копирование и восстановление информации;
Сложно переоценить необходимость создания резервной копии. Для начала обычно составляется план резервного копирования, что копировать, куда, как.
Способы создания бекапа:
А) Полный бекап.
Используем утилиту командной строки mysqldump. Параметров этой утилиты очень, очень много.
Создание: mysqldump –uroot –proot shop > backup.sql
Восстановление: mysql –uroot –proot shop< backup.sql
(запускать из под админа, в командной строке заходим в папку, где установлен сервер, с помощью команды >cd путь, затем запускаем mysqldump.exe, далее уже команда)
Б) Бекап таблицы.
Создание: mysqldump –uroot –proot shop продажи> C:\Program Files\MySQL\MySQL Server 5.6\bin\backup
2. Разделение прав пользователей;
Есть две команды создать пользователя и удалить пользователя. У каждого пользователя есть Логин, хост(место подключения, машина, сетевой путь) и пароль. С разных машин можно дать разные права одному и тому же пользователю. Имя пользователя состоит из двух частей его логин @ хост, в качестве хоста может быть имя, сетевой адрес или айпишник. Если не требуется айпишник, то ставим %
Пример: create user ‘fedya’@’%’
Вход: mysql –u fedya
Но ему пока ничего не доступно.
Задать пароль: SET Password FOR ‘fedya’@’%’ = PASSWORD(‘root’);
Сам по себе сервер mysql хранит логины и пароли в отдельной БД mysql.user
Задать пароль можно простым обновлением записи в этой базе данных.
Можно создать пользователя и установить ему пароль сразу же:
create user ‘vasya’@’%’ IDENTIFIED BY ‘root’;
Для управления привилегиями пользователей используется команда:
GRANT операция_которую_можно_делать
ON Таблица
TO USER
Доступ можно дать к таблице, функции, вьюшке, к колонке.
Например,
GRANT SELECT
ON SHOP.*
TO ‘fedya’@’%’
Или
GRANT SELECT, INSERT
ON SHOP.*
TO ‘fedya’@’%’
Или на конкретную таблицу:
GRANT SELECT, INSERT
ON SHOP.Продажи
TO ‘fedya’@’%’
Забрать права:
REVOKE операция_которую_нужно_забрать
ON Таблица
FROM USER
Запрос тормоз:
SELECT COUNT(*) FROM CITI C1 WHERE EXISTS(SELECT * FROM CITY C2 WHERE C1.NAME = C2.NAME)
Запрос создание таблицы на др движке
CREATE TABLE CITY1 ENGINE=MYISAM SELECT * FROM WORLD.CITY;
CREATE TABLE CITY2 ENGINE=INNODB SELECT * FROM WORLD.CITY;