学习笔记----MySQL高级

最新推荐文章于 2023-02-02 22:48:42 发布

骑着笨鸟的小白

最新推荐文章于 2023-02-02 22:48:42 发布

阅读量170

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/weixin_44934118/article/details/96440727

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

视图

1.问题
对于复杂的查询，往往是有多个数据表进行关联查询而得到，如果数据库因为需求等原因发生了改变，为了保证查询出来的数据与之前相同，则需要在多个地方进行修改，维护起来非常麻烦。

解决办法：定义视图

2.什么是视图
**通俗的讲，视图就是一条SELECT语句执行后返回的结果集。**所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了变化，视图也会跟着改变）；

方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性。

3.定义视图
建议以v_开头

create view 视图名称 as select语句；

4.查看视图
查看表会将所有的视图也列出来

show tables;

5.使用视图
视图的用途就是查询

6.删除视图

drop view 视图名称；
eg:
drop view v_goods;

7.视图demo
在这里插入图片描述

8.视图的作用

提高了重用性，就像一个函数
对数据库重构，却不影响程序的运行
提高了安全性能，可以对不同的用户
让数据更加清晰

事务

1.什么是事务
所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

2.为什么要有事务
事务广泛的运用于订单系统、银行系统等多种场景

例如：

A用户和B用户是银行的储户，现在A要给B转账500元，那么需要做以下几件事：
1.检查A的账户余额>500元；
2.A 账户中扣除500元;
3.B 账户中增加500元;

正常的流程走下来，A账户扣了500，B账户加了500，皆大欢喜。
那如果A账户扣了钱之后，系统出了故障了呢？A白白损失了500，而B也没有收到本该属于他的500,

以上的案例中，隐藏着一个前提条件：A扣钱和B价钱，要么同时成功，要么同时失败。事务的需求就在于此。

事务四大特性（简称ACID）重点【面试高频题】

原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）

以下内容出自《高性能MySQL》第三版，了解事务的ACID及四种隔离级有助于我们更好的理解事务运作。

下面举一个银行应用是解释事务必要性的一个经典例子。假如一个银行的数据库有两张表：支票表（checking）和储蓄表（savings）。现在要从用户Jane的支票账户转移200美元到她的储蓄账户，那么至少需要三个步骤：

检查支票账户的余额高于或者等于200美元。
从支票账户余额中减去200美元。
在储蓄帐户余额中增加200美元。

上述三个步骤的操作必须打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤。

可以用START TRANSACTION语句开始一个事务，然后要么使用COMMIT提交将修改的数据持久保存，要么使用ROLLBACK撤销所有的修改。事务SQL的样本如下：

1.start transaction;
2.select balance from checking where customer_id = 10233276;
3.update checking set balance = balance - 200.00 where customer_id = 10233276;
4.update savings set balance = balance + 200.00 where customer_id = 10233276;
5.commit;

一个很好的事务处理系统，必须具备这些标准特性：

原子性（atomicity）

一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。

一致性（consistency）

数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了即使在执行第三、四条语句之间同时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所作的修改也不会保存到数据库中。）

隔离性（isolation）

通常来说，一个事务所作的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）

持久性（durability）

一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

事务命令

表的引擎类型必须是innodb类型才可以使用事务，这是Mysql表的默认引擎

查看表的创建语句，可以看到engine=innodb

-- 选择数据库
use jing_dong;
-- 查看goods表
show create table goods;

开启事务，命令如下:

开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中

begin;
或者
start transaction;

提交事务，命令如下

将缓存中的数据变更维护到物理表中

commit;

回滚事务，命令如下：

放弃缓存中变更的数据

rollback;

注意
1.修改数据的命令会自动的触发事务，包括insert、update、delete
2.而在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

索引

1.什么是索引
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度

索引目的
索引的目的在于提高查询效率，可以类比字典。

3.索引原理
数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。
在这里插入图片描述

4.索引的使用

查看索引

show index from 表名;

创建索引
- 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
- 字段类型如果不是字符串，可以不填写长度部分

create index 索引名称 on 表名(字段名称(长度))

删除索引

