MySQL经典题

1、请你谈谈Mysql事务隔离级别，Mysql的默认隔离级别是什么？

为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别：

• READ-UNCOMMITTED(未提交读):最低的隔离级别，允许脏读，也就是可能读取到其他会话中未提交事务修改的数据，可能会导致脏读、幻读、不可重复读。
• READ-COMMITTED(提交读)：只能读取到已经提交的数据。Oracle等多数数据库默认都是改级别，可以阻止脏读，但是还是有可能发生幻读，不可重复读。
• REPEATABLE-READ（可重复读）:对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。Mysql默认的隔离级别
• SERIALIZABLE(序列化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之前就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读、幻读。

2、可重复读解决了哪些问题？

1、可重复读的核心就是一致性读。保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据，会造成幻读。

2、而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

3、查询只承认在事务启动前就已经提交完成的数据。

4、可重复读解决的是重复读的问题，可重复读在快照读的情况下是不会有幻读，但当前读的时候会有幻读。

3、对慢SQL会考虑到哪些优化？

1、分析语句，是否加载了不必要的字段、数据。

2、分析SQL执行计划（expl索引信息）,如果SQL很复杂，优化SQL结构,尽可能避免全表扫描，首先考虑Where及Order by涉及列上建立索引。

3、按照可能优化点执行表结构变更，增加索引、SQL写等操作。

4、如果表数据量太大，考虑分表

5、利用缓存，减少查询次数

6、减少SQL中函数运算与其它计算

4、Mysql的默认引擎为什么是InnoDB？

Mysql5.5之前引擎默认是Mylsam，5.5之后就是innoDB，来看看两者的对比吧

1、是否支持行级锁

Myisam只有表级锁，而InnoDB支持行级锁和表级锁，默认我行级锁。

也就说，Myisam一锁就是锁住了整张表，这在并发写的情况下多慢，而InnoDB支持高并发写入。

2、是否支持事务

Myisam不支持事务

InnoDB提供事务支持，具有提交commit和回滚rollback事务的能力

3、是否支持外键

Myisam不支持，而InnoDB支持。

4、是否支持数据库异常崩溃后的安全恢复

Myisam不支持，InnoDB支持

使用InnoDB的数据库在异常崩溃后，数据可重新启动的时候会保证数据库恢复到奔溃前的状态。这个恢复的过程依赖于redo log。

扩展一下：

• MySQL InnoDB引擎使用redo log（重做日志）保证事务的持久性，使用undo log(回滚日志)来保证事务的原子性。
• MySQL InnoDB引擎通过锁机制、MVCC等手段来保证事务的隔离性
• 保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

5、是否为聚集索引

InnoDB是聚集索引，MyIsam是非聚集索引

聚集索引：索引的叶子节点包含了完整的表数据,那么这种索引就称为聚集索引，聚集索引是将索引列字段和行记录数据维护在了一起,它的叶子节点存储的是 索引列字段 + 完整的行记录数据,通过聚集索引能直接获取到整行数据。innoDB的主键索引就是聚集索引。
非聚集索引：非聚集索引是相比较于聚集索引来说,它是把索引和行数据分开维护,叶子节点并没有包含完整的数据记录。

5、MySQL索引底层结构为什么使用B+树？

一般的数据库都是采用b树或者b+树来实现存储结构的，b树是一种多路平衡树，他的整体高度相比二叉树会矮很多，对于数据库来说，所有的数据都是存储在磁盘上的，而磁盘io效率是很低的，特别是在随机磁盘io的一个情况下效率更低，那么树的高度就能够决定磁盘io的一个次数，磁盘io次数越少对性能的提升就越大。

B+树的存储结构：

• b+树的所有数据都存储子啊叶子节点上，非叶子节点上存储他的key值信息。
• 叶子节点的数据使用双向链表的方式进行关联。

为什么采用B+树：

1、b+树非叶子节点不存储数据，所以每一层都能存储的索引数量会增加，这就意味着在层高相同的情况下存储的数据量比B树更多，使得磁盘的io次数更少。

2、相对于mysql中范围查询而言，而b+树的所有数据都能存储在叶子节点并使用双向链表来关联，所以在查询的时候只需要查询两个节点进行遍历即可，而B树需要扫描所有节点。

3、在数据检索方面，由于所有数据都存储在叶子节点上，所有b+树的磁盘io速度会更加稳定一些。

4、因为叶子节点存储数据，所以b+树的全局扫描能力更强，因为它只需要扫描叶子节点，而B树却需要遍历整个树。

6、SQL性能分析

explain可以分析一条sql的优劣，通过mysql反馈我们，需要我们自己去读懂explain的sql执行结果来判断是否要进行优化，根据不同的业务和操作的数据量大小优化。

使用 explain 查询的结果包含的字段及其解释如下:

字段	描述
id	查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序 两种情况 id相同，执行顺序从上往下 id不同，id值越大，优先级越高，越先执行
select_type	查询类型，主要用于区别普通查询，联合查询，子查询等复杂查询 1、simple ——简单的select查询，查询中不包含子查询或者UNION 2、primary ——查询中若包含任何复杂的子部分，最外层查询被标记 3、subquery——在select或where列表中包含了子查询 4、derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中 5、union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived 6、union result:UNION 的结果
table	输出的行所引用的表
type	显示联结类型，显示查询使用了何种类型，按照从最佳到最坏类型排序 1、system：表中仅有一行（=系统表）这是const联结类型的一个特例。 2、const：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量。 3、eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描。 4、ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体。 5、range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描。 6、index:index 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。 7、all：遍历全表以找到匹配的行。 注意:一般保证查询至少达到range级别，最好能达到ref。
possible_keys	指出MySQL能使用哪个索引在该表中找到行
key	显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。查询中如果使用覆盖索引，则该索引和查询的select字段重叠
key_len	表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度。
ref	显示索引的哪一列被使用了，如果有可能是一个常数，哪些列或常量被用于查询索引列上的值
rows	根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数
Extra	包含不适合在其他列中显示，但是十分重要的额外信息 1、Using filesort：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序” 2、Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。 3、Using index:表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。 4、Using where :表明使用where过滤 5、using join buffer:使用了连接缓存 6、impossible where:where子句的值总是false，不能用来获取任何元组 7、select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count(*),不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。 8、distinct:优化distint操作，在找到第一匹配的元组后即停止找同样值的动作。

7、B+树的叶子节点的是单向链表还是双向链表？

Mysql的innodb是以页为存储单位的，每个B+Tree的叶子节点都是一个页的大小的倍数，默认一页的大小是16K。

页结构如下图所示：

也就是每一个页都包含两个页指针，一个是previous page指针，指向上一个页，一个是next page指针，指向下一个页。
头部还有Page的类型信息和用来唯一标识Page的编号。根据这个指针分布可以想象到Page链接起来就是一个双向链表。

8、如何查询慢sql产生的原因

1、分析sql执行计划（explain extended）,思考可能的优化点，是否命中索引。

2、是否内存不足。

3、是否网络速度慢

4、是否查询出的数据量过大

5、是否返回了不必要的行和列

6、是否锁或者死锁

7、是否I/O吞吐量小，形成了瓶颈效应

8、sp_lock,sp_who,活动的用户查看，原因是读写竞争资源

9、索引是什么？

索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据。

优点：

1、提高数据检索效率，降低数据库IO成本

2、通过索引对数据进行排序，降低数据的排序成本，降低cpu的消耗

缺点：

1、建立索引需要占用物理空间

2、降低表的增删改效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长。

10、索引失效的情况有哪些？

1、like以%开头索引无效，当like以%结尾，索引有效。

2、or会使索引失效。如果查询字段相同，也可以使用索引。例如where age=20 or age=30（索引生效），where age=20 or name=‘张三’（这里就算你age和name都单独建索引，还是一样失效）。

3、组合索引，使用的不是第一列索引的时候，索引失效，即没有遵循最左匹配原则。

4、数据类型出现隐式转换，如varchar不加单引号的时候可能会自动转为int类型，这个时候索引失效

5、在索引字段上使用is null 或者is not null的时候，索引失效

6、在索引字段上使用not、<>、!=的时候是不会使用索引的，走的是全表查询

7、对索引字段进行计算操作，函数操作是不会使用索引

8、当全表扫描速度比索引速度快的时候不会使用索引。

11、Mysql索引分类

单列索引

• 普通索引：MySQL 中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值 和空值，纯粹为了查询数据更快一点。
• 唯一索引：索引列中的值必须是唯一的，但是允许为空值，
• 主键索引：是一种特殊的唯一索引，不允许有空值。

组合索引： 多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使 用，使用组合索引时遵循最左前缀集合。
全文索引： 只有在 MyISAM 引擎上才能使用，只能CHAR,VARCHAR,TEXT 类型字段上使用全文 索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就 能找到该字段所属的记录行，比有"你是个靓仔，靓女 …" 通过靓仔，可能就可以找到该条记录
空间索引： 空间索引是对空间数据类型的字段建立的索引，MySQL 中的空间数据类型有四种， GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用 SPATIAL 关 键字。要求，引擎为 MyISAM，创建空间索引的列，必须将其声明为 NOT NULL。

12、索引设计的原则？

查询更快，占用空间更小

1、适合索引的列是频繁出现在where子句及order by,group by中的列

2、定义有外键的数据列一定要建立索引

3、更新频繁的字段不适合创建索引

4、对于定义为text、image和bit数据类型的列不要建立索引

5、尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6、不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可

7.若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

13、char 和varchar类型的选择

char长度固定，处理速度要比varchar快很多，但是相对较费存储空间;所以对存储空间要求不大，但在速度上有要求的可以使用char类型，反之可以用varchar类型。

14、Mysql都有哪些锁？

按锁颗粒度分：

• 行锁：锁某行数据，锁粒度小，并发度高

• 表锁：锁整张表。锁粒度最大，并发度低

还可以分为：

• 共享锁：也就是读锁，一个事务给某行数据加了读锁，其他事务可以读，但不能写。
• 排他锁：也就是写锁，一个事务给某行数据加了写锁，其他事务不能读，也不能写。

还可以分为：

• 乐观锁：并不会真正的去锁某行记录，而是通过一个版本号来实现。乐观锁每次在执行数据的修改操作时，都会带上一个版本号，一旦版本号和数据的版本号一致就可以执行修改操作并对版本号执行 +1 操作，否则就执行失败。
• 悲观锁：行锁，表锁、读锁，写锁都是悲观锁

15、Mysql主从复制

为什么需要主从复制？

1、在业务复杂的系统中，有可能存在锁表的场景，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样即使出现了锁表的情景，通过读从都也可以保证其他 业务的正常运作。

2、做数据的热备

3、构架的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

什么是主从复制？

Mysql主从复制是指数据可以从一个Mysql数据库服务器主节点复制到一个或多个从节点。Mysql默认采用异步复制方式，这样从节点不用一直访问住服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

主从复制的主要用途：

1、读写分离：主库出现了锁表的情景，也通过读从库也可以保证业务的正常运作。

2、数据实时备份，当系统中某个节点出现故障的时候，方便切换。

3、高可用HA

4、架构扩展，随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。

16、数据库三大范式

第一范式(1NF):要求设计数据库时字段具有原子性,(字段不能是对象或者集合或者数组 这类非原子性数据)

第二范式(2NF):在满足第一范式的基础上,还要求每张表的数据唯一(强制要求你加主键)

第三范式(3NF):在满足第二范式基础上,还要求不能有其他表非主键列(不能有重复的列 否则数据冗余 需要添加主外键关联)

17、什么是Buffer pool,什么是LRU算法？mysql有什么改进？

buffer pool是缓存池，用来储存查询数据库的数据，减少访问磁盘io的次数。具体看这个buffer pool
LRU算法是选择最近最久未使用的页面予以淘汰。在InnoDB 管理 Buffer Pool 的 LRU 算法，它巧妙的使用了链表，把刚访问的数据放在链表头部，链表尾部的就是最近未被访问过的数据，予以淘汰。
但有个问题就是如果遇到全文查询，或者select *查询的话，就会导致热点数据排到后面去，但其实这些数据只查到过一次。
改进的LRU算法，就是将缓存池分为了new、old两个区域。查询到的数据不会像之前一样直接插入到头部，而是插入到new的尾部，old的头部，也就是它们两个中间位置，再看它的使用情况，如果再次使用就往前移，如果没有使用就往后移。

18、什么是索引下推？

索引下推是从 MySQL5.6 开始引入一个特性，英文是 index condition pushdown，一般简称为 ICP，索引下推通过减少回表的次数，来提高数据库的查询效率。
索引下推详细

19、什么是自适应哈希索引？

1、发现某一查询满特点就会自己创建hash索引
2、存在与InnoDB中的缓存中
3、只适合等值查询，不适合范围查询。
具体看这自适应哈希索引

20、mysql的binlog有几种日志格式？有什么区别？

1、三种格式：row、statement、mixed

2、区别：row格式文件比较大，statement比较小，row格式保存的是一行一行的数据，statement保存的是sql语句，mixed格式介于二者之间，statement容易丢数据，row格式则不会。
3、statement容易丢数据原因是，有时候，SQL语句里面会用到一些函数，比如说取当前日期的函数sysdate，你要是用statement，binlog里同步过去的就是这个带有函数的SQL语句，而主库的当前日期，和binlog同步到slave上的当前日期，肯定是有差异的，这样两条数据就不一致了，所以这样同步的数据，就会有问题。
4、row是直接把表插入到备份库中，statement是导出主库语句后，导入到备份库中，存在时间差。
详情

21、binlog和redolog的区别？

1、redolog是物理日志，binlog是逻辑日志

逻辑日志：可以简单理解为记录的SQL语句。
物理日志：因为mysql数据最终是保存在数据页中的，物理日志记录的就是数据页变更。
详情