MySQL

MySql

三范式

第一范式:(确保每列保持原子性)所有字段值都是不可分解的原子值。

第二范式:(确保表中的每列都和主键相关)在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

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

一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 mysql 数据库，又插入了一条数据，此时 id 是几？

• 数据库引擎如果是 MyISAM ，那 id 就是 8。

• 数据库引擎如果是 InnoDB，那 id 就是 6。

　　InnoDB 表只会把自增主键的最大 id 记录在内存中，所以重启之后会导致最大 id 丢失。

索引

有哪些索引类型

三个角度分析：

1.数据结构角度上可以分为B+tree 索引、hash 索引、flfltext索引 （InnoDB、MyISAM都支持)。

2.存储角度上可以分为聚集索引和非聚集索引。

3.逻辑角度上可以分为 primary key、normal key（普通键）、单列、复合和覆盖索引。

适合创建索引

　　1).主键自动建立唯一索引

　　2).频繁作为查询查询条件的字段应该创建索引

3).查询中与其它表关联的字段，外键关系建立索引

　 6).单键/组合索引的选择问题(在高并发下倾向创建组合索引)

　　7).查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

8).查询中统计或者分组字段

不适合创建索引

　　1).表记录太少

　　2).经常增删改的表(因为不仅要保存数据，还要保存一下索引文件)

　　3).数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。

4).频繁更新的字段不适合创建索引

　　5).where条件里用不到的字段不创建索引

注意：如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

聚集索引和非聚集索引

聚集索引：

1.聚集索引：该索引中键值的逻辑顺序决定了表中相应行的物理顺序。

2.我们的汉语字典的正文本身就是一个聚集索引。

3.们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

4.聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行 的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此 类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。

每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。

非聚集索引：

1.非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

2.目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。

索引的实现

(1) BTree 索引是大多数 MySQL 存储引擎的默认索引类型，不是B+Tree。 (2) 哈希索引是memory引擎表的默认索引类型，memory也支持btree。 (3) 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行（即不能使用哈希索引来做覆盖索引扫描）。 (5) 空间索引不会要求where子句使用索引最左前缀可以全方位索引数据，可以高效使用任何数据组合查找 配合使用mercontains()函数使用。 (6) 索引将随机I/O变为顺序I/O

Hash索引或者B+树索引

1.MySQL中，只有HEAP/MEMORY引擎才显示支持Hash索引。

2.常用的InnoDB引擎中默认使用的是B+树索引，它会实时监控表上索引的使用情况，如果认为建立哈希索引可以提高查询效率，则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引（在InnoDB中默认开启自适应哈希索引），通过观察搜索模式，MySQL会利用index key的前缀建立哈希索引，如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询。 B+树索引和哈希索引的明显区别是：

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；

如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索； ​ 同理，哈希索引没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；

哈希索引也不支持多列联合索引的最左匹配规则；

B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

在大多数场景下，都会有范围查询、排序、分组等查询特征，用B+树索引就可以了。

为什么要为InnoDB表设置自增列做主键

解答思路:使用自增列做主键，写入顺序是自增的，和B+数叶子节点分裂顺序一致.InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致时，存取效率是最高的。

验证 mysql 的索引是否满足需求

需要根据查询需求来决定配置索引的类型，一旦确定索引类型之后，可以使用 explain 查看 SQL 执行计划，确认索引是否满足需求。

索引失效

1.最佳左前缀法则：如果索引了多列，要尊守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。　　

2.索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描。　　

3.存储引擎不能使用索引中范围条件右边的列。　　

如这样的sql: select * from user where username='123' and age>20 and phone='1390012345',其中username, age, phone都有索引，只有username和age会生效，phone的索引没有用到。　　

4.尽量使用覆盖索引(只访问索引的查询(索引列和查询列致))

如select age from user减少select *　　

5.mysql在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描。　　

6.is null, is not null 也无法使用索引。　　

7.like 以通配符开头(‘%abc..’)mysql索引失效会变成全表扫描的操作。　　

所以最好用右边like 'abc%'。如果两边都要用，可以用select age from user where username like '%abc%',其中age是索引列，假如index(a,b,c), where a=3 and b like 'abc%' and c=4，a能用，b能用，c不能用　　

8.字符串不加单引号索引失效　　

9.少用or，用它来连接时会索引失效 union all　　

10.尽量避免子查询，而用join

sql优化& 全文扫描

其思路是尽量减少全文索引的过程，即尽量避免索引失效：

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在where 及 order by 涉及的列上建立索引。

2.避免在 where 子句中对字段进行 null 值判断，导致引擎放弃使用索引而进行全表扫描，解决办法：可以在num上设置默认值0，然后这样查询=0

3.like前后‘%’，也会导致全文索引，下面的查询也将导致全表扫描：

　　select id from t where name like '%abc%'

模糊查询：使用instr函数替换like， 用instr代替like，可以提高效率。

14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

13.很多时候用 exists（） 代替 in 是一个好的选择：（EXISTS 指定一个子查询，检测行的存在。语法：EXISTS subquery。参数 subquery 是一个受限的 SELECT 语句 （不允许有 COMPUTE 子句和 INTO 关键字）。结果类型为 Boolean，如果子查询包含行，则返回 TRUE。）

　　select num from a where num in(select num from b)

　　用下面的语句替换：

　　select num from a where exists(select 1 from b where num=a.num)

18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

解决办法：在项目中，凡是设计到字符串类型的数据，一律采用varchar或者text类型

19.不使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

29.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

解决办法：查询时应该进行分页查询，用limit进行分页，如果数据很大的话，对数据库来说，避免不必要的损耗。

1、避免where 子句中使用!=或<>操作符

3、避免 where 子句null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，

4、避免 or 来连接，解决办法：union all（UNION ALL 这个指令的目的也是要将两个 SQL 语句的结果合并在一起。 UNION ALL 和 UNION 不同之处在于 UNION ALL 会将每一笔符合条件的资料都列出来，无论资料值有无重复。）

5、避免前置百分号，若要提高效率，可以考虑全文检索。

6、慎用in 和 not in，否则会导致全表扫描。解决办法：对于连续的数值，能用 between 就不要用 in

7、where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描： select id from t where num=@num 可以改为强制查询使用索引： select id from t with(index(索引名)) where num=@num

8、避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如： select id from t where num/2=100 应改为: select id from t where num=100*2

MyISAM Innodb 索引

1.MyISAM索引实现：

1.MyISAM引擎使用B+树作为作为索引结构，叶子节点的data域存的是数据记录的地址。

