MySQL索引的创建及使用总结
一、索引的概念及作用
索引本质上是对表中的数据按照一定的顺序进行逻辑排序,如果把表看成一本书,索引就是书的目录,创建索引的目的在于提高查询效率。拿汉语字典打比方,字典的目录就可以看成是索引,我们可以根据拼音、笔画、偏旁部首等排序的目录快速找到需要的字,这个例子也说明在一张表中可以创建多个索引,而每一个索引就是一种检索方式。
有效的创建索引可以提高检索的速度,但索引也不是创建得越多越好。虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行插入(insert)、更新(update)和删除(delete from)操作时,MySQL不仅要保存数据,同时还要更新索引文件。
MySQL的索引的分类:
1.根据索引所包含字段的个数可以把索引分为:单列索引和组合索引。如果一个索引只包含一个列,该索引称为单列索引;如果一个索引包含多个列,则称为组合索引。实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
2.根据索引字段数据的唯一性可以把索引分为:普通索引和唯一索引。普通索引不要求索引字段的值唯一,比如“name”字段定义为普通索引之后,允许同名的人存在;唯一索引要求索引字段的值唯一,比如“user”字段定义为唯一索引之后,则用户名必须唯一。
3.根据索引的存储结构可以把索引分为:hash索引和btree索引。不同的存储引擎支持的索引类型也不一样:
InnoDB存储引擎:支持事务,支持行级别锁定,支持 B-tree、不支持 Hash 索引;
MyISAM存储引擎:不支持事务,支持表级别锁定,支持 B-tree、不支持 Hash 索引;
Memory存储引擎:不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引;
NDB存储引擎:支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree。
二、索引的创建
首先创建一张表emp,在该表的基础上进行索引操作。创建emp表的代码如下:
create table emp(
emp_id int auto_increment comment '员工编号',
name char(20) not null default '' comment '员工姓名',
gender char(10) not null default '' comment '性别',
birth datetime not null default '0000-00-00' comment '出生日期',
salary decimal(10,2) not null default 0 comment '工资',
phone char(20) not null default '' comment '电话',
card_number char(30) not null default '' comment '银行卡号',
login_account varchar(50) not null default '' comment '登录账户',
login_password varchar(50) not null default '' comment '登录密码',
department int not null default 0 comment '所属部门',
PRIMARY KEY(emp_id)
) engine=InnoDB auto_increment=1 default charset=utf8 comment='员工表';
(一)在创建表的时候同时创建索引
通过create table命令在创建表的同时创建索引,语法如下:
create table tablename(
col_name type,
col_name type,
... ,
index [indexname](column_list[(length)]), --定义普通索引
unique [indexname](column_list[(length)]), --定义唯一索引
primary key(col_name)
);
举例:
create table stu(
stu_id int auto_increment comment '学生学号',
stu_name char(20) not null default '' comment '学生姓名',
age int not null default 0,
phone char(20) not null default '',
addr varchar(200) not null default '',
dept_id int not null default 0,
index idx_stu_name(stu_name),
index idx_age(age),
index idx_stu_name_age(stu_name,age),
primary key(stu_id)
);
(二)创建表之后添加索引
有两种方法可以添加索引:
方法一:利用alter table命令,语法格式如下:
--格式: alter table tablename add index [indexname](column_list[(length)]); --创建普通索引
--格式: alter table tablename add unique [indexname](column_list[(length)]); --创建唯一索引
mysql> alter table emp add index idx_name(name(6)); --创建一个单列索引,包含name字段,名称为idx_name
alter table emp add index idx_name_birth(name,birth); --创建一个多列索引,包含name字段,名称为idx_name_birth
alter table emp add unique idx_card_number(card_number); ----创建一个唯一索引,包含card_number字段
方法二:利用create index命令,语法格式如下:
--格式: create [unique] index index_name tablename(column_list[(length)]);
mysql> create index idx_name emp(name); --创建一个单列索引,包含name字段,名称为idx_name
create index idx_name_birth emp(name,birth); --创建一个多列索引,包含name字段,名称为idx_name_birth
create unique index idx_card_number emp(card_number); --创建一个唯一索引,包含card_number字段
三、删除索引
使用alter table命令删除索引:
--格式: alter table tablename drop index indexname;
mysql> alter table drop index idx_name; --删除名称为idx_name的索引
四、显示索引信息
可以使用show index命令列出表中的索引信息:
--格式: show index from tablename;
mysql> show index from emp;
五、索引创建和使用总结
(一)索引的作用
1.建立索引可以提高数据查询的速度;
2.唯一索引和主键索引可以保证数据的唯一性。
(二)索引存在的问题
1.索引文件需要占用物理存储空间;
2.创建索引和维护索引会耗费时间,如果创建的索引过多,当插入、删除、更新数据时需要动态维护索引,特别时在一个大表上创建了较多索引时,会降低数据库的性能。
(三)创建索引的原则
1.在经常需要搜索的列上,创建索引可以加快检索的的速度;
2.在进行多表查询时,在表的连接条件上创建索引,可以提高连接查询的速度;
3.在进行排序(order by)和分组(group by)查询时,针对需要排序和分组的列上创建索引,可以加快排序和分组查询的速度;
4.在where子句中如果用到运算符Like,如果通配符放到开头,则索引不起作用。比如:name like '张%‘可以使用索引,而name like ‘%张’ 则不能使用索引;
5.索引列中的NULL值将不会被包含在索引中,复合索引中如果有一列含有NULL值则组合索引都将失效。因此,在创建表时需要为字段指定默认值0(数值型字段)、’ ‘字符串(字符串字段)或’0000-00-00’(日期型字段);
6.指定索引长度,如果一个字段是char(20)或者varchar(50),在创建索引时指定前缀长度(比如前10个字符),则可以提高查询速度并且可以减少索引所占用的磁盘空间,也可以减少磁盘I/0操作;
7.不要对索引列进行运算,否则会使索引失效;
8.查询中很少使用到的列一般不创建索引;
9.数据大量重复的字段(比如性别字段)不应该建立索引,这样的索引并不能提高查询效率;
10.当表的增删改操作远远大于检索操作时不应该创建索引。
11.可以使用explain命令查看索引的使用情况。