1、MyISAM存储获与InnoDB存储引擎 

MyISAM存储引擎不支持事务、表锁设计、支持全文索引。缓冲池只缓存索引文件,并不缓存数据文件。 

InnoDB存储引擎支持事务、行锁设计、支持外键,支持一致性的非锁定读,也就是默认读取的操作不会产生锁。通过多版本控制来获得高并发性,并实现了SQL标准的4种隔离级别。 

对于MyISAM来说,data存的是数据地址。索引是索引,数据是数据。索引放在XX.MYI文件中,数据放在XX.MYD文件中,所以也叫非聚集索引。

MySQL面试题-风君雪科技博客 

对于InnoDB来说,data存的是数据本身。索引也是数据。数据和索引存在一个XX.IDB文件中,所以也叫聚集索引。

MySQL面试题-风君雪科技博客 

2、MySQL中的InnoDB中有哪些锁?

如下图所示。

MySQL面试题-风君雪科技博客

上图只是针对InnoDB存储引擎来说的,如对于此引擎来说,意向锁其实是表级别的锁。

行锁在实现时有3种算法,如下:

(1)Record Lock:间个行记录上锁

(2)Gap Lock:间隙锁,锁定一个范围,但不包含记录本身 

(3)Next-Key Lock:Gap Lock + Record Lock,锁定一个范围,并且锁定记录本身,可解决幻读问题

注意,如果走唯一索引,那么Next-Key Lock会降级为Record Lock,即仅锁住索引本身,而不是范围。也就是说Next-Key Lock前置条件为事务隔离级别为RR且查询的索引走的非唯一索引、主键索引。 

下面我们通过具体的例子来模拟上面出现的幻读问题: 

