表索引视图触发器表的索引

转载

attitude 2024-04-23 21:16:39

文章标签 表索引视图触发器数据库学习 mysql 字段 文章分类 数据仓库大数据

文章目录

前言
一、索引概述

1.索引类型
2.索引存储
3.索引优点
4.索引缺点
5.使用建议

二、创建和查看索引

1.普通索引

1.创建表时定义索引
2.已存在的表上创建索引
3.查看索引执行情况

2.唯一索引

1.创建表时定义索引
2.已存在的表上创建索引

3.全文索引

1.创建表时定义索引
2.已存在的表上创建索引
3.使用场景
4.MySQL8中文分词支持

4.多列索引

1.创建表时定义索引
2.已存在的表上创建索引

5.隐藏索引
6.删除索引

三、索引的设计原则
总结

前言

日常生活中，我们经常会在电话号码簿中查阅“某人”的电话号码，按姓查询或者按字母排序查询；在字典中查阅“某个词”的读音和含义等等，以快速的找到特定记录。在这里，“姓”和“字母”都可看作是索引，而按“姓”或者“字母”查询则是按索引查询！

索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录，是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。

通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列，否则数据库系统将读取每条记录的所有信息进行匹配。例如，索引相当于新华字典的音序表，如果要查“过”字，如果不适用音序，就需要从字典的第一页开始翻几百页；如果提取拼音出来，构成音序表，就只需要从10多页的音序表中直接查找，这样就可以大大节省时间。因此，使用索引可以在很大程度上提高数据库的查询速度，有效地提高了数据库系统的性能。

一、索引概述

1.索引类型

索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

2.索引存储

数据库底层索引实现主要有两种存储类型，B树(BTREE)和哈希(HASH)索引，InnoDB和MyISAM 使用BTREE索引；而MEMORY 存储引擎可以使用BTREE 和HASH 索引，默认用BTREE.在没有指定的情况下，数据库使用的引擎是 InnoDB。

3.索引优点

可以提高检索数据的速度。

4.索引缺点

创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态地维护索引，造成数据的维护速度降低了。

5.使用建议

索引可以提高查询的速度，但是会影响插入记录的速度，因为向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。

二、创建和查看索引

创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。创建索引有3种方式，分别是创建表的时候创建索引、在已经存在的表上创建索引和使用ALTER TABLE语句来创建索引。本节将根据具体的索引分类详细的讲解这3种创建方法。

1.普通索引

所谓普通索引，就是在创建索引时，不附加任何限制条件（唯一、非空等限制）。该类型的索引可以创建在任何数据类型的字段上。

1.创建表时定义索引

CREATE TABLE tablename(
    propname1 type1,
    propname2 type2,
    ……
    propnamen type..n,
    INDEX | KEY
    [indexname] (propnamen [(length)] [ ASC | DESC ] ) );

其中，参数INDEX和KEY是用来指定字段为索引的，两者选择其中之一就可以了，作用是一样的；参数indexname是索引名字，可省略；参数propnamen是索引对应的字段的名称，该字段必须为前面定义好的字段；参数length是可选参数，其指索引的长度，必须是字符串类型才可以使用；参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列，如果不指定，则为升序。
举例：

#创建一个class表，对表的id列创建降序索引(DESC)
create table class(id int,name varchar(64),teacher varchar(64),index(id DESC));

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

方法一: 执行CREATE语句

CREATE INDEX indexname ON tablename (propname [(length)] [ASC|DESC]);

参数INDEX是用来指定字段为索引，此处不能为KEY；参数indexname是新创建的索引的名字；参数tablename是指需要创建索引的表的名称，该表必须是已经存在的，如果不存在，需要先创建；参数propname指定索引对应的字段的名称，该字段必须为前面定义好的字段；参数length是可选参数，表示索引的长度，必须是字符串类型才可以使用；参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列,默认升序。
举例：

