Оптимизация поискового запроса MySQL - программирование
Подтвердить что ты не робот

Оптимизация поискового запроса MySQL

Вам нужна ваша помощь в оптимизации одного запроса mysql. Например, возьмем простую таблицу.

CREATE TABLE `Modules` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `moduleName` varchar(100) NOT NULL,
 `menuName` varchar(255) NOT NULL,
PRIMARY KEY (`ID`),
KEY `moduleName` (`moduleName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Позволяет заполнить его некоторыми данными:

INSERT INTO  `Modules` (`moduleName` ,`menuName`)
VALUES 
    ('abc1',  'name1'), 
    ('abc',  'name2'), 
    ('ddf',  'name3'), 
    ('ccc',  'name4'), 
    ('fer',  'name5');

И пример строки. Пусть это будет abc_def;

Традиционно мы пытаемся найти все строки, содержащие строку поиска.

Наоборот, моя задача - найти все строки, содержащие moduleName в строке ввода. На данный момент у меня есть следующий запрос для получения желаемого результата:

SELECT `moduleName` ,`menuName` 
FROM `Modules` 
WHERE 'abc_def' LIKE(CONCAT(`moduleName`,'%'))

Это вернет

moduleName   | menuName 
---------------------------
abc          | name2

Проблема заключается в том, что этот запрос не использует индекс.

Есть ли способ заставить его использовать его?

4b9b3361

ОТВЕТЫ

Ответ 1

Кажется, вы неправильно поняли, что такое индекс и как он может помочь ускорить запрос.

Посмотрите, каков ваш индекс moduleName. Это, в основном, отсортированный список сопоставлений от moduleName до ID. И что вы выбираете?

SELECT moduleName, menuName 
FROM Modules
WHERE 'abc_def' LIKE CONCAT(moduleName,'%');

Вот вам два поля для каждой строки, которые имеют какое-то отношение к некоему отображенному значению поля moduleName. Как вам может помочь индекс? Точного совпадения нет, и нет возможности воспользоваться преимуществами того, что у нас есть отсортированные имена модулей.

Что вам нужно, чтобы воспользоваться преимуществами индекса, - это проверить точное соответствие в условии:

SELECT moduleName, menuName 
FROM Modules
WHERE moduleName = LEFT('abc_def', LENGTH(moduleName));

Теперь мы имеем точное совпадение, но поскольку правильная часть условия также зависит от имени модуля, это условие будет проверяться для каждой строки. Так как в его случае MySQL не может предсказать, сколько строк будет соответствовать, но он может предсказать, что ему потребуется доступ к файлу randon для извлечения menuNames для каждой соответствующей строки, MySQL не будет использовать индекс.

Итак, у вас есть в основном два подхода:

  • если вы знаете, что условие значительно сужает число совпадающих строк, тогда вы можете просто форсировать индекс
  • Другой вариант - расширить ваш индекс до составного индекса покрытия (moduleName, menuName), тогда все результаты для запроса будут получены из индекса напрямую (то есть из памяти).

Подход №2 (см. SQLfiddle) даст вам индексный хит с простым запросом и должен предложить гораздо лучшие результаты на большая таблица. На небольших таблицах я (т.е. Lserni - см. Комментарий) не думаю, что это стоит усилий.

Ответ 2

Вы эффективно выполняете регулярное выражение на поле, поэтому никакой ключ не будет работать. Однако в вашем примере вы можете сделать его более эффективным, поскольку каждое имя moduleName должно быть меньше или равно "abc_def", поэтому вы можете добавить:

and moduleName <= 'abc_def'

Единственная альтернатива, о которой я могу думать, это:

where modleName in ('a','ab','abc','abc_','abc_d','abc_de','abc_def')

Не очень.

Ответ 3

Попробуйте добавить подсказку индекса к вашему вопросу.

SELECT `moduleName` ,`menuName` 
FROM `Modules` USE INDEX (col1_index,col2_index) 
WHERE 'abc_def' LIKE(CONCAT(`moduleName`,'%'))

Ответ 4

Так как ваш движок dtabase - это "InnoDB",   Все пользовательские данные по умолчанию в InnoDB хранятся на страницах, содержащих индекс B-дерева

B-tree are good for following lookups:
● Exact full value (= xxx)
● Range of values (BETWEEN xx AND yy)
● Column prefix (LIKE 'xx%')
● Leftmost prefix

Итак, для вашего запроса, а не для использования индекса или чего-то для оптимизации,  мы можем думать о ускорении запроса.

Вы можете ускорить запрос, создав индекс покрытия.

Индекс покрытия относится к случаю, когда all fields selected in a query are covered by an index, в этом случае InnoDB (не MyISAM) will never read the data in the table, but only use the data in the index, significantly speeding up the select. Обратите внимание, что в InnoDB первичный ключ включен во все вторичные индексы, так что все вторичные индексы являются составными индексами. Это означает, что если вы запустите следующий запрос в InnoDB:

SELECT `moduleName` ,`menuName` 
FROM `Modules1` 
WHERE 'abc_def' LIKE(CONCAT(`moduleName`,'%'))

MySQL will always use a covering index and will not access the actual table

To believe, go to **Explain**

What does Explain statement mean?

table: Указывает, на какую таблицу влияет выход.

type: Показывает, какой тип соединения используется. От лучшего до худшего   типы: system, const, eq_ref, ref, range, index, all

possible_keys: Указывает, какие индексы MySQL могут выбрать для поиска строк в этой таблице

key: Указывает ключ (индекс), который MySQL фактически решил использовать. Если MySQL решает использовать один из индексов возможных_keys для поиска строк, этот индекс указывается в качестве значения ключа.

key_len: Это длина используемого ключа. Чем короче, тем лучше.

ref: Какой столбец (или константа) используется

rows: Число строк MySQL считает, что он должен проверять выполнение запроса.

extra Extra info: Плохие, чтобы увидеть здесь "использование временных" и "использование filesort"

У меня было 1990 строк.

Мои эксперименты:

Я бы рекомендовал решение Isern для where where

    case 1) no indexes
explain select `moduleName` ,`menuName`  FROM `Modules1` WHERE moduleName = SUBSTRING('abc_def', 1, LENGTH(moduleName));
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | Modules | ALL  | NULL          | NULL | NULL    | NULL | 2156 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Способы создания индексов покрытия

case 2) ALTER TABLE `test`.`Modules1` ADD index `mod_name` (`moduleName`)

explain select `moduleName` ,`menuName`  FROM `Modules1` WHERE moduleName = SUBSTRING('abc_def', 1, LENGTH(moduleName));
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | Modules | ALL  | NULL          | NULL | NULL    | NULL | 2156 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

Здесь он показывает используемый индекс. См. Столбцы: клавиша, Экстра

case 3) ALTER TABLE  `test`.`Modules1` DROP INDEX  `mod_name` ,
ADD INDEX  `mod_name` (  `moduleName` ,  `menuName` )

  explain select `moduleName` ,`menuName`  FROM `Modules1` WHERE moduleName = SUBSTRING('abc_def', 1, LENGTH(moduleName));
+----+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+
| id | select_type | table    | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+----+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | Modules | index | NULL          | mod_name | 1069    | NULL | 2066 | Using where; Using index |
+----+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+
1 row in set (0.00 sec)


case 4) ALTER TABLE  `test`.`Modules1` DROP INDEX  `mod_name` ,
ADD INDEX  `mod_name` (  `ID` ,  `moduleName` ,  `menuName` )

  explain select `moduleName` ,`menuName`  FROM `Modules1` WHERE moduleName = SUBSTRING('abc_def', 1, LENGTH(moduleName));
+----+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+
| id | select_type | table    | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+----+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | Modules | index | NULL          | mod_name | 1073    | NULL | 2061 | Using where; Using index |
+----+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

изменить:

use where moduleName regexp "^(a|ab|abc|abc_|abc_d|abc_de|abc_def)$";
in place  of substring()

Ответ 5

DECLARE @SEARCHING_TEXT AS VARCHAR (500)

SET @SEARCHING_TEXT = 'ab'

SELECT 'moduleName', 'menuName' FROM [MODULES] WHERE FREETEXT (MODULENAME, @SEARCHING_TEXT);

Ответ 6

Я не уверен, что это действительно хороший запрос, но он использует индекс:

SELECT `moduleName` ,`menuName`
FROM `Modules` WHERE LEFT('abc_def', 7) = `moduleName`
UNION ALL
SELECT `moduleName` ,`menuName`
FROM `Modules` WHERE LEFT('abc_def', 6) = `moduleName`
UNION ALL
SELECT `moduleName` ,`menuName`
FROM `Modules` WHERE LEFT('abc_def', 5) = `moduleName`
UNION ALL
SELECT `moduleName` ,`menuName`
FROM `Modules` WHERE LEFT('abc_def', 4) = `moduleName`
UNION ALL
SELECT `moduleName` ,`menuName`
FROM `Modules` WHERE LEFT('abc_def', 3) = `moduleName`
UNION ALL
SELECT `moduleName` ,`menuName`
FROM `Modules` WHERE LEFT('abc_def', 2) = `moduleName`
UNION ALL
SELECT `moduleName` ,`menuName`
FROM `Modules` WHERE LEFT('abc_def', 1) = `moduleName`

Общее решение

И это общее решение, используя динамический запрос:

SET @search='abc_def';

SELECT
  CONCAT(
    'SELECT `moduleName` ,`menuName` FROM `Modules` WHERE ',
    GROUP_CONCAT(
      CONCAT(
        'moduleName=\'',
        LEFT(@search, ln),
        '\'') SEPARATOR ' OR ')
    )
FROM
  (SELECT DISTINCT LENGTH(moduleName) ln
   FROM Modules
   WHERE LENGTH(moduleName)<=LENGTH(@search)) s
INTO @sql;

Это создаст строку с SQL-запросом, у которой есть условие WHERE moduleName='abc' OR moduleName='abc_' OR ..., и она должна иметь возможность быстро создавать строку из-за индекса (если нет, ее можно улучшить с помощью временной индексированной таблицы с числами от 1 до максимально допустимой длины строки, например, в скрипте). Затем вы можете просто выполнить запрос:

PREPARE stmt FROM @sql;
EXECUTE stmt;

Смотрите здесь скрипку здесь.

Ответ 7

мой ответ может быть более сложным

alter table Modules add column name_index int
alter table Modules add index name_integer_index(name_index);

когда вы вставляете в таблицу модулей, вы caculate значение int moduleName, что-то вроде select ascii('a')

когда вы запускаете свой запрос, вам просто нужно запустить

SELECT `moduleName`, `menuName`
FROM   `Modules`
WHERE  name_index >
  (select ascii('a')) and name_index < (select ascii('abc_def'))

он будет использовать name_integr_index

Ответ 8

Подобно предложению fthiella, но более гибкому (поскольку он может легко справляться с более длинной строкой): -

SELECT DISTINCT `moduleName` ,`menuName`
FROM `Modules`
CROSS JOIN (SELECT a.i + b.i * 10 + c.i * 100 + 1 AS anInt FROM integers a, integers b, integers c) Sub1
WHERE LEFT('abc_def', Sub1.anInt) = `moduleName`

Это (как указано) обрабатывает строку длиной до 1000 символов, но медленнее, чем решение fthiellas. Можно легко сократить для строк длиной до 100 символов, и в этот момент это кажется немного быстрее, чем решение fthiellas.

Вставка проверки длины в нем ускоряет его: -

SELECT SQL_NO_CACHE  DISTINCT `moduleName` ,`menuName`
FROM `Modules`
INNER JOIN (SELECT a.i + b.i * 10 + c.i * 100 + 1 AS anInt FROM integers a, integers b, integers c ) Sub1
ON Sub1.anInt <= LENGTH('abc_def') AND Sub1.anInt <= LENGTH(`moduleName`)
WHERE LEFT('abc_def', Sub1.anInt) = `moduleName`

Или с небольшой поправкой, чтобы вернуть возможные подстроки из подзапроса: -

SELECT SQL_NO_CACHE  DISTINCT `moduleName` ,`menuName`
FROM `Modules`
CROSS JOIN (SELECT DISTINCT LEFT('abc_def', a.i + b.i * 10 + c.i * 100 + 1) AS aStart FROM integers a, integers b, integers c WHERE( a.i + b.i * 10 + c.i * 100 + 1) <= LENGTH('abc_def')) Sub1
WHERE aStart = `moduleName`

Обратите внимание, что эти решения зависят от таблицы целых чисел с одним столбцом и строками со значениями от 0 до 9.

Ответ 9

подобные запросы не используют индексы... но в качестве альтернативы вы можете определить полный текстовый индекс для поиска таких строк. но движок innodb не поддерживает его, только myisam поддерживает его.

Ответ 10

Добавить индексный ключ в moduleName check http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html Характеристики индекса B-Tree для получения дополнительной информации

Не уверен, почему вы используете LIKE, всегда лучше избегать этого. Мое предложение состояло в том, чтобы все строки сохранили его в JSON, а затем выполнили поиск AJAX на нем.

Ответ 11

(предыдущая часть ответа удалена - см. новый ответ, который является тем же, но лучше для этого).

newtover approach # 2 (см. SQLfiddle) даст вам индексный хит с простым запросом, и должны предлагать более высокие показатели в более длинных таблицах:

SELECT `moduleName`, `menuName` 
FROM `Modules` 
WHERE moduleName = LEFT('abc_def', LENGTH(moduleName));

Если вам нужны данные из большого количества столбцов (а не только menuName), т.е. если Modules больше и больше, вам может быть лучше обслуживать перемещение moduleName в таблицу поиска, содержащую только ID, moduleName и ее длину (для сохранения одного вызова функции).

Необходимое дополнительное пространство невелик, и если moduleName имеет низкую мощность, т.е. у вас осталось немного moduleName, повторяющихся по лотам menuName s, вы действительно можете сохранить значительное пространство.

Новая схема будет:

moduleName_id    integer, keys to Lookup.id
...all the fields in Modules except moduleName...


Lookup table
   id            primary key
   moduleName    varchar
   moduleLength  integer

и запрос:

SELECT `Lookup`.`moduleName`,`menuName` 
FROM `Modules` INNER JOIN `Lookup`
    ON (`Modules`.`moduleName_id` = Lookup.id)
WHERE `Lookup`.`moduleName` = LEFT('abc_def',
         `Lookup`.`moduleLength`);

Этот SQLfiddle начинается с вашей схемы и изменяет ее для достижения вышеуказанного. Усовершенствования скорости и пространства хранилища сильно зависят от данных, которые вы помещаете в таблицы. Я намеренно поставил себя в лучших условиях (много коротких полей в модулях, в среднем сто menuName для каждого moduleName), и смог сэкономить около 30% пространства для хранения; результаты поиска были примерно в 3 раза быстрее, и, вероятно, были предвзяты кэшированием ввода-вывода, поэтому, если кто-то не проведет более тщательное тестирование, я оставил бы его с "заметной экономией места и времени".

С другой стороны, на небольших простых таблицах и равном количестве меню и модулей (т.е. 1:1) будет незначительный штраф за хранение без заметного увеличения скорости. В этой ситуации, однако, пространства и время будут очень маленькими, поэтому, возможно, более "нормализованная" форма выше может быть продолжением, несмотря на добавленную сложность.

Ответ 12

Мы можем достичь с помощью одного самого функционала instead двух функций как SUBSTRING ('abc_def', 1, LENGTH (moduleName))

where locate(moduleName, 'abc_def');