mysql视图，备份，索引

1.视图

本文参考了高性能MySQL

数据库中的视图是一个虚拟表。同真实的表一样，视图包含一系列带有名称的行和列数据。行和列数据来自由定义视图查询所引用的表，并且在引用视图时动态生成。

 

视图的实现

视图中不存放任何数据，在使用SQL语句访问视图的时候，返回的数据时MySQL从其他表中生成的。视图和表在同一个命名空间，在很多地方对于视图和表是同样对待的。不过也有不同，不能对视图创建触发器，不能使用DROP TABLE命令删除视图。

 

假设我们创建一个视图：

create view Oceania as

select * from Country where Continent ='Oceania'

with check option;

 

实现视图最简单的方法是将select语句的结果存放到临时表中。当需要访问视图的时候，直接访问临时表就可以。比如：

select Code, Name from Oceania where Name =‘Australia’;

 

在实现上述查询时，有两种方式去实现，一种是使用临时表，还有一种是重写含有视图的查询，将视图的定义SQL直接包含进查询的SQL中：

使用临时表算法：

create temporary table TMP_Oceania_123 as

select * from Country where Continent = ‘Oceania’;

select Code, Name from TMP_Oceania_123where Name = ‘Australia’;

 

使用合并算法的：

select Code, Name from Country

where Continent = ‘Oceania’ and Name = ‘Australia’;

 

如果可能，会尽可能使用合并算法。下图是这两种算法实现的细节。

 

 

如果视图中包含Group by, Distinct, 任何聚合函数，union，子查询等，只要无法在原表记录和视图记录中建立一一映射的场景中，MySQL都会使用临时表算法来实现。可以使用Explain来确定MySQL使用的具体算法。

 

更新视图

视图可以被更新。只要指定了合适的条件，就可以更新，删除甚至向视图中写入数据。如果视图定义中包含了group by, union, 聚合函数以及其他一些特殊情况，就不能被更新了。更新视图的查询可以是一个关联语句，但是有一个限制，被更新的列必须来自同一个表中。所有使用临时表算法实现的视图都无法被更新。

 

视图的性能

使用临时表算法实现的视图，在某些时候性能会很糟糕（可能比直接使用等效查询语句要好一点）。MySQL会以递归的方式执行这些视图，先会执行外层查询，即使外层查询优化器将其优化的很好，但是，内外结合的优化却无法做到非常好。如果打算使用视图来提升性能，需要做比较详细的测试。即便是合并算法实现的视图也会有额外的开销，而且使徒的性能很难预测。

 

视图的限制

视图有很多限制，比如，MySQL不支持物化视图（将视图结果数据存放在一个可以查看的表中，并定期充原始表中刷新数据到这个表中）。也不支持在视图中创建索引。当然，可以通过构建缓存表或者汇总表的办法来模拟。也可以直接使用工具Flexviews来实现这个目的。

2.备份

MySQL并不会保存视图定义的原始SQL语句，所以，如果我们希望简单的修改视图的结果来重新定义，有的人可能会直接使用show create view语句来查看之前的定义，但是该语句查询出来的视图创建语句会让人大失所望，没有格式化，没有注释，没有缩进，基本不可读。

MySQL备份（Backup）与 恢复（Restore）汇总
1.mysqldump
2.mysqlbackup
3.mysqlhotcopy
4.xtrabackup/innobackupex
5.cp
备份备于一切，今天汇总一下常用的几种备份方法，以及恢复的步骤。

1.mysqldump

在日常工作中，我们会使用mysqldump命令创建sql格式的转储文件来备份数据库。或者我们把数据导出后做数据迁移，主备搭建等操作。mysqldump是一个逻辑备份工具,复制原始的数据库对象定义和表数据产生一组可执行的SQL语句。 默认情况下,生成insert语句，也能生成其它分隔符的输出或XML格式的文件。
shell> mysqldump [arguments] > file_name

我们简单的来看一下日常的用法：
备份所有的数据库：
shell> mysqldump --all-databases > dump.sql (不包含INFORMATION_SCHEMA,performance_schema,sys，如果想要导出的话还要结合--skip-lock-tables和--database一起用)
备份指定的数据库：
shell> mysqldump --databases db1 db2 db3 > dump.sql
当我们只备份一个数据的时候可以省去 --databases 直接写成：mysqldump test > dump.sql 不过有一些细微的差别，如果不加的话，数据库转储输出不包含创建数据库和use语句，所以可以不加这个参数直接导入到其它名字的数据库里
当然我们也可以只备份某个表 ：
mysqldump --user [username] --password=[password] [database name] [table name] table_name.sql

了解了简单的一些用法后我们再着重的看一下几个参数：

--master-data 获取备份数据的Binlog位置和Binlog文件名，用于通过备份恢复的实例之间建立复制关系时使用，该参数会默认开启。
--dump-slave 用于在slave上dump数据，建立新的slave。因为我们在使用mysqldump时会锁表，所以大多数情况下，我们的导出操作一般会在只读备库上做，为了获取主库的Relay_Master_Log_File和Exec_Master_Log_Pos，需要用到这个参数，不过这个参数只有在5.7以后的才会有
–no-data, -d 不导出任何数据，只导出数据库表结构

刚刚我们说过在使用mysqldump的时候会锁表，我们来详细的看一下它的锁机制。

我们开两个窗口，在第一个里面执行mysqldump -uroot -pxxxxx --master-data=2 --databases dbname > /tmp/dbnamedate +%F.sql
然后第二个窗口登陆进去，使用show process的命令可以看到目前dump的session正在执行 

SELECT /*!40001 SQL_NO_CACHE */ * FROM table_name; 可以看到这条sql正在以no_cache的模式查询数据。
然后我们在同样的表上执行一下select，发现被阻塞了。光标一直不返回。

一般遇到这种文件，我们会想是不是有锁呢？
为了验证我们查看一下锁的信息，可以发现dump的进程实际上是加了锁的。

我们把具体的general_log打开，然后看一下当时的操作：

4101044 Query FLUSH /*!40101 LOCAL */ TABLES
4101044 Query FLUSH TABLES WITH READ LOCK （关闭所有打开的表，同时对于所有数据库中的表都加一个读锁，直到显示地执行unlock tables，该操作常常用于数据备份的时候。）
4101044 Query SHOW MASTER STATUS（这是因为我用了--master-data=2）
所以这个时候表就会被锁住。
如果我不加--master-data参数(mysqldump -uroot -pxx --databases db > /tmp/dbnamedate +%F.sql) mysql会显示的对每一张要备份的表执行
LOCK TABLES table_name1 READ,LOCK TABLES table_name2 READ
并且也不会有读的阻塞。
那有没有不锁的方法，其实也是有的，就是使用--single-transaction把备份的操作放在一个事务里去进行
带上--single-transaction参数的mysqldump备份过程：
如果是5.6版本的mysql