mysql索引总结

一、分类

　　1、主键索引

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

　　　　特点：

　　　　　　　不能重复，  id 不能是null

　　　　语法：　　　

#随表一起建索引：
#使用AUTO_INCREMENT关键字的列必须有索引(只要有索引就行)
CREATE TABLE customer (id INT(10) UNSIGNED  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)索引

　　2、单值索引

　　　　即一个索引只包含单个列，一个表可以有多个单列索引

　　　特点：

　　　　　　单列，一个表可以有多个单值索引

　　　语法：

#随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY `index_customer_name` (`customer_name`)  
);

#单独建单值索引：
CREATE INDEX idx_customer_name ON customer(customer_name);

#删除索引：
DROP INDEX idx_customer_name on customer; 　

　　3、唯一索引

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

　　特点：

　　　　不能重复，比如id 可以是null

　　语法：

#随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY `index_customer_name` (`customer_name`),
  UNIQUE `index_custommer_no` (`customer_no`)
);

#单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 

#删除索引：
DROP INDEX idx_customer_no on customer ;

　　4、复合索引

　　　　即一个索引包含多个列

　　　　在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)

　　　　当表的行数远大于索引列的数目时可以使用复合索引

　　　　特点：

　　　　　　多个列构成的索引（相当于二级目录）

　　　　语法：

#随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY  `index_customer_name`(`customer_name`),
  UNIQUE `customer_name`(`customer_name`),
  KEY `index_customer_no_customer_name`(customer_no,customer_name)
);
#单独建索引：
CREATE INDEX idx_no_name ON customer(customer_no,customer_name); 
#删除索引：
DROP INDEX idx_no_name  on customer;

 　　5、查询索引

show index from 表名;

　　

 二、需要创建索引的情况总结

　　1、主键自动建立唯一索引

　　2、频繁作为查询条件的字段应该创建索引(where 后面的语句)

　　3、查询中与其它表关联的字段，外键关系建立索引

　　4、单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)

　　5、查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

　　6、查询中统计或者分组字段

三、不需要创建索引的情况总结

　　1、表记录太少

　　2、经常增删改的表：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

　　3、Where条件里用不到的字段不创建索引

　　4、数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引，某个数据列包含许多重复的内容，建立索引没有太大实际效果

四、小结

　　分类：

主键索引： 不能重复 id 不能是null
单值索引： 单列，一个表可以有多个单值索引；
唯一索引： 不能重复，比如id 可以是null
复合索引：    多个列构成的索引（相当于二级目录）

　　创建索引

方式一：
    create 索引类型  索引名 on 表（字段）
            
单值索引：    create index dept_index on tb(dept);  
唯一索引：     create UNIQUE index name_index on tb(name);  
复合索引：    create index dept_name_index on tb(dept,name);
            
            
方式二：
    alter table 表名 add 索引类型  索引名 （字段）
            
单值索引：    alter table tb add index dept_index (dept);  
唯一索引：     alter table tb add UNIQUE index name_index (name);    
复合索引：    alter table tb  add index dept_name_index (dept,name);

注意：如果一个字段是primary key ,则该字段默认是主键索引

　　删除索引

drop index 索引名 on 表名;
drop index name_index on tb;

　　查询索引

show index from 表名;

 　　索引的优势

1、提高查询效率（降低IO使用率）
2、降低CPU使用率（...order by desc,因为B树索引本身是已经排序好的）

　　索引的弊端

1、索引本身很大，可以存放在内存/硬盘（通常为硬盘）
2、索引不是所有情况均适用：a、少量数据   b、频繁更新的字段
3、索引会降低增删改的效率