Hoe voeg ik indexen toe aan MySQL-tabellen?

Ik heb een zeer grote MySQL-tabel met ongeveer 150.000 gegevensrijen. Momenteel, wanneer ik probeer te rennen

SELECT * FROM table WHERE id = '1';

de code werkt prima omdat het ID-veld de primaire index is.
Voor een recente ontwikkeling in het project moet ik de database echter op een ander veld doorzoeken. Bijvoorbeeld:

SELECT * FROM table WHERE product_id = '1';

Dit veld was niet eerder geïndexeerd; ik heb er echter een toegevoegd, dus mysql indexeert nu het veld, maar wanneer ik de bovenstaande query probeer uit te voeren, werkt deze erg langzaam. Een EXPLAIN-query laat zien dat er geen index is voor het product_id-veld wanneer ik er al een heb toegevoegd, en als gevolg daarvan duurt de query 20 minuten tot 30 minuten om een ​​enkele rij te retourneren.

Mijn volledige EXPLAIN-resultaten zijn:

| id | select_type | table | type | possible_keys| key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+--------------+------+---------+------+-------+------------------+
|  1 | SIMPLE      | table | ALL  | NULL         | NULL | NULL    | NULL |157211 | Using where |
+----+-------------+-------+------+--------------+------+---------+------+-------+------------------+

Het is misschien handig om te weten dat ik zojuist een kijkje heb genomen en dat het veld ID is opgeslagen als INT, terwijl het veld PRODUCT_ID is opgeslagen als VARCHAR. Zou dit de oorzaak van het probleem kunnen zijn?

Antwoord 1, autoriteit 100%

ALTER TABLE `table` ADD INDEX `product_id_index` (`product_id`)

Vergelijk integernooit met stringsin MySQL. Als idintis, verwijder dan de aanhalingstekens.

Antwoord 2, autoriteit 24%

ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);

Antwoord 3, autoriteit 13%

Je kunt deze syntaxis gebruiken om een ​​index toe te voegen en het soort index (HASH of BTREE) te bepalen.

create index your_index_name on your_table_name(your_column_name) using HASH;

of

create index your_index_name on your_table_name(your_column_name) using BTREE;

Je kunt hier meer te weten komen over verschillen tussen BTREE- en HASH-indexen:
http://dev.mysql.com/doc/refman /5.5/nl/index-btree-hash.html

Antwoord 4, autoriteit 10%

Indexen van twee typen kunnen worden toegevoegd: wanneer u een primaire sleutel definieert, zal MySQL deze standaard als index gebruiken.

Uitleg

Primaire sleutel als index

Bedenk dat je een tbl_studenttabel hebt en dat je student_idals primaire sleutel wilt:

ALTER TABLE `tbl_student` ADD PRIMARY KEY (`student_id`)

Bovenstaande instructie voegt een primaire sleutel toe, wat betekent dat geïndexeerde waarden uniek moeten zijn en niet NULL kunnen zijn.

Specificeer indexnaam

ALTER TABLE `tbl_student` ADD INDEX student_index (`student_id`)

Bovenstaande instructie maakt een gewone index aan met de naam student_index.

Creëer een unieke index

ALTER TABLE `tbl_student` ADD UNIQUE student_unique_index (`student_id`)

Hier is student_unique_indexde indexnaam die is toegewezen aan student_id en creëert een index waarvoor waarden uniek moeten zijn (hier kan null worden geaccepteerd).

Fulltext-optie

ALTER TABLE `tbl_student` ADD FULLTEXT student_fulltext_index (`student_id`)

Bovenstaande instructie maakt de naam van de Fulltext-index aan met student_fulltext_index, waarvoor u MyISAM Mysql Engine nodig heeft.

Hoe indexen te verwijderen ?

DROP INDEX `student_index` ON `tbl_student`

Hoe beschikbare indexen controleren?

SHOW INDEX FROM `tbl_student`

Antwoord 5, autoriteit 9%

Het is vermeldenswaard dat meerdere veldindexen uw queryprestaties drastisch kunnen verbeteren. Dus in het bovenstaande voorbeeld nemen we aan dat ProductID het enige veld is om op te zoeken, maar als de query ProductID = 1 AND Category = 7 zou zijn, dan helpt een index met meerdere kolommen. Dit wordt bereikt met het volgende:

ALTER TABLE `table` ADD INDEX `index_name` (`col1`,`col2`)

Bovendien moet de index overeenkomen met de volgorde van de zoekvelden. In mijn uitgebreide voorbeeld zou de index (ProductID,Category) moeten zijn en niet andersom.

Antwoord 6, autoriteit 2%

U zegt dat u een index heeft, de uitleg zegt iets anders. Als u het echter echt doet, gaat u als volgt verder:

Als u een index op de kolom heeft en MySQL besluit deze niet te gebruiken, kan dit omdat:

1. Er is een andere index in de query die MySQL geschikter acht om te gebruiken, en deze kan er maar één gebruiken. De oplossing is meestal een index die meerdere kolommen omspant als hun normale manier van ophalen een waarde van meer dan één kolom is.
2. MySQL besluit dat er te veel overeenkomende rijen zijn en denkt dat een tablescan waarschijnlijk sneller is. Als dat niet het geval is, helpt een ANALYZE TABLEsoms.
3. Bij complexere zoekopdrachten besluit het deze niet te gebruiken op basis van extreem intelligent doordacht voodoo in het zoekplan dat om de een of andere reden gewoon niet past bij uw huidige vereisten.

In het geval van (2) of (3) kunt u MySQL overhalen om de index te gebruiken door index hint sytax, maar als je dat doet, voer dan zeker een aantal tests uit om te bepalen of het daadwerkelijk de prestaties verbetert om de index te gebruiken terwijl je hem hint.

Antwoord 7

Een betere optie is om de beperkingen direct toe te voegen tijdens de CREATE TABLE-query (ervan uitgaande dat u over de informatie over de tabellen beschikt)

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT NOT NULL,
    CONSTRAINT fk_category
    FOREIGN KEY (categoryId) 
    REFERENCES categories(categoryId)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=INNODB;

Antwoord 8

gebruik phpmyadmin, een geweldige tool voor MySQL-beheer, inclusief indexering