MySQL dhatabhesi indekisi mhando/gadzira/shandisa musanganiswa ALTER chirevo chekushandiswa muMySQL

MySQLindex type/gadzira/shandisa combo alter inMySQLkuraira chirevo kushandiswa

MySQL indexes

Kugadzwa kweMySQL index kwakakosha zvakanyanya pakushanda kwakanaka kweMySQL, uye iyo index inogona kuvandudza zvakanyanya kukurumidza kudzoreredza kweMySQL.

Semuyenzaniso, kana MySQL ine dhizaini inonzwisisika uye kushandiswa kwemaindekisi iri Lamborghini, ipapo MySQL isina indexes uye indexes munhu matatu matatu.

Indekisi yakakamurwa kuita single-column index uye composite index.Indekisi yekoramu imwe chete, kureva kuti, indekisi ine koramu imwe chete, tafura inogona kuva nema indexes ecolumn akawanda, asi iyi haisi indekisi yemubatanidzwa.Composite index, kureva, index ine makoramu akawanda.

Paunenge uchigadzira index, unofanirwa kuve nechokwadi chekuti index imamiriro ekushandiswa kune SQL query (kazhinji sechimiro cheWHERE clause).

Muchokwadi, iyo index zvakare tafura, tafura inochengetedza kiyi yekutanga uye index minda, uye inonongedza kune zvinyorwa zveiyo tafura yesangano.

Zviri pamusoro apa zviri kutaura pamusoro pezvakanakira kushandisa indekisi, asi kunyanya kushandiswa kwemaindekisi kuchatungamirira mukushungurudzwa.Naizvozvo, iyo index ichavawo nekukanganisa kwayo: kunyangwe iyo index inovandudza zvakanyanya kumhanya kwemubvunzo, inoderedza kukurumidza kwekuvandudza tafura, senge INSERT, UPDATE uye DELETE patafura.Nekuti kana uchivandudza tafura, MySQL haingochengetedzi data chete, asiwo inochengetedza index index.

Kuisa indexing faira reindex rinodya dhisiki nzvimbo.

normal index

kugadzira index

Iyi ndiyo inonyanya kukosha index, haina zvirambidzo.Inogona kugadzirwa nenzira dzinotevera:

CREATE INDEX indexName ON mytable(username(length)); 

Kune mhando dzeCHAR neVARCHAR, kureba kunogona kuve kushoma pane hurefu chaihwo hwemunda; pamhando dzeBLOB neTEKSTO, hurefu hunofanirwa kutaurwa.

Shandura chimiro chetafura (wedzera index)

ALTER table tableName ADD INDEX indexName(columnName)

Taura zvakananga paunenge uchigadzira tafura

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);  

Syntax yekudonhedza index

DROP INDEX [indexName] ON mytable; 

unique index

Izvo zvakafanana neyakare yakajairwa index, mutsauko ndewekuti: kukosha kweiyo index column kunofanirwa kuve kwakasiyana, asi null values ​​inobvumidzwa.Panyaya yeiyo composite index, musanganiswa wemakoramu akoshi inofanirwa kuve yakasarudzika.Inogona kugadzirwa nenzira dzinotevera:

kugadzira index

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

Shandura chimiro chetafura

ALTER table mytable ADD UNIQUE [indexName] (username(length))

Taura zvakananga paunenge uchigadzira tafura

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  
 
);  

Wedzera uye bvisa indexes uchishandisa iyo ALTER command

Pane nzira ina dzekuwedzera index patafura yedata:

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): Ichi chirevo chinowedzera kiyi yekutanga, zvinoreva kuti index index inofanirwa kuve yakasarudzika uye haigone kuve NULL.
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): Kukosha kweiyo index yakagadzirwa neshoko iri kunofanirwa kuve kwakasiyana (kunze kweNULL, NULL inogona kuoneka kakawanda).
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): Wedzera yakajairika index, iyo index kukosha inogona kuoneka kakawanda.
• ALTER TABLE tbl_name wedzera FULLTEXT index_name (column_list):Chirevo chinotsanangura indekisi seFULLTEXT yekunyora-zvinyorwa zvizere.

Muenzaniso unotevera ndewekuwedzera index patafura.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

Iwe unogona zvakare kushandisa iyo DROP clause pane ALTER command kudonhedza indexes.Edza muenzaniso unotevera kudonhedza index:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

Wedzera uye bvisa makiyi ekutanga uchishandisa ALTER command

Kiyi yekutanga inongokwanisa kuita pachikamu chimwe chete. Paunenge uchiwedzera indekisi yekiyi yekutanga, unofanirwa kuona kuti kiyi yekutanga haisi NULL nekukasira (NOT NULL).Mienzaniso ndeiyi inotevera:

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

Iwe unogona zvakare kudzima kiyi yekutanga neiyo ALTER command:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

Iwe unongoda kutsanangura PRIMARY KEY paunodonhedza kiyi yekutanga, asi kana uchidonhedza index, unofanirwa kuziva zita reindex.

ratidza ruzivo rwe index

Unogona kushandisa murairo we SHOW INDEX kunyora ruzivo rwakakosha patafura.Ruzivo rwekubuda runogona kuumbwa nekuwedzera \G.

Edza mienzaniso inotevera:

mysql> SHOW INDEX FROM table_name; \G
........