MySQL/MariaDB에서 두 개의 큰 테이블을 결합하는 방법

MySQL/MariaDB에서 두 개의 큰 테이블을 결합하는 방법

테이블이 두 개 있어요.kw250000개의 레코드와t_n10만 장의 기록을 가지고 있습니다.
두 테이블을 모두 결합하여 다음 쿼리에 대한 총 결과를 얻었습니다.

SELECT kw.id AS kw_id,
       kw.word AS kw_word,
       t_n.translation AS t_n_translation
FROM kw, t_n 
WHERE kw.id = t_n.keyword_id
  AND kw.word LIKE '1%' 
GROUP BY t_n.translation 
LIMIT 10

또한 위의 쿼리 대신 간단한 결합을 시도했습니다.

SELECT kw.id AS kw_id,
       kw.word AS kw_word,
       t_n.translation AS t_n_translation
FROM kw 
INNER JOIN t_n
    ON kw.id = t_n.keyword_id
   AND kw.word LIKE '1%' 
GROUP BY t_n.translation 
LIMIT 10

그러나 두 쿼리 모두 메모리 및 처리 시스템 리소스에 따라 약 60~150초가 소요되었습니다.데이터베이스를 확장하고 여러 사용자를 늘리는 데 이 실행 시간 초과는 견딜 수 없습니다.
큰 테이블 두 개를 연결하는 효율적인 방법이 있을까요?

갱신하다
이 쿼리는 JQuery UI 자동 완성용입니다.GROUP BY Clause를 사용하여 명확한 값을 얻습니다.명확한 가치를 얻을 수 있는 다른 적절한 솔루션이 있습니까?

테이블 구조

--
-- Table structure for table `kw`
--

CREATE TABLE `kw` (
  `id` int(32) NOT NULL,
  `word` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `pos` varchar(12) NOT NULL,
  `definition` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `t_n`
--

CREATE TABLE `t_n` (
  `id` int(16) NOT NULL,
  `keyword_id` int(16) NOT NULL,
  `translation` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `created_on` datetime NOT NULL,
  `user_id` varchar(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `kw`
--
ALTER TABLE `kw`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `t_n`
--
ALTER TABLE `t_n`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `kw`
--
ALTER TABLE `kw`
  MODIFY `id` int(32) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `t_n`
--
ALTER TABLE `t_n`
  MODIFY `id` int(16) NOT NULL AUTO_INCREMENT;
COMMIT;

당신이 원하는 것은 다음과 같습니다.

SELECT kw.id as kw_id, kw.word as kw_word, t_n.translation as t_n_translation
FROM kw INNER JOIN
     t_n
     ON kw.id = t_n.keyword_id AND kw.word LIKE '1%' ;

이 쿼리에 대해 인덱스를 지정하려는 경우kw(word, id)그리고.t_n(keyword_id):

create index idx_kw_word_id on kw(word, id);
create index idx_t_n_keyword_id on t_n(keyword_id);

적절한 방법은JOIN ON표가 어떻게 관련되어 있는지와WHERE필터링:

SELECT kw.id AS kw_id,
       kw.word AS kw_word,
       t_n.translation AS t_n_translation
FROM kw 
INNER JOIN t_n
    ON kw.id = t_n.keyword_id
WHERE kw.word LIKE '1%' 
ORDER BY t_n.translation 
LIMIT 10

이 불명확한 것은GROUP BY필요하거나 적절합니다.용도를 설명해 주시고, 없으면 어떻게 되는지 알려주세요.aLIMIT없이ORDER BY거의 의미가 없습니다.아마 당신은ORDER BY 대신 GROUP BY(그래서 내가 변경했어)

만약 당신이 아무것도 없다고 가정한다면TEXT열, 이 인덱스가 도움이 됩니다.

kw:   INDEX(word, id)
t_n:  INDEX(keyword_id, translation)

이러한 인덱스는 '복합'이지만 그다지 관련이 없으며 퍼포먼스가 향상되는 '커버링'도 있습니다.

내가 볼 수 있다면SHOW CREATE TABLE, 의 변경 등, 한층 더 개선되는 경우가 있습니다.PRIMARY KEY.

더

int(32)-- 그(32)아무 의미도 없어요 INT는 항상 32비트(4바이트) 숫자입니다.SMALLINT는 16비트(2바이트) 숫자입니다.

인 것 같다keyword_id에 있어서 유일할 것이다t_n만약 그렇다면, 그것을PRIMARY KEY완전히 없애버리면id.

그 시점에서 두 테이블이 하나로 합쳐질 수 있을 것 같습니까?여러 가지 다른 단어들이 있는데, 각각 하나의 번역이 있는 건가요?하지만 두 테이블이 크기가 다른 걸 보니 아닐 수도 있어요.스키마를 설명해 주세요.

다음 솔루션에 대해 조언할 수 있습니다.

1. 인덱스 추가:

   CREATE INDEX keyword_id_ix on t_n(keyword_id);

2. 를 사용하여 큰 테이블을 필터링LIKE:

   SELECT * FROM kw WHERE  kw.word LIKE '1%'

3. 다음을 사용하여 결과를 두 번째 표와 결합JOIN:

   SELECT
       kw.id as kw_id, kw.word as kw_word, t_n.translation as t_n_translation
   FROM (
       SELECT * FROM kw WHERE  kw.word LIKE '1%'
   ) kw 
   INNER JOIN t_n ON kw.id = t_n.keyword_id;
   
   

SQL finder(여기에 있음)

언급URL:https://stackoverflow.com/questions/63629290/how-to-join-two-huge-tables-in-mysql-mariadb