MYSQL 那点破事！索引、SQL调优、事务、B+树、分表 ....

大家好，我是Tom哥~

为了便于大家查找问题，了解全貌，整理个目录，我们可以快速全局了解关于mysql数据库，面试官一般喜欢问哪些问题

接下来，我们逐条来看看每个问题及答案

MyISAM 和 InnoDB 的区别？

答案：InnoDB 支持 事务、外键、聚集索引，通过MVCC来支持高并发，索引和数据存储在一起。InnoDB 不保存表的具体行数，执行 select count() from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数。

InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁，并发能力低。MySQL 将默认存储引擎是 InnoDB

mysql 锁有哪些类型？

答案：mysql锁分为共享锁( S lock ) 、排他锁 ( X lock )，也叫做读锁和写锁。根据粒度，可以分为表锁、页锁、行锁。

什么是间隙锁？

答案：间隙锁是可重复读级别下才会有的锁，mysql会帮我们生成了若干 左开右闭 的区间，结合MVCC和间隙锁可以解决幻读问题。

如何避免死锁？

答案：死锁的四个必要条件：1、互斥 2、请求与保持 3、环路等待 4、不可剥夺。

数据库的隔离级别？

答案：读未提交、读已提交、可重复读（mysql的默认级别，每次读取结果都一样，但是有可能产生幻读）、串行化。

Mysql有哪些类型的索引？

答案：

什么是覆盖索引和回表？

答案：

1、覆盖索引，指的是在一次查询中，一个索引包含所有需要查询的字段的值，可能是返回值或where条件

假如我们创建了一个(money，buyer_id)的联合索引，索引的叶子节点包含了 buyer_id 的信息，则不会再 回表 查询。

2、回表，指查询时一些字段值拿不到，需要到主键索引B+树再查一次。

Mysql的最左前缀原则？

答案：即最左优先，在检索数据时从联合索引的最左边开始匹配，直到遇到范围查询（如：> 、< 、between、like等）

例子：where a = 1 and b = 2 and c > 3 and d = 4 ，如果建立(a,b,c,d)组合索引，d是用不到索引的；如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

线上SQL的调优经验？

答案：

官方为什么建议采用自增id 作为主键？

答案：自增id是连续的，插入过程也是顺序的，总是插入在最后，减少了页分裂，有效减少数据的移动。所以尽量不要使用字符串（如：UUID）作为主键。

索引为什么采用B+树，而不用B-树，红黑树？

答案：提升查询速度，首先要减少磁盘IO次数，也就是要降低树的高度。

事务的特性有哪些？

答案：ACID。

如何实现分布式事务？

答案：

日常工作中，MySQL 如何做优化？

答案：

mysql 主从同步具体过程？

答案：

什么是主从延迟？

答案：指一个写入SQL *** 作在主库执行完后，将数据完整同步到从库会有一个时间差，称之为主从延迟。计算公式：

注意：不同服务器要保持时钟一致

主从延迟排查方法？

答案：通过 show slave status 命令输出的 Seconds_Behind_Master 参数的值来判断

主从延迟要怎么解决？

答案：

如果数据量太大怎么办？

答案：mysql表的数据量一般控制在千万级别，如果再大的话，就要考虑分库分表。除了分表外，列举了面对海量数据业务的一些常见优化手段

分表后ID如何保证全局唯一呢？

答案：分库分表后，多张表共用一套全局id，原来单表主键自增方式满足不了要求。我们需要重新设计一套id生成器。特点：全局唯一、高性能、高可用、方便接入。

分表后可能遇到的哪些问题？

答案：分表后，与单表的最大区别是有分表键 sharding_key ，用来路由具体的物理表，以电商为例，有买家和卖家两个维度，以 buyer_id 路由，无法满足卖家的需求，反之同样道理。如何解决？

A 、B、C都是错的。

A        read uncommitted 性能最好

B        serializable 安全性最高

C        mysql默认隔离级别：repeatable_read

附上一些概念帮助你理解：

脏读、非重复读、幻像读都是不安全的：

1、脏读（dirty read）：一个事务可以读取另一个尚未提交事务的修改数据。

2、非重复读（nonrepeatable read）：在同一个事务中，同一个查询在T1时间读取某一行，在T2时间重新读取这一行时候，这一行的数据已经发生修改，可能被更新了（update），也可能被删除了（delete）。

3、幻像读（phantom read）：在同一事务中，同一查询多次进行时候，由于其他插入 *** 作（insert）的事务提交，导致每次返回不同的结果集。

提高安全性的另一个方面是牺牲效率。所以效率的排名和安全性倒序

再补充一句查看MYsql的隔离级别：select @@tx_isolation;

先来总体说一下我对这个问题的理解，用一句话概括：

数据库是可以控制事务的传播和隔离级别的，Spring在之上又进一步进行了封装，可以在不同的项目、不同的 *** 作中再次对事务的传播行为和隔离级别进行策略控制。

