您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

关于Mysql数据库,这些知识点你确定都会了吗?

时间:2019-10-15 10:16:33  来源:  作者:

说到MySQL, 大家都很熟悉,因为这是我们工作中不可避免会使用到的技术,但是你真正的掌握了它吗?还是每天在重复crud呢!那么怎么样告别crud呢!来到这里就对了。简单概念的上的东西我就不提了。直接上技术。

数据库三大范式

第一范式1NF:

数据表中的字段,必须是不可拆分的最小单元,也就是确保每一个字段的原子性。例如:

 

关于Mysql数据库,这些知识点你确定都会了吗?

 

 

那么怎么去设计才是正确的呢?其中 address 可以再分为省、市、地区、街道、名牌号,违反了第一范式。

既然要满足原子性不可以分割,我们把这些可以拆分的数据都给拆分出来不就行了吗?以下是笔者拆分后的数据。

 

关于Mysql数据库,这些知识点你确定都会了吗?

 

 

第二范式2NF:使用的时候只需与此表关联即可。

满足1NF的基础上,要求:表中的所以列,都必需依赖主键,而不能有任何一列与主键没有关系。言下之意就是一个表设计只能描述一件事情,不能把其它

 

关于Mysql数据库,这些知识点你确定都会了吗?

 

 

无关的也嵌入进来。第二范式消除了表的无关数据。

那么怎么去设计才是正确的呢?其中 address 可以再分为省、市、地区、街道、名牌号,违反了第一范式。

此表中区域地址和你的用户心情毫无关系。正确的做法就是建立另外一张描述你情绪的表。

第三范式 3NF:

满足2NF的基础上,任何非主字段不依赖与其它非主字段,在2NF基础上消除传递依赖,也就是我们不允许设计的字段不能出现冗余现象。

 

关于Mysql数据库,这些知识点你确定都会了吗?

 

 

我们从这几张表中可以看出province,city,district,street,detailaddress,doorid都是依赖于region_id,所以不满足第三范式。

笔者再多分享一点给大家,在实际工作当中,我们可能会反3NF,那么什么时候去反3NF呢?举个实际工作当中的例子吧!例如我们在一个订单中可能需要关联到用户,我们查看订单的时候需要显示出用户名,用户的其它信息就不用显示出来了,如果不反三范式我们 查询都需要关联用户表,如果查询很普遍的话,就会影响到性能,所以我们干脆就可以把用户名这个字段设计到订单中。这样就可以提高性能。在实际工作中灵活运用。

数据库五大约束

1、主键约束(primary key)

唯一性,非null性

2、唯一约束 (unique)

唯一性,可以空,单只能有一个

3、检查约束 (check)

对该列数据的范围、格式的限制(如:年龄、性别等)

4、默认约束(default)

该数据的默认值

5、外键约束(foreign key)

建立两表之间的关系

数据库事务

数据库事务是什么?

指的是单个逻辑工作单元执行的一系列操作,要么全部执行成功,要么完全不执行。

事务的四大特性

原子性

原子性是指事务包含的所有一系列操作要么全部成功提交,要么全部失败回滚。它是数据库事务最本质的特性。

一致性

一致性是指在事务开始之前和结束以后,数据库的完整性约束没有被破坏,这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性、这里有个容易混淆点,容易和数据一致性混淆。这里更多的强调单机下的事务一致性,必须是一个事务内部。

隔离性

每个事务都有各自的资源单位,事务与事务之间是互相隔离的、不可见,而事务的结果是对其它事务是可见的。这里可以理解为资源粒子度的划分与隔离。

持久性

持久性确保的是一旦提交了事务,出现系统故障,该事物的更新也不会丢失。可以理解为持久化到磁盘中。

事务的四种隔离级别

隔离级别由低到高分别为

读未提交(READ_UNCOMMITTED)

就是一个事务A去读取一个事务B未提交的数据。如果B事务出现回滚,那么事务A就会出现脏读的问题。

读已提交 (READ_COMMINTED)

一个事务A读取一个事务B已提交的数据,解决了脏读问题,但是在一个事务范围内两个相同的查询却返回了不同的数据,那么这就是不可重复读。

可重复读 (REPETABLE_READ)

事务开启时,不再允许其它的事务修改数据。这样就可以无限制读取没有被修改的数据,解决了不可重复读,当有并行插入操作时候就会出现幻读。

可串行化(SERIALIZABLE)

在可串行化的隔离级别下,将事务串行化顺序执行。那么事务不能进行并行操作,也就解决了幻读的问题。

MYSQL InnoDB 引擎默认事务隔离级别是可重复读(REPEATABLE_READ) ORACLE 引擎默认事务隔离级别是可串行化(SERIALIZABLE)

InnoDB 索引

