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

MySQLindex hom / tsim / siv combo hloov hauvMySQLkev siv cov lus txib

MySQL indexes

Kev tsim ntawm MySQL Performance index yog qhov tseem ceeb heev rau kev ua haujlwm zoo ntawm MySQL, thiab qhov Performance index tuaj yeem txhim kho qhov nrawm ntawm MySQL.

Piv txwv li, yog tias MySQL nrog tsim tsim nyog thiab siv cov indexes yog Lamborghini, ces MySQL tsis muaj indexes thiab indexes yog tib neeg lub tsheb kauj vab.

Qhov Performance index muab faib ua ib kab index thiab composite index.Ib-kab Index, uas yog, qhov Performance index tsuas muaj ib kem xwb, ib lub rooj muaj peev xwm muaj ntau yam ib-kem indexes, tab sis qhov no tsis yog ib qho index.Composite index, uas yog, ib qho index muaj ntau kab.

Thaum tsim ib qho Performance index, koj yuav tsum xyuas kom meej tias qhov Performance index yog ib qho mob uas siv rau SQL query (feem ntau yog ib qho xwm txheej ntawm WHERE clause).

Qhov tseeb, qhov Performance index kuj yog ib lub rooj, lub rooj txuag cov ntsiab lus tseem ceeb thiab qhov ntsuas qhov ntsuas, thiab taw qhia rau cov ntaub ntawv ntawm lub rooj sib tham.

Cov saum toj no yog tham txog cov txiaj ntsig ntawm kev siv cov indexes, tab sis kev siv ntau dhau ntawm kev ntsuas yuav ua rau muaj kev tsim txom.Yog li ntawd, qhov ntsuas kuj tseem yuav muaj qhov tsis txaus: txawm hais tias qhov ntsuas tau zoo txhim kho cov lus nug ceev, nws yuav txo qhov nrawm ntawm kev hloov kho lub rooj, xws li INSERT, UPDATE thiab DELETE ntawm lub rooj.Vim tias thaum hloov kho lub rooj, MySQL tsis tsuas yog txuag cov ntaub ntawv xwb, tab sis kuj txuag cov ntaub ntawv index.

Indexing cov ntaub ntawv index uas siv qhov chaw disk.

ib txwm index

tsim index

Qhov no yog qhov yooj yim tshaj plaws index, nws tsis muaj kev txwv.Nws tuaj yeem tsim tau raws li hauv qab no:

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

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

Hloov cov qauv rooj (ntxiv index)

ALTER table tableName ADD INDEX indexName(columnName)

Qhia ncaj qha thaum tsim lub rooj

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

Syntax poob ib qho index

DROP INDEX [indexName] ON mytable; 

cim index

Nws zoo ib yam li yav dhau los qhov kev ntsuas zoo tib yam, qhov sib txawv yog: tus nqi ntawm qhov ntsuas kab ke yuav tsum yog qhov tshwj xeeb, tab sis cov txiaj ntsig null raug tso cai.Nyob rau hauv cov ntaub ntawv ntawm ib tug composite index, kev sib xyaw ua ke ntawm kem qhov tseem ceeb yuav tsum yog tshwj xeeb.Nws tuaj yeem tsim tau raws li hauv qab no:

tsim index

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

Hloov cov qauv rooj

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

Qhia ncaj qha thaum tsim lub rooj

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

Ntxiv thiab tshem tawm indexes siv ALTER hais kom ua

Muaj plaub txoj hauv kev ntxiv qhov ntsuas rau cov ntaub ntawv:

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): Cov lus no ntxiv cov ntsiab lus tseem ceeb, uas txhais tau hais tias qhov ntsuas qhov tseem ceeb yuav tsum yog qhov tshwj xeeb thiab tsis tuaj yeem yog NULL.
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): Tus nqi ntawm qhov ntsuas tau tsim los ntawm nqe lus no yuav tsum yog qhov tshwj xeeb (tshwj tsis yog NULL, NULL yuav tshwm sim ntau zaus).
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): Ntxiv qhov ntsuas ib txwm, tus nqi ntsuas tuaj yeem tshwm sim ntau zaus.
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):Cov nqe lus qhia qhov Performance index raws li FULLTEXT rau tag nrho cov ntawv indexing.

Cov piv txwv hauv qab no yog ntxiv qhov ntsuas rau ib lub rooj.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

Koj tseem tuaj yeem siv DROP clause ntawm ALTER hais kom ua kom poob indexes.Sim ua qhov piv txwv hauv qab no kom poob qhov ntsuas:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

Ntxiv thiab tshem tawm thawj tus yuam sij siv ALTER hais kom ua

Tus yuam sij tseem ceeb tsuas yog ua rau ntawm ib kab xwb. Thaum ntxiv qhov tseem ceeb qhov ntsuas, koj yuav tsum xyuas kom meej tias tus yuam sij tseem ceeb tsis yog NULL los ntawm lub neej ntawd (TSIS NULL).Piv txwv li no:

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

Koj tseem tuaj yeem rho tawm tus yuam sij tseem ceeb nrog ALTER hais kom ua:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

Koj tsuas yog yuav tsum tau qhia qhov PRIMARY KEY thaum tso tus yuam sij tseem ceeb, tab sis thaum poob qhov ntsuas, koj yuav tsum paub lub npe ntsuas.

qhia cov ntaub ntawv index

Koj tuaj yeem siv SHOW INDEX cov lus txib los sau cov ntaub ntawv txheeb xyuas qhov cuam tshuam hauv lub rooj.Cov ntaub ntawv tso zis tuaj yeem raug formatted los ntawm kev ntxiv \G.

Sim ua piv txwv hauv qab no:

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