当前位置: 澳门新濠3559 > 数据库 > 正文

什么是索引,由表中的一个字段或多个字段生成

时间:2019-11-09 00:17来源:数据库
mysql学习笔记之三(索引) 索引 数据库对象索引:一种组合数据的方式,通过索引对象,可以快速查询到数据库对象表中的特定记录,是一种提高性能的最常用方式。 一个索引会包含

mysql学习笔记之三(索引)

索引

数据库对象索引:一种组合数据的方式,通过索引对象,可以快速查询到数据库对象表中的特定记录,是一种提高性能的最常用方式。

一个索引会包含表中的按照一定顺序排序的一列或多列字段。

索引操作:

创建索引,修改索引,删除索引。

数据库对象索引主要为了提高从表中检索数据的速度。由于数据存储在数据库表中,所以索引是创建在数据库对象上的,由表中的一个字段或多个字段生成的键组成,这些键存储在数据结构里(B-树或哈希表)中,通过MySql可以快速有效的查找与键值相关联的字段。根据索引的存储类型,可以将所以分为B型树索引和哈希索引

注意:InnoDB和MyISAM存储引擎支持Btree类型索引,MEMORY存储引擎支持Hash类型索引,默认为前者索引。

数据库对象索引的出现,除了提供数据库管理系统的查找速度,而且还可以保证字段的唯一性。从而实现数据库表的唯一性。

六种索引:普通索引,唯一索引,全文索引,单列索引,多列索引,空间索引

利弊:

利:提高查找速度

弊:过多索引占用磁盘空间

适合创建索引的情况:

1、经常被查询的字段,即在where中出现的字段

2、分组的字段,即在group by中出现的字段

3、存在依赖关系的字表和父表之间的联合查询,即主键或外键字段

4、设置唯一完整性的字段

不适合创建的情况:

1、在查询中很少被使用的字段

2、拥有许多重复值的字段 普通索引

创建:

三种方式:

1、创建表时创建索引

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

3、通过SQL语句在ALTER TABLE来创建

1、

create table tablename(
column1 type,
column2 type,
....
INDEX|KEY [indexname] (columname [(长度)] [ASC|DESC])
)

 

通过index或key来指定字段为索引。
在创建索引时,可以指定索引的长度。这是因为不同存储引擎定义了标的最大索引数和最大索引长度
mysql所支持的存储引擎对每一个表至少支持16个索引,总索引长度至少为256字节

create table t_index1(
id int,
name varchar(20),
loc varchar(20),
index index_id(id)
)

 

之后向表里插入多条记录(这是必须的,如果只有一条记录,出现不了下面的字样),然后执行

explain select * from t_index1 where id=1G

之后出现

possible key:index_id

key:index_id

的字样之后就说明id的索引对象被启用

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

create index indexname on tablename(column[(长度)][asc|desc])

create index index_name on t_index1(name)
验证:

explain select * from t_index1 where id=1G

字样出现

possible key:index_name

key:index_name

3、通过SQL语句alter table 创建普通索引

alter table tablename add index|key indexname(columnname[(length)][asc|desc])又见识到create和alter的两个功能。修改索引会不会用alter呢?删除索引会不会用drop呢?往后看看

唯一索引

1、创建表时创建唯一索引

create table tablename(
column1 type,
column2 type,
...
unique index|key indexname(column[(length)][asc|desc])
)

 

所谓的唯一索引就是在普通索引上添加上关键字UNIQUE。

2、在已经存在的表上创建一个唯一索引
试着自己写写

create unique index indexname on tablename(columname[(长度)][asc|desc])

完全OK3、通过SQL语句alter table创建索引
alter table tablename add unique index|key indexname(columnname[(length)][asc|desc])

全文索引

全文索引主要关联在数据类型为char,varchar,text字段,以便能够更加快速查询数据量较大的字符串类型的字段
mysql从3.23.23版本开始支持全文索引,只能在存储类型为myISAM的数据库表上创建全文索引。
默认情况下,全文索引的搜索方式为不区分大小写,如果全文索引所关联的字段为二进制数据类型,则以区分大小写的搜索方式执行。
关键字:fulltext index
三种创建方式跟上面的类似,将index关键字写成fulltext index就是创建了全文索引

多列索引

