MySQL月末知识总结

2401_84103045

于 2024-04-30 05:41:58 发布

阅读量536

点赞数 26

分类专栏：程序员文章标签： mysql 数据库

本文链接：https://blog.csdn.net/2401_84103045/article/details/138332385

版权

程序员专栏收录该内容

239 篇文章 0 订阅

订阅专栏

的基本思路是在数据中增加一个字段version，表示该数据的版本号，每当数据被修改，版本号加1。

当某个线程查询数据时，将该数据的版本号一起查出来；
当该线程更新数据时，判断当前版本号与之前读取的版本号是否一致，如果一致才进行操作

悲观锁

===

悲观锁在操作数据时比较悲观，认为别人会同时修改数据。

因此操作数据时直接把数据锁住，直到操作完成后才会释放锁；上锁期间其他人不能修改数据。

3.4自增锁

======

自增锁（AUTO-INC锁）

自增锁是一种特殊的表级锁，主要用于事务中插入自增字段，也就是我们最常用的自增主键id。

通过innodb_autoinc_lock_mode参数可以设置自增主键的生成策略。防止并发插入数据的时候自增id出现异常。

当一张表的某个字段是自增列时，innodb会在该索引的末位加一个排它锁。

**为了访问这个自增的数值，需要加一个表级锁，不过这个表级锁的持续时间只有当前sql，**而不是整个事务，即当前sql执行完，该表级锁就释放了。其他session无法在这个表级锁持有时插入任何记录。

四、MySQL InnoDB存储引擎

==================

InnoDB优势

========

1）支持事务

======

InnoDB 最重要的一点就是支持事务，可以说这是 InnoDB 成为 MySQL 中最流行的存储引擎的一个非常重要的原因

InnoDB 还实现了 SQL92 标准所定义的 4 个隔离级别

2）灾难恢复性好

========

commit、rollback、crash-recovery 来保障数据的安全

3）使用行级锁

=======

InnoDB 的行锁机制是通过索引来完成的，但毕竟在数据库中 99%的 SQL 语句都要使用索引来检索数据。行锁定机制也为 InnoDB 在承受高并发压力的环境下增强了不小的竞争力。

4）实现了缓冲处理

=========

InnoDB 提供了专门的缓存池，实现了缓冲管理，不仅能缓冲索引也能缓冲数据，常用的数据可以直接从内存中处理，比从磁盘获取数据处理速度要快。

当一条 SQL 执行的时候，如果是读操作，要查找的数据所在的数据页在内存中时，则将结果返回。否则会把对应的数据页加载到内存中，然后再返回结果。

同样对于写操作来说。如果要修改的行所在的数据页在内存中，则修改后返回对应的结果（当然还有后续操作）。如果不在的话，则会从磁盘里将该行所对应的数据页读到内存中再进行修改。

1.缓冲池的预读机制

==========

InnoDB使用两种预读算法来提高I/O性能：线性预读（linear read-ahead）和随机预读

会产生两个问题

缓冲池污染问题

还有一种情况是当执行一条 SQL 语句时，如果扫描了大量数据或是进行了全表扫描，此时缓冲池中就会加载大量的数据页，从而将缓冲池中已存在的所有页替换出去，这种情况同样是不合理的。这就是缓冲池污染，并且还会导致 MySQL 性能急剧下降。

预读失效问题

被预先加载进缓冲池的页，并没有被访问到

2.缓冲刷新策略

========

通常来说，缓冲池是通过LRU（Latest Recent Used，最近最少使用）

5）文件大小不受限制

==========

InnoDB 的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘分区）。

这与 MyISAM 表不同，比如在 MyISAM 表中每个表被保存在分离的文件中。

**InnoDB 表可以是任何尺寸，**即使在文件尺寸被限制为 2GB 的操作系统上。

6）支持外键

======

InnoDB 支持外键约束，检查外键、插入、更新和删除，以确保数据的完整性。

在存储表中数据时每张表的存储都按主键顺序存放，如果没有显式地在定义表时指定主键，InnoDB 会为每一行生成一个 6 字节的 ROWID ，并以此作为主键。

物理存储

====

1. 数据文件（表数据和索引数据）

==================

数据文件用来存放数据表中的数据和所有的索引数据，包括主键和其他普通索引。

InnoDB 存储的数据采用表空间（Tablepace）进行存放设计。表空间是用来存放 MySQL 系统相关信息的一个特殊共享表空间。

InnoDB 的表空间分为以下两种形式：

共享表空间，表数据和索引都存放在同一个表空间。默认的表空间文件就是上面所提到的 MySQL 初始化路径下的 ibdata1 文件。
独立表空间，每个表的数据和索引被存放在一个单独的 .ibd 文件中。

InnoDB 的表空间分为以下两种形式：

共享表空间，表数据和索引都存放在同一个表空间。默认的表空间文件就是上面所提到的 MySQL 初始化路径下的 ibdata1 文件。
独立表空间，每个表的数据和索引被存放在一个单独的 .ibd 文件中。

