SQL索引知识整理

1.mySql 添加索引

1.添加PRIMARY KEY（主键索引）

 ALTER TABLE 'table_name' ADD PRIMARY KEY ( 'column' )

不能使用CREATE INDEX 创建主键索引;

2.添加UNIQUE(唯一索引)

ALTER TABLE 'table_name' ADD UNIQUE ( 'column' );
CREATE UNIQUE INDEX index_name ON table_name ('column');

3.添加INDEX(普通索引)

ALTER TABLE 'table_name' ADD INDEX index_name ( 'column' );
CREATE INDEX index_name ON table_name ('column');

4.添加多列索引

ALTER TABLE 'table_name' ADD INDEX index_name ( 'column1', 'column2','column3' );
CREATE INDEX index_name ON table_name ('column1', 'column2','column3');

5.添加FULLTEXT(全文索引)

ALTER TABLE 'table_name' ADD FULLTEXT ( 'column')

删除索引

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

查看索引

show index from tblname;
show keys from tblname;