Доработка схемы БД с учетом обратной совместимости для ускорения

Для ускорения выборки запросы с JOIN следует переписать на обычный SELECT, для чего изменить схему данных, а именно перенести часть полей из большой таблицы FieldMapping (1,5М записей) в таблицу 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)

Хранимая процедура для приведения к типу DATETIME:

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');

Триггеры поддерживают целостность данных, а за счет «денормализации» и индексации получаем выигрыш в скорости. Триггеры не сделаны для 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.