重新建设网站的报告,怎么学做网站跟聊天软件,电商网站设计周志,北京学做网站目录
MySQL的架构
MySQL中的锁
MySQL中的事务
事务特性
隔离级别
事务日志
多版本并发控制MVCC
影响MySQL性能的物理因素
InnoDB缓冲池
MySQL常用的数据类型以及优化
字符串类型
日期和时间类型
数据标识符 MySQL的架构 默认情况下#xff0c;每个客户端连接都…目录
MySQL的架构
MySQL中的锁
MySQL中的事务
事务特性
隔离级别
事务日志
多版本并发控制MVCC
影响MySQL性能的物理因素
InnoDB缓冲池
MySQL常用的数据类型以及优化
字符串类型
日期和时间类型
数据标识符 MySQL的架构 默认情况下每个客户端连接都会在服务器进程中拥有一个线程该连接的查询只会在这个单独的线程中执行该线程驻留在一个内核或者是CPU上服务器维护了一个缓存区用来存放已经就绪的线程因此不需要为每个新的连接创建或者是销毁线程。 MySQL中的锁
MySQL的锁的粒度 行锁并发大系统开销大比如内核态和用户态之间的频繁转换 表锁并发小系统开销小。
MySQL的读写锁 读锁读锁也称为共享锁并发访问共享资源的时候不进行阻塞MySQL中的查询语句是读锁在查询语句后面添加 for share /for update 可以为查询语句加写锁。 写锁也称为排他锁并发访问共享资源时会出现阻塞MySQL中增删改语句都是自带写锁的。
注意增删改是隐式上锁查询语句要上锁需要显式上锁。
死锁
死锁是指两个或者是多个事务相互持有和请求相同资源上的锁产生了循环依赖。
INnoDB目前处理死锁的方式是将持有最少行级排他锁的事务进行回滚。实际上InnoDB存储引擎会帮我们先检测锁的情况如果检测到死锁那么会立刻返回一个错误的信息。
一旦发生死锁如果不回滚其中的一个事务就无法打破死锁。 MySQL中的事务
事务特性
MySQL事务MySQL中只有INnoDB引擎才支持事务
事务就是一组SQL语句作为一个工作单位以原子方式进行处理作为事务的一组语句要么全部执行成功要么全部执行失败。
事务的四大特性 原子性一个事务必须被视为不可分割的工作单元整个事务的所有操作要么全部提交成功要么全部失败回滚。 一致性可以简单的理解为守恒数据库总是从一个一致性状态转换到下一个一致的状态。比如两个账户进行转账那么转账前后这两个账户里面的总钱数都是一致的。 隔离性隔离性最常见的一种情况就是一个事务所做的修改在最终提交以前对其他事务都是不可见的。 持久性事务一旦成功提交那么事务所做的修改就会被永久的保存在数据库中。 隔离级别
MySQL默认的隔离级别是可重复读。 读未提交READUNCOMMITTED在事务中可以查看到其他事务中还没有提交的修改。这个级别的隔离一般很少使用。 读已提交READ COMMITTED一个事务可以看到其他事务在它开始之后提交的修改在该事务提交之前其所做的任何修改对其他事务都是不可见的。这个级别仍然会出现不可重复读不可重复读同一事务中两次执行相同SQL语句可能会看到不同的数据结果 可重复读REPEATABLE READ解决了读已提交隔离级别的不可重复读问题保证了在同一个事务中多次读取相同行数据结果是一样的。但是不能解决幻读问题。幻读指的是当某个事务在读取范围内的记录的时候另一个事务又在该范围内插入了新的记录当之前的事务再次读取某个范围内的记录是产生了幻行。 可串行化SERIALIZABLE前制事务按序执行相当于加锁阻塞所以可能会导致大量超时和锁竞争的相关问题出现。在实际生产环境中也很少用到这个隔离级别。
隔离级别是否出现脏读是否出现不可重复读是否出现幻读读未提交是是是读已提交否是是可重复读否否是可串行化否否否事务日志
事务日志可以提高事务的效率存储引擎只需要更改内存中的数据副本而不是每次修改磁盘中的表然后再把更改的记录写入事务中事务日志会被持久化存在磁盘上。
因为事务日志采用的是追加写的操作是在硬盘中一小块区域内的顺序IO而不是随机IO所以写入事务日志是一种相对比较快的操作最后会有一个后台进程在某个时间去更新磁盘中的表。操作日志顺序写入磁盘一次然后更新磁盘中的表一次会操作两次磁盘 多版本并发控制MVCC
多版本并发控制MVCC可以理解为行级锁的一种变种但是mvcc在很多情况下避免了加锁操作因此开销更低。
简要理解mvcc的思想和一些设计MVCC的工作原理是使用数据在某个时间节点的快照来实现的
意味着无论事务运行多长时间都可以看到数据的一致视图也意味着不同事务可以在同一时间看到同一张表的不同数据。
MySQL就是通过MVCC解决了幻读的问题。
每个存储引擎实现MVCC的方式都是不同的InnoDB通过为每个事务在启动时分配一个【事务ID】来实现MVCC该ID在事务首次读取任何数据的时候分配只读事务ID永远为0后面每次操作数据都会影响这个事务ID的值所有在下次来读取的时候就可以通过循环来比较这个事务ID从而返回对应的视图视图的存在类似一个链表保存不同时间的数据。
注意MVCC只适用于【重复读】和【读已提交】这两个事务的隔离级别。 InnoDB默认为可重复读隔离级别并且通过间隙锁策略来防止在这个隔离级别上的幻读InnoDB不只锁定在查询中涉及到的行还会对索引结构中的间隙进行锁定以防止幻行被插入。 影响MySQL性能的物理因素
MySQL服务器的性能受限于整个系统最薄弱的环节承载MySQL服务器的操作系统和硬件是最主要的限制因素。最常见的几个就是磁盘空间大小可用内存CPU网络以及磁盘的材质涉及到IO所以能用固态硬盘那就尽量使用固态硬盘。
为MySQL服务器配置大内存并不是为了在内存中保存大量的数据而是为了减少磁盘IO次数磁盘IO访问数据比直接访问内存中的数据要慢几个数量级。
MySQL的读取写入缓存如果有足够大的内存是可以大幅度的减少磁盘IO的次数因为如果数据都可以装到内存的话那么服务器一旦完成数据的缓存预热那么每次读取数据都是一次的缓存命中在这种情况下仍然会从内存中进行逻辑的读取但是不会从磁盘中进行物理的读取数据。而且写入也是可以在内存中执行的但是数据最后必然是会被写入磁盘进行持久化的也就是说缓存可以延迟写操作但缓存不能像消除读操作那样消除写操作的磁盘IO。
事实上缓存的存在除了运行延迟写操作外还可以允许写操作与其他方式组合起来一起使用。 多次写操作一次刷新这种设计不仅可以减少IO次数还可以把写入磁盘的随机IO变成顺序IO 一个数据片段可以在内存中被多次更改而无须每一次都将新值写入磁盘。当数据最终被刷新到磁盘时自上次物理写入以来发生的所有修改都将被持久化。 IO合并 许多不同的数据片段可以在内存中被修改这些修改可以被收集在一起因此物理写可以作为单个磁盘操作来执行。
写操作可以从缓存中收益可以将随机IO转变成顺序IO。 内存与交换
当操作系统因为没有足够的内存来容纳虚拟内存时而将一些虚拟内存写入磁盘时就会发生交换。写操作是缩短磁盘的整体寿命的当然我们也可以关闭交换这样就可以完全消除交换带来的负面影响但是需要承担因为内存耗尽导致进程被终止的情况。 InnoDB缓冲池
InnoDB缓冲池不仅缓存索引还缓存行数据自适应哈希索引更改缓冲区锁和其他内部结构等InnoDB还是用缓冲池来实现延迟写操作从而可以将多个写操作合并在一起并按顺序执行。InnoDB严重依赖缓冲池所以应该确保为其分配足够的内存。
当然大型的缓冲区也会带来一些其他问题比如更长的关闭时间和预热时间而且如果缓冲池中存在许多脏页被修改过的数据还没被同步到磁盘中就是说内存中的数据与磁盘中的数据不一致那么InnoDB可能需要很长时间才能关闭因为它会在关闭的时候将脏页写到数据文件中。
InnoDB默认使用同一个后台线程来刷新脏页以及合并写操作并按顺序执行以提高效率当脏页的百分比超过设置的阈值时InnoDB会尽可能快的刷新页面以降低脏页的数量。 事务日志InnoDB使用日志来降低提交事务的成本它不会在每个事务提交时将缓冲池刷新到磁盘而是将事务记录到日志中使用追加的方式来记录这个日志避免了随机IO使用这个事务日志InnoDB可以将随机磁盘转换为顺序IO。InnoDB最终必须还是要将更改的数据写入数据文件因为日志的大小是固定的采取的是循环写入的方式当到达日志末尾的时候它会环绕到日志的开头如果日志记录中包含更改且尚未应用于数据文件的操作则会到值无法覆盖日志记录因为这将删除以提交事务的唯一永久记录。
事务日志是使用连续的磁盘空间来进行记录的InnoDB引擎中在事务提交的时候必须先将该事务的所有事务日志写入到磁盘上的redoLog File和undoLog File 中进行持久化。 日志缓冲区InnoDB修改数据时会将修改记录写入到日志缓冲区并将其保存在内存中当缓冲区满了事务提交时或者每秒一次这三个条件以先满足为准InnoDB会将缓冲区刷新到磁盘上的日志文件中。与InnoDB的普通数据相比日志的条目非常紧凑。 InnoDB如何刷新日志缓冲区
使用互斥锁锁定缓冲区将其刷新到所需的位置如何将剩余的条目移动到缓冲区的前面当释放互斥锁时可能会有多个事务准备刷新其日志条目InnoDB使用了一组提交特性可以在单次IO操作将一组日志全部提交。日志缓冲区必须被刷新到持久存储中以确保提交的事务完全的持久。
InnoDB_flush_log_at_trx_commit可以用来控制日志缓冲区的刷新位置和刷新频率 0每秒定时将日志缓冲区写入日志文件并且刷新日志文件但在事务提交时不做任何操作。 1每次事务提交时将日志缓冲区写入日志文件并将其刷新到持久存储中这是默认的设置也是最安全的设置 2每次事务提交时都将日志缓冲区写入日志文件但不执行刷新。InnoDB按计划每秒刷新一次。与0设置最主要的区别是如果只是MySQL进程崩溃设置2不会丢失任何事务但是如果整个服务器崩溃或者是断电仍然可能丢失事务。
注意在大多数操作系统中将缓冲区写入日志只是将数据从InnoDB的内存缓冲区移动到操作系统的缓存中依然还是在内存中它实际上不会将数据写入到持久存储因此如果发生崩溃或者是断电设置为0和2通常会导致最多一秒的数据丢失因为数据可能只存在操作系统的缓存中。
MySQL常用的数据类型以及优化
字符串类型
可变字符串varchar varchar用于存储可变长度的字符串比固定长度的类型更节省空间因为它仅使用必要的空间。 varchar需要额外使用1或者是2个字节来记录字符串的长度如果列的最大长度小于或者是等于255字节则只使用一个字节表示否则使用两个字节进行表示。比如varchar(10)需要11个字节的存储空间而varchar(1000)需要1002个字节的存储空间。 注意由于行是可变长度在更新数据的时候可能会增长这会导致额外的工作如果行的增长使原来位置无法容纳跟多内容则具体的处理行为取决于使用的存储引擎。例如innodb可能会需要分割页面来容纳行。
下面这些情况使用varchar是比较好的 字符串的最大长度远大于平均长度 列的更新很少可以避免内存碎片的产生。
固定字符串char char是固定的长度MySQL总是为定义的字符串长度分配足够的空间当存储char值时MySQL会删除所有尾随的空格
char适用于存储非常短的字符串或者是适用于所有值的长度几乎相同的情况下。固定长度不容易产生内存碎片。
日期和时间类型
DATETIME这种类型可以保存大范围的数值从1000年到9999年精度为1微秒。它以YYYYMMDDHHMMSS格式存储压缩压缩成整数的日期和时间且与时区无关。但是需要8个字节进行存储。
TIMESTAMP时间戳存储自1970年1月1日格林尼治标准时间午夜以来经过的秒数只需要4个字节就可所以范围要比datatime要小得多只能表示1970年到2038年1月19日时间戳的时间依赖于时区。
数据标识符
数据标识符一般来说标识符是数据行的唯一标识。比如我们最常见的ID就是最常见的标识符。标识符可能是主键中的部分或者是全部。
为标识符选择好数据类型后要确保在所有相关的表中使用的是相同的数据类型否则在进行多表联查的时候就可能会出问题标识符应该与联接表中的对应列的数据类型保持一致。 整数类型整数通常是标识符的最佳选择因为他们的速度快并且可以自动递增。AUTO_INCREMENT是一个列属性可以为新的行自动的生成一个整数类型的值。但是这种类型的标识符也有缺点整数类型可能存在整数意外耗尽的情况从而导致服务器停机所以如果选择了整数类型的数据作位标识符那么应该确保选择合适预期数据增长的整数大小。 字符串类型如果可能应该尽可能的避免使用字符串类型作为标识符的数据类型因为它们非常消耗空间并且通常比整数类型慢特别是在有索引的情况下。
另外对于完全随机的字符串要特别小心如MD5(),SHA1()或者是UUID()生成的字符串这些函数生成的新值会任意分布在很大的空间内这会减慢insert和某些类型的select查询的速度。 因为插入的值会写到索引的随机位置所以会使得INSERT查询变慢这会导致页分裂磁盘随机访问以及对聚簇存储引擎产生聚簇索引碎片。 select查询也会变慢因为逻辑上相邻的行指的是内存中的数据会广泛分布在磁盘和内存中。 对于所有类型的查询随机值都会导致缓存的性能低下因为它们会破坏引用的局部性而这正是缓存的工作原理。
MySQL会对null进行索引但是oracle不会。
