mysql的使用优化和原理

最新推荐文章于 2024-11-12 21:14:07 发布

哦豁

最新推荐文章于 2024-11-12 21:14:07 发布

阅读量1.4k

点赞数

分类专栏：数据库框架文章标签： java

本文链接：https://blog.csdn.net/laiyer_/article/details/123350838

版权

数据库框架专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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

表基础认识

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已经写完了，很有可能造成数据不一致。极端的情况，读第一行数据在第一天，读第二行数据在第二天，那者中间发生了那么多变化，第一行数据还停留在第一天，这怎么可能保持一致呢？