Mysql视图及索引

最新推荐文章于 2024-05-06 10:39:48 发布

✘君临沂

最新推荐文章于 2024-05-06 10:39:48 发布

阅读量515

点赞数 1

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_74915426/article/details/131626312

版权

2.3.1.顺序访问（Sequential Access）

2.3.2.索引访问（Index Access）

这篇Mysql视图及索引就到这了，各位大佬多多提取意见，小编愿意受教！！！！

一.视图

1.1.什么是视图

视图（View）是数据库中的一个虚拟表，它是基于一个或多个实际表查询的结果集。视图与实际表类似，可以被查询、插入、更新和删除数据，但它本身并不存储任何数据，只是存储了对数据的定义和查询规则。

通过创建视图，可以对数据库中的数据进行一定程度的逻辑组织和封装，以简化复杂的查询操作。视图通常用来隐藏底层表的复杂性，提供一个简单和易于理解的访问接口。

1.2.主要优点

简化查询：视图可以定义一系列经常使用的查询，简化对数据的访问过程，减少编写复杂查询的工作量。
数据安全性：通过视图可以限制用户只能访问其所需的字段，隐藏敏感数据，提高数据安全性。
数据一致性：通过视图可以确保不同应用程序或用户访问相同的数据，避免数据的重复存储和冗余。
逻辑封装：通过视图可以将复杂的数据模型和查询组织为更简单、更易于理解的逻辑结构，提高数据模型的可维护性和可扩展性。

创建视图后，可以像使用普通表一样进行查询操作。例如，可以通过 SELECT 语句从视图中检索数据，也可以通过 INSERT、UPDATE 和 DELETE 语句对视图进行数据的插入、更新和删除。

需要注意的是，视图只是对查询结果进行了逻辑封装，并不实际存储数据。因此，对视图的修改操作实际上是对底层表的修改操作。同时，由于视图是一个虚拟表，它的性能受到底层表的限制，查询视图时会访问底层表的数据。

总结而言，视图是一种方便、灵活和安全的数据库工具，用于简化复杂查询、提高数据安全性和逻辑封装。它在数据库设计和应用程序开发中扮演着重要的角色。

1.3. 视图与数据表的区别

视图（View）和数据表（Table）在数据库中具有以下主要区别：

存储方式：数据表是实际存储数据的结构化对象，它将数据存储为行和列的形式。而视图并不存储实际的数据，它只是一个虚拟表，存储了对数据的定义和查询规则。

数据的来源：数据表是直接存储数据的地方，数据可以通过INSERT语句插入到表中。而视图是基于一个或多个实际表的查询结果，它通过查询语句从数据表中检索数据生成。

数据的更新：数据表可以进行任意的增删改操作，通过INSERT、UPDATE和DELETE语句来修改表中的数据。而视图的数据是从底层表中派生的，对视图的更新操作实际上是对底层表的更新操作。

数据的完整性：数据表可以定义一系列的约束条件，如主键、唯一键、外键等，以确保数据的完整性。视图没有自己的物理结构，所以无法在视图上定义这些约束条件。

数据的查看和访问权限：数据表的访问权限控制是基于整个表的。视图可以控制对底层表中特定列的访问权限，可以隐藏敏感数据，提高数据的安全性。

数据的组织和封装：视图可以通过定义查询语句，对底层表进行逻辑组织和封装，从而简化复杂的查询操作。数据表较为简单，只是存储了原始数据。

性能影响：视图的查询性能受底层表的影响，查询视图时会访问底层表的数据。数据表的查询性能与表中的索引、分区等因素有关。

综上所述，数据表是实际存储数据的对象，而视图是基于一个或多个实际表的查询结果。数据表用于存储和管理数据，而视图用于简化复杂查询、提供数据逻辑封装和安全性。视图不存储数据，而是定义了对数据的查询操作，并通过查询底层表来生成结果。

1.4.特点

视图（View）在数据库中具有以下特点：

虚拟表：视图是一个虚拟表，它本身不存储实际的数据，只是存储了对数据的定义和查询规则。视图的查询结果是基于底层表的查询操作得到的。

数据隐藏：视图可以隐藏底层表的复杂性，只暴露给用户所需的数据。通过视图可以限制用户只能访问特定的列，隐藏敏感数据，提高数据的安全性。

