MySQL视图、索引、备份与恢复、执行计划

目录

一、前言

1.导读

2.学习的好处

二、视图

1.什么是视图

2.视图与数据表的区别

3.使用视图的优点

4.视图的语法

1.创建视图（CREATE VIEW）

2.查询视图数据

3.更新视图数据

4.修改视图定义(ALTER VIEW)

5.删除视图（DROP VIEW）

三、索引

1.什么是索引

2.为什么要使用索引

3.索引的优缺点

4.何时不使用索引

5.索引分类

5.1.普通索引

5.2.唯一索引

5.2.主键索引

5.2.组合索引

5.2.全文索引

6.使用索引时的注意事项

四、执行计划

五、导入导出

1.使用工具，导入导出数据

2.使用mysqldump导入导出

3.LOAD DATA INFILE

---

一、前言

1.导读

这篇博客围绕的是视图、索引、导入导出、执行计划来展开的，相信学过数据库的同志们，对这些词汇都略有耳闻，那么这些词汇的意思是什么？实际的用途是什么？大多数人可能没有过多的去了解，其实以上技术都为了一个东西而产生——调优，也就是指对MySQL数据库进行优化以提升其性能和效率的过程。下面就由我来带领和大家走进MySQL视图、索引、导入导出、执行计划的技术分享。

2.学习的好处

刚刚说到了，这些技术的产生都是为了调优，那么主要突出在那些方面呢？

我列举了以下几点：
 

        1.提升查询性能：通过调优数据库参数、优化查询语句、设计合适的索引等方式，可以提升查询的执行速度和效率，减少查询的响应时间，提高用户体验。

        2.提高系统的吞吐量：通过调整数据库的缓冲区大小、优化磁盘访问方式、合理利用CPU和内存等资源，可以提高系统的吞吐量，支持更高的并发访问量，提升系统的处理能力。

        3.减少资源消耗：调优可避免低效查询的出现，减少不必要的资源消耗，如CPU、内存、磁盘等，提高数据库服务器的利用率，降低硬件成本。

        4.加强安全性：通过优化数据库的配置和权限设置，可以加强数据库的安全性，防止未授权的访问、SQL注入等安全风险。

        5.优化备份和恢复：优化存储引擎、设计合理的备份策略和恢复机制，可以提升备份和恢复的速度和效率，降低备份对系统性能的影响。

        6.降低维护成本：数据库调优可以提高数据库的稳定性和可靠性，减少故障发生的频率和影响范围，降低维护和故障排除的成本。

总结而言，MySQL调优可以帮助提升数据库性能和效率，提高系统吞吐量，减少资源消耗，加强安全性，优化备份和恢复过程，降低维护成本，从而提供更好的用户体验和系统可用性。

二、视图

1.什么是视图

在数据库中，视图（View）是一个虚拟的表，它基于一个或多个数据库表的查询结果而生成。与实际的数据库表类似，视图具有字段和行，并且可以像表一样被查询、过滤和操作。但与表不同的是，视图并不存储数据，它是从其他表中取得数据的一种方式。

以下是关于数据库中视图的一些重要概念和特点：

        

        1.定义：视图是一种逻辑上的表，它由一个查询语句定义，该查询语句可以包含更复杂的逻辑，如表连接、聚合函数、子查询等。

        2.数据独立性：通过使用视图，可以隐藏底层表的结构细节，使应用程序只需关注视图的结构，而不必了解表的具体信息。这种数据独立性使得在不影响应用程序的情况下修改底层表结构成为可能。

        3.数据安全性：通过视图，可以限制用户对数据的访问权限。只向用户公开了需要的部分数据。例如，视图可以将敏感数据过滤掉，只返回符合特定条件的数据。

        4.查询简化：视图可以将复杂的查询逻辑封装为一个简单的视图，使应用程序可以以简洁的方式使用汇总、过滤、联接等复杂查询结果。

        5.更新性：在某些情况下，视图可以进行更新操作。这取决于视图的定义和底层表的约束条件。一些视图可以进行直接的插入、更新和删除操作，而其他视图只能用于查询。

小贴士：

视图的使用场景

假设有一个电子商务平台，其中包含以下两个表：

1. 用户表（users）：

   • user_id（用户ID）
   • username（用户名）
   • email（电子邮件）
   • password（密码）

2. 商品表（products）：

   • product_id（商品ID）
   • product_name（商品名称）
   • price（价格）
   • description（描述）
   • user_id（卖家用户ID）

现在，我们可以使用视图来演示这些重要概念和特点。

      

  1.数据独立性:创建一个名为"商品列表"（product_list）的视图，通过连接用户表和商品表来检索商品信息。视图的定义如下：

