MySQL——虚拟表以及索引（笔记整理）

虚拟表

在MySQL中有三种虚拟表：临时表、内存表、视图

一、临时表

• 是建立在系统临时文件夹中的表；
• 只在当前连接可见，当关闭连接时，MySQL会自动删除表并释放所有空间；
• 如果使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表

语法：

CREATE TEMPORARY TABLE  表名

        注：默认情况下，断开与数据库的连接后，临时表会自动被销毁 

        当然也可手动销毁，读取和删除表与普通表语句是一样的。如下：

# 查询临时表
select * from 表名;

#删除临时表
drop table if exists 表名;

临时表注意事项：

• 临时表只在当前连接可见，当这个连接关闭时，会自动DROP；
• 同一个查询语句，只能用一次临时表，即不能将临时表和自己做连接等；
• 如果超出了临时表的容量，临时表会转换成磁盘表；
• SHOW TABLES语句不会列出临时表，在information_schema表中也不存在临时表信息；
• 两个不同的连接可以使用相同名字的临时表，两个表之间不存在关系，如果临时表名字和已存在的磁盘表名字一样，那么临时表会暂时覆盖磁盘表。

二、内存表

定义

是指使用Memory引擎的表，这种表的结构在磁盘里，数据都保存在内存里，系统重启时候数据会被清空，但表结构还在。内存表也可以被看作是临时表的一种。

语法：需要将存储引擎设置为：ENGINE =MEMORY

CREATE TABLE IF NOT  EXISTS tmp_table(...

....)

ENGINE = MEMORY;

内存表注意事项：

• 当MySQL服务重启之后，内存表的数据会丢失，表结构依旧存。
• data目录下只有tmp_memory.frm，表结构放在磁盘上，数据放在内存中。
• 可以创建索引，删除索引，支持唯一索引。
• 不影响主备，主库上插入的数据，备库也可以查到。
• SHOW TABLES语句可以查看的到表。
• 内存表使用哈希散列索引把数据保存在内存中，因此具有极快的速度，适合缓存中小型数据。
• 内存表不能包含BLOB或者TEXT列

临时表与内存表的区别：

三、视图 

 定义

视图是SELECT 语句组成的查询定义的虚拟表，它的定义存储在数据库中。但与表不同的是， 视图实际上不包含任何数据，不分配空间；

视图可以是 建立在一个或多个表上，也可以建立在视图上，但是对视图数据的操作最终都会转换为对基本表的操作。

语法

➢ 创建和修改视图只需在SELECT语句之前添加CREATE OR REPLACE VIEW 视图名 AS…即可

#创建视图：统计每门课程的平均分，行为学号，列为每个课程id
CREATE OR REPLACE VIEW v_score AS
SELECT id,
AVG(CASE cid WHEN 'C001' THEN score ELSE 0 END) 'C001',
AVG(CASE cid WHEN 'C002' THEN score ELSE 0 END) 'C002',
AVG(CASE cid WHEN 'C003' THEN score ELSE 0 END) 'C003',
…
FROM scores GROUP BY id;

#查看存在的视图
SHOW TABLES LIKE 'v_%';
#查看视图
SELECT * FROM v_score where id<3;
#查看创建视图的命令
SHOW CREATE VIEW v_score;
#查看视图数据内容
DESC v_score;
SHOW FIELDS FROM v_score;

视图应用场景：

1、保密工作，例如有一个员工工资表，如果只希望财务看到员工工资这个字段，而其他人不能看到工资字段，建立视图的时候可以把工资这个敏感字段隐藏起来

2、有一个查询语句非常复杂，包含多个表、子查询等，有时还想把这个巨大无比的SELECT语句
和其他表关联起来得到结果，可以用一个视图来代替这个复杂的SELECT语句

四、派生表

定义

➢ 派生表类似于临时表，但在SELECT语句中使用派生表比临时表简单，因为没有创建临时表的步骤。
➢ 派生表是 查询结果组成的虚拟表。是在外部查询的FROM子句中定义的，不需要手动创建。只要外部查询一结束，派生表也就消失。
➢ 派生表可以简化查询，避免使用临时表。相比手动生成临时表性能更优越，目的主要是为了缩小数据的查找范围，提高查询效率

#示例：
SELECT * FROM
(SELECT id, age, address, IF(sex='男',0,1) FROM student) T
WHERE T.age>40;

即，SELECT * FROM 加一个查询语句，命名T

其他：WITH  表名  AS （）

WITH AS 短语的用法类似派生表，它将一个SELECT语句的结果另起一个别名，方便接下来的使用。

# 示例
WITH a AS(SELECT id, age, address, IF(sex='男',0,1) FROM student)
SELECT * FROM a
WHERE age>40;

索引

功能

1.加速查找

2.在使用order by、 group by 子句时，利用索引可以减少排序和分组的时间

3.MySQL中的primary key，unique等都是索引，实现表与表之间的完整性约束

4.防止重复数据产生

索引的类型

索引的创建

➢ 普通索引可以通过以下几种方式创建：

1. 创建索引，CREATE INDEX 索引名 ON tablename(字段列表)
2. 修改表，ALTER TABLE tablename ADD INDEX  <索引名>（字段列表）
3. 建表时指定索引，CREATE TABLE tablename（字段类型，INDEX 索引名）；

#创建示例：

CREATE INDEX index1 ON student(id);   #添加普通索引
CREATE INDEX index2 ON student(id,sname);   #添加联合索引
CREATE UNIQUE index3 ON student(id);   #添加唯一索引
ALTER TABLE student ADD PRIMARY KEY(id);  #添加主键索引
ALTER TABLE student ADD UNIQUE index4(id);   #添加主键索引

#建表时指定索引
CREATE TABLE IF NOT EXISTS test(id INT ,sname VARCHAR(10),
PRIMARY KEY(id),UNIQUE indexid(id));

查看索引：

SHOW INDEX FROM test；

 删除索引：

DROP INDEX <索引名>ON<表名>

如何正确使用索引：

➢ 创建了索引并不是时时都会生效，有些情况将导致索引失效，注意以下几种情况：
1. 最左前缀匹配原则，若有联合索引index(a,b,c) ，若WHERE子句中有a就会用到联合索引，若只用到b ， c就会失去索引效果。
2. 在WHERE子句中进行NULL值 值判断的话会导致引擎放弃索引而产生全表扫描。
3. 避免在WHERE子句中使用!= ， < >等 比较运算符和IN，否则会导致引擎放弃索引而产生全表扫描。
4. 避免在WHERE子句中使用OR来连接条件。
5. 索引列不能参与计算，避免在WHERE子句中=的左边使用表达式操作或者函数操作。
6. 避免在WHERE子句中使用LIKE 模糊查询