简化复杂查询：视图可以通过定义查询语句，对底层表进行逻辑组织和封装，从而简化复杂的查询操作。用户可以像查询普通表一样对视图进行查询，而不需要了解底层表的结构和关系。

数据一致性：通过视图，可以确保不同应用程序或用户访问相同的数据，避免数据的重复存储和冗余。视图可以提供一个统一的数据访问接口，保证数据的一致性。

维护性和扩展性：视图提供了一种将多个表合并成单个逻辑表的方式，使得数据库模型更易于维护和扩展。当底层表结构发生变化时，只需要更新视图的定义，而不需要修改使用视图的应用程序。

查询性能：视图的查询性能受到底层表的影响，查询视图时会访问底层表的数据。适当地使用索引和优化查询语句，可以提高视图的查询性能。

可编辑性：视图可以进行插入、更新和删除的操作，但实际上是对底层表的操作。修改视图可能会影响到底层表的数据。

视图是数据库中的一种重要工具，它可以简化复杂查询、提供数据的逻辑封装和安全性，改善数据的维护性和可扩展性。使用视图可以隐藏底层表的复杂性，提供一个简化和统一的数据访问接口。

1.5.语法

创建和使用视图的语法根据数据库管理系统（DBMS）的不同有所差异，下面是一般性的视图语法：
创建视图：
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
在上述语法中，view_name 是视图的名称，column1, column2, ... 是从 table_name 表中选择的列，condition 是查询条件。
修改视图：
ALTER VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
使用 ALTER VIEW 语句可以修改视图的定义，可以更改选择的列和查询条件。
删除视图：
DROP VIEW view_name;
使用 DROP VIEW 语句可以删除指定的视图。
查询视图：
SELECT * FROM view_name;
使用 SELECT 语句可以查询视图中的数据，就像查询普通表一样。
需要注意的是，视图的语法可能会因具体的DBMS而有所不同，不同的DBMS可能支持不同的视图功能和语法扩展。具体的语法细节应根据所使用的数据库管理系统的文档或参考手册来确定。

此外，视图还可以使用其他功能和扩展，如内连接、外连接、子查询、函数等，以满足更复杂的数据查询需求。这些在使用视图时可以根据具体情况进行选择和应用。

二.索引

2.1.什么是索引

索引（Index）是数据库中用于快速定位和访问数据的数据结构。它类似于图书馆的书目索引，通过索引可以加快对数据表中特定行的查找速度。

索引是数据库中的一种数据结构，用于提高数据的检索速度和查询效率。它类似于书籍的目录，为数据库表中的数据建立了一种快速访问的路径，帮助数据库系统在执行查询时能够更有效地定位和访问数据。

具体来说，索引是由一组列（如表的列或表达式）组成的，每个列都有对应的索引值和指向实际数据位置的指针。通过构建索引，数据库系统会根据索引值的顺序或哈希算法对数据进行排序分组，加快数据的搜索和匹配过程。

2.2.在数据库的作用

提高查询性能：索引可以快速定位到满足查询条件的数据行，减少了全表扫描的开销，提高了查询的速度。

加速排序：当对数据进行排序时，有索引的情况下可以利用索引的有序性，加快排序操作的速度。

约束唯一性：索引可以用于实施唯一性约束，保证某列中的值唯一，例如定义主键或唯一键。

支持连接操作：通过创建连接索引，可以加速连接操作的执行速度，提高多表查询的效率。

数据库中的索引通常是在关键列（例如主键、唯一键、经常用于搜索的列）上创建的。索引可以基于单个列，也可以基于多个列的组合。

2.3.访问方式

2.3.1.顺序访问（Sequential Access）

顺序访问（Sequential Access）是一种数据访问的方式，它按数据的存储顺序依次读取或写入数据。在顺序访问中，数据会被按照其在存储媒体（如磁盘、磁带）中的物理位置进行顺序扫描，依次取出或写入。

顺序访问适用于对整个数据集进行遍历的场景，要求数据按照预定的顺序进行操作，而不需要在任意位置进行随机访问。它的特点是读取或写入数据需要按照顺序一个接一个地进行，不能跳过或忽略某个数据。

对于存储在磁盘或磁带等介质上的大量数据，顺序访问的效率比较高。这是因为磁头或读写头只需要移动一次，然后按照顺序进行读取或写入，减少了访问磁盘的寻道时间，提高了数据的读写速度。

