Наиболее эффективный способ сравнения больших значений типа "текст" в MySQL с использованием движка InnoDB

#mysql #text #blob #innodb #query-performance

#mysql #текст #большой двоичный объект #innodb #запрос-производительность

Вопрос:

У меня есть промежуточная таблица, подобная этой :

 CREATE TABLE `staging` (
  `created_here_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `desc_text`  TEXT NOT NULL );

И таблица назначения как :

 CREATE TABLE `final_tbl` (
  `row_id` BIGINT NOT NULL AUTO_INCREMENT,
  `created_here_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `desc_text`  TEXT NOT NULL );

Я хочу вставить desc_text в final_tbl, только если он отсутствует. Я имею в виду два варианта :

проверьте, существует ли staging.desc_text в final_tbl.desc_text, если нет, то вставьте в final_tbl
Поддерживайте столбец в «final_tbl», в котором будет храниться значение SHA224 столбца desc_text. сравните значение SHA224 для staging.desc_text со столбцом SHA224 в итоговой таблице, а затем решите, вставлять или игнорировать.

Я хочу знать, какой вариант будет быстрее?

Ответ №1:

Хммм . . .

Создайте столбец SHA224 с индексом:

 create index unq_final_tbl_sha224 on final_tbl(sha224);

Затем выполните обновление следующим образом:

 insert into final_tbl(desc_text, sha224)
    select *
    from (select desc_text, sha224
          from staging s
          where not exists (select 1 from final_tbl f where f.ssh224 = s.ssh224)
         ) s
    where not exists (select 1 from final_tbl f where f.desc_text = s.desc_text);

Идея подзапроса заключается в том, чтобы быть абсолютно уверенным, что MySQL не получит никаких идей о сравнении длинной формы поля перед сравнением хэш-значения. Вероятно, безопасно использовать and без подзапроса, но приведенный выше вариант более консервативен.

1. Спасибо, Гордон, вместо вашего предложения, если я использую » вставить игнорирование в final_tbl ( desc_text, sha224) выберите desc_text, sha224 из промежуточного «. При вставке записей в final_tbl, не будет ли mysql сначала проверять столбец уникального ключа и сразу после этого принимать решение, вместо сравнения столбца ‘desc_text’?

2.@abb . . . Я думал о чем-то подобном, но если у вас есть два разных desc_text значения с одинаковым значением sha224, то второе не будет вставлено. Такое столкновение с хешированием довольно редко, но не невозможно.

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

4. Хорошая мысль @RickJames , шансы даже редки с SHA2. Зависимость от уникального ключа, сгенерированного SHA2, незначительна.

Ответ №2:

MySQL 5.7 поддерживает сгенерированные столбцы.

Создайте хэш-поле SHA-512 в desc_text:

 ALTER TABLE final_tbl ADD sha512 AS SHA2(desc_text, 512);

и добавьте к нему уникальный индекс:

 ALTER TABLE final_tbl ADD UNIQUE (sha512);

Затем при дублировании хэша вы получите сообщение об ошибке:

 mysql> insert into final_tbl(desc_text) values('aaa');
ERROR 1062 (23000): Duplicate entry 'd6f644b19812e97b5d871658d6d3400ecd4787faeb9b8990c1e7608288664be7' for key 'sha512'