Indizes auf primary und foreign keys

Ich habe eine Datenbank erstellt mit einem GUI-tool und ich habe bemerkt, was scheint, eine inkonsistente Verwendung von KEY (aka INDEX) Definitionen:

CREATE TABLE `foo_bar` (
  `foo_id` int(10) unsigned NOT NULL,
  `bar_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`foo_id`, `bar_id`),
  KEY `foo_bar_fk2` (`bar_id`), -- <== ???
  CONSTRAINT `foo_bar_fk1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`foo_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `foo_bar_fk2` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`bar_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci COMMENT='Links between Foo and Bar';

Habe ich die folgenden Fragen zu Indizes:

  1. Ist es notwendig, explizit zu definieren, die Indizes für primär-und Fremdschlüssel?
  2. Wenn es nicht, tun Sie tatsächlich bekommen zwei Indizes (und weniger Leistung)?
  3. Ist es anders in InnoDB-und MyISAM - (foreign keys abgesehen)?
InformationsquelleAutor | 2010-08-20
  1. 5

    Habe ich einige Experimente an, was Sie mir erzählt und ich dachte, dass ich teilen würde, es als Antwort.

    Ersten ich einige test-Tabellen:

    CREATE TABLE foo (
    foo_id int(10) unsigned NOT NULL,
    PRIMARY KEY (foo_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
CREATE TABLE bar (
    bar_id int(10) unsigned NOT NULL,
    PRIMARY KEY (bar_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;


CREATE TABLE foo_bar (
    foo_id int(10) unsigned NOT NULL,
    bar_id int(10) unsigned NOT NULL
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;

    Bisher keine Indizes vorhanden:

    mysql> SHOW INDEXES FROM foo_bar;
Empty set (0.00 sec)

    Hinzufügen Primärschlüssel erzeugt einen index:

    mysql> ALTER TABLE foo_bar
    -> ADD PRIMARY KEY (`foo_id`, `bar_id`);
Query OK, 0 rows affected (0.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEXES FROM foo_bar;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| foo_bar |          0 | PRIMARY  |            1 | foo_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| foo_bar |          0 | PRIMARY  |            2 | bar_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.02 sec)

    Wenn ich einen foreign key auf foo_id es nutzt die primary key-index seit dieser Spalte ist die erste in den index:

    mysql> ALTER TABLE foo_bar
    -> ADD CONSTRAINT `foo_bar_fk1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`foo_id`) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEXES FROM foo_bar;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| foo_bar |          0 | PRIMARY  |            1 | foo_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| foo_bar |          0 | PRIMARY  |            2 | bar_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

    Wenn ich einen foreign key auf bar_id es erstellt einen index, da keine vorhandene index wiederverwendet werden können:

    mysql> ALTER TABLE foo_bar
    -> ADD CONSTRAINT `foo_bar_fk2` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`bar_id`) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEXES FROM foo_bar;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| foo_bar |          0 | PRIMARY     |            1 | foo_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| foo_bar |          0 | PRIMARY     |            2 | bar_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| foo_bar |          1 | foo_bar_fk2 |            1 | bar_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.02 sec)

    Einer unserer ausländischen Tasten ist mit Hilfe der primary key-index. Das bedeutet, dass wir nicht zu entfernen, wie index!

    mysql> ALTER TABLE foo_bar
    -> DROP PRIMARY KEY;
ERROR 1025 (HY000): Error on rename of '.\test\#sql-568_c7d' to '.\test\foo_bar' (errno: 150)

    Es sei denn, wir erstellen einen index für den Fremdschlüssel oder ziehen wir die Schlüssel selbst:

    mysql> ALTER TABLE foo_bar
    -> DROP FOREIGN KEY `foo_bar_fk1`;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE foo_bar
    -> DROP PRIMARY KEY;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

    Die Schlussfolgerung ist, dass MySQL erstellt Indizes automatisch, wenn Sie verpflichtet sind, die für eine Funktionalität (aber nur, wenn Sie absolut notwendig sind).

    InformationsquelleAutor Álvaro González
  2. 2

    Den foreign key Indizes werden verwendet, um referenzielle Einschränkungen.

    Ermöglichen Sie Ihnen, zu definieren, die cascade-delete-Klauseln, so dass das löschen einer Zeile aus der übergeordneten Tabelle werden Zeilen löschen von child-Tabellen. Es kann auch verwendet werden, um sicherzustellen, dass wenn Sie versuchen, erstellen Sie eine Zeile in der untergeordneten Tabelle verknüpft werden können, um einem Elternteil (also der Childrow.ParentId gültig ist)

    Edit: Ah sorry, habe ich missverstanden. InnoDB erstellt automatisch Indizes für Fremdschlüssel. Sehen nicht-mysql-index-foreign-key-Spalten-automatisch

    • Sorry, wenn ich nicht erklären, richtig, meine Frage ist über die KEY foo_bar_fk2 (bar_id) Teil, d.h., der index-definition. Der Zweck der primär-und Fremdschlüssel ist klar.
    • Sorry für mein Missverständnis. Antwort bearbeitet
    InformationsquelleAutor PaulG
  3. 1

    1) Primärschlüssel werden automatisch indexiert, in mysql.
    2) Siehe oben, nicht notwendig, es zu tun
    3) MyISAM unterstützt keine Einschränkungen. (Ich glaube, Sie haben gesagt, dass implizit, so ist dies mehr für andere zum Lesen drüber). Einige grafische tools wie MySQL Workbench verursachen keine Warnungen, wenn Sie versuchen, verwenden Sie die Fremdschlüssel mit MyISAM, aber offensichtlich nicht erstellen.

    Btw: Welche GUI benutzt du (auf dem OS)?

    Gut zu Lesen: data-warehousing-whitepaper (enthält einen Vergleich der MyISAM-und InnoDB-Tabellen)

    • Hmmm... 1) Sind Fremdschlüssel automatisch indiziert, wie gut? 2) bekommen Sie doppelte Indizes wenn Sie es trotzdem tun? 3) ich Rede Indizes - im Moment bin ich mit einer Kombination von MySQL Workbench, HeidiSQL und einem text-editor.
    • 2) ich habe nie es, es einfach mal probieren 🙂 , aber Vorsicht, wahrscheinlich können Sie tun es einfach in deine GUI, aber es nie wirklich passiert. 3) Mir auch, in den MyISAM-es gibt keine foreign key (Einschränkungen) unterstützt. Ich war nur unter Angabe, es mehr explizit im anderen Fall gefragt.
    • Ich habe gerade editiert meinen post einen link hinzufügen, ich war auf der Suche nach all der Zeit, seit ich deine Frage Lesen. Es ist ein wenig übertrieben, aber Es ist gut zu Lesen und es ist ein Vergleich über InnoDB und MyISAM in eine tabellarische übersicht. laden Sie einfach die .pdf-whitepaper auf dieser Seite. HTH
    InformationsquelleAutor Matt Bannert
Schreibe einen Kommentar