然而，顺序访问也有其局限性。当需要对数据进行随机访问或根据特定的搜索条件进行查询时，顺序访问效率较低。由于需要遍历整个数据集，当数据量大时会有较高的时间开销。

在数据库中，索引的设计和使用大多是为了支持高效的随机访问，即根据特定条件或键值快速检索和访问数据。相比于顺序访问，随机访问能够根据查询条件快速定位到指定的数据行，提高数据查询和操作的效率。然而，在某些特定的情况下，顺序访问仍然可以提供高效的数据处理方式，例如进行大规模数据的批量处理或分析。

2.3.2.索引访问（Index Access）

索引访问（Index Access）是指通过数据库索引来定位和访问数据的过程。索引可以看作是数据表中特定列或列组合的快速查找结构，它提供了一种高效的方法来加速数据的检索和查询操作。

在索引访问中，数据库系统会根据查询条件和索引的定义，利用索引结构来定位满足条件的数据行。以下是一般的索引访问过程：

查询优化：数据库系统首先会解析查询语句，并通过查询优化器分析查询的条件和表结构，确定最佳的执行计划。

索引选择：优化器会评估各种索引的选择，并选择最合适的索引来处理查询。选择的依据包括索引的选择性、数据分布等因素。

索引搜索：一旦确定使用了哪个索引，数据库系统将根据索引的结构，通过索引树（如B树、B+树）进行快速搜索。这种搜索算法可以在索引中定位到满足查询条件的数据行，并返回相应的结果。

数据访问：索引搜索的结果将返回一个或多个满足条件的指针或行标识符，然后数据库系统使用这些指针或标识符来直接访问对应的数据行。

通过使用索引访问，可以大幅提高数据查询和检索的效率，避免了全表扫描和线性搜索的开销。索引访问在处理大量数据时非常有用，并且对于复杂的查询和连接操作尤为重要。

需要注意的是，索引的设计和使用需谨慎。过多的索引可能会增加数据操作的开销和存储空间，且索引的更新也需要成本。因此，在实际应用中，需要根据具体场景、数据访问模式和查询需求来合理选择和使用索引。

2.4.优缺点

优点：

提高查询性能：索引能够加速数据的检索过程，通过快速定位到满足查询条件的数据行，提高查询的速度和效率。对于大型数据表或复杂查询，索引可以显著降低查询开销。
加速排序：当对数据进行排序时，有索引的情况下可以利用索引的有序性，加快排序操作的速度。
支持随机访问：索引使得数据库可以实现快速的随机访问，根据特定条件或键值迅速定位和访问数据，满足各种查询需求。
约束唯一性：通过在列上创建唯一索引，可以保证该列中的值的唯一性，实现唯一性约束。
支持连接操作：通过创建连接索引，可以加速连接操作的执行速度，提高多表查询的效率。

缺点：

增加存储开销：索引需要占用存储空间，特别是对于大型表和复杂索引结构来说，会占用较大的磁盘空间。
增加写操作开销：对于插入、更新和删除操作，会导致索引的更新，增加了写操作的开销，降低了写入性能。
索引维护开销：随着数据的增删改，索引需要进行维护，包括索引的重新组织和重建等操作，有一定的维护成本。
延迟更新：对包含索引的表进行大量写入操作时，由于索引的维护过程，会导致数据的延迟更新。
索引选择困难：在设计索引时，需要权衡考虑查询性能和存储开销，选择合适的索引策略和数据列进行索引，这需要一定的经验和优化技巧。

总体而言，索引在提高查询性能、支持随机访问和数据约束方面具有重要的优势，但需要注意索引的存储开销、维护成本和写入性能等问题。设计和使用索引时，应根据具体应用的需求和特点，合理选择和管理索引。

2.5.何时不使用

在以下情况下，可能不需要使用索引：

小型表：对于小型表，数据量较少且查询操作较简单的情况，使用索引可能带来的性能改进可能并不明显，而索引的维护成本可能会超过其带来的收益。在这种情况下，可以考虑不使用索引。
插入频繁的表：如果表需要频繁进行插入操作，并且对查询性能要求不高，可以考虑不使用索引。因为频繁的插入会导致索引的维护成本增加，可能会降低写入性能。
内存表：对于完全在内存中操作的临时表或缓存表，由于数据量较小且无磁盘访问开销，通常不需要使用索引。
不常被查询的列：如果某个列或多个列很少被用作查询条件，或者在查询中并不起重要的作用，那么对这些列创建索引可能没有太大的意义。
数据完全有序：如果表的数据已经按照查询所需的顺序有序存储，或者查询通常需要遍历整个表，那么创建索引可能对查询性能带来较少的改进。
数据分布均匀：如果数据分布均匀，查询时需要返回大部分或全部数据，那么使用索引可能并不能提供明显的查询性能提升。
查询使用了复杂的函数或表达式：对于包含复杂函数、表达式或类型转换的查询条件，索引可能无法直接应用，因为索引要求查询条件与索引列的数据类型和格式完全匹配。

