（2）MySQL基础

1 说说MySQL存储引擎InnoDB、Mysaim的特点？

• InnoDB

（1）具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
（2）支持外键。
（3）InnoDB 中不保存表的具体行数，也就是说，执行select count() from table时，InnoDB要扫描一遍整个表来计算有多少行。注意的是，当count()语句包含 where条件时，两种表的操作是一样的。
（4）对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引。
（5）DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。

• MyISAM

（1）不支持事务操作。
（2）不支持外键。
（3）MyISAM保存表的具体行数，执行 select count(*) from table 时只要简单的读出保存好的行数即可。
（4）对于AUTO_INCREMENT类型的字段，在MyISAM表中，可以和其他字段一起建立联合索引。

2 MySQL存储引擎中索引的实现机制？

数据库索引类似于读一本书时查找特定的内容，先看书的目录，查找对应的页码，翻到指定页码查看内容。首先在索引中查找对应索引值，然后根据索引记录查找对应的数据行。MySQL一般以B+树（B-Tree的变种）作为其索引结构，我们先了解一下基本的数据结构。

B-Tree索引数据结构

B-Tree无论是叶子结点还是非叶子结点，都含有key和一个指向数据的指针，只要找到某个节点后，就可以根据指针找到磁盘地址从而找到数据。

B+Tree索引数据结构

B+Tree所有叶子结点才有指向数据的指针。非叶子结点就是纯索引数据和主键。每个叶子结点都有指向下一个叶子结点的链接。所有值按顺序存储，每个叶子到根的距离相同，非叶子节点不存储数据，只存储指针索引；叶子节点存储所有数据，不存储指针，每个叶子节点都有指向相邻下一个叶子节点的指针，即顺序访问指针，

非叶子结点存放在内存中，也叫内结点，因此，在有限的内存中， B-Tree中每个数据的指针会带来额外的内存占用，减少了放入内存的非叶子结点数；B+Tree则尽可能多地将非叶子结点放入内存中。

数据库系统普遍使用B+树作为索引结构，而不选红黑树等其他结构。一般来说，索引本身较大，不会全部存储在内存中，会以索引文件的形式存储在磁盘上。所以索引查找数据过程中就会产生磁盘IO操作，而磁盘IO相对于内存存取非常缓慢，因此索引结构要尽量减少磁盘IO的存取次数。为了减少磁盘IO，磁盘往往会进行数据预读，会从某位置开始，预先向后读取一定长度的数据放入内存，即局部性原理。因为磁盘顺序读取的效率较高，不需要寻道时间，因此可以提高IO效率。预读长度一般为页的整数倍，主存和磁盘以页作为单位交换数据。当需要读取的数据不在内存时，触发缺页中断，系统会向磁盘发出读取磁盘数据的请求，磁盘找到数据的起始位置并向后连续读取一页或几页数据载入内存，然后中断返回，系统继续运行。而一般数据库系统设计时会将B+树节点的大小设置为一页，这样每个节点的载入只需要一次IO。

InnoDB 引擎使用B+树作为索引结构，数据文件本身就是索引文件。数据文件按照B+树的结构进行组织，叶节点的data域存储完整的数据记录，索引的key即为表的主键。主键索引的叶子结点存放的是key值和数据，叶子结点载入内存时，数据一起载入，找到叶子结点的key，就找到了数据。

InnoDB主索引

辅助索引的叶子结点存放的是key值和对应的记录的主键值，使用辅助索引查询，首先检索辅助索引获取主键，然后用主键在主索引中检索获取记录。

InnoDB辅助索引

3 谈谈MySQL支持的事务隔离级别？

Read Uncommitted（读取未提交内容）：在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

Read Committed（读取提交内容）：这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

Repeatable Read（可重读）：这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

Serializable（可串行化）：这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争

4 谈谈MySQL主备同步的基本原理？

