MySQL底层

最新推荐文章于 2024-11-08 17:49:58 发布

流星要去哪里

最新推荐文章于 2024-11-08 17:49:58 发布

阅读量1.3k

点赞数 46

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/wjw2110a/article/details/141169064

版权

Mysql的优化（服务器、数据库、sql）

索引失效

SQL语句、索引

执行一条sql语句期间发什么了什么？
1. 建立连接：经过TCP三次握手，并经过用户名密码验证，超过8小时会连接器会自动断开，最大连接数151
2. 查询缓存：select语句mysql会先去查询缓存数据，在mysql8.0版本开始将这个功能删除了
3. 解析sql：识别关键字，语法分析是否正确
4. 执行sql：
  - 预处理：检查sql语句在表中的字段是否存在
  - 优化器：将sql语句的执行方案确定下来，优先执行成本最小的计划
  - 执行器：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端
什么情况下会导致索引失效？：
1. 使用左或左右模糊匹配有可能会全表扫描
2. 查询条件使用索引函数，改变了原值
3. 对索引进行表达式计算
4. 在遇到数据类型隐式类型转换时，如字符串转为数字
5. Where语句使用or中条件不是索引列

B树、B+树、非聚簇索引、聚簇索引

mysql中的索引有哪些？
什么是聚簇索引和非聚簇索引?
1. 聚簇索引的叶子节点存放的是主键值和数据行，支持覆盖索引
2. 非聚簇索引的叶子节点存放的是主键值或数据记录的地址（InnoDB辅助索引的data域存储相应记录主键的值，MyISAM辅助索引的data域保存数据记录的地址）
B树和B+树索引有什么区别？
1. B-Tree能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。
2. B+Tree是B-Tree的改进版本，同时也是数据库索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高
MyISAM和InnoDB实现B+树索引方式的区别是什么？
1. MyISAM，B+Tree叶节点的data域存放的是数据记录的地址，在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录，这被称为“非聚簇索引
2. InnoDB，其数据文件本身就是索引文件，相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的节点data域保存了完整的数据记录，这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引，这被称为“聚簇索引”或者聚集索引，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。

事务四大特性、并行、隔离级别

Mysql事务四大特性ACID，在InnoDB引擎下：
1. 原子性：事务操作要么都完成，要么都不完成
2. 一致性：在事务执行前后都保证数据完整性
3. 隔离性：事务之间互不影响，不同的隔离级别提供不同隔离效果
4. 持久性：事务一旦提交，数据库的改变是永久的，发生故障也不会丢失，将事务的变成存储到如磁盘来实现。
Mysql事务并行会引发什么问题：
1. 脏读:事务A读取数据时读到了事务B修改了但是未提交的数据，c此时B回滚了数据，A得到了操作中过期的数据，读到了错误的数据，这种现象为脏读。
2. 不可重复读:事务A读取了数据正在进行代码处理，此时事务B提交了对这条数据的修改。此时事务A再去验证时这条数据发生了变化，两次读到的数据不一样，这种现象被称为不可重复读。
3. 幻读:事务A读取到了符合条件的1条数据，事务B也读取到了1条数据，此时B事务增加或减去了1条数据，事务A再次读取的数据数量不一样了，这种现象被称为幻读。
4. 总的来说严重性脏读>不可重复读>幻读,不可重复读是数据发生了变化，幻读是数据条数发生变化。
Mysql事务隔离等级：
1. 读未提交：事务修改了还未提交就被其他事务读到了
2. 读提交：事务提交后才能被其他事务看到
3. 可重复读：一个事务执行过程看到的数据，和启动时候看到的数据是一致的，InnoDB的默认隔离级别
4. 串行化：给数据上锁，当冲突发生的时候必须等待前一个事务完成，才能继续执行。
5. 事务提升至串行化可以解决上述三种事务问题，但是会影响性能，通常使用可重复读作为隔离等级，但要解决幻读问题快照读（普通 select 语句）可以通过MVCC方式解决，即执行和启动看到的数据一致，当前读（select ... for update 等语句）可以通过next-key lock（记录锁+间隙锁）的方式来决绝幻读，即给语句加锁有其他语句插入就会被阻塞。

MVCC

