mysql的使用优化和原理

每次复习的时候看写的文档很低效,因为不符合认知的规律。本次对数据库的知识进行更合逻辑的组织。

表基础认识

1.表的设计

参考此文章:此文章从数据库理论的角度理解三范式,并举例对表进行改进,相当不从的文章。
http://t.csdn.cn/NKdzR
原文知乎:https://www.zhihu.com/question/24696366/answer/29189700)

2.字段类型

文章来源:http://t.csdn.cn/k6aEg
数值类型:
在这里插入图片描述
有符号数可以表示负数,无符号数只能为非负数即可,如果不手动指定UNSIGNED,那么默认就是有符号的;
在这里插入图片描述
括号后面是显示宽度:
例如字段a的显示宽度为4,是因为TINYINT有符号值的范围是-128到127,
-128的长度为4(负号、1、2、8共四位),所以默认的显示宽度最大为4,其他的以此类推;
#假设显示宽度小于最大值显示宽度呢?

3.表的描述

首先来了解一下表的描述:
在这里插入图片描述
#extra是什么意思?
这里要扩展一下key(主键约束):
在这里插入图片描述
#外键约束是什么意思呢?

SQL语句

DQL(数据查询语言)

查询语句,凡是select语句都是DQL;

DML(数据操作语言)

insert,delete,update,对表的数据进行增删改;

DDL(数据定义语言)

create,drop,alter,对表结构的增删改;

TCL(事务控制语言)

commit提交事务,rollback回滚事务;

DCL(数据控制语言)

grant授权,revoke撤销权限等。
地址:http://t.csdn.cn/Pvmia

数据库原理

1.IO模型

mysql是使用的单进程多线程。
#为什么不使用单线程多路复用呢?
根据此片文章的说法,这是个历史和生态上的问题;
http://t.csdn.cn/HYUbV

#高并发下的mysql
最大支持连接大约为16000;这是远远不够的;

1.索引

(1)索引的数据结构分析

A.为什么要使用B+树?

  • Hash表 适合等值查询、不适合范围查询。
  • 有序数组 插入需要移动大量元素;
  • 低阶的平衡树呢?
    低阶的平衡树的高度会很高,由于索引比较大,是放在磁盘内的,树越高访问磁盘的次数就越多;
    图片来源在这里插入图片描述
    如果以上说法是正确的,那么IO一次将获取以树节点为起始位置的一块数据;
    #那么岂不是树的阶数越大越好,大到索引的一个节点磁盘IO一次性能读的最大量?
    最终的数据显然在磁盘上都是接近随机分布的,如果是的话,磁盘的预读性原理对索引的加载是有益的能减少IO次数,同时叶子节点保存了储存的data(如何存储到值的,也许聚簇索引就是随着数据的一边添加一边生成的),取值也能一块快取进内存

#以以上结论为基础,如果索引的一个节点中的数据是物理上连续分布的,那么当索引更新时,这种物理上的连续性如何保持呢?
一个树有很多节点,所有节点都连续分布吗,还是只是一个节点内的数据连续分布

  • B树和B+树的区别?
    B树的索引每个节点是存值的?一边储存行数据一边生成聚簇索引,并将行数据放到索引叶子节点里吗,
    而B+树只有叶子节点存值;B+树还采用了链表;
    姑且认为两种树叶子节点都存了所有的键,这种情况下如果B树跨页了就要慢慢迭代查出所有

B.关于磁盘IO
磁盘IO的时间长,只是因为机械运动太慢了吗?

(2)索引的分类

组合索引和字符索引是不是一回事啊?

(3)索引的创建

聚簇索引是自动创建的。
组合索引:
第一个键位的权重最大;

(4)如何科学的建立索引

对于读来说:
索引获取速度=IO速度(数据个数/次)+字符对比(内存)-回表次数
①索引的字段所占字节少越好,这样一次从磁盘获取的数据就最大;
②系统字节数的数据,一般整型好于字符串?待验证
③索引的基数越大越好,区分度大,回表次数就越少;
其他:字符串索引使用字符串前缀建立索引

CREATE TABLE person_info(
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);    

另外一方面,对于写来说,要让写数据对索引的调整小越好;
①主键索引自增;

其他:
组合索引要避免冗余;

#如何检验索引?

2.数据的持久化