2.索引文件和数据文件是分离的。所以也叫做 “非聚集”索引，之所以叫做“非聚集”索引，是因为他的索引文件只保存数据记录的地址，不包含完整的数据记录。

3.MyISAM引擎中查找流程是：按照b+树的查找规则进行的，如果指定的key存在，则取出器data域，然后在以data域的值为地址，读出相应数据记录。

2.InnoDB也使用B+树作为索引，但是与MyISAM的区别是：

1.就是他的数据文件本身就是索引文件，不像MyISAM索引那样那样 索引文件也数据文件是分离的。

因为：在innnodb中，表数据文件就是按照b+树的性质组织的一个索引文件，在棵b+树的叶节点的data域保存了完成的数据记录。这个索引的key就是数据表得主键，因此innodb表数据文件就是主索引。所以innnodb的索引也叫做“聚集”索引。

2.因为innodb的数据文件本身按主键聚集，所以要求innodb必须要有主键，如果没有显示的指定，那么Mysql会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这样的列，那么mysql会自动为innodb表生成一个隐藏的字段作为主键，这个字段的时间是6个字节，类型是长整形的。

3.第二个区别就是innodb的辅助索引data域存的是相应记录的主键，而不是地址。换句话说就是 innodb的辅助索引都用主键作为data域的值。辅助索引需要检索两边索引：首先检查辅助索引获得主键，再在主索引中利用查找到的key获得记录。

mysql为什么使用B+树

这个题可以理解为B+树在同类数据结构中的优点：

红黑树，B树，B+树：

红黑树的缺点：

在大规模数据存储的时候，红黑树会出现由于树的深度过大而造成磁盘IO读写过于频繁，导致效率低下的情况。

原因：

树的深度过大会造成磁盘IO频繁读写。要求只能通过某种较好的树结构减少树的结构尽量减少树的高度，B树可以有多个子女，从几十到上千，可以降低树的高度。

B树和B+树的区别：

1.B树所有叶子结点都出现在同一层，叶子结点不包含任何关键字信息。

2.B+树所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接，所有的非叶子结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。 (而B 树的非终节点也包含需要查找的有效信息)

B+相比B树在操作系统的文件索引和数据库索引优势：

B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。数据库中基于范围的查询是非常频繁，B树不支持这样的操作（效率太低）

1) B+的磁盘读写代价更低

B+的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。举个例子，假设磁盘中的一个盘块容纳16bytes，而一个关键字2bytes，一个关键字具体信息指针2bytes。一棵9阶B-tree(一个结点最多8个关键字)的内部结点需要2个盘快。而B+ 树内部结点只需要1个盘快。当需要把内部结点读入内存中的时候，B 树就比B+ 树多一次盘块查找时间(在磁盘中就是盘片旋转的时间)。

2) B+-tree的查询效率更加稳定

由于非叶子结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

Hash/B+树索引

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

B+树索引和哈希索引的明显区别是：

1.如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；

2.如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；

3.同理，哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；

4.哈希索引也不支持多列联合索引的最左匹配规则；

B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

事物

事务

所谓事务,它是一个操作集合，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

ACID 是什么？（与事务有关）

原子性（Atomicity）: 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

一致性（Consistency）: 事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。

隔离性（Isolation）: 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

数据库实现事务隔离的方式，基本可以分为以下两种。

·一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。

·MVCC，也经常称为多版本数据库。

持久性（Durability）: 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。引入redo日志记录数据修改后的值，可以避免数据在事务提交之前必须写入到磁盘的需求，减少I/O。

原子性/持久性是如何保证的？

