SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 25.01.2017 15:26:43

klow
Активист
Зарегистрирован: 06.12.2014
Сообщений: 260

Index AND Function

Прошу помощи. Запутался в двух соснах.
Есть таблица, для упрощения с одним полем ID

CREATE TABLE _test (
  ID int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (ID)
)
ENGINE = INNODB
AUTO_INCREMENT = 359779
AVG_ROW_LENGTH = 136
CHARACTER SET utf8
COLLATE utf8_unicode_ci;
Таблица имеет около 45 тыс. записей

Есть функция, для упрощения возвращает веденный параметр
CREATE FUNCTION GetId(Id INT)
  RETURNS int(11)
  SQL SECURITY INVOKER
BEGIN
RETURN id;
END

Пытаюсь выполнить скрипт
EXPLAIN
SELECT t.* FROM _test t WHERE t.ID = GetId(:uID);

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    t    index    null    PRIMARY    4    null    44617    Using where; Using index

ПОЧЕМУ не используется индекс?

В таком вариате все нормально
EXPLAIN
SELECT t.* FROM _test t WHERE t.ID = :uID;

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    t    const    PRIMARY    PRIMARY    4    const    1    Using index

Отредактированно klow (25.01.2017 15:27:48)

Неактивен

 

#2 25.01.2017 15:55:44

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5291

Re: Index AND Function

оптимизатор не учитывает функции
не умеет - такое ограничение

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

Неактивен

 

#3 25.01.2017 16:01:53

klow
Активист
Зарегистрирован: 06.12.2014
Сообщений: 260

Re: Index AND Function

vasya написал:

можно говорить о баге

Плохо, я надеялся, что сам что-то нахомутал.

У меня MySql 5.5.54. Интересно на других версиях что?

Неактивен

 

#4 25.01.2017 16:04:01

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5291

Re: Index AND Function

проверил в 5.7.9, то же самое

Неактивен

 

#5 25.01.2017 16:05:31

klow
Активист
Зарегистрирован: 06.12.2014
Сообщений: 260

Re: Index AND Function

Неужели раньше никто не наступал на эти грабли?

Отредактированно klow (25.01.2017 16:05:46)

Неактивен

 

#6 25.01.2017 16:19:50

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5291

Re: Index AND Function

наверное, плакали, кололись, но продолжали жевать кактус

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

Неактивен

 

#7 25.01.2017 16:25:04

klow
Активист
Зарегистрирован: 06.12.2014
Сообщений: 260

Re: Index AND Function

1. Не могу использовать символ @. В клиенте MySql для Net он используется для переменных.
2. В одном запросе может быть много обращений к этой функции.

SELECT
...
FROM table1 t1 join table2 t2 on t2.id=GetId(t1.id);

Отредактированно klow (25.01.2017 16:26:51)

Неактивен

 

#8 25.01.2017 23:04:52

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3776

Re: Index AND Function

Попробуйте добавить DETERMINISTIC к CREATE FUNCTION. Если функция недетерминистичная, MySQL может думать, что она меняется и вычислять ее для каждой строки.
Если не поможет, то нужно постить багу.

Неактивен

 

#9 26.01.2017 00:16:16

klow
Активист
Зарегистрирован: 06.12.2014
Сообщений: 260

Re: Index AND Function

Спасибо! При DETERMINISTIC использует индекс. Но меня это не спасает. У меня реальная функция NOT DETERMINISTIC.

rgbeast написал:

Если функция недетерминистичная, MySQL может думать, что она меняется и вычислять ее для каждой строки.

Похоже, что именно так.

Отредактированно klow (26.01.2017 00:18:19)

Неактивен

 

#10 26.01.2017 00:33:49

klow
Активист
Зарегистрирован: 06.12.2014
Сообщений: 260

Re: Index AND Function

На что влияет "READS SQL DATA"? Какой в нем смысл?

Отредактированно klow (26.01.2017 00:35:32)

Неактивен

 

#11 26.01.2017 01:33:02

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3776

Re: Index AND Function

"READS SQL DATA" означает, что функция содержит SELECT, но не UPDATE и DELETE. Эта характеристика может использоваться оптимизатором, но с примерами ее работы не сталкивался.

Про пользовательские переменные в .net попробуйте как здесь рекомендуют:
http://blog.tjitjing.com/index.php/2009 … fined.html

Неактивен

 

#12 26.01.2017 01:46:46

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3776

Re: Index AND Function

Еще как вариант попробуйте заменить JOIN на JOIN t1 с результатом подзапроса из t2. Индексы на t1 будут использованы, если подзапрос будет считаться независимым.

Неактивен

 

#13 26.01.2017 10:02:22

klow
Активист
Зарегистрирован: 06.12.2014
Сообщений: 260

Re: Index AND Function

rgbeast написал:

Про пользовательские переменные в .net попробуйте как здесь рекомендуют:
http://blog.tjitjing.com/index.php/2009 … fined.html

С эти знаком. Но у меня очень много запросов, переписывать их всех еще тот геморой. Заводить отдельное соединение для данного запроса тоже не очень хороший вариант.

Неактивен

 

#14 26.01.2017 10:06:44

