MySQL高级——MySQL索引分类及建索引命令语句

一、索引分类

• 主键索引 设定为主键后数据库自动建立索引，InnoDB为聚簇索引
• 单值索引 即一个索引只包含单个列，一个表可以有多个单列索引
• 唯一索引 索引列的值必须唯一，但允许有空值
• 复合索引 即一个索引包含多个列，在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)，当表的行数远大于索引列的数目时可以使用复合索引

二、索引的基本语法

• 创建索引 create [unique] index indexName on tableName(columnName(length)); alert tableName add [unique] index indexName on (columnName(length));
• 删除索引 drop index [indexName] on tableName;
• 查看索引 show index from tableName;

三、使用alert命令给数据表添加索引的四种方式

• 添加一个主键，意味着索引值必须是唯一的，且不能为null

  ALTER TABLE tableName add PRIMARY KEY(colunmName);
  

• 创建索引的值必须是唯一的（除了null外，null可能会出现多次）

  ALTER TABLE tableName add unique indexName(columnName);
  

• 创建普通索引，索引值可以出现多次

  ALTER TABLE tableName add index indexName(columnName);
  

• 创建一个指定了索引为fulltext，用于全文索引

  ALTER TABLE tableName add fulltext indexName(columnName);
  

四、主键索引创建和删除

• 随表一起建主键索引

  CREATE TABLE customer (
  			id INT(10) AUTO_INCREMENT,
  			customer_no VARCHAR(200),
  			customer_name VARCHAR(200),
  			PRIMARY KEY(id) 
  );
  

• 单独建主键索引

  ALTER TABLE customer add PRIMARY KEY customer(customer_no); 
  

• 删除建主键索引

  ALTER TABLE customer drop PRIMARY KEY;  
  

• 修改建主键索引

  必须先删除掉(drop)原索引，再新建(add)索引
  

五、单值索引的创建和删除

• 随表一起建单值索引

  CREATE TABLE customer(
  	id INT(10) AUTO_INCREMENT,
  	customer_no VARCHAR(200),
  	customer_name VARCHAR(200),
    	PRIMARY KEY(id),
    	KEY (customer_name)  
  );
  

• 单独建单值索引

  CREATE INDEX idx_customer_name ON customer(customer_name);
  

• 删除单值索引

  DROP INDEX idx_customer_name ;
  

六、唯一索引的创建和删除

• 随表一起建索引

  CREATE TABLE customer(
  	id INT(10) AUTO_INCREMENT,
  	customer_no VARCHAR(200),
  	customer_name VARCHAR(200),
   	PRIMARY KEY(id),
    	UNIQUE (customer_no)
  );
  

• 单独建唯一索引

   CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
  

• 删除唯一索引

  DROP INDEX idx_customer_no on customer ;
  

七、复合索引的创建和删除

• 随表一起建复合索引

  CREATE TABLE customer(
  	id INT(10) UNSIGNED  AUTO_INCREMENT ,
  	customer_no VARCHAR(200),
  	customer_name VARCHAR(200),
    	PRIMARY KEY(id),
    	KEY (customer_no,customer_name)
  );
  

• 单独建复合索引

  CREATE INDEX idx_no_name ON customer(customer_no,customer_name); 
  

• 删除复合索引

  DROP INDEX idx_no_name  on customer ;
  

八、复合索引与单值索引区别

• no 有一个索引 idx_no

单值索引：create index idx_no on emp(no);

• no 与 name 有同一个索引 idx_no_name

  复合索引：create index idx_no_name on emp(no,name);