Uhlobo lwesalathiso sesiseko sedatha ye-MySQL / yenza/sebenzisa indibaniselwano ye-ALTER yokusetyenziswa kwengxelo kwi-MySQL

MySQLuhlobo lwesalathiso/yenza/sebenzisa i-combo guqulela kuyoMySQLukusetyenziswa kwengxelo yomyalelo

MySQL indexes

Ukusekwa kwesalathisi se-MySQL kubaluleke kakhulu ekusebenzeni ngokufanelekileyo kwe-MySQL, kwaye isalathisi sinokuphucula kakhulu isantya sokubuyiswa kwe-MySQL.

Umzekelo, ukuba i-MySQL ngoyilo olufanelekileyo kunye nokusetyenziswa kwezalathisi yiLamborghini, emva koko i-MySQL ngaphandle kwezalathisi kunye nezalathisi yitricycle yomntu.

Isalathiso sahlulwe sibe yisalathisi somhlathi omnye kunye nesalathiso esihlanganisiweyo.Isalathiso sekholamu enye, oko kukuthi, isalathisi siqulathe umhlathi omnye kuphela, itheyibhile inokuba nezalathisi ezininzi zekholamu enye, kodwa esi ayisosalathiso esidityanisiweyo.Isalathiso esidityanisiweyo, oko kukuthi, isalathisi sinemiqolo emininzi.

Xa udala isalathiso, kufuneka uqinisekise ukuba isalathisi yimeko esetyenziswe kumbuzo we-SQL (ngokubanzi njengemeko yegatya elithi WHERE).

Enyanisweni, isalathisi sikwayitheyibhile, itheyibhile igcina isitshixo sokuqala kunye nemimandla yesalathisi, kwaye ikhomba kwiirekhodi zetheyibhile yequmrhu.

Ezi zingasentla zithetha ngeengenelo zokusebenzisa izalathisi, kodwa ukusetyenziswa ngokugqithiseleyo kwezalathisi kuya kukhokelela ekusetyenzisweni kakubi.Ngoko ke, isalathisi siya kuba neentsilelo: nangona isalathisi siphucula kakhulu isantya sombuzo, siya kunciphisa isantya sokuhlaziya itafile, njenge-INSERT, UPDATE kunye ne-DELETE yetafile.Ngenxa yokuba xa uhlaziya itafile, i-MySQL ayigcini nje kuphela idatha, kodwa igcina ifayile yesalathisi.

Isalathiso kwifayile yesalathisi edla isithuba sedisk.

isalathisi esiqhelekileyo

yenza isalathisi

Esi sesona salathisi sisisiseko, akukho zithintelo.Inokwenziwa ngolu hlobo lulandelayo:

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

Kwiintlobo ze-CHAR kunye ne-VARCHAR, ubude bunokuba ngaphantsi kobude bokwenyani bomhlaba, kwi-BLOB ne- TEXT iintlobo, ubude kufuneka buxelwe.

Lungisa isakhiwo setafile (yongeza isalathisi)

ALTER table tableName ADD INDEX indexName(columnName)

Cacisa ngokuthe ngqo xa udala itafile

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

Isintaksi ukulahla isalathiso

DROP INDEX [indexName] ON mytable; 

isalathisi esisodwa

Iyafana nesalathiso esiqhelekileyo sangaphambili, umahluko kukuba: ixabiso lekholamu yesalathiso kufuneka ibe yodwa, kodwa amaxabiso angasebenziyo avunyelwe.Kwimeko yesalathiso esidibeneyo, indibaniselwano yamaxabiso ekholamu kufuneka ibe yodwa.Inokwenziwa ngolu hlobo lulandelayo:

yenza isalathisi

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

Lungisa isakhiwo setafile

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

Cacisa ngokuthe ngqo xa udala itafile

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

Yongeza kwaye ususe izalathisi usebenzisa i ALTER umyalelo

Kukho iindlela ezine zokongeza isalathisi kwitafile yedatha:

• ALTER TABLE tbl_name YONZA ISIQINISO ESISISEKO (uluhlu_lwekholamu): Le ngxelo yongeza iqhosha eliphambili, elithetha ukuba ixabiso lesalathisi kufuneka libe lodwa kwaye alikwazi ukuba NULL.
• ALTER TABLE tbl_name YONGEZA UNIQUE index_name (uluhlu_loluhlu): Ixabiso lesalathiso esidalwe yile nkcazo kufuneka ibe yodwa (ngaphandle kwe-NULL, NULL inokuvela amaxesha amaninzi).
• ALTER TABLE tbl_name YONGEZA INDEX index_name (uluhlu_loluhlu): Yongeza isalathisi esiqhelekileyo, ixabiso lesalathisi linokuvela ngamaxesha amaninzi.
• ALTER TABLE tbl_name YONZA FULLTEXT index_name (uluhlu_luluhlu):Ingxelo ikhankanya isalathisi njenge FULLTEXT yesalathiso sokubhaliweyo okugcweleyo.

Lo mzekelo ulandelayo kukongeza isalathisi kwitafile.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

Ungasebenzisa kwakhona igatya elithi DROP kumyalelo we ALTER ukulahla izalathisi.Zama lo mzekelo ulandelayo ukulahla isalathiso:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

Yongeza kwaye ususe izitshixo eziphambili usebenzisa i-ALTER umyalelo

Iqhosha eliphambili linokwenza kuphela kwikholamu enye.Imizekelo yile ilandelayo:

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

Unokucima isitshixo esiphambili ngomyalelo we-ALTER:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

Kufuneka uchaze kuphela ISIQINISO SOMNQOPHISO xa ulahla isitshixo sokuqala, kodwa xa ulahla isalathisi, kufuneka wazi igama lesalathisi.

bonisa ulwazi lwesalathiso

Ungasebenzisa umyalelo BONISA ISIKHOKELO ukudwelisa ulwazi lwesalathiso esifanelekileyo kwitheyibhile.Ulwazi lwemveliso lungalungiswa ngokudibanisa \G.

Zama le mizekelo ilandelayo:

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