SQLinfo.ru - Все о MySQL Фестиваль «Российские интернет-технологии» 2017

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

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

Вы не зашли.

#1 26.09.2016 11:05:57

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2027

Увеличивается ли размер на hdd от добавления в таблицу foreign key

Доброго дня, Коллеги!

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

Environment: Mysql 5.6, Debian.

Суть: Есть таблица t с много-много записей. Тип InnoDB. Одно из полей f - ссылка на поле f1 другой таблицы t1.

Суть вопроса - если добавить foreign key на t.f references t1.f1 (не создавай ключика на поле t.f) - это же не увеличит
размер таблицы (с точки зрения занимаемого таблицей дискогого пространства)? Эксперимент показал, что нет - но вдруг я не учел чего.

И еще - сам механизм проверки foreign key - это просто невидимый для нас триггер или что-то более глубокое?


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#2 26.09.2016 13:02:25

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

Re: Увеличивается ли размер на hdd от добавления в таблицу foreign key

на t.f должен быть ключ. В старых версиях его нужно было создавать явно, иначе ошибка. В новых он создается автоматически.

The columns in the child table must be an index, or the leftmost part of an index. Index prefixes are not supported (thus, TEXT and BLOB columns cannot be used as foreign keys). If MariaDB automatically creates an index for the foreign key (because it does not exist and is not explicitly created), its name will be index_name.


https://mariadb.com/kb/en/mariadb/foreign-keys/

Неактивен

 

#3 27.09.2016 02:47:41

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2027

Re: Увеличивается ли размер на hdd от добавления в таблицу foreign key

Вася, спасибо!
Ты говоришь про MariaDB, в MySQL точно также? Какие версии, к слову, ты подразумеваешь под новыми и старыми?
Впрочем, проведу эксперимент, отпишусь.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#4 27.09.2016 03:32:35

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

Re: Увеличивается ли размер на hdd от добавления в таблицу foreign key

В MySQL также.
К слову в доке 5.0 ( http://dev.mysql.com/doc/refman/5.0/en/ … aints.html ) было сказано:

Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.)

Но теперь минимальная дока - 5.5, а там этого нет. Приходится полагаться на память smile
(хотя нет, можно скачать в архиве старую доку http://dev.mysql.com/doc/index-archive.html )

Соответственно, errno 150 возникало для четвертой версии.

Неактивен

 

#5 27.09.2016 11:52:01

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2027

Re: Увеличивается ли размер на hdd от добавления в таблицу foreign key

В оперативную память своей головы diffы между документациями версий загружает только верховный шаман sqlinfo smile.
Понял, буду пробовать. А то табличка на диске весит чуть не 100 гб, "лишний" FK, а соответственно индекс может хорошо скушать место.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#6 15.11.2016 20:07:05

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

Re: Увеличивается ли размер на hdd от добавления в таблицу foreign key

deadka написал:

И еще - сам механизм проверки foreign key - это просто невидимый для нас триггер или что-то более глубокое?

вот тут paulus говорил, что fk это третья табличка связей, которую видит только innodb.

Неактивен

 

#7 15.11.2016 20:47:14

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2027

Re: Увеличивается ли размер на hdd от добавления в таблицу foreign key

Ага, спасибо. По существу вопроса я проверил на версии 5.6 - foreign key начинает содержать в себе одноименный индекс, который потом используется в запросах, судя по explain.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#8 15.11.2016 20:59:16

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

Re: Увеличивается ли размер на hdd от добавления в таблицу foreign key

Ага, это то, о чем я говорил.
А из сказанного paulus-ом следует, что fk будет занимать больше места, чем обычный индекс, так как fk= index на дочерней таблице (который показывает explain) + доп таблица, которая вероятно тоже проиндексирована.

Неактивен

 

#9 15.11.2016 21:07:43

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

Re: Увеличивается ли размер на hdd от добавления в таблицу foreign key

Кстати, всё есть на sqlinfo smile
http://sqlinfo.ru/forum/viewtopic.php?pid=19115#p19115

paulus написал:

Внешний ключ — это отдельная табличка many-to-many в пространстве
данных InnoDB, которая не видна из MySQL.

to paulus, а почему many-to-many? Ведь значения родительского ключа должны быть уникальны.

Неактивен

 

#10 15.11.2016 21:11:35

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2027

Re: Увеличивается ли размер на hdd от добавления в таблицу foreign key

fk - да, вопрос насколько больше места будет занимать )).
paulus, присоединяюсь к вопрос про many to many.
Ну и вообще, будем рады любым каментам по сабжу.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#11 16.11.2016 00:10:24

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6599

Re: Увеличивается ли размер на hdd от добавления в таблицу foreign key

Ох, ребята, вы хотите, чтобы я думал  smile