通过binlog完成主备同步，实现最终一致性，binlog有三种格式：statement、row、mixed。 涉及到的线程有主库上的dump_thread、备库上的io_thread、sql_thread，涉及到的日志有binlog、relaylog。

MySQL主备同步流程

上图一个update语句在节点A执行，然后同步到节点B的完整流程图。主备同步的工作原理是一个完全备份加上二进制日志备份的还原。不同的是这个二进制日志的还原操作基本上是实时的，具体步骤：
（1）主库接收到客户端发送的一条update语句，执行内部事务逻辑，同时写binlog。
（2）备库通过 change master 命令，设置主库的IP、端口、用户名和密码，以及要从哪个位置开始请求 binlog。这个位置包含文件名和偏移量。
（3）在备库上执行start slave命令，启动两个线程 io_thread 和 sql_thread，其中 io_thread 负责与主机进行连接。
（4）主库校验完用户名和密码，按照接收到的位置去读取binlog，发给备库。
（5）备库接收到binlog后，写到本地文件（relay log，中转文件）。
（6）备库读取中转文件，解析出命令，然后执行。

备库通过两个线程来实现同步：I/O 线程，负责读取主库的二进制日志，并将其保存为中继日志； SQL 线程，负责执行中继日志。

5 数据库锁有哪几种粒度？

按照对数据操作的锁粒度来分：行级锁、表级锁、页级锁、间隙锁。
（1）行级锁：行级锁是MySQL中锁定粒度最细的一种锁。表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。
（2）表级锁：表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
（3）页级锁：页级锁是 MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折中的页级锁，一次锁定相邻的一组记录。
（4）间隙锁：锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。该锁只会在隔离级别是RR或者以上的级别内存在。间隙锁的目的是为了让其他事务无法在间隙中新增数据。

6 如何优化数据库性能？

常用的数据库性能优化措施有：索引优化、分库分表、批量操作、更换SSD硬盘、业务优化、读写分离。

• 索引优化：MySQL索引的建立对于高效运行是很重要的。少量的数据，没有合适的索引影响不是很大，但是当随着数据量的增加，性能会急剧下降。如果对多列进行索引，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。更多的优化案例参考：https://www.jianshu.com/p/076b9035123c
• 批量操作：在业务允许的前提下，将多次操作合并为一个操作。
• 读写分离：数据库写入效率要低于读取效率，一般系统中数据读取频率高于写入频率，单个数据库实例在写入的时候会影响读取性能，采用读写分离可以显著提高效率。实现方式主要基于mysql的主从复制，通过路由的方式使应用对数据库的写请求只在master上进行，读请求在slave上进行。
• 更换SSD硬盘：SSD硬盘的读写速度比传统机械硬盘快很多，建议将硬盘更换为SSD硬盘。
• 业务优化：通用的技术手段无法提高数据库性能的话，要考虑从业务角度优化，比如一次查询效率很低，尝试分多次查询。

7 如何进行SQL优化？

首先开启慢查询日志定位执行效率低的SQL语句，如下所示：

查询慢查询日志是否开启：
show variables like 'slow_query_log';
开启慢查询日志：
set global slow_query_log = 1;
设置慢查询日志的存放地址：
set global slow_query_log_file='/home/mysql/data/slowlog';
设置慢查询超时阈值：
set global long_query_time=3;（默认为10S）

慢查询是在SQL执行结束之后才记录，不能在应用反映执行效率出现问题的时候定位问题，通过show processlist可以查看当前MySQL在进行的线程，线程的状态、耗时等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。

explain命令是查看MySQL查询优化器如何执行查询的主要方法，可以很好的分析SQL语句的执行情况。

explain分析

• select_type列

