Доработка схемы БД: обратная совместимость и ускорение после MSSQL→MySQL

Денормализация для скорости: часть данных из большой таблицы FieldMapping переносится в Document; обратную совместимость обеспечивают триггеры и функция разбора дат.

Избавляемся от JOIN

Для ускорения выборки запросы с JOIN переписываются на обычный SELECT: часть полей из FieldMapping (≈1,5M строк) добавляются в Document.

ALTER TABLE `Document`
ADD `FM_DateCreated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
ADD `FM_KodClient` CHAR(8) NOT NULL DEFAULT '',
ADD `FM_InECP` tinyint(1) UNSIGNED NOT NULL DEFAULT '0';

Заполнение «зеркальных» колонок — триггерами и процедурами; поля не обновляют напрямую. По ним строится индекс под типовые фильтры.

Приведение даты

Исходные строки могли быть в форматах 2-4-2012 10:57 и 13.03.2012 16:23:11.

SELECT * FROM FieldMapping WHERE FieldID = 25 AND FieldValue NOT REGEXP '[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*'
AND FieldValue NOT REGEXP '[0-9]*\.[0-9]*\.[0-9]* [0-9]*:[0-9]*:[0-9]*'
LIMIT 10;
-- Empty SET (4.13 sec)

Функция PARSE_DATE:

DROP FUNCTION IF EXISTS `PARSE_DATE`;

DELIMITER $$

CREATE FUNCTION PARSE_DATE(str VARCHAR(255))
RETURNS DATETIME
BEGIN

  SELECT SUBSTRING(str, 1, LOCATE(' ', str) - 1) INTO @mydate;
  SELECT SUBSTRING(str, LOCATE(' ', str) + 1, 100) INTO @mytime;

  IF @mydate REGEXP '[0-9]*-[0-9]*-[0-9]*' THEN
    SET @pos1 = LOCATE('-', @mydate);
    SET @pos2 = LOCATE('-', @mydate, @pos1 + 1);
    SET @DAY = SUBSTRING(@mydate, 1, @pos1 - 1);
    SET @MONTH = SUBSTRING(@mydate, @pos1 + 1, @pos2 - @pos1 - 1);
    SET @YEAR = SUBSTRING(@mydate, @pos2 + 1, 100);
  ELSE
    SET @pos1 = LOCATE('.', @mydate);
    SET @pos2 = LOCATE('.', @mydate, @pos1 + 1);
    SET @DAY = SUBSTRING(@mydate, 1, @pos1 - 1);
    SET @MONTH = SUBSTRING(@mydate, @pos1 + 1, @pos2 - @pos1 - 1);
    SET @YEAR = SUBSTRING(@mydate, @pos2 + 1, 100);
  END IF;

  RETURN CAST(CONCAT(@YEAR,'-', @MONTH, '-', @DAY, ' ', @mytime) AS DATETIME);

END$$

DELIMITER ;

-- test cases:

SELECT PARSE_DATE('13.03.2012 16:23:11');
SELECT PARSE_DATE('03.04.2012 9:34:59');
SELECT PARSE_DATE('09.06.2015 16:14:10');
SELECT PARSE_DATE('13-3-2012 10:56');
SELECT PARSE_DATE('2-4-2012 10:57');
SELECT PARSE_DATE('29-5-2013 17:2');

Триггеры

Триггеры на INSERT/UPDATE в FieldMapping; для DELETE не делались (событие не ожидается).

DROP TRIGGER IF EXISTS `date_created_insert`;
DROP TRIGGER IF EXISTS `date_created_update`;

DELIMITER $$

  CREATE TRIGGER `date_created_insert` BEFORE INSERT ON `FieldMapping`
    FOR EACH ROW BEGIN
      IF NEW.FieldID = 25 THEN
        UPDATE Document SET FM_DateCreated = PARSE_DATE(NEW.FieldValue) WHERE ID = NEW.DocumentID;
      ELSEIF NEW.FieldID = 39 THEN
        UPDATE Document SET FM_KodClient = NEW.FieldValue WHERE ID = NEW.DocumentID;
      ELSEIF NEW.FieldID = 22 THEN
        UPDATE Document SET FM_InECP = IF(LOWER(NEW.FieldValue) = 'true', 1, 0) WHERE ID = NEW.DocumentID;
      END IF;
    END;
  $$

  CREATE TRIGGER `date_created_update` BEFORE UPDATE ON `FieldMapping`
    FOR EACH ROW BEGIN
      IF NEW.FieldID = 25 THEN
        UPDATE Document SET FM_DateCreated = PARSE_DATE(NEW.FieldValue) WHERE ID = NEW.DocumentID;
      ELSEIF NEW.FieldID = 39 THEN
        UPDATE Document SET FM_KodClient = NEW.FieldValue WHERE ID = NEW.DocumentID;
      ELSEIF NEW.FieldID = 22 THEN
        UPDATE Document SET FM_InECP = IF(LOWER(NEW.FieldValue) = 'true', 1, 0) WHERE ID = NEW.DocumentID;
      END IF;
    END;
  $$

DELIMITER ;

Индексация

Типовой запрос:

SELECT SQL_CALC_FOUND_ROWS Document.ID, Document.IDEDO, Document.SigOne, Document.SigTwo, Document.KeyOne, Document.KeyTwo, Document.NameFile, Document.IsError
FROM Document WHERE Document.FM_KodClient = '00007060' AND Document.SigOne = 1 AND Document.SigTwo = 0
ORDER BY Document.FM_DateCreated DESC, Document.ID DESC LIMIT 0, 10

Индекс:

ALTER TABLE `Document` ADD INDEX `idx_search`(FM_KodClient, SigOne, SigTwo, FM_DateCreated, ID);

Без индекса: ~0,11 с; с индексом: ~0,01 с.

Полнотекст и поиск по полям — отдельно через Sphinx.