MySQL的视图和索引（调优）

MySQL 视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的
数据库中只存放了视图的定义，并没有存放视图中的数据，这些数据都存放在定义视图查询所引用的真实表中。使用视图查询数据时，数据库会从真实表中取出对应的数据。因此，视图中的数据是依赖于真实表中的数据的。一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变
视图可以从原有的表上选取对用户有用的信息，那些对用户没用，或者用户没有权限了解的信息，都可以直接屏蔽掉，作用类似于筛选。这样做既使应用简单化，也保证了系统的安全

语法
CREATE VIEW <视图名> AS <SELECT语句>
注意：

修改：

二.视图和数据表的区别

1. 数据存储：
- 数据表：数据表是数据库中的实体，它以行和列的形式持久存储数据，每个表都有自己的物理存储位置
- 视图：视图是虚拟的，不存储实际的数据，而是根据查询定义创建的虚拟表
2. 数据内容：
- 数据表：数据表包含实际的数据，可以直接对表进行插入、更新和删除等操作，并且数据的变化会直接反映在表中
- 视图：视图是基于查询结果的，它并不存储实际的数据内容。视图中的数据来自于基本表，通过查询规则和字段选择生成
3. 数据更新：
- 数据表：可以直接对表进行插入、更新和删除等操作，数据的变化会直接反映在表中，并且会触发相应的约束和触发器
- 视图：对视图进行的更新操作通常是基于关联的基本表，而不是直接对视图进行修改。对视图进行的更新操作可能会引起基本表数据的变化
4. 数据访问：
- 数据表：可以直接对表进行查询操作，使用表名和列名来访问和操作数据
- 视图：可以像对表一样对视图进行查询操作，使用视图名和列名来访问和操作数据。视图隐藏了底层的复杂查询逻辑
5. 物理实现：
- 数据表：数据表具有实际的物理存储，存储在数据库中的某个位置，并且可以有索引等物理结构来提高查询性能
- 视图：视图并不具有实际的物理存储，它是一个动态查询结果，在查询时根据基本表的数据生成

⭐⭐总的来说，数据表是存储实际数据的实体，可以进行直接的数据操作和修改，而视图是基于查询结果的虚拟表，可以简化数据访问、提供数据安全性和逻辑灵活性。视图通过定义特定的查询规则和字段选择来过滤和操作数据，提供了一种抽象和简化的方式来获取和处理数据

三.为什么要使用视图

1. 简化数据访问：

概念：

视图可以隐藏底层复杂的查询逻辑，用户只需使用简单的视图查询语句即可获取所需的数据结果。它提供了一个简单、直观的接口，简化了数据访问的过程

实例：

假设有一个包含订单信息的表和客户信息的表。通过创建一个视图，将这两个表关联起来，并且只选择需要的列，可以大大简化查询操作。例如，可以创建一个视图，显示每个订单的订单号、客户姓名和订单日期。这样，当需要获取订单的相关信息时，只需使用简单的视图查询语句，而无需编写复杂的联接查询语句

2. 数据安全性：

概念：

通过视图，可以限制用户只能访问特定的列或行，提高数据的安全性和访问权限控制。可以基于视图设置权限，确保只有具有相应访问权限的用户可以查看和操作数据

实例：

在一个企业的员工管理系统中，可能存在一个包含敏感信息的员工表，如薪水、社保号等。通过创建一个视图，选择需要展示的非敏感字段（如姓名、职位等），将敏感字段隐藏起来，可以限制未授权用户对敏感数据的访问。这样，只有具有访问权限的用户可以通过视图访问和获取受限制的数据，增强了数据的安全性

3. 数据抽象和封装：

概念：

视图可以将多个关联表的数据进行组合，提供更加直观和高层次的数据抽象，简化数据操作和使用方式。通过视图，可以隐藏底层的数据结构，用户只需要关注所需的数据接口

实例：

在一个电商平台的数据库中，存在产品信息表、库存信息表和订单信息表。为了提供更方便的数据访问方式，可以创建一个视图，将产品信息、库存信息和订单信息联接起来，并选择需要的列。这样，应用程序可以直接通过视图查询来获取特定产品的库存情况和相关订单信息，而无需关注底层表的复杂关系和查询逻辑

4. 逻辑数据独立性：

概念：

通过使用视图，可以将应用程序与具体的物理表结构解耦。当底层表结构发生变化时，只需更新视图定义，而无需修改应用程序。这提供了更好的灵活性和可维护性

实例：

