MySQL锁的类型事务的基本特性和隔离级别 ACID靠什么保证什么是MVCC 主从同步原理 MyISAM和InnoDB的区别索引类型及影响执行计划怎么看慢查询优化

本文链接：https://blog.csdn.net/zwb_dzw/article/details/128715748

五、分表后非sharding_key的查询怎么处理，分表后的排序？

六、mysql主从同步原理

七、MyISAM和InnoDB的区别

八、mysql中索引类型及对数据库的性能的影响

九、mysql执行计划怎么看

十、业务系统里面的sql耗时吗？统计过慢查询吗？对慢查询都怎么优化过？

一、MySQL锁的类型

锁的类型有哪些

基于锁的属性分类：共享锁、排他锁。
基于锁的粒度分类：行级锁(INNODB)、表级锁(INNODB、MYISAM)、页级锁(BDB引擎 )、记录锁、间隙锁、临键锁。
基于锁的状态分类：意向共享锁、意向排它锁。

共享锁 (Share Lock)

共享锁又称读锁，简称S锁；当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能对数据加写锁，直到所有的读锁释放之后其他事务才能对其进行加持写锁。共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免出现重复读的问题。

排他锁 （ eXclusive Lock ）

排他锁又称写锁，简称X锁；当一个事务为数据加上写锁时，其他请求将不能再为数据加任何锁，直到该锁释放之后，其他事务才能对数据进行加锁。排他锁的目的是在数据修改时候，不允许其他人同时修改，也不允许其他人读取。避免了出现脏数据和脏读的问题。

表锁

表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访问；

特点：粒度大，加锁简单，容易冲突；

行锁

行锁是指上锁的时候锁住的是表的某一行或多行记录，其他事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问；

特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高；

页锁

页级锁是 MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折中的页级，一次锁定相邻的一组记录。

特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般；

间隙锁

属于行锁中的一种，间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻 ID之间出现空隙则会形成一个区间，遵循左开右闭原则。

范围查询并且查询未命中记录，查询条件必须命中索引、间隙锁只会出现在 REPEATABLE_READ重复读的事务级别中。

触发条件：防止幻读问题，事务并发的时候，如果没有间隙锁，就会发生如下图的问题，在同一个事务里， A 事务的两次查询出的结果会不一样。

比如表里面的数据 ID 为 1,4,5,7,10 , 那么会形成以下几个间隙区间， -n-1 区间， 1-4 区间，7-10 区间， 10-n 区间（ -n 代表负无穷大， n 代表正无穷大）

临建锁

也属于行锁的一种，并且它是INNODB的行锁默认算法，总结来说它就是记录锁和间隙锁的组合，临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住。

触发条件：范围查询并命中，查询命中了索引。

结合记录锁和间隙锁的特性，临键锁避免了在范围查询时出现脏读、重复读、幻读问题。加了临键锁之后，在范围区间内数据不允许被修改和插入。

意向锁

如果当事务A加锁成功之后就设置一个状态告诉后面的人，已经有人对表里的行加了一个排他锁了，你们不能对整个表加共享锁或排它锁了，那么后面需要对整个表加锁的人只需要获取这个状态就知道自己是不是可以对表加锁，避免了对整个索引树的每个节点扫描是否加锁，而这个状态就是意向锁。

意向共享锁

当一个事务试图对整个表进行加共享锁之前，首先需要获得这个表的意向共享锁。

意向排他锁

当一个事务试图对整个表进行加排它锁之前，首先需要获得这个表的意向排它锁。

InnoDB 存储引擎的锁的算法

Record lock ：单个行记录上的锁

Gap lock ：间隙锁，锁定一个范围，不包括记录本身

Next-key lock ： record+gap 锁定一个范围，包含记录本身

1. innodb 对于行的查询使用 next-key lock

2. Next-locking keying 为了解决 Phantom Problem 幻读问题

3. 当查询的索引含有唯一属性时，将 next-key lock 降级为 record key

4. Gap 锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生

5. 有两种方式显式关闭 gap 锁：（除了外键约束和唯一性检查外，其余情况仅使用 record lock ） A.

将事务隔离级别设置为 RC B. 将参数 innodb_locks_unsafe_for_binlog 设置为 1

二、事务的基本特性和隔离级别

事务基本特性 ACID 分别是：

原子性 指的是一个事务中的操作要么全部成功，要么全部失败。