innoDB特性

  1. 完全的事务支持
  2. 基于行存储的行级锁
  3. 多版本并发控制
  4. 原子死锁检测
  5. 原子崩溃恢复

innodb 架构

innodb 逻辑存储结构

在 innodb下,所有的数据都存储在一个表空间中,表空间又由段(segment)、 区(extent)、 页(page)、行(row)组成,页在有些文档中也成为块(block) 1 extent = 64 page

innodb 存储结构图

B-tree

定义:B树满足如下条件,即可称之为m阶B树:

  1. 每个节点最多可以拥有m棵子树;
  2. 根节点最少拥有2棵子树(存在子树的情况下);
  3. 除了根节点以外,其余每个分支节点至少拥有m/2棵子树;
  4. 所有的页节点都在同一层上;
  5. 有k棵子树的分支节点则存在k-1个关键码,关键码按照递增次序进行排列;
  6. 关键子树量需要满足ceil(m/2)-1 <= n <= m-1;

b树图

B-tree 的特点是每个节点不仅存放键值,而且存放数据。

b+tree图

B+树特点:1. 所有的叶子节点中包含了全部元素的信息,及指向含这些元素记录的指针,且叶子节点本身依关键字的大小自小而大顺序链接。2. 所有的中间节点元素都同时存在于子节点,在子节点元素中是最大(或最小)元素。

B+树的优点:1. 单一节点存储更多的元素(因为不含有对应的值,仅仅含有键),使得查询的IO次数更少。2. 所有查询都要从跟节点查找到叶子节点,查询性能稳定,相对于B树更加稳定,因为B+树只有叶子节点存储了对应的值信息。3. 所有叶子节点形成有序双向链表,对于SQL的范围查询以及排序查询都很方便。4. b/b+树的共同优点:每个节点有更多的孩子,插入不需要改变树的高度,从而减少重新平衡的次数,非常适合做数据库索引这种需要持久化在磁盘,同时需要大量查询和插入的应用。树中节点存储这指向页的信息,可以快速定位到磁盘对应的页上面。

Mysql 锁总结

锁不仅是资源占有的一种处理机制,更是多线程或并发编程下对数据一致性的一种保证。加锁和释放锁本身也会消耗资源。了解并合理利用锁机制,能大大提升数据库的性能。锁的作用者是事务,也就是说,锁是针对事务使用而言。单个操作不显示的开启和提交/回滚事务,默认情况下每个操作会自动开启一个事务。

共享锁

一个事务对数据加共享锁,也可以允许其它事务对此交集数据加此锁。但阻止其它事务对此交集数据加排他锁。

加共享锁语句:SELECT * FRPM TABLE_NAME WHERE LOCK IN SHARE MODE;

排他锁

一个事务对数据加排他锁,会阻止其它事务对此交集数据加任和锁。

加排他锁语句:SELECT * FROM TABLE_NAME WHERE FOR UPDATE;

意向锁

为了允许行锁和表锁共存,实现多粒度锁机制,InoDB还有两种内部使用的意向锁,在这里的两种意向锁都是表锁。

意向共享锁

事务打算给数据行共享锁,事务在给一个数据行加共享锁前必须先取得该表的意向共享锁。

意向排他锁

事务打算给数据行加排他锁,事务在给一个数据行加排他锁前必须先取得该表的意向排他锁。

意向锁是innodb 自动加的,不需要用户干预。

表级锁

每个事务操作会锁住整张表,粒子度最大,简单粗暴。优点是加锁和释放锁次数会大大减少。缺点是锁冲突的概率会大大增加,高并发情况下不可取。

页级锁

资源开销介于行级锁和表级锁,会出现死锁。

行级锁

每个事务仅会锁住被影响的行,也就是说,涉及到哪些行记录,哪些行才会被锁住,会出现死锁。优点是锁冲突概率小,并发度高。缺点是由于锁离子度小,加锁和释放锁的次数大大增加,资源开销大。

mysql的行级锁通过索引项上的索引来实现的,innodb这种行锁实现特点意味着只有通过索引条件检索数据,innodb才会使用行级锁,否则,innodb将使用表锁。

间隙锁(Next-Key锁)

当我们用范围条件而不是相等条件检索数据,并请求共享锁或排他锁时,innodb会给符合条件的已有数据的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做间隙,innodb也会对这个间隙加锁,这种锁机制不是所谓的间隙锁。InnoDb使用间隙锁的目的,一方面是为了防止幻读,以满足相关隔离级别的要求,对于上面的例子,要是不使用间隙锁,如果其它事务插入更改了任何记录,那么本事务再次执行上述语句,就会发生幻读;另一方面,是为了满足其恢复和复制的需要,有关恢复和复制机制的影响,以及不同隔离级别下innodb使用间隙锁的情况。

很显然,在使用范围条件检索并锁定记录时,innodb对这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待。因此,在实际开发中,尤其是并发插入比较多的应用,我们尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。