显示了对应行是简单还是复杂select：
SIMPLE表示查询不包含子查询和union，如果查询中有任何复杂的子部分，则最外层部分标记为PRIMARY；
SUBQUERY表示包含在select列表中的子查询中的select（换句话说不在from子句中）；
DERIVED表示包含在from子句的子查询中的from，MySQL会递归执行并将结果放到一个临时表中。服务器内部称其为派生表，因为该临时表是从子查询中派生来的；
UNION表示在union中的第二个和随后的select被标记为union；
UNION RESULT表示用来从UNION的匿名临时表检索结果的select。
除了这些值，SUBQUERY和UNION还可以被标记为DEPENDENT和UNCACHEABLE。DEPENDENT意味着select依赖于外层查询中发现的数据。UNCACHEABLE意味只select中的某些特性阻止结果被缓存于Item_cache中。

• table列
  显示对应行正在访问哪个表，通常是表名或者该表的别名（如果SQL定义了别名）

• type列

访问类型，下面依次从最差到最优：
ALL表示全表扫描，MySQL必须扫描整个表，从头到尾，来找到所需要的行（有个例外如使用了limit或者extra列中显示了Using distinct/not exists）；
index跟全表扫描一样，只是扫描时按索引次序进行而不是行。主要优点是避免了排序，最大缺点是要承担按索引次序读取整个表的开销。如果在extra列中看到Using index，说明MySQL正在使用覆盖索引，只扫描索引的数据，而不是按索引次序的每一行，它比按索引次序全表扫描的开销要少很多；
range范围扫描，是一个有限制的索引扫描，开始于索引的某一点，返回匹配这个值域的行。比全索引扫描好一些，因为用不着遍历全部索引。显而易见的范围扫描是带有between或在where子句里带有>的查询；
ref是一种索引访问，返回所有匹配某个单个值的行。然而，它可能会找到多个符合条件的行，此类索引访问只有当使用非唯一性索引或者唯一性索引的非唯一性前缀时才会发生。ref_or_null是ref的一个变体，它意味着MySQL必须在初次查找的结果里进行第二次查找以找出NULL条目；
eq_ref这种索引查找，MySQL知道最多只返回一条符合条件的记录。这种访问方法可以在MySQL使用主键或者唯一性索引查找时看到；
const，system当MySQL能对查询的某部分进行优化并将其转换成一个常量时，它就会使用这些访问类型；
NULL这种访问方式意味着MySQL能在优化阶段分解查询语句，在执行阶段甚至不用在访问表或者索引。

• possible_keys列

这一列显示了查询可以使用哪些索引，这是基于查询访问的列和使用的比较操作符来判断的。这个列是在优化过程的早期创建的，因此有些罗列出来的的索引可能对于后续优化过程是没用的。

• key列

显示MySQL决定采用哪个索引来优化对该表的访问。如果该索引没有出现在possible_keys列中，那么MySQL选用它是出于另外的原因如可能选择了一个覆盖索引，哪怕没有where子句。换言之，possible_keys揭示了哪一个索引有助于高效行查找，而key显示的是优化采用的哪一个索引可以最小化查询成本。

• key_len列

显示了MySQL在索引里使用的字节数，可通过该列计算查询中使用的索引的长度。

• ref列

显示了之前的表在key列记录的索引中查找值所用的列或常量

• rows列

表示MySQL预估的为了找到所需的行而要读取的行数。根据表的统计信息和索引的选用情况，这个估算可能很不精确。通过把所有rows列值相乘，可以粗略的估算出整个查询会检查的行数。如下面这个查询大约会检查4行：

explain select f.film_id from film f
  inner join film_actor fa using(film_id)
  inner join actor a using(actor_id);

**8 select * from table t where size > 10 group by size order by size语句执行顺序？

MySQL的执行顺序如下：
FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
ON: 对虚表VT1进行ON筛选，只有那些符合<join-condition>的行才会被记录在虚表VT2中。
JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合<where-condition>的记录才会被插入到虚拟表VT4中。
GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5
CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6
HAVING： 对虚拟表VT6应用having过滤，只有符合<having-condition>的记录才会被 插入到虚拟表VT7中。
SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。
DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9.
ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10
LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回

因此执行顺序将是 from table t > where size > 10 > group by size > order by size