Mofuta oa index ea database ea MySQL / theha / sebelisa motsoako oa tšebeliso ea polelo ea ALTER ho MySQL

MySQLmofuta oa index/etsa/sebelisa combo alter inMySQLtšebeliso ea polelo ea taelo

Li-index tsa MySQL

Ho thehoa ha index ea MySQL ho bohlokoa haholo bakeng sa ts'ebetso e nepahetseng ea MySQL, 'me index e ka ntlafatsa haholo lebelo la ho khutlisa MySQL.

Ka mohlala, haeba MySQL e entsoeng ka mokhoa o nepahetseng le e ngotsoeng ka mokhoa o nepahetseng ke Lamborghini, joale MySQL ntle le moralo oa indexed ke tricycle ea motho.

Lenane le arotsoe ka index ea kholomo e le 'ngoe le index e kopaneng.index ea kholomo e le 'ngoe, ke hore, index e na le kholomo e le 'ngoe feela, tafole e ka ba le li-index tse ngata tsa kholomo e le 'ngoe, empa sena ha se index e kopaneng.Lenane la li-composite, ke hore, index e na le likholomo tse ngata.

Ha u theha index, u lokela ho etsa bonnete ba hore index ke boemo bo sebetsang ho potso ea SQL (hangata e le boemo ba karolo ea WHERE).

Ha e le hantle, index le eona ke tafole, tafole e boloka lintlha tsa mantlha le likarolo tsa index, 'me e supa lirekoto tsa tafole ea mokhatlo.

Ba ka holimo ba bua ka melemo ea ho sebelisa li-index, empa tšebeliso e feteletseng ea li-index e tla lebisa tlhekefetsong.Ka hona, index e tla boela e be le mefokolo ea eona: le hoja index e ntlafatsa haholo lebelo la potso, e tla fokotsa lebelo la ho ntlafatsa tafole, joalo ka INSERT, UPDATE le DELETE ea tafole.Hobane ha u ntlafatsa tafole, MySQL ha e boloke data feela, empa hape e boloka faele ea index.

Ho supa index ea index e jang sebaka sa disk.

index e tloaelehileng

theha index

Ena ke index ea mantlha ka ho fetisisa, ha e na lithibelo.E ka etsoa ka litsela tse latelang:

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

Bakeng sa mefuta ea CHAR le VARCHAR, bolelele bo ka ba tlase ho bolelele ba tšimo; bakeng sa mefuta ea BLOB le TEXT, bolelele bo tlameha ho boleloa.

Fetola sebopeho sa tafole (eketsa index)

ALTER table tableName ADD INDEX indexName(columnName)

Hlalosa ka ho toba ha u theha tafole

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

Syntax ho theola index

DROP INDEX [indexName] ON mytable; 

index e ikhethang

E tšoana le index e tloaelehileng e fetileng, phapang ke hore: boleng ba index ea index e tlameha ho ba bo ikhethang, empa litekanyetso tse se nang thuso li lumelloa.Tabeng ea index ea composite, motsoako oa boleng ba kholomo e tlameha ho ikhetha.E ka etsoa ka litsela tse latelang:

theha index

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

Fetola sebopeho sa tafole

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

Hlalosa ka ho toba ha u theha tafole

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

Kenya le ho tlosa li-index u sebelisa taelo ea ALTER

Ho na le mekhoa e mene ea ho kenyelletsa index ho tafole ea data:

• ALTER TABLE tbl_name EKELA PRIMARY KEY (kholomo_lenane): Polelo ena e eketsa senotlolo sa mantlha, se bolelang hore litekanyetso tsa index li tlameha ho ba tse ikhethang 'me li ke ke tsa ba NULL.
• ALTER TABLE tbl_name ADD UNIQUE index_name (kholomo_lenane): Boleng ba index e entsoeng ke polelo ena e tlameha ho ba e ikhethang (ntle le NULL, NULL e ka hlaha ka makhetlo a mangata).
• ALTER TABLE tbl_name ADD INDEX index_name (kholomo_lenane): Kenya index e tloaelehileng, boleng ba index bo ka hlaha hangata.
• ALTER TABLE tbl_name ADD FULLTEXT index_name (kholomo_lenane):Polelo e totobatsa index e le FULLTEXT bakeng sa index ea mongolo o felletseng.

Mohlala o latelang ke oa ho kenya index ho tafole.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

U ka sebelisa poleloana ea DROP ho taelo ea ALTER ho theola li-index.Leka mohlala o latelang ho theola index:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

Kenya le ho tlosa linotlolo tsa mantlha u sebelisa taelo ea ALTER

Senotlolo sa mantlha se ka sebetsa kholomong e le 'ngoe feela. Ha u kenya index ea li-primary key, u lokela ho etsa bonnete ba hore senotlolo ha se NULL ka ho sa feleng (NOT NULL).Mehlala ke e latelang:

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

U ka boela ua hlakola senotlolo sa mantlha ka taelo ea ALTER:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

U hloka feela ho hlakisa PRIMARY KEY ha u lahla senotlolo sa mantlha, empa ha u lahla index, u tlameha ho tseba lebitso la index.

bonts'a lintlha tsa index

U ka sebelisa taelo ea SHOW INDEX ho thathamisa lintlha tsa index tse amehang tafoleng.Lintlha tse hlahisoang li ka hlophisoa ka ho eketsa \G.

Leka mehlala e latelang:

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