假设一个博客系统中，有一个包含文章信息的表和一个包含评论信息的表。可以创建一个视图，联接这两个表，并提供一个按时间顺序展示的内容视图。当底层表结构发生变化时，只需更新视图的定义，而不会影响到应用程序的查询操作。这样可以提高逻辑数据独立性，简化系统的维护和迁移过程

5. 查询性能优化：

概念：

视图可以预先将复杂查询结果计算并存储，以提高查询性能。视图可以创建索引，可以缓存结果，通过优化查询计划来提高数据检索效率

6. 代码复用：

概念：

视图可以将常用的查询逻辑封装为一组可复用的视图，减少编写重复查询的代码量。这样可以提高开发效率和代码的可维护性

⭐⭐总的来说，视图提供了一种简化、安全、抽象的数据访问方式，可以提高数据访问的效率、数据安全性和应用程序的灵活性。通过使用视图，可以将复杂的查询逻辑隐藏起来，简化数据操作，提高代码复用性，并降低维护成本

四.什么时候该使用视图（常见使用场景）

1. 数据权限控制：

- 示例：在一个企业内部的人力资源系统中，有一个包含所有员工信息的表。根据不同的用户角色和权限，可以创建不同的视图，限制用户只能看到他们负责的部门或团队的员工信息，而无法访问其他部门的数据。这样可以有效控制数据的访问权限，提高数据的安全性

2. 数据报表和分析：

- 示例：在一个销售管理系统中，有一个包含订单信息的表。可以创建一个视图，对订单数据进行筛选、分组、计算等操作，生成各种类型的报表，如销售额统计、客户购买趋势分析等。这样，用户可以通过简单的查询视图来获取需要的报表数据，而无需编写复杂的查询语句

3. 数据整合和补充：

- 示例：在一个电商平台中，有多个数据库表存储了产品信息、库存信息和销售信息。可以创建一个视图，将这些表联接起来，以提供更全面的产品信息。例如，可以创建一个视图，包含产品名称、价格、库存量和销售数量等信息，方便用户查看产品的完整信息

4. 数据规范化和简化：

- 示例：在一个多表关联的数据库中，有一些常用的查询操作需要经常被使用，可以为这些查询操作创建视图，以简化查询语句并提高可读性。例如，可以创建一个视图，将多个表关联起来，并根据特定条件筛选出符合条件的数据，使得查询时只需使用简单的视图查询语句来获取所需结果

⭐⭐视图的使用场景是多样的，可以根据具体的数据需求和应用要求进行灵活的定义和使用，以提高数据操作的效率和易用性

五.索引是什么

索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配
可以把索引比作新华字典的音序表。例如，要查“库”字，如果不使用音序，就需要从字典的 400 页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从 10 多页的音序表中直接查找。这样就可以大大节省时间

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

-------------------------------------------------------------------------------------------------------------------------

创建索引
 create index index_name on table(column)
修改表结构方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
删除索引
DROP INDEX index_name ON table

六.为什么要使用索引

1. 提高查询性能：

概念：

通过使用索引，数据库可以更快地定位和访问满足特定条件的数据行，从而提高查询的速度。索引可以将全表扫描的开销减少为直接定位索引中的数据行的开销，特别是在大型数据集上能体现出明显的性能提升

实例：

例如，考虑一个包含百万行数据的用户表，需要查询用户名为"John"的用户信息。如果没有索引支持，数据库需要逐行扫描整个表来找到匹配的记录。但如果在用户名列上创建了索引，根据索引的引导，数据库可以很快定位到匹配条件的行，大大提高查询速度
文字的说服力显然没有代码展示的直观，那么话不多说，直接上展示！！

2. 加速排序操作：

概念：

索引不仅能加速查询操作，还可以加速排序操作。当查询需要对结果进行排序时，索引可以按照索引列的顺序进行快速排序，减少排序所需的时间和资源消耗

实例：

例如，考虑一个包含大量订单信息的订单表，需要按照订单金额对订单进行排序。如果在金额列上创建了索引，数据库可以利用索引的有序性，在排序操作中避免对整个表的数据进行排序，提高排序性能

3. 支持连接操作：

概念：

索引可以加速连接操作，提高多表查询的效率。通过索引，数据库可以快速定位到需要连接的行，减少连接操作的开销

实例：

例如，考虑一个包含订单信息和客户信息的订单表和客户表，需要查询某个客户的订单信息。在客户表的客户ID列和订单表的客户ID列上创建索引，可以快速定位到满足条件的订单信息，提高连接查询的效率

