mysql索引分类

mysql索引分类

• 主键索引

  设定为主键后数据库会自动建立索引，innodb为聚簇索引

  随表一起建索引：
  CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
    PRIMARY KEY(id) 
  );
  UNSIGNED (无符号的)
  使用  AUTO_INCREMENT 关键字的列必须有索引(只要有索引就行)。
   
  CREATE TABLE customer2 (id INT(10) UNSIGNED   ,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) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
    PRIMARY KEY(id),
    KEY (customer_name)  
  );
   随表一起建立的索引 索引名同 列名(customer_name)
  单独建单值索引：
  CREATE  INDEX idx_customer_name ON customer(customer_name); 
   
  删除索引：
  DROP INDEX idx_customer_name ;

• 唯一索引

  索引列的值必须唯一，但允许有空值

  随表一起建索引：
  CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
    PRIMARY KEY(id),
    KEY (customer_name),
    UNIQUE (customer_no)
  );
  建立 唯一索引时必须保证所有的值是唯一的（除了null），若有重复数据，会报错。  
   
  单独建唯一索引：
  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_name),
    UNIQUE (customer_name),
    KEY (customer_no,customer_name)
  );
   
  单独建索引：
  CREATE  INDEX idx_no_name ON customer(customer_no,customer_name); 
   
  删除索引：
  DROP INDEX idx_no_name  on customer ;

  
  ​
  

• 覆盖索引

  创建一个索引，该索引包含查询中用到的所有字段，称为“覆盖索引”。

  使用覆盖索引，MySQL 只需要通过索引就可以查找和返回查询所需要的数据，而不必在使用索引处理数据之后再进行回表操作。

  也就是说，查询的列和索引的列一致，可以使用覆盖索引，查询中应该尽量使用覆盖索引，减少使用 select * from。

    select id , name from t_xxx where age=18;
           有一个组合索引  idx_id_name_age_xxx 包含了(覆盖了)，id,name,age三个字段。查询时直接将建立了索引的列读取出来了，而不需要去查找所在行的其他数据。所以很高效。
           (个人认为：在数据量较大，固定字段查询情况多时可以使用这种方法。)