Mysql之索引和视图

Mysql之索引和视图

author：onceday date：2022年6月5日

1.索引概述

是一种将数据库中单列或者多列的值进行排序的结构。应用索引，可以大幅度提高查询的速度。

索引的优势在于可以不用遍历整个数据库，但是缺点在于维护索引需要占据物理空间，和消耗时间。

索引会影响数据库的插入操作，向有索引的表中插入数据时，数据库系统会按照索引进行排序。

索引一般包括B树(Btree)索引和哈希(Hash)索引。

索引分为以下几类:

• 普通索引：不应用任何限制条件的索引，该索引可以在任何数据类型中创建，字段本身的约束条件可以判断其值是否为空或唯一。用户查询时可以通过索引进行查询。
• 唯一性索引：使用UNIQUE参数可以设置唯一的索引。创建索引时，其值唯一，用户可以快速定位某条记录，主键是一种特殊唯一索引。
• 全文索引：使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR或者TEXT类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。在默认情况下，应用全文搜索大小写不敏感。如果索引的列使用二进制排序后，可执行大小写敏感的全文索引。
• 单列索引：单列索引只对应一个字段的索引，可以包括普通索引，唯一性索引，全文索引等。需要保证索引值对应一个字段即可。
• 多列索引：在表的多个字段上创建一个索引，该索引指向创建时对应的多个字段，用户可以通过这几个字段进行查询。用户必须使用这些字段中的第一个字段来应用该索引。
• 空间索引：使用SPATIAL参数可以设置索引为空间索引，空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。

2.在建立数据表时创建索引

在建立数据表时创建索引的基本语法结构如下：

create table table_name(
属性名 数据类型[约束条件],
属性名 数据类型[约束条件]
    ......
属性名 数据类型
[UNIQUE | FULLTEXT | SPATIAL] INDEX|KEY
[别名]( 属性名 1 [(长度)][ASC|DESC])
);

其中，属性名后的属性值，含义如下：

• unique:可选项，表明索引为唯一性索引。
• fulltext:可选项，表明索引为全文搜索。
• spatial:可选项，表明索引为空间。

index和key参数用于指定字段索引，只需要选择其中一种即可。

别名作为可选项，其作用是给创建的索引取新名称。

• 属性名1：指索引对应的字段名称，该字段必须被预先定义。
• 长度：可选项，指索引的长度，必须是字符串类型才可以使用。
• ASC/DESC:可选项，ASC表示升序排列，DESC参数表示降序排列。

2.1 普通索引

不需要添加unique，fulltext等任何参数。

create table score(
id int(11) auto_increment primary key not null,
name varchar(50) not null,
math int(5) not null,
english int(5) not null,
chinese int(5) not null,
index(id));

mysql> desc score;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int         | NO   | PRI | NULL    | auto_increment |
| name    | varchar(50) | NO   |     | NULL    |                |
| math    | int         | NO   |     | NULL    |                |
| english | int         | NO   |     | NULL    |                |
| chinese | int         | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

2.2 创建唯一性索引

需要使用unique参数进行约束。

create table address(
id int(11) auto_increment primary key not null,
name varchar(50),
address varchar(200),
unique index address(id asc));

mysql> show create table address;
| address | CREATE TABLE `address` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `address` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
1 row in set (0.01 sec)

唯一索引可以约束字段的唯一性，但不能提高用户查找速度。

2.3 创建全文索引

全文索引的创建只能作用在char、varchar、text类型的字段上。创建全文索引需要使用fulltext参数进行约束。

create table cards(
id int(11) auto_increment primary key not null,
name varchar(50),
number bigint(11),
info varchar(50),
fulltext key cards_info(info)) engine=MyISAM;

注意：只有MyISAM类型的数据表支持fulltext全文索引，InnoDB或其他类型的数据表不支持全文索引。

2.4 创建单列索引

创建单列索引，即在数据表的单个字段上创建索引。创建该类型索引不需要引入约束参数，用户在建立时只需要指定单列字段名。

create table telephone(
	id int(11) primary key auto_increment not null,
    name varchar(50) not null,
    tel varchar(50) not null,
    index tel_num(tel(20)));

2.5 创建多列索引

指定表的多个字段即可实现。

create table information(
	id int(11) auto_increment primary key not null,
    name varchar(50) not null,
    sex varchar(5) not null,
    birthday varchar(50) not null,
    index info(name,sex));

在多列索引中，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。如果没有用到第一字段，则索引不起任何作用，用户想要优化查询速度，可以应用该类索引形式。

