MySql에서 DATETIME 필드의 날짜 부분에 인덱스를 만드는 방법

MySql에서 DATETIME 필드의 날짜 부분에 인덱스를 만드는 방법

---

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' 

시간이 오래 걸립니다.

편집하다:

"에이 블로그 게시물을 살펴 가지고 왜 MySQL을의 DATETIME은 피해야한다 수 있습니다 "

---

올바르게 기억하면 함수를 통해 열을 전달하기 때문에 전체 테이블 스캔이 실행됩니다. MySQL은 쿼리 최적화 프로그램이 함수의 결과를 실제로 알 수 없기 때문에 인덱스를 우회하여 모든 열에 대해 순종적으로 함수를 실행합니다.

내가 할 일은 다음과 같습니다.

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

그것은 당신에게 2008-08-17에 일어난 모든 것을 줄 것입니다.

---

귀엽게 들리려는 것은 아니지만 간단한 방법은 날짜 부분과 색인 만 포함 된 새 열을 추가하는 것입니다.

---

날짜 부분에만 인덱스를 만들 수 없습니다. 당신이해야하는 이유가 있습니까?

날짜 부분에만 인덱스를 만들 수 있더라도 옵티마이 저는 여전히 위 쿼리에 인덱스를 사용하지 않을 것입니다.

나는 당신이 그것을 찾을 것이라고 생각합니다

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

효율적이고 원하는 것을 수행합니다.

---

다른 옵션 ( 버전 5.7.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;

---

mySql의 세부 사항에 대해서는 모르지만 날짜 필드 전체를 인덱싱하는 데 어떤 해가 있습니까?

그런 다음 검색 :

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

인덱스가 B- 트리이거나 합리적인 다른 것이라면 빠르게 찾을 수 있습니다.

---

Valeriy Kravchuk은 MySQL 사이트에서이 문제에 대한 기능 요청에 대해이 방법을 사용한다고 말했습니다.

"그동안 DATETIME 값을 문자열로 저장하기 위해 문자 열을 사용할 수 있습니다. 처음 N 개의 문자 만 인덱싱됩니다. MySQL 5에서 트리거를 신중하게 사용하면이 아이디어를 기반으로 합리적으로 강력한 솔루션을 만들 수 있습니다."

이 열을 추가하기 쉬운 루틴을 작성하고 트리거를 사용하여이 열을 동기화 상태로 유지할 수 있습니다. 이 문자열 열의 인덱스는 매우 빠릅니다.

---

The one and good solution that is pretty good working is to use timestamp as time, rather than datetime. It is stored as INT and being indexed good enough. Personally i encountered such problem on transactions table, that has about million records and slowed down hard, finally i pointed out that this caused by bad indexed field (datetime). Now it runs very quick.

---

I don't know about the specifics of mySQL, but what's the harm in just indexing the date field in its entirety?

If you use functional magic for * trees, hashes, ... is gone, because for obtaining values you must call the function. But, because you do not know the results ahead, you have to do a full scan of the table.

There is nothing to add.

Maybe you mean something like computed (calculated?) indexes... but to date, I have only seen this in Intersystems Caché. I don't think there's a case in relational databases (AFAIK).

A good solution, in my opinion, is the following (updated clintp example):

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

Whether you use 00:00:00.0000 or 00:00 in my opinion makes no difference (I've generally used it in this format).

---

datetime LIKE something% will not catch the index either.

Use this: WHERE datetime_field >= curdate();
That will catch the index,
and cover today:00:00:00 up to today:23:59:59
Done.

---

What does 'explain' say? (run EXPLAIN SELECT * FROM transactionlist where date(TranDateTime) = '2008-08-17')

If it's not using your index because of the date() function, a range query should run fast:

SELECT * FROM transactionlist where TranDateTime >= '2008-08-17' AND TranDateTime < '2008-08-18'

---

Rather than making an index based on a function (if that is even possible in mysql) make your where clause do a range comparison. Something like:

Where TranDateTime > '2008-08-17 00:00:00' and TranDateTime < '2008-08-17 11:59:59')

This lets the DB use the index on TranDateTime (there is one, right?) to do the select.

---

If modifying the table is an option, or you're writing a new one, consider storing date and time in separate columns with respective types. You get performance by having a much smaller key space, and reduced storage (compared to a date-only column derived from a datetime). This also makes it feasible to use in compound keys, even before other columns.

In OP's case:

+-------------------+------------------+------+-----+---------+----------------+
| 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       |                |
+-------------------+------------------+------+-----+---------+----------------+

---

Create a new fields with just the dates convert(datetime, left(date_field,10)) and then index that.

참고URL : https://stackoverflow.com/questions/95183/how-does-one-create-an-index-on-the-date-part-of-datetime-field-in-mysql