CREATE VIEW product_list AS
  SELECT p.product_id, p.product_name, p.price, p.description, u.username
  FROM products p
  INNER JOIN users u ON p.user_id = u.user_id;

通过这个视图，应用程序可以直接查询"product_list"视图，而无需了解底层的表结构。

       

 2.数据安全性：创建一个名为"用户商品列表"（user_product_list）的视图，仅包含某个特定用户的商品清单。视图的定义如下：

CREATE VIEW user_product_list AS
  SELECT p.product_id, p.product_name, p.price, p.description
  FROM products p
  INNER JOIN users u ON p.user_id = u.user_id
  WHERE u.username = 'specific_user';

通过这个视图，只有特定用户可以查询到自己所发布的商品信息，其他用户无法访问到该视图，也可防止失误导致删除表的操作（权限不够的人只给ta视图进行查询操作）。

        

3.查询简化：创建一个名为"热门商品"（popular_products）的视图，显示价格高于特定
阈值的商品清单。视图的定义如下：

CREATE VIEW user_product_list AS
  SELECT p.product_id, p.product_name, p.price, p.description
  FROM products p
  INNER JOIN users u ON p.user_id = u.user_id
  WHERE u.username = 'specific_user';

这个视图使应用程序能够直接查询"popular_products"视图，而无需编写复杂的过滤条件。只需在视图的后面追加自己所需的where条件即可。

      

         

4.更新性：在某些情况下，可以对视图进行数据库操作。例如，可以创建一个允许用户更新个人商品信息的视图。

CREATE VIEW my_products AS
 SELECT product_id, product_name, price, description
 FROM products
 WHERE user_id = 'current_user';

这个视图可以让用户直接在视图中更新自己所发布的商品信息，而无需直接操作底层的表。

2.视图与数据表的区别

• 视图不是数据库中真实的表，而是一张虚拟表，其结构和数据是建立在对数据中真实表的查询基础上的。

• 存储在数据库中的查询操作 SQL 语句定义了视图的内容，列数据和行数据来自于视图查询所引用的实际表，引用视图时动态生成这些数据。

• 视图没有实际的物理记录，不是以数据集的形式存储在数据库中的，它所对应的数据实际上是存储在视图所引用的真实表中的。

• 视图是数据的窗口，而表是内容。表是实际数据的存放单位，而视图只是以不同的显示方式展示数据，其数据来源还是实际表。

• 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些 SQL 语句的集合。从安全的角度来看，视图的数据安全性更高，使用视图的用户不接触数据表，不知道表结构。

• 视图的建立和删除只影响视图本身，不影响对应的基本表。

3.使用视图的优点

1.定制用户数据，聚焦特定的数据

在实际的应用过程中，不同的用户可能对不同的数据有不同的要求。

例如，当数据库同时存在时，如学生基本信息表、课程表和教师信息表等多种表同时存在时，可以根据需求让不同的用户使用各自的数据。学生查看修改自己基本信息的视图，安排课程人员查看修改课程表和教师信息的视图，教师查看学生信息和课程信息表的视图。

2.简化数据操作

在使用查询时，很多时候要使用聚合函数，同时还要显示其他字段的信息，可能还需要关联到其他表，语句可能会很长，如果这个动作频繁发生的话，可以创建视图来简化操作。

3. 提高数据的安全性

视图是虚拟的，物理上是不存在的。可以只授予用户视图的权限，而不具体指定使用表的权限，来保护基础数据的安全。

4. 共享所需数据

通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次。

5. 更改数据格式

通过使用视图，可以重新格式化检索出的数据，并组织输出到其他应用程序中。

6.重用 SQL 语句

视图提供的是对查询操作的封装，本身不包含数据，所呈现的数据是根据视图定义从基础表中检索出来的，如果基础表的数据新增或删除，视图呈现的也是更新后的数据。视图定义后，编写完所需的查询，可以方便地重用该视图。

视图优点在上面也提到几个就不过多赘述了，大家看看就好！！

4.视图的语法

1.创建视图（CREATE VIEW）

在数据库中创建视图可以基于一个或多个表进行查询，并将查询结果作为视图的定义。语法如下：

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table1
WHERE condition;

创建视图时，你可以指定视图的名称（view_name），定义视图所包含的列以及查询的条件。

小贴士：

如果出现以上错误说明视图中有两个列字段相同，需要去重。

2.查询视图数据

一旦视图创建完成，可以像查询表一样查询视图，使用SELECT语句获取视图中的数据。例如：

