MySQL database index hom / tsim / siv ua ke ALTER nqe lus siv hauv MySQL

MySQLHom ntawv qhia / kev tsim / kev siv ua ke hloov pauv hauvMySQLKev siv cov lus txib Suav

Cov ntsiab lus MySQL

Kev tsim cov indexes MySQL yog qhov tseem ceeb rau kev ua haujlwm zoo ntawm MySQL, vim tias cov indexes tuaj yeem txhim kho MySQL qhov kev rov qab sai dua.

Hais yooj yim xwb, yog tias lub kaw lus MySQL uas tsim tau zoo uas siv cov indexes zoo li Lamborghini, ces lub kaw lus MySQL uas tsis siv cov indexes zoo li lub tsheb laij teb rickshaw.

Cov ntsuas tau muab faib ua cov ntsuas ib kab thiab cov ntsuas sib xyaw. Cov ntsuas ib kab tsuas muaj ib kab xwb. Ib lub rooj tuaj yeem muaj ntau cov ntsuas ib kab, tab sis cov no tsis yog cov ntsuas sib xyaw. Cov ntsuas sib xyaw muaj ntau kab.

Thaum tsim ib qho index, koj yuav tsum xyuas kom meej tias cov index raug siv ua ib qho mob hauv cov lus nug SQL (feem ntau yog ib qho mob hauv WHERE clause).

Qhov tseeb, ib qho index kuj yog ib lub rooj uas khaws cov thawj qhov tseem ceeb thiab cov teb index thiab taw tes rau cov ntaub ntawv hauv lub rooj entity.

Cov lus sib tham saum toj no tau hais txog cov txiaj ntsig ntawm kev siv cov indexes, tab sis kev siv ntau dhau tuaj yeem ua rau muaj kev siv tsis raug. Yog li ntawd, cov indexes kuj muaj lawv qhov tsis zoo: thaum lawv txhim kho qhov ceev ntawm cov lus nug, lawv tuaj yeem ua rau qeeb qeeb ntawm kev hloov kho lub rooj, xws li INSERT, UPDATE, thiab DELETE kev ua haujlwm. Qhov no yog vim tias thaum hloov kho lub rooj, MySQL yuav tsum khaws tsis yog cov ntaub ntawv xwb tab sis kuj tseem cov ntaub ntawv index.

Kev tsim ib daim ntawv qhia yuav siv qhov chaw disk rau cov ntaub ntawv qhia.

Cov Ntsuas Ib Txwm

Tsim ib qho index

Qhov no yog qhov ntsuas yooj yim tshaj plaws, thiab nws tsis muaj kev txwv. Nws tuaj yeem tsim tau ntau txoj hauv kev:

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

Rau cov hom CHAR thiab VARCHAR, qhov ntev yuav tsawg dua qhov ntev tiag tiag ntawm daim teb; rau cov hom BLOB thiab TEXT, qhov ntev yuav tsum tau teev tseg.

Hloov kho cov qauv ntawm lub rooj (ntxiv cov index)

ALTER table tableName ADD INDEX indexName(columnName)

Qhia meej ncaj qha thaum tsim lub rooj

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

Syntax rau kev rho tawm ib qho index

DROP INDEX [indexName] ON mytable; 

Cov Ntawv Qhia Tshwj Xeeb

Nws zoo ib yam li cov ntsuas ib txwm muaj, tsuas yog tias cov nqi hauv kab ntawv ntsuas yuav tsum yog qhov tshwj xeeb, tab sis cov nqi null raug tso cai. Rau cov ntsuas sib xyaw, kev sib xyaw ua ke ntawm cov nqi kab ntawv yuav tsum yog qhov tshwj xeeb. Nws tuaj yeem tsim tau raws li cov hauv qab no:

Tsim ib qho index

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

Hloov kho cov qauv ntawm lub rooj

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

Qhia meej ncaj qha thaum tsim lub rooj

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

Siv cov lus txib ALTER los ntxiv thiab rho tawm cov indexes.

Muaj plaub txoj hauv kev los ntxiv cov index rau hauv lub rooj ntaub ntawv:

• HLUA LUB ROOJ tbl_name NTXIV TUS KEY TSEEM CEEB (column_list): Cov lus no ntxiv ib qho tseem ceeb tseem ceeb, uas txhais tau tias tus nqi ntsuas yuav tsum yog qhov tshwj xeeb thiab tsis tuaj yeem yog NULL.
• HLUA LUB ROOJ tbl_name NTXIV LUB CIM index_name (column_list): Tus nqi uas tsim los ntawm cov lus no yuav tsum yog qhov tshwj xeeb (tshwj tsis yog NULL, uas yuav tshwm sim ntau zaus).
• HLUA LUB ROOJ tbl_name NTXIV INDEX index_name (column_list): Ntxiv ib qho ntsuas tsis tu ncua, thiab tus nqi ntsuas tuaj yeem tshwm sim ntau zaus.
• HLOOV LUB ROOJ tbl_name NTXIV COV NTAUB NTAWV TAG NRHO index_name (column_list):Cov lus no qhia txog cov index ua FULLTEXT rau kev indexing tag nrho cov ntawv nyeem.

Cov piv txwv hauv qab no qhia txog yuav ua li cas ntxiv ib qho index rau hauv ib lub rooj.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

Koj kuj tseem siv tau kab lus DROP hauv ALTER command kom rho tawm cov indexes. Sim ua piv txwv hauv qab no kom rho tawm cov index:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

Siv cov lus txib ALTER los ntxiv thiab rho tawm cov yuam sij tseem ceeb.

Ib tus yuam sij tseem ceeb tsuas yog siv tau rau ib kab xwb. Thaum ntxiv tus yuam sij tseem ceeb, koj yuav tsum xyuas kom meej tias tus yuam sij tseem ceeb tsis yog null los ntawm lub neej ntawd (TSIS YOG NULL). Piv txwv li hauv qab no:

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

Koj tseem siv tau ALTER command kom rho tawm tus primary key:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

Thaum rho tawm tus yuam sij tseem ceeb, koj tsuas yog yuav tsum qhia tus yuam sij tseem ceeb xwb, tab sis thaum rho tawm tus lej ntsuas, koj yuav tsum paub lub npe lej ntsuas.

Tso saib cov ntaub ntawv qhia txog qhov ntsuas

Koj siv tau cov lus txib SHOW INDEX los teev cov ntaub ntawv tseem ceeb rau ib lub rooj. Koj tuaj yeem tsim cov zis los ntawm kev ntxiv \G.

Sim ua piv txwv hauv qab no:

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