Индексирование таблиц MySQL: создание индекса MySQL, удалить индекс и другие операции. Индексы оптимизация mysql

Индексы MySQL: Создание индексов, удаление, настройки

От правильно составленной структуры базы данных очень сильно зависит скорость работы всего проекта. Еще одним инструментом, позволяющим значительно сократить время отклика базы, являются индексы БД MySQL. Перед тем, как рассматривать различные операции с ними, стоит определиться, что же такое индексы СУБД и какие преимущества можно получить при их использовании.

Индексы часто используются на высоконагруженных проектах и если Ваш сайт вырос из виртуального хостинга, то потребуется аренда VPS сервера.

Индексы – это специфические объекты базы данных, позволяющие значительно повысить скорость поиска значений из таблиц базы данных. MySQL index представляет из себя структуру, в которой хранятся значения одного (в некоторых случаях - нескольких) столбца таблицы и ссылок на строки, где эти значения расположены. Так как для хранения индексов чаще всего используются бинарные деревья, поиск среди них занимает чрезвычайно мало места.

Принцип работы индексов очень прост. Для примера рассмотрим запрос: SELECT Name FROM Persons WHERE Points

У нас имеется таблица Persons, в которой есть два поля: Name и Points. При поиске в неиндексированной таблице, система последовательно перебирает все строки и сравнивает их с 10. При выполнении условия – выводит их. Даже при наличии в таблице всего 1000 записей, такой запрос может оказаться ресурсоемким, если одновременно поступит от 100 абонентов. Индексирование таблиц MySQL позволяет сократить число операций. Если поле Points будет проиндексировано, поиск будет проводиться по самому индексу, без перебора всех строк таблицы.

Индексы бывают двух видов:

Простые. Одному полю таблицы соответствует один индекс.
Составные. Один индекс соответствует нескольким полям таблицы.
Покрывающий. Этот вид индексов создается специально под определенный запрос и включает в себя все поля таблицы, фигурирующие в запросе.

Индексирование таблиц MySQL

Использование индексирования таблиц MySQL имеет свои особенности:

Индексировать есть смысл только определенные поля таблицы. Нередки случаи, когда неопытные пользователи индексируют всю БД. На практике такой шаг может привести к обратному результату: в MySQL индексы негативно влияют на скорость вставки и обновления данных, так как при этих операциях возникает необходимость изменять данные в информационной части индекса.
Операции индексирования полей таблиц БД должна быть проделана работа по анализу наиболее частых запросов к БД. Только в этом случае использование индексов позволит оптимизировать работу БД. Выявить наиболее медленные запросы к БД поможет slow log (сохраняет в специальный файл запросы, время выполнения которых превысило заданное пользователем значение). Включить его в MySQL можно, отредактировав конфигурационный файл.
Использование одного составного индекса вместо нескольких односоставных для ускорения наиболее активно использующихся запросов также ускорит скорость работы с БД.
Покрывающие индексы позволяют получать результат вообще без обращения к таблице БД. Иногда добавление дополнительного проиндексированного поля позволяет существенно сократить время обработки запросов.

В MySQL индексы с низкой селективностью (чем больше различных значений принимает параметр, тем выше его селективность) зачастую оказываются неэффективными.
В запросах, где используется LIKE %, индексы не используются, даже если поле проиндексировано. Например, при таком запросе: LIKE(SELECT Name FROM Persons WHERE Points LIKE '%10')

Определившись с назначением и особенностями использования индексов СУБД, перейдем к основным операциям с ними.

Создание индекса в MySQL

Для операции создания индекса MySQL предусматривает такой синтаксис:CREATE [UNIQUE | FULLTEXT] INDEX ON ( [(length)],... ) где:

[UNIQUE | FULLTEXT] – определяет, будет ли индекс содержать только уникальные значения (UNIQUE), или в нем будут присутствовать и повторяющиеся значения (FULLTEXT). По умолчанию используется режим FULLTEXT. Length – определяет длину символов поля для индексирования. Если Length оставить пустым, то в индекс попадет поле целиком вне зависимости от длины.

- уникальный идентификатор индекса. Если это поле не определено, ему будет присвоено имя первого подлежащего индексации столбца.