#先创建class1表，然后在id列创建降序索引
create table class1(id int,name varchar(64),teacher varchar(64);
create index index_id on class1(id DESC);

方法二: 执行ALTER TABLE语句

ALTER TABLE tablename ADD INDEX | KEY indexname  (propname [(length)] [ASC|DESC]);

在上述语句中，参数tablename是需要创建索引的表；关键字IDNEX或KEY用来指定创建普通索引；参数indexname用来指定所创建的索引名；参数propname用来指定索引所关联的字段的名称；参数length用来指定索引的长度；参数ASC用来指定升序排序；参数DESC用来指定降序排序。
举例：

#id列创建升序索引
alter table class1 add index index_id(id ASC);

3.查看索引执行情况

查看索引执行情况

EXPLAIN  查询语句

举例：

explain select * from class1 where id>0;

表索引视图触发器表的索引_表索引视图触发器

输出结果：

key：实际使用的索引。如果为NULL，则没有使用索引

possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。此值越短越好！

2.唯一索引

所谓唯一索引，就是在创建索引时，限制索引的字段值必须是唯一的。通过该类型的索引可以比普通索引更快速地查询某条记录。

1.创建表时定义索引

CREATE TABLE tablename(
    propname1 type1,
    ……
    propnamen type..n,
    UNIQUE INDEX | KEY [indexname] (propnamen [(length)] [ ASC | DESC ] ) );

注意：参数UNIQUE INDEX和UNIQUE KEY是用来指定字段为索引的，两者选择其中之一即可；参数indexname是索引名字，可省略；参数propnamen是索引对应的字段的名称，该字段必须为前面定义好的字段且必须定义为 UNIQUE 约束；参数length是可选参数，其指索引的长度，必须是字符串类型才可以使用；参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列，如果不指定，则为升序。
举例：

#name列创建唯一索引，并设为降序
create table class2(id int,name varchar(64) unique,teacher varchar(64),unique index index_name(name DESC));

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

方法一:

CREATE UNIQUE INDEX indexname ON tablename (propname [(length)] [ASC|DESC]);

举例：

create table class2(id int,name varchar(64) unique,teacher varchar(64));
create unique index index_name on class2(name DESC);

方法二:

ALTER TABLE tablename ADD UNIQUE INDEX | KEY indexname (propname [(length)] [ASC|DESC]);

举例：

alter table class2 add unique index index_name(name DESC);

3.全文索引

全文索引主要对字符串类型建立基于分词的索引，主要是基于CHAR、VARCHAR和TEXT的字段上，以便能够更加快速地查询数据量较大的字符串类型的字段。

全文索引以词为基础的，MySQL默认的分词是所有非字母和数字的特殊符号都是分词符。

MySQL从3.23.23版本开始支持全文索引，MySQL5.6以前只能在存储引擎为MyISAM的数据表上创建全文索引,5.6之后InnoDB开始支持全文索引(5.7之后支持中文全文索引) 。在默认情况下，全文索引的搜索执行方式为不区分大小写，如果全文索引所关联的字段为二进制数据类型，就以区分大小写的搜索方式执行。

1.创建表时定义索引

CREATE TABLE tablename(
    propname1 type1,
    propname2 type2,
    ……
    propnamen type..n,
    FULLTEXT INDEX | KEY
    [indexname] (propnamen [(length)] ) );

举例：

create table class3(id int, name varchar(128) UNIQUE, teacher varchar(64),comment varchar(1024),FULLTEXT INDEX index_comm(comment));
insert into class3 values(1,'一班','玛卡巴卡','玛卡巴卡，拿着香皂，正在洗石头');
select * from class3 where match(comment) AGAINST('拿着香皂');	#查找关键字段

表索引视图触发器表的索引_mysql_02

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

方法一: 执行create 语句
在MySQL中创建全文索引除了通过SQL语句FULLTEXT INDEX来实现外，还可以通过SQL语句CREATE FULLTEXT INDEX来实现，其语法形式如下：

CREATE FULLTEXT INDEX index_name ON tablename( propname1 [ ( length ) ] );

在上述语句中，关键字CREATE FULLTEXT INDEX表示用来创建全文索引。
举例：

create table class4(id int, name varchar(128) UNIQUE, teacher varchar(64),comment varchar(1024));
create fulltext index index_comm on class4(comment);

方法二: 执行ALTER TABLE 语句
除了上述两种方式来创建全文索引外，在MySQL中创建全文索引还可以通过SQL语句ALTER来实现，其语法形式如下：

ALTER TABLE tablename ADD FULLTEXT INDEX|KEY indexname(propname [(length)]);

举例：

alter table class4 add fulltext index index_comm(comment);

3.使用场景

根据全文索引字段进行全文检索数据：

SELECT * FROM 表名 WHERE MATCH(`列名`) AGAINST('关键字');

举例：

insert into class4 values(1,'一班','玛卡巴卡','Cat Dog');
select * from class4 where match(comment) AGAINST('Cat');
select * from class4 where match(comment) AGAINST('CAT');
select * from class4 where match(comment) AGAINST('cAT');

表索引视图触发器表的索引_表索引视图触发器_03

从这个例子也可以看出，数据库确实是不区分大小写的。

4.MySQL8中文分词支持

配置文件my.ini（Windows 10默认路径: C:\ProgramData\MySQL\MySQL Server 8.0）中增加如下配置项，同时重启MySQL80 服务:

ngram_token_size=2

效果如图:

表索引视图触发器表的索引_字段_04

之后重启MySQL，全文索引字段就会变得十分便捷。

举例：

create table class5(id int, name varchar(128) unique, teacher varchar(64),comment varchar(64),fulltext index index_des(comment) with parser ngram);
insert into class5 values(1,'一班','玛卡巴卡','玛卡巴卡，拿着香皂，正在洗石头');
select * from class5 where match(comment) against('拿着');
select * from class5 where match(comment) against('着香');
select * from class5 where match(comment) against('在洗');
select * from class5 where match(comment) against('石头');
select * from class5 where match(comment) against('洗石头');

表索引视图触发器表的索引_mysql_05

4.多列索引

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

1.创建表时定义索引

CREATE TABLE tablename(
    propname1 type1,
    ... ...
    propnamen type..n,
    INDEX | KEY [indexname] (propname1 [(length)] [ ASC | DESC ],
                             Propname2 [(length)] [ ASC | DESC ], 
                              ... ...                               
                             Propnamen [(length)] [ ASC | DESC ]) 
);

注意：和普通索引定义基本相同，不同之处就是增加了多个索引列。
举例：

create table class6(id int, name varchar(128) UNIQUE, teacher varchar(64), index index_mult_columns(id, teacher));

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

方法一: 执行create 语句

CREATE  INDEX indexname
    ON tablename( propname1 [(length)] [ ASC | DESC ],  
                   Propname2 [(length)] [ ASC | DESC ],   
                              ... ...                                
                         Propnamen [(length)] [ ASC | DESC ]  );

举例：

create table class6(id int, name varchar(128) UNIQUE, teacher varchar(64));
create  index index_id on class6(id, name);

方法二: 执行ALTER TABLE 语句
除了上述两种方式来创建全文索引外，在MySQL中创建全文索引还可以通过SQL语句ALTER来实现，其语法形式如下：

ALTER TABLE tablename
    ADD INDEX|KEY indexname(propname1 [(length)] [ ASC | DESC ],  
                   Propname2 [(length)] [ ASC | DESC ],              
                              ... ...                                          
                   Propnamen [(length)] [ ASC | DESC ]  );

举例：

alter table class6 add index index_muti_columns(id,teacher);

5.隐藏索引

MySQL 8开始支持隐藏索引。隐藏索引提供了更人性化的数据库操作。
隐藏索引，顾名思义，让索引暂时不可见，不会被优化器使用。默认情况下索引是可见的。隐藏索引可以用来测试索引的性能。验证索引的必要性时不需要删除索引，可以先将索引隐藏，如果优化器性能无影响就可以真正地删除索引。

ALTER TABLE tablename ALTER  INDEX  index_name INVISIBLE;  #隐藏索引
ALTER TABLE tablename ALTER  INDEX  index_name VISIBLE;    #取消隐藏

6.删除索引

所谓删除索引，就是删除表中已经创建的索引。之所以要删除索引，是因为这些索引会降低表的更新速度，影响数据库的性能。
在MySQL中删除索引通过SQL语句DROP INDEX来实现，其语法形式如下：

DROP INDEX indexname ON tablename;

若要对索引进行修改，可以先删除索引，再添加新的索引以达到修改目的。

三、索引的设计原则

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。本节将介绍一些索引的设计原则。

选择唯一性索引
唯一性索引的值是唯一的，可以更快速地通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段，为该字段建立唯一性索引可以很快确定某个学生的信息，如果使用姓名的话，可能存在同名现象，从而降低查询速度。
为经常需要排序、分组和联合操作的字段建立索引
经常需要使用ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间，如果为其建立索引，可以有效地避免排序操作。
为经常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度，为这样的字段建立索引可以提高整个表的查询速度。
限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大，修改表时，对索引的重构和更新很麻烦。
尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间多。
尽量使用前缀来索引
如果索引的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间，如果只检索字段前面的若干字符，这样可以提高检索速度。
删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

总结

表的索引讲到这里，就接近尾声了（应该完全够用了），学习笔记只起到参考作用，难免会出错。如有误处，还请大神指出！

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：maven 查找包冲突 maven仓库有包但提示找不到

下一篇：分段回归R2 分段回归法

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