Like и '=' равны производительности оператора в кластере sql - mysql

Question

MySQL-Cluster-GPL-7.4.12-1.el6.x86_64

Запрос 1

    select
    userinfo.username,
    userinfo.firstname,
    userinfo.lastname,
    userinfo.email,
    radcheck.attribute,
    radcheck.`value`,
    radusergroup.groupname,
    userinfo.id,
    userinfo.account_type,
    userinfo.workphone,
    userinfo.homephone,
    userinfo.mobilephone,
    userinfo.address,
    userinfo.zone,
    userinfo.account_state,
    userinfo.link_type,
    userinfo.device_owner,
    userinfo.email
    FROM
    userinfo
    INNER JOIN radcheck ON userinfo.username = radcheck.username
    INNER JOIN radusergroup ON userinfo.username = radusergroup.username
    WHERE
    radcheck.attribute='Expiration'  AND userinfo.mobilephone LIKE '9876543210%'

Этот запрос занимает около 8 секунд, чтобы завершить ниже explain вывод для запроса

    +----+-------------+--------------+------+----------------------+-----------+---------+----------------------------+------+-------------+
    | id | select_type | table        | type | possible_keys        | key       | key_len | ref                        | rows | Extra       |
    +----+-------------+--------------+------+----------------------+-----------+---------+----------------------------+------+-------------+
    |  1 | SIMPLE      | radcheck     | ref  | username,attribute   | attribute | 34      | const                      |    9 | Using where |
    |  1 | SIMPLE      | userinfo     | ref  | username,mobilephone | username  | 131     | ctradius.radcheck.username |   13 | Using where |
    |  1 | SIMPLE      | radusergroup | ref  | username             | username  | 66      | ctradius.userinfo.username |   10 | Using where |
    +----+-------------+--------------+------+----------------------+-----------+---------+----------------------------+------+-------------+

Запрос 2

Ниже приведен запрос, который завершен в < 0,005 с

    select
    userinfo.username,
    userinfo.firstname,
    userinfo.lastname,
    userinfo.email,
    radcheck.attribute,
    radcheck.`value`,
    radusergroup.groupname,
    userinfo.id,
    userinfo.account_type,
    userinfo.workphone,
    userinfo.homephone,
    userinfo.mobilephone,
    userinfo.address,
    userinfo.zone,
    userinfo.account_state,
    userinfo.link_type,
    userinfo.device_owner,
    userinfo.email
    FROM
    userinfo
    INNER JOIN radcheck ON userinfo.username = radcheck.username
    INNER JOIN radusergroup ON userinfo.username = radusergroup.username
    WHERE
    radcheck.attribute like 'Expiration%'  AND userinfo.mobilephone LIKE '9876543210%'

Ниже приведено объяснение запроса

 +----+-------------+--------------+-------+----------------------+-------------+---------+----------------------------+------+------------------------+
    | id | select_type | table        | type  | possible_keys        | key         | key_len | ref                        | rows | Extra                  |
    +----+-------------+--------------+-------+----------------------+-------------+---------+----------------------------+------+------------------------+
    |  1 | SIMPLE      | userinfo     | range | username,mobilephone | mobilephone | 203     | NULL                       |  585 | Using where; Using MRR |
    |  1 | SIMPLE      | radusergroup | ref   | username             | username    | 66      | ctradius.userinfo.username |   10 | Using where            |
    |  1 | SIMPLE      | radcheck     | ref   | username,attribute   | username    | 66      | ctradius.userinfo.username |   17 | Using where            |
    +----+-------------+--------------+-------+----------------------+-------------+---------+----------------------------+------+------------------------+

Вопрос

Почему выполнение запроса быстрее, когда like оператор (2-й запрос) вместо = (1-й запрос)?

Answer 1

У вас есть два совершенно разных плана выполнения.

Для вашего первого запроса MySQL ищет все записи с radcheck.attribute='Expiration' через указатель на radcheck.attribute (и предполагается, что есть 9 подходящих строк). Затем он объединит другие таблицы, используя username (и индексы на username) для каждого возможного имени пользователя, затем считывает значение userinfo.mobilephone со стола и видит, подходит ли он.

Для вашего второго запроса он проверит индекс на userinfo.mobilephone за все, что начинается с 9876543210 (при условии, что он найдет 585 строк). Затем он объединит другие таблицы, используя username (и индексы на username) для всех имен пользователей с правильным мобильным телефоном, затем считывает значение radcheck.attribute со стола и видит, подходит ли он.

Ваш второй запрос, конечно, будет намного быстрее, когда у вас есть только несколько строк, которые начинаются с вашего мобильного номера, но много строк с radcheck.attribute='Expiration'потому что он должен будет выполнить остальную часть выполнения (присоединение и особенно чтение из таблицы) с намного меньшим количеством строк (хотя вам нужно иметь намного больше строк, чем показано в вашем объяснении, чтобы оправдать 8 секунд).

MySQL должен угадать, какой путь быстрее и выбирает путь на основе вашего запроса и некоторых статистических данных о ваших таблицах. Он выбрал совершенно неправильно.

В вашем первом запросе MySQL предположил, что поиск = в индексе лучше, чем искать like в индексе (и даст всего 9 строк, что, очевидно, было неправильно). В вашем втором запросе он просто должен был выбрать, если искать like в первом или во втором индексе лучше - и угадал правильно. Но если бы вы, например, искали userinfo.mobilephone LIKE '0%', это могло бы быть медленнее (в зависимости от ваших данных).

Есть несколько вещей, которые вы можете попробовать:

• использование optimize table userinfo, radcheck, radusergroup, Это восстановит ваши индексы и статистику. В зависимости от ваших данных, он может больше не принимать только 9 строк для вашего первого запроса.

• заставить mysql всегда присоединяться в порядке вашего второго запроса с STRAIGHT_JOIN:

  ...
  from radcheck
  straight_join userinfo ON userinfo.username = radcheck.username
  straight_join radusergroup ON userinfo.username = radusergroup.username
  where ...
  

  Но это может привести к более медленным запросам в тех случаях, когда вы ищете, например, userinfo.mobilephone LIKE '0%'или нет условий на userinfo.mobilephone на всех, так что испытайте это при разных обстоятельствах.

• Продолжайте использовать like 'Expiration%', вы можете даже переключить его на = в тех случаях, когда у вас нет мобильного телефона или вы просто ищете его, хотя не всегда гарантированно использовать второй способ (и может измениться в других версиях mysql).

Кстати, вы могли бы получить дополнительную производительность, если бы вы заменили usernameстолбцы (целое число) idсделайте его (первую часть) первичным ключом ваших трех таблиц и используйте его id присоединиться к вашим таблицам, потому что ваши индексы станут намного меньше, и вам, например, не придется искать имя пользователя после того, как вы нашли значения для Expiration, Это должно сократить время выполнения вашего первого запроса (на основании некоторых предположений о ваших таблицах и данных, я бы предположил более чем на 50% - но это всего лишь предположение, и это может быть только 5%). Но так как это не оправдывает изменение всего приложения для этого (хотя другие запросы также выиграют от этого), вы можете подумать об этом, когда вам все равно придется вносить значительные изменения в код. (Вы можете смоделировать некоторую его часть и попробовать, стоит ли это усилий, добавив индекс radcheck(attribute, username), это должно дать вам 30%-50% за ваш первый запрос - при условии username это уже не ваш первичный ключ)