http://t.csdn.cn/CYz9f三种log的区别

3.查询优化器

http://t.csdn.cn/KgngJ

查询语句的优化

在这里插入图片描述
来分析是否走索引:
单字段建立索引,多字段查询
多字段家里索引,单字段查询;最左边的可以,符合最左原则
①不等于不符合
②is null 不符合
③or不符合,最好使用in
#既然or和in是等价的,语句优化器不能优化吗?
④like 不是从第一个字母开始的,同组合索引的最左原则;

4.事务机制(并发)

(1)事务的概念

一组操作,只允许同时成功和失败;

(2)ACID原则

原子性
隔离性
一致性
持久性

(3)隔离级别及实现

A.Read Unconmmited
读未提交,也叫脏读,是指读取最新的数据。这种情况如果进行写操作的事务回滚,数据又变化了,实际业务中不允许出现这种情况;

B.Read Commited
读已提交,解决了脏读问题。
在此隔离级别,执行写操作的事务会一直持有修改行的读锁,另外一个事务无法进行读操作,只有这个事务提交后才能拿到读锁进行读操作。
然而在以下情况会出现不可重复读现象:
①A读----->B写并提交事务------->A再次读
两次读的结果不同。不满足一致性。

②A读取第n行和第n+x行之间的数据------>B在两行之间插入一行数据——>A再次读取此区间的数据
此时就会多出一行数据,称为幻读;

#为什么要求两次读要一致?
举个列子:假设数据库到某时刻进行清账结算,那么就要使得此时刻的状态处于一个静止状态,就要隔离事务的其他写操作变化数据。说白了,就是这个时刻之前的数据的最终状态。

C.Read Repeatable
可重复读,解决脏读和部分不可重复读问题;
写与写依然要使用独占锁进行控制;
使用MVCC解决,即多版本并发控制。
在这里插入图片描述
版本链用undo log来记录,用事务id和回滚指针描述。事务开始写的时候立马生成一个版本,如果回滚,则回滚执政会的指向会对应发生变化(不太确定)
如果此时4号事务要进行读操作,会立即生成一个readView(快照),那么显然它访问的版本应该是5,应该读取版本id为2的数据;
在这里插入图片描述
第二次读,readView和活跃id是不会发生变化的,相当于我他妈只读一次,以后一直使用历史数据,除非我自己又修改了数据!

对于查询时的版本链数据是否看见的判断逻辑:

如果被访问版本的 trx_id 属性值小于 m_ids 列表中最小的事务id,表明生成该版本的事务在生成 ReadView
前已经提交,所以该版本可以被当前事务访问。

如果被访问版本的 trx_id 属性值大于 m_ids 列表中最大的事务id,表明生成该版本的事务在生成 ReadView
后才生成,所以该版本不可以被当前事务访问。

如果被访问版本的 trx_id 属性值在 m_ids 列表中最大的事务id和最小事务id之间,那就需要判断一下 trx_id 属性值是不是在
m_ids 列表中,如果在,说明创建 ReadView 时生成该版本的事务还是活跃的,该版本不可以被访问;如果不在,说明创建
ReadView 时生成该版本的事务已经被提交,该版本可以被访问。
原文链接:https://blog.csdn.net/qq_35190492/article/details/109044141

原作者的这个结论的前提似乎是版本链是依次增大的,但是我认为先开始的事务不一定先写,版本链条并不一定是自增的;
**可能的原因是数据行的版本就是依次递增的,而不是使用事务id作版本号的;**这样以上结论就是成立的;

#关于MVCC是否解决了幻读问题
事实证明可重复读隔离级别下还是会出出现幻读的;
A事务开启,查数据,B事务插入一条数据并提交;
A事务再update 数据,查数据会发现多出一行
#关于MVCC是否解决了一致性问题?
在这里插入图片描述
这次把这两个readView看作是前后两个对不同行数据的快照读,由于快照读的时间不同,在两个快照的时间间隔里,5已经写完了,很有可能造成数据不一致。极端的情况,读第一行数据在第一天,读第二行数据在第二天,那者中间发生了那么多变化,第一行数据还停留在第一天,这怎么可能保持一致呢?

http://t.csdn.cn/L9IKf

5.还有哪些引擎,有什么区别?

mysql的扩展、集群

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值