Mysql的MVCC多版本并发控制（读已提交、可重复读）：
1. Mvcc的组成：
  - Undo_Log版本链：每一个事务都会有自己的链，该版本链在所有引用结束前会一直增加版本号直到不再被引用后删除，记录了trx_id(版本号)、DB_ROLL_PTR（唯一标识）
  - ReadView视图：mvcc只有在快照读=select查询语句（当前读=先查询在更改数据，当前读在可重复读的隔离级别会产生新的ReadView）才能实现，读已提交在每次生成ReadView，因此可能会出现不可重复读。可重复读只在第一次执行快照读时生成ReadView，后续进行复用
  - 注意！！：快照读（Snapshot Read）通常不会导致幻读现象，因为InnoDB使用多版本并发控制（MVCC）来处理快照读。然而，如果事务中包含了当前读那么在可重复读隔离级别下，InnoDB会使用Next-Key Locks来防止幻读Next-Key Locks是记录锁和间隙锁的组合，它锁定了索引记录及其前后的间隙，防止其他事务在这些间隙中插入新的记录
  - 优点：不需要加锁，可以提高数据库并发性能，减少锁的使用，死锁的几率就会降低

锁

Mysql中有哪些锁？：
1. 全局锁可以解决备份数据不一致：但是整个数据库处于只读状态，其他线程会被阻塞，比如想要更新数据库时就无法进行别的数据库操作。InnoDB引擎使用可重复读的隔离级别可以解决，在备份数据库之前开启事务会创建Read View，由于MVCC版本控制的支持，依然可以对数据进行更新操作。
2. 表锁：加锁后会限制别的线程读写，也会限制本线程接下来的读写操作，直到锁的释放。尽量避免在使用 InnoDB 引擎的表使用表锁，InnoDB 引擎实现了颗粒度更细的行级锁
3. 行级锁：只有InnoDB 引擎支持主要有三种类：
  - Record Lock（记录锁），也就是仅仅把一条记录锁上，有S 锁（共享锁）和 X 锁（排他）之分的，s锁可以追加s锁但不可加x锁，x锁不可以追加任何锁，当事务执行 commit 后，事务过程中生成的锁都会被释放
  - Gap Lock（间隙锁），只能在可重复读的隔离级别下，锁定一个范围，但是不包括记录本身，其他事务就无法再往里插入数据了，间隙锁的共享锁和排他锁是兼容的，不存在互斥关系，因为间隙锁的目的就是防止幻读
  - Next-Key Lock：Record Lock + Gap Lock（记录锁+间隙锁），锁定一个范围，并且锁定记录本身，无法在返回内插入数据，也无法修改数据本身，一个事务获取了x（排它锁），多个事务项目兼容但另一个事务获取相同的x锁会被阻塞
4. 乐观锁、悲观锁可以表、行锁实现：
  - 乐观锁：读多写少场景，通过在数据库设置一个版本号字段，读取数据时，‌将此版本号一同读出，‌数据每更新一次，‌版本号加1。‌提交更新时，‌若当前版本号与初次取出的版本号一致，‌则进行更新，‌否则视为过期数据
  - 悲观锁：读少写多场景，通过共享锁和排他锁来实现
    - 排它锁：事务开启后主动加锁如UPDATE和LOCK IN SHARE MODE语句属于悲观锁的实现。而直接执行修改语句会自动加上排他锁（当前事务提交或回滚之前，其他事务不能对这些数据行进行修改或读取），同时MVCC 允许其他事务读取到一致性视图（consistent read），即在事务开始时创建的数据快照
    - 共享锁：它允许多个事务读取同一数据，‌但不允许修改，加共享锁时，‌其他事务只能加共享锁，‌不能加排他锁。‌共享锁的实现依赖于数据库的锁机制，‌适用于需要确保数据读取一致性，‌但不涉及数据修改的场景。共享锁进行追加后需要等待所有共享锁释放，‌才能对这条数据进行修改‌
5. mysql事务死锁怎么解决？
  1. 产生原因：
    - 两个事务同时执行，它们将会相互等待对方释放锁，从而导致死锁
    - 事务中执行了多个长时间的查询，很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或死锁
  2. 解决办法：
    - 死锁产生前：尽量使用行级锁而不是表级锁。优化查询语句和减少事务中的操作数来减少事务持续时间。在读多写少的场景中，使用乐观并发控制（OCC）而不是悲观锁定可能更合适。OCC通过在事务提交时检查数据是否已被其他事务修改来避免锁定
    - 死锁产生后：使用锁超时，事务在等待锁超过设定的时间后将自动回滚。启用数据库的死锁检测功能，让数据库管理系统能够自动检测死锁并回滚某个事务来解锁。这通常是最后的手段，因为它可能导致数据不一致的问题。应当只在其他方法都无法实现时使用

