Индексы в Mysql

dikens3 · Непрочитанное сообщение **dikens3** » 2007-06-27 17:00:33

Есть запрос:

explain SELECT count(1) FROM log WHERE (`date-time` BETWEEN '2007-06-27' AND '2007-06-27' + INTERVAL 1 DAY) AND `text-action` = '32' AND ( dst_email LIKE '%user@domain.ru%' );

Есть другой запрос: (Отличие только в знаке отрицания !='32' - не равно 32)

Код: Выделить всё

explain SELECT count(1) FROM log WHERE (`date-time` BETWEEN '2007-06-27' AND '2007-06-27' + INTERVAL 1 DAY) AND `text-action` != '32' AND ( dst_email LIKE '%user@domain.ru%' );

Теперь собственно ломаю голову, почему в 1 случае поле type=range и соответственно поиск идёт только по нужным данным. (это хорошо)
А во втором = ALL (Что не есть гуд и поиск идёт по всем данным)
Подробнее:

Код: Выделить всё

mysql> explain SELECT count(1) FROM log WHERE (`date-time` BETWEEN '2007-06-27' AND '2007-06-27' + INTERVAL 1 DAY) AND `text-action` = '32' AND ( dst_email LIKE '%user@domain.ru%' );
+----+-------------+-------+-------+---------------------------------+-----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys                   | key       | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------------------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | log   | range | text-action,date-time,text-date | text-date |       6 | NULL |  149 | Using where |
+----+-------------+-------+-------+---------------------------------+-----------+---------+------+------+-------------+

Код: Выделить всё

mysql> explain SELECT count(1) FROM log WHERE (`date-time` BETWEEN '2007-06-27' AND '2007-06-27' + INTERVAL 1 DAY) AND `text-action` != '32' AND ( dst_email LIKE '%user@domain.ru%' );
+----+-------------+-------+------+---------------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys                   | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | log   | ALL  | text-action,date-time,text-date | NULL |    NULL | NULL | 2322 | Using where |
+----+-------------+-------+------+---------------------------------+------+---------+------+------+-------------+

Ключи на date-time, text-action (Это то поле, которое 32), и совмещённый text-date(text-action,date-time)

Что делать, как лечить и почему не используется индекс? Никакой.

Описалово:

Код: Выделить всё

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

Хостинг HostFood.ru · **Хостинг HostFood.ru**

Тарифы на хостинг в России, от 12 рублей: https://www.host-food.ru/tariffs/hosting/
Тарифы на виртуальные сервера (VPS/VDS/KVM) в РФ, от 189 руб.: https://www.host-food.ru/tariffs/virtualny-server-vps/
Выделенные сервера, Россия, Москва, от 2000 рублей (HP Proliant G5, Intel Xeon E5430 (2.66GHz, Quad-Core, 12Mb), 8Gb RAM, 2x300Gb SAS HDD, P400i, 512Mb, BBU):
https://www.host-food.ru/tariffs/vydelennyi-server-ds/
Недорогие домены в популярных зонах: https://www.host-food.ru/domains/

Alex Keda

нифига не понял...

dikens3 · Непрочитанное сообщение **dikens3** » 2007-06-27 20:10:09

lissyara писал(а):нифига не понял...

По русски звучит так:

Запрос номер 1 при поиске в базе будет обрабатывать 149 строк (это нормально, индекс работает)

Запрос номер 2 при поиске в базе будет обрабатывать 2322 строк (Это все имеющиеся в базе на данный момент)

За месяц строк будет 100 000, за год 1 000 000 - вот нафиг он их будет все обрабатывать(просматривать), когда можно диапазон уменьшить индексами. (С ростом базы время на запрос будет уходить всё больше и больше про запросу номер 2)

Alex Keda

дай

Код: Выделить всё

SHOW CREATE TABLE

dikens3 · Непрочитанное сообщение **dikens3** » 2007-06-27 22:42:13

lissyara писал(а):дай
Код: Выделить всё
SHOW CREATE TABLE

Завтра, на работе всё.

dikens3 · Непрочитанное сообщение **dikens3** » 2007-06-28 9:50:01

Код: Выделить всё

CREATE TABLE `log` (
  `username` varchar(50) default NULL,
  `text-action` smallint(6) unsigned NOT NULL default '0',
  `src_email` varchar(50) default NULL,
  `dst_email` text,
  `src_ip` varchar(15) default NULL,
  `hostname` varchar(255) default NULL,
  `helo_ip` varchar(255) default NULL,
  `size` int(11) default NULL,
  `date-time` timestamp NOT NULL default CURRENT_TIMESTAMP,
  KEY `text-action` (`text-action`),
  KEY `src_email` (`src_email`),
  KEY `hostname` (`hostname`),
  KEY `helo_ip` (`helo_ip`),
  KEY `date-time` (`date-time`),
  KEY `text-date` (`text-action`,`date-time`)
) ENGINE=InnoDB DEFAULT CHARSET=koi8r COMMENT='Таблица логов'

