一、分类
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、索引会降低增删改的效率