Mysql基础

开闭数据库安全模式

SET SQL_SAFE_UPDATES = 0/1    // 0:关闭  1：开启

b树（b-树）和b+树

  B树也称B-树,它是一颗多路平衡查找树。二叉树我想大家都不陌生，其实，B树和后面讲到的B+树也是从最简单的二叉树变换而来的，并没有什么神秘的地方，下面我们来看看B树的定义。
  (1)每个节点最多有m-1个关键字（可以存有的键值对）。
  (2)根节点最少可以只有1个关键字。
  (3)非根节点至少有m/2个关键字。
  (4)每个节点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。
  (5)所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同。
  (6)每个节点都存有索引和数据，也就是对应的key和value。

B+树其实和B树是非常相似的，我们首先看看相同点。
  (1)根节点至少一个元素
  (2)非根节点元素范围：m/2 <= k <= m-1

不同点。
  (1)B+树有两种类型的节点：内部结点（也称索引结点）和叶子结点。内部节点就是非叶子节点，内部节点不存储数据，只存储索引，数据都存储在叶子节点。
  (2)内部结点中的key都按照从小到大的顺序排列，对于内部结点中的一个key，左树中的所有key都小于它，右子树中的key都大于等于它。叶子结点中的记录也按照key的大小排列。
  (3)每个叶子结点都存有相邻叶子结点的指针，叶子结点本身依关键字的大小自小而大顺序链接。
  (4)父节点存有右孩子的第一个元素的索引。

B+树相对于B树有一些优势：
  (1)单一节点存储的元素更多，使得查询的IO次数更少，所以也就使得它更适合做为数据库MySQL的底层数据结构了。
  (2)所有的查询都要查找到叶子节点，查询性能是稳定的，而B树，每个节点都可以查找到数据，所以不稳定。
  (3)所有的叶子节点形成了一个有序链表，更加便于查找。

B树和B+树相比，有两个最核心的区别：
  1、B树没有内部节点和叶子结点的区分，它的每个节点都是即存了key又存了data。
  2、由于没有内部节点和叶子结点的区分，使得B树没有将叶子结点用链表串联起来的结构。

这两个区别给B树带来了两个检索的特点：
  1、进行单个key查询时，B树最快可以在O(1)的时间代价内就查到。而从平均时间代价来看，会比B+树稍快一些。但波动会比较大，因为每个节点既存key又存data会使得树变高，底层的节点的IO次数就会变多。
  2、进行范围查询时，由于缺乏叶子结点的连接，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的IO问题，效率不如B+树。

因此，存在大量范围查询的场景，适合使用B+树（比如数据库）；
而对大量单个key查询的场景，可以考虑B树（比如NOSQL的MongoDB）

事物并发

脏写、脏读、不可重复读、幻读

  脏写：事务A、事务B同时在更新一条数据，事务A将其更新为A值，事务B将其更新为B值。随后事务A回滚，根据undo log将其恢复为事务A修改前的值。事务B更新了，但是值没了。
  脏读：事务B查询事务A修改过的数据，但是事务A还没提交，事务A回滚导致事务B再次查询拿不到事务A修改的数据。
  无论是脏读和脏写，都是因为一个事物去更新或者查询了另一个还没提交的事务更新过的数据。因为事务还没有提交，所以随时可能回滚，必然导致另一个事务查询或者更新的数据失效。

  不可重复读：事务在执行期间多次读取同一条数据，得到不同的结果。事务A读取一条记录值为A，执行期间事务B将记录值改为B，第二次查询为B，同理多次修改导致事务A读取数据值不同。
幻读：每次查询会看到一些之前没有的记录。事务A查询到结果之后，事务B插入记录，事务A再次查询时记录条数变多。

事务隔离级别

事务隔离级别：读未提交、读已提交、可重复读、序列化。Mysql默认开启RR级别。
  read uncommitted（读未提交）：不会脏写。RU隔离级别，就是可以读到人家没提交的事务修改的数据，只能避免脏写问题；
   read committed（读已提交：RC）：不会脏写、脏读。RC隔离级别，可以读到人家提交的事务修改过的数据，可以避免脏写和脏读问题。
  repeatable read（可重复读：RR）：不会脏写、脏读、不可重复读。RR是不会读到别的已经提交事务修改的数据，可以避免脏读、脏写和不可重复读的问题；
  serializable（串行化）：不会有并发问题。串行是让事务都串行执行，可以避免所有问题。