Ключей уже всяких накидал.

Alex Keda

странно... даже не знаю

==========
логи лучше хранить в MyISAM - будет быстрей на порядок, когда таблица выростет

dikens3 · Непрочитанное сообщение **dikens3** » 2007-06-28 10:43:46

lissyara писал(а):странно... даже не знаю
==========
логи лучше хранить в MyISAM - будет быстрей на порядок, когда таблица выростет

Мне внешние ключи нужны, а там их нет.
mysql 4.1.22

bakake · Непрочитанное сообщение **bakake** » 2007-06-29 7:26:05

Внешние ключи на таблице с логами это извращение

А все таки, если попробовать все тоже самое в тестовых целях но на таблице myisam (пересоздать и перелить данные на полутора тысячах записей займет минуту в пределе)?

dikens3 · Непрочитанное сообщение **dikens3** » 2007-06-29 9:58:04

bakake писал(а):Внешние ключи на таблице с логами это извращение А все таки, если попробовать все тоже самое в тестовых целях но на таблице myisam (пересоздать и перелить данные на полутора тысячах записей займет минуту в пределе)?

Смотря чего нужно достичь, если база будет непротиворечивая, то почему нет?

bakake · Непрочитанное сообщение **bakake** » 2007-06-29 13:38:32

Ну если я правильно понял БД на 500 таблиц у тебя не будет? Таблиц будет скорее всего в пределе 20. А теперь если прикинуть, чем преимущественно занимается эта БД? Я полагаю что в нее постоянно что то пишут и раз в месяц что нибудь оттуда читают. Создавая индекс или констрайнт-форинкей, ты есессно несколько ускоряешь выполнение запросов на выборку и несколько замедляешь процесс вставки/обновления.
А если учесть, что далеко не всегда полный скан таблицы это плохо...

dikens3 · Непрочитанное сообщение **dikens3** » 2007-06-29 13:58:06

bakake писал(а):Ну если я правильно понял БД на 500 таблиц у тебя не будет? Таблиц будет скорее всего в пределе 20. А теперь если прикинуть, чем преимущественно занимается эта БД? Я полагаю что в нее постоянно что то пишут и раз в месяц что нибудь оттуда читают. Создавая индекс или констрайнт-форинкей, ты есессно несколько ускоряешь выполнение запросов на выборку и несколько замедляешь процесс вставки/обновления.
А если учесть, что далеко не всегда полный скан таблицы это плохо...

Таблиц 12 всего.
Действительно постоянно что-то пишется в таблицу логов.( эдак 20 записей в минуту, что не так много)
Выборка должна происходить максимально быстро. (Использовать индексы)
Вобщем не существенно, т.к. в таблице log у меня не используются внешние ключи.(переделал на MyISAM)

bakake · Непрочитанное сообщение **bakake** » 2007-06-29 14:04:45

Аааа! Я предложил только попробовать myisam! Кстати, заработало?
PS
Кстати, в myisam можно сжатые таблицы использовать. Для архива логов -- самое оно

dikens3 · Непрочитанное сообщение **dikens3** » 2007-06-29 15:17:41

bakake писал(а):Аааа! Я предложил только попробовать myisam! Кстати, заработало?
PS
Кстати, в myisam можно сжатые таблицы использовать. Для архива логов -- самое оно

А ссылку на сжатые архивы?

Заработало, только прилось мозгами включить и использовать ещё одно поле (size - размер письма). Он существует только в двух случаях:
1. Когда письмо принято (text-action = 33)
2. Когда письмо отправлено (text-action = 32)
Во всех остальных случаях (если вирус, хост заблокирован оно мне не нужно и устанавливается NULL)

В итоге статистика по всем письмам (Принятым, непринятым и отправленным делается запросом с UNION(иначе индексы не хотят работать))
Запрос приобрёл такой вид:

Код: Выделить всё

explain SELECT src_email,dst_email,`text-action`,`date-time` FROM log WHERE ( dst_email LIKE '%user@domain.ru%' ) AND (`date-time` BETWEEN '2007-06-29' AND '2007-06-29' + INTERVAL 1 DAY) AND `text-action` = 33 
UNION SELECT src_email,dst_email,`text-action`,`date-time` FROM log WHERE ( dst_email LIKE '%user@domain.ru%' ) AND (`date-time` BETWEEN '2007-06-29' AND '2007-06-29' + INTERVAL 1 DAY) AND `size` IS NULL 
UNION SELECT src_email,dst_email,`text-action`,`date-time` FROM log WHERE ( src_email LIKE 'user@domain.ru' ) AND (`date-time` BETWEEN '2007-06-29' AND '2007-06-29' + INTERVAL 1 DAY) AND `text-action` = 32 ORDER BY `date-time`