CREATE TABLE T (id int ,name varchar(50),f_id int,PRIMARY KEY (id), KEY(f_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into T SELECT 1,'张三',10;
insert into T SELECT 2,'李四',30;

InnoDB在数据库中会为索引维护一套B+树,用来快速定位行记录。B+索引树是有序的,所以会把这张表的索引分割成几个区间。

 MySQL面试题-风君雪科技博客

事务A执行如下语句,需要将张三修改成李四。  

select * from t;
update t set name = '李四' where f_id = 10;

这时SQL语句走非唯一索引,因此使用 Next-Key Lock加锁,不仅会给f_10=10的行加上行锁,而且还会给这条记录的两边添加上间隙锁,即(-∞,10]、(10,30]这2个区间都加了间隙锁。  

MySQL面试题-风君雪科技博客

此时如果B事务要执行如下语句,都会报错 [Err] 1205 - Lock wait timeout exceeded; try restarting transaction

INSERT INTO T SELECT 3,'王五',10;  -- 满足行锁,执行阻塞
INSERT INTO T SELECT 4,'赵六',8;   -- 满足间隙锁,执行阻塞
INSERT INTO T SELECT 5,'孙七',18;  -- 满足间隙锁,执行阻塞

不仅插入 f_id = 10 的记录需要等待事务A提交,f_id <1010< f_id <30 的记录也无法完成,而大于等于30的记录则不受影响,这足以解决幻读问题了。  

刚刚讲的是f_id 是索引列的情况,那么如果 f_id不是索引列会怎么样呢? 

这时候数据库会为整个表加上间隙锁。所以,如果是没有索引的话,不管 f_id 是否大于等于30,都要等待事务A提交才可以成功插入。  

3、数据库事务隔离级别

总结一下: 

√: 可能出现    ×: 不会出现

事务的隔离级别

Read 

uncommitted 

未提交读

Read committed

提交读

Repeatable read 

可重复读

Serializable

序列化

脏读  事务1更新了记录,但没有提交,事务2读取了更新后的行,然后事务T1回滚,现在T2读取无效。违反隔离性导致的问题,添加行锁实现

×

×

×

不可重复读  事务1读取记录时,事务2更新了记录并提交,事务1再次读取时可以看到事务2修改后的记录(修改批更新或者删除)需要添加行锁进行实现

×

×

幻读   事务1读取记录时事务2增加了记录并提交,事务1再次读取时可以看到事务2新增的记录。需要添加表锁进行实现。InnoDB存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)机制和间隙锁解决了该问题

×

注意点: 
(1)要分清不可重复读和幻读的区别 一个是更新记录,另外一个是读取了新增的记录,
(2)与脏读比起来,不可重复读和幻读都是读取了事务提交后的记录,脏读是读取了事务未提交的记录。 
(3)脏读和不可重复读采用行锁避免,而幻读采用Next-Key Lock锁来避免。 Innodb默认的repeatable read隔离级别下就可以做到避免幻读的问题。
 
4、事务的ACID属性

事务与锁是不同的。事务具有ACID属性:

(1)原子性(atomicity):由redo log重做日志来保证事务的原子性

(2)持久性(consistency):由redo log重做日志来保证事务的持久性

(3)一致性(isolation):undo log用来保证事务的一致性

(4)隔离性(durability):一个事务在操作过程中看到了其他事务的结果,如幻读。锁是用于解决隔离性的一种机制。事务的隔离级别通过锁的机制来实现。

5、InnoDB的索引

InnoDB的常见索引如下图所示。

MySQL面试题-风君雪科技博客

另外还有术语“联合索引”和“覆盖索引”,联合索引可能是聚集索引,也可能是非聚集索引,因为如果在建立表时,通过primary指定多个列为主键,则这多个列也算是联合索引,那么也是聚集索引。覆盖索引是非聚集索引,SQL只需要通过索引就可以返回查询所需要的数据,而不必通过二级索引查到主键之后再去查询数据。

6、索引建立原则

1、最左前缀匹配原则

非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,因为c用到了>符号。如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

2、尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录

3、索引列不能参与计算,保持列“干净”

比如from_unixtime(create_time) = 2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

7、一致性的非锁定读

MVCC(Multiversion concurrency control )是一种多版本并发控制机制。
(1)插入
同一事务中(假设事务id=1)插入两条记录,记录的数据版本号为事务id=1,删除版本号为null,如下图所示。
MySQL面试题-风君雪科技博客 

(2)查询

查询时需要同时满足以下两个条件:

1、查找数据版本号,早于(小于等于)当前事务id的数据行。 这样可以确保事务读取的数据是事务之前已经存在的。或者是当前事务插入或修改的。

2、查找删除版本号为null或者大于当前事务版本号的记录。 这样确保取出来的数据在当前事务开启之前没有被删除。

假如有一个事务中执行查询(假设事务id=2)

事务id为2

start transaction;
select * from user where sex = '男';  --(1)
select * from user where sex = '男';  --(2)
commit;

假设在执行这个事务ID为2的过程中,刚执行到(1),这时有另一个事务(假设事务id=3)往这个表里插入了一条数据;

事务id为3

start transaction;
INSERT INTO user (name,sex) VALUES ('王五','男');
commit;

此时表中的数据如下:  

MySQL面试题-风君雪科技博客 

然后接着执行事务 id=2 中的(2),由于id=3的数据的创建时间(事务ID为3),执行当前事务的ID为2,而InnoDB只会查找事务ID小于等于当前事务ID的数据行,所以 id=3 的数据行并不会在执行事务 id=2 中的 (2) 被检索出来。在事务 id=2 中的两条select 语句检索出来的数据都只会下表:

MySQL面试题-风君雪科技博客 

(3)删除

假如有一个事务中执行查询(假设事务id=4)  

事务id为4

start transaction;
select * from user where sex = '男';  --(1)
select * from user where sex = '男';  --(2)
commit;

假设事务 id=4 刚执行到(1),此时有另外一个事务 id=5 执行了删除语句,会更新数据的删除版本号为当前事务id = 5  

事务id为5

start transaction;
DELETE FROM user WHERE id = 1;
commit;

此时数据库表中数据如下:

 MySQL面试题-风君雪科技博客 

接着执行事务 id=4的事务(2),根据SELECT 检索条件可以知道,它会检索创建时间(创建事务的ID)小于当前事务ID的行和删除时间(删除事务的ID)大于当前事务的行,表中id=1的行由于删除时间(删除事务的ID)大于当前事务的ID,所以事务 id=2 的(2)在执行的时候也会把表中 id=1 的数据检索出来,所以事务4中的两条select 语句检索出来的数据都如下:

MySQL面试题-风君雪科技博客

(4)修改

可以理解为,当一个事务中 修改一条记录时, 是先复制该数据,新数据数据版本号为当前事务id,删除版本号为 null 。然后更新 原来数据的删除版本号为 当前事务id。如下:

假如一个事务 id=6 执行了一条update语句

事务id为6

start transaction;
UPDATE user SET name='李四1' WHERE id = 2
commit;

执行结果如下:  

 MySQL面试题-风君雪科技博客

可参考:https://www.cnblogs.com/ilovejaney/p/14439030.html 

8、MySQL大数据量分页优化 

首先需要新建一张辅助分页的表pagination,有id与page字段,类型都为整数。同步id值与t_report_app表中的id,如下:  

INSERT INTO pagination(id) SELECT id FROM t_report_app

然后使用如下语句插入page值。  

SET @p:= 0;  // 声明一个变量p并赋初始值为0
UPDATE pagination SET page=CEIL((@p:= @p + 1) / 10) ORDER BY id DESC;

我们按每页10条记录,id降序进行分页。如果插入或删除记录则需要同步pagination表记录。 

这样如果我们查询第10页的记录就可以直接知道t_report_app记录中10条数据的id了,如下:

MySQL面试题-风君雪科技博客

从SQL本身也可以出发进行优化。

存在SQL:SELECT * FROM ttl_product_info ORDER BY id LIMIT N,M。其中 LIMIT N,M 存在的问题最大:取出N+M行,丢弃前N行,返回 N ~ N+M 行的记录,如果N值非常大,效率极差(表记录1500w,N=10000000,M=30 需要9秒)。

解决办法:SQL:SELECT id FROM ttl_product_info WHERE id > N LIMIT M,id 列是索引列,id > N属于 range 级别,效率自然高,然后从位置开始取30条记录,效率极高(表记录1500w,N=10000000,M=30,需要0.9毫秒)。

当然想要实现上述效果的前提是:

id是唯一索引,而且单调递增。

N 的值是上一次查询的记录的最后一条id,(需要前端保存一下,不能直接用传统的方法获得)

不支持跨页查询,只能按照第1,2,3,4页这样查询逐页查询。

9、SQL性能分析 

MySQL的调优手段,主要包括慢日志查询分析与Explain查询分析SQL执行计划。

explain模拟优化器执行SQL语句,在5.6以及以后的版本中,除过select,其他比如insert,update和delete均可以使用explain查看执行计划,从而知道mysql是如何处理sql语句,分析查询语句或者表结构的性能瓶颈。执行计划包含的信息如下:

信息 描述
id

查询的序号,包含一组数字,表示查询中执行select子句或操作表的顺序
**两种情况**
id相同,执行顺序从上往下
id不同,id值越大,优先级越高,越先执行

id值如果为NULL则最后执行

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:优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作。

还可以使用慢查询, MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10S以上的语句。默认情况下,Mysql数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。

使用mysqldumpslow、pt-query-digest进行日志分析,可参考:http://www.shixinke.com/mysql/slow-query-log

10、数据库表水平拆分和垂直拆分

(1)水平拆分举例

假设QQ的用户有100亿,如果只有一张表,每个用户登录的时候数据库都要从这100亿中查找,会很慢很慢。如果将这一张表分成100份,每张表有1亿条,就小了很多,比如qq0,qq1,qq1…qq99表。

用户登录的时候,可以将用户的id%100,那么会得到0-99的数,查询表的时候,将表名qq跟取模的数连接起来,就构建了表名。比如123456789用户,取模的89,那么就到qq89表查询,查询的时间将会大大缩短。

(2)垂直拆分举例 

通常我们按以下原则进行垂直拆分: 
1,把不常用的字段单独放在一张表;, 
2,把text,blob等大字段拆分出来放在附表中; 
3,经常组合查询的列放在一张表中;

例如存储一个文件时,简要的常见信息放到一张表中,而真正存储文件内容的text放到另外一张附表中。 

11、为什么InnoDB用B+树而不用B树?为什么不用AVL树?为什么不用红黑树?

B+中只有叶子节点存储数据信息,所以非叶子节点有更多的空间存储key,可以使得树更矮(扇出大),所以IO操作次数更少。B+的非叶子节点存储数据信息。 

树的所有叶结点构成一个有序链表,可以按照关键码排序的次序遍历全部记录。由于数据顺序排列并且相连,所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻,所以缓存命中性没有B+树好。
红黑树搜索效率也高,为什么数据库使用b+树,这是因为红黑树仍然是一棵二叉树,相同数量的节点,红黑树更深,不适用与磁盘的IO。红黑树相较于AVL树来说,对平衡要求的宽松,大大减少了旋转带来的耗时问题,平衡树旋转是很耗时的,因此,在内存中使用红黑树效率高,如hashmap等。
对于AVL来说:
(1)维护平衡过程的成本代价很高,因为每次删除一个节点或者增加一个节点的话,需要一次或者多次的左旋,右旋等去维护“平衡”状态,
(2)还有就是节点存储的数据内容太少。没有很好利用操作系统和磁盘数据交换特性,也没有利用好磁盘IO的预读能力。因为操作系统和磁盘之间一次数据交换是已页为单位的,一页 = 4K,即每次IO操作系统会将4K数据加载进内存。但是,在二叉树每个节点的结构只保存一个关键字,一个数据区,两个子节点的引用,并不能够填满4K的内容。幸幸苦苦做了一次的IO操作,却只加载了一个关键字,在树的高度很高,恰好又搜索的关键字位于叶子节点或者支节点的时候,取一个关键字要做很多次的IO。因此平衡二叉树也是不太符合MySQL的查询结构的。
(3)如果节点很多的话,那么这个AVL树的高度还是会很高的,那么查询效率还是会很低

12、主键与聚集索引

由于一个表只能有一个聚集索引,并且如果该表设置了主键,默认主键就是聚集索引。但由于主键列在查询条件中并没有频繁作为查询条件使用,这时候在主键上建立聚集索引就太浪费资源了,这种情况下我们可以删除主键约束,给主键建立唯一约束,保证主键列值没有重复值,删除主键约束后就可以在其他列上建立聚集索引。

如果表中已经建立了其他列为聚集索引,再在一个列上面创建主键时,这时候主键列只能是非聚集索引。 

13、InnoDB什么时候会退化为表级锁

众多资料中都说innodb使用的是行级锁,但实际上是有限制的。只有在你增删改查时匹配的条件字段带有索引时,innodb才会使用行级锁,在你增删改查时匹配的条件字段不带有索引时,innodb使用的将是表级锁。因为当你匹配条件字段不带有所引时,数据库会全表查询,所以这需要将整张表加锁,才能保证查询匹配的正确性。在生产环境中我们往往需要满足多人同时对一张表进行增删改查,所以就需要使用行级锁,所以这个时候一定要记住为匹配条件字段加索引。 

14、MySQL 如何保证持久性?MyISAM 能保证持久性吗

MyISAM 能保证持久性吗,因为MyISAM不支持事务,所以没有ACID属性。

当做数据修改的时候,不仅在内存中操作,还会在redo log中记录这次操作。当事务提交的时候,会将redo log日志进行刷盘(redo log一部分在内存中,一部分在磁盘上)。当数据库宕机重启的时候,会将redo log中的内容恢复到数据库中,再根据undo log(回滚日志)和binlog内容决定回滚数据还是提交数据。

15、介绍一下mysql redo log和bin log的作用,MySQL binlog 里是逻辑日志还是物理日志?会记录查询SQL吗

重做日志文件为redo log,用来保存原子性和一致性,而bin log为二进制日志。

第一:redo log是在InnoDB存储引擎层产生,而binlog是MySQL数据库的上层产生的,并且二进制日志不仅仅针对INNODB存储引擎,MySQL数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。

第二:两种日志记录的内容形式不同。MySQL的binlog是逻辑日志,其记录是对应的SQL语句。而innodb存储引擎层面的重做日志是物理日志。

第三:两种日志与记录写入磁盘的时间点不同,二进制日志只在事务提交完成后进行一次写入。而innodb存储引擎的重做日志在事务进行中不断地被写入,并日志不是随事务提交的顺序进行写入的。

二进制日志仅在事务提交时记录,并且对于每一个事务,仅在事务提交时记录,并且对于每一个事务,仅包含对应事务的一个日志。而对于innodb存储引擎的重做日志,由于其记录是物理操作日志,因此每个事务对应多个日志条目,并且事务的重做日志写入是并发的,并非在事务提交时写入,其在文件中记录的顺序并非是事务开始的顺序。

第四:binlog不是循环使用,在写满或者重启之后,会生成新的binlog文件,redo log是循环使用。

第五:binlog可以作为恢复数据使用,主从复制搭建,redo log作为异常宕机或者介质故障后的数据恢复使用。

16、为什么尽量选择单调递增数值类型的主键

InnoDB中数据记录本身被存于主索引(B+树)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的结点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页。

如果使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引结点的后续位置,当一页写满,就会自动开辟一个新的页,这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。

如果使用非自增主键,由于每次插入主键的值近似于随机,因此每次新纪录都要被插入到现有索引页的中间某个位置,此时MySQL不得不为了将新记录查到合适位置而移动元素,甚至目标页可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。

简单的说:

索引树只能定位到某一页,每一页内的插入还是需要通过比较、移动插入的。所以有序主键可以提升插入效率。 

18、如何保证缓存和数据库的一致性  

延时双删除,如下:  

(1)先淘汰缓存  

(2)再写数据库(这两步和原来一样)  

(3)休眠1秒,再次淘汰缓存  

这么做,可以将1秒内所造成的缓存脏数据,再次删除。  

无法做到强一致性,只能做到最终一致性。 

db更新分为两个阶段,更新前及更新后,更新前的删除很容易理解,在db更新的过程中由于读取的操作存在并发可能,会出现缓存重新写入数据,这时就需要更新后的删除。

19、mysql 的主从延迟的原因 

MySQL主从复制延迟是怎样形成的:  

1、主库的worker线程在写binlog的时候是并发工作的(并行写入),而主库的dump线程和从库的IO线程都是单线程推拉binlog,特别是SQL线程是拿着relay log中的event逐一单线程回放的(5.6版本开启slave_parallel_workers支持特定情况下的并行复制,5.7版本之后全面支持并行复制后在复制层面已极大改善了延迟问题)。因此即使不考虑网络延迟,主流MySQL版本在高并发的情况下,消费很可能赶不上生产,采用异步复制的从库很有可能跟不上主库的进度。

2、在复制期间,无论是主库或从库负载高(特别是从库落盘压力大,关系到sync_binlog、innodb_flush_log_at_trx_commit的设置)或者是网络传输慢(特别是跨机房的同步)等情况发生时,都会产生主从延迟,并且是不可避免的。如果要实现强一致性,可采用Semi-sync,但采用该plugin也无法保证持续强一致性(rpl_semi_sync_master_timeout会引起复制模式的降级) 

20、select语句的执行流程 

MySQL面试题-风君雪科技博客  

①通过客户端/服务器通信协议与 MySQL 建立连接。 

②查询缓存,这是 MySQL 的一个可优化查询的地方,如果开启了 Query Cache 且在查询缓存过程中查询到完全相同的 SQL 语句,则将查询结果直接返回给客户端;如果没有开启Query Cache 或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析,并生成解析树。 

③预处理器生成新的解析树。 

④查询优化器生成执行计划。 

⑤查询执行引擎执行 SQL 语句,此时查询执行引擎会根据 SQL 语句中表的存储引擎类型,以及对应的 API 接口与底层存储引擎缓存或者物理文件的交互情况,得到查询结果,由MySQL Server 过滤后将查询结果缓存并返回给客户端。若开启了 Query Cache,这时也会将SQL 语句和结果完整地保存到 Query Cache 中,以后若有相同的 SQL 语句执行则直接返回结果。 

21、组合索引(Composite Index)中多个字段的顺序 

联合索引时会遵循最左前缀匹配的原则,即最左优先。

CREATE INDEX t_idx ON t (column_1,column_2,column_3);

当查询(column_1)、(column_1,column_2)(column_1,column_2,column_3)这三种组合是可以用到我们定义的联合索引的。如果我们查询(column_1,column_3)就只能用到column_1的索引了。我们不用太关心索引的先后顺序,什么意思呢?比如使用(column_1,column_2)和(column_2,column_1)的效果是一样的,数据库的查询优化器会自动帮助我们优化我们的sql,看哪个执行的效率最高

可参考:https://www.cnblogs.com/ricklz/p/12508704.html 

22、那什么情况下会发生明明创建了索引,但是执行的时候并没有通过索引呢? 

1、like 以%开头,索引无效;当like前缀没有%,后缀有%时,索引有效。

2、or语句前后没有同时使用索引。当or左右查询字段只有一个是索引,该索引失效,只有当or左右查询字段均为索引时,才会生效 

3、组合索引,不是使用第一列索引,索引失效。 

4、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型,使索引无效,产生全表扫描。 

5、对索引字段进行计算操作、字段上使用函数。(索引为 emp(ename,empno,sal))。索引列不能参与计算,保持列“干净” 

比如from_unixtime(create_time) = 2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

 MySQL面试题-风君雪科技博客 

23、b+树可以存储的数据条数

以InnoDB引擎为例,简单计算一下一颗B+树可以存放多少行数据。

B+树特点:只有叶子节点存储数据,而非叶子节点存放的是用来找到叶子节点数据的索引(如上图:key和指针)

InnoDB存储引擎的最小存储单元为16k(就像操作系统的最小单元为4k 即1页),在这即B+树的一个节点的大小为16k 

假设数据库一条数据的大小为1k,则一个节点可以存储16条数据

而非叶子节点,key一般为主键假设8字节,指针在InnoDB中是6字节,一共为14字节,一个节点可以存储 16384/14 = 1170个索引指针。 

可以算出一颗高度为2的树(即根节点为存储索引指针节点,还有1170个叶子节点存储数据),每个节点可以存储16条数据,一共1170*16条数据  = 18720条

高度为3的树,可以存放 1170 * 1170 * 16 = 21902400条记录

两千多万条数据,我们只需要B+树为3层的数据结构就可以完成,通过主键查询只需要3次IO操作就能查到对应记录。

24、主从未完全同步,master挂了,未同步的内容会造成什么影响,怎么恢复 

参考:线上MySQL读写分离,出现写完读不到问题如何解决  

25、数据库死锁

避免死锁最直观的方法就是在两个事务相互等待时,当一个事务的等待时间超过设置的某一阈值,就对这个事务进行回滚,另一个事务就可以继续执行了。这种方法简单有效,在 InnoDB 中,参数 innodb_lock_wait_timeout 是用来设置超时时间的。 

26、mysql的Join原理以及索引数据结构和实现原理

只知道mysql innoDB join只支持Nested Loop,不支持Hash Join,就是确定一个驱动表后不断Join得到结果集,再继续往下Join。所以Join的顺序很重要。

  

7、数据库范式与反范式

1、范式

数据库逻辑设计的规范化就是我们一般所说的范式,我们可以这样来简单理解范式: 

(1)第一范式(确保每列保持原子性) 

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值,就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。

比如某些数据库系统中需要用到“地址”这个属性,本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分,那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储,这样在对地址中某一部分操作的时候将非常方便。这样设计才满足数据库的第一范式。 

(2)第二范式(确保表中的每列都和主键相关) 

第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表,因为订单中可能会有多种商品,所以要将订单编号和商品编号作为数据库表的联合主键,如下表所示。

 订单信息表

 MySQL面试题-风君雪科技博客

这样就产生一个问题:这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关,而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。

而如果把这个订单信息表进行拆分,把商品信息分离到另一个表中,把订单项目表也分离到另一个表中,就非常完美了。如下所示。

MySQL面试题-风君雪科技博客 

(3)第三范式(确保每列都和主键列直接相关,而不是间接相关) 

第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。

比如在设计一个订单数据表的时候,可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息(比如姓名、所属公司等)的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

MySQL面试题-风君雪科技博客

这样在查询订单信息的时候,就可以使用客户编号来引用客户信息表中的记录,也不必在订单信息表中多次输入客户信息的内容,减小了数据冗余。

更高的范式要求这里就不再作介绍了,个人认为,如果全部达到第二范式,大部分达到第三范式,系统会产生较少的列和较多的表,因而减少了数据冗余,也利于性能的提高。 

完全按照规范化设计的系统几乎是不可能的,除非系统特别的小,在规范化设计后,有计划地加入冗余是必要的。

从性能角度来说,冗余数据库可以分散数据库压力,冗余表可以分散数据量大的表的并发压力,也可以加快特殊查询的速度,冗余字段可以有效减少数据库表的连接,提高效率。 

(4)反范式  

通过适当的数据冗余,来提高读的效率

MySQL面试题-风君雪科技博客

如何查询订单详情信息?

SELECT
b.用户名,
b.电话,
b.地址,
a.订单ID,
sum(c.商品价格* c.商品数量)AS 订单价格,
c.商品价格,
d.商品名称
FROM '订单表' a
JOIN '用户表' b ON a.用户ID = b.用户ID
JOIN '订单商品表' c ON c.订单ID = b.订单ID
JOIN '商品表' d ON d.商品ID = c.商品ID
GROUP BY b.用户名,b.电话,b.地址,a.订单ID,c.商品价格,d.商品名称

该查询需要关联多张表,然后再通过sum汇总出价格,查询效率不太高。 如果通过表中部分数据的冗余,进行反范式化设计,如下图:

MySQL面试题-风君雪科技博客

简化sql的查询 

SELECT
b.用户名,
b.电话,
b.地址,
a.订单ID,
a.订单价格,
c.商品价格,
c.商品名称
FROM '订单表' a
JOIN '用户表' b ON a.用户ID = b.用户ID
JOIN '订单商品表' c ON c.订单ID = b.订单ID

互联网项目中,读写比率大概是3:1或是4:1的关系,读远远高于写,写的时候增加数据冗余,增加了读的效率,这样还是很值得的。

反范式的目的是减少读取数据的开销,那么随之带来的就是更多写数据的开销。因为我们需要预先定稿大量的数据副本。

反范式还会带来数据的不一致,可以通过异步的写来进行定期数据整理,修复不一致的数据。