В версиях MySQL младше 3.22 эта команда не активна, а в более поздних – в плане создания индексов работает аналогично команде ALTER TABLE. При работе с ALTER TABLE добавление записей происходит при помощи команды ADD INDEX, MySQL при помощи этой команды позволяет создавать индексы PRIMARY KEY (создать индекс такого типа при помощи CREATE INDEX нельзя).

Удаление индекса в MySQL

В MySQL удалить индекс можно при помощи такого оператора:DROP INDEX ON

Индексирование таблиц MySQL – обширная тема, а умение правильно работать с MySQL index – целое искусство. Администраторы из RigWEB все знают особенности индексирования MySQL на хостинге, VPS и выделенных серверах и умеют применять свои знания на практике, поэтому если у Вас остались вопросы - обращайтесь к нам!

Заказать VDS

VPS/VDS за 375 руб. от RigWEB.RU с администрированием

VPS/VDS серверы

Помогла ли вам статья?

Да Нет 8

раз уже помогла

Please enable JavaScript to view the comments powered by Disqus.

rigweb.ru

Оптимизация индексов таблицы mysql

У меня есть следующая ситуация и вы хотите выбрать оптимальные индексы (некоторые индексы синглов и несколько индексов).

Row Номер: 14000000

Таблица: Добавить

