【Mysql的脉络】

Gbh.

已于 2023-06-06 10:11:44 修改

阅读量49

点赞数

文章标签： mysql 数据库 sql

于 2023-06-05 20:00:40 首次发布

本文链接：https://blog.csdn.net/weixin_42551107/article/details/131049936

版权

说一下mysql的工作原理

MySQL是一种关系型数据库管理系统，其工作原理主要分为以下三个部分：

1.客户端连接：用户通过客户端连接到MySQL服务器，发送SQL语句请求操作数据库。

2.解析器：MySQL服务器接收到SQL语句后，首先由解析器对SQL语句进行语法分析，将SQL语句转换为内部操作指令。

3.分析器：MySQL服务器对SQL语句进行分析，判断需要访问哪些表，哪些索引，并根据查询条件进行查询优化，以提高查询效率。

4.优化器：MySQL服务器对SQL语句进行优化，包括选择最优的访问路径、排序、聚合和索引等。

5.执行器：MySQL服务器对优化后的查询指令进行执行，以访问存储引擎中的数据。

6.存储引擎：MySQL服务器使用存储引擎来管理数据的存储和访问。存储引擎负责将数据保存在磁盘上，并提供读写数据的接口。MySQL支持多种存储引擎，如InnoDB、MyISAM等。

通过以上工作原理，MySQL实现了高性能的数据存储和查询功能，成为了广泛应用的数据库管理系统之一。

mysql的锁

唯一索引等值查询：

当查询的记录是存在的，next-key lock 会退化成「记录锁」。
当查询的记录是不存在的，next-key lock 会退化成「间隙锁」。
MySQL的锁和事务都是数据库中常见的概念，它们分别用于控制并发操作和维护数据的一致性。

MySQL提供了多种不同类型的锁，包括共享锁（Shared Lock）、排他锁（Exclusive Lock）、行锁（Row Lock）和表级锁（Table Lock）等。这些锁能够在并发操作时保证数据的正确性。

共享锁（Shared Lock）：共享锁用于读操作，多个事务可以同时获取共享锁，但是不能获取排他锁或行锁。
排他锁（Exclusive Lock）：排他锁用于写操作，同一时间只能有一个事务获取排他锁，其他事务不能获取任何锁。
行锁（Row Lock）：行锁在操作记录级别上加锁，可以避免多个事务同时修改同一行记录的问题。
表级锁（Table Lock）：表级锁在整个表上加锁，可以保证整个表的完整性，但是锁定期间其他事务不能读写，影响并发性能。

事务的了解

事务是一组操作，这些操作要么全部执行成功，要么全部失败，保证了数据的一致性，常见的MySQL事务类型有四种：

READ UNCOMMITTED（未提交读）：读取数据时不加锁，其他事务可以随时修改数据。
READ COMMITTED（提交读）：读取数据时加行锁，保证读取数据时其他事务不能修改该行数据，但是读取到的数据可能已经被修改或删除了。
REPEATABLE READ（可重复读）：读取数据时加行锁或快照，保证在事务执行期间，读取的数据不会被其他事务修改。
SERIALIZABLE（串行化）：事务串行执行，保证读写数据的完全隔离，避免了脏读、幻读、不可重复读等问题。

总之，MySQL的锁和事务是非常重要的，它们对于保证数据的正确性和并发性能都有着至关重要的作用，因此开发人员在使用MySQL时一定要了解并熟练掌握这些概念。
ACID是指数据库事务的四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。这四个特性保证了数据库事务的正确执行和数据的完整性。

MySQL实现ACID事务的原理如下：

原子性：MySQL使用redo日志和undo日志来实现事务的原子性。redo日志记录了数据修改的情况，undo日志记录了数据修改前的状态。在事务执行过程中，redo日志先记录数据修改，如果事务执行失败，则用undo日志将数据修改回原来的状态。
一致性：MySQL使用锁机制来实现事务的一致性。在事务执行期间，其他事务不能修改被锁定的数据，以保证数据的一致性。
隔离性：MySQL使用多版本并发控制（MVCC）来实现事务的隔离性。MVCC在每个修改的数据上都保存了一个版本号，事务读取数据时只能读取比它先提交的版本号低的数据，这样可以避免读取到其他事务未提交的数据，保证了隔离性。
持久性：MySQL使用redo日志和binlog来实现事务的持久性。redo日志记录数据修改的情况，并将其写入磁盘，binlog记录了SQL语句操作的情况，也会写入磁盘。这样即使服务器断电或者崩溃，事务提交前的数据也能得到恢复，保证了持久性。
mysql的启动原理
MySQL的启动原理可以简单分为以下五个步骤：
初始化系统参数：MySQL在启动时会读取系统参数文件（my.cnf）并初始化系统参数，这些参数包括MySQL的默认字符集、存储引擎、线程池等等。
分配内存和打开日志文件：MySQL在启动时需要分配内存，包括内存池、缓冲区、线程栈等等，并打开日志文件，包括错误日志、慢查询日志和二进制日志。
加载存储引擎：MySQL支持多种存储引擎，如InnoDB、MyISAM等，启动时需要加载需要使用的存储引擎。
启动线程和监听端口：MySQL启动时会创建多个线程，如主线程、IO线程、查询线程等等，并开始监听指定的端口，等待客户端连接。
处理客户端请求：当客户端连接到MySQL服务器时，MySQL会创建一个新的线程处理客户端请求，根据SQL语句查询数据，并将结果返回给客户端，直到客户端断开连接。

以上是MySQL的简单启动过程，具体情况可能会因为不同版本或者环境而有所差异。

幻读怎么解决

MVCC加上间隙锁的方式
（1）在快照读读情况下，mysql通过mvcc来避免幻读。
（2）在当前读读情况下，mysql通过next-key来避免幻读。锁住某个条件下的数据不能更改。

快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

select * from table where ?;
1
当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;

一条SQL语句执行得很慢的原因有哪些

一个 SQL 执行的很慢，我们要分两种情况讨论：

1、大多数情况下很正常，偶尔很慢，则有如下原因

(1)、数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。

(2)、执行的时候，遇到锁，如表锁、行锁。

2、这条 SQL 语句一直执行的很慢，则有如下原因。

(1)、没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。

(2)、数据库选错了索引。

explain你记得哪些字段，分别有什么含义？

1.建立必要索引对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

选错索引强制索引

select * from salaries force index(idx_emp_no ) where emp_no=10005

普通索引（非聚簇）先记录change buffer

on条件是在生成临时表时使用的条件,它不管on中的条件是否为真都会返回左表中的记录。

where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左表的记录)

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null – 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0

3.应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20 – 可以这样查询： select id from t where num=10 union all select id from t where num=20

5.in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3) – 对于连续的数值，能用 between 就不要用 in 了： select id from t where num between 1 and 3

6.下面的查询也将导致全表扫描：select id from t where name like ‘%李%’若要提高效率，可以考虑全文检索。

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：select id from t where num=@num – 可以改为强制查询使用索引： select id from t with(index(索引名)) where num=@num

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100 – 应改为: select id from t where num=100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)=’abc’ – name以abc开头的id应改为: select id from t where name like ‘abc%’

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。