死锁

死锁是指两个或多个事务在同一个资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环。当事务试图以不同的顺序锁定资源时,就可能产生死锁。多个事务同时锁定同一个资源时也可能产生死锁。

innodb避免死锁

  1. 为了在单个innodb表上执行多个并发写入操作时避免死锁,可以在事务开始时通过为预期要修改的每个记录(行) 使用SELECT ... FOR UPDATE 语句来获取必要的锁,即使这些行的更改语句是在之后才执行的。
  2. 在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请共享锁、更新时再申请排他锁,因为这时候当用户在申请排他锁时,其它事务可能又已经获取了相同记录的共享锁,从而造成锁冲突,甚至死锁
  3. 如果事务需要修改或锁定多个表,则应在每个事务中以相同的顺序使用加锁语句。在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低死锁的机会
  4. 同过SELECT...LOCK IN SHARE MODE 获取行的读锁后,如果当前事务再需要对该记录进行更新操作,则很可能造成死锁。
  5. 改变事务隔离级别

如果出现死锁,可以使用SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息。如引发的SQL语句,事务已经获得的锁,正在等待什么锁,以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

乐观锁

加锁是为了占用资源,我们上面说过加锁和释放锁都会有资源开销。在有些不需要加锁就能获取资源岂不是更好?乐观锁是乐观的认为在抢占资源是不用加锁就能获取资源(因为没有其它事务抢占资源或者发生的冲突概率小,稍稍尝试几次就能成功,美滋滋)。适用冲突概率小的情景下。

悲观锁

在冲突概率大的情况下,悲观的认为抢不到资源或者多次都抢不到资源。只能通过加锁的方式抢占资源,然后再做处理,最后释放资源。

SQL优化

优化必备的explain命令

explain命令是用来查询SQL的执行计划 用法:explain select filed from table;

会查询出以下重要字段:

+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ | id | selecttype | table | partitions | type | possiblekeys | key | keylen | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | t0 | NULL | range | idxtradeid | idxtrade_id | 8 | NULL | 2 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ 1 row in set (0.02 sec)

重要字段说明:

select_type:使用的select查询类型,比如simple、primary、union、subquery等;

table:关于访问哪张表,如果是多表,则按访问的先后排序排列;

type:非常非常重要的指标,表示mysql在表中找到行记录的方式,又称访问类型。访问类型的性能指标从差到好依次是system > const > eqref > ref > fulltext > refornull ? indexmerge > uniquesubquery > indexsubquery > range > index > ALL, 一般来说,得保证查询至少达到range级别,最好能达到ref,否则可能出现性能问题;

possible_keys: 可能用到的所以,如果为null,表示没有可能用到的索引;

key:用到的索引,如果为null,表示没有使用索引;

key_len: 按字节计算的索引长度,值越小,表示越快;

ref:关联关系中另一个表的列名称;

rows: 查询数据返回的行数;

extra:与关联操作有关的信息

索引优化

1.禁止无边界范围查询 != , < , > , <= , >= 否则 不会命中索引。

2.禁止无边界范围查询 NOT IN , 否则不会命中索引

3.禁止左模糊或全模糊查询,否则不会命中索引

4.字段的默认值不要为null,否则不会命中索引(使用默认约束Default Counstraint)填充数据默认值

5.在字段上计算后,不会命中索引

6.组合索引的最左前缀原则

7.关于number类型的字段不加单引号也会走索引

8.对于多表 JOIN 时的 ON 条件中 字段类型一定要一致,否则也不会命中索引

9.varchar 查询性能比 bigint 好,因为bigint类型字段上会全表扫描,而在varchar上每个字符判断会走索引,这样避免全表扫描。

10.小数类型使用decimal,禁止使用float与double float和double存储数据时,可能或损失精度,进而判断的时候导致结果不准,强制使用decimal数据类型。

11.表达是否的概念时,字段使用is_开头,数据类型使用unsigned tinyint类型, 1表示是 0 表示否。

12.任何非负数都必须声明为unsigned类型 比如年龄。状态吗等,这样最大容量正值会扩大一倍。

13.如果存储的字符串长度几乎相等,必须使用char定长字符串类型 比如手机号码11位。

14.有时候是不需要建索引 性别字段,状态,这种不同值很少的字段是不需要建立索引的。

15.单表行数超过500万行或单表容量超过2G,才推荐分表

16.进行update或delete时,必先select,避免出现误删数据

数据库拆分

数据库承载的数据以及请求负载较高时,我们就要考虑使用读写分离、数据缓存。但随着业务的增长,数据库的压力达到了承载的阀值米就要考虑分库分表,分解,分摊单个数据库压力。

垂直拆分

