MySQL，其他部分知识点补充

谁借莪１个温暖的怀抱￠ 2024-03-17 17:05 62阅读 0赞

## 一.**视图** ##

### 1.**什么是视图?** ###

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

### 2. **视图与数据表的区别** ###

>  *  视图不是数据库中真实的表，而是一张虚拟表，其**结构和数据是建立在对数据中真实表的查询基础上的。**
>  *  存储在数据库中的查询操作 SQL 语句定义了视图的内容，列数据和行数据来自于视图查询所引用的实际表，引用视图时**动态生成这些数据**。
>  *  视图没有实际的物理记录，不是以数据集的形式存储在数据库中的，它所对应的数据实际上是存储在视图所引用的真实表中的。
>  *  视图是数据的窗口，而表是内容。表是实际数据的存放单位，而视图**只是以不同的显示方式展示数据，其数据来源还是实际表**。
>  *  视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些 SQL 语句的集合。从安全的角度来看，视图的数据安全性更高，使用视图的用户不接触数据表，不知道表结构。
>  *  视图的建立和删除只影响视图本身，不影响对应的基本表。

### 3.视图优点 ###

> **1)定制用户数据，聚焦特定的数据**
> 
> 在实际的应用过程中，不同的用户可能对不同的数据有不同的要求。
> 
> 例如，当数据库同时存在时，如学生基本信息表、课程表和教师信息表等多种表同时存在时，可以根据需求让不同的用户使用各自的数据。学生查看修改自己基本信息的视图，安排课程人员查看修改课程表和教师信息的视图，教师查看学生信息和课程信息表的视图。
> 
> **2 简化数据操作**
> 
> 在使用查询时，很多时候要使用聚合函数，同时还要显示其他字段的信息，可能还需要关联到其他表，语句可能会很长，如果这个动作频繁发生的话，可以**创建视图来简化操作**。
> 
> **3 提高数据的安全性**
> 
> 视图是虚拟的，物理上是不存在的。可以**只授予用户视图的权限**，**而不具体指定使用表的权限，来保护基础数据的安全。**
> 
> **4 共享所需数据**
> 
> 通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次。
> 
> **5 更改数据格式**
> 
> 通过使用视图，可以重新格式化检索出的数据，并组织输出到其他应用程序中。
> 
> **6) 重用 SQL 语句**
> 
> 视图提供的是对查询操作的封装，本身不包含数据，所呈现的数据是根据视图定义从基础表中检索出来的，如果基础表的数据新增或删除，视图呈现的也是更新后的数据。视图定义后，编写完所需的查询，可以方便地重用该视图。

### 4.用法 ###

#### 4.1语法： ####

CREATE VIEW <视图名> AS <SELECT语句>

#### 4.2语法说明 ####

> 语法说明如下。
> 
>  *  `<视图名>`：指定视图的名称。该名称在数据库中**必须是唯一的，不能与其他表或视图同名。**
>  *  `<SELECT语句>`：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。
> 
> 对于创建视图中的 SELECT 语句的指定存在以下限制：
> 
>  *  用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。
>  *  SELECT 语句不能引用系统或用户变量。
>  *  SELECT 语句不能包含 FROM 子句中的子查询。
>  *  SELECT 语句不能引用预处理语句参数。

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

## 二.索引 ##

### 1.什么是索引？ ###