2.6.何时失效

数据分布不均匀：如果数据分布不均匀，索引可能会失去其有效性。例如，当索引列中的大部分值都相同或非常接近时，查询可能需要扫描大部分索引或表数据，而无法利用索引的快速访问特性。

数据更新频繁：当索引所在的表频繁进行插入、更新或删除操作时，索引会面临维护成本。特别是在大量数据更新的情况下，索引更新可能会导致性能下降，造成索引失效。

查询条件不适合索引：如果查询条件与索引定义不匹配，索引可能无法被利用。例如，索引是针对列A的，但查询条件使用了与列A无关的其他列，这样的查询可能无法使用索引，而会导致索引失效。

使用了函数或表达式：如果查询条件中使用了函数、表达式或类型转换等操作，索引可能无法被使用。这是因为索引要求查询条件与索引列的数据类型和格式完全匹配。

范围查询过大：对于范围查询，如果所涉及的范围较大，索引可能会失去效果。例如，查询一个很大的日期范围或文本模糊查询，这样的查询可能需要扫描大量索引或表数据，从而导致索引失效。

统计信息不准确：索引的选择和使用依赖于数据库统计信息，包括数据分布、列基数和索引选择性等。如果统计信息不准确或过时，优化器可能做出错误的索引选择，导致索引失效。

为了减少索引失效的可能性，可以采取以下措施：

定期更新统计信息：保持数据库中的统计信息是最新和准确的，以帮助优化器正确选择索引。
谨慎使用索引：根据具体的查询需求和数据访问模式，仔细选择和设计索引，避免过多或不必要的索引。
避免频繁的数据更新：对于需要频繁插入、更新或删除的表，需要权衡考虑索引的更新成本和性能影响。
避免使用过多的函数或表达式：尽量避免在查询条件中使用函数、表达式或类型转换等操作，以保证索引的使用效果。
优化查询语句：通过优化查询语句的结构和条件，使其能够更好地利用索引，提高查询性能。

总之，索引的失效可能是多种因素共同作用的结果。在设计和使用索引时，需要综合考虑数据分布、数据访问模式、查询需求和性能等因素，合理选择和管理索引，以提高数据库的查询性能和效率。

2.7.索引的分类

2.7.1.普通索引

是最基本的索引，它没有任何限制
普通索引是一种普遍且基本的索引类型，在一个或多个列上创建。它使用B树或B+树数据结构来存储索引值和对应的数据位置，用于提高查询效率。

创建普通索引的语法如下：

CREATE INDEX index_name
ON table_name(column_name);

例如，在名为employees的表的last_name列上创建普通索引，可以使用以下语句：

CREATE INDEX idx_employees_last_name
ON employees(last_name);

2.7.2.唯一索引

唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
唯一索引是普通索引的一种特殊形式。它要求索引列的值是唯一的，可以用来加速唯一性约束的验证。在创建唯一索引时，数据库系统会自动检查索引列的值是否重复。

创建唯一索引的语法如下：

CREATE UNIQUE INDEX index_name
ON table_name(column_name);

例如，在名为students的表的student_id列上创建唯一索引，可以使用以下语句：

CREATE UNIQUE INDEX idx_students_id
ON students(student_id);

2.7.3.主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。
主键索引是一种特殊的唯一索引，它由主键列创建。主键是表中的唯一标识符，每个主键值都必须唯一且非空。主键索引可用于快速定位表中的特定行。

在创建表时，可以通过指定主键来自动创建主键索引。以下是创建主键索引的语法示例：

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    PRIMARY KEY (column1)
);

例如，在名为orders的表中，创建一个主键索引并指定order_id列为主键：

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    ...
    PRIMARY KEY (order_id)
);

2.7.4.组合索引