数据库的垂直拆分:通常将所有的数据按照不同的业务建立并存储不同的表(table),垂直拆分是按照业务将一个数据库拆分多个数据库。原来每个业务对应一张表,垂直拆分后,是一个业务对应一个数据库(当然也有坑可能是多个业务对应一个数据库)。其核心是专库专用。达到的结果是将原来一个数据库系统的压力按照业务均摊到各个拆分后的数据库中。垂直拆分也是比较推荐的一种拆分方式。

垂直分片往往需要对架构和设计进行调整。在当前微服务化的进程中,对数据库的垂直拆分是非常友好的。

数据表的垂直拆分:单表的数据达到2GB或者500万行记录就要考虑拆分数据表,垂直拆分表就将热点列和不经常使用的列表拆分开,降低单表的大小。

水平拆分

当一般垂直拆分遇到瓶颈时,会对数据表进行水平拆分。这种方式与垂直拆分不同的地方是,他不会更改表结构。水平分表是将一个表拆分成多结构相同的多个表;并且这些表分布在不同的数据库。

分库分表中间件有两种 1.代理模式的分库分表中间件:MyCat; 2.客户端模式的分库分表中间件:ShardingJDBC 3.支持事务的分布式数据库(当然ShardingProxy也是代理分库分表中间件)

总结: 结合着微服务体系,一般会进行垂直拆分。当微服务中的数据库出现压力时,然后进行水平拆分。

欢迎大家评论留言。



Tags:Mysql 数据库   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
巡检工作是保障系统平稳有效运行必不可少的一个环节,目的是能及时发现系统中存在的隐患。本文介绍了美团MySQL数据库巡检系统的框架和巡检内容,希望能够帮助大家了解什么是数...【详细内容】
2020-06-04  Tags: Mysql 数据库  点击:(59)  评论:(0)  加入收藏
逻辑架构 mysql逻辑架构图 MySQL逻辑架构整体分为三层,最上层为客户层, 并非MySQL所独有,诸如,连接处理、授权认证、 安全等功能均在这一层处理。 MySQL大多数核心服务均在中间...【详细内容】
2020-04-30  Tags: Mysql 数据库  点击:(62)  评论:(0)  加入收藏
一、确保mysql开启了binlog日志功能在/etc/my.cnf文件里的[mysqld]区块添加:#这个是存储的位置为mysql配置文件的位置log-bin=mysql-bin然后重启mysql服务生效 二、创建数据...【详细内容】
2020-03-23  Tags: Mysql 数据库  点击:(53)  评论:(0)  加入收藏
连接是关系数据库模型的主要特点。连接查询是关系数据库中最主要的查询,主要包括内连接、外连接等。通过连接运算可以实现多个表查询。当查询数据时,通过连接操作查询出存放在多个表中的不同实体信息。当两个或多个表中...【详细内容】
2019-10-25  Tags: Mysql 数据库  点击:(111)  评论:(0)  加入收藏
说到Mysql, 大家都很熟悉,因为这是我们工作中不可避免会使用到的技术,但是你真正的掌握了它吗?还是每天在重复crud呢!那么怎么样告别crud呢!来到这里就对了。简单概念的上的东西...【详细内容】
2019-10-15  Tags: Mysql 数据库  点击:(104)  评论:(0)  加入收藏
概述需求:模拟生产数据库故障,恢复到故障前一秒场景:有一份初始备份和后面的binlog,早上9点故障,然后直接拿所有的备份和binlog到另外一台服务器做恢复,按备份文件和备份文件记录...【详细内容】
2019-09-20  Tags: Mysql 数据库  点击:(143)  评论:(0)  加入收藏
概述今天主要介绍MySQL查看数据库表容量大小的几个方法,仅供参考。1、查看所有数据库容量大小SELECT table_schema AS &#39;数据库&#39;, sum( table_rows ) AS &#39;记录数&...【详细内容】
2019-09-17  Tags: Mysql 数据库  点击:(165)  评论:(0)  加入收藏
数据库系统与文件系统最大的区别在于数据库能保证操作的原子性,一个操作要么不做要么都做,即使在数据库宕机的情况下,也不会出现操作一半的情况,这个就需要数据库的日志和一套完...【详细内容】
2019-09-11  Tags: Mysql 数据库  点击:(279)  评论:(0)  加入收藏
概述MySQL支持多种字符集(character set)提供用户存储数据,同时允许用不同排序规则(collation)做比较。下面基于MySQL5.7介绍一下字符集相关变量的使用。一、字符集、字符序的概...【详细内容】
2019-09-11  Tags: Mysql 数据库  点击:(144)  评论:(0)  加入收藏
方法一cmd 到mysql bin目录下用如下命令:mysqldump --opt -h192.168.0.156 -uusername -ppassword --skip-lock-tables databasename>database.sql把ip改成localhost就可以的...【详细内容】
2019-05-15  Tags: Mysql 数据库  点击:(541)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(7)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(32)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(28)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条