MySQL的索引(二十三)上-阿里云开发者社区

一. 索引

一.一索引的产生

前面已经学习了查询，有没有想过这样一个问题? 数据库表是如何查询的呢?

自然是从第一条记录到最后一条记录，依次遍历，看条件是否符合，如果符合，就展示出来，如果不符合，就继续遍历，直到末尾。

如从 user 表里面查询 id=5 的员工记录，

select * from user where id=5;

会先从 user 表的开头 id=1 处时开始遍历，遍历到id=4时，发现不符合条件，那么继续遍历，到id=5了，发现id=5符合条件，就将这一条记录展示出来。

由于id是主键，只存在唯一一条记录，那么就不继续往下面遍历了。

如果从user 表里面查询 name=‘岳泽霖’ 的员工记录. （岳泽霖的员工编号 id是5）

select * from user where name='岳泽霖';

会先从 user 表的开头 id=1处时开始遍历，查询id=1时的name名称，看是否符合条件为 ‘岳泽霖’，

发现不符合条件，继续遍历。遍历到id=4时，也不符合条件，继续遍历

到id=5时，该条记录的name 是 ‘岳泽霖’，符合条件，就将这一条记录展示出来。

由于 name并不是主键，也不是唯一约束，可能存在多个值，所以会继续往下面遍历，

发现id=6,id=7，直到最后，也没有找到其他的记录，所以共展示出一条记录， id=5的记录。

(注意: id=6,id=7… 继续查询了)

查询，类似于算法中的搜索，不同的搜索算法，查询的时间和所需要的空间是不同的。

很明显，上面的这种数据库查询方式不太友好，特别是数据量大的时候。

假如，user 表有三百万条数据，要查询最后一条记录，需要将前面的 (三百万-1) 条数据先查询后判断一下，最后才能得出一条。如果要查询的记录不存在，那么将所有的三百万条数据查询判断之后，发现没有对应的数据，得到 null, 也是不太友好的。

实际生活和工作中，数据量远远大于三百万，尤其是目前大数据的年代。所以，会导致查询的速度更慢。

不知道有没有观察过字典，字典前面常常有几十页，是拼音索引，笔画索引，部首索引等，利用索引，可以很方便的定位到数据库中的记录。

MySQL数据库就支持索引。索引主要是为了提高查询速度，缩短查询的时间。

一.二索引的实际效果

老蝴蝶先在数据库 yuejl 中创建了一个表 myindex, 用于存放大量数据。

myindex 表里面，有 id (主键)，name,name_cp(与name查询时，进行比较,数据完全一样) 三个字段。

show create table myindex \G

通过 java 的批处理程序，已经插入了将近三百万条数据，形式为（i,两个蝴蝶飞i,两个蝴蝶飞i）

由于 id是主键，隐含了唯一索引，用 name 和 name_cp 字段来进行演示。

演示一:

分别查询 name 为 20000000 （二百万）和 2999999 (三百万-1) 的时间。

然后，为 name 添加索引，再搜索 name为 2000000 和 2999999 的时间。

观察，前后时间的对比。

// 添加索引之前
  select * from myindex where name='两个蝴蝶飞2000000';
  select * from myindex where name='两个蝴蝶飞2999999';
  //添加索引
  create index index_name on myindex(name);
  //添加索引之后，再次进行查询
  select * from myindex where name='两个蝴蝶飞2000000';
  select * from myindex where name='两个蝴蝶飞2999999';

未创建索引之前，查询 2000000的时间大约是 0.77s, 2999999的时间大约是 0.90s,

而创建索引之后，查询2000000的时间大约是0.00s, 2999999的时间大约也是 0.00s, 忽略成瞬查。

（每一次查询的最终时间，并不是一样的，受内存等因素的影响）

注意，为已经存在了大量数据的表添加索引时，花费时间很长。本次演示花费了 10s以上。

接下来，重新删除掉索引，为 name_cp 添加索引进行演示。

alter table myindex drop index index_name;

演示二:

name和 name_cp的数据，完全一样。

// 添加索引之前 name的查询
  select * from myindex where name='两个蝴蝶飞2000000';
  select * from myindex where name='两个蝴蝶飞2999999';
  // 添加索引之前 name_cp的查询
  select * from myindex where name_cp='两个蝴蝶飞2000000';
  select * from myindex where name_cp='两个蝴蝶飞2999999';
  //添加索引 为 name_cp
  create index index_name_cp on myindex(name_cp);
  //添加索引之后，name进行查询
  select * from myindex where name='两个蝴蝶飞2000000';
  select * from myindex where name='两个蝴蝶飞2999999';
  //添加索引之后，name_cp进行查询
  select * from myindex where name_cp='两个蝴蝶飞2000000';
  select * from myindex where name_cp='两个蝴蝶飞2999999';

每次查询结果的时间通常是不一样的，但添加了索引之后，也同样达到了瞬查的效果。

用 id (隐含了唯一索引) 进行查询的话:

select * from myindex where id=2000000;
select * from myindex where id=2999999;

发现，索引可以大大的缩短查询的时间。