在创建索引时,所关联的字段不是一个字段,而是多个字段
虽然可以通过所关联的字段进行查询,但是只有查询条件使用了所关联字段中的第一个字段,多列索引才会被使用。

create table t_name(
column1 type,
column2 type,
...
index|key index_columni_columnj(columni[(length)][asc|desc],columnj[(length)][asc|desc])

 

)其他两种创建方式类推一下就可以了。
六种索引:普通索引,唯一索引,全文索引,单列索引,多列索引,空间索引
一共是六种,这里讲到了三种。

网上搜索到的空间索引

空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列,必须

将其声明为NOT NULL,空间索引只能在存储引擎为MYISAM的表中创建

create table index6(
id int,
space geometry not null,
spatial index index6_sp(space)
);

 

以上都是创建索引,有创建就得有删除

删除索引

drop index indexname on tablename语法很简单。

索引 数据库对象索引:一种组合数据的方式,通过索引对象,可以快速查询到数据库对象表中的特定记录,是一...

索引与触发器,索引触发器

1》索引的含义和特点:

    索引是什么,索引相当于字典里面的目录序表,比如查询一个“星”字,如果不按照拼音来找的话,那么我们需要把整个字典全部遍历查询一边。才能查到这个字,        如果按照拼音来找的,那么只需要在几页音序表中查询。就可以通过音序就快速查到,这个字在字典的哪一页。在数据库中,索引是建立在表上面的,索引可以很大程
     度上提高数据库的查询,同时也提高了数据库的性能,不同的存储引擎定义了索引的最大长度和索引的数量,所有的存储引擎对每个表最少支持16个索引,索引的长度      最少支持位256字节;

  索引优点:
    其优点可以提高数据的检索速度,针对于有依赖关系的子表和父表,在联合查询的时候可以提高查询速度。
  索引的缺点:
    创建和维护索引需要消耗时间,索引需要占用物理空间,每一个索引都需要占用一定的物理空间,大量的索引会影响插入数据,数据库系统会按照索引进行排                   序,这样降低了插入数据的速度;
  解决办法:在插入数据时,先临时删除表的索引,然后插入数据,数据插入完成后,再创建索引。

***2》索引的分类:
  Mysql的索引类型有:普通索引,唯一性索引,全文索引,单列索引、多列索引和空间索引等;

  1>普通索引
    创建普通索引时,不附加任何限制条件,,这类索引可以创建在任何的数据类型上面,
  2>唯一性索引
     使用unique参数可以设置唯一索引,在创建唯一索引时,限制该索引的值必须是唯一的。比如在student表中,user_name 字段设置为唯一索引的话,            那么此值必须是唯一的。
  3>全文索引
     使用fulltext参数可以设置为全文索引,全文索引只能创建char varchar或者Text类型的字段上。只有MyISAM的存储引擎才支持此索引。Mysql5.6                       innodb开始支持全文索引
  4>单列索引
    在表中的单个字段上创建索引,单列索引只根据该字段进行索引。单列索引可以是、普通索引、也可以是唯一索引,还可以是全文索引。只要保证该索               引只对应一个字段即可。***

*       5>多列索引
    多列索引是在表的多个字段上创建一个索引,该索引指向创建时对应的多个字段。可以通过这几个字段进行查询。但是使用了多列索引,只有查询这些         字段中的第一个字段时才会被使用索引。比如:在表中id、name和sex字段上建立一个多列索引,那么,只有查询条件使用了id 字段时多列索引才会被使           用;
    6>空间索引
    使用spatial参数可以设置为空间索引, 空间索引只能建立在空间数据类型上,目前只有使用MyISAM存储引擎才支持空间索引。而且此索引的字段值不            能为空。
  练习:查询一下是否有其它类型的索引,
      Hash 索引主键索引 B-tree索引*