2. 日志文件

========

默认情况下，InnoDB 存储引擎的数据目录下会有两个名为 ib_logfile0 和 ib_logfile1 的文件。在 MySQL 官方手册中将其称为 InnoDB 存储引擎的重做日志文件（redo log file）。

每个 InnoDB 存储引擎至少有 1 个重做日志文件组（group），

每个文件组下至少有 2 个重做日志文件，如默认的 ib_logfile0 和 ib_logfile1。

五、MySql相关小知识

============

1.自增主键用完了该怎么办

=============

旦自增id达到最大值，此时数据继续插入是会报一个主键冲突异常如下所示

//Duplicate entry ‘4294967295’ for key ‘PRIMARY’

解决方法：将Int类型改为BigInt类型

1.1线上怎么修改列的数据类型的

================

方式一:使用mysql5.6+提供的在线修改功能

对于修改数据类型这种操作，是不支持并发的DML操作！也就是说，如果你直接使用ALTER这样的语句在线修改表数据结构，会导致这张表无法进行更新类操作(DELETE、UPDATE、DELETE)。因此，直接ALTER是不行滴！

方式二:借助第三方工具

1、pt-online-schema-change，简称pt-osc - 2、GitHub正式宣布以开源的方式发布的工具，名为gh-ost

如果你的表里有触发器和外键，这两个工具是不行

方式三:改从库表结构，然后主从切换

mysql架构一般是读写分离架构，从机是用来读的。我们直接在从库上进行表结构修改，不会阻塞从库的读操作。改完之后，进行主从切换即可。

可能会有数据丢失的情况

但是：

一般达不到最大值，我们就分库分表了，所以不曾遇见过！"

2.char和varchar的区别

=================

char类型的长度是固定的，varchar的长度是可变的。这就表示，存储字符串’abc’，使用char(10)，表示存储的字符将占10个字节，如果不足10字节，将使用空格占位，所以检索CHAR值时需删除尾随空格使用varchar2(10),，则表示只占3个字节，10是最大值，当存储的字符小于10时，按照实际的长储。
2.char类型的效率比varchar的效率稍高

3.set字段类型

=========

SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。

指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。这样SET成员值本身不能包含逗号。

SET最多可以有64个不同的成员。

当创建表时，SET成员值的尾部空格将自动被删除。

4.BLOB 和 TEXT 有什么区别？

====================

TEXT与BLOB的主要差别就是BLOB保存二进制数据，TEXT保存字符数据。。

主要差别

TEXT与BLOB的主要差别就是BLOB保存二进制数据，TEXT保存字符数据。

目前几乎所有博客内容里的图片都不是以二进制存储在数据库的，而是把图片上传到服务器然后正文里使用

标签引用，这样的博客就可以使用TEXT类型。而BLOB就可以把图片换算成二进制保存到数据库中。

5.MySQL数据库预计运维三年,怎么优化？

======================

1、设计良好的数据库结构，允许部分数据冗余，尽量避免 join 查询，提高效率。

2、选择合适的表字段数据类型和存储引擎，适当的添加索引。

3、MySQL 库主从读写分离。

4、找规律分表，减少单表中的数据量提高查询速度。

5、添加缓存机制，比如 memcached，apc 等。

6、不经常改动的页面，生成静态页面。

7、书写高效率的 SQL。比如 SELECT ***** FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE

6.什么是存储过程？用什么来调用？

=================

存储过程是一个预编译的 SQL 语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。

如果某次操作需要执行多次 SQL，使用存储过程比单纯 SQL 语句执行要快。可以用一个命令对象来调用存储过程

7.什么是触发器，可以用来做什么

================

触发器是与 MySQL 数据表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合

触发器的这种特性可以协助应用在数据库端确保数据的完整性。

六、MySQL索引底层实现原理

===============

索引是数据结构。

数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的。

计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等

但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

6.1B树

=====

B树事实上是一种平衡的多叉查找树，也就是说最多可以开m个叉（m>=2）

二、五阶B树

MySQL月末知识汇总

B树的查询过程和二叉排序树比较类似，从根节点依次比较每个结点，因为每个节点中的关键字和左右子树都是有序的，所以只要比较节点中的关键字，或者沿着指针就能很快地找到指定的关键字，如果查找失败，则会返回叶子节点，即空指针。

6.2Plus版 — B+树

==============

作为B树的加强版，B+树与B树的差异在于

所有的关键字全部存储在叶子节点上，且叶子节点本身根据关键字自小而大顺序连接。
非叶子节点可以看成索引部分，节点中仅含有其子树（根节点）中的最大（或最小）关键字。
有n棵子树的节点含有n个关键字（也有认为是n-1个关键字）。

查找：

===

B+树的查找过程，与B树类似，只不过查找时，如果在非叶子节点上的关键字等于给定值，并不终止，而是继续沿着指针直到叶子节点位置。因此在B+树，不管查找成功与否，每次查找都是走了一条从根到叶子节点的路径。

