Mtundu wa database ya MySQL / pangani / gwiritsani ntchito kugwiritsa ntchito mawu a ALTER mu MySQL

MySQLmtundu / pangani / gwiritsani ntchito combo kusinthaMySQLkugwiritsa ntchito mawu

MySQL indexes

Kukhazikitsidwa kwa ndondomeko ya MySQL n'kofunika kwambiri kuti MySQL igwire bwino ntchito, ndipo ndondomekoyi ikhoza kupititsa patsogolo liwiro la kubweza kwa MySQL.

Mwachitsanzo, ngati MySQL yokhala ndi kapangidwe koyenera komanso kugwiritsa ntchito ma index ndi Lamborghini, ndiye kuti MySQL yopanda ma index ndi ma index ndi njinga ya anthu atatu.

Mlozerawu umagawika m'gawo limodzi ndi index yophatikizika.Mlozera wagawo limodzi, ndiye kuti, index ili ndi gawo limodzi lokha, tebulo limatha kukhala ndi milozera yambiri yagawo limodzi, koma iyi si index yophatikizika.Mlozera wamagulu, ndiye kuti, index imodzi ili ndi magawo angapo.

Popanga ndondomeko, muyenera kuwonetsetsa kuti ndondomekoyi ndi chikhalidwe chomwe chimagwiritsidwa ntchito pafunso la SQL (nthawi zambiri ngati chikhalidwe cha WHERE clause).

M'malo mwake, cholozeracho ndi tebulo, tebulo limasunga makiyi oyambira ndi magawo a index, ndikulozera ku zolemba za gulu.

Zomwe zili pamwambazi ndizokhudza ubwino wogwiritsa ntchito ma index, koma kugwiritsa ntchito mopitirira muyeso kumayambitsa nkhanza.Chifukwa chake, indexyo idzakhalanso ndi zofooka zake: ngakhale indexyo imathandizira kwambiri liwiro lafunso, idzachepetsa liwiro la kukonzanso tebulo, monga INSERT, UPDATE ndi DELETE ya tebulo.Chifukwa pokonzanso tebulo, MySQL sikuti imangosunga deta, komanso imasunga fayilo ya index.

Kuwonetsa fayilo ya index yomwe imadya disk space.

normal index

kupanga index

Ichi ndiye index yofunikira kwambiri, ilibe zoletsa.Ikhoza kupangidwa m'njira zotsatirazi:

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

Pamitundu ya CHAR ndi VARCHAR, utali ukhoza kukhala wocheperako kutalika kwa gawolo; pamitundu ya BLOB ndi TEXT, utali uyenera kufotokozedwa.

Sinthani mawonekedwe a tebulo (onjezani index)

ALTER table tableName ADD INDEX indexName(columnName)

Nenani mwachindunji popanga tebulo

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

Syntax kuti muchepetse index

DROP INDEX [indexName] ON mytable; 

wapadera index

Ndizofanana ndi index wamba wamba, kusiyana kwake ndikuti: mtengo wa index uyenera kukhala wapadera, koma zopanda pake zimaloledwa.Pankhani ya index yophatikizika, kuphatikiza kwa mizere kuyenera kukhala kwapadera.Ikhoza kupangidwa m'njira zotsatirazi:

kupanga index

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

Sinthani kapangidwe katebulo

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

Nenani mwachindunji popanga tebulo

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

Onjezani ndikuchotsa ma index pogwiritsa ntchito lamulo la ALTER

Pali njira zinayi zowonjezerera index ku tebulo la data:

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): Mawu awa akuwonjezera kiyi yoyamba, zomwe zikutanthauza kuti ma index akuyenera kukhala apadera ndipo sangakhale NULL.
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): Mtengo wa ndondomeko yopangidwa ndi mawuwa uyenera kukhala wapadera (kupatula NULL, NULL ikhoza kuwoneka kangapo).
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): Onjezani index yodziwika bwino, mtengo wa index ukhoza kuwoneka kangapo.
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):Mawuwa amafotokoza mlozera ngati FULLTEXT polozera mawu onse.

Chitsanzo chotsatira ndichowonjezera cholozera patebulo.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

Mutha kugwiritsanso ntchito ndime ya DROP pa lamulo la ALTER kuti mugwetse ma index.Yesani zotsatirazi kuti mutsitse index:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

Onjezani ndi kuchotsa makiyi oyambira pogwiritsa ntchito lamulo la ALTER

Kiyi yoyambira imatha kuchitapo kanthu pamndandanda umodzi wokha. Mukayika mlozera wa makiyi oyambira, muyenera kuwonetsetsa kuti kiyi yoyamba si NULL mwachisawawa (OSATI NULL).Zitsanzo ndi izi:

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

Mutha kuchotsanso kiyi yoyamba ndi lamulo la ALTER:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

Muyenera kungotchula PRIMARY KEY pogwetsa kiyi yoyamba, koma pogwetsa index, muyenera kudziwa dzina la index.

onetsani zambiri za index

Mutha kugwiritsa ntchito lamulo la SHOW INDEX kuti mulembe zomwe zili patebulopo.Zomwe zimatuluka zitha kusinthidwa powonjezera \G.

Yesani zitsanzo zotsatirazi:

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