MySQL的索引(二十三)

勿以恶小而为之，勿以善小而不为--------------------------刘备

上一章简单介绍了 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.

优点:

1. 大大加快数据的查询速度 ，是最主要的原因
2. 通过创建唯一索引，可以保证每一行数据的唯一性。
3. 对于外键的字段，添加索引，可以加速表和表之间的连接。
4. 对于分组和排序的字段添加索引，也可以减少分组和排序的时间.

缺点:

1. 创建索引和维护索引要花费时间，并且随着数据量的增加，花费的时间也增加。
2. 索引需要占磁盘空间，如果有大量的索引，可以索引文件与数据文件还大。
3. 当对表数据进行维护(插入，更新，删除)时，索引也要动态的维护，降低了数据的维护速度。

一.四 索引的分类

索引可以分为以下几类

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

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

唯一索引，索引值必须唯一，但允许是空值。类似于 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也支持)

一.五 索引的设计原则

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

1. 索引并非越多越好
2. 数据量小的表最好不使用索引
3. 查询中很少使用的列或者参考的列，不使用索引
4. 只有很少数据值的列，不应该使用索引。 如 性别列，学历列。
5. 对于经常更新的表，避免使用索引， 对于经常查询的列，使用索引。

二. 创建索引

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

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

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

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

二.一.一 创建命令

命令:

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;

二.一.六 创建唯一索引

创建一个 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);

三. 删除索引

与 创建索引的后两种方式对应，删除索引，也有相对应的方式 .

三.一 alter table 表名 drop index 索引名

注意，这个 index 是单独的index, 表示索引的意思。

只根据 索引名称进行删除。 这也是创建索引时，建议自定义索引名的原因。

操作1: 删除 noIndex 表的 唯一索引 索引名为 noIndex_name

alter table noIndex drop index noIndex_name;

三.二 drop index 索引名 on 表名

操作1: 删除 noIndex 表的普通索引，索引名为 noIndex_age

drop index noIndex_age on noIndex;

四. 索引的补充

不常用，仅作为一个补充。

四.一 创建 单列索引

创建 index3 表，name 为单列索引

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

四.二 创建组合索引

创建 index4 表,name ,age 为组合索引

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

要注意，需要遵循 最左前缀原则。

如 单独查询 age 是不能触发索引的

possible_keys 是 null

查询 name 和 age 是可以触发的

单独查询 name 也是可以触发的。

四.三 创建 全文索引

创建表 index5, name 为全文索引

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

发现，引擎为 InnoDB ，也没有报错。

如果版本低，不支持，那么创建时，可以在创建表时，指定引擎

// 创建表
  create table index7(
    id int(11) primary key,
    name varchar(20),
    age int(3),
    fulltext index index7_name(name(20))
  ) engine=MyISAM;
  // 展示表信息
  show create table index7 \G

四.四 创建 空间索引

创建表为 index6, 数据类型必须为那四种的一种

create table index6(
  id int(11) primary key,
  name geometry not null,
  age int(3),
  spatial index index6_name(name)
  );
  show create table index6 \G

如果版本低，不支持，那么创建时，可以在创建表时，指定引擎

create table index8(
  id int(11) primary key,
  name geometry not null,
  age int(3),
  spatial index index8_name(name)
  ) engine=MyISAM;
  show create table index8 \G

索引非常重要，需要掌握。

谢谢!!!