特性

所有关键字都存储在叶子节上，且链表中的关键字恰好是有序的。
不可能非叶子节点命中返回。
非叶子节点相当于叶子节点的索引，叶子节点相当于是存储（关键字）数据的数据层。
更适合文件索引系统。

6.3为什么使用B树（B+树）

===============

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。

换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。下面先介绍内存和磁盘存取原理，然后再结合这些原理分析B-/+Tree作为索引的效率

主存存取原理

======

当系统需要读取主存时，则将地址信号放到地址总线上传给主存，主存读到地址信号后，解析信号并定位到指定存储单元，然后将此存储单元数据放到数据总线上，供其它部件读取。

主存存取的时间仅与存取次数呈线性关系，因为不存在机械操作

磁盘存取原理

======

当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁盘，

磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。

为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。

局部性原理与磁盘预读

==========

局部性原理：**当一个数据被用到时，其附近的数据也通常会马上被使用。**从这个位置开始，顺序向后读取一定长度的数据放入内存

磁盘预读：预读的长度一般为页（page）的整倍数。

页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k）

主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

此时如果缓存空间满了，就会触发缓存过期策略

缓存过期策略：

=======

FIFO：First In First Out，先进先出

LRU：Least Recently Used，最近最少使用

LFU：Least Frequently Used，最不经常使用

性能分析总结

======

数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)O(h)=O(logdN)。

一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。

（h表示树的高度 & 出度d表示的是树的度，即树中各个节点的度的最大值）

而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树效率明显比B-Tree差很多。

综上所述，用B-Tree作为索引结构效率是非常高的。

此外：因为数据都是存储在叶子节点上的，所以非叶子节点上没有数据域，因此可以拥有更大的出度，从而拥有更好的性能。

6.4MySQL索引实现

============

MYISAM索引实现

==========

MySQL月末知识汇总

可以看出MyISAM的索引文件仅仅保存数据记录的地址。

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

==========

MySQL月末知识汇总

在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。

这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

与myisam不同点

==========

叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有）
第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

使用聚簇索引的优势

=========

辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；如果存储完整记录，则需要对辅助索引维护，只存主键值，那么只需要维护聚簇索引树就可以了。
因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。
聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的非聚簇索引，那么他的数据的物理地址必然是凌乱的，为了拿到数据，需要不停的寻道不停的旋转。而聚簇索引则只需一次I/O就可以获得一页的数据。（强烈的对比）

6.5总结

=====

1.索引是利用空间换取时间，将数据的引用按照合适的数据结构存储，就是索引

2.索引使用b+树，因为b+树很适合文件系统查找

3.了解索引的实现，在创建索引的时候，可以提升表的效率

七、Mysql优化

=========

Mysql的优化，大体可以分为三部分：索引的优化，sql语句的优化，表的优化

1.索引优化

======

一般的应用系统，读写比例在10：1左右，在生产环境中，我们遇到最多的也是最容易出现问题的，还是一些复杂的查询操作，

因此对查询语句的优化是重中之重，加速查询最好的方法就是索引。

索引类型

====

普通索引：是最基本的索引，它没有任何限制。

唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。

主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引

全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。它更像是一个搜索引擎

索引优化

====

只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
尽量使用短索引，如果可以，应该制定一个前缀长度
对于经常在where子句使用的列，最好设置索引，这样会加快查找速度
对于有多个列where或者order by子句的，应该建立复合索引
对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
尽量不要在列上进行运算（函数操作和表达式操作）
尽量不要使用not in和<>操作

2.sql慢查询优化

==========

操作

最后

终极手撕架构师的学习笔记：分布式+微服务+开源框架+性能优化

：1左右，在生产环境中，我们遇到最多的也是最容易出现问题的，还是一些复杂的查询操作，

因此对查询语句的优化是重中之重，加速查询最好的方法就是索引。

索引类型

====

普通索引：是最基本的索引，它没有任何限制。

唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。

主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引

全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。它更像是一个搜索引擎

索引优化

====

只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
尽量使用短索引，如果可以，应该制定一个前缀长度
对于经常在where子句使用的列，最好设置索引，这样会加快查找速度
对于有多个列where或者order by子句的，应该建立复合索引
对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
尽量不要在列上进行运算（函数操作和表达式操作）
尽量不要使用not in和<>操作

2.sql慢查询优化

==========

操作

最后

终极手撕架构师的学习笔记：分布式+微服务+开源框架+性能优化

[外链图片转存中…(img-9HSU23Zd-1714426906653)]

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

2401_84103045

关注

26
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
MySQL月末知识总结

终极手撕架构师的学习笔记：分布式+微服务+开源框架+性能优化：1左右，在生产环境中，我们遇到最多的也是最容易出现问题的，还是一些复杂的查询操作，因此对查询语句的优化是重中之重，加速查询最好的方法就是索引。索引类型====普通索引：是最基本的索引，它没有任何限制。唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。
复制链接

扫一扫