MVCC

MVCC：多版本并发控制隔离机制。基于undo log版本链条和ReadView机制。本质是协调你多个事务并发运行的时候，并发的读写同一批数据，此时应该如何协调互相的可见性。
  依赖undo log版本链，每条数据有两个隐藏字段trx_id、roll_pointer.trx_id是最近一次更新这条数据的事务id，roll_pointer指向更新这个事务之前生成的undo_log。多个事务串行执行的时候，每个人修改了一行数据，都会更新隐藏字段txr_id和roll_pointer，同时之前多个数据快照对应的undo log，会通过roll_pinter指针串联起来，形成一个重要的版本链！

执行一个事务的时候，就给你生成一个ReadView，里面比较关键的东西有4个：
  （1）m_ids，这个就是说此时有哪些事务在MySQL里执行还没提交的；
  （2）min_trx_id，就是m_ids里最小的值；
  （3）max_trx_id，这是说mysql下一个要生成的事务id，就是最大事务id；
  （4）creator_trx_id，就是你这个事务的id
  通过undo log多版本链条，加上你开启事务时候生产的一个ReadView，然后再有一个查询的时候，根据ReadView进行判断的机制，你就知道你应该读取哪个版本的数据。而且他可以保证你只能读到你事务开启前，别的提交事务更新的值，还有就是你自己事务更新的值。假如说是你事务开启之前，就有别的事务正在运行，然后你事务开启之后 ，别的事务更新了值，你是绝对读不到的！或者是你事务开启之后，比你晚开启的事务更新了值，你也是读不到的！

锁

  其实多个事务同时更新一行数据，此时都会加锁，然后都会排队等待，必须一个事务执行完毕了，提交了，释放了锁，才能唤醒别的事务继续执行。那么在这多个事务运行的时候，加的是X锁，也就是Exclude独占锁，当有一个事务加了独占锁之后，此时其他事务再要更新这行数据，都是要加独占锁的，但是只能生成独占锁在后面等待。
  对一行数据的读和写两个操作默认是不会加锁互斥的，因为MySQL设计mvcc机制就是为了解决这个问题，避免频繁加锁互斥。
  事务执行时会对行数据加锁。
  DDL（alter）操作会与DML操作冲突，所以会加表级锁。
  比较常见的两种表锁，反而是更新和查询操作加的意向独占锁和意向共享锁，但是这个意向独占锁和意向共享锁，大家暂时可以当他是透明的就可以了，因为两种意向锁根本不会互斥。

sql四种语言

1. DDL 数据库定义语言

DDL（Data Definition Language）数据库定义语言statements are used to define the database structure or schema.
DDL是SQL语言的四大功能之一。
用于定义数据库的三级结构，包括外模式、概念模式、内模式及其相互之间的映像，定义数据的完整性、安全控制等约束
DDL不需要commit.
CREATE
ALTER
DROP
TRUNCATE
COMMENT
RENAME

2. DML 数据操纵语言

DML（Data Manipulation Language）数据操纵语言statements are used for managing data within schema objects.
由DBMS提供，用于让用户或程序员使用，实现对数据库中数据的操作。
DML分成交互型DML和嵌入型DML两类。
依据语言的级别，DML又可分成过程性DML和非过程性DML两种。
需要commit.
SELECT
INSERT
UPDATE
DELETE
MERGE
CALL
EXPLAIN PLAN
LOCK TABLE

3. DCL 数据库控制语言

DCL（Data Control Language）数据库控制语言  授权，角色控制等
GRANT 授权
REVOKE 取消授权

4. TCL 事务控制语言

TCL（Transaction Control Language）事务控制语言
SAVEPOINT 设置保存点
ROLLBACK  回滚
SET TRANSACTION
SQL主要分成四部分：
（1）数据定义。（SQL DDL）用于定义SQL模式、基本表、视图和索引的创建和撤消操作。
（2）数据操纵。（SQL DML）数据操纵分成数据查询和数据更新两类。数据更新又分成插入、删除、和修改三种操作。
（3）数据控制。包括对基本表和视图的授权，完整性规则的描述，事务控制等内容。
（4）嵌入式SQL的使用规定。涉及到SQL语句嵌入在宿主语言程序中使用的规则。