一致性 指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如 A 转账给 B100块钱，假设 A 只有 90 块，支付之前我们数据库里的数据都是符合约束的 , 但是如果事务执行成功了 ,我们的数据库数据就破坏约束了 , 因此事务不能成功 , 这里我们说事务提供了一致性的保证。

隔离性 指的是一个事务的修改在最终提交前，对其他事务是不可见的。

持久性 指的是一旦事务提交，所做的修改就会永久保存到数据库中。

隔离性有 4 个隔离级别，分别是：

read uncommit 读未提交，可能会读到其他事务未提交的数据，也叫做脏读。

用户本来应该读取到 id=1 的用户 age 应该是 10，结果读取到了其他事务还没有提交的事务，结果读取结果 age=20 ，这就是脏读。

read commit 读已提交，一个事务里两次读取结果不一致，叫做不可重复读。

不可重复读解决了脏读的问题，他只会读取已经提交的事务。

用户开启事务读取 id=1 用户，查询到 age=10 ，再次读取发现结果 =20，在同一个事务里同一个查询读取到不同的结果叫做不可重复读。

repeatable read 可重复复读，这是 mysql的默认级别，就是一个事务里多次读取结果都一致，但是有可能产生幻读。

serializable 串行，一般是不会使用的，他会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题。

问题

