数据库基础学习五一、索引的概念二、创建索引 1、创建表时创建索引 2、在已存在的表上创建索引三、查看索引四、修改索引五、删除索引六、索引分类六、索引优缺点

本文链接：https://blog.youkuaiyun.com/weixin_44076570/article/details/100047973

MySQL 数据库基础学习

索引（index/key）

索引概念
创建索引
查看索引
修改索引
删除索引
索引分类
索引优缺点

一、索引的概念

在MySQL中，索引（index），也叫键（key），它是存储引擎用于快速找到记录的一种数据结构。
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。
对相关列使用索引是提高查询速度的最佳途径。

二、创建索引

1、创建表时创建索引

通过约束在对应字段基础上构建索引（主键生成主键索引，特殊的唯一索引；唯一键生成唯一索引；外键生成普通索引）
使用index或key关键字在指定字段基础上可构建指定名称的索引

create  table  a(
    a  int  auto_increment  primary key,  #主键（自动生成主键索引，特殊的唯一索引）
    b  char(6)  unique,  #唯一键（自动生成唯一索引）
    c  datetime,
    d  float,
    index  cc(c),  #普通索引，在该表字段c上构建名为cc的普通索引
    key  dd(d)   #普通索引，在该表字段d上构建名为dd的普通索引
);

构建效果如下图所示：在这里插入图片描述

_{图1表结构及索引}

在这里插入图片描述

2、在已存在的表上创建索引

使用alter table语句创建索引
语法：

alter  table  表名
  add  [unique]  index  索引名(字段名[(长度)],…)  [asc|desc];

示例：

#对已存在表a的字段c、d构建组合索引ee

alter  table  a
  add  index  ee(c,d);  #组合索引

使用create index创建索引
语法：

create  [unique]  index  索引名  on  表名(字段名[(长度)],…)  [asc|desc];

示例

#对已存在表a的字段d构建普通索引ff
create  index  ff  on  a(d);

三、查看索引

可直接通过SqlYog左侧树形控件查看指定表的“索引”节点信息，如前面图1所示。
可直接通过SqlYog左侧树形控件找到指定表，点击鼠标右键，见图3所示；点击“管理索引”后即可查看如前面图2所示。
可直接使用“show index from 表名;”语句查看指定表的索引
信息。

如：执行 show index from a; 可查看到图4效果。

在这里插入图片描述

可直接使用“show keys from 表名;”语句查看指定表的索引
信息。

如：执行 show keys from a; 可查看到图5效果（结果集与图4一致）。
在这里插入图片描述
_{图5表索引信息查看}

四、修改索引

先删除指定索引，再以修改后的内容创建同名索引。

五、删除索引

使用alter table语句删除索引
语法：

drop index 索引名 on 表名;

示例

#删除表a索引ff
drop  index  ff  on  a;

注意：删除表后，对应该表的索引也会被删除。

六、索引分类

普通索引：是最基本的索引，它没有任何限制。
唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值；如果是组合索引，则列值的组合必须唯一。
主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值；一般是在建表的时候同时创建主键索引。
组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用；使用组合索引时遵循最左前缀集合。
全文索引：要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。

六、索引优缺点

优点：提高查询速度、加快排序、连接、分组
缺点：索引需要额外占用磁盘空间
- 索引是需要维护的：对表添加、修改、删除记录，都会影响到索引的维护----花费不必要的时间（降低数据的维护速度）

附：测试索引提高查询速度

第一步：构建函数以便其对应表a添加数据

注意：默认情况下创建函数无效
#查看指定变量状态
show  variables  like  '%func%';
#会发现变量log_bin_trust_function_creators值为0，其控制是否可以信任存储函数创建者
#启动函数创建
set  global  log_bin_trust_function_creators=1;

#定义函数通过循环操作向表中添加数据信息(0~100000) 
delimiter $$  #更改sql语句的结束标识
create  function  aaa()  returns  int
begin
  declare v int;
  set v=0;
  while v<=100000 do-- 循环开始
    insert into a(b,c,d) values(v,now(),v);
    set v=v+1;
  end  while;-- 循环体结束位置
  return (select count(*) from  a);-- 函数返回值
end$$
delimiter ;
#调用函数
select aaa();

#执行上边语句完成后，表中会添加相应测试数据

第二步：删除表a字段d上的索引

#删除索引 
drop  index  dd  on a;

第三步：针对用户的数据查询动作，确定其花费时间（比较有索引及无索引应用时的耗时）

set @a=NOW(3);--  变量赋值（将系统的日期赋予给一个变量）
-- select *  from a where  a=99999;
select *  from a where  d=99999;
#上边两条语句在执行时须屏蔽其中一条以测试耗时
set @b=NOW(3);
select TIMESTAMPDIFF(microsecond,@a,@b)/1000;