*3》如何设计索引:
  为了让索引使用效率更高,在创建索引时,必须考虑在那些字段上创建索引和创建什么类型的索引;
  1>唯一索引的设置:
    唯一索引的值是唯一的,可以更快速的通过该索引可以确定某条记录;
    比如:身份证号码是唯一的,可以建立唯一索引,如果是名字的话,那么有可能出现同名的状况,从而减低查询速度。
  2>为经常需要排序、分组和联合操作的字段建立索引:
    经常需要order by group by distinct和union等操作的字段,排序操作会浪费很多时间,如果为这些字段建立索引,可以有效地的避免排序操作;
  3>为常作为查询条件的字段建立索引:
    如果某一个字段常用需要来查询条件,那么该字段的查询速度影响这个张表的速度,因此为这样的字段建立索引,可以提高整张表的查询速度;
  4>限制索引的数目:
    索引的数目不是越多越好,每个索引都需要占用磁盘空间。索引越多,需要的磁盘空间就越大,修改表时,所索引的重构和更新麻烦,越多的索引更新          表就变得很浪费时间;
  5>尽量使用数据量少的索引:
    如果索引的值很长,那么查询的速度会受到影响,比如对一个Char(100)类型的字段进行全文索引需要的时间肯定要比char(10)类型的字段需要的时间更          多;
  6>删除不再使用和很少使用的索引:
    表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不在需要,DBA应该定期的找出这些索引,将它们删除,从而减少索引对           更新操作的影响;*

*4》*如何创建索引**

*  语法:
    [unique|fulltext|spatial] index |key
    [别名] (属性名1 [(长度)] [ASC|DESC] )
    unique可选参数,代表唯一索引
    fulltext 可选参数,代表全文索引
    spatial 可选参数,代表空间索引
    index 和key 用来指定字段为索引两者选择其一。
    别名可选参数,给创建的索引取新的名称。
    长度可选参数,给索引执定长度,必须是字符类型的才可以指定长度。
    ASC升序,DESC降序。*

**              1>创建普通索引
      Mysql->create table aatest(
          id int,
          name varchar(20),
          sexboolean,
          index(id));
      使用 index设置id为普通索引。
     Mysql> show create table aatestG; 查看一下表详细结构
澳门新濠3559,       Mysql>explain select * from aatest where id=1 G; 查看索引是否被使用。**

**                 2>建立唯一索
       唯一索引使用unique进行约束
      create table aatest2(
             id int unique,
               name varchar(20),
             unique index aatest_id(id ASC));**

**     3>创建全文索引
       create table aatest3(
             id int,
             info varchar(20),
               fulltext index aatest3_info(info));
       *******5.6版本已支持全文索引**

**     4>创建单列索引
       create table aatest4(
             id int,
              subject varchar(30),
             index aatest4_st(subject(10)));subject(10)指定索引的长度**

**               5>创建多列索引
      多列索引,是在表上多个字段创建一个索引。
        create table aatest5(
              id int,
                name varchar(20),
              sex char(4),
              index aatest5_ns(name,sex));**

**5》在已经有的表上建立索引:
  语法:
    create [unique | fulltext | spatial ] index 索引名
    on 表名 (属性名 [(长度)] [ ASC | DESC]);

    alter table 表名 ADD [unique | fulltext | spatial ] index 索引名
    (属性名 [(长度)] [ ASC | DESC]);

    1>创建普通索引
      create index zytest_id on zytest(id);
      alter table zytest add index zytest_id(id);

    2>创建唯一索引
      create unique index zytest1_id on zytest1(id);
      alter table zytest1 add unique index zytest1_id(id);

    3>创建全文索引
      create fulltext index zytest2_id on zytest2(info);
      alter table zytest2 add fulltext zytest_2(info);**

**              4>创建单列索引
      create index zytest3_addr on zytest3(address(4));
      alter table zytest3 add index zytest3_addr(address(4));

    5>创建多列索引
      create index zytest4_na on zytest4(name,address);
      alter table zytest4 add index zytest4_na(name,address);**6》如何删除索引:**

**  如果没有别名,+索引名称
  语法:drop index 索引名 ON 表名
       drop indexid on zytest;

  如果有别名的话。直接+索引别名
  语法:drop index 索引别名 ON 表名

================触发器:

1》**触发器的含义与作用****

**    触发器(trigger)是由事件来触发某个操作,主要是由insert update delete等事件来触发某种特定的条件,满足触发器的触发条件时,数据库就会执行触     发器定义的程序语句,比如:当学生表当中增加了一个学生记录,学生的总数就必须同时改变。可以在这里创建一个触发器,每次增加一个学生的记录。
   就执行一次计算学生的总数量的操作。这可以保证每次增加学生后的记录统计一直保持最新;触发器触发的执行语句可以只有一个。也可能有多个;