CREATE TABLE `Add` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(255) NOT NULL, `Description` longtext, `IDUser` int(11) NOT NULL, `DateAdded` datetime(4) DEFAULT NULL, `IDCategory` int(11) NOT NULL, `RowVersion` datetime NOT NULL, `IDStatus` int(11) DEFAULT '3', `IDModeration` int(11) DEFAULT '4', `Price` double DEFAULT NULL, `DateFrom` datetime DEFAULT NULL, `DateTo` datetime DEFAULT NULL, `City` varchar(50) DEFAULT NULL, `IDRegion` int(11) DEFAULT NULL, `IDCity` int(11) DEFAULT NULL, `IDNeighbourhood` int(11) DEFAULT NULL, `DateAccepted` datetime(4) DEFAULT NULL, `DateUpdated` datetime DEFAULT NULL, `latitude` double DEFAULT NULL, `longitude` double DEFAULT NULL, `ShowLandLine` tinyint(1) DEFAULT NULL, `DateUpToDated` datetime(4) DEFAULT NULL, `Telephone` varchar(50) DEFAULT NULL, `UserType` tinyint(1) DEFAULT NULL, `Neighbourhood` varchar(255) DEFAULT NULL, `IP` varchar(16) DEFAULT NULL, `FullEditToken` varchar(32) DEFAULT NULL, `IDSource` tinyint(4) DEFAULT NULL, `ExpiredAt` datetime DEFAULT NULL, `BadWordsFlag` tinyint(4) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `IX_Add_DateFrom` (`DateFrom`), KEY `IX_Add_IDUser` (`IDUser`), KEY `IX_Add_DateUpdated` (`DateUpdated`), KEY `IX_Add_DateUpToDated` (`DateUpToDated`), KEY `IX_Add_DateTo` (`DateTo`), KEY `IX_Add_ON_IDUser_IDStatus_IDModeratiON_DateFrom_DateTo` (`IDUser`,`IDStatus`,`IDModeration`,`DateFrom`,`DateTo`), KEY `IX_Add_DateAdded` (`DateAdded`), KEY `IX_Add_Telephone` (`Telephone`), KEY `FK_Add_AddModeration` (`IDModeration`), KEY `FK_Add_AddStatus` (`IDStatus`), KEY `FK_Add_Category` (`IDCategory`), KEY `FK_Add_col_city` (`IDCity`), KEY `FK_Add_col_neighbourhood` (`IDNeighbourhood`), KEY `FK_Add_col_region` (`IDRegion`), KEY `IDSource` (`IDSource`), KEY `IX_Add_Name` (`Name`), KEY `IX_Add_IP` (`IP`), KEY `IX_Add_UserType` (`UserType`), KEY `IX_Add_IDUser_IDStatus` (`IDUser`,`IDStatus`), KEY `FullEditToken` (`FullEditToken`), KEY `IX_ExpiredAt` (`ExpiredAt`), CONSTRAINT `Add_ibfk_1` FOREIGN KEY (`IDSource`) REFERENCES `AddSource` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_Add_AddModeration` FOREIGN KEY (`IDModeration`) REFERENCES `AddModeration` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_Add_AddStatus` FOREIGN KEY (`IDStatus`) REFERENCES `AddStatus` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_Add_Category` FOREIGN KEY (`IDCategory`) REFERENCES `Category` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_Add_User` FOREIGN KEY (`IDUser`) REFERENCES `User` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_Add_col_city` FOREIGN KEY (`IDCity`) REFERENCES `col_city` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_Add_col_neighbourhood` FOREIGN KEY (`IDNeighbourhood`) REFERENCES `col_neighbourhood` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_Add_col_region` FOREIGN KEY (`IDRegion`) REFERENCES `col_region` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=16452993 DEFAULT CHARSET=utf8 |

show index from Add возвращение:

+-------+------------+--------------------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+--------------------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Add | 0 | PRIMARY | 1 | ID | A | 1905 | NULL | NULL | | BTREE | | | | Add | 1 | IX_Add_DateFrom | 1 | DateFrom | A | 1905 | NULL | NULL | YES | BTREE | | | | Add | 1 | IX_Add_IDUser | 1 | IDUser | A | 1905 | NULL | NULL | | BTREE | | | | Add | 1 | IX_Add_DateUpdated | 1 | DateUpdated | A | 1905 | NULL | NULL | YES | BTREE | | | | Add | 1 | IX_Add_DateUpToDated | 1 | DateUpToDated | A | 1905 | NULL | NULL | YES | BTREE | | | | Add | 1 | IX_Add_DateTo | 1 | DateTo | A | 1905 | NULL | NULL | YES | BTREE | | | | Add | 1 | IX_Add_ON_IDUser_IDStatus_IDModeratiON_DateFrom_DateTo | 1 | IDUser | A | 1905 | NULL | NULL | | BTREE | | | | Add | 1 | IX_Add_ON_IDUser_IDStatus_IDModeratiON_DateFrom_DateTo | 2 | IDStatus | A | 1905 | NULL | NULL | YES | BTREE | | | | Add | 1 | IX_Add_ON_IDUser_IDStatus_IDModeratiON_DateFrom_DateTo | 3 | IDModeration | A | 1905 | NULL | NULL | YES | BTREE | | | | Add | 1 | IX_Add_ON_IDUser_IDStatus_IDModeratiON_DateFrom_DateTo | 4 | DateFrom | A | 1905 | NULL | NULL | YES | BTREE | | | | Add | 1 | IX_Add_ON_IDUser_IDStatus_IDModeratiON_DateFrom_DateTo | 5 | DateTo | A | 1905 | NULL | NULL | YES | BTREE | | | | Add | 1 | IX_Add_DateAdded | 1 | DateAdded | A | 1905 | NULL | NULL | YES | BTREE | | | | Add | 1 | IX_Add_Telephone | 1 | Telephone | A | 1905 | NULL | NULL | YES | BTREE | | | | Add | 1 | FK_Add_AddModeration | 1 | IDModeration | A | 6 | NULL | NULL | YES | BTREE | | | | Add | 1 | FK_Add_AddStatus | 1 | IDStatus | A | 4 | NULL | NULL | YES | BTREE | | | | Add | 1 | FK_Add_Category | 1 | IDCategory | A | 190 | NULL | NULL | | BTREE | | | | Add | 1 | FK_Add_col_city | 1 | IDCity | A | 635 | NULL | NULL | YES | BTREE | | | | Add | 1 | FK_Add_col_neighbourhood | 1 | IDNeighbourhood | A | 952 | NULL | NULL | YES | BTREE | | | | Add | 1 | FK_Add_col_region | 1 | IDRegion | A | 63 | NULL | NULL | YES | BTREE | | | | Add | 1 | IDSource | 1 | IDSource | A | 10 | NULL | NULL | YES | BTREE | | | | Add | 1 | IX_Add_Name | 1 | Name | A | 1905 | NULL | NULL | | BTREE | | | | Add | 1 | IX_Add_IP | 1 | IP | A | 1905 | NULL | NULL | YES | BTREE | | | | Add | 1 | IX_Add_UserType | 1 | UserType | A | 4 | NULL | NULL | YES | BTREE | | | | Add | 1 | IX_Add_IDUser_IDStatus | 1 | IDUser | A | 1905 | NULL | NULL | | BTREE | | | | Add | 1 | IX_Add_IDUser_IDStatus | 2 | IDStatus | A | 1905 | NULL | NULL | YES | BTREE | | | | Add | 1 | FullEditToken | 1 | FullEditToken | A | 127 | NULL | NULL | YES | BTREE | | | | Add | 1 | IX_ExpiredAt | 1 | ExpiredAt | A | 100 | NULL | NULL | YES | BTREE | | | +-------+------------+--------------------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 27 rows in set (0.00 sec)

и наши основные запросы

## наиболее querie ы используется в системе

Общая часть:

SELECT `a`.`Description` AS `Description`, GROUP_CONCAT(CONCAT_WS('=', `catt`.`Name`, IF(`gc`.`Name` IS NOT NULL, `gc`.`Name`, `aav`.`Value`))) AS `Attributes`, ((SELECT IF(`ae`.`IDAdmin` IS NOT NULL, CONCAT('Admin=', `adm`.`adminName`), IF(`ae`.`IDUser` IS NOT NULL, CONCAT('USER=', `ae`.`IDUser`), '')) FROM `AddEvent` AS `ae` LEFT JOIN `Admin` AS `adm` ON `ae`.`IDAdmin` = `adm`.`adminId` WHERE `a`.`ID` = `ae`.`IDAdd` ORDER BY `ae`.`AddedAt` DESC LIMIT 1) ) AS `AdminOrUser`, 'accepted' AS `AddStatus`, `a`.`ID` AS `ID`, `a`.`Name` AS `Name`, `a`.`IDStatus` AS `IDStatus`, `a`.`BadWordsFlag` AS `BadWordsFlag`, `a`.`Price` AS `Price`, `u`.`TelephonePrimary` AS `Telephone`, `a`.`DateTo` AS `DateTo`, IF(`a`.`DateTo`, `a`.`DateTo`, `a`.`DateAdded`) AS `CustomDate`, `a`.`IDModeration` AS `IDModeration`, ((SELECT 1 FROM `AddChanges` AS `ac` WHERE `ac`.`IDAdd` = `a`.`ID` AND `ac`.`IDModeration` = 4 ORDER BY `ac`.`ID` DESC LIMIT 1)) AS `HasChange`, ((SELECT GROUP_CONCAT(CONCAT_WS('>', `ai`.`Path`, `ai`.`IsDefault`)) FROM `AddImage` AS `ai` WHERE `ai`.`IDAdd` = `a`.`ID`)) AS `Images`, `ca2`.`Name` AS `ParentCategory`, `ca1`.`Name` AS `Category`, `r`.`Name` AS `RegionName`, `ct`.`Name` AS `CityName`, `n`.`Name` AS `NeighbourhoodName`, `u`.`Email` AS `Email` FROM `Add` AS `a` LEFT JOIN `User` AS `u` ON `u`.`ID` = `a`.`IDUser` LEFT JOIN `Category` AS `ca1` ON `ca1`.`ID` = `a`.`IDCategory` LEFT JOIN `Category` AS `ca2` ON `ca2`.`ID` = `ca1`.`IDParent` LEFT JOIN `col_region` AS `r` ON `r`.`Id` = `a`.`IDRegion` LEFT JOIN `col_city` AS `ct` ON `ct`.`Id` = `a`.`IDCity` LEFT JOIN `col_neighbourhood` AS `n` ON `n`.`Id` = `a`.`IDNeighbourhood` LEFT JOIN `AddAttributeValues` AS `aav` ON `aav`.`IDAdd` = `a`.`ID` LEFT JOIN `CategoryAttribute` AS `catt` ON `catt`.`ID` = `aav`.`IDCategoryAttribute` LEFT JOIN `GenericCollection` AS `gc` ON `gc`.`ID` = `aav`.`Value`

# 1 детальный вид

WHERE (`a`.`DateTo` > CURRENT_TIMESTAMP() AND `a`.`IDStatus` = 1 AND `a`.`IDModeration` = 1) AND (`a`.`IDStatus` <> 2) GROUP BY `a`.`ID` ORDER BY `a`.`ID` DESC LIMIT 51

# подробно с состоянием

WHERE ((`a`.`DateTo` > CURRENT_TIMESTAMP() AND `a`.`IDStatus` = 1 AND `a`.`IDModeration` = 1 ) AND (`a`.`IDStatus` <> 2) ) AND (`a`.`IDRegion` = '8') GROUP BY `a`.`ID` ORDER BY `a`.`ID` DESC LIMIT 51

# детализированы категории

WHERE ((`a`.`DateTo` > CURRENT_TIMESTAMP() AND `a`.`IDStatus` = 1 AND `a`.`IDModeration` = 1 ) AND (`a`.`IDStatus` <> 2) ) AND (`a`.`IDCategory` = '43627' OR `ca1`.`IDParent` = '43627' OR `ca1`.`IDParent` IN ( SELECT `ca3`.`ID` AS `ID` FROM `Category` AS `ca3` WHERE `ca3`.`IDParent` = '43627') ) GROUP BY `a`.`ID` ORDER BY `a`.`ID` DESC LIMIT 51

#detailed с датой категории состояния

WHERE (((((`a`.`DateTo` > CURRENT_TIMESTAMP() AND `a`.`IDStatus` = 1 AND `a`.`IDModeration` = 1) AND (`a`.`IDStatus` <> 2) ) AND (UNIX_TIMESTAMP(`a`.`DateAdded`) >= '1503430200')) AND (UNIX_TIMESTAMP(`a`.`DateAdded`) <= '1505071799') ) AND (`a`.`IDCategory` = '43627' OR `ca1`.`IDParent` = '43627' OR `ca1`.`IDParent` IN ( SELECT `ca3`.`ID` AS `ID` FROM `Category` AS `ca3` WHERE `ca3`.`IDParent` = '43627')) ) AND (`a`.`IDRegion` = '8') GROUP BY `a`.`ID` ORDER BY `a`.`ID` DESC LIMIT 51

# Резюме Режим

SELECT 'accepted' AS `AddStatus`, `a`.`ID` AS `ID`, `a`.`Name` AS `Name`, `a`.`IDStatus` AS `IDStatus`, `a`.`BadWordsFlag` AS `BadWordsFlag`, `a`.`Price` AS `Price`, `u`.`TelephonePrimary` AS `Telephone`, `a`.`DateTo` AS `DateTo`, IF(`a`.`DateTo`, `a`.`DateTo`, `a`.`DateAdded`) AS `CustomDate`, `a`.`IDModeration` AS `IDModeration`, ((SELECT 1 FROM `AddChanges` AS `ac` WHERE `ac`.`IDAdd` = `a`.`ID` AND `ac`.`IDModeration` = 4 ORDER BY `ac`.`ID` DESC LIMIT 1)) AS `HasChange`, ((SELECT GROUP_CONCAT(CONCAT_WS('>', `ai`.`Path`, `ai`.`IsDefault`)) FROM `AddImage` AS `ai` WHERE `ai`.`IDAdd` = `a`.`ID`) ) AS `Images`, `ca2`.`Name` AS `ParentCategory`, `ca1`.`Name` AS `Category`, `r`.`Name` AS `RegionName`, `ct`.`Name` AS `CityName`, `n`.`Name` AS `NeighbourhoodName`, `u`.`Email` AS `Email` FROM `Add` AS `a` LEFT JOIN `User` AS `u` ON `u`.`ID` = `a`.`IDUser` LEFT JOIN `Category` AS `ca1` ON `ca1`.`ID` = `a`.`IDCategory` LEFT JOIN `Category` AS `ca2` ON `ca2`.`ID` = `ca1`.`IDParent` LEFT JOIN `col_region` AS `r` ON `r`.`Id` = `a`.`IDRegion` LEFT JOIN `col_city` AS `ct` ON `ct`.`Id` = `a`.`IDCity` LEFT JOIN `col_neighbourhood` AS `n` ON `n`.`Id` = `a`.`IDNeighbourhood` WHERE (((((`a`.`DateTo` > CURRENT_TIMESTAMP() AND `a`.`IDStatus` = 1 AND `a`.`IDModeration` = 1) AND (`a`.`IDStatus` <> 2)) AND (UNIX_TIMESTAMP(`a`.`DateAdded`) >= '1503430200')) AND (UNIX_TIMESTAMP(`a`.`DateAdded`) <= '1505071799') ) AND (`a`.`IDCategory` = '43627' OR `ca1`.`IDParent` = '43627' OR `ca1`.`IDParent` IN ( SELECT `ca3`.`ID` AS `ID`

stackoverrun.com

mysql - Оптимизация запросов использования индекса MySQL

У меня есть следующая таблица MySQL (MyISAM) с примерно 3 миллионами строк.

CREATE TABLE `tasks` ( `id` int(11) NOT NULL AUTO_INCREMENT, `node` smallint(6) NOT NULL, `pid` int(11) NOT NULL, `job` int(11) NOT NULL, `a_id` int(11) DEFAULT NULL, `user_id` int(11) NOT NULL, `state` int(11) NOT NULL, `start_time` int(11) NOT NULL, `end_time` int(11) NOT NULL, `stop_time` int(11) NOT NULL, `end_stream` int(11) NOT NULL, `message` varchar(255) DEFAULT NULL, `rate` float NOT NULL, `exiting` int(11) NOT NULL DEFAULT '0', `bytes` int(11) NOT NULL, `motion` tinyint(4) NOT NULL, PRIMARY KEY (`id`), KEY `a_id` (`a_id`), KEY `job` (`job`), KEY `state` (`state`), KEY `end_time` (`end_time`), KEY `start_time` (`start_time`), ) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;

Теперь, когда я запускаю следующий запрос, MySQL использует только индекс a_id и должен сканировать несколько тысяч строк.

SELECT count(id) AS tries FROM `tasks` WHERE ( job='1' OR job='3' ) AND a_id='614' AND state >'80' AND state < '100' AND start_time >='1386538013';

Когда я добавляю дополнительный индекс KEY newkey (a_id, state, start_time), MySQL все еще пытается использовать только a_id, а не новый. Только при использовании индекса подсказки/силы в запросе он использовался. Изменение полей в запросе не помогает.

Любые идеи? Мне не обязательно нужны намеки в моих заявлениях. Тот факт, что MySQL не делает этого автоматически, указывает мне, что есть проблема с моей таблицей, ключами или запросом где-то. Любая помощь приветствуется.

Дополнительная информация:

mysql> show index in tasks; +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tasks | 0 | PRIMARY | 1 | id | A | 3130554 | NULL | NULL | | BTREE | | | | tasks | 1 | a_id | 1 | a_id | A | 2992 | NULL | NULL | YES | BTREE | | | | tasks | 1 | job | 1 | job | A | 5 | NULL | NULL | | BTREE | | | | tasks | 1 | state | 1 | state | A | 9 | NULL | NULL | | BTREE | | | | tasks | 1 | end_time | 1 | end_time | A | 1565277 | NULL | NULL | | BTREE | | | | tasks | 1 | newkey | 1 | a_id | A | 2992 | NULL | NULL | YES | BTREE | | | | tasks | 1 | newkey | 2 | state | A | 8506 | NULL | NULL | | BTREE | | | | tasks | 1 | newkey | 3 | start_time | A | 3130554 | NULL | NULL | | BTREE | | | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

EXPLAIN с кавычками и без них:

qaru.site

optimization - Оптимизация индексов MySQL для запроса (база данных торговых тиков)

Моя база данных MySQL насчитывает более 350 миллионов строк и растет. Сейчас 32 ГБ. Я использую SSD и много оперативной памяти, но хотел бы получить совет, чтобы убедиться, что я использую соответствующие индексы.

CREATE TABLE 'qcollector' ( 'key' bigint(20) NOT NULL AUTO_INCREMENT, 'instrument' char(4) DEFAULT NULL, 'datetime' datetime DEFAULT NULL, 'last' double DEFAULT NULL, 'lastsize' int(10) DEFAULT NULL, 'totvol' int(10) DEFAULT NULL, 'bid' double DEFAULT NULL, 'ask' double DEFAULT NULL, PRIMARY KEY ('key'), KEY 'datetime_index' ('datetime') ) ENGINE=InnoDB; show index from qcollector; +------------+------------+----------------+--------------+-------------+-----------+-- -----------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | qcollector | 0 | PRIMARY | 1 | key | A | 378866659 | NULL | NULL | | BTREE | | | | qcollector | 1 | datetime_index | 1 | datetime | A | 63144443 | NULL | NULL | YES | BTREE | | | +------------+------------+----------------+--------------+-------------+-----------+------ -------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.03 sec) select * from qcollector order by datetime desc limit 1; +-----------+------------+---------------------+---------+----------+---------+---------+--------+ | key | instrument | datetime | last | lastsize | totvol | bid | ask | +-----------+------------+---------------------+---------+----------+---------+---------+--------+ | 389054487 | ES | 2012-06-29 15:14:59 | 1358.25 | 2 | 2484771 | 1358.25 | 1358.5 | +-----------+------------+---------------------+---------+----------+---------+---------+--------+ 1 row in set (0.09 sec)

Типичный запрос медленный (полное сканирование таблицы, этот запрос занимает 3-4 минуты):

qaru.site

Оптимизация индекса mysql для таблицы с несколькими индексами, которые индексируют одни и те же столбцы

У меня есть таблица, в которой хранятся некоторые основные данные о посещениях посетителей на сторонних веб-сайтах. Это его структура:

id, site_id, unixtime, unixtime_last, ip_address, uid

Есть четыре индексы: id, site_id/unixtime, site_id/ip_address и site_id/uid

Есть много различных типов способов, которыми мы запрашиваем эту таблицу, и все они являются специфическими для site_id. Индекс с unixtime используется для отображения списка посетителей для заданной даты или временного диапазона. Остальные два используются для поиска всех посещений с IP-адреса или «uid» (уникальное значение cookie, созданного для каждого посетителя), а также для определения того, является ли это новым посетителем или возвращающимся посетителем.

Очевидно, что хранение идентификатора сайта внутри 3-х индексов неэффективно для скорости записи и хранения, но я не вижу никакого способа обойти это, так как мне нужно иметь возможность быстро запрашивать данные для данного конкретного site_id.

Любые идеи по повышению эффективности?

Я действительно не понимаю B-деревья, кроме некоторых очень простых вещей, но эффективнее иметь самый левый столбец индекса с наименьшей дисперсией - правильно? Поскольку я считал, что site_id является вторым столбцом индекса как для ip_address, так и для uid, но я думаю, что это сделает индекс менее эффективным, поскольку IP и UID будут отличаться от идентификатора сайта, потому что у нас есть только около 8000 уникальные сайты на сервере базы данных, но миллионы уникальных посетителей на всех ~ 8000 сайтах ежедневно.

Я также решил полностью удалить идентификатор сайта из индексов IP и UID, так как вероятность того, что один и тот же посетитель перейдет на несколько сайтов с одним и тем же сервером базы данных, довольно мала, но в тех случаях, когда это происходит, я боюсь это может быть довольно медленным, чтобы определить, является ли это новым посетителем этого site_id или нет. Запрос будет что-то вроде:

select id from sessions where uid = 'value' and site_id = 123 limit 1

... так что, если этот посетитель посетили этот сайт раньше, нужно только найти одну строку с этим Site_ID, прежде чем он остановился. Это было бы не очень быстро, но приемлемо быстро. Но, скажем, у нас есть сайт, который получает 500 000 посетителей в день, а конкретный посетитель любит этот сайт и отправляется туда 10 раз в день. Теперь они впервые попадают на другой сайт на том же сервере базы данных. Вышеприведенный запрос может занять довольно много времени, чтобы выполнить поиск по всем потенциально тысячам строк для этого UID, разбросанных по всему диску, поскольку он не будет искать один для этого идентификатора сайта.

Любое представление о делая это как можно более эффективным был бы оценен :)

Обновления - это MyISAM стол с MySQL 5.0. Мои проблемы связаны как с производительностью, так и с местом для хранения. Эта таблица прочна и написана тяжело. Если бы мне пришлось выбирать между производительностью и хранением, моя самая большая проблема - это производительность, но оба они важны.

Мы используем memcached сильно во всех областях нашего сервиса, но это не повод не заботиться о дизайне базы данных. Я хочу, чтобы база данных была максимально эффективной.

stackoverrun.com

MySql - Оптимизация индекса

У нас есть продукт Analytics. Для каждого из наших клиентов мы даем один код JavaScript, который они размещают на своих сайтах. Если пользователь посещает наш сайт, код java-скрипта попадает на наш сервер, поэтому мы храним эту страницу от имени нашего клиента. Каждый из наших клиентов содержит уникальное доменное имя, которое означает, что клиент определяется домен Нама

базы данных сервера: MySql 5,6 Таблица строк: 400000000

Ниже наша схема таблицы.

В приведенном выше таблице guid представляет посетителя нашего сайта клиента, а sid представляет собой сеанс посетителя нашего сайта клиента. Это означает, что для каждого сида должен быть назначен guid.

Нам нужны запросы как следующие

Запрос 1: Найти уникальные, всего посетитель

SELECT count(DISTINCT guid) AS count,count(guid) AS total FROM page_views WHERE domain = 'abc' AND stats_time BETWEEN '2015-10-05 00:00:00' AND '2015-10-04 23:59:59'

композит планирование индекса: домен, STATS_TIME, с.и.д.

Запрос 2: Найти уникальным , итоговые сессии

SELECT count(DISTINCT sid) AS count,count(sid) AS total FROM page_views WHERE domain = 'abc' AND stats_time BETWEEN '2015-10-05 00:00:00' AND '2015-10-04 23:59:59'

композит планирования индекс: домен, STATS_TIME, справ

Query 3: Найти посетителей, сессий по странам, регионам, по городам

композит планирования индекс: домен, страна

композитный индекс планирование: домен, регион

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

Можно ли оптимизировать эти комбинации индексов, чтобы уменьшить размер индекса и повысить производительность.

stackoverrun.com

mysql - Индексы хэширования MySQL для оптимизации

Так что, возможно, это noob, но я возился с парами таблиц.

У меня ТАБЛИЦА Примерно 45 000 записей

У меня ТАБЛИЦА B примерно 1,5 миллиона записей

У меня есть запрос:

update schema1.tablea a inner join ( SELECT DISTINCT ID, Lookup, IDpart1, IDpart2 FROM schema1.tableb WHERE IDpart1 is not NULL AND Lookup is not NULL ORDER BY ID,Lookup ) b Using(ID,Lookup) set a.Elg_IDpart1 = b.IDpart1, a.Elg_IDpart2 = b.IDpart2 where a.ID is NOT NULL AND a.Elg_IDpart1 is NULL

Итак, я заставляю индекс на ID, Lookup. Каждая таблица также имеет индекс для этих столбцов, но из-за подзапроса я заставил его.

Он запускает FOR-EVER для запуска, и это действительно должно произойти, я бы представил себе менее 5 минут...

Мои вопросы касаются индексов, а не запроса.

Я знаю, что вы не можете использовать хеш-индекс в упорядоченном индексе.

В настоящее время у меня есть индексы как для идентификатора, так и для поиска, и как один индекс, и это индекс B-Tree. Основываясь на моем подклассе WHERE, соответствует ли хэш-индекс в качестве метода оптимизации?

Могу ли я иметь один хэш-индекс, а остальные индексы - индекс B-дерева?

Это не поле первичного ключа.

Я бы опубликовал свой пояс, но я изменил имя на этих таблицах. В основном используется индекс только для ID... вместо использования идентификатора, Lookup, я хотел бы заставить его использовать оба или хотя бы превратить его в другой тип индекса и посмотреть, помогает ли это?

Теперь я знаю, что MySQL достаточно умен, чтобы определить, какой индекс наиболее подходит, и что он делает? Поле Lookup отображает первую и вторую часть идентификатора...

Любая помощь или понимание этого оцениваются.

UPDATE

An EXPLAIN на UPDATE после того, как я вынул подзапрос.

+----+-------------+-------+------+-----------------------------+--------------+---------+-------------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-----------------------------+--------------+---------+-------------------+-------+-------------+ | 1 | SIMPLE | m | ALL | Lookup_Idx,ID_Idx,ID_Lookup | | | | 44023 | Using where | | 1 | SIMPLE | c | ref | ID_LookupIdx | ID_LookupIdx | 5 | schema1.tableb.ID | 4 | Using where | +----+-------------+-------+------+-----------------------------+--------------+---------+-------------------+-------+-------------+

tablea соответствующие индексы:

ID_LookupIdx (ID, Lookup)

tableb соответствующие индексы:

ID (ID)
Lookup_Idx (Lookup)
ID_Lookup_Idx (ID, Lookup)

Все индексы являются нормальными B-деревьями.

qaru.site