SELECT * FROM view_name;

注意，查询视图时可以像查询表一样使用WHERE子句进行过滤操作。

3.更新视图数据

视图的可更新性取决于视图的定义和底层表的约束条件。在某些情况下，你可以对视图进行UPDATE、INSERT和DELETE操作，以更新底层表中的数据。例如：

UPDATE view_name SET column1 = value1 WHERE condition;
INSERT INTO view_name (column1, column2, ...) VALUES (value1, value2, ...);
DELETE FROM view_name WHERE condition;

注意，进行视图更新时，需要确保视图的定义满足各种约束条件。

4.修改视图定义(ALTER VIEW)

你可以使用ALTER VIEW语句来修改已存在的视图定义。例如：

ALTER VIEW view_name AS
SELECT column1, column2, ...
FROM table1
WHERE condition;

通过修改视图定义，可以重新定义视图的查询逻辑、添加新的列或更改查询条件。

5.删除视图（DROP VIEW）

如果不再需要某个视图，可以使用DROP VIEW语句将其从数据库中删除。例如：

DROP VIEW view_name;

请谨慎删除视图，因为一旦删除，视图的定义和数据将无法恢复。

三、索引

1.什么是索引

索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。

可以把索引比作新华字典的音序表。例如，要查“库”字，如果不使用音序，就需要从字典的 400 页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从 10 多页的音序表中直接查找。这样就可以大大节省时间。

因此，使用索引可以很大程度上提高数据库的查询速度还有效的提高了数据库系统的性能。

2.为什么要使用索引

索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一 一对应关系的有序表。

索引是 MySQL 中十分重要的数据库对象，是数据库性能调优技术的基础，常用于实现数据的快速检索。

在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

1.顺序访问

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。

顺序访问实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。

2.索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。

使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

总而言之，不使用索引，MySQL 就必须从第一条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL 就能快速到达一个位置去搜索数据文件，而不必查看所有数据，这样将会节省很大一部分时间。

我这里准备了一组上万的数据现在来测试一下索引的效率。

建立索引前，查询速度0.181s

建立索引后，查询速度0.013s

相信看完对比，你也会有所震撼，为什么小小的索引效果却有这么大！!

3.索引的优缺点

索引有其明显的优势，也有其不可避免的缺点。

优点

索引的优点如下：

• 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。

• 可以给所有的 MySQL 列类型设置索引。

• 可以大大加快数据的查询速度，这是使用索引最主要的原因。

• 在实现数据的参考完整性方面可以加速表与表之间的连接。

• 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

缺点

增加索引也有许多不利的方面，主要如下：

• 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。

• 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。

• 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

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

刚刚我们在做有无索引查询效果差异的时候大家可以看到chuan创建索引的时间远远大于查询的时间，所以在非必要情况下，不要轻易创建索引。

4.何时不使用索引

索引虽好但也有不足之处，那么什么时候不该建议使用索引呢？

• 表记录太少

• 经常增删改的表

• 数据重复且分布均匀的表字段，只应该为经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）

• 频繁更新的字段不适合创建索引(会增加IO负担)

• where条件里用不到的字段不创建索引

5.索引分类

MySQL索引分为普通索引、唯一索引、主键索引、组合索引、全文索引。索引不会包含有null值的列，索引项可以为null（唯一索引、组合索引等），但是只要列中有null值就不会被包含在索引中。

5.1.普通索引

普通索引是最基本的索引，它没有任何限制；

• 创建索引语法：

 create index index_name on table(column)；

• 修改表结构方式添加索引：

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

• 删除索引

DROP INDEX index_name ON table

5.2.唯一索引

唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

CREATE UNIQUE INDEX indexName ON table(column(length))

5.2.主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值

select * from t_log where id = '07489cdafd6d4a3489884cd3c00c7b27'

利用执行计划查看

 可以看到key字段下面的PRIMARY，说明使用的索引是主键（Primary Key）。

5.2.组合索引

组合索引指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则。

create index idx_userid_moduleid_url on t_log(userid,moduleid,url)

注意：组合遵循最左前缀原则。那么何为最左前缀原则？

例如我们上面的案例userid字段就是我们的最左前缀，我们要使用组合索引的时候，必须带上该字段否则组合索引无效，我们利用执行计划来测试一下。

不带最左前缀

EXPLAIN select * from t_log where moduleid = '' and url = ''

可能执行的索引并没有我们刚刚创建的索引只有普通索引，走的也是普通索引。

 

带最左前缀(多个字段)

EXPLAIN select * from t_log where userid = '' and moduleid = '' and url = ''