undo log(撤销日志）

事务中，每当执行一条SQL语句对数据产生了影响，就会记录下来与之相反的操作到undo log(撤销日志）中，例如，更新会记录之前的状态，删除会形成insert，添加会形成delete，一旦事务被回滚，则执行undo log中记录的操作，来完成恢复到之前的状态。

这里是个逻辑恢复！同时，每当执行一条事务中的SQL，会将操作记录到redo log中，此时事务一旦被提交，就将该redolog中的操作，持久化到磁盘上，数据就持久的记录下来了（ACID的D）。

PS：还有，undolog才是原子性的关键。提供redolog，应该主要目的是提升磁盘的IO开销吧，如果直接写入磁盘，IO开销，会很大。如果先将操作记录到redolog中，可以顺序的记录，批量的记录，再一起同步到磁盘上，速度会比直接写磁盘快些。 mysql在生成redolog时，会使用 innodb log buffer，先缓冲到内存中，再同步到redolog上。速度会更快

 

隔离性衍生出事物的问题：

如果不考虑事务的隔离性，会发生的几种问题：以及解决方法

1.数据丢失：数据发生数据覆盖，两个线程同时对同一个数据进行写操作，会发生数据的覆盖。 后修改的数据覆盖前一次的修改。解决方法：对同一个数据的写操作加上排它锁，要写数据了， 就把它用X锁锁住， 锁住后，除非你释放， 否则别人无法获得X锁

2.脏读：读到没提交的数据，一个线程在写操作的时候，另一个线程进来读读完立即释放。读到脏数据。解决方法：对同一个数据加共享锁，读和写、写和读、写和写不能共存，读和读可以共存。约定一下， 读一个数据之前加S锁， 读完之后立刻释放该S锁 ! ”

3.不可重复读：两次读的数据不一致，解决方法：我们之前的约定是读数据时加S锁， 读完立马释放，问题就出现在这里了，看来在读数据的时候， 也需要一直锁定了， 直到事务提交

4.幻读：两次读的数据的量不一致， 和不可重复读很类似，不过修改数据改成增加数据。 解决方法：串行化

不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

innodb MVCC主要是为Repeatable-Read事务隔离级别做的。

3.MVCC 具体实现分析

a.MVCC是通过保存数据在某个时间点的快照来实现的. InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的,这两个列，分别保存了这个行的创建时间，一个保存的是行的删除时间。

b.这里存储的并不是实际的时间值,而是系统版本号(可以理解为事务的ID)，每开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID.

4.实现mvcc的要求：如何保证隔离性性呢

InnoDB会根据以下两个条件检查每行记录:

a.InnoDB只会查找创建版本,行的系统版本号小于或等于事务的系统版本号，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的.

b.行的删除版本要么未定义,要么小于当前事务版本号,这可以确保事务读取到的行，在事务开始之前未被删除.

只有a,b同时满足的记录，才能返回作为查询结果.

解决问题的方式——隔离级别：

MySQL 的事务隔离是在 MySQL. ini 配置文件里添加的，在文件的最后添加：transaction-isolation = REPEATABLE-READ

事务隔离级别	意义	实现	脏读	不可重复读	幻读
读未提交（read-uncommitted）	一事务读到了另一事务未提交的脏数据	事务T在读数据的时候并未对数据进行加锁，事务T在修改数据的时候对数据增加行级共享锁	是	是	是
读取已提交（read-committed）	在一个事务多次读取同一数据时，另一事务对数据进行了更改，导致此事务多次读取结果不同	事务T在读取数据时增加行级共享锁，读取一旦结束，立即释放；事务T在修改数据时增加行级排他锁，直到事务结束才释放	否	是	是
可重复读（repeatable-read）	在一个事务（开始到结束）多次读取同一数据时，另一事务对数据进行了更改，导致此事务多次读取结果相同，但存在幻读	事务T在数据读取时，必须增加行级共享锁，直到事务结束；事务T在修改数据过程中，必须增加行级排他锁，直到数据结束。	否	否	是
串行化（serializable）	最高的隔离级别，在这个隔离级别下，不会产生任何异常。并发的事务，就像事务是在一个个按照顺序执行一样	事务T在读取数据时，必须先增加表级共享锁，直到事务结束才释放；事务T在修改数据时，必须先增加表级排他锁，直到事务结束才释放。	否	否	否

mysql 的锁机制

锁是伴随着mysql1.5采用innodb存储引擎的新特性事务而存在的。

Mysql用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。这些锁统称为悲观锁。

MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

MyISAM和MEMORY存储引擎采用的是表级锁。MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

MySQL大致可归纳为以下3种锁：

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

• 间隙锁就是：当我们用范围条件，innoDB会给符合条件的已有数据记录的 索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 （Next-Key锁）。*

innodb：

InnoDB使用间隙锁的目的是为了防止幻读，以满足相关隔离级别的要求；

1. InnoDB与MyISAM的最大不同有两点：一是支持事务；二是采用了行级锁。

2. InnoDB的锁通过锁定索引来实现，如果查询条件中有主键则锁定主键，如果有索引则先锁定对应索引然后再锁定对应的主键（可能造成死锁），如果连索引都没有则会锁定整个数据表。所以在项目中为一写必要的列就行索引的建立。

InnoDB存储引擎既支持行级锁，也支持表级锁，但默认情况下是采用行级锁。

3.InnoDB两种类型的行锁。

· 共享锁（s）：又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上读锁，则事务T可以读A但不能修改A，其他事务只能再对A加读锁，而不能加写锁，直到T释放A上的读锁。这保证了其他事务可以读A，但在T释放A上的读锁之前不能对A做任何修改。

· 排他锁（Ｘ）：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上写锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，这两种意向锁都是表锁。

· 意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

· 意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁

意向锁是InnoDB自动加的，不需用户干预。

对于InnoDB表，主要有以下几点

（１）InnoDB的行销是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。

（２）InnoDB间隙锁机制，以及InnoDB使用间隙锁的原因。

（３）在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

（４）ＭySQL的恢复和复制对InnoDB锁机制和一致性读策略也有较大影响。

（５）锁冲突甚至死锁很难完全避免。

在了解InnoDB的锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

• 尽量使用较低的隔离级别

• 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会。

• 选择合理的事务大小，小事务发生锁冲突的几率也更小。

• 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁。

• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大减少死锁的机会。

• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响。

• 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁。

• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能

myisam

4.MyISAM表锁特点：

MySQL的表级锁有两种模式：表共享读锁和表独占写锁。

读锁：阻塞其他事物的写，放行读

写锁：阻塞其他事物的读和写

concurrent_insert：

MyISAM表的读操作与写操作之间，以及写操作之间是串行的！在一定条件下，MyISAM表也支持查询和插入操作的并发进行。

MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

· 当concurrent_insert设置为0时，不允许并发插入。

· 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。

· 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

对于ＭyISAM的表锁，主要有以下几点

（１）共享读锁（S）之间是兼容的，但共享读锁（S）和排他写锁（X）之间，以及排他写锁之间（X）是互斥的，也就是说读和写是串行的。

（２）在一定条件下，ＭyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表和插入的锁争用问题。

（３）ＭyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIPORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。

（４）由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，ＭyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

如何加表锁

MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁，这个过程并不需要用户干预。

MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁的原因。

6.MyISAM的锁调度：

MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个 MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？

答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。

这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM 的调度行为。

通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。

有点像Java内存模型中存在的天然的先行发生关系：

\1. 程序次序规则：同一个线程内，按照代码出现的顺序，前面的代码先行于后面的代码，准确的说是控制流顺序，因为要考虑到分支和循环结构。

\2. 管程锁定规则：一个unlock操作先行发生于后面（时间上）对同一个锁的lock操作。

\3. volatile变量规则：对一个volatile变量的写操作先行发生于后面（时间上）对这个变量的读操作。

乐观锁与悲观锁

• 乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。

• 悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。

　　数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。

当前读和快照读

快照读

  读取的是记录数据的可见版本（可能是过期的数据），不用加锁

生成的快照时间是第一次读，并且这个快照在整个事物内有效。

当前读

  读取的是记录数据的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录   应用于update、insert、delete ，因为快照读的局限性导致这三种操作在并发下可能会出现异常，所以只能使用当读。

user表，里面有四条数据

1、select快照读（照片）

  当你执行select *之后，在A与B事务中都会返回4条一样的数据，这是不用想的，当执行select的时候，innodb默认会执行快照读，相当于就是给你目前的状态找了一张照片，以后执行select 的时候就会返回当前照片里面的数据，当其他事务提交了也对你不造成影响，和你没关系，这就实现了可重复读了，那这个照片是什么时候生成的呢？不是开启事务的时候，是当你第一次执行select的时候，也就是说，当A开启了事务，然后没有执行任何操作，这时候B insert了一条数据然后commit,这时候A执行 select，那么返回的数据中就会有B添加的那条数据......之后无论再有其他事务commit都没有关系，因为照片已经生成了，而且不会再生成了，以后都会参考这张照片。

2、update、insert、delete 当前读

  当你执行这几个操作的时候默认会执行当前读，也就是会读取最新的记录，也就是别的事务提交的数据你也可以看到，这样很好理解啊，假设你要update一个记录，另一个事务已经delete这条数据并且commit了，这样不是会产生冲突吗，所以你update的时候肯定要知道最新的信息啊。

  我在这里介绍一下update的过程吧，首先会执行当前读，然后把返回的数据加锁，之后执行update。加锁是防止别的事务在这个时候对这条记录做什么，默认加的是排他锁，也就是你读都不可以，这样就可以保证数据不会出错了。但注意一点，就算你这里加了写锁，别的事务也还是能访问的，是不是很奇怪？数据库采取了一致性非锁定读，别的事务会去读取一个快照数据。   innodb默认隔离级别是RR， 是通过MVVC来实现了，读方式有两种，执行select的时候是快照读，其余是当前读，所以，mvvc不能根本上解决幻读的情况

MVCC

数据库实现事务隔离的方式，基本可以分为以下两种。

· 一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。

· MVCC，也经常称为多版本数据库

MVCC是一种多版本并发控制机制。锁机制可以控制并发操作,但是其系统开销较大,而MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销。innodb MVCC主要是为Repeatable-Read事务隔离级别做的。

2.MVCC实现

MVCC是通过保存数据在某个时间点的快照来实现的.

3.MVCC 具体实现分析

a.InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的,这两个列，分别保存了这个行的创建时间，一个保存的是行的删除时间。

b.这里存储的并不是实际的时间值,而是系统版本号(可以理解为事务的ID)，每开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID.

4.实现mvcc的要求：

InnoDB会根据以下两个条件检查每行记录:

a.InnoDB只会查找创建版本,行的系统版本号小于或等于事务的系统版本号，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的.

b.行的删除版本要么未定义,要么大于当前事务版本号,这可以确保事务读取到的行，在事务开始之前未被删除.

只有a,b同时满足的记录，才能返回作为查询结果.

数据库事务实现方法

\1. 在MySQL.exe中手动控制事务

–mysql>begin;

–mysql>select * from ACCOUNTS;

–mysql>commit；

\2. 通过JDBC API控制事务

–setAutoCommit(boolean autoCommit)：设置是否自动提交事务

–commit()：提交事务

–rollback()：撤销事务

\3. 通过Spring框架

@Transcationl注解修饰方法

xml文件配置

存储引擎

MySQL 常用的引擎

• InnoDB 引擎：mysql 5.1 后默认的数据库引擎，提供acid 事务的支持，提供了行级锁和外键的约束，设计的目标为处理大数据容量的数据库系统。MySQL 运行的时候，InnoDB 会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎是不支持全文搜索，同时启动也比较的慢，它是不会保存表的行数的，所以当进行 select count(*) from table 指令的时候，需要进行扫描全表。由于锁的粒度小，写操作是不会锁定全表的,所以在并发度较高的场景下使用会提升效率的。

• MyIASM 引擎：不提供事务的支持，也不支持行级锁和外键。因此当执行插入和更新语句时，即执行写操作的时候需要锁定这个表，所以会导致效率会降低。不过和 InnoDB 不同的是，MyIASM 引擎是保存了表的行数，于是当进行 select count(*) from table 语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将 MyIASM 作为数据库引擎的首选。

InnoDB的体系结构。

谈及 InnoDB的体系结构，首先要考虑MySQL的体系结构，分为MySQL的 server层和存储引擎层两部分。先要跟面试官聊清楚MySQL的整体方向，然后再去涉及InnoDB体系结构。建议从三方面

介绍InnoDB体系结构:内存、线程、磁盘。

内存中包含 insert-buffffer, data -buffffer s index_-bulffffer、 redo log -buffffer double_write

内存刷新到磁盘的机制: redo log buffffer、脏页、binlog cache的刷新条件。

各种线程的作用: master_thread、 purge_thread、 redo log thread、 read thread、 write thread、

page cleancer thread.

磁盘中存放的数据文件:redo log, undo log biniog.

innodb和myisam区别 5.5之前之后

五个方向去介绍。

事务的支持不同(InnoDB 支持事务、MyISAM 不支持事务):

锁粒度 （lnnoDB行锁应用、MyISAM表锁):

存储空间(InnoDB既缓存索引文件又缓存数据文件，MyISAM只能缓存索引文件):

存储结构(MyISAM:数据文件的扩展名为.MYDmyData，索引文件的扩展名是.MYI

myIndex: InnoDB:所有的表都保存在同一个数据文件里面，即.Ibd）:统计记录行数（MyISAM:保存有

表的总行数，select count() from table会直接取出该值:InnoDB:没有保存表的总行数，select count(*)

from table 会遍历整个表，消耗相当大)。

两种存储引擎的大致区别表现在：

1）InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。

2）MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用

3）InnoDB支持外键，MyISAM不支持

4）从MySQL5.5.5以后，InnoDB是默认引擎

5）InnoDB不支持FULLTEXT类型的索引

6）InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表

7）对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引

9）InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like '%lee%'

8）清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表

\3. 应用场景

MyISAM适合：(1)做很多count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务。

InnoDB适合：(1)可靠性要求比较高，或者要求事务；(2)表更新和查询都相当的频繁，并且行锁定的机会比较大的情况。

\2. 为什么MyISAM会比Innodb 的查询速度快。

INNODB在做SELECT的时候，要维护的东西比MYISAM引擎多很多；

1）数据块，INNODB要缓存，MYISAM只缓存索引块， 这中间还有换进换出的减少；

2）innodb寻址要映射到块，再到行，MYISAM 记录的直接是文件的OFFSET，定位比INNODB要快

3）INNODB还需要维护MVCC一致；

InnoDB会根据以下两个条件检查每行记录:

a.InnoDB只会查找创建版本,行的系统版本号小于或等于事务的系统版本号，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的.

b.行的删除版本要么未定义,要么大于当前事务版本号,这可以确保事务读取到的行，在事务开始之前未被删除.

只有a,b同时满足的记录，才能返回作为查询结果.

虽然你的场景没有，但他还是需要去检查和维护

优化

问题排查手段

• 使用 show processlist 命令查看当前所有连接信息。

• 使用 explain 命令查询 SQL 语句执行计划。

• 开启慢查询日志，查看慢查询的 SQL。

优化一条慢SQL

针对 SQL语句的优化，我们不要一上来就回答添加索引，这样显得太不专业。

我们可以从如下几个角度去分析:

(1)回归到表的设计层面，数据类型选择是否合理。

(2)大表碎片的整理是否完善。

(3)表的统计信息是不是准确的。

(4)审查表的执行计划，判断字段上面有没有合适的索引。

(5)针对索引的选择性，建立合适的索引(就又涉及大表DDL的操作问题。所以说，我们要有能力把各个

知识点联系起来)。

explain

id：select查询的序列号，（若没有子查询和联合查询，id则都是1。Mysql会按照id从大到小的顺序执行query，在id相同的情况下，则从上到下执行）

select_type：select查询的类型（simple、primary、union），主要是区别普通查询和联合查询、子查询之类的复杂查询。

table：输出的行所引用的表。

type：联合查询所使用的类型，从最好到最差：

system 、 const、eq_ref 、 ref 、 fulltext、ref_or_null、 index_merge 、unique_subquery 、 index_subquery 、 range、 index 、ALL

possible_keys：在查询过程中可能用到的索引。如果为空，则没有相关的索引。

key：访问过程中实际用到的索引。如果没有索引被选择，键是NULL。

key_len：使用的索引的长度。这个值可以得出一个多重主键里mysql实际使用了哪一部分。

ref：显示哪个字段或常数与key一起被使用。

rows：返回请求数据的行数，在innodb上是不准确的。

Extra：附加说明

服务器负载过高或者网页打开缓慢

优化思路中的四维度模型

首先要发现问题的过程，通过操作系统、数据库、程序设计、硬件角度四个维度找到问题所在。

先找到瓶颈点的位置，制定好优化方案， 形成处理问题的体系模型。

体系制定好之后，在测试环境进行优化方案的测试。

测试环境下如果优化效果很好，再实施到生产环境上。

最后做好处理问题的记录。好记性不如烂笔头，多做总结，方可大步前进。

MySQL 性能优化

• 为搜索字段创建索引。

• 避免使用 select *，列出需要查询的字段。

• 垂直分割分表。

• 选择正确的存储引擎。

死锁&锁等待&数据库的监控表

死锁是指两个或多个事务在同一资源上互相占用，并请求加锁时，而导致的恶性循环现象。当多个事务以不同顺序试图加锁同一资源时，就会产生死锁。

锁等待:MySQL数据库中，不同session 在更新同行数据时，会出现锁等待的现象。

重要的三张锁的监控表:innodb trx、 innodb_locks 和 innodb_lock waits。

语法

DDL、DML、DCL

1.DDL：数据定义语言，用于定义和管理 SQL 数据库中的所有对象的语言

1.CREATE - 在数据库中创建对象创建 2.ALTER - 改变数据库的结构 修改 3.DROP - 从数据库中删除对象 删除一行 4.truncate - 从表中删除所有记录，包括为记录分配的所有空间都将被删除 删除表中的所有行，但表结构及其列、约束、索引等保持不变 2.DML数据操作语言，SQL中处理数据等操作统称为数据操纵语言

1.SELECT -查询 2.INSERT - 添加 3.UPDATE - 更新 4.DELETE - 从表中删除所有记录，保留记录的空间 删除 5.CALL - 调用PL/SQL或Java子程序 6.EXPLAIN PLAN - explain access path to data

3.DCL数据控制语言，用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等

COMMIT - 提交 SAVEPOINT - 确定事务中的一个点，以后可以回滚到该点 保存点 ROLLBACK - 回滚 SET TRANSACTION - 更改事务选项，如要使用的回滚段 设置当前事务的特性，它对后面的事务没有影响．

delete table & truncate table

TRUNCATE TABLE 和不带 WHERE 的 DELETE 功能是一样的，都是删除表中的所有数据，不过 TRUNCATE TABLE 速度更快，占用的日志更少，这是因为 TRUNCATE TABLE 直接释放数据页并且在事务日志中也只记录数据页的释放，而 DELETE 是一行一行地删除，在事务日志中要记录每一条记录的删除。

那么可不可以用 TRUNCATE TABLE 代替不带 WHERE 的 DELETE 呢？在以下情况是不行的：

1、要保留标识的情况下不能用 TRUNCATE TABLE，因为 TRUNCATE TABLE 会重置标识。

2、需要使用触发器的情况下不能使用 TRUNCATE TABLE ，它不会激发触发器。

3、对于由 FOREIGN KEY 约束引用的表（即主键所在的表，不是外键所在的表）不能使用 TRUNCATE TABLE。

4、对于参与了索引视图的表不能使用 TRUNCATE TABLE ，注意指索引视图，并非普通视图。

Where与Having的区别

1.Where关键字的作用是过滤，选取符合条件的记录，而Having关键字的作用则是为聚合结果指定条件。

2.Where 子句是用来指定 "行" 的条件的，而Having 子句是指定 “组” 的条件的 where id> ? and having id>1 group by name=11;

3.使用Where子句指定条件时，由于排序之前就对数据进行了过滤，所以能够减少排序的数据量。但是Having子句是在排序之后才对数据进行分组的，因此与前者相比，需要排序的数据量就要多得多。

4.使用Where子句更具速度优势的另一个理由是，可以对Where子句指定条件所对应的列创建索引，这样可以大幅提高处理速度。

5.Where子句中不能使用聚合函数，而Having子句中可以。

聚合函数，聚合函数就用来输入多个数据，输出一个数据的。如cout(id)，sum(number)，而每个聚合函数的输入就是每一个多数据的单元格。

order by 和 distinct去重的底层实现

order by：两种排序方式 = 利用有序的索引获取有序的数据 + 相应的排序算法，在内存中将取得数据进行排序

\1. 利用有序的索引进行排序：实际上当我们的用于orderby 设计到的列和explain 执行计划中所利用索引完全一致，mysql可以利用索引键直接取得本来已经排序好的数据，最优排序

这个内存区域就是通过sort_buffer_size系统变量所设置的排序区，他是每个线程的独享的，也就是说同一个时刻在mysql 中可能存在sore_buffer_size内存区域

mysql1.4算法优化，系统参数 数据类型最大长度 > 所要查找的字段类型的大小总和，会第二种

DISTINCT 实际上和 GROUP BY 操作的实现非常相似，只不过是在 GROUP BY 之后的每组中只取出一条记录而已。

什么我没有进行 GROUP BY 操作的时候，执行计划中会告诉我这里通过索引进行了 GROUP BY 呢？其实这就是于 DISTINCT 的实现原理相关的，在实现 DISTINCT的过程中，同样也是需要分组的，然后再从每组数据中取出一条返回给客户端。

视图和游标

视图：是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改会影响基本表。它使得我们获取数据更容易，相比多表查询。

游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

union和union all有什么不同?

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。

UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。 从效率上说，UNION ALL 要比UNION快很多，

所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL。

如何实施大表DDL语句才能把性能影响降到最低?

传统方法：导入导出数据，新建一张与原表一样的表结构，把需要执行的DDL 语句在无数据的新表中执行，然后把老表中的数据导入到新表中，最后把新表改成老表的名字。也可以

通过第三方工具(percona-toolkit)中的 pt-online-schema-change 命令进行在线操作，但对于

MySQL5.7版本可以直接在线“online ddl”。

还可以介绍一下 MySQL对大表进行drop table操作时，可以对数据文件建立硬连接，这样可以缩短执

行时间。依赖的原理:OS HARD LINK。当多个文件名同时指向同一个 INODE时，这个INODE 的引用数

N>1，删除其中任何一个文件名只是删除了一个指针，不会删除数据文件.当INODE 的引用数N-1时，删

除文件时需要把这个文件相关的所有数据块清除，所以会比较耗时。

双一

两个参数着手分析。一个是sync_binlog-1，另一个就是 innodb_flflush log at trx_commit -l。innodb flflush log at trx _commit 和 sync_binlog两个参数是控制

MySQL 磁盘写入策略以及数据安全性的关键参数。

innodb flflush log_ at trx _commit设置为1，每次事务提交时，MySQL都会把log bufer 的数据写入log fifile，并且刷到磁盘中。

sync_ binlog -N（N>O)， MySQL在每写N次二进制日志binarylog时，会使用fdatasyncO函数将它的写 二进制日志 binary log同步到磁盘中。

char & varchar

1.char的长度是不可变的，而varchar的长度是可变的。

定义一个char[10]和varchar[10]。 如果存进去的是‘csdn’,那么char所占的长度依然为10，除了字符‘csdn’外，后面跟六个空格，varchar就立马把长度变为4了，取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的。

2.char的存取数速度还是要比varchar要快得多，因为其长度固定，方便程序的存储与查找。 char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率。 varchar是以空间效率为首位。

3.char的存储方式是：对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。 varchar的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。

4.两者的存储数据都非unicode的字符数据。

float & double

内连接 & 左连接 & 右连接 & 全外连接 & 交叉连接

内连接: 只连接匹配的行

左外连接: 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行

右外连接: 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行

例如1： SELECT a.,b. FROM luntan LEFT JOIN usertable as b ON a.username=b.username

例如2： SELECT a.,b. FROM city as a FULL OUTER JOIN user as b ON a.username=b.username

全外连接: 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。

交叉连接: 生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配

例如： SELECT type,pub_name FROM titles CROSS JOIN publishers ORDER BY type

内连接、外连接、交叉连接、笛卡儿积

内连接：只有两个元素表相匹配的才能在结果集中显示。

左外连接:左边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。

右外连接:右边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。

全外连接：连接的表中不匹配的数据全部会显示出来。

交叉连接： 笛卡尔效应，显示的结果是链接表数的乘积。

获取当前数据库版本号

select version();

MySQL版本&MySQL分支版本

产生分支的原因：许多开发人员认为有必要将其拆分成其他项目，并且每个分支 项目都有自己的专长。该需求和Oracle对核心产品增长缓慢的担忧，导致出现了许多开发人员感兴趣的子项目和分支。

三个流行的MySQL分支:Drizzle、 MariaDB 和 Percona Server(包括XtraDB引擎)。

MariaDB不仅是MySQL的替代品，主要是创新和提高了 MySQL自有技术。新功能介绍如下:multi-source replication 多源复制、表的并行复制CMySQL5.7版本中也加入 该特性入、galera cluster 集群、spider 水平分片和 TokuDB存储引擎。

XtraDB是 InnoDB存储引擎的增强版，可用来更好地发挥最新的计算机硬件系统性能，还包含在高性能 模式下的新特性。它可以向下兼容，因为它是在 InnoDB 基础上构建的，所以有更多的指标和扩展功能。而且它在CPU多核的条件下，可以更好地使用内存，将数据库性能提到更高。

Drizzle 与 MySQL 的差别就比较大了，并且不能兼容，如果想运行此环境，就需要重写一些代码了。

binlog的格式

第一种，statement 格式。 （报表，清单）

优点:不需要记录每一行的变化，减少了 binlog日志量，节约了IO，提高了性能。

缺点:当使用一些特殊函数，或者跨库操作时容易丢失数据。注:在生产中不建议使用。

第二种，row格式。

优点:清晰记录每行的数据信息，不会出现跨库丢数据的情况，安全性非常高。

缺点：当内容记录到日志中时，都将以每行的修改来记录，会产生大量的 binlog，网络开销比较大。

注:生产中推荐使用。

第三种，mixed 格式。

MySQL5.1的一个过渡版本，DDL语句会记录成 statement,DML 会记录成 row。

注:生产中不建议使用。

分布式

主从复制原理

主服务器把数据更新记录到二进制日志中，从服务器通过IO thread 向主库发起binlog请求，主服务器通过 I/O dump thread把二进制日志传递给从库，从库通过IO thread 记录到自己的中继日志中。然后通过SQL thread应用中继日志中 SQL的内容。

主从复制延迟

主库可以并发写入，但从库只能通过单SQL thread 完成任务(MySQL5.7之前)，这是出现主从延迟的最核心原因。

再从其他方面来总结主从延迟原因:

C1) MySQL主从之间的同步本来就不是实时同步的，是异步的同步，也就是说，主库提交事务之后，从 库才再执行一遍。

(2)在主库上对没有索引大表的列进行delete 或者 update的操作

3)从库的硬件配置没有主库的好，经常忽略从库的重要性。

(4)网络抖动导致IO线程复制延迟。

如何监控——通过第三方工具(业界中的瑞土军刀 percona-toolkit)中的 pt-heartbeat 命令进行主从延迟监控。

传统方法，通过比较主从服务器之间的 position号的差异值。 还可以通过查看 seconds behind master 估算一下主从延迟时间。

介绍完诸多引起延迟的原因之后，可以再进行展开延迟的解决方法的讨论。

(1)使用MySQL5.7的并行复制功能。在5.6版本中就有了并行的概念，但其中的并行复制是基于库级别的，即slave parallel_type =database.但这种模式下，只是基于多库少表的情况，并不适用于真实的生产环境下。在MySQL5.7版本中，真正实现了基于组提交的并行复制，简单说就是主库并行执行SQL语句，从库也可以通过多个workers线程并发执行relay log中主库提交的事务。想要开启MySOL5.7的并行复制，可以在从库设置参数slave parallel_workers>O提交的事务。并把5.7 版本中新添加的 slaveparallel_ type 参数设置为 LOGICAL_CLOCK。该参数有DATABASE 和 LOGICAL_CLOCK两个值。

MySQL5.6默认是DATABASE.

2)可以采用Percona公司的percona-xtradb-cluster (简称PXC架构)，这种架构下可以实现多节点写入，达到实时同步。

3)业务初期规划时，就要选择合适的分库、分表策略，避免单表或者单库过大，带来额外的复制压力，从而带来主从延迟的问题。

(4)避免一些无用的IO消耗，可以增加高转速的磁盘、SSD或者PCIE-SSD 设备。

(5)阵列级别要选择RAIDI0, raid cache策略要采用WB，坚决不要采用WT。

(6)IO调度要选择deadline模式。

(7)适当调整buffffer pool 的大小。

(8)避免让数据库进行各种大量运算，要记住数据库只是用来存储数据的，让应用端多分担些压力，或者可以通过缓存来完成。

数据库分库分表

将存放在同一个数据库中的数据分散存放到多个数据库（主机）上面，以达到分散单台设备负载的效果。数据的切分同一时候还能够提高系统的总体可用性，由于单台设备宕机之后。仅仅有总体数据的某部分不可用，而不是全部的数据。

数据的切分（Sharding）依据其切分规则的类型。能够分为两种切分模式。

一种是依照不同的表（或者Schema）来切分到不同的数据库（主机）之上，这样的切能够称之为数据的垂直（纵向）切分。另外一种则是依据表中的数据的逻辑关系，将同一个表中的数据依照某种条件拆分到多台数据库（主机）上面。这样的切分称之为数据的水平（横向）切分。

 

垂直切分的最大特点就是规则简单，实施也更为方便，尤其适合各业务之间的耦合度非常低。相互影响非常小，业务逻辑非常清晰的系统。在这样的系统中，能够非常easy做到将不同业务模块所使用的表分拆到不同的数据库中。依据不同的表来进行拆分。对应用程序的影响也更小，拆分规则也会比較简单清晰。

功能模块越清晰，耦合度越低，数据垂直切分的规则定义也就越easy

垂直切分的长处

◆ 数据库的拆分简单明了，拆分规则明白；

◆ 应用程序模块清晰明白，整合easy。

◆ 数据维护方便易行，easy定位。

垂直切分的缺点

◆ 部分表关联无法在数据库级别完毕。须要在程序中完毕。

◆ 对于訪问极其频繁且数据量超大的表仍然存在性能平静，不一定能满足要求。

◆ 事务处理相对更为复杂；

◆ 切分达到一定程度之后，扩展性会遇到限制；

◆ 过读切分可能会带来系统过渡复杂而难以维护。

水平切分于垂直切分相比。相对来说略微复杂一些。由于要将同一个表中的不同数据拆分到不同的数据库中，对于应用程序来说，拆分规则本身就较依据表名来拆分更为复杂，后期的数据维护也会更为复杂一些。简单的理解为依照表依照模块来切分数据，而水平切分就不再是依照表或者是功能模块来切分了。一般来说，简单的水平切分主要是将某个訪问极其平庸的表再依照某个字段的某种规则来分散到多个表之中。每一个表中包括一部分数据。

◆ 表关联基本能够在数据库端全部完毕；

◆ 不会存在某些超大型数据量和高负载的表遇到瓶颈的问题；

◆ 应用程序端总体架构修改相对较少；

◆ 事务处理相对简单；

◆ 仅仅要切分规则能够定义好。基本上较难遇到扩展性限制；

水平切分的缺点

◆ 切分规则相对更为复杂，非常难抽象出一个能够满足整个数据库的切分规则；

◆ 后期数据的维护难度有所添加，人为手工定位数据更困难；

◆ 应用系统各模块耦合度较高，可能会对后面数据的迁移拆分造成一定的困难。

当我们某个（或者某些）表的数据量和訪问量特别的大，通过垂直切分将其放在独立的设备上后仍然无法满足性能要求，这时候我们就必须将垂直切分和水平切分相结合。先垂直切分，然后再水平切分。才干解决这样的超大型表的性能问题。

联合切分的长处

◆ 能够充分利用垂直切分和水平切分各自的优势而避免各自的缺陷；

◆ 让系统扩展性得到最大化提升。

联合切分的缺点

◆ 数据库系统架构比較复杂。维护难度更大。

◆ 应用程序架构也相对更复杂；

整合：

两种解决思路：

\1. 在每一个应用程序模块中配置管理自己须要的一个（或者多个）数据源。直接訪问各个数据库，在模块内完毕数据的整合；

\2. 通过中间代理层来统一管理全部的数据源。后端数据库集群对前端应用程序透明；

分布式事务（多数据源）

一次大的操作由不同的小操作组成，这些小的操作分布在不同的服务器上，且属于不同的应用，分布式事务需要保证这些小操作要么全部成功，要么全部失败。本质上来说，分布式事务就是为了保证不同数据库的数据一致性。

最具代表性的就是 XA分布式事务协议。

这个协议包含两阶段提交和三阶段提交。

在这个协议中包含两个角色：事务协调者和事务的参与者。

第一个阶段：作为事务协调者的结点会首先向所有的参与者结点发送一个prepare的请求，这些参与者在接受到请求后，写入到undo 和redo 日志中。如果参与者执行成功，暂时不提交事务，而是向事务协调者返回“完成”这样一个消息。

第二个阶段：如果事务协调者收到的都是完成这样的正向返回，那么他 将会给所有的参与者发送commit请求。参与者在接收到commit请求后，进行本地事务的提交，并释放锁资源。当本地事务提交完以后，将会向协调者返回“完成”消息。

然后 协调者在接受到所有事务参与者的“完成”反馈之后，整个分布式事务完成。

在XA的第一阶段，如果某个事务参与者反馈失败消息，说明该节点的本地事务执行不成功，必须回滚。

于是在第二阶段，事务协调节点向所有的事务参与者发送Abort请求。接收到Abort请求之后，各个事务参与者节点需要在本地进行事务的回滚操作，回滚操作依照Undo Log来进行。

XA两阶段提交的不足：

2.协调者单点故障问题

事务协调者是整个XA模型的核心，一旦事务协调者节点挂掉，参与者收不到提交或是回滚通知，参与者会一直处于中间状态无法完成事务。

3.丢失消息导致的不一致问题。

在XA协议的第二个阶段，如果发生局部网络问题，一部分事务参与者收到了提交消息，另一部分事务参与者没收到提交消息，那么就导致了节点之间数据的不一致。

◆ 引入分布式事务的问题。

◆跨节点Join的问题；MySQL一种特殊的存储引擎Federated解决

◆ 跨节点合并排序分页问题。建议通过应用程序来解决。

分布式锁

分布式场景中的数据一致性问题一直是一个比较重要的话题。分布式的cap理论告诉我们“任何一个分布式系统都无法同时满足一致性（Consistency）、可用性（Availability）和分区容错性（Partition tolerance），最多只能同时满足两项。”所以，很多系统在设计之初就要对这三者做出取舍。在互联网领域的绝大多数的场景中，都需要牺牲强一致性来换取系统的高可用性，系统往往只需要保证“最终一致性”，只要这个最终时间是在用户可以接受的范围内即可。

在很多场景中，我们为了保证数据的最终一致性，需要很多的技术方案来支持，比如分布式事务、分布式锁等。有的时候，我们需要保证一个方法在同一时间内只能被同一个线程执行。在单机环境中，Java中其实提供了很多并发处理相关的API，但是这些API在分布式场景中就无能为力了。也就是说单纯的Java Api并不能提供分布式锁的能力。所以针对分布式锁的实现目前有多种方案。

基于缓存实现分布式锁

可以使用缓存来代替数据库来实现分布式锁，这个可以提供更好的性能，同时，很多缓存服务都是集群部署的，可以避免单点问题。并且很多缓存服务都提供了可以用来实现分布式锁的方法，比如Tair的put方法，redis的setnx方法等。并且，这些缓存服务也都提供了对数据的过期自动删除的支持，可以直接设置超时时间来控制锁的释放。

基于Zookeeper实现分布式锁

大致思想即为：每个客户端对某个方法加锁时，在zookeeper上的与该方法对应的指定节点的目录下，生成一个唯一的瞬时有序节点。 判断是否获取锁的方式很简单，只需要判断有序节点中序号最小的一个。 当释放锁的时候，只需将这个瞬时节点删除即可。同时，其可以避免服务宕机导致的锁无法释放，而产生的死锁问题。

来看下Zookeeper能不能解决前面提到的问题。

锁无法释放？使用Zookeeper可以有效的解决锁无法释放的问题，因为在创建锁的时候，客户端会在ZK中创建一个临时节点，一旦客户端获取到锁之后突然挂掉（Session连接断开），那么这个临时节点就会自动删除掉。其他客户端就可以再次获得锁。

非阻塞锁？使用Zookeeper可以实现阻塞的锁，客户端可以通过在ZK中创建顺序节点，并且在节点上绑定监听器，一旦节点有变化，Zookeeper会通知客户端，客户端可以检查自己创建的节点是不是当前所有节点中序号最小的，如果是，那么自己就获取到锁，便可以执行业务逻辑了。

不可重入？使用Zookeeper也可以有效的解决不可重入的问题，客户端在创建节点的时候，把当前客户端的主机信息和线程信息直接写入到节点中，下次想要获取锁的时候和当前最小的节点中的数据比对一下就可以了。如果和自己的信息一样，那么自己直接获取到锁，如果不一样就再创建一个临时的顺序节点，参与排队。

单点问题？使用Zookeeper可以有效的解决单点问题，ZK是集群部署的，只要集群中有半数以上的机器存活，就可以对外提供服务。

你接触过哪些MySQL的主流架构?架构应用中有哪些问题需要考虑?

集群架构:

(I) MS.

(2) MHA。

(3)MM+Keepalived.

(4) PXC

(5)利用中间件 ProxySOL配合PXC架构。

再总结各种集群的优缺点，可以先从 MHA开始说起。

MHA 优点总结:

(1)故障切换时，可以自行判断哪个从库与主库的数据最接近，就切换到上面，可以减少数据丢失的风

险，保证数据的一致性。

(2)支持 binlog server，可提高 binlog传送效率，进一步减少数据丢失风险。

(3)结合MySQL5.7的增强半同步功能，来确保故障切换时的数据不丢失。

缺点:

(1)自动切换的脚本太简单了，而且比较老化，建议后期逐渐完善。

(2)搭建MHA架构，需要开启Linux系统互信协议，所以对于系统安全性来说是个不小的考验。

再介绍一下MM+Keepalived 集群的建议:

(1)一定要完善好切换脚本，Keepalived的切换机制要合理，避免发生切换不成功的现象。

(2)从库的配置尽量要与主库的一致，绝对不能太差:避免主库宕机时发生切换，新的主库(原来的从库)影

响线上业务进行。

3)对于延迟的问题，在这套架构中也不能避免。可以改变架构模式，使用PXC 完成实时同步功能，基本

上可以达到没有延迟。

(4) Keepalived 无法解决脑裂的问题，因此在进行服务异常判断时，可以修改判断脚本，通过对第三方

节点补充检测来决定是否进行切换，可降低脑裂问题产生的风险。

(5)采用Keepalived 架构，在设置两节点状态时，都要设置成backup 状态，而且还都是不抢占模式，通

过优先级来决定谁是主库。避免发生脑裂、冲突现象。

(6)安装好MySQL需要的一些依赖包:建议配置好Yum源，用Yum安装 Keepalived 即可。

最后可以介绍一下PXC架构的优缺点。

优点如下:

(1)实现MySQL 数据库集群架构的高可用性和数据的强一致性。

(2)完成了真正的多节点读写的集群方案。

(3)改善了传统意义上的主从复制延迟的问题，基本上达到了实时同步。

(4)对于新加入的节点可以自动部署，无须手动备份，维护起来很方便。

(5)由于是多节点写入，所以发生数据库故障时切换很容易。

缺点如下:

(1)新加入的节点开销大，需要复制完整的数据，采用SST传输开销太大。(2)任何更新事务都需要全局验证通过，才会在每个节点库上执行。集群性能受限于性能最差的节点，也

就是经常说的短板效应。

(3)因为需要保证数据的一致性，所以在多节点并发写时，锁冲突问题比较严重。

(4)存在写扩大问题，所有的节点上都会发生写操作。

(5)只支持 InnoDB存储引擎表。

(6)没有表级别的锁定，执行DDL语句操作会把整个集群锁住，而且也“kill”不了(注:建议使用OSC操作)。

(7)所有的表必须含有主键，否则操作数据时会报错。

 

你之前处理过MySQL的哪些案例?

解答思路:说到案例，逃离不了 MySQL 的五大知识模块:体系结构、数据的备份恢复、复制、高可用集群

架构和优化。我们可以从这五个方面着手考虑，比如：

(1) MySQL版本的升级。

(2)处理集群架构中的各种“坑”和问题（你遇到过的就可以)。

(3)根据公司业务类型，合理设计MySQL库、表和后期架构。

(4)定期进行灾备恢复演练。

(5)恢复误删除的数据信息

数据库连接池优点

初始化一个数据库连接对象是非常消耗资源的，频繁的创建和销毁数据库连接对象，会对资源造成极大的浪费。连接池就是在一开始初始化的时候，就初始化好了一定数量的数据库连接对象，需要连接的时候，使用已经初始化好的连接对象。当使用完毕后，不进行销毁，而是放回到数据库连接池中，为以后的使用。