Nau'in ma'auni na MySQL / ƙirƙira / amfani da haɗin haɗin ALTER bayanin amfani a cikin MySQL

MySQLnau'in index / ƙirƙira / amfani da haɗin haɗin haɗin gwiwaMySQLAmfani da sanarwa na umarni

MySQL index

Ƙirƙirar ƙididdiga ta MySQL yana da matukar muhimmanci ga ingantaccen aiki na MySQL, kuma maƙasudin na iya inganta saurin dawo da MySQL.

Misali, idan MySQL tare da ƙira mai ma'ana da amfani da fihirisa Lamborghini ne, to MySQL ba tare da fihirisa da fihirisa ba ɗan adam ne.

An raba fihirisar zuwa fihirisar ginshiƙi ɗaya da fihirisar haɗaɗɗiya.Fihirisar ginshiƙi ɗaya, wato, fihirisa ya ƙunshi ginshiƙi ɗaya kawai, tebur na iya samun maƙasudin ginshiƙa guda ɗaya, amma wannan ba ƙididdiga ba ce.Fihirisar haɗaka, wato, fihirisa ta ƙunshi ginshiƙai da yawa.

Lokacin ƙirƙirar fihirisar, kuna buƙatar tabbatar da cewa fihirisar yanayin yanayi ne da aka yi amfani da shi ga tambayar SQL (gaba ɗaya a matsayin yanayin jumlar INA).

A gaskiya ma, fihirisar ma tebur ne, tebur yana adana maɓalli na farko da filayen fihirisa, kuma yana nuna bayanan teburin mahaɗan.

Abubuwan da ke sama suna magana ne game da fa'idodin yin amfani da fihirisa, amma yawan amfani da fihirisa zai haifar da cin zarafi.Don haka, index ɗin kuma zai sami gazawarsa: kodayake index yana inganta saurin tambaya sosai, zai rage saurin sabunta tebur, kamar INSERT, UPDATE da DELETE akan tebur.Domin lokacin sabunta tebur, MySQL ba wai kawai yana adana bayanai ba, har ma yana adana fayil ɗin index.

Fitar da fayil ɗin fihirisar da ke cinye sararin diski.

al'ada index

ƙirƙirar index

Wannan ita ce fihirisar asali, ba ta da hani.Ana iya ƙirƙirar ta ta hanyoyi masu zuwa:

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

Don nau'ikan CHAR da VARCHAR, tsayin zai iya zama ƙasa da ainihin tsawon filin; don nau'ikan BLOB da TEXT, dole ne a ƙayyade tsayi.

Gyara tsarin tebur (ƙara fihirisa)

ALTER table tableName ADD INDEX indexName(columnName)

Saka kai tsaye lokacin ƙirƙirar tebur

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

Daidaitawa don sauke fihirisa

DROP INDEX [indexName] ON mytable; 

na musamman index

Ya yi kama da fihirisar yau da kullun na baya, bambancin shine: ƙimar ginshiƙi dole ne ya zama na musamman, amma ana ba da izini mara amfani.A cikin yanayin ƙididdiga mai haɗaka, haɗin ƙimar ginshiƙi dole ne ya zama na musamman.Ana iya ƙirƙirar ta ta hanyoyi masu zuwa:

ƙirƙirar index

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

Gyara tsarin tebur

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

Saka kai tsaye lokacin ƙirƙirar tebur

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

Ƙara kuma cire firikwensin ta amfani da umarnin ALTER

Akwai hanyoyi guda huɗu don ƙara fihirisa zuwa teburin bayanai:

• ALTER TABLE tbl_name ADD PRIMARY KEY (jerin_column): Wannan bayanin yana ƙara maɓalli na farko, wanda ke nufin cewa ƙimar fihirisa dole ne su kasance na musamman kuma ba za su iya zama NULL ba.
• ALTER TABLE tbl_name ADD UNIQUE index_name (jerin_column): Dole ne ƙimar fihirisar da wannan bayanin ya ƙirƙira ya zama na musamman (sai dai NULL, NULL na iya bayyana sau da yawa).
• ALTER TABLE tbl_name ADD INDEX index_name (jerin_column): Ƙara ƙididdiga ta al'ada, ƙimar fihirisar zata iya bayyana sau da yawa.
• ALTER TABLE tbl_name ADD FULLTEXT index_name (jerin ginshiƙi):Bayanin ya ƙayyadad da fihirisar a matsayin FULLTEXT don cikakken rubutun rubutu.

Misali mai zuwa shine don ƙara fihirisa a tebur.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

Hakanan zaka iya amfani da jumlar DROP akan umarnin ALTER don sauke fihirisa.Gwada misali mai zuwa don sauke fihirisar:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

Ƙara ku cire maɓallan farko ta amfani da umarnin ALTER

Maɓalli na farko kawai zai iya aiki akan shafi ɗaya kawai.Lokacin ƙara fihirisar maɓalli na farko, kuna buƙatar tabbatar da cewa maɓallin farko baya NULL ta tsohuwa (BA NULL).Misalai sune kamar haka:

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

Hakanan zaka iya share maɓalli na farko tare da umarnin ALTER:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

Kuna buƙatar saka maɓalli na PRIMARY kawai lokacin jefar da maɓallin farko, amma lokacin jefar da fihirisa, dole ne ku san sunan fihirisar.

nuna bayanan index

Kuna iya amfani da umarnin SHOW INDEX don jera bayanan fihirisa masu dacewa a cikin tebur.Za a iya tsara bayanan fitarwa ta ƙara \G.

Gwada misalai masu zuwa:

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