MySQL基础下篇[表的创建/约束的使用/事务和范式以及索引的使用]~3

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: MySQL基础下篇[表的创建/约束的使用/事务和范式以及索引的使用]~

再次查询:

验证过程如下:

读已提交:

read committed,事务A只能读取到事务B提交之后的数据,这种隔离级别避免了读取到脏数据现象,但是这种隔离级别不可重复读取数据,也就是说,在事务开启之后,第一次读到的数据是3条,当前事务还没有结束,可能第二次再读取的时候,读到的数据是4条,3不等于4,称为不可重复读取


这种隔离级别是比较真实的数据,每一次读到的数据是绝对真实的,Oracle数据库默认的隔离级别是:read committed

设置隔离级别为read committed

执行下述命令:

set global transaction isolation level read committed;


再次查询:

验证过程如下:

可重复读:

repeatable read,事务A开启之后,不管是多久,每一次在事务A中读取到的数据都是一致的,即使事务B将数据已经修改,并且提交了,事务A读取到的数据还是没有发生改变,这就是可重复读,它解决了不可重复读取数据的问题,但是它存在会出现幻影读的问题,即为每一次读取到的数据都是幻象,不够真实

设置隔离级别为repeatable read

执行下述命令:

set global transaction isolation level repeatable read;

再次查询:

验证过程如下:

举例:

早晨9点开启了事务,只要事务不结束,到晚上9点,读取到的数据还是那样,读到的都是假象。不够绝对的真实

MySQL中默认的事务隔离级别即为它

序列化/串行化:

