1. Toy模板网首页
  2. > Toy博客

深入解析MySQL视图、索引、数据导入导出：优化查询和提高效率

9月前 作者：无法自律的人 分类：Toy博客 阅读(63) 违法举报

这篇具有很好参考价值的文章主要介绍了深入解析MySQL视图、索引、数据导入导出：优化查询和提高效率。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

目录

1. 视图（View）：

什么是视图？

为什么要使用视图？

视图的优缺点

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

2) 简化数据操作

3) 提高数据的安全性

4) 共享所需数据

5) 更改数据格式

6) 重用 SQL 语句

示例操作

没使用前

使用后

2. 索引（Index）：

什么是索引？

为什么要使用索引？

1) 顺序访问

2) 索引访问

索引的优缺点【重点】

优点

缺点

什么时候不使用索引

索引何时失效【重点】

索引分类

1) 普通索引

2) 唯一索引

3) 主键索引

4) 组合索引：

3. 数据导入导出：

什么是数据导入导出？

为什么要使用数据导入导出？

示例操作

导出

手动导出

 dom命令出

导出表数据和表结构

 只导出表结构

导入

LOAD DATA INFILE 导入导出

【注意】

1. 视图（View）：

什么是视图？

  1. 视图是基于一个或多个表的查询结果集，类似于虚拟表。它是一个虚拟的表，没有实际的存储数据，通过查询定义的，可以像表一样查询和使用。
  2. 视图可以用于简化复杂的查询操作，隐藏底层表结构细节，提供更简洁和易读的查询接口。
  3. 视图还可以用于限制用户对数据的访问权限，通过授权不同的视图给不同的用户，实现数据安全性控制。

 创建视图语法：

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

语法说明如下：

<视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
-<SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

对于创建视图中的 SELECT 语句的指定存在以下限制：

  • 用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。
  • SELECT 语句不能引用系统或用户变量。
  • SELECT 语句不能包含 FROM 子句中的子查询。
  • SELECT 语句不能引用预处理语句参数。

为什么要使用视图？

视图提供了数据封装和抽象的能力，可以简化复杂查询、隐藏细节、实现安全性控制、提高数据访问的灵活性。

视图的优缺点

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

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


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

2) 简化数据操作

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

3) 提高数据的安全性

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

4) 共享所需数据

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

5) 更改数据格式

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

6) 重用 SQL 语句

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

示例操作

没使用前

sql语句长 

SELECT * from 
t_mysql_score sc,
t_mysql_course c,
t_mysql_teacher t,
t_mysql_student s
where sc.cid = c.cid
and sc.sid = s.sid
and c.tid = t.tid

结果：

深入解析MySQL视图、索引、数据导入导出：优化查询和提高效率,mysql,数据库

使用后

我们利用

CREATE VIEW v_student_score as
SELECT * from 
t_mysql_score sc,
t_mysql_course c,
t_mysql_teacher t,
t_mysql_student s
where sc.cid = c.cid
and sc.sid = s.sid
and c.tid = t.tid

创建之后会出现一个错误

深入解析MySQL视图、索引、数据导入导出：优化查询和提高效率,mysql,数据库

代表了很多重复的意思 ，我们更改*为s.*,c.*,t.tname,sc.score

CREATE VIEW v_student_score as
SELECT s.*,c.*,t.tname,sc.score from 
t_mysql_score sc,
t_mysql_course c,
t_mysql_teacher t,
t_mysql_student s
where sc.cid = c.cid
and sc.sid = s.sid
and c.tid = t.tid

结果：

深入解析MySQL视图、索引、数据导入导出：优化查询和提高效率,mysql,数据库

利用查询语句查询视图

SELECT * from v_student_score

 深入解析MySQL视图、索引、数据导入导出：优化查询和提高效率,mysql,数据库

2. 索引（Index）：

什么是索引？

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

  1. 索引可以根据一个或多个列的值进行排序和搜索，提高查询时的效率。
  2. MySQL索引（Index）是一种特殊的数据结构，建立在表的列上，旨在加快数据库查询的速度
  3. 通过在索引列上创建索引，数据库可以更快地定位和访问特定值，而无需扫描整个数据表。
  4. 索引可以应用于单个列或多个列的组合，可以按升序或降序排序。
  5. 常见的索引类型包括主键索引、唯一索引、普通索引等。

   创建索引：

CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(字段名[(长度)][ASC|DESC])

为什么要使用索引？

索引可以加快数据库查询的速度，提高查询效率，特别是在大数据量的情况下，可以显著减少查询的时间。

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

1) 顺序访问

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

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

2) 索引访问

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

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

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

索引的优缺点【重点】

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

优点

索引的优点如下：

  • 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
  • 可以给所有的 MySQL 列类型设置索引。
  • 可以大大加快数据的查询速度，这是使用索引最主要的原因。
  • 在实现数据的参考完整性方面可以加速表与表之间的连接。
  • 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

缺点

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

  • 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
  • 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

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

什么时候不使用索引

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

索引何时失效【重点】

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

索引分类

创建日志文件

CREATE TABLE `t_log` (
  `id` varchar(32) NOT NULL COMMENT '唯一标识',
  `ip` varchar(15) NOT NULL COMMENT 'IP地址',
  `userid` varchar(32) NOT NULL COMMENT '用户ID',
  `moduleid` varchar(32) NOT NULL COMMENT '模块ID',
  `content` varchar(500) NOT NULL COMMENT '日志内容',
  `createdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
  `url` varchar(100) DEFAULT NULL COMMENT '请求URL地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后导入我们的文件t_log.sql

【ps】文件里的数据均为虚拟数据

1) 普通索引

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

select COUNT(1) from t_log    -- 用时：0.026s


建索引前  0.11s

select * from t_log where moduleid = '100301';


创建索引所花费的时间： 1.132s

Create index idx_moduleid on t_log(moduleid);


建索引后 0.001s

select * from t_log where moduleid = '100301';

深入解析MySQL视图、索引、数据导入导出：优化查询和提高效率,mysql,数据库

  删除索引  0.014s

drop index idx_moduleid on t_log

可以查看走过的索引

EXPLAIN select * from t_log where moduleid = '100301';

有索引

深入解析MySQL视图、索引、数据导入导出：优化查询和提高效率,mysql,数据库

无索引

深入解析MySQL视图、索引、数据导入导出：优化查询和提高效率,mysql,数据库

2) 唯一索引

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

  
-- Duplicate entry '/quartz/queryJobLst' for key 'idx_url'    有重复列段
-- 创建
create UNIQUE index idx_url on t_log(url);
-- 删除
drop index idx_url on t_log;
我们的 t_log里面的 url有很多的 /quartz/queryJobLst数据

深入解析MySQL视图、索引、数据导入导出：优化查询和提高效率,mysql,数据库

3) 主键索引

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

我们的id是主键所以查询的时间还是很快的
 

 -- 主键索引所花费的时间：0s
select * from t_log where id = '07489cdafd6d4a3489884cd3c00c7b27';
EXPLAIN select * from t_log where id = '07489cdafd6d4a3489884cd3c00c7b27';


深入解析MySQL视图、索引、数据导入导出：优化查询和提高效率,mysql,数据库

4) 组合索引：

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


用的最左原则，看似有多个索引，其实走了一个索引，走的是左边第一个索引

-- 花费的时间：2.965s
create index idx_userid_moduleid_url on t_log(userid,moduleid,url);


深入解析MySQL视图、索引、数据导入导出：优化查询和提高效率,mysql,数据库

-- 走组合索引
EXPLAIN select * from t_log where userid = '' and moduleid = '' and url = '';
EXPLAIN select * from t_log where userid = '' and moduleid = '';
EXPLAIN select * from t_log where userid = '' ;
EXPLAIN select * from t_log where userid = '' and url = '';
-- 不走组合索引
EXPLAIN select * from t_log where moduleid = '';
EXPLAIN select * from t_log where url = '';
EXPLAIN select * from t_log where moduleid = '' and url = '';


 

3. 数据导入导出：

什么是数据导入导出？

  1. 数据导入是将外部数据文件的数据导入到MySQL数据库中。
  2. 数据导出是将MySQL数据库中的数据导出为外部数据文件，以便在其他系统或数据库中使用。
  3. 数据导入导出常用的工具有mysqldump、mysqlimport等。
  4. 数据导入导出通常用于数据迁移、数据备份和恢复、与其他系统进行数据交换等场景。
  5. 数据导入导出的文件格式可以是纯文本格式，也可以是其他数据库可识别的格式如SQL文件。

   数据导入：
   使用LOAD DATA INFILE语句导入纯文本数据文件：

LOAD DATA INFILE 'path_to_file' INTO TABLE table_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

   数据导出：
  使用SELECT ... INTO OUTFILE语句将查询结果导出为纯文本数据文件：

SELECT column1, column2, ... INTO OUTFILE 'path_to_file' FROM table_name;

为什么要使用数据导入导出？

数据导入导出允许将数据从一个系统迁移到另一个系统，进行备份和恢复，与其他系统进行数据交换和共享。

示例操作

导出

手动导出

选择需要导的表，然后右键，转储SQL文件，结构和数据，保存即可

深入解析MySQL视图、索引、数据导入导出：优化查询和提高效率,mysql,数据库

在数据多的情况下，我们导出的时间就需要很多

深入解析MySQL视图、索引、数据导入导出：优化查询和提高效率,mysql,数据库

 

 dom命令出

找到我们的mysql的安装位置，找到bin文件夹，cmd进去。

导出的数据在mysql的bin目录下

导出表数据和表结构

mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql(这个名字随便叫)
 

  mysqldump -uroot -p123456 mybatis_ssm > 1234567.sql

深入解析MySQL视图、索引、数据导入导出：优化查询和提高效率,mysql,数据库
我们的bin文件夹下面就有123456.sql文件

深入解析MySQL视图、索引、数据导入导出：优化查询和提高效率,mysql,数据库

 只导出表结构
mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql
  
mysqldump -uroot -p -d abc > abc.sql

 

导入

为了防止多种因素，很多企业都是在dom命令建立数据库，所以我在这里也演示一下；

还是在bin文件夹里面cmd命令里面进行

mysql -u root -p

输入设置用户的密码
深入解析MySQL视图、索引、数据导入导出：优化查询和提高效率,mysql,数据库

进去之后就可以正常使用sql语句了

注意：首先建立空数据库

mysql>create database abc;


方法一

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

方法二
 

mysql -u用户名 -p密码 数据库名 < 数据库名.sql
            #mysql -uabc_f -p abc < abc.sql

LOAD DATA INFILE 导入导出

可先通过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 "D:/TEST/test.txt"；就可以了。

-- 导出
select * from t_log into outfile 'D:/12345678.sql';
-- 导出
load data infile 'D:/12345678.sql' into table t_log(id,ip,userid,moduleid,content,createdate,url); 
show variables like 'secure%'
desc t_log;

select * FROM t_log;

【注意】

在使用视图、索引和数据导入导出时，需要根据具体的数据结构和业务需求进行慎重的选择和操作。此外，视图和索引的创建需要考虑数据库的性能和资源消耗，并根据实际情况进行适当的优化和管理。文章来源地址https://www.toymoban.com/news/detail-542737.html

到了这里，关于深入解析MySQL视图、索引、数据导入导出：优化查询和提高效率的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处： 如若内容造成侵权/违法违规/事实不符，请点击违法举报进行投诉反馈，一经查实，立即删除！

分享到：
领支付宝红包 赞助服务器费用

相关文章

  • MySQL导入导出优化

    在实际应用中，我们经常需要将大量数据从MySQL数据库中导入或导出。如果数据量较大，导入导出时可能会导致性能问题或失败。本文将介绍一些MySQL数据导入导出的优化技巧，帮助大家更高效地完成数据迁移。 1. 关闭自动提交事务 在进行大数据量导入时，可以关闭MySQL的自

    2024年02月07日
    浏览(49)
  • MySQL高级篇复盘笔记（一）【存储引擎、索引、SQL优化、视图、触发器、MySQL管理】

    MySQL高级篇复盘笔记（一）【存储引擎、索引、SQL优化、视图、触发器、MySQL管理】

    ❤ 作者主页：欢迎来到我的技术博客😎 ❀ 个人介绍：大家好，本人热衷于 Java后端开发 ，欢迎来交流学习哦！(￣▽￣)~* 🍊 如果文章对您有帮助，记得 关注 、 点赞 、 收藏 、 评论 ⭐️⭐️⭐️ 📣 您的支持将是我创作的动力，让我们一起加油进步吧！！！🎉🎉 连接层

    2024年02月06日
    浏览(77)
  • 【MySQL】深入理解MySQL索引优化器原理（MySQL专栏启动）

    【MySQL】深入理解MySQL索引优化器原理（MySQL专栏启动）

    📫作者简介： 小明java问道之路，专注于研究 Java/ Liunx内核/ C++及汇编/计算机底层原理/源码，就职于大型金融公司后端高级工程师，擅长交易领域的高安全/可用/并发/性能的架构设计与演进、系统优化与稳定性建设。   📫 热衷分享，喜欢原创~ 关注我会给你带来一些不一样

    2024年01月15日
    浏览(60)
  • MySQL数据备份与还原、索引、视图

    MySQL数据备份与还原、索引、视图

    一.备份与还原  /***************************样例表***************************/     CREATE DATABASE booksDB;     use booksDB;     CREATE TABLE books     (       bk_id  INT NOT NULL PRIMARY KEY,       bk_title VARCHAR(50) NOT NULL,       copyright YEAR NOT NULL     );     INSERT INTO books     VALUES (11078, \\\'Learning

    2024年02月15日
    浏览(40)
  • Mysql之视图，索引及数据的备份与恢复

    Mysql之视图，索引及数据的备份与恢复

    目录 一、视图 1.视图是什么 2.视图与数据表的区别 3.视图的优缺点 优点： 缺点： 4.视图的应用场景 5.语法运用 二、索引 1.什么是索引 2.为什么要使用索引 3.使用索引的优缺点 4.何时不使用索引 5.索引何时失效 6.索引分类 三、数据的备份与恢复 1.数据库工具内导入导出 2.m

    2024年02月13日
    浏览(57)

  • MySQL索引优化：提升查询速度的实战解析

    当涉及到大型数据库和复杂查询时，索引在MySQL中是一个重要的性能优化工具。通过使用索引，可以加速查询速度，减少查询的执行时间。下面是一个详细的MySQL添加索引的教程，使用Markdown格式进行说明。 步骤1：选择合适的列 首先，需要选择哪些列需要添加索引。通常情况

    2024年02月12日
    浏览(66)

  • 建库、建表、修改表、复制表、字符类型、数值类型、枚举类型、日期时间类型、检索目录、数据导入命令、数据导入步骤、数据导出命令、非空、默认值、唯一索引

    1.1 问题 建库练习 建表练习 修改表练习 1.2 方案 在MySQL50主机完成练习。 1.3 步骤 实现此案例需要按照如下步骤进行。 步骤一：建库练习 库名命名规则： 仅可以使用数字、字母、下划线、不能纯数字 区分字母大小写， 具有唯一性 不可使用MySQL命令或特殊字符 命令操作如下

    2024年01月17日
    浏览(61)

  • MySQL 导出和导入数据

    MySQL导出数据的主要作用是将数据库中的数据以某种格式（如CSV、SQL等）导出到一个文件中，以便在需要时进行导入或备份。 导出数据的作用包括但不限于以下几个方面： 数据备份：导出数据可以将数据库中的数据备份到文件中，以防止数据丢失或意外删除。 数据迁移：如

    2024年02月20日
    浏览(34)

  • MySQL导入/导出数据

    1、mysql 命令导入 使用 mysql 命令导入语法格式为： your_username、your_host、your_port、your_database 分别为你的 MySQL 用户名、主机、端口和数据库。 实例： 以上命令将将备份的整个数据库 runoob.sql 导入。 执行上述命令后，系统将要求输入 MySQL 用户的密码。输入密码并按Enter键。 这

    2024年02月20日
    浏览(40)
  • 【MySQL】数据备份(导出数据 / 导入数据)

    【MySQL】数据备份(导出数据 / 导入数据)

     SELECT...INTO OUTFILE 是 MySQL 用于导出数据的语句，它允许将查询结果保存到指定的文件中。 该语句的基本语法如下：   column1, column2, ... ：要导出的列名。 INTO OUTFILE \\\'file_path\\\' ：指定导出数据时要保存到的文件路径。 FIELDS TERMINATED BY \\\'field_separator\\\' ：指定字段之间的分隔符，默认

    2024年02月12日
    浏览(51)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包，优惠每天领

二维码1

领取红包

二维码2

领红包