> 在MySQL中，索引是一种用于**提高查询效率的数据结构**。它是在数据库表中一个或多个列上创建的结构，可以加速数据的检索和查询操作。
> 
> MySQL支持多种类型的索引，包括：
> 
> 1. B树索引：大多数MySQL索引都是基于B树（B-tree）数据结构实现的。它适用于范围查询、排序和等值查询，能够在较大数据集上快速定位数据。  
> 2. 唯一索引：用于确保索引列的数值是唯一的，通常用于约束表中的唯一性。  
> 3. 主键索引：是一种特殊的唯一索引，用于唯一标识表中的每一行数据。主键索引通常会自动创建。  
> 4. 全文索引：用于全文搜索场景，可以对文本列进行快速搜索和匹配。  
> 5. 哈希索引：适用于只进行等值查询的场景，哈希索引可以快速定位数据，但不支持范围查询。
> 
> 创建索引可以通过\`CREATE INDEX\`语句在表中的一个或多个列上完成。同时，针对常见的查询需求，MySQL还会自动创建一些隐式索引，如主键索引和唯一索引。
> 
> 索引的好处是加快了查询速度，因为数据库系统不需要扫描整个表来获取特定数据。相反，它可以利用索引的结构更快地定位到所需的数据。然而，索引也需要占用额外的存储空间，并导致插入、更新和删除操作的性能开销。因此，需要在使用索引时平衡查询性能和数据更新的需求。
> 
> 在设计数据库时，常见的需要创建索引的情况包括：
> 
> \- 主键字段：用于唯一标识每一行数据的字段。  
> \- 外键字段：用于关联其他表的字段。  
> \- 经常被查询的字段：经常出现在查询条件中的字段。  
> \- 经常用于排序和分组的字段。
> 
> 需要注意的是，过多或不必要的索引可能会导致性能下降和存储空间的浪费。因此，在创建索引时需要谨慎考虑实际查询需求和数据更新的频率。
> 
> 总结来说，MySQL中的索引是一种用于提高查询效率的数据结构，通过快速定位和访问数据来加速查询操作。适当使用索引可以显著提高数据库的性能。

### 2. 为什么要使用索引？ ###

> 使用索引的主要目的是**提高数据库的查询性能和响应速度**。下面列举了一些使用索引的重要原因：
> 
> 1. **加快数据检索**：索引允许数据库引擎直接定位到符合查询条件的数据行，而不需要扫描整个表。这可以显著减少查询所需的时间和资源，尤其是在大型数据库表中查询大量数据时。
> 
> 2. **提高查询性能**：通过快速定位到数据，索引可以大幅度减少查询的执行时间，提高查询性能。特别是在频繁执行的查询上，索引的使用可以显著加快查询结果的返回。
> 
> 3**. 减少磁盘I/O操作**：索引将数据按照特定的结构组织，并存储在磁盘上。通过使用索引，数据库可以最小化需要读取的数据块数量，从而减少了磁盘I/O操作的次数。
> 
> 4. **支持排序和分组操作**：索引可以为常见的排序和分组操作提供优化。通过使用索引，数据库引擎可以快速定位并按照指定的排序顺序或分组字段对数据进行处理，减少了排序和分组操作的时间消耗。
> 
> 5. **提高并发性能**：在并发数据库环境中，多个查询同时执行可能会导致性能问题。使用合适的索引可以减少锁定和争用，提高并发性能，从而使多个查询能够在较短的时间内完成。
> 
> 需要注意的是，索引的创建和维护需要消耗一定的计算和存储资源，并且在对表进行插入、更新和删除操作时可能会引入额外的性能开销。因此，在设计索引时需要权衡查询性能和数据更新的频率，避免过多或不必要的索引。
> 
> 总结一下：使用索引可以加快数据库的查询速度、提高查询性能、减少磁盘I/O操作，并支持排序和分组操作。合理使用索引可以显著提高数据库的效率和响应性能。  
>

### 3.优缺点 ###

> 索引有其明显的优势，也有其不可避免的缺点。
> 
> **优点**
> 
> 索引的优点如下：
> 
>  *  通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
>  *  可以给所有的 MySQL 列类型设置索引。
>  *  可以大大加快数据的查询速度，这是使用索引最主要的原因。
>  *  在实现数据的参考完整性方面可以加速表与表之间的连接。
>  *  在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间
> 
> **缺点**
> 
> 增加索引也有许多不利的方面，主要如下：
> 
>  *  创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
>  *  索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
>  *  当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。
> 
> > 索引可以提高查询速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。

### 4. 什么时候不适用索引？ ###

>  *  表记录太少
>  *  经常增删改的表
>  *  数据重复且分布均匀的表字段，只应该为经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）
>  *  频繁更新的字段不适合创建索引(会增加IO负担)
>  *  where条件里用不到的字段不创建索引

### 5.索引何时失效 ###

>  *  like以通配符%开头索引失效
>  *  当全表扫描比走索引查询的快的时候，会使用全表扫描，而不走索引
>  *  字符串不加单引号索引会失效
>  *  where中索引列使用了函数（例如substring字符串截取函数）
>  *  where中索引列有运算（用了< or > 右边的索引会失效，用<= or >= 索引不会失效）
>  *  is null可以走索引，is not null无法使用索引（取决于某一列的具体情况）
>  *  复合索引没有用到左列字段（最左前缀法则，如果没用用到最左列索引，或中间跳过了某列有索引的列，索引会部分失效）
>  *  条件中有or，前面的列有索引，后面的列没有，索引会失效。想让索引生效，只能将or条件中的每个列都加上索引

### 6.索引分类 ###

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

#### 6.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
    ```