Многие ко многим — потому что вот так:

[blendie] root test > CREATE TABLE a (a INT, INDEX(a)) ENGINE=InnoDB;
Query OK, 0 rows affected (0,02 sec)

[blendie] root test > INSERT INTO a VALUES (1), (1), (2);
Query OK, 3 rows affected (0,01 sec)
Records: 3  Duplicates: 0  Warnings: 0

[blendie] root test > CREATE TABLE b (b INT, INDEX(b), FOREIGN KEY(b) REFERENCES a(a)) ENGINE=InnoDB;
Query OK, 0 rows affected (0,02 sec)

[blendie] root test > INSERT INTO b VALUES (1), (1), (2);
Query OK, 3 rows affected (0,00 sec)
Records: 3  Duplicates: 0  Warnings: 0

[blendie] root test > INSERT INTO b VALUES (3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `b_ibfk_1` FOREIGN KEY (`b`) REFERENCES `a` (`a`))



Мне казалось, что в 5.0 это была отдельная структура, которая хранила данные. Но сейчас кажется, что достаточно просто индексов и знания, что в этом месте нужно проверять связность. Т.е. удалить даже одну строку из первой таблички оно уже не дает, видимо, я был неправ:

[blendie] root test > DELETE FROM a WHERE a = 1 LIMIT 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `b_ibfk_1` FOREIGN KEY (`b`) REFERENCES `a` (`a`))

Неактивен

 

#12 16.11.2016 00:23:02

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2027

Re: Увеличивается ли размер на hdd от добавления в таблицу foreign key

paulus вышел из темноты, уря!

В контексте вопроса от vasya
( Ведь значения родительского ключа должны быть уникальны ) - мне тоже раньше так казалось, вроде даже в документации видел, что поле в "папе", куда ссылается другое поле в "дочке"-таблице - оно должно быть либо PK (который по определению NOT NULL UNIQUE) либо просто UNIQUE, твой же пример свидетельствует о том, что можно ссылаться просто на индексированное поле. А ведь так даже непонятно, на какую именно запись в a ссылается запись из b (а вот если бы поле a было бы уникально, то такого бы не было!).


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#13 16.11.2016 00:23:27

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

Re: Увеличивается ли размер на hdd от добавления в таблицу foreign key

О-о-о..
А это не баг? В доке у MariaDB явно сказано:

The referenced columns must be a PRIMARY KEY or a UNIQUE index.

Какая версия у тебя?

Неактивен

 

#14 16.11.2016 00:27:33

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2027

Re: Увеличивается ли размер на hdd от добавления в таблицу foreign key

ИМХО баг, потому что как бе противоречит логике.

Код:

mysql> CREATE TABLE a (a INT, INDEX(a)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO a VALUES (1), (1), (2);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE b (b INT, INDEX(b), FOREIGN KEY(b) REFERENCES a(a)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO b VALUES (1), (1), (2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.6.31-0ubuntu0.15.10.1 |
+-------------------------+
1 row in set (0.00 sec)

Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#15 16.11.2016 00:29:24

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2027

Re: Увеличивается ли размер на hdd от добавления в таблицу foreign key

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

Код:

mysql> CREATE TABLE a (a INT) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE b (b INT, INDEX(b), FOREIGN KEY(b) REFERENCES a(a)) ENGINE=InnoDB;
ERROR 1215 (HY000): Cannot add foreign key constraint

Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#16 16.11.2016 00:29:42

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

Re: Увеличивается ли размер на hdd от добавления в таблицу foreign key

Не баг, однако. В доке у MySQL:

InnoDB  allows a foreign key constraint to reference a non-unique key. This is an InnoDB extension to standard SQL.

Неактивен

 

#17 16.11.2016 00:35:10

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2027

Re: Увеличивается ли размер на hdd от добавления в таблицу foreign key

Вот как. Специальное расширение, которое отходит от ANSI-стандарта. Остается вопрос - зачем..
vasya, а у тебя на тесте от paulus в MariaDB валится тест? Как догадываюсь, таблица b не должна создасться. Покажешь лог (и версию mariadb)?


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#18 16.11.2016 00:47:49

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

Re: Увеличивается ли размер на hdd от добавления в таблицу foreign key

5.5.23-MariaDB-log
удивительно, но создается

Неактивен

 

#19 16.11.2016 00:50:04

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2027

Re: Увеличивается ли размер на hdd от добавления в таблицу foreign key

Ну тогда это либо бага в документации MariaDB либо бага в реализации. sad


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#20 16.11.2016 00:58:22

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

Re: Увеличивается ли размер на hdd от добавления в таблицу foreign key

Зато мы можем ещё добавить 1.
Всех пускаем, никого не выпускаем smile

Неактивен

 

Board footer

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