drop index 索引名称 on 表名;

5.索引demo
创建测试表testindex

create table test_index(title varchar(10));

使用python程序（ipython也可以）通过pymsql模块向表中加入十万条数据

from pymysql import connect

def main():
    # 创建Connection连接
    conn = connect(host='localhost',port=3306,database='jing_dong',user='root',password='mysql',charset='utf8')
    # 获得Cursor对象
    cursor = conn.cursor()
    # 插入10万次数据
    for i in range(100000):
        cursor.execute("insert into test_index values('ha-%d')" % i)
    # 提交数据
    conn.commit()

if __name__ == "__main__":
    main()

查询

开启运行时间监测：

set profiling=1;

查找第1万条数据ha-99999

select * from test_index where title='ha-99999';

查看执行的时间：

show profiles;

为表title_index的title列创建索引：

create index title_index on test_index(title(10));

执行查询语句：

select * from test_index where title='ha-99999';

再次查看执行的时间

show profiles;

注意

要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。
建立索引会占用磁盘空间

账户管理（了解）

在生产环境下操作数据库时，绝对不可以使用root账户连接，而是创建特定的账户，授予这个账户特定的操作权限，然后连接进行操作，主要的操作就是数据的crud
MySQL账户体系：根据账户所具有的权限的不同，MySQL的账户可以分为以下几种
- 服务实例级账号：，启动了一个mysqld，即为一个数据库实例；如果某用户如root,拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库、连同这些库中的表
- 数据库级别账号：对特定数据库执行增删改查的所有操作
- 数据表级别账号：对特定表执行增删改查等所有操作
- 字段级别的权限：对某些表的特定字段进行操作
- 存储程序级别的账号：对存储程序进行增删改查的操作
账户的操作主要包括创建账户、删除账户、修改密码、授权权限等

注意：
1.进行账户操作时，需要使用root账户登录，这个账户拥有最高的实例级权限
2.通常都使用数据库级操作权限

MySQL主从同步配置

1.主从同步的定义
主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。
使用主从同步的好处：

通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。
提高数据安全，因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据
在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能

2.主从同步的机制
在这里插入图片描述
Mysql服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。

在使用二进制日志时，主服务器的所有操作都会被记录下来，然后从服务器会接收到该日志的一个副本。从服务器可以指定执行该日志中的哪一类事件（譬如只插入数据或者只更新数据），默认会执行日志中的所有语句。

每一个从服务器会记录关于二进制日志的信息：文件名和已经处理过的语句，这样意味着不同的从服务器可以分别执行同一个二进制日志的不同部分，并且从服务器可以随时连接或者中断和服务器的连接。

主服务器和每一个从服务器都必须配置一个唯一的ID号（在my.cnf文件的[mysqld]模块下有一个server-id配置项），另外，每一个从服务器还需要通过CHANGE MASTER TO语句来配置它要连接的主服务器的ip地址，日志文件名称和该日志里面的位置（这些信息存储在主服务器的数据库里）

3.配置主从同步的基本步骤
有很多种配置主从同步的方法，可以总结为如下的步骤：
1.在主服务器上，必须开启二进制日志机制和配置一个独立的ID
2.在每一个从服务器上，配置一个唯一的ID，创建一个用来专门复制主服务器数据的账号
3.在开始复制进程前，在主服务器上记录二进制文件的位置信息
4.如果在开始复制之前，数据库中已经有数据，就必须先创建一个数据快照（可以使用mysqldump导出数据库，或者直接复制数据文件）
5.配置从服务器要连接的主服务器的IP地址和登陆授权，二进制日志文件名和位置

骑着笨鸟的小白

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
学习笔记----MySQL高级

视图1.问题对于复杂的查询，往往是有多个数据表进行关联查询而得到，如果数据库因为需求等原因发生了改变，为了保证查询出来的数据与之前相同，则需要在多个地方进行修改，维护起来非常麻烦。解决办法：定义视图2.什么是视图**通俗的讲，视图就是一条SELECT语句执行后返回的结果集。**所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。视图是对若干张基本表的引用，一张虚表，查询...
复制链接

扫一扫