脏读 (Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个 RollBack 了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读 (Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读 (Phantom Read): 在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

详见另一篇文件介绍。

三、ACID靠什么保证的？

A原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql。
C一致性由其他三大特性保证、程序代码要保证业务上的一致性。
I隔离性由MVCC来保证。
D持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，宕机的时候可以从redo log恢复。

InnoDB redo log 写盘， InnoDB 事务进入 prepare 状态。

如果前面 prepare 成功， binlog 写盘，再继续将事务日志持久化到 binlog，如果持久化成功，那么InnoDB 事务则进入 commit 状态 ( 在 redo log 里面写一个 commit 记录 )。

redolog 的刷盘会在系统空闲时进行。

四、什么是MVCC

多版本并发控制：读取数据时通过一种类似快照的方式将数据保存下来，这样读锁就和写锁不冲突了，不同的事务 session 会看到自己特定版本的数据，版本链。

MVCC 只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别够和MVCC 不兼容 , 因为 READ UNCOMMITTED 总是读取最新的数据行 , 而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

聚簇索引记录中有两个必要的隐藏列：

trx_id ：用来存储每次对某条聚簇索引记录进行修改的时候的事务 id 。

roll_pointer ：每次对哪条聚簇索引记录有修改的时候，都会把老版本写入 undo日志中。这个 roll_pointer就是存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获得上一个版本的记录信息。 ( 注意插入操作的 undo 日志没有这个属性，因为它没有老版本 )。

开始事务时创建readview，readView维护当前活动的事务id，即未提交的事务id，排序生成一个数组。

访问数据，获取数据中的事务 id （获取的是事务 id 最大的记录），对比 readview ：

如果在readview的左边（比readview都小），可以访问（在左边意味着该事务已经提交）。

如果在readview的右边（比readview都大）或者就在readview中，不可以访问，获取roll_pointer，取上一版本重新对比（在右边意味着，该事务在readview生成之后出现，在readview中意味着该事务还未提交）。

已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的 ReadView,而可重复读隔离级别则在第一次读的时候生成一个 ReadView ，之后的读都复用之前的 ReadView 。

这就是 Mysql 的 MVCC, 通过版本链，实现多版本，可并发读 - 写，写 - 读。通过 ReadView生成策略的不同实现不同的隔离级别。

已提交读和可重复读的区别就在于它们生成 ReadView 的策略不同 。

五、分表后非sharding_key的查询怎么处理，分表后的排序？

1. 可以做一个 mapping 表，比如这时候商家要查询订单列表怎么办呢？不带user_id查询的话你总不能扫全表吧？所以我们可以做一个映射关系表，保存商家和用户的关系，查询的时候先通过商家查询到用户列表，再通过 user_id 去查询。

2. 宽表，对数据实时性要求不是很高的场景，比如查询订单列表，可以把订单表同步到离线（实时）数仓，再基于数仓去做成一张宽表，再基于其他如 es 提供查询服务。

3. 数据量不是很大的话，比如后台的一些查询之类的，也可以通过多线程扫表，然后再聚合结果的方式来做。或者异步的形式也是可以的。

union

排序字段是唯一索引：

首先第一页的查询：将各表的结果集进行合并，然后再次排序。

第二页及以后的查询，需要传入上一页排序字段的最后一个值，及排序方式。

根据排序方式，及这个值进行查询。如排序字段 date ，上一页最后值为 3，排序方式降序。查询的时候 sql 为 select ... from table where date < 3 order by date desc limit 0,10。这样再将几个表的结果合并排序即可。

六、mysql主从同步原理

主从同步过程：

Mysql 的主从复制中主要有三个线程： master （ binlog dump thread ）、 slave （ I/O thread 、SQL thread ）， Master 一条线程和 Slave 中的两条线程。

主节点 binlog，主从复制的基础是主库记录数据库的所有变更记录到 binlog。binlog 是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件。
主节点 log dump 线程，当 binlog 有变动时，log dump 线程读取其内容并发送给从节点。
从节点 I/O线程接收 binlog 内容，并将其写入到 relay log 文件中。

从节点的 SQL 线程读取 relay log 文件内容对数据更新进行重放，最终保证主从数据库的一致性。

注：主从节点使用 binglog 文件 + position 偏移量来定位主从同步的位置，从节点会保存其已接收到的偏移量，如果从节点发生宕机重启，则会自动从 position 的位置发起同步。

由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

全同步复制

主库写入 binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

半同步复制

和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回 ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

mysql支持的复制类型：

　　（１）：基于语句的复制：在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。

一旦发现没法精确复制时，会自动选着基于行的复制。

　　（２）：基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持

　　（３）：混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

复制解决的问题

MySQL复制技术有以下一些特点：

(1) 数据分布 (Data distribution )

(2) 负载平衡(load balancing)

(3) 备份(Backups)

(4) 高可用性和容错行 High availability and failover

七、MyISAM和InnoDB的区别

MyISAM ：

不支持事务，但是每次查询都是原子的；

支持表级锁，即每次操作是对整个表加锁；

存储表的总行数；

一个 MYISAM 表有三个文件：索引文件、表结构文件、数据文件；

采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

InnoDb ：

支持 ACID 的事务，支持事务的四种隔离级别；

支持行级锁及外键约束：因此可以支持写并发；

不存储总行数；

一个 InnoDb引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为 2G），受操作系统文件大小的限制；

主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持 B+ 树结构，文件的大调整。

八、mysql中索引类型及对数据库的性能的影响

普通索引：允许被索引的数据列包含重复的值。

唯一索引：可以保证数据记录的唯一性。

主键索引：是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字 PRIMARY KEY 来创建。

联合索引：索引可以覆盖多个数据列，如像 INDEX(columnA, columnB) 索引，遵循最左匹配原则。

全文索引：通过建立倒排索引 , 可以极大的提升检索效率 ,解决判断字段是否包含的问题，是目前搜索引擎使用的一种关键技术。可以通过 ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引索引可以极大的提高数据的查询速度。

通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大，如果非聚集索引很多，一旦聚集索引改变，那么所有非聚集索引都会跟着变。

九、mysql执行计划怎么看

执行计划就是 sql 的执行查询的顺序，以及如何使用索引查询，返回的结果集的行数。

EXPLAIN SELECT * from A where X=? and Y=?

id ：是一个有顺序的编号，是查询的顺序号，有几个 select 就显示几行。id的顺序是按 select 出现的顺序增长的。id列的值越大执行优先级越高越先执行，id列的值相同则从上往下执行，id列的值为NULL最后执行。
selectType 表示查询中每个select子句的类型。
table：表示该语句查询的表。
type：优化sql的重要字段，也是我们判断sql性能和优化程度重要指标。
possible_keys：它表示Mysql在执行该sql语句的时候，可能用到的索引信息，仅仅是可能，实际不一定会用到。
key：此字段是 mysql 在当前查询时所真正使用到的索引。他是possible_keys的子集。
key_len：表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否完全被使用，这也是我们优化sql时，评估索引的重要指标。
rows：mysql 查询优化器根据统计信息，估算该sql返回结果集需要扫描读取的行数，这个值相关重要，索引优化之后，扫描读取的行数越多，说明索引设置不对，或者字段传入的类型之类的问题，说明要优化空间越大。
filtered：返回结果的行占需要读到的行(rows列的值)的百分比，就是百分比越高，说明需要查询到数据越准确，百分比越小，说明查询到的数据量大，而结果集很少。
extra