+----+--------------+--------------+-------+--------------------------+-----------+---------+-------+------+----------------+
| id | select_type  | table        | type  | possible_keys            | key       | key_len | ref   | rows | Extra          |
+----+--------------+--------------+-------+--------------------------+-----------+---------+-------+------+----------------+
|  1 | PRIMARY      | log          | range | date-time,text-date      | text-date |       6 | NULL  |  284 | Using where    |
|  2 | UNION        | log          | ref   | size,date-time,size-date | size-date |       5 | const |    1 | Using where    |
|  3 | UNION        | log          | range | date-time,text-date      | text-date |       6 | NULL  |  115 | Using where    |
|NULL | UNION RESULT | <union1,2,3> | ALL   | NULL                     | NULL      |    NULL | NULL  | NULL | Using filesort |
+----+--------------+--------------+-------+--------------------------+-----------+---------+-------+------+----------------+
4 rows in set (0.05 sec)

P.S. Как сжатые делать, где почитать?

dikens3 · Непрочитанное сообщение **dikens3** » 2007-06-29 15:22:37

сам нашёл, читаю:

Код: Выделить всё

# myisampack -t log.MYI
Compressing log.MYD: (1595 records)
- Calculating statistics

normal:      4  empty-space:       2  empty-zero:         1  empty-fill:   2
pre-space:   0  end-space:         4  intervall-fields:   0  zero:         0
Original trees:  10  After join: 7
- Compressing file
Min record length:     34   Max length:    118   Mean total length:     66
43.23%

User time 0.02, System time 0.00
Maximum resident set size 1452, Integral resident set size 1368
Non-physical pagefaults 183, Physical pagefaults 10, Swaps 0
Blocks in 5 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 16, Involuntary context switches 14

bakake · Непрочитанное сообщение **bakake** » 2007-06-29 15:46:32

dikens3 писал(а): Заработало, только прилось мозгами включить и использовать ещё одно поле (size - размер письма). Он существует только в двух случаях:
1. Когда письмо принято (text-action = 33)
2. Когда письмо отправлено (text-action = 32)
Во всех остальных случаях (если вирус, хост заблокирован оно мне не нужно и устанавливается NULL)

А все, до меня дошло

Можно заменить NULL на 0 и, главное, само поле сделать "NOT NULL". Тогда индекс будет работать в случае !=

dikens3 · Непрочитанное сообщение **dikens3** » 2007-06-29 15:57:14

В случае с text-action так и есть (выше пример создания таблицы). NOT NULL default '0'
Индекс не работает.

bakake · Непрочитанное сообщение **bakake** » 2007-06-29 16:06:20

Опс, действительно. Не посмотрел. Гм.. ну тогда вижу только способ попробовать where text-action in (и тут перечислить все случаи за исключением 32) %)

dikens3 · Непрочитанное сообщение **dikens3** » 2007-06-29 18:02:09

Тут тоже есть нюанс, т.к. эти самые случаи постепенно добавляются. Уж очень не хочется править везде. (В php станичках потом лень выискивать, да и забуду однозначно)
Делал через NOT IN ('32','33'), не помогло.

bakake · Непрочитанное сообщение **bakake** » 2007-06-29 22:14:30

С другой стороны, union работает, в принципе намана

Alex Keda

а я так и ниасилил JOIN/UNION

ProFTP · Непрочитанное сообщение **ProFTP** » 2009-11-29 19:47:12

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

часто используемые INDEX, KEY,
я например посмотрел разные таблицы форумов в MyISAM и по моему все ставят от фонаря их

есть ли какие-то реальные примеры, статьи...?

zg · Непрочитанное сообщение zg » 2009-11-29 20:37:28

ProFTP писал(а):и по моему все ставят от фонаря их

никогда не ставил индексы от фонаря

ProFTP писал(а):есть ли какие-то реальные примеры, статьи...?

мне мануала хватило

ProFTP · Непрочитанное сообщение **ProFTP** » 2009-12-04 1:01:54

ну вот например как понять вот это

Код: Выделить всё

CREATE TABLE `search_main` (
             `word`       varchar(100)  NOT NULL   default '',
             `page`       int(11)       NOT NULL   default '0',
             `relevance`  int(11)       NOT NULL   default '0',
        KEY `word` (`word`,`page`)
) TYPE=MyISAM;