4. 保证数据完整性：

概念：

索引可以定义唯一性约束（UNIQUE 索引）或主键约束（PRIMARY KEY 索引），保证表中的数据不重复或唯一

实例：

例如，在用户表的邮箱列上创建唯一索引，可以确保每个用户都拥有唯一的邮箱地址。当插入或修改数据时，如果违反了唯一性约束，数据库会阻止操作并报错，确保数据完整性

⭐⭐总结来说，索引的优势包括提高查询性能、加速排序操作、支持连接操作和保证数据完整性。通过合理创建和维护索引，可以显著提高数据库的查询效率和操作性能，提升应用程序的响应速度

七.索引的不足之处

1.占用存储空间：

概念：

索引需要占用额外的存储空间，特别是在大型表上创建复合索引时占用的空间更多。这会增加数据库的存储需求

实例：

例如，如果在一个包含大量数据的日志表上创建了多个列的复合索引，可能会消耗大量的存储空间

2.增加写操作的开销：

概念：

当对索引列进行插入、更新或删除操作时，数据库需要维护索引的结构，这会增加写操作的开销

实例：

例如，如果频繁地向一个包含索引的表中插入新数据，索引的维护成本可能会显著增加

3.索引维护开销：

概念：

索引需要定期进行维护和优化，以确保索引的有效性和性能。当表的数据发生变化时，索引的维护工作可能会导致额外的开销

实例：

例如，当在一个包含大量数据的表上删除数据时，可能需要重建索引来回收空间或提高性能

4.可能导致查询性能下降：

概念：

过多或不合理的索引可能会导致查询性能下降。比如，在频繁更新的表上创建过多的索引，会增加更新操作的成本和延迟

实例：

例如，如果在一个经常有数据变动的表上创建了大量的索引，可能会导致写操作的性能下降

八.索引在什么情况下会失效

1. 不使用索引列进行查询：
索引只能在使用索引列（索引的最左列或最左前缀）作为查询条件时才能起作用。如果查询中没有使用索引列，那么索引将无法生效，数据库将会执行全表扫描
2. 对索引列进行了函数操作：
对索引列进行函数操作（如函数、运算符、类型转换等）会导致索引失效，因为函数操作可能会改变列的值，使得索引无法直接匹配查询条件
3. 使用了索引列的部分进行范围查询：
一般情况下，当使用索引列进行范围查询（如`>`、`<`、`BETWEEN`等）时，索引仍然可以起作用。但如果在范围查询中使用了索引列的部分进行了运算或函数操作，将导致索引失效
4. 数据表过小：
如果数据表的大小非常小，如只有几行数据，数据库通常会选择忽略索引，并直接进行全表扫描来获取数据，因为通过索引的查找开销可能会大于直接全表扫描的开销
5. 索引选择性太低：
选择性是指索引列不同取值的唯一性程度。如果索引列的选择性非常低，即索引列的取值重复较多，那么索引的效果将降低，数据库可能会选择放弃使用索引而进行全表扫描
6. 统计信息不准确：
数据库依赖于统计信息来估计查询成本，选择执行计划。如果统计信息不准确或过时，可能会导致数据库做出错误的执行计划选择，使得索引失效
7. 数据页分散程度高：
如果索引的数据页分散程度高，即索引中的数据在物理上存储非常分散，需要进行大量的磁盘IO操作来获取索引数据，可能会导致索引效率低下
8.like以通配符%开头索引失效

九.索引的类别

1. 唯一索引（Unique Index）：
保证索引列的唯一性，即索引列的值在整个表中是唯一的
语法：
CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);
2. 主键索引（Primary Key Index）：
用于定义表的主键，确保索引列的唯一性，并且不允许为空
语法：
CREATE TABLE table_name (
       column1 datatype PRIMARY KEY,
       column2 datatype,
       ...
   );
或者：
  ALTER TABLE table_name ADD PRIMARY KEY (column1);
3. 聚集索引（Clustered Index）：

物理上将数据行按照索引的顺序进行存储，并且一个表只能有一个聚集索引。在大部分数据库管理系统中，主键索引默认就是聚集索引
创建主键索引时，聚集索引将会被自动创建
4. 非聚集索引（Non-clustered Index）：
在单独的数据区域存储索引的数据，并在叶子节点上保存指向实际数据行的引用。一个表可以有多个非聚集索引
语法：
CREATE INDEX index_name ON table_name (column1, column2, ...);
5. 全文索引（Full-text Index）：
用于全文搜索，可以在文本数据（如文章、描述等）中快速找到匹配的词语或短语
语法：
 CREATE TABLE table_name (
       column1 datatype,
       column2 datatype,
       ...
       FULLTEXT (column1, column2, ...)
   );
