资深技术面试官干货分享:MySQL 知识
一、mysql的执行引擎

2、MySQL 索引的“使用”注意事项?
– 0、 应尽量避免在 where 子句中对字段进行 null 值判断 ,否则将导致引擎放弃使用索引而进行全表扫描 。
– 1、 应尽量避免在 WHERE 子句中使用 != 或 <>操作符 , 否则引擎将放弃使用索引而进行全表扫描。 优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。 注意,column IS NULL 也是不可以使用索引的。
– 2、 应尽量避免在 where 子句中使用 or 来连接条件 ,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描。
– 3、 应尽量避免在 WHERE 子句中对字段进行表达式操作 ,这将导致引擎放弃使用索引而进行全表扫描。
– 4、 应尽量避免在 WHERE 子句中对字段进行函数操作 ,这将导致引擎放弃使用索引而进行全表扫描。
– 5、 不要在 WHERE 子句中的 = 左边进行函数、算术运算或其他表达式运算 ,否则系统将可能无法正确使用索引。
– 6、 尽量使用数字型字段 ,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。
– 7、 列类型是字符串类型,查询时一定要给值加引号 ,否则索引失效。
– 8、 LIKE 查询,% 不能在前,因为无法使用索引 。如果需要模糊匹配,可以使用全文索引。
– 9、 尽量的扩展索引,不要新建索引 。
– 10、 复合索引遵循前缀原则 。索引的最左匹配特性
3、索引结构
为了描述B-Tree,首先定义一条记录为一个二元组 [key, data] ,key 为记录的键值,对应表中的主键值,data 为一行记录中除主键外的数据。
InnoDB存储引擎就是用 B Tree 实现其索引结构。
B 树结构中有:

2). InnoDB的辅助索引
InnoDB的所有辅助索引都引用主键作为data域。例如,下图为定义在Col3上的一个辅助索引:

总结:
主键索引的叶子节点存的数据是整行数据( 即具体数据 )。在 InnoDB 里,主键索引也被称为 聚集索引 (clustered index)。
非主键索引的叶子节点存的数据是整行数据的主键,键值是索引。在 InnoDB 里,非主键索引也被称为 辅助索引 (secondary index)。
当通过辅助索引来查询数据时,需要进过两步:
首先,InnoDB 存储引擎会遍历辅助索引找到主键。
然后,再通过主键在聚集索引中找到完整的行记录数据。
面试题2、为什么主键需要是自增 ID ,又或者为什么主键需要带有时间性关联?
三、事务
(一)、事务的特性 :指的是 ACID

(二)、事务的隔离级别
1、事务定义了四种事务隔离级别,不同数据库在实现时,产生的并发问题是不同的。


4、死锁
死锁发生在当多个进程访问同一数据库时,其中每个进程拥有的锁都是其他进程所需的,由此造成每个进程都无法继续下去。简单的说,进程 A 等待进程 B 释放他的资源,B 又等待 A 释放他的资源,这样就互相等待就形成死锁。
下列方法有助于最大限度地降低死锁:
- 1、innodb: 默认的存储引擎是 InnoDB ,并且也是最主流的选择
- 2、MyISAM:在 MySQL5.1 以及之前的版本,默认的存储引擎是 MyISAM ,但是目前已经不再更新

- 非叶子节点只存储键值信息,
- 数据全部冗余到了叶子节点,
- 而且所有叶子节点之间都有一个链指针,所以支持范围查找
- 在 B Tree 中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储 key 值信息,这样可以大大加大每个节点存储的 key 值数量,降低 B Tree 的高度。
- 通常在 B Tree 上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构。 因此可以对 B Tree 进行两种查找运算:一种是对于主键的范围查找和分页查找,另一种是从根节点开始,进行随机查找。
- 在B-树基础上,为叶子结点增加链表指针,所有关键字都在叶子结点中出现,非叶子结点作为叶子结点的索引;B 树总是到叶子结点才命中;


- 原子性 Atomicity :一个事务(transaction)中的所有操作,或者全部完成,或者全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被恢复(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。即,事务不可分割、不可约简。
- 一致性 Consistency :在事务开始之前和事务结束以后,数据库的完整性没有被破坏。
- 隔离性 Isolation :数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
- 持久性 Durability :事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

- READ UNCOMMITTED(读未提交):事务中的修改,即使没有提交,对其他事务也都是可见的。
- 会导致脏读。
- READ COMMITTED(读已提交):事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。
- 会导致不可重复读。
- 这个隔离级别,也可以叫做“不可重复读”。
- REPEATABLE READ(可重复读):一个事务按相同的查询条件读取以前检索过的数据,其他事务插入了满足其查询条件的新数据。产生幻行。
- 会导致幻读。
- SERIALIZABLE(可串行化):强制事务串行执行。

- MySQL 默认的事务隔离级别为可重复读(repeatable-read) 。
- 上图的
处,MySQL 因为其间隙锁的特性,导致其在可重复读的隔离级别下,不存在幻读问题。也就是说,上图 处,需要改成“否”!!!! - 有些资料说可重复读解决了幻读,实际是存在的,可以通过 SELECT xxx FROM t WHERE id = ? FOR UPDATE 的方式,获得到悲观锁,禁止其它事务操作对应的数据,从而解决幻读问题
- 表锁:系统开销最小,会锁定整张表,MyIsam 使用表锁。
- 行锁:最大程度的支持并发处理,但是也带来了最大的锁开销,InnoDB 使用行锁。
- 乐观锁:可以读,不能写
- 悲观锁:不能读写,就是上面看到的共享锁和排他锁
- 悲观锁大多数情况下依靠数据库的锁机制实现,以保证操作最大程度的独占性。如:SELECT xxx FROM t WHERE id = ? FOR UPDATE 的方式
- 乐观锁,大多是基于数据版本( Version )记录机制实现:乐观锁,实际就是通过版本号,从而实现 CAS 原子性更新。

- 设置获得锁的超时时间。 通过超时,至少保证最差情况下,可以有退出的口子。
- 按同一顺序访问对象。 这个是最重要的方式。
- 避免事务中的用户交互。
- 保持事务简短并在一个批处理中。
- 使用低隔离级别。
- 使用绑定连接。
(1) SELECT (2) DISTINCT六、什么是 MVCC 多版本并发控制(MVCC),是一种用来 解决读-写冲突 的无锁并发控制,也就是为事务分配单向增长的时间戳,为每个修改保存一个版本,版本与事务时间戳关联,读操作只读该事务开始前的数据库的快照。 这样在读操作不用阻塞写操作,写操作不用阻塞读操作的同时,避免了脏读和不可重复读。(3) FROM (4) JOIN (5) ON (6) WHERE (7) GROUP BY (8) HAVING (9) ORDER BY (10) LIMIT 复制代码