在多个字段的情况下，可能走的索引有普通索引和组合索引，走的是普通索引。

 

带最左前缀(只有最左前缀字段)

EXPLAIN select * from t_log where userid = ''

在只有最左前缀下可能走的是组合索引，走的也是组合索引。

5.2.全文索引

MySQL提供了全文搜索（Full-Text Search）功能以实现对文本数据进行高效的搜索和匹配。全文索引是一种特殊类型的索引，用于在文本列上进行全文搜索。

使用全文索引可以加速针对文本列的搜索操作，并支持更灵活的搜索模式，例如关键词匹配、短语搜索、布尔搜索等。

要使用全文索引功能，首先需要创建一个全文索引。为了创建全文索引，需要满足以下条件：

        1.表的存储引擎必须是MyISAM或InnoDB。在 MySQL 5.6 之后，InnoDB 存储引擎也支持全文索引。

        2.只有 CHAR、VARCHAR 和 TEXT 类型的列可以被索引。

要在一个表的列上创建全文索引，可以使用以下语句：

CREATE FULLTEXT INDEX index_name ON table_name (column_name);

一旦全文索引创建完成，可以使用全文搜索功能。以下是几个常用的全文搜索函数：

• MATCH: 用于指定要搜索的列。
• AGAINST: 用于指定搜索的关键词。
• BOOLEAN MODE: 用于支持布尔搜索。

下面是一个使用全文索引进行搜索的例子：

SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('keyword');

注意:全文索引在大量数据和复杂查询场景下性能可能有限。在某些情况下，可以考虑使用更专业的全文搜索引擎，如Elasticsearch和Solr。

总结来说，MySQL的全文索引是一种用于文本列的索引类型，可以提供高效的全文搜索功能。通过创建索引和使用相应的查询语句，可以实现对文本数据的灵活和高效的搜索。

6.使用索引时的注意事项

• like以通配符%开头索引失效

• 当全表扫描比走索引查询的快的时候，会使用全表扫描，而不走索引

• 字符串不加单引号索引会失效

• where中索引列使用了函数（例如substring字符串截取函数）

• where中索引列有运算（用了< or > 右边的索引会失效，用<= or >= 索引不会失效）

• is null可以走索引，is not null无法使用索引（取决于某一列的具体情况）

• 复合索引没有用到左列字段（最左前缀法则，如果没用用到最左列索引，或中间跳过了某列有索引的列，索引会部分失效）

• 条件中有or，前面的列有索引，后面的列没有，索引会失效。想让索引生效，只能将or条件中的每个列都加上索引

四、执行计划

刚刚在测试索引效果的时候反复提到执行计划，那么执行计划是什么呢？

MySQL的执行计划（Execution Plan）是通过使用EXPLAIN关键字来获取的。执行计划为查询优化器提供了关于如何执行查询的详细信息，包括表的读取顺序、使用的索引、连接方法等。以下是在MySQL中获取执行计划的基本步骤：

1. 准备一个查询语句：首先，准备一个需要获取执行计划的SELECT语句。

2. 使用EXPLAIN关键字：在查询语句前加上EXPLAIN关键字，形成EXPLAIN SELECT语句，例如：

   EXPLAIN SELECT column1, column2, ...
   FROM table_name
   WHERE condition;
   

3. 执行查询并查看执行计划：执行上述EXPLAIN SELECT语句，将会返回一个Execution Plan，即查询的执行计划。执行计划是一个结果集，包含了查询优化器根据查询语句生成的执行计划详细信息。

   例如，执行计划可能包含以下列信息：

   • id: 查询块的唯一标识符。
   • select_type: 查询的类型，如简单查询、联接查询、子查询等。
   • table: 查询涉及的表名。
   • type: 表示MySQL用于执行查询的访问方式，如全表扫描、索引扫描、范围扫描等。
   • possible_keys: 可能使用的索引列表。
   • key: 实际使用的索引。
   • rows: 查询过程中扫描的行数估计。
   • extra: 其他附加信息，如是否使用临时表、是否使用文件排序等。

   通过分析执行计划，你可以了解查询语句的性能瓶颈，是否使用了适当的索引、连接方式等。根据执行计划的输出，可以进行索引优化、查询重写等操作来改进查询性能。

请注意，执行计划中的具体信息会根据查询语句和数据库架构而有所不同。因此，理解和分析执行计划需要熟悉MySQL查询优化的基本原则和技巧。使用EXPLAIN关键字能够帮助你更好地优化你的查询，并提升MySQL的性能。

五、导入导出

1.使用工具，导入导出数据

导入