klow
Активист
Зарегистрирован: 06.12.2014
Сообщений: 260

Re: Index AND Function

rgbeast написал:

Еще как вариант попробуйте заменить JOIN на JOIN t1 с результатом подзапроса из t2. Индексы на t1 будут использованы, если подзапрос будет считаться независимым.

не уловил смыл. Большой разницы, думаю, не будет так ка для t2.id=GetId()  будет перебор всех значений из t2.
У меня есть другая идея, проверю - по результатам отпишусь.

Отредактированно klow (26.01.2017 10:52:13)

Неактивен

 

#15 26.01.2017 11:26:14

klow
Активист
Зарегистрирован: 06.12.2014
Сообщений: 260

Re: Index AND Function

Результаты моего эксперимента.
Изменил функцию

CREATE DEFINER = 'root'@'%'
FUNCTION GetId(num INT)
  RETURNS int(11)
  DETERMINISTIC
  READS SQL DATA
BEGIN
RETURN (SELECT MAX(id) FROM _test);
END

Как видно функция DETERMINISTIC и, естественно, использует индексы.
Запустил скрипт
SELECT * FROM _test t WHERE t.ID = GetId(1);

она возвратила последнее значение из таблицы _test с использованием индекса. Все как и ожидалось.

Добавляем одну (несколько) записей в таблицу.
И снова запускаем скрипт. Так как функция GetId DETERMINISTIC ожидается что скрипт возвратит строку, что и в первый раз.
Нет, он возвращает последнюю добавленную строку.
Меня такое поведение очень даже устраивает, но как тогда быть с утверждением "Это характеристика, показывающая всегда ли является результат работы
функции следствием ее аргументов."?

Отредактированно klow (26.01.2017 11:45:56)

Неактивен

 

#16 04.02.2017 23:34:27

klow
Активист
Зарегистрирован: 06.12.2014
Сообщений: 260

Re: Index AND Function

неужели ни кто не прокомментирует мои испытания, которые, вроде как, противоречат документации MySql. Что я не так сделал?

Неактивен

 

#17 05.02.2017 00:01:22

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5291

Re: Index AND Function

klow написал:

Добавляем одну (несколько) записей в таблицу.
И снова запускаем скрипт. Так как функция GetId DETERMINISTIC ожидается что скрипт возвратит строку, что и в первый раз.

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

P.S. Кроме того, в доке сказано, что можно указывать DETERMINISTIC для не детерминированных ф-ий и наоборот, но в этом случае будете сам себе злобный буратина.

MySQL does not check that a routine declared DETERMINISTIC is free of statements that produce nondeterministic results. However, misdeclaring a routine might affect results or affect performance. Declaring a nondeterministic routine as DETERMINISTIC might lead to unexpected results by causing the optimizer to make incorrect execution plan choices. Declaring a deterministic routine as NONDETERMINISTIC might diminish performance by causing available optimizations not to be used.

Неактивен

 

#18 05.02.2017 00:38:57

klow
Активист
Зарегистрирован: 06.12.2014
Сообщений: 260

Re: Index AND Function

Спасибо!

vasya написал:

Это неверное суждение.

Это неверное суждение было основано на

значит оно тоже является неверным?!

Неактивен

 

#19 05.02.2017 00:54:03

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5291

Re: Index AND Function

Надпись на двери: Заседание клуба педантов пройдет здесь.
Она верная или нет?
Ведь, на самом деле - не здесь, а в помещении за этой дверью (но обычно так никто не пишет).
Так же и с процитированным вами утверждением. Оно верное, но на заседании клуба педантов его раскритикуют за неполноту.

Неактивен

 

#20 05.02.2017 09:17:15

klow
Активист
Зарегистрирован: 06.12.2014
Сообщений: 260

Re: Index AND Function

Я Вас понял, но, думаю, в данном случае, именно это уточнение, которое Вы ранее привели, было бы очень важно для этого утверждения. Ведь существенно меняться смысл.
Но не суть, я понял, еще раз спасибо!
Правда, жаль других пользователей, которые могут его воспринять буквально и наступят на те-же грабли. sad

Отредактированно klow (05.02.2017 09:28:31)

Неактивен

 

#21 05.02.2017 19:18:33

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3776

Re: Index AND Function

klow, мне кажется, что наиболее важно то, что слово в описании DETERMINISTIC не влияет на поведение функции, а влияет лишь на оптимизатор. Ошибки на совести автора функции.

Что касается определения, то в документации написано, что зависит только от аргументов. Однако обычно это свойство используется при репликации. В последнем случае данные в таблицах тоже идентичны, поэтому широкая трактовка (как предлагает vasya) вполне допустима. Думаю, что это бага документации.

См. также обсуждение на Stack Overflow:
http://stackoverflow.com/questions/7946 … n-in-mysql

Неактивен

 

#22 05.02.2017 19:29:57

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3776

Re: Index AND Function

Запостил багу документации:
https://bugs.mysql.com/bug.php?id=84822

Нажмите "Affects me" в багтрекере

Неактивен

 

#23 06.02.2017 08:28:31

klow
Активист
Зарегистрирован: 06.12.2014
Сообщений: 260

Re: Index AND Function

Спасибо!

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson