MySQL索引

2022-03-06 10:13:56

1.概述

索引就是一种提高查询效率的数据结构。其优点是大大加快了数据的查询速度。缺点是索引需要消耗数据库资源，对表进行增删改时由于需要维护索引，其速度有影响。

2.索引分类

为了下面的测试方便，首先创建一个表并插入数据：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL COMMENT ‘用户名‘,
  `china_name` varchar(30) DEFAULT NULL COMMENT ‘中文名‘,
  `password` varchar(20) DEFAULT NULL COMMENT ‘密码‘,
  `id_card` varchar(20) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL COMMENT ‘手机号‘,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_card` (`id_card`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user values(null,‘zhangsan‘,‘张三‘,‘1234‘,‘19991010‘,‘15645254258‘),
(null,‘lisi‘,‘李四‘,‘1234‘,‘19970101‘,‘17545254258‘),
(null,‘zhangwu‘,‘张五‘,‘1234‘,‘19991012‘,‘13645254258‘),
(null,‘liliu‘,‘李六‘,‘1234‘,‘19990203‘,‘15648554258‘),
(null,‘zhaoliu‘,‘赵柳‘,‘1234‘,‘19990509‘,‘15475254258‘),
(null,‘lifei‘,‘李飞‘,‘1234‘,‘19970210‘,‘1564524148‘),
(null,‘zhaohong‘,‘赵虹‘,‘1234‘,‘19960315‘,‘15645254328‘),
(null,‘songqian‘,‘宋倩‘,‘1234‘,‘19970310‘,‘15645254257‘);

2.1主键索引

当字段被设置为主键后数据库就会自动创建索引，此列的值不能为空。此索引是在表创建时就自动创建了。

使用下面的语句查询表的索引：

show index from user;

查询的截图如下：

其中第一条就是主键的索引，索引名称是primary，索引的列名是id列。第二条是唯一索引，下面介绍。

2.2唯一索引

当字段被设置为唯一约束后数据库就会自动创建索引，此列的值是唯一的，但允许有且仅有一个NULL。此索引是在表创建时就自动创建了。

上面在创建表时，指定了身份证号码（id_card列）为唯一约束，就创建了一个索引，索引的名称就是列名：

2.3普通索引

普通索引又称单值索引、单列索引，也就是一个索引值包含一列。一个表可以有多个普通索引。

假如给china_name属性添加索引，方法有两种，如下:

1）创建表时创建索引（不常用）

CREATE TABLE `user` (
    ...
  `china_name` varchar(30) DEFAULT NULL COMMENT ‘中文名‘,
   key(`china_name`)

)

这种当时不能指定索引的名称，默认是字段的名称。

2）创建表后创建索引（推荐）

语法：

create index 索引名称 on 表名(列名);

真实的sql：

create index index_user_china_name on user(china_name);

如果需要删除索引，语法如下：

drop index 索引名称 on 表名;

2.4复合索引

复合索引是一个索引包含多列。其创建方式也有两种，和单值索引类似，这里只介绍第二种方式

下例是给username和password添加复合索引

create index index_user_username_password on user(username,password)

截图如下：

需要注意的是，在使用复合索引时，需要遵循最左侧原则，而且在查询过程中会动态调整字段的顺序。也就是说，在查询时，需包含创建索引时其左侧的字段作进行条件查询，才能利用索引提高效率，条件的顺序可不按照创建索引时的顺序。

且看下面的几种情况：

1）只根据username查询：可以有效利用索引提高查询效率。原因是

2）只根据password查询：不可以。原因是在创建索引时其左侧的字段是username，并未根据此条件查询。

3）根据username和password查询：可以有效利用索引提高查询效率。原因是按创建索引时顺序查询的。

4）根据password和username查询：可以有效利用索引提高查询效率。原因是包含了创建索引时其左侧的字段。虽然没按顺序，但也可以。