在前台页面时，查询数据用了1s,处理数据用了0.5s, 那么用户共延时 1.5s, 用户会感觉到卡。

而用了索引之后，查询数据用了0.00s,处理数据用了0.5s (实际开发中，后端程序逻辑也会优化) ，

那么用户共延时 0.5s, 用户不会感觉到很卡。

一.三索引的优缺点

索引是在存储引擎中实现的，与数据表的存储引擎有关。每一种的存储引擎的索引都不一定完全相同，

每一种存储引擎也不一定支持所有的索引类型。

目前的索引类型有两种: BTREE 和 HASH.

MyISAM 和 InnoDB 存储引擎只支持 BTREE . MEMORY/HEAP 支持 BTREE 和 HASH.

通常的常见存储引擎为 MyISAM 和 InnoDB, 5.7版本默认的存储引擎是 InnoDB. 需要掌握 BTREE.

优点:

大大加快数据的查询速度，是最主要的原因。

通过创建唯一索引，可以保证每一行数据的唯一性。

对于外键的字段，添加索引，可以加速表和表之间的连接。

对于分组和排序的字段添加索引，也可以减少分组和排序的时间。

缺点:

创建索引和维护索引要花费时间，并且随着数据量的增加，花费的时间也增加。

索引需要占磁盘空间，如果有大量的索引，可以索引文件与数据文件还大。

当对表数据进行维护(插入，更新，删除)时，索引也要动态的维护，降低了数据的维护速度。

一.四索引的分类

索引可以分为以下几类

一.四.一普通索引和唯一索引

普通索引是默认索引，允许插入重复值和空值。

唯一索引，索引值必须唯一，但允许是空值。类似于 UNIQUE 约束(修改表结构的唯一约束，是对索引的维护)。

如果是组合索引，那么组合的值，必须唯一。

其中，主键索引是一种特殊的唯一索引，只是不允许有空值。

创建表时，设置主键，隐含了唯一索引。

一.四.二单列索引和组合索引

单列索引只包含单个列，组合索引包含了两个及两个以上的列。

(name) 是单列索引， (name,name_cp) 是多列索引。

注意，多列索引时，需要遵循最左前缀原则

即: (name,name_cp) 组合索引时， where 条件是name_cp ，没有name的话，是不能用索引的。

(name,name_cp,age) 组合索引时， where条件是 name_cp和age，没有 name的话，是不能用索引的。

(name,name_cp,age) 组合索引时， where 条件是 name,age的话，是可以用索引的。

组合索引时，要注意各个列的顺序，要将查询时最常用的列放置在左边。

一.四.三全文索引

全文索引是 FULLTEXT . 支持全文查找，可以重复，也可以为空值。通常在 CHAR,VARCHAR,TEXT上创建。

注意，只有 MyISAM存储引擎才支持全文索引（ps:实际操作中，5.6版本之后，InnoDB也支持）

一.四.四空间索引

只能在空间数据类型上使用。空间数据类型有 GEOMETRY,POINT,LINESTRING 和 POLYGON.

不允许有空值。只有 MyISAM 存储引擎才支持空间索引 (ps:不常用,5.7.4版本之后，InnoDB也支持)

一.五索引的设计原则

索引设计的不合理，或者缺少索引，都可能会对性能造成相应的影响。高效的索引，才是重要的。

索引并非越多越好

数据量小的表最好不使用索引

查询中很少使用的列或者参考的列，不使用索引

只有很少数据值的列，不应该使用索引。如性别列，学历列。

对于经常更新的表，避免使用索引，对于经常查询的列，使用索引。

二. 创建索引

创建索引时，可以在创建表的同时，创建索引，也可以在已经存在的表上创建索引。

注意，对于已经存在的表，要注意表数据对索引的影响。如表中有相同的数据，那么是不能创建唯一索引的。

只讲解最常见的普通索引和唯一索引。组合索引，在文章最后讲解。

全文索引，空间索引，用得较少，只在文章最后简单讲述一下。

二.一在创建表的同时，创建索引

二.一.一创建命令

命令:

create table table_name [col_name data_type]
  [空|unique|fulltext|spatial] index index_name (col_name [length]) [asc|desc]

index_name 如果不指定，默认col_name 为索引值。通常情况下，建议指定。

length 为索引的长度，只有字符串类型才能指定索引长度.

二.一.二普通表的创建，用于比较区别

如创建一个 noIndex 表，里面只有 id,name 和 age 三个字段。

// 创建表
  create table noIndex(
  id int(11) primary key,
  name varchar(20),
  age int(3)
  );
 // 展示表信息
  show create table noIndex \G

二.一.三创建普通索引

创建一个 index1 表，在 name 上创建索引。名称为 index1_name

// 创建表
  create table index1(
  id int(11) primary key,
  name varchar(20),
  age int(3),
  index index1_name(name)
  );
  // 展示表信息
  show create table index1 \G

说明，索引创建成功。

二.一.四查看索引是否正在使用

查看索引是否正在使用，用 explain 。关于 explain 的用法，后面老蝴蝶会讲解的。

