MySQL常见的索引及创建方式

MySQL常见的索引及创建方式

普通索引

CREATE INDEX indexName ON tableName(columnName(length)); 
ALTER table tableName INDEX indexName(columnName); 
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
);  

主键索引

ALTER table tableName ADD CONSTRAINT indexName PRIMARY KEY (columnName);
CREATE TABLE `user_innodb` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

全文索引

CREATE FULLTEXT indexName ON tableName(columnName);
ALTER table tableName FULLTEXT INDEX indexName(columnName);
CREATE TABLE article (
	id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	title VARCHAR(200),
	content TEXT,
	FULLTEXT (title, content) --在title和content列上创建全文索引
);

唯一索引

CREATE UNIQUE indexName ON tableName(columnName);
ALTER table tablename UNIQUE [indexName](columnName);
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
); 

联合索引（组合索引）

CREATE INDEX indexName ON tableName(columnName,columnName);
ALTER table tableName INDEX indexName(columnName,columnName);
CREATE TABLE mytable (
    `id` int(11) ,
    `name` VARCHAR(32) ,
    INDEX index_mytable_id_name (`id`,`name`) 
);