Mysql连接池

Mysql连接池的作用:
- 限制连接数量，避免过多的连接导致数据库性能下降
- 限制连接的最长时间，避免数据库连接错误导致的一直得不到释放，造成资源的泄露
- 最大的空闲连接数，避免资源浪费

Mysql的优化（服务器、数据库、sql）

服务器层面：
1. 读写分离：
  1. 读写分离：读写分离就是读操作和写操作从以前的一台服务器上剥离开来，将主库压力分担一些到从库。本质上是因为访问量太大，主库的压力过大，单机数据库无法支撑并发读写。然后一般而言读的次数远高于写，因此将读操作分发到从库上，这就是常见的读写分离。
  2. 读写分离还有个操作就是主库不建查询的索引，从库建查询的索引。因为索引是需要维护的，比如你插入一条数据，不仅要在聚簇索引上面插入，对应的二级索引也得插入，修改也是一样的。所以将读操作分到从库了之后，可以在主库把查询要用的索引删了，减少写操作对主库的影响。
  3. 实现方式：
    1. 做法一:代码封装讲白了就是代码层面抽出一个中间层，由中间层来实现读写分离和数据库连接。就是搞了个代理类，对外暴露正常的读写接口，里面封装了逻辑，将读操作指向从库的数据源，写操作指向主库的数据源。优点:简单，并且可以根据业务定制化变化，随心所欲。缺点:如果数据库容机了，发生主从切换了之后，就得修改配置重启。如果系统是多语言的话需要为每个语言都实现一个中间层代码，重复开发
    2. 做法二：中间件一般而言是独立部署的系统，客户端与这个中间件的交互是通过 SQL协议的。所以在客户端看来连接的就是一个数据库，通过 SQL 协议交互也可以屏蔽多语言的差异。缺点就是整体架构多了一个系统需要维护，并且可能成为性能瓶颈，毕竟交互都需要经过它中转。常见的开源数据库中间件有:官方的 MySQL-Proxy、360的 Atlas、ShardingSphere、Mycat 等
2. 主从复制：
  1. 主服务器开启二进制日志（binlog日志）
  2. 从服务器通过io线程读取主服务器二进制日志并写入从服务器本地中继日志（relay log）日志中
  3. 从服务器的sql线程从中继日志中读取并执行sql语句，通过这三步同步主服务器和从服务器的数据

数据库层面：
1. 分库：
  1. 水平分库：按我的理解来说就是相同的表结构复制一份分到另一个库中，每个库的表结构是一样的，但是数据是不一样的。这样做能在大数量的情况下提高读写性能，因为减少了单一数据库的读写压力。能提高存储容量。当一个数据库故障了，别的数据库还能正常运行，只影响小部分数据查询
  2. 垂直分库：按我个人理解就是把一个数据库里面的多个表，按照功能，分成多个数据库存放。举个例子:数据库有很多张表，用户表、商品表、订单表等，那么可以根据功能属性进行垂直分库，将用户表等信息放到存放用户信息的数据库，将商品表、订单表存放到与商品订单有关的商品订单库。这样做的好处就是将数据负载分散到不同的数据库上，从而提高系统的性能和扩展性、降低单一数据库的复杂度。
2. 分表：
  1. 水平分表：我个人的理解就是在同一个数据库中，有几个相同表，里面的数据是不一样的，但表结构是一样的数据按照固定的规则选择数据表存放，如:商品表1、商品表2。提高了读写性能，减少了单表的压力、可弹性增加存储容量只需要增加一个表就行。
  2. 垂直分表：垂直分表一般是将不常用的字段单独放在一张表、将大字段分一张表、把经常需要同时査出来的信息放一张表。这样做可以冷数据和热数据分开提高查询效率。
sql语句层面？
1. 根据慢 SQL 日志，找出需要优化的一些语句。常见优化方向:
  1. 避免 SELECT*，只查询必要的字段
  2. 避免在 SQL 中进行函数计算等操作，使得无法命中索引
  3. 避免使用 %LKE，导致全表扫描
  4. 注意联合索引需满足最左匹配原则
  5. 不要对无索引字段进行排序操作
  6. 连表查询需要注意不同字段的字符集是否一致，否则也会导致全表扫描
2. ExPlain性能查询