2.6 创建空间索引

需要设置SPATIAL参数，只有MyISAM类型表支持该类型索引。索引字段必须有非空约束。

create table list(
	id int(11) primary key auto_increment not null,
    goods geometry not null,
    spatial index listinfo(goods))engine=MyISAM;

空间索引对应的数据类型必须为空间数据类型。

3. 在已建立的数据表中创建索引

可以在已经创建的表中，在已经存在的一个或几个字段上创建索引。

create [unique | fulltext | spatial] index_name
on table_name(属性 [(length)][asc | desc]);

参数说明：

• index_name为索引名称，给用户创建的索引赋予新的名称。
• table_name为表名，指定创建索引的表名称。
• 可选参数，指定索引类型，包括unique（唯一索引）、fulltext(全文索引)、spatial(空间索引)。
• 属性参数，指定索引对应的字段名称。该字段必须已经预存在用户想要操作的数据表中，如果该数据表中不存在用户指定的字段，则系统会提示异常。
• length为可选参数，用于指定索引长度。
• asc和desc参数，指定数据表的排列顺序。

3.1 创建普通索引

create index stu_info on studentinfo(sid);

3.2 创建唯一索引

create unique index 索引名 on 数据表名称(字段名称);
create unique index index1_id on index1(cid);

3.3 创建全文索引

create fulltext index 索引名 on 数据表名称(字段名称)
create fulltext index index2_info on index2(info);

3.4 创建单列索引

create index 索引名 on 数据表名称(字段名称(长度));
create index index3_addr on index3(address(4));

3.5 创建多列索引

create index 索引名 on 数据表名称(字段名称 1,字段名称 2,......);
create index index4_na on index4(name,address);

与建立数据表时创建多列索引相同，当创建多列索引时，用户必须使用第一字段作为查询条件，否则索引不能生效。

3.6 创建空间索引

create spatial index 索引名 on 数据表名称(字段字段);

其中，spatial用来设置索引为空间索引，用户要操作的数据表类型必须为MyISAM类型，并且字段名称必须存在非空约束，否则将不能正常创建空间索引。

4.修改数据表结构添加索引

修改已经存在表上的索引，可以通过ALTER TABLE语句为数据表添加索引，其基本结构如下：

alter table table_name add [unique | fulltext | spatial] index index_name(属性名 [(length)] [asc|desc]);

4.1 添加普通索引

alter table studentinfo add index timer (time(20));

4.2 添加唯一索引

alter table 表名 add unique index 索引名称*(字段名称);

4.3 添加全文索引

全文索引创建只能作用在char,varchar,text类型的字段上。

alter table 表名 add fulltext index 索引名称(字段名称);
alter table workinfo add fulltext index index_ext(address);

4.4 添加单列索引

建立数据表时创建单列索引相同，用户可以设置单列索引。

alter table 表名 add index 索引名称(字段名称(长度));

4.5 添加多列索引

使用ALTER修改数据表结构同样可以添加多列索引。

alter table 表名 add index 索引名称(字段名称 1,字段名称2,......);

4.6 添加空间索引

alter table 表名 add spatial index 索引名称(字段名称);

需要数据表类型为myIASAM类型，其字段名称也必须存在非空约束。

5.删除索引

创建索引后，如果用户不在需要该索引，可以删除指定表的索引。

drop index index_name on table_name;

参数index_name是用户需要删除的索引名称，参数table_name指定数据表的名称。

drop index index_id on workinfo;

6.创建视图

6.1 概念

视图是一个虚拟表，是从数据库中一个或者多个表中导出来的表，其内容由查询定义。同真实的表一样，视图包含一系列的带有名称的列和行数据。

数据库中只存放了视图的定义，并没有存放视图中的数据。

使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。

6.2 视图的作用

视图的作用类似于筛选。

• 简单性：看到就是需要的，简化操作，不需要为每次操作指定全部的条件。
• 安全性：视图的安全性可以防止未授权用户查看特定的行和列，使有权限用户只能看到表中特定行的方法如下：
  • 在表中增加一个标志用户名的列
  • 建立视图，使用户只能看到标有自己用户名的行。
  • 把视图授权给其他用户。
• 逻辑数据独立性：视图可以使应用程序和数据库表在一定程度上独立。程序可以建立在视图之上，从而程序与数据库表被视图分割开来。

6.3 查看创建视图的权限

需要create view的权限，同时应该具有查询涉及的列的SELECT权限。