或者：
ALTER TABLE table_name ADD FULLTEXT (column1, column2, ...);
6. 复合索引（Composite Index）：
由多个列组合而成的索引，可以对多个列进行查询和排序，提高多列条件查询的性能
语法：
CREATE INDEX index_name ON table_name (column1, column2, ...);

十.索引的类型

1. B-树索引（B-Tree Index）：
是最常见的索引类型，在MySQL中被广泛使用。B-树索引适用于等值查询、范围查询和排序，它采用平衡树的数据结构，可以高效地支持数据的快速查找
2. 哈希索引（Hash Index）：
基于哈希表实现的索引，适用于等值查询，对于范围查询和排序性能较差。哈希索引将数据存储为哈希值和链表的形式，可以在O(1)的时间复杂度内找到指定的值
3. 有序索引（Ordered Index）：
在某些存储引擎中，也可以称为聚集索引（Clustered Index）。有序索引是按照数据行的逻辑顺序存储数据的索引，通常与数据行存储在一起，因此可以加快范围查询和排序的性能
4. 全文索引（Full-text Index）：
用于全文搜索，在MySQL中可以使用全文索引进行对文本数据的高效搜索，支持关键字查询、短语匹配和布尔运算等
5. 空间索引（Spatial Index）：
用于处理空间数据类型（如地理信息、坐标等）的索引，提供高效的空间查询和分析功能
6. 前缀索引（Prefix Index）：
对索引列的前缀进行索引，适用于长字符串类型的列或者大文本字段，可以减少索引的存储空间和提高查询性能

💡💡辉辉小贴士：索引的最左原则是什么意思

概念

索引的最左前缀匹配原则（Leftmost Prefix Match）是指，在一个复合索引（Composite Index）中，只有最左边的列进行查询时，索引才能被有效利用。具体来说，当一个复合索引包含多个列时，查询必须满足以下条件才能充分利用索引
1. 查询条件中使用了索引的最左列，并且是连续的。也就是说，查询语句中的查询条件必须以索引的最左列开始，并且不能跳过这个最左列
2. 查询条件中不使用索引中的其他列。如果查询需要使用索引的其他列进行过滤或排序，但这些列不是最左列或不是连续的，则索引将无法利用它们

-------------------------------------------------------------------------------------------------------------------------
例子：
假设有一个复合索引包含两列 `(column1, column2)`，那么以下查询将充分利用索引（这是因为查询条件是索引的最左列，并且是连续的）
SELECT * FROM table WHERE column1 = 'value'
而以下查询无法充分利用索引(这是因为查询条件不是最左列，违反了最左前缀匹配原则)
SELECT * FROM table WHERE column2 = 'value'
⭐⭐需要注意的是，虽然最左前缀匹配原则在一些情况下限制了索引的使用，但对于满足条件的查询，利用最左前缀匹配原则可以提高查询效率，因为数据库可以利用索引的有序性和前缀匹配来快速定位满足条件的数据行，减少不必要的数据访问和扫描。因此，在设计复合索引时，应根据实际查询的需求和最左前缀匹配原则来确定索引的顺序

💡💡辉辉小贴士：为什么like以通配符%开头索引会失效

在MySQL中，当使用`LIKE`操作符并以通配符`%`开头进行模糊查询时，大多数情况下索引会失效。这是因为通配符`%`开头的模糊查询无法使用B树索引的前缀匹配机制

具体原因如下：

1. 索引的有序性：
在B树索引中，数据是按照索引列的顺序进行存储和排序的。这使得数据库可以快速定位满足查询条件的数据行。但是，通配符`%`作为查询条件的开头时，查询的范围变得无限大，索引的有序性无法发挥作用
2. 前缀匹配机制失效：
B树索引可以有效利用索引列的前缀匹配，加速查询速度。例如，当使用`LIKE 'abc%'`进行查询时，索引可以直接定位到以"abc"开头的数据行。但是，如果查询条件以`%`开头，如`LIKE '%abc'`，则无法利用索引的前缀匹配机制，需要遍历整个索引来找到匹配的数据行

由于通配符`%`开头的模糊查询无法有效利用索引的有序性和前缀匹配机制，数据库会选择进行全表扫描，逐行检查每条记录，从而导致索引失效，查询效率降低