serializable(最高的隔离级别),这是最高隔离级别,效率最低,解决了所有的问题,这种隔离级别表示事务排队,不能并发,synchronized,线程同步(事务同步),每一次读取到的数据都是最真实的,并且效率是最低的`

设置隔离级别为serializable

执行下述命令:

set global transaction isolation level serializable;

再次查询

验证过程如下:

D:持久性

事务最终结束的一个保障 —> 事务提交,就相当于将没有保存到硬盘上的数据保存到硬盘上

数据库设计范式:

第一范式:

要求任何一张表必须有主键,每一个字段原子性不可再分,第一范式是最核心,最重要的范式,所有的表的设计都需要满足

举例:

第二范式:

建立在第一范式的基础上,要求所有非主键字段完全依赖主键

以上的表并不满足第一范式

修改之后:

学生编号和教师编号两个字段联合做主键,复合主键(PK:学生编号+教师编号),经过修改之后,以上的表满足了第一范式,但是并不满足第二范式,因为"张三依赖1001”,“王老师依赖001”,显然产生了部分依赖,那么产生部分依赖有什么缺点呢?


数据冗余,空间浪费了,“张三”重复了,"王老师"重复了。


为了让上面这张表满足第二范式,我们对其进行修改


使用三张表来表示多对多的关系:

多对多设计技巧多对多,三张表,关系表两个外键

第三范式:

建立在第二范式的基础上,要求所有非主键字段直接依赖主键,不要产生传递依赖

以上表的设计是描述,班级和学生的关系,很显然是一对多的关系,一个教室中有多个学生。


以上表满足第一范式,因为它包含主键,且也满足第二范式,因为主键不是复合主键,没有产生部分依赖,主键是单一主键,但是并不满足第三范式,因为一年一班依赖01,01依赖1001,产生了传递依赖,不符合第三范式的要求,产生了数据的冗余。


修改如下:

一对多,两张表,多的表加外键

设计数据库表的时候,按照以上的范式进行,可以避免表中数据的冗余,空间的浪费。


数据库设计总结:

一对多,两张表,多的表加外键

多对多,三张表,关系表两个外键

一对一:在实际的开发中,可能存在一张表字段太多,太庞大,这个时候需要拆分

举例:

未拆分的一张表:

拆分成两张之后:

一对一,外键唯一

数据库设计三范式是理论上的,实践和理论有的时候有偏差,最终的目的都是为了满足客户的需求,有的时候会拿冗余换执行速度,因为在sql当中,表和表之间连接次数越多,效率越低(笛卡尔积),有的时候可能会存在冗余,但是为了减少表的连接次数,这样做也是合理的,并且对于开发人员来说,sql语句的编写难度也会降低


引入索引:

索引是在数据库表的字段上添加的,是为了提高查询效率存在的一种机制,一张表的一个字段可以添加一个索引,当然,多个字段联合起来也可以添加索引,索引相当于一本书的目录,是为了缩小范围而存在的一种机制


比如:对于一本字典来说,查找某个汉字有两种方式:


第一种方式:一页一页挨着查找,直到找到为止,这种查找方式属于全字典查找,效率比较低


第二种方式:先通过目录(索引)去定位一个大概的位置,然后直接定位到这个位置,做局域性扫描,缩小扫描的范围,快速的查找,这种查找方式属于通过索引检索,效率比较高。

举例:

select * from person where name='jack';

如果执行上述这条SQL语句,那么将会在name字段进行扫描,因为查询条件是:name=‘jack’;

如果name字段上没有添加索引[目录],或者说没有给name字段创建索引,MySQL会进行全扫描,会将name字段上的每一个值都对比一遍,效率比较低。


MySQL在查询方面主要就是两种方式:

第一种方式:全表扫描

第二种方式:根据索引检索

注意:


在实际开发中,汉语字典前面的目录是排序的,按照a b c d e f…排序,原因是只有排序了才会有区间查找这一说(缩小扫描范围其实就是扫描某个区间罢了),在MySQL数据库当中索引也是需要排序的,并且这个索引的排序和TreeSet数据结构相同,TreeSet(TreeMap)底层是一个自平衡的二叉树,在MySQL当中索引是一个B-Tree数据结构


遵循左小右大原则存放,采用中序遍历方式遍历存取数据

索引的实现原理:

假设有一张用户表:t_user


a:在任何数据库当中主键上都会自动添加索引对象,id字段上自动有索引,因为id是PK,另外在MySQL当中,一个字段上如果有unique约束的话,也会自动创建索引对象


b:在任何数据库当中,任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号


c:在MySQL当中,索引是一个单独的对象,不同的存储引擎以不同形式存在,在MyISAM存储引擎中,索引存储在一个.MYI文件中,在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中,在MEMORY存储引擎当中索引被存储在内存当中,不管索引存储在哪里,索引在MySQL当中都是一个树的形式存在


在MySQL当中,主键上,以及unique字段上都会自动添加索引,那么在什么条件下,我们会考虑给字段添加索引呢?

条件1:数据量非常庞大
条件2:该字段经常出现在where的后面,以条件的形式存在,也就是说这个字段总是被扫描
条件3:该字段很少的DML(insert delete update)操作,因为DML之后,索引需要重新排序

建议不要随意添加索引,因为索引也是需要维护的,太多的话反而会降低系统的性能,建议通过主键查询,建议通过unique约束的字段进行查询,效率都是比较高的

创建索引:

语法:

create index 索引名 on 表名(字段名);

举例:

//为person表中的id字段创建索引名为id_student_index
create index id_student_index on  person(id);

删除索引:

语法:

drop index 索引名 on 表名;

举例:

//删除person表上名为id_student_index的索引
drop index id_student_index on person;

使用SQL语句查询当前某个字段是否添加索引:

语法:

explain select ......where=“需要查看是否添加索引的字段名”;

索引失效:

第一种:当SQL语句的限制条件为模糊查询时,比如下述实例:

即使name字段上添加了索引,索引也不会生效,原因是因为模糊匹配当中以“%”开头,索引不知道从哪里开始查找,因此我们应该避免模糊查询的时候以“%”开始


第二种:使用or的时候会失效,比如下述实例:

使用or的时候会失效,如果使用or,那么要求or两边的条件字段都要有索引,那么索引才会生效,如果其中一边有一个字段没有索引,那么另一个字段上的索引也会失效


第三种:使用复合索引的时候,没有使用左侧的列查找,索引失效。比如下述实例:

复合索引即为:两个或两个以上字段联合起来添加一个索引

第四种:在where当中索引列参与了运算,索引失效。比如下述实例:

第五种:在where当中使用了函数,索引失效。比如下述实例:


索引的分类:

索引是各种数据库进行优化的重要手段,优化的时候优先考虑的因素就是索引,在数据库索引可以分为很多类:

单一索引:一个字段上添加索引
复合索引:两个字段或者更多的字段上添加索引
主键索引:主键上添加索引
唯一性索引:具有unique约束的字段上添加索引
..............

注:唯一性比较弱的字段上添加索引的用处不大

视图:

view:站在不同的角度去看待同一份数据,只有DQL语句才能以view的形式创建

创建视图:

create view 视图名 as  DQL语句[这里必须是DQL语句];

删除视图:

drop view 视图名;

视图的作用:

面向视图对象进行增删改查,对视图对象的增删改查会导致原表被操作(视图的特点:通过对视图的操作,会影响原表数据)

举例:

假设有一条非常复杂的SQL语句,而这条SQL语句需要在不同的位置上反复使用,每一次使用这个SQL语句的时候都需要重新编写,非常的麻烦,那么我们可以将这条复杂的SQL语句以视图对象的形式新建,在需要编写这条SQL语句的位置直接使用视图对象,可以大大简化开发,并且有利于后期的维护,因为修改的时候只需要修改视图对象所映射的SQL语句,我们以后面向视图开发的时候,使用视图的时候可以像使用table一样,可以对视图进行增删改查等操作,视图不是在内存当中,视图对象也是存储在硬盘上的,不会消失


注:视图对应的语句只能是DQL语句,但是视图对象创建完成之后,可以对视图进行增删改查操作

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
15天前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
8天前
|
NoSQL 关系型数据库 MySQL
2024Mysql And Redis基础与进阶操作系列(4-2)作者——LJS[含MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法]
24MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法(4-2) 学不会你来砍我!!!
|
16天前
|
存储 SQL 关系型数据库
MySQL的事务隔离级别
【10月更文挑战第17天】MySQL的事务隔离级别
91 43
|
17天前
|
存储 关系型数据库 MySQL
如何在MySQL中进行索引的创建和管理?
【10月更文挑战第16天】如何在MySQL中进行索引的创建和管理?
41 1
|
7天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
38 0
|
7天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
34 0
|
19天前
|
监控 关系型数据库 MySQL
mysql8索引优化
综上所述,深入理解和有效实施这些索引优化策略,是解锁MySQL 8.0数据库高性能查询的关键。
27 0
|
26天前
|
存储 SQL 关系型数据库
Mysql学习笔记(二):数据库命令行代码总结
这篇文章是关于MySQL数据库命令行操作的总结,包括登录、退出、查看时间与版本、数据库和数据表的基本操作(如创建、删除、查看)、数据的增删改查等。它还涉及了如何通过SQL语句进行条件查询、模糊查询、范围查询和限制查询,以及如何进行表结构的修改。这些内容对于初学者来说非常实用,是学习MySQL数据库管理的基础。
103 6
|
23天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
56 3
Mysql(4)—数据库索引
|
9天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
45 2