select Selete_priv,Create_view_priv from mysql.user where user="用户名";

• Select_priv:属性表现用户是否具有select权限，Y表示拥有select权限，N表示没有。
• Create_view_priv:属性表示用户是否具有create view权限，mysql.user表示mysql数据库下面的user表。
• “用户名”参数表示要查询是否拥有drop权限的用户，该参数需要用单引号括起来。

select Select_priv,Create_view_priv from mysql.user where user="root";

6.4 创建视图的步骤

create [algorithm = {undefined|merge|temptable}]
	view 视图名[(属性清单)]
	as select 语句
	[with [cascaded]local] check option];

• algorithm，表示视图选择的算法
• “视图名”参数，表示要创建的视图名称。
• “属性清单“，指定视图中各个属性的名词，默认情况下与select语句中查询的属性相同。
• select语句参数是一个完整的查询语句，表示从某个表中查出满足条件的记录，将这些记录导入视图中。
• with check option是可选参数，表示更新视图时要保证在该视图的权限范围之内。

在一个数据表中建立视图：

create view
book_view1(a_sort,a_talk,a_books)
as select sort,talk,books
from tb_book

在多个数据表中建立视图：

create algorithm=merge view
book_view1(a_sort,a_talk,a_books,a_name)
as select sort,talk,books,tb_user.name from tb_book,tb_name where tb_book.id=tb_name.id with local check option;

注意事项：

1. 运行创建视图的语句需要用户具有创建视图(create view)的权限，若加了[or replace]时，还需要用户具有删除视图（drop view）的权限。
2. select语句不能包含from子句中的子查询。
3. select语句不能引用系统或用户变量。
4. select语句不能引用预处理语句参数。
5. 在存储子程序内，定义不能引用子程序参数或局部变量。
6. 在定义中引用的表或视图必须存在，但是创建视图后，可以舍弃定义引用的表或视图，可通过check table检查视图定义是否存在这类问题、
7. 在定义中不能引用temporary表，不能创建temporary视图。
8. 在视图定义中命名的表必须已存在。
9. 不能将触发程序与视图关联在一起。
10. 在视图定义中允许使用order by，如果选择的视图有自己的order by语句，将忽略视图定义中的order by。

6.5 查看视图

第一种方式：

describe 视图名;

第二种方式：

desc 视图名;

第三种方式：

show table status like '视图名';

• "like"表示后面匹配的是字符串；
• “视图名”参数指要查看的视图名称，需要用单引号定义。

show table status like 'book_view1';

第四种方式：

show create view 视图名
show create view book_view1;

6.6 修改视图

第一种方式，可以在视图存在的时候修改视图，不存在的时候创建视图。

create or replace [algorithm = {undefined|merge|temptable}]
	view 视图[(属性清单)]
	as select 语句
[with [cascaded|local]check option];

第二种方式：

alter view [algorithm={merge | temptable|undefined}]
view view_name [(column_list)] 
as select_statement[with [cascaded | local] check option] 

• view_name：视图名称
• select——statement：SQL语句用于限定视图。

在创建视图时，在使用了with check option，with encryption，with schemabing ,view_metadata这些功能后，如果想保留这些选项的提供的功能，必须在alter view语句中将他们包括进去。

6.7 更新视图

对视图的更新其实就是对表的更新，更新视图是指通过视图来插入(insert)、更新(update)和删除(delete)表中的数据。

通过视图更新时，都要转换到基本表来更新，并且只能更新权限范围内的数据，超出了范围，就不能更新。

update book_view2 set a_book="php典型表" where id=27;

最好不要通过视图来更新数据，容易失败，限制很多：

1. 视图中包含count()、sum()、max()、min()等函数。
2. 视图中包含union、union all、distinct、group by、havig等关键字。
3. 常量视图
4. 视图中的select中包含子查询。
5. 由不可更新的视图导出的视图。
6. 创建视图时，algorithm为temptable类型。
7. 视图对应的表上存在没有默认值的列，而且该列没有包含在视图里。

6.8 刪除视图

删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，捕获删除数据。

可以使用drop view来删除视图，用户必须拥有drop权限。

drop view if exists <视图名> [restrict | cascade]

• IF EXISTS 参数指判断视图是否存在，如果存在则执行，不存在则不执行。
• “视图名”列表参数表示要删除的视图的名称和列表，各个视图名称之间用逗号隔开。

注：本内容收集整理于互联网，仅供学习交流之用！