`word`,`page` - это просто что они должны быть одинаковые оба?

Код: Выделить всё

CREATE TABLE {$db_prefix}messages (
  id_msg int(10) unsigned NOT NULL auto_increment,
  id_topic mediumint(8) unsigned NOT NULL default '0',
  id_board smallint(5) unsigned NOT NULL default '0',
  poster_time int(10) unsigned NOT NULL default '0',
  id_member mediumint(8) unsigned NOT NULL default '0',
  id_msg_modified int(10) unsigned NOT NULL default '0',
  subject tinytext NOT NULL,
  poster_name tinytext NOT NULL,
  poster_email tinytext NOT NULL,
  poster_ip tinytext NOT NULL,
  smileys_enabled tinyint(4) NOT NULL default '1',
  modified_time int(10) unsigned NOT NULL default '0',
  modified_name tinytext NOT NULL,
  body text NOT NULL,
  icon varchar(16) NOT NULL default 'xx',
  approved tinyint(3) NOT NULL default '1',
  PRIMARY KEY (id_msg),
  UNIQUE topic (id_topic, id_msg),
  UNIQUE id_board (id_board, id_msg),
  UNIQUE id_member (id_member, id_msg),
  KEY approved (approved),
  KEY ip_index (poster_ip(15), id_topic),
  KEY participation (id_member, id_topic),
  KEY show_posts (id_member, id_board),
  KEY id_topic (id_topic)
) TYPE=MyISAM;

почему UNIQUE стоит именно там?

я вот набрал и не могу найти

Достаточно того, что MySQL сам построит индексы по полю, если при объявлении структуры таблицы мы зададим полю уникальность UNIQUE, говорящую о том, что данное поле не может хранить два одинаковых значения.

# KEY является синонимом для INDEX.
# В MySQL ключ UNIQUE может иметь только различающиеся значения. При попытке добавить новую строку с ключом, совпадающим с существующей строкой, возникает ошибка выполнения команды.
# PRIMARY KEY представляет собой уникальный ключ KEY с дополнительным ограничением, что все столбцы с данным ключом должны быть определены как NOT NULL. В MySQL этот ключ называется PRIMARY (первичный). Таблица может иметь только один первичный ключ PRIMARY KEY. Если PRIMARY KEY отсутствует в таблицах, а некоторое приложение запрашивает его, то MySQL может превратить в PRIMARY KEY первый ключ UNIQUE, не имеющий ни одного столбца NULL.
# PRIMARY KEY может быть многостолбцовым индексом. Однако нельзя создать многостолбцовый индекс, используя в определении столбца атрибут ключа PRIMARY KEY. Именно таким образом только один столбец будет отмечен как первичный. Необходимо использовать синтаксис PRIMARY KEY(index_col_name, ...).
# Если ключ PRIMARY или UNIQUE состоит только из одного столбца и он принадлежит к числовому типу, то на него можно сослаться также как на _rowid (новшество версии 3.23.11).
# Если индексу не назначено имя, то ему будет присвоено первое имя в index_col_name, возможно, с суффиксами (_2, _3, ...), делающими это имя уникальным. Имена индексов для таблицы можно увидеть, используя SHOW INDEX FROM tbl_name. SHOW Syntax.

Достаточно того, что MySQL сам построит индексы по полю, если при объявлении структуры таблицы мы зададим полю уникальность UNIQUE, говорящую о том, что данное поле не может хранить два одинаковых значения.

UNIQUE — опция для INDEX. Используется для указания того, что соотвествующий столбец должен содержать уникальные значения. При указании UNIQUE в MySQL слово INDEX можно опускать (т.к. UNIQUE-столбцы в любом случае будут проиндексированы, но это не даст вставить в столбец неуникальные данные).

как это понимать вообще про UNIQUE? два одинаковых не может, тогда сколько? одно значение должно быть? нафига тогда это поле?
в чем разница между UNIQUE и PRIMARY?

zg · Непрочитанное сообщение zg » 2009-12-04 6:32:05

ProFTP писал(а):KEY `word` (`word`,`page`)

мде... смысла в этом мало. Но это просто составной индекс.

ProFTP писал(а):как это понимать вообще про UNIQUE?

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

ProFTP писал(а):в чем разница между UNIQUE и PRIMARY?

если тебе что-то это скажет:
1. UNIQUE может хранить NULL, PRIMARY нет.
2. PRIMARY может быть только один на таблицу, UNIQUE несколько.
Больше различий нет.

forum.lissyara.su

Индексы в Mysql

Индексы в Mysql

Услуги хостинговой компании Host-Food.ru

Re: Индексы в Mysql

Re: Индексы в Mysql

Re: Индексы в Mysql

Re: Индексы в Mysql