在name 上创建了索引:

explain select * from index1 where name='两个蝴蝶飞' \G

possible_keys 和 key 均为 index1_name, 表示使用了索引。

在 age 上没有创建索引:

explain select * from index1 where age=24 \G

possible_keys 和 key 为 null, 表示没有使用索引。

二.一.五 show index from 表名查看索引信息

可以通过 show index from 表名，来查看当前表的索引信息

select index from index1;

参数	说明
Table	创建索引的表名
Non_uniqu	索引非唯一索引吗?， 1 表示非唯一索引,0表示唯一索引
Key_name	索引的名称，主键的话，是 PRIMARY
Seq_in_index	列在索引中的位置，如果是单列索引，那么是1，组合索引，为每个字段在索引中定义的顺序
Column_name	定义索引的列字段
Collation	何种顺序存储在索引中。 A 表示升序 (默认，ASC), null 表示无分类
Cardinality	索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大
Sub_part	索引的长度。字符串类型时，展示长度，不是字符串类型，为null
Packed	指示关键字如何被压缩。若没有被压缩，值为 NULL
Null	用于显示索引列中是否包含 NULL。若列含有 NULL，该列的值为 YES。若没有，则该列的值为 NO
Index_type	索引的类型，有BTREE 和 HASH两种
Comment	评注，注释
Index_comment	索引的评注

二.一.六创建唯一索引

创建一个 index2表，在 name 上创建唯一索引，名称为 index2_name

// 创建表
  create table index2(
  id int(11) primary key,
  name varchar(20),
  age int(3),
  unique index index2_name(name(20))
  );
  // 展示表信息
  show create table index2 \G

插入重复数据，验证一下唯一索引起作用了

insert into index2 values(1,'两个蝴蝶飞',24);
select * from index2;
insert into index2 values(2,'两个蝴蝶飞',25);

唯一索引，起作用了。

二.二在已经存在的表上创建索引

二.二.一 alter table 表名创建索引

命令:

alter table 表名  add [空|UNIQUE|FULLTEXT\SPATIAL] index [index_name] (col_name (length)) [ASC|DESC]

最开始的 noIndex 表是没有索引的。

操作1: 给这个表的 age 字段添加普通索引

alter table noIndex add index noIndex_age (age) ;

操作2: 给这个表的 name 字段添加唯一索引

alter table noIndex add index noIndex_name (name(20)) ;

查看一下，当前的索引

show create table noIndex \G

添加索引成功。

二.二.二 create index index_name on 表名 (列名) 创建索引

命令:

create [空|unique|fulltext|spatial] index index_name
  on table_name (col_name(length)) [ASC|DESC]

操作1: 给 index2 表的 age 创建普通索引

create index index2_age
  on index2 (age);

操作2：给 index1 表的 age 创建唯一索引

create  unique index index1_age
  on index1 (age);

MySQL的索引(二十三)上

一. 索引

一.一索引的产生

一.二索引的实际效果

演示一:

演示二:

一.三索引的优缺点

一.四索引的分类

一.四.一普通索引和唯一索引

一.四.二单列索引和组合索引

一.四.三全文索引

一.四.四空间索引

一.五索引的设计原则

二. 创建索引

二.一在创建表的同时，创建索引

二.一.一创建命令

二.一.二普通表的创建，用于比较区别

二.一.三创建普通索引

二.一.四查看索引是否正在使用

二.一.五 show index from 表名查看索引信息

二.一.六创建唯一索引

二.二在已经存在的表上创建索引

二.二.一 alter table 表名创建索引

二.二.二 create index index_name on 表名 (列名) 创建索引

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

MySQL的索引(二十三)上

一. 索引

一.一 索引的产生

一.二 索引的实际效果

演示一:

演示二:

一.三 索引的优缺点

一.四 索引的分类

一.四.一 普通索引和唯一索引

一.四.二 单列索引和组合索引

一.四.三 全文索引

一.四.四 空间索引

一.五 索引的设计原则

二. 创建索引

二.一 在创建表的同时，创建索引

二.一.一 创建命令

二.一.二 普通表的创建，用于比较区别

二.一.三 创建普通索引

二.一.四 查看索引是否正在使用

二.一.五 show index from 表名 查看索引信息

二.一.六 创建唯一索引

二.二 在已经存在的表上创建索引

二.二.一 alter table 表名 创建索引

二.二.二 create index index_name on 表名 (列名) 创建索引

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

一.一索引的产生

一.二索引的实际效果

一.三索引的优缺点

一.四索引的分类

一.四.一普通索引和唯一索引

一.四.二单列索引和组合索引

一.四.三全文索引

一.四.四空间索引

一.五索引的设计原则

二.一在创建表的同时，创建索引

二.一.一创建命令

二.一.二普通表的创建，用于比较区别

二.一.三创建普通索引

二.一.四查看索引是否正在使用

二.一.五 show index from 表名查看索引信息

二.一.六创建唯一索引

二.二在已经存在的表上创建索引

二.二.一 alter table 表名创建索引