索引

全表扫描：在没有任何索引数据结构的时候，无论如何查找数据，都是一个全表扫描的过程。根据双向链表依次把磁盘上的数据页加载到缓存页里去，然后在一个缓存页内部来查找那条数据。最坏的情况下，得把所有数据页里的每条数据都得遍历一遍，才能找到需要的那条数据，这就是全表扫描！
  针对主键的索引实际上就是主键目录，这个主键目录，就是把每个数据页的页号，还有数据页里最小的主键值放在一起，组成一个索引的目录。
  当你为一个表的主键建立起来索引之后，其实这个主键的索引就是一颗B+树，然后当你要根据主键来查数据的时候，直接就是从B+树的顶层开始二分查找，一层一层往下定位，最终一直定位到一个数据页里，在数据页内部的目录里二分查找，找到那条数据。这就是索引最真实的物理存储结构，采用跟数据页一样的页结构来存储，一个索引就是很多页组成的一颗B+树。
  非主键索引在查找时先找到对应的主键索引，进行“回表”，找到对应的完整数据。

常见索引使用规则

  1.等值匹配：where语句中的几个字段名称和联合索引的字段完全一样，而且都是基于等号的等值匹配，那百分百会用到索引，即使where语句里写的字段的顺序和联合索引里的字段顺序不一致，MySQL会自动优化为按联合索引的字段顺序去找。
  2.最左侧列：这个意思就是假设联合索引是KEY(class_name,student_name, subject_name)，那么不一定必须要在where语句里根据三个字段来查，其实只要根据最左侧的部分字段来查，也是可以的。
  3.最左前缀：如果要用like语法来查，比如select * from student_scorewhere class_name like ‘1%’，查找所有1打头的班级的分数，那么也是可以用到索引的。
  4.范围查找：可以用select * from student_score where class_name>‘1班’ and class_name<'5班’这样的语句来范围查找某几个班级的分数。
  5.等值匹配+范围查找：一般如果写SQL语句，都是用联合索引的最左侧的多个字段来进行等值匹配+范围搜索，或者是基于最左侧的部分字段来进行最左前缀模糊匹配，或者基于最左侧字段来进行范围搜索，这就要写符合规则的SQL语句，才能用上建立好的联合索引。

  应该尽量最好是按照联合索引的字段顺序去进行order by排序，这样就可以直接利用联合索引树里的数据有序性，到索引树里直接按照字段值的顺序去获取你需要的数据。

索引设计原则

  1.涵盖select、order by、group by，保证sql可以用到索引
  2.一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。
  3.尽量是对那些字段的类型比较小的列来设计索引，比如说什么tinyint之类的，因为他的字段类型比较小，说明这个字段自己本身的值占用磁盘空间小，此时你在搜索的时候性能也会比较好一点。不过当然了，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间，那你也得去设计这样的索引，比较关键的其实还是尽量别把基数太低的字段包含在索引里，因为意义不是太大。
  万一要是真的有那种varchar(255)的字段，可能里面的值太大了，都放索引树里太占据磁盘空间了，完全可以换一种策略，也就是仅仅针对这个varchar(255)字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里而已。
  order by 和 group by会用不到。
  4.**设计索引别太多，建议两三个联合索引就应该覆盖掉表的全部查询。**否则索引太多必然导致你增删改数据的时候性能很差，因为要更新多个索引树。
  **另外很关键一点，建议大家主键一定是自增的，别用UUID之类的，**因为主键自增，那么起码聚簇索引不会频繁的分裂，主键值都是有序的，就会自然的新增一个页而已，但是如果用的是UUID，那么也会导致聚簇索引频繁的页分裂。

查询过程