注意：Spring不仅可以控制事务传播行为（PROPAGATION_REQUIRED等），还可以控制事务隔离级别（ISOLATION_READ_UNCOMMITTED等）。

（以下是个人理解，如果有瑕疵请及时指正）

下面我具体解释一下：

为了大家能够更好的理解，先来明确几个知识点：

事务的传播行为：简单来说就是事务是手动提交还是自动提交，事务什么时候开始，什么时候提交。

事务的隔离级别：简单来说，就四个，提交读，提交读，重复读，序列化读。

首先我来描述一下，数据库（mysql）层面上对于事务传播行为和隔离级别的配置和实验方法：

数据库层面（采用命令行）：其实mySql命令行很简单，希望实验 *** 作一下：

//连接数据库，我这里是本地，后面是用户名密码，不要打分号，如果指令不行，配置下环境变量，网上有很多。

1 cmd中执行：mysql -hlocalhost -uroot -pmysql

//查看本地数据库事务传播行为是手动提交（0），还是自动提交（1）。

2select @@autocommit;

//如果是0，希望设置为手动提交,这里其实是设置本对话的autocommit，因为如果你再开一个cmd，发现还是没改回来，如果想修改全局的，网上有global方法。

3set @@autocommit=0;

//然后查询本地数据库中的一条记录,我本地数据库为test1;

4use test1;

5select from task where taskid=1;

//同时新开一个窗口cmd，连接数据库，并且修改这条记录，update语句我就不写了，或者直接修改数据库本条记录。

//再次执行select from task where taskid=1;发现值没变。OK因为此时数据库隔离级别为repeatable read 重复读，因为mysql默认的隔离级别是重复读。

//修改数据库隔离级别

6set global transaction isolation level read committed;

//查看一下，可能需要重新连接一下

7select @@tx_isolation;

//这时在执行一下4,5 *** 作，发现值变了，ok。因为已经改变了数据库隔离级别，发生了重复读出不同数据的现象。

（以上 *** 作希望有不明白的上网自学一下，很有用，先把数据库隔离级别弄明白了）

然后再来讲一下，Spring对事务传播行为和隔离级别的二次封装。

因为不同项目可能在一个mysql的不同数据库上，所以可以在项目中配置数据库的传播行为和隔离级别：

关于spring的传播行为（PROPAGATION_REQUIRED、PROPAGATION_REQUIRED等），我《数据库隔离级别（mysql+Spring）与性能分析 》文章中有讲，网上也有很多相关资料，我就不说了。

关于spring的事务隔离级别与数据库的一样，也是那四个，多了一个default，我也不仔细讲了。

下面主要讲一下spring的配置方法：

<property name="transactionAttributes">

<props>

<prop key="save">PROPAGATION_REQUIRED</prop>

<prop key="update">PROPAGATION_REQUIRED</prop>

<prop key="delete">PROPAGATION_REQUIRED</prop>

<prop key="get">PROPAGATION_REQUIRED,readOnly</prop>

<prop key="find">PROPAGATION_REQUIRED,ISOLATION_READ_UNCOMMITTED</prop>

</props>

就以find为例，可以配置这么配置，前面是控制传播行为，后面是控制事务隔离级别的。那么这时哪怕数据库层面上是重复读，但是还是以这里为准，你会发现在同一个事务中两次查询的结果是不一样的。

最后扫除一个盲区，readonly这个属性，是放在传播行为中的，一般书都这么归类，我也尝试了一下，readonly并不能影响数据库隔离级别，只是配置之后，不允许在事务中对数据库进行修改 *** 作，仅此而已。

下面，将利用MySQL的客户端程序，分别测试几种隔离级别。测试数据库为test，表为tx；表结构：

id                                  int

num

int

两个命令行客户端分别为A，B；不断改变A的隔离级别，在B端修改数据。

（一）、将A的隔离级别设置为read uncommitted(未提交读)

在B未更新数据之前：

客户端A：

B更新数据：

客户端B：

客户端A：

经过上面的实验可以得出结论，事务B更新了一条记录，但是没有提交，此时事务A可以查询出未提交记录。造成脏读现象。未提交读是最低的隔离级别。

（二）、将客户端A的事务隔离级别设置为read committed(已提交读)

在B未更新数据之前：

客户端A：

B更新数据：

客户端B：

客户端A：

经过上面的实验可以得出结论，已提交读隔离级别解决了脏读的问题，但是出现了不可重复读的问题，即事务A在两次查询的数据不一致，因为在两次查询之间事务B更新了一条数据。已提交读只允许读取已提交的记录，但不要求可重复读。

为了避免上面出现的几种情况，在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同。 序列化（Serializable）：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询 *** 作的事务访问到。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

以上就是关于MYSQL 那点破事！索引、SQL调优、事务、B+树、分表 ....全部的内容，包括:MYSQL 那点破事！索引、SQL调优、事务、B+树、分表 ....、数据库隔离级别，以下叙述哪些错误的是、spring mvc 怎么样设置mysql事物隔离级别等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！