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

Question

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

Как создать индекс в части даты поля 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'

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

EDIT:

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

451 13

mysql

13 ответов:

Comments

Ничего не найдено.

Michael Johnson · Accepted Answer · 2016-02-09 23:59:48

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

то, что я бы сделал, это что-то вроде:
SELECT * FROM transactionlist 
WHERE TranDateTime BETWEEN '2008-08-17 00:00:00' AND '2008-08-18 23:59:59';
Это должно дать вам все, что произошло на 2008-08-17, и все, что произошло ровно в 2008-08-18 00:00:00. Если это проблема, вы можете изменить второй термин на '2008-08-17 23:59: 59' и просто получить 2008-08-17.

Mike Tunnicliffe · Accepted Answer · 2008-09-18 21:22:34

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

MarkR · Accepted Answer · 2008-09-18 22:03:18

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

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

Я думаю, что вы найдете это
SELECT * FROM transactionlist WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-18'
эффективен и делает то, что вы хотите.

Liran Brimer · Accepted Answer · 2017-06-14 18:13:59

другой вариант (актуально для версии 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;

Clinton Pierce · Accepted Answer · 2008-09-18 21:21:23

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

потом просто поиска:
 select * from translist 
     where TranDateTime > '2008-08-16 23:59:59'
        and TranDateTime < '2008-08-18 00:00:00'
если индексы являются b-деревьями или чем-то еще, что разумно, они должны быть найдены быстро.

Ray Jenkins · Accepted Answer · 2008-09-18 21:25:19

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

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

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

Valentin Rusk · Accepted Answer · 2012-02-27 18:16:01

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

Dr. Tyrell · Accepted Answer · 2014-10-17 07:19:25

datetime как что-то% не будет ловить индекс либо.

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

nathan · Accepted Answer · 2008-09-18 21:20:52

Что значит "объяснить"? (запустите EXPLAIN SELECT * FROM transactionlist where date (TranDateTime) = '2008-08-17')

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

выберите * из списка транзакций, где TranDateTime > = '2008-08-17' и TranDateTime

Justsalt · Accepted Answer · 2008-09-18 21:24:27

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

Где TranDateTime > ' 2008-08-17 00: 00: 00 ' и TranDateTime

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

Mari · Accepted Answer · 2012-11-15 05:19:34

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

antonia007 · Accepted Answer · 2012-12-28 03:13:11

Я не знаю о специфике 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 на мой взгляд, не имеет никакого значения (я обычно использовал его в этом формате).

Nitro Chummy · Accepted Answer · 2014-06-24 03:23:40

почему никто не предложил использовать LIKE? Разве это не делает работу также? Будет ли это так же быстро, как между ними?
SELECT * FROM transactionlist where TranDateTime LIKE '2008-08-17%'