执行计划

  基础的以及日常的SQL优化就是设计好索引，让一般不太复杂的普通查询都用上索引，但是针对复杂表结构和大数据量的上百行复杂SQL的优化，需要先知道复杂SQL是怎么执行的（如何检索查询、如何筛选过滤、如何使用函数、如何进行排序、如何进行分组），这就是执行计划。
  查询优化器会针对每条SQL生成一个执行计划。

  const：直接就可以通过聚簇索引或者二级索引+聚簇索引回源，轻松查到你要的数据，这种根据索引直接可以快速查找数据的过程，在执行计划里称之为const，意思就是性能超高的常量。但是这里有一个要点，你的二级索引必须是唯一索引，才是属于const方式的，也就是说你必须建立unique key唯一索引，保证一个二级索引的每一个值都是唯一的，才可以。
  ref：普通二级索引，不是唯一索引，这种查询速度也是很快的，他在执行计划里叫做ref。如果是包含多个列的普通索引，那么必须是从索引最左侧开始连续多个列都是等值比较才可以是属于ref方式。然后一个例外，就是如果你用name IS NULL这种语法的话，即使name是主键或者唯一索引，还是只能走ref方式。说白了，就是在二级索引里搜你要的值以及是NULL的值，然后再回源去聚簇索引里查罢了，因为同时有索引等值比较和NULL值查询，就叫做ref_or_null了。
  range：SQL里有范围查询的时候就会走这个方式。比如写一个SQL是select * from table where age>=x and age <=x，假设age就是一个普通索引，此时就必然利用索引来进行范围筛选，一旦利用索引做了范围筛选，那么这种方式就是range。
  index：针对这种只要遍历二级索引就可以拿到你想要的数据，而不需要回源到聚簇索引的访问方式，就叫做index访问方式。
  all：最次的一种就是all了，all意思就是直接全表扫描，扫描你的聚簇索引的所有叶子节点，也就是一个表里一行一行数据去扫描。

  const、ref和range本质都是基于索引查询，只要你索引查出来的数据量不是特别大，一般性能都极为高效，index稍微次一点，需要遍历某个二级索引，但是因为二级索引比较小，所以遍历性能也还可以的。
可能一个SQL里用多个索引，意思就是可能对多个索引树进行查找，接着用intersection交集、union并集的方式来进行合并，此时可能给你在执行计划里也会看到这些字样，那你起码这里要知道是怎么回事，其实他就是告诉你，他查找了多个索引，做了一些结果集的交集或者是并集，而且这种方式也不一定是会发生的。

   先从一个表里查一波数据，这个表叫做“驱动表”，再根据这波数据去另外一个表里查一波数据进行关联，另外一个表叫做“被驱动表”。

  内连接，英语是inner join，意思就是要求两个表里的数据必须是完全能关联上的，才能返回回来，这就是内连接。
  外连接，是outer join，这个outer join分为左外连接和右外连接，左外连接的意思就是，在左侧的表里的某条数据，如果在右侧的表里关联不到任何数据，也得把左侧表这个数据给返回出来，右外连接反之，在右侧的表里如果关联不到左侧表里的任何数据，得把右侧表的数据返回出来。

  如果你是之前的那种内连接，那么连接条件是可以放在where语句里的，但是外连接一般是把连接条件放在ON字句里的。
通常而言，针对多表查询的语句，我们要尽量给两个表都加上索引，索引要确保从驱动表里查询也是通过索引去查找，接着对被驱动表查询也通过索引去查找。如果能做到这一点，你的多表关联语句性能就会很高！
  把查出来的数据都写入一个临时表里，也可以叫做物化表，意思就是说，把这个中间结果集进行物化。

主从复制

主从复制架构，顾名思义，就是部署两台服务器，每台服务器上都得有一个MySQL，其中一个MySQL是master（主节点），另外一个MySQL是slave（从节点）。
  系统平时连接到master节点写入数据，当然也可以从里面查询了，和使用一个单机版的MySQL是一样的，但是master节点会把写入的数据自动复制到slave节点去，让slave节点可以跟master节点有一模一样的数据。

读写分离架构的意思就是，业务系统可以往主节点写入数据，但是从从节点去查询数据，把读写操作做一个分离，分离到两台MySQL服务器上去，一台服务器专门写入数据，然后复制数据到从节点，另外一台服务器专门查询数据。

  MySQL自己在执行增删改的时候会记录binlog日志，binlog日志里就记录了所有数据增删改的操作。从库上有一个IO线程，这个IO线程会负责跟主库建立一个TCP连接，接着请求主库传输binlog日志给自己，这个时候主库上有一个IO dump线程，就会负责通过这个TCP连接把binlog日志传输给从库的IO线程。接着从库的IO线程会把读取到的binlog日志数据写入到自己本地的relay日志文件中去，然后从库上另外有一个SQL线程会读取relay日志里的内容，进行日志重做，把所有在主库执行过的增删改操作，在从库上做一遍，达到一个还原数据的过程。