*         语法:
    create trigger 触发器名称 before|after 触发事件
    on 表名 for each row 执行语句
    berfore指触发事件之前执行的触发语句。
    After 表示在触发事件之后执行语句
    触发事件包括(insert update delete)等
    on表名在XXX表之上
    执行语句指的是XXSQL语句和触发事件类型要对应

  A 触发器 B存放A总记录,
  当A表删除一条数据之后--->触发器将统计的最终结果写入到B表当中,用户每次想要得到A表的结果,只需要去B表当中查询就行了。
  select count(*) from A >B表当中。*

**2》**创建触发器

**  1>创建一个表alvin
    create table alvin(
        userid int(10),
        username varchar(20),
        old int(4),
        address varchar(30));

  2>创建一个表为trigger_time用来存放触发后条件的结果
     create table trigger_time(
          zhixing_time time);
     Query OK, 0 rows affected (0.15 sec)

  3>创建只有单个执行语句的触发器
     create trigger alvin1 before insert
          on alvin for each row
      insert into trigger_time values(now());
      Query OK, 0 rows affected (0.07 sec)       **

**        4>创建有多个执行语句的触发器

  举例一、
      [email protected] 10:49>delimiter &&#告诉MYSQL该命令段下面的内容在提示结束后再执行分析。默认是以分号(;)执行
      [email protected] 10:53>create trigger alvin3 after delete
         ->on alvin for each row
           -> begin
         ->insert into trigger_time values('21:01:01');
         ->insert into trigger_time values('22:01:01');
          ->end    
         ->&&
     Query OK, 0 rows affected (0.05 sec)**

**             [email protected] 10:54>delimiter;#结束退出,注意分号要有空格

    [email protected] 10:57>select * from alvin;
    +--------+-------------+------+----------+
    | userid | username | old | address |
    +--------+-------------+------+----------+
    | 110 | zengxiaohua | 28 | tianxing |
    +--------+-------------+------+----------+
    1 row in set (0.00 sec)**

**             [email protected] 11:07>delete from alvin where userid='110';#执行删除动作看看触发器是否成功
    Query OK, 1 row affected (0.05 sec)

    [email protected] 11:07>select * from trigger_time;#:查看触发器的执行结果
      +--------------+
      | zhixing_time |
      +--------------+
      | 19:09:41 |
      | 21:01:01 |
      | 22:01:01 |
      +--------------+
    3 rows in set (0.00 sec)**

**     举例二、
    alvin1表存放了学生的信息。每次增加(insert)一个学生的信息。就触发一次统计。统计结果存入aac表里面;
    首先创建一个alvin1表结构
      create table alvin1(
          user_id int(10),
          username varchar(20),
          old tinyint(4),
          address varchar(30));

      create table aac(
          my_count int);
    然后开始创建一个触发器
      delimiter&&
      create trigger alvin123 before insert on
      alvin1 for each row begin
      declare ycount int(10);#:申明变量类型
      set ycount=(select count(*) from alvin1);#:给变量赋值
      insert into aac(my_count) values(ycount);#:调用变量
      end&&
      delimiter ;   **

**  看看before和after的区别
    create trigger alvin123 after insert on
      zyalvin1 for each row
      begin
      declare ycount int(10);
      set ycount=(select count(*) from zyalvin1);
      insert into aac(my_count)values(ycount);
      end&&

    [email protected] 16:24>insert into alvin1 values('1001','zhangsan','18','China');开始测试
    [email protected] 16:24>select * from aac;查看触发器统计的结果。**

**3》***查看触发器***

*  1> 查看所有触发器,提前要进入某库
    #: show triggers G;

  2>在triggers表中查看触发信息
    [email protected] 11:20>use information_schema;
    [email protected] 11:19>select * from information_schema.triggers G;
  小技巧:所有触发器的信息都存在information_schema库中的triggers表里面,在使用select 查询单个触发器的时候。可以根据triggers表里面的字段名称
      Trigger_name字段进行查询。
    [email protected]_schema 11:24>select * from triggers where trigger_name='alvin1'G;*

*  4》**删除触发器*****

**  语法:
  1>删除alvin1触发器
    [email protected](none) 12:18>use zytest;
      Database changed
    [email protected] 12:18>drop trigger alvin1;
    Query OK, 0 rows affected (0.03 sec)**

 

