Как создать индекс в части даты поля DATETIME в MySql

mysql

55034 просмотра

13 ответа

Как мне создать индекс для части даты поля DATETIME?

mysql> SHOW COLUMNS FROM transactionlist;
+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| TransactionNumber | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| WagerId           | int(11)          | YES  | MUL | 0       |                |
| TranNum           | int(11)          | YES  | MUL | 0       |                |
| TranDateTime      | datetime         | NO   |     | NULL    |                |
| Amount            | double           | YES  |     | 0       |                |
| Action            | smallint(6)      | YES  |     | 0       |                |
| Uid               | int(11)          | YES  |     | 1       |                |
| AuthId            | int(11)          | YES  |     | 1       |                |
+-------------------+------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

TranDateTime используется для сохранения даты и времени транзакции, как это происходит

Моя таблица содержит более 1 000 000 записей и заявление

SELECT * FROM transactionlist where date(TranDateTime) = '2008-08-17' 

занимает много времени.

РЕДАКТИРОВАТЬ:

Посмотрите на этот пост в блоге « Почему DATETIME в MySQL можно и нужно избегать »

Автор: Charles Faiga Источник Размещён: 17.05.2019 03:10

Ответы (13)


56 плюса

Решение

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

Что бы я сделал, это что-то вроде:

SELECT * FROM transactionlist 
WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-17 23:59:59.999999';

Это должно дать вам все, что произошло 2008-08-17.

Автор: Michael Johnson Размещён: 18.09.2008 06:21

9 плюса

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

Автор: Mike Tunnicliffe Размещён: 18.09.2008 06:22

8 плюса

Вы не можете создать индекс только для части даты. Есть ли причина, по которой вы должны это сделать?

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

Я думаю, вы найдете это

SELECT * FROM transactionlist WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-18'

Эффективен и делает то, что вы хотите.

Автор: MarkR Размещён: 18.09.2008 07:03

6 плюса

Другой вариант (актуально для версии 7.5.3 и выше) - создать сгенерированный / виртуальный столбец на основе столбца datetime, а затем проиндексировать его.

CREATE TABLE `table` (
`my_datetime` datetime NOT NULL,
`my_date` varchar(12) GENERATED ALWAYS AS (DATE(`my_daetime`)) STORED,
KEY `my_idx` (`my_date`)
) ENGINE=InnoDB;
Автор: Liran Brimer Размещён: 14.06.2017 03:13

3 плюса

Я не знаю о специфике mySql, но какой вред в том, чтобы индексировать поле даты целиком?

Тогда просто ищите:

 select * from translist 
     where TranDateTime > '2008-08-16 23:59:59'
        and TranDateTime < '2008-08-18 00:00:00'

Если индексы представляют собой b-деревья или что-то еще, что разумно, они должны быть найдены быстро.

Автор: Clinton Pierce Размещён: 18.09.2008 06:21

2 плюса

Валерий Кравчук в ответ на запрос функции для этой самой проблемы на сайте MySQL сказал использовать этот метод.

«Тем временем вы можете использовать символьные столбцы для хранения значений DATETIME в виде строк, причем индексируются только первые N символов. При некотором осторожном использовании триггеров в MySQL 5 вы можете создать достаточно надежное решение на основе этой идеи».

Вы можете написать процедуру довольно легко, чтобы добавить этот столбец, а затем с помощью триггеров синхронизировать этот столбец. Индекс в этом строковом столбце должен быть довольно быстрым.

Автор: Ray Jenkins Размещён: 18.09.2008 06:25

2 плюса

Единственное и хорошее решение, которое довольно хорошо работает, - это использовать метку времени как время, а не как время. Он хранится как INT и индексируется достаточно хорошо. Лично я столкнулся с такой проблемой в таблице транзакций, которая имеет около миллиона записей и сильно замедлилась, наконец, я указал, что это вызвано неправильным индексированным полем (datetime). Теперь он работает очень быстро.

Автор: Valentin Rusk Размещён: 27.02.2012 04:16

1 плюс

Я не знаю о специфике mySQL, но какой вред в том, чтобы индексировать поле даты целиком?

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

Добавить нечего.

Может быть, вы имеете в виду что-то вроде вычисляемых (вычисляемых?) Индексов ... но на сегодняшний день я видел это только в Intersystems Caché. Я не думаю, что есть случай в реляционных базах данных (AFAIK).

На мой взгляд, хорошим решением является следующее (обновленный пример clintp):

SELECT * FROM translist 
WHERE TranDateTime >= '2008-08-17 00:00:00.0000'
  AND TranDateTime < '2008-08-18 00:00:00.0000'

Используете ли вы 00:00:00.0000или, 00:00по моему мнению, не имеет значения (я обычно использовал его в этом формате).

Автор: antonia007 Размещён: 11.03.2010 11:38

1 плюс

datetime LIKE что-то% тоже не поймает индекс.

Используйте это: WHERE datetime_field> = curdate ();
Это поймает индекс
и покроет сегодня: 00: 00: 00 и сегодня: 23: 59: 59
Готово.

Автор: Dr. Tyrell Размещён: 17.10.2014 04:19

0 плюса

Что говорит «объяснить»? (запустите EXPLAIN SELECT * FROM транзакции, где date (TranDateTime) = '2008-08-17')

Если он не использует ваш индекс из-за функции date (), запрос диапазона должен выполняться быстро:

SELECT * FROM транзакции, где TranDateTime> = '2008-08-17' И TranDateTime <'2008-08-18'

Автор: nathan Размещён: 18.09.2008 06:20

0 плюса

Вместо создания индекса, основанного на функции (если это возможно даже в mysql), сделайте предложение where для сравнения диапазонов. Что-то вроде:

Где TranDateTime> '2008-08-17 00:00:00' и TranDateTime <'2008-08-17 11:59:59')

Это позволяет БД использовать индекс для TranDateTime (он есть, верно?) Для выбора.

Автор: Justsalt Размещён: 18.09.2008 06:24

0 плюса

Создайте новые поля только с датами, convert(datetime, left(date_field,10))а затем проиндексируйте их.

Автор: Mari Размещён: 08.08.2011 09:11

0 плюса

Если модификация таблицы является опцией или вы пишете новую, рассмотрите возможность сохранения даты и времени в отдельных столбцах с соответствующими типами. Вы получаете производительность, имея намного меньшее пространство ключей и уменьшенное хранилище (по сравнению со столбцом только для даты, полученным из даты и времени). Это также делает возможным использование в составных ключах, даже перед другими столбцами.

В случае ОП:

+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| TransactionNumber | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| WagerId           | int(11)          | YES  | MUL | 0       |                |
| TranNum           | int(11)          | YES  | MUL | 0       |                |
| TranDate          | date             | NO   |     | NULL    |                |
| TranTime          | time             | NO   |     | NULL    |                |
| Amount            | double           | YES  |     | 0       |                |
| Action            | smallint(6)      | YES  |     | 0       |                |
| Uid               | int(11)          | YES  |     | 1       |                |
| AuthId            | int(11)          | YES  |     | 1       |                |
+-------------------+------------------+------+-----+---------+----------------+
Автор: Walf Размещён: 14.12.2018 05:30
Вопросы из категории :
32x32