组合索引指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。
组合索引是建立在多个列上的索引。它将这些列的值组合在一起，以建立索引。组合索引可提高多列查询的性能，特别是在涉及这些列的等值查询或范围查询时。

创建组合索引的语法如下：

CREATE INDEX index_name
ON table_name(column1, column2, ...);

例如，在名为products的表上创建组合索引，包含category和price两列：

CREATE INDEX idx_products_category_price
ON products(category, price);

2.7.5.全文索引

全文索引用于文本内容的搜索和匹配，支持全文搜索和模糊查询。
全文索引的数据结构常见的有倒排索引等。
全文索引用于在文本数据中进行关键字搜索。它能够快速定位到包含指定关键字的文档或文本片段。全文索引适用于非结构化的文本数据。

2.7.6.位图索引

位图索引使用位图来表示数据的存在或缺失，适用于低基数（基数指不同值的数量）列。
位图索引可以快速地执行位运算来进行多列条件的过滤。
位图索引是一种特殊的索引类型，适用于列的基数（不同值的数量）相对较小的情况。它使用位图的方式来表示每个列值的存在或缺失，加速对该列的查询操作。

2.7.7.唯一索引

唯一索引保证索引列中的值是唯一的，用于强制实施唯一性约束。

以下是创建唯一索引的SQL语法：

CREATE UNIQUE INDEX index_name
ON table_name(column_name);

其中，index_name为索引的名称，table_name为表的名称，column_name为要创建唯一索引的列名。

举个例子，我们可以在名为students的表的student_id列上创建唯一索引，如下所示：

CREATE UNIQUE INDEX idx_students_id
ON students(student_id);

以上语句将在students表的student_id列上创建名为idx_students_id的唯一索引。这将确保students表中的每个学生ID都是唯一的，不存在重复的学生ID。

此外，还有许多其他类型的索引和变种，如前缀索引、多列索引、稀疏索引等。不同的数据库管理系统可能支持不同类型的索引，具体的索引选择取决于数据特性、查询需求和系统性能要求等因素。

三.数据的导入导出

3.1 基础导入

第一步:在想要导入数据的数据库中进行选中该数据库，右键选中运行SQL文件；

如图操作:

第二步:点击' 运行SQL文件 '后选择要导入的SQL文件路径所在；

如图操作:

第三步:点击开始即可，显示如图已完成导入；

如图操作:

3.2 基础导出

第一步:在想要导出的数据库或数据表选中" 转储SQL文件 " ，再点击" 结构和数据 "

如图操作:

第二步:选中完要导出的路径后，点击保存即可

如图操作:

3.3 优化导入

进行优化: 在安装的mysql路径下，找到 .ini 的文件，在[mysqld]下面进行增加双引号中的

" secure_file_priv=D: " 这一行代码，意思是说mysql可以访问D盘的权限，相当于授权的意思

如图所示:

完成以上操作之后，在进行MySQL服务的重启访问，之后即可完成优化。

优化后的导入:

新建查询输入以下代码即可

load data infile 'D:/t_dd_log.sql' into table 
t_log(id,ip,userid,moduleid,content,createdate,url);

其中 D:/t_dd_log.sql 是你要导入SQL文件的路径，导入数据到 t_log 表中，(id,ip,userid,moduleid,content,createdate,url); 这里就是 t_log 中的字段(属性)名称

如图:

3.4 优化导出

导出的话，只需输入以下代码即可

select * from t_log into outfile 'D:/t_dd_log.sql';

其中 t_log 是要导出的数据表名称，而D:/t_dd_log.sql是导出到D盘路径上，文件名称为t_dd_log.sql 。如图所示表示完成:

这篇Mysql视图及索引就到这了，各位大佬多多提取意见，小编愿意受教！！！！

✘君临沂

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
2
评论
Mysql视图及索引

视图（View）是数据库中的一个虚拟表，它是基于一个或多个实际表查询的结果集。视图与实际表类似，可以被查询、插入、更新和删除数据，但它本身并不存储任何数据，只是存储了对数据的定义和查询规则。通过创建视图，可以对数据库中的数据进行一定程度的逻辑组织和封装，以简化复杂的查询操作。视图通常用来隐藏底层表的复杂性，提供一个简单和易于理解的访问接口。索引（Index）是数据库中用于快速定位和访问数据的数据结构。它类似于图书馆的书目索引，通过索引可以加快对数据表中特定行的查找速度。索引。
复制链接

扫一扫