1》索引的含义和特点: 索引是什么,索引相当于字典里面的目录序表,比如查询一个“星”字,如果不按照拼...

本文内容:

什么是索引

创建索引

  • 普通索引
  • 唯一索引
  • 全文索引
  • 单列索引
  • 多列索引

查看索引

删除索引

 

首发日期:2018-04-14

 


什么是索引:

 

  • 索引可以帮助快速查找数据
  • 基本上索引都要求唯一(有些不是),所以某种程度上也约束了数据的唯一性。
  • 索引创建在数据表对象上,由一个或多个字段组成,这若干个字段组成“键”存储到数据结构中(B树或者哈希表)。【可以根据数据结构分类成B树索引(innodbmyisam引擎),哈希索引(memory引擎)】
  • 由于索引是加快数据查找的,所以一般都使用在经常需要用来查找的字段上(比如where中常用字段)
  • mysql支持的索引类型有:普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引。

 

补充:

  • 主键与索引的区别:主键也是一种索引,主键也是一种唯一索引,但索引的作用主要是提高查找速度,而主键作用主要是标识记录唯一性(当然也便于查找)。

创建索引:

 

普通索引:index

普通索引就是没有“唯一”,“不为空”要求的索引。

语法:

create table 表名(字段 数据类型,字段 数据类型,…index|key [索引名] (字段  [索引的长度] [asc|desc]));

  • index|key代表可以使用index关键字或key关键字
  • 索引名是可选的,不填时索引名为字段名
  • 字段是加索引的字段,字段可以有多个(多列索引)。
  • 索引的长度,可选的(有些引擎设置了最大长度,一般不深入学习应该很少遇到,这里不讲述)
  • asc|desc是可选的,代表索引中的“关键字”的排序

在现有表结构的情况下增加索引:create index 索引名 on 表名 (字段  [索引的长度] [asc|desc]);

在现有表结构的情况下增加索引:alter table 表名 add index|key 索引名(字段  [索引的长度] [asc|desc]);

 

 

唯一索引:

唯一索引,就是创建索引时,要求字段的数据是唯一的。

创建语法:

create table 表名(字段 数据类型,字段 数据类型,… unique index|key [索引名] (字段  [索引的长度] [asc|desc]));

  • 【参数信息参考普通索引的】

在现有表结构的情况下增加索引:create unique index 索引名 on 表名 (字段  [索引的长度] [asc|desc]);

在现有表结构的情况下增加索引:alter table 表名 add unqiue index|key 索引名(字段  [索引的长度] [asc|desc]);

 

 

全文索引:fulltext index

全文索引一般使用在数据类型为char,varchar和text的字段上,方便查找这些比较长的数据。

myisam存储引擎支持全文索引

创建语法:

create table 表名(字段 数据类型,字段 数据类型,… fulltext index|key [索引名] (字段  [索引的长度] [asc|desc]));

  • 【参数信息参考普通索引的】

在现有表结构的情况下增加索引:create fulltext index 索引名 on 表名 (字段  [索引的长度] [asc|desc]);

在现有表结构的情况下增加索引:alter table 表名 add fulltext index|key 索引名(字段  [索引的长度] [asc|desc]);

 

单列索引:

  • 上面的加索引字段为一个时,为单列索引

 

多列索引:

  • 加索引的字段为多个时,为多列索引
  • 创建语法:只需要将上面的(字段  [索引的长度] [asc|desc])变成(字段1  [索引的长度] [asc|desc],字段2  [索引的长度] [asc|desc],字段1  [索引的长度] [asc|desc])即可。

 

 


查看索引:

 

可以通过查看表结构查看索引

  • 澳门新濠3559 1

可以通过查看表创建语句查看索引

  • 澳门新濠3559 2

可以使用explain语句查看索引,explain是用来判断mysql语句执行的时间效率的。

  • 所以可以使用explain select语句 where 加上索引的字段作为条件;
  • 结果中的possible_key是可能使用到的索引,key实际使用到的索引。
  • 比如:

    explain select * from user_info where username="lilei";
    

    澳门新濠3559 3

 


删除索引:

 

drop index 索引名 on 表名;

  • 比如:
    drop index myindex on user_info;
    

 


编辑:数据库 本文来源:什么是索引,由表中的一个字段或多个字段生成

关键词: