MySQL数据库——面试知识点整理

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

        2.　应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0

        3.　应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

        4.　应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20可以这样查询：select id from t where num=10 union all select id from t where num=20

        5.　in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3

        6.　下面的查询也将导致全表扫描：select id from t where name like ‘%李%'若要提高效率，可以考虑全文检索。

        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

        9.　应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)='abc' ，name以abc开头的id应改为:select id from t where name like ‘abc%'

        10.　不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

        11.　在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

        12.　不要写一些没有意义的查询，如需要生成一个空表结构：select col1,col2 into #t from t where 1=0　这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：create table #t(…)

        13.　很多时候用 exists 代替 in 是一个好的选择：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)

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

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

        16.　应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

        17.　尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

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

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

        20.　尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

        21.　避免频繁创建和删除临时表，以减少系统表资源的消耗。

        22.　临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

        23.　在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

        24.　如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

        25.　尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

        26.　使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

        27.　与临时表一样，游标并不是不可使 用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

        28.　在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC 消息。

        29.　尽量避免大事务操作，提高系统并发能力。

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

1、事务四大特性（ACID）

    原子性(Atomicity):原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

    一致性(Consistency):如果事务执行之前数据库是一个完整性的状态,那么事务结束后,无论事务是否执行成功,数据库仍然是一个完整性状态。 (数据库的完整性状态:当一个数据库中的所有的数据都符合数据库中所定义的所有的约束,此时可以称数据库是一个完整性状态。)

    隔离性(Isolation):事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。

    持久性(durability):持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

    引申：NOSQL CAP BASE

    1.关系型数据库和非关系型数据库区别？

    优点

    成本：nosql数据库简单易部署，基本都是开源软件，不需要像使用oracle那样花费大量成本购买使用，相比关系型数据库价格便宜。当然还有免费的

    查询速度：nosql数据库将数据存储于缓存之中，关系型数据库将数据存储在硬盘中，自然查询速度远不及nosql数据库。

    存储数据的格式：nosql的存储格式是key,value形式、文档形式、图片形式等等，所以可以存储基础类型以及对象或者是集合等各种格式，而数据库则只支持基础类型。

    扩展性：关系型数据库有类似join这样的多表查询机制的限制导致扩展很艰难。

    缺点

    维护的工具和资料有限，因为nosql是属于新的技术，不能和关系型数据库10几年的技术同日而语。

    不提供对sql的支持，如果不支持sql这样的工业标准，将产生一定用户的学习和使用成本。

    不提供关系型数据库对事物的处理。

    非关系型数据库的优势

    性能NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。

    可扩展性同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。

关系型数据库的优势

    复杂查询可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。

    事务支持使得对于安全性能很高的数据访问要求得以实现。对于这两类数据库，对方的优势就是自己的弱势，反之亦然。

2.CAP 分布式系统不可能同时满足一致性（C：Consistency）、可用性（A：Availability）和分区容忍性（P：Partition Tolerance），最多只能同时满足其中两项

dubbo+zookeeper 主要实现CP

springcloud eureka [hystrix] 主要实现AP

以上与服务注册细节相关

3.BASE 是基本可用（Basically Available）、软状态（Soft State）和最终一致性（Eventually Consistent）三个短语的缩写。 BASE 理论是对 CAP 中一致性和可用性权衡的结果，它的理论的核心思想是：即使无法做到强一致性，但每个应用都可以根据自身业务特点，采用适当的方式来使系统达到最终一致性。

2、数据库隔离级别，每个级别会引发什么问题，mysql默认是哪个级别？

    SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

    Read Uncommitted（读取未提交内容）

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

    Read Committed（读取提交内容）

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

    Repeatable Read（可重读）

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

    Serializable（可串行化）

    这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。 这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。

    脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

    不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

    幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

    读不影响写：事务以排他锁的形式修改原始数据，读时不加锁，因为 MySQL 在事务隔离级别Read committed 、Repeatable Read下，InnoDB 存储引擎采用非锁定性一致读－－即读取不占用和等待表上的锁。即采用的是MVCC中一致性非锁定读模式。 因读时不加锁，所以不会阻塞其他事物在相同记录上加 X锁来更改这行记录。

    写不影响读：事务以排他锁的形式修改原始数据，当读取的行正在执行 delete 或者 update 操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB 存储引擎会去读取行的一个快照数据。

    间隙锁：间隙锁主要用来防止幻读，用在repeatable-read隔离级别下，指的是当对数据进行条件，范围检索时，对其范围内也许并存在的值进行加锁！ 当查询的索引含有唯一属性（唯一索引，主键索引）时，Innodb存储引擎会对next-key lock进行优化，将其降为record lock,即仅锁住索引本身，而不是范围！若是普通辅助索引，则会使用传统的next-key lock进行范围锁定！

3、MySQL的锁算法

    Record Lock：单个行记录上的锁。

    Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

    Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

4、MySQL的MVCC

    MVCC的全称是“多版本并发控制”。这项技术使得InnoDB的事务隔离级别下执行一致性读操作有了保证，换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值。 这是一个可以用来增强并发性的强大的技术，因为这样的一来的话查询就不用等待另一个事务释放锁。这项技术在数据库领域并不是普遍使用的。一些其它的数据库产品，以及mysql其它的存储引擎并不支持它。

    mysql的innodb采用的是行锁，而且采用了多版本并发控制来提高读操作的性能。

1.什么是多版本并发控制呢MVCC ？

    其实就是在每一行记录的后面增加两个隐藏列，记录创建版本号和删除版本号，而每一个事务在启动的时候，都有一个唯一的递增的版本号。 在InnoDB中，给每行增加两个隐藏字段来实现MVCC，两个列都用来存储事务的版本号，每开启一个新事务，事务的版本号就会递增。

2.默认的隔离级别（REPEATABLE READ）下，增删查改？

    SELECT

    读取创建版本小于或等于当前事务版本号，并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的

    INSERT

    将当前事务的版本号保存至行的创建版本号

    UPDATE

    新插入一行，并以当前事务的版本号作为新行的创建版本号，同时将原记录行的删除版本号设置为当前事务版本号

    DELETE

    将当前事务的版本号保存至行的删除版本号

3.什么是快照读和当前读？

    快照读：读取的是快照版本，也就是历史版本

    当前读：读取的是最新版本

    普通的SELECT就是快照读，而UPDATE、DELETE、INSERT、SELECT …  LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。

4.什么是锁定读？

    在一个事务中，标准的SELECT语句是不会加锁，但是有两种情况例外。

    SELECT ... LOCK IN SHARE MODE 给记录假设共享锁，这样一来的话，其它事务只能读不能修改，直到当前事务提交

    SELECT ... FOR UPDATE 给索引记录加锁，这种情况下跟UPDATE的加锁情况是一样的

5.什么是一致性非锁定读？

    consistent read （一致性读），InnoDB用多版本来提供查询数据库在某个时间点的快照。如果隔离级别是REPEATABLE READ，那么在同一个事务中的所有一致性读都读的是事务中第一个这样的读读到的快照； 如果是READ COMMITTED，那么一个事务中的每一个一致性读都会读到它自己刷新的快照版本。Consistent read（一致性读）是READ COMMITTED和REPEATABLE READ隔离级别下普通SELECT语句默认的模式。 一致性读不会给它所访问的表加任何形式的锁，因此其它事务可以同时并发的修改它们。

    MVCC实现一致性非锁定读，这就有保证在同一个事务中多次读取相同的数据返回的结果是一样的，解决了不可重复读的问题。

6.什么是悲观锁和乐观锁？

        悲观锁：正如它的名字那样，数据库总是认为别人会去修改它所要操作的数据，因此在数据库处理过程中将数据加锁。其实现依靠数据库底层。

        乐观锁：如它的名字那样，总是认为别人不会去修改，只有在提交更新的时候去检查数据的状态。通常是给数据增加一个字段来标识数据的版本。

7.select时怎么加排它锁？

    使用锁定读，普通select不会引起加锁，而是去读取最新的快照。同上4

    事务以排他锁的形式修改原始数据，当读取的数据正在进行更新等操作，则直接去读取快照，而不是等锁释放

5、MYSQL的两种存储引擎区别（事务、锁级别等等），各自的适用场景

MyISAM

    不支持事务，但是每次查询都是原子的；

    支持表级锁，即每次操作是对整个表加锁；

    存储表的总行数；

    一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；

    采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

    适用OLAP

InnoDb

    支持ACID的事务，支持事务的四种隔离级别；

    支持行级锁及外键约束：因此可以支持写并发；

不存储总行数；

    一个InnoDb引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G），受操作系统文件大小的限制；

    主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；

    最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

    适用OLTP

InnoDB主要特性

    主要包括：插入缓存（insert buffer）、两次写(double write)、自适应哈希(Adaptive Hash index)、异步IO(Async IO)、刷新邻接页(Flush Neighbor Page)

感兴趣可以参考书籍《MySQL技术内幕:innodb存储引擎》 网上找了一个博客InnoDB关键特性

6、索引有B+索引和hash索引，各自的区别？

主要区别

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

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

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

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

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

7、为什么B+树适合作为索引的结构？

    B树：有序数组+平衡多叉树

    B+树：有序数组链表+平衡多叉树 叶子存储数据，空间占用小，且是双链表，修改效率快

    不同于B树只适合随机检索，B+树同时支持随机检索和顺序检索

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

    平衡二叉树没能充分利用磁盘预读功能，而B树是为了充分利用磁盘预读功能来而创建的一种数据结构，也就是说B树就是为了作为索引才被发明出来的的。

1.局部性原理与磁盘预读

    由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。 为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：

    当一个数据被用到时，其附近的数据也通常会马上被使用。 程序运行期间所需要的数据通常比较集中。 由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

2.为什么说红黑树没能充分利用磁盘预读功能？

    红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

    也就是说，使用红黑树（平衡二叉树）结构的话，每次磁盘预读中的很多数据是用不上的数据。因此，它没能利用好磁盘预读的提供的数据。然后又由于深度大（较B树而言），所以进行的磁盘IO操作更多。

8、B+索引数据结构，和B树的区别 ？

    除了以上的，主要区别，其他请看B- B+ B*树 实际上B-就是B树，二叉树不叫B树，像这种写法B-Tree，可以是B-树也可以说是B树[-可能是连接符，可能是翻译问题]，B+ B* 是改善的B树

9、索引的分类（主键索引、唯一索引），最左前缀原则，哪些情况索引会失效？

        1.关于索引优化最前面已有了，不再罗列

        2.各种索引区别

        普通索引：最基本的索引，没有任何限制。

        唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

        主键索引：它 是一种特殊的唯一索引，不允许有空值。

        全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时耗空间。

        组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

10、聚集索引和非聚集索引区别是什么？

    聚集（clustered）索引，也叫聚簇索引。

        定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

    非聚集（unclustered）索引。

        定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

    总结

        使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。

    非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。

        不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好。

        重复度高的可能使得索引失效

        具体最前面有更详细的    

11、schema(表结构)对性能的影响？

1.冗余数据的处理

    适当的数据冗余可以提高系统的整体查询性能(在P2P中,在userinfo对象中有realname和idnumber);

关系数据库的三范式:

    第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库，是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值；

    第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。 即各字段和主键之间不存在部分依赖

    第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。即在第二范式的基础上，不存在传递依赖 (不允许有冗余数据)

2.大表拆小表，有大数据的列单独拆成小表

    在一个数据库中,一般不会设计属性过多的表;

    在一个数据库中,一般不会有超过500/1000万数据的表(拆表,按照逻辑拆分,按照业务拆分);

    有大数据的列单独拆成小表(富文本编辑器,CKeditor);

3.根据需求的展示设置更合理的表结构

4.把常用属性分离成小表

    在P2P项目中,我们把logininfo和userinfo和account表拆成了三张表;

    减少查询常用属性需要查询的列;

    便于常用属性的集中缓存;

12、数据库的主从复制 ？

    就算MYSQL拆成了多个,也必须分出主和从,所有的写操作都必须要在主MYSQL 上完成;

    所有的从MYSQL的数据都来自于(同步于)主MYSQL;

    既然涉及到同步,那一定有延迟;有延迟,就一定可能在读的时候产生脏数据;所以,能够在从MYSQL上进行的读操作,一定对实时性和脏数据有一定容忍度的数据;比如,登陆日志,后台报表,首页统计信息来源;文章;资讯;SNS消息;

    在我们的P2P中,做主从,绝大部分的读操作,都必须在主MYSQL上执行;只有(登陆日志,报表,满标一审列表,满标二审列表,用户的流水信息,充值明细,投标明细查询类的业务可以定位到从MYSQL);

    【一定注意】:在MYSQL主从时,如果一个业务(service中的一个方法)中,如果既有R操作,又有W操作,因为W操作一定要在主MYSQL上,所以在一个事务中所有的数据来源都只能来自于一个MYSQL

    要完成主从同步,就必须让在Master上执行的所有的DML和DDL能够正确的在Salve上再执行一遍;MYSQL选择使用文件来记录SQL;

    完成主从同步,第一个事情就是把在主服务器上的bin-log(二进制文件)打开,bin-log文件就可以记录在MYSQL上执行的所有的DML+DDL+TCL;

    MYSQL使用被动注册的方式来让从MYSQL请求同步主MYSQL的binlog;原因:被动请求的方式,主的MYSQL不需要知道有哪些从的MYSQL,我额外添加/去掉从MYSQL服务器,对主MYSQL服务器的正常运行没有任何影响;

    第二步,从MYSQL后台一个线程发送一个请求,到主服务器请求更新数据;最重要的数据(我这次请求,请求你bin-log的哪一行数据之后的数据)

    第三步,主MYSQL后台一个线程接收到从MYSQL发送的请求,然后读取bin-log文件中指定的内容,并放在从MYSQL的请求响应中;

    第四步,从MYSQL的请求带回同步的数据,然后写在从MYSQL中的relay-log(重做日志)中;relay-log中记录的就是从主MYSQL中请求回来的哪些SQL数据;

    第五步,从MYSQL后台一个线程专门用于从relay-log中读取同步回来的SQL,并写入到从MYSQL中,完成同步;

    MYSQL的主从同步是经过高度优化的,性能非常高;

这里东西太多，更多请参考mysql优化的课程笔记

13、explain和join

EXPLAIN:

使用方式： explain SQL;

返回结果：

ID：执行查询的序列号；

select_type：使用的查询类型

DEPENDENT SUBQUERY：子查询中内层的第一个SELECT，依赖于外部查询的结果集；

DEPENDENT UNION：子查询中的UNION，且为UNION 中从第二个SELECT 开始的后面所有SELECT，同样依赖于外部查询的结果集；

PRIMARY：子查询中的最外层查询，注意并不是主键查询；

SIMPLE：除子查询或者UNION 之外的其他查询；

SUBQUERY：子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集；

UNCACHEABLE SUBQUERY：结果集无法缓存的子查询；

UNION：UNION 语句中第二个SELECT 开始的后面所有SELECT，第一个SELECT 为PRIMARY

UNION RESULT：UNION 中的合并结果；

table：这次查询访问的数据表；

type：对表所使用的访问方式：

all：全表扫描

const：读常量，且最多只会有一条记录匹配，由于是常量，所以实际上只需要读一次；

eq_ref：最多只会有一条匹配结果，一般是通过主键或者唯一键索引来访问；

fulltext：全文检索，针对full text索引列；

index：全索引扫描；

index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行merge 之后再读取表数据；

index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或者唯一索引；

rang：索引范围扫描；

ref：Join 语句中被驱动表索引引用查询；

ref_or_null：与ref 的唯一区别就是在使用索引引用查询之外再增加一个空值的查询；

system：系统表，表中只有一行数据；

unique_subquery：子查询中的返回结果字段组合是主键或者唯一约束；

possible_keys：可选的索引；如果没有使用索引，为null；

key：最终选择的索引；

key_len：被选择的索引长度；

ref：过滤的方式，比如const（常量），column（join），func（某个函数）；

rows：查询优化器通过收集到的统计信息估算出的查询条数；

Extra：查询中每一步实现的额外细节信息

Distinct：查找distinct 值，所以当mysql 找到了第一条匹配的结果后，将停止该值的查询而转为后面其他值的查询；

Full scan on NULL key：子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用使用；

Impossible WHERE noticed after reading const tables：MySQL Query Optimizer 通过收集到的统计信息判断出不可能存在结果；

No tables：Query 语句中使用FROM DUAL 或者不包含任何FROM 子句；

Not exists：在某些左连接中MySQL Query Optimizer 所通过改变原有Query 的组成而使用的优化方法，可以部分减少数据访问次数；

Select tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段的时候，MySQL Query Optimizer 会通过索引而直接一次定位到所需的数据行完成整个查询。当然，前提是在Query 中不能有GROUP BY 操作。如使用MIN()或者MAX（）的时候；

Using filesort：当我们的Query 中包含ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。

Using index：所需要的数据只需要在Index 即可全部获得而不需要再到表中取数据；

Using index for group-by：数据访问和Using index 一样，所需数据只需要读取索引即可，而当Query 中使用了GROUP BY 或者DISTINCT 子句的时候，如果分组字段也在索引中，Extra 中的信息就会是Using index for group-by；

Using temporary：当MySQL 在某些操作中必须使用临时表的时候，在Extra 信息中就会出现Using temporary 。主要常见于GROUP BY 和ORDER BY 等操作中。

Using where：如果我们不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现Using where 信息；

Using where with pushed condition：这是一个仅仅在NDBCluster 存储引擎中才会出现的信息，而且还需要通过打开Condition Pushdown 优化功能才可能会被使用。控制参数为engine_condition_pushdown 。

profiling: Query Profiler是MYSQL5.1之后提供的一个很方便的用于诊断Query执行的工具，能够准确的获取一条查询执行过程中的CPU，IO等情况；

开启profiling：set profiling=1

执行QUERY，在profiling过程中所有的query都可以记录下来

查看记录的query：show profiles

选择要查看的profile：show profile cpu, block io for query 6

status是执行SQL的详细过程

Duration：执行的具体时间

CPU_user：用户CPU时间

CPU_system：系统CPU时间

Block_ops_in：IO输入次数

Block_ops_out：IO输出次数

profiling只对本次会话有效

JOIN的原理

        在mysql中使用Nested Loop Join来实现join； A JOIN B：通过A表的结果集作为循环基础，一条一条的通过结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果；

JOIN的优化原则

    尽可能减少Join 语句中的Nested Loop 的循环总次数，用小结果集驱动大结果集；

    优先优化Nested Loop 的内层循环；

    保证Join 语句中被驱动表上Join 条件字段已经被索引；

    扩大join buffer的大小；

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

内连接 

        只有两个表相匹配的行才能在结果集中出现 分为三种：等值连接、自然连接、不等连接 

外连接 

        左外连接(LEFT OUTER JOIN或LEFT JOIN) 以左边为准，右边没用则为空

        右外连接(RIGHT OUTER JOIN或RIGHT JOIN) 以右边为准，左边没有则为空

        全外连接(FULL OUTER JOIN或FULL JOIN) 左右均可能为空 

交叉连接

        没有WHERE 子句，它返回连接表中所有数据行的笛卡尔积 

笛卡儿积

15、死锁怎么解决？

产生死锁的原因主要是

        系统资源不足。

        进程运行推进的顺序不合适。

        资源分配不当等。

        如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。

产生死锁的四个必要条件

        1. 互斥条件：一个资源每次只能被一个进程使用。

        2. 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。

        3. 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。

        4. 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁。

死锁的预防和解除

        理解了死锁的原因，尤其是产生死锁的四个必要条件，就可以最大可能地避免、预防和解除死锁。所以，在系统设计、进程调度等方面注意如何不让这四个必要条件成立，如何确定资源的合理分配算法，避免进程永久占据系统资源。此外，也要防止进程在处于等待状态的情况下占用资源,在系统运行过程中，对进程发出的每一个系统能够满足的资源申请进行动态检查，并根据检查结果决定是否分配资源，若分配后系统可能发生死锁，则不予分配，否则予以分配 。因此，对资源的分配要给予合理的规划。 如何将死锁减至最少

        虽然不能完全避免死锁，但可以使死锁的数量减至最少。将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务回滚，而回滚会取消事务执行的所有工作。由于死锁时回滚而由应用程序重新提交。

下列方法有助于最大限度地降低死锁

    按同一顺序访问对象。

    避免事务中的用户交互。

    保持事务简短并在一个批处理中。

    使用低隔离级别。

    使用绑定连接。

作者：我是一禅呐
链接：https://www.jianshu.com/p/37b7d5b19945