#### 6.2.唯一索引 ####

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

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

#### 6.3.主键索引 ####

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。简单来说：主键索引是加速查询 + 列值唯一（不可以有null）+ 表中只有一个。

CREATE TABLE mytable( 
        ID INT NOT NULL, 
        username VARCHAR(16) NOT NULL, 
        PRIMARY KEY(ID) 
    );

#### 6.4.组合索引 ####

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

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

#### 6.5.全文索引 ####

*  **概念**

> 全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。
> 
> fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。
> 
> 值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

*  **版本支持**

Mysql 5.6之前版本，只有MyISAM支持全文索引，5.6之后，Innodb和MyISAM均支持全文索引。另外，只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

*  **案例**

创建数据表`t_articles`

CREATE TABLE t_articles (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        body TEXT,
        FULLTEXT (title,body)
    );
    给现有的article表的title和body字段创建全文索引，索引名称为fulltext_article
    
    ALTER TABLE t_articles ADD FULLTEXT INDEX fulltext_article (title, body);
    导入测试数据
    
    INSERT INTO t_articles VALUES
    (NULL,'MySQL Tutorial', 'DBMS stands for DataBase ...'),
    (NULL,'How To Use MySQL Efficiently', 'After you went through a ...'),
    (NULL,'Optimising MySQL','In this tutorial we will show ...'),
    (NULL,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    (NULL,'MySQL vs. YourSQL', 'In the following database comparison ...'),
    (NULL,'MySQL Security', 'When configured properly, MySQL ...');
    
    
    INSERT INTO t_articles VALUES
    (NULL,'abc', 'zs red blue ...'),
    (NULL,'xyz', 'ls white');
    
    
    INSERT INTO t_articles VALUES
    (NULL,'aaa', 'zs red blue ...'),
    (NULL,'bbb', 'ls white red');\

*  **示例一：自然语言模式（IN NATURAL LANGUAGE MODE，默认模式）**

SELECT * FROM t_articles where MATCH (title,body) AGAINST ('database')

*  **示例二：布尔搜索模式（IN BOOLEAN MODE）**

<table> 
 <thead> 
  <tr> 
   <th>符号</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>+</td> 
   <td>必须包含此字符串</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>必须不包含此字符串</td> 
  </tr> 
  <tr> 
   <td>""</td> 
   <td>双引号内作为整体不能拆词</td> 
  </tr> 
  <tr> 
   <td>&gt;</td> 
   <td>提高该词的相关性，查询的结果靠前</td> 
  </tr> 
  <tr> 
   <td>&lt;</td> 
   <td>降低该词的相关性，查询的结果靠后</td> 
  </tr> 
  <tr> 
   <td>*</td> 
   <td>通配符，只能接在词后面</td> 
  </tr> 
 </tbody> 
</table>

包含red或者blue
    SELECT * FROM t_articles where MATCH (title,body) AGAINST ('red blue' IN BOOLEAN MODE );
    #包含red，但是必须包含blue
    SELECT * FROM t_articles where MATCH (title,body) AGAINST ('red +blue' IN BOOLEAN MODE );
    #包含red，但是一定不能包含blue
    SELECT * FROM t_articles where MATCH (title,body) AGAINST ('red -blue' IN BOOLEAN MODE );

> 注意：长度超过2的英文关键字才会生效

*  **示例三：中文搜索**

5.6之后MySQL自带ngram解析器，可以解析中日韩三国文字，如果不使用ngram解析器，则MySQL默认使用空格与符号作为分隔符（对于英文自然够用了，但对于中日韩文字就不好用了，所以才需要ngram解析器）。

**查看数据库版本**

select version();

**查看数据库引擎**

show engines;

**修改MySQL全文检索最小许可字符**

[mysqld]
    ft_min_word_len = 2

> 全文检索的最小许可字符(默认4，通过 SHOW VARIABLES LIKE 'ft\_min\_word\_len' 可查看)，中文通常是两个字就是一个词，所以做中文的话需要修改这个值为2最好。services.msc

**注意：必须要重启Mysql服务**

**创建全文索引并设置ngram解析器**

>     ALTER TABLE t_book
>     ADD FULLTEXT INDEX fulltext_bookname_type (bookname, booktype) WITH PARSER ngram;
> 
> 基于t\_book表中的bookname和booktype字段创建全文索引并设置ngram解析器，让其支持中文检索。

**关键词搜索：**

SELECT * FROM t_book where MATCH (bookname, booktype) AGAINST ('三国')

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

## 三.数据库备份和恢复 ##

### 1.数据的导入导出 ###

> \- DELETE/TRUNCATE
> 
> \-- 花费的时间：10.597s
> 
> DELETE FROM t\_log;
> 
> \-- 花费的时间：0.121s
> 
> TRUNCATE TABLE t\_log;
> 
> \-- 1.使用工具，类似Sqlyog、Navicat等导入导出数据。
> 
> \-- 使用Navicat工具导入 t\_log 共耗时 45s；
> 
> \-- 使用mysqldump导入 包含t\_log表的整个数据库 共耗时 20s；
> 
> \-- 使用单表数据导入load data infile 的方式，共耗时 7.502s

### 2.使用mysqldump导入导出 ###

#### 2.1 导出 ####

##### 2.1.1 导出表数据和表结构 #####

mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql(这个名字随便叫)
    
        D:\SoftwareInstallPath\mysql-8.0.13-winx64\bin>mysqldump -uroot -p123456 mybatis_ssm > 1234567.sql

##### 2.1.2 只导出表结构 #####

mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql
    
      mysqldump -uroot -p -d abc > abc.sql
    
        注：导出的数据在mysql的bin目录下

#### 2.2 导入 ####

注意：首先建立空数据库
    
        mysql>create database abc;

##### 2.2.1 方法一 #####

mysql>use abc;                   #选择数据库
    
        mysql>set names utf8;            #设置数据库编码
    
        mysql>source /D:/SoftwareInstallPath/mysql-8.0.13-winx64/bin/1234567.sql;  #导入数据

##### 2.2.2 方法二 #####

mysql -u用户名 -p密码 数据库名 < 数据库名.sql
    
          #mysql -uabc_f -p abc < abc.sql
    
      可先通过SELECT INTO OUTFILE方式，将数据导出到Mysql的C:\ProgramData\MySQL\MySQL Server 5.5\data目录下，再通过LOAD DATA INFILE方式导入。
    
    
    
      1) select * from 表名 into outfile '/文件名.sql';
    
      2) load data infile '/文件名.sql' into table 表名(列名1,...);
    
     这时候就可以在 mysql.ini 文件的 [mysqld] 代码下增加 secure_file_priv=E:/TEST 再重启 mysql 就可以了。然后在导出的地址下面写上刚才配置的这个地址 eg: select * from tb_test into outfile "E:/TEST/test.txt"；就可以了。
    
    select * from t_log into outfile 'E:/12345678.sql';
    
    load data infile 'E:/12345678.sql' into table t_log(id,ip,userid,moduleid,content,createdate,url);
    
    show variables like 'secure%'
    
    desc t_log
    
    select * FROM t_log;