MySQL

SQL查询语句执行流程

当客户端和servel端建立连接之后，先查询缓存，如果缓存命中就返回结果（一旦一张表中执行更新数据，这张表的缓存就会清空，所以缓存命中几率不高，特别是更新频繁的表），如果缓存没有命中，就将查询语句交给分析器进行语法分析，然后由优化器进行优化（有索引选择合适索引，join连接优化连接顺序），然后交生产执行计划，交由执行器执行

redoLog、undoLog和binLog

• redoLog（InnoDB引擎日志）：为了提高效率，mysql不会每来一条更新操作都写一次磁盘，而是现将记录写到redoLog（顺序IO）中，然后更新内存，等到系统空闲时再将redoLog中的记录写到对应磁盘位置。redoLog数据结构是一个环形队列，一端写数据，一端刷磁盘（将redoLog日志文件中的更新，写到对应数据磁盘上）。innodb_flush_log_at_trx_commit设置成1，表示每次事务的redo Log都直接持久化到磁盘。
• undoLog：回滚日志，提供回滚操作，保证事务失败之后数据回滚到上一个版本。保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。
• mysql的update操作：当redoLog写满、系统空闲、关机的时候，就会将redoLog中的数据刷盘，刷盘的时候会将redoLog记录的操作更新到磁盘中；当内存不够用，就会将最近不使用的数据页淘汰掉，如果淘汰的是脏页就要更新磁盘数据。
  
• binLog（Server层日志）：binLog有两种模式，statement格式记录的是sql语句，而row格式记录的是内容（记两条，更新前一条更新后一条）；Bin log 用于记录了完整的逻辑记录，所有的逻辑记录在 bin log 里都能找到，所以在备份恢复时，是以 bin log 为基础，通过其记录的完整逻辑操作，备份出一个和原库完整的数据。sync_binlog设置为1，表示每次事务的binLog都持久化到磁盘。

事务执行的流程
undo日志用于记录事务开始前的状态，用于事务失败时的回滚操作；redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。例如某一事务的事务序号为T1，其对数据X进行修改，设X的原值是5，修改后的值为15，那么Undo日志为<T1, X, 5>，Redo日志为<T1, X, 15>。
（1）写undo日志到log buffer；

（2）执行事务，并写redo日志到log buffer；

（3）如果innodb_flush_log_at_trx_commit=1，则将redo日志写到log file，并刷新落盘。

（4）提交事务。

更新操作

• 更新操作的流程：mysql执行update更新操作的时候，如果该记录的数据页在内存中，就更新内存中数据页的记录，并把更新操作写到redoLog日志文件中；如果记录的数据页不在内存中，就将更新操作写到changeBuffer中（下次读取该数据页的时候进行merege），并将changBuffer写的操作写到redoLog日志文件中。
• 写redoLog的优势：将更新时的随机写转化为了顺序写，写redolog是顺序写的，先写redolog等合适的时候再写磁盘，间接的将随机写变成了顺序写，性能确实会提高不少。
• redoLog刷盘&内存更新脏页：当redoLog写满、系统空闲、关机的时候，就会将redoLog中的数据刷盘，刷盘的时候会将redoLog记录的操作更新到磁盘中；当内存不够用，就会将**最近最少使用（LRU）**的数据页淘汰掉，如果淘汰的是脏页就要更新磁盘数据。
• 两阶段提交，将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

LRU&大表查询

大表的全表查询是将查询数据放到net buffer中，buffer满了就通过网络发送出去，所以不会因为查询大表造成OOM。
LRU
所有的查询读取的数据页被放在buffer pool中，通过LUR算法管理（链表形式实现）。
①当查询记录在buffer pool有，就将该数据页放到链首
②新插入的放在头，当有新查询需要加载数据页且buffer pool满了的时候就需要淘汰旧页，将链尾数据页淘汰，将新查询的数据页放到链头。
大表查询的问题
对于上述的LRU的实现在查询大表的时候会对缓存造成严重破坏，查询大表会将缓存中之前加载的热点数据页全部淘汰掉，造成其他查询缓存命中很低。
InnoDB的LRU改进
按5:3将buffer pool化分为yong区和old区，链表前5/8是yong区，后3/8是old区。数据插入时且buffer pool不够时，淘汰链尾的数据页，将新加载数据页放到LRU_add分界处，如果新插入数据页存在超过1秒，就重新放到链头，否则位置保持不变。这样就解决了大表查询对缓存的破坏。

删除操作

删除表中的数据通过drop table可以完全删除这个表（数据加结构），但是使用delete删除的时候是标记删除，将数据页上删除的记录标记为可复用。所以即使delete了记录也会有很多的空额空间，可以使用alter table A engine=InnoDB重建表，释放空洞的空间，缩容。其过程就是创建一个新表，将数据拷贝一遍，使空间紧凑。

join实现流程和优化

• 被驱动表可以使用索引：当表a（N行）和表b（M行） join的时候，选择一个小表作为驱动表，大表作为被驱动表，驱动表会全盘扫描，被驱动表可以走索引，所以大表根据索引查找就是走树索引（可以使用索引），比较快，扫描行数N*log2M。
• 当被驱动表不能使用索引：由于被驱动表不能使用索引，索引比较次数是M*N，当数据很大时尽量不要使用join。①可以通过应用层拆分成多个语句然后再拼接查询结果。②使用临时表：将被驱动表（大表）过滤后的数据放入临时表（当前连接有效），并加索引，然后再去进行join。

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

③hash join：mysql不支持，可以通过业务端实现。将t1表（驱动表）查出来存到一个hash结构中，获取表 t2 中满足条件的 2000 行数据。将这2000行拿去hash查找对应的t1表数据，拼成结果集返回。

InnoDB存储引擎（支持事务、行锁）

InnoDb数据页结构
操作系统每次读取数据按页读取，一页大小4kb；mysql一页的大小是16kb，当一行数据大于16kb就需要跨页存储
InnoDB的B+树索引

• 主键索引： InnoDB中的id通过B+数建立索引主键索引，b+树的每个节点是一个数据页（16K），非叶子节点存储的是主键+下一页的指针，叶子节点存储的是数据，一个页中可以存储多条数据，一个页中的数据通过以链表形式连接（以主键顺序）。当一页中的数据较多的时候，还会为链表再建立一个索引，加快一页中的查询速度。所有叶子节点都通过指针相连，所有当进行范围查询的时候，可以直接遍历叶子结点，速度很快。
  
• 一般两层的B+树可以存储20000条数据，三层B+树可以存储2000W条数据。假设id为int类型，非叶子节点中的一个(id,指针)对的大小就是(8+6)b，一页可以存储的指针对16 * 1024/(8+6)=1170个，每个指针对指向下层的一个叶子节点，假设一条数据大小是1kb，一页可以存储的数据大概是16条，所以一个两层的B+树能够存储的数据大概是1170*16=18724条。
• 联合索引：如果对多个字段建立一个联合索引，mysql会根据这几个字段顺序计算出对应的数值，根据这个数值建立一个B+树的索引，最底层叶子节点存储的是每个数值对应的id值，找到id之后再去主键索引中查找数据（回表）。回表需要连续访问两次索引，所以对于需要关联的字段可以创建联合索引，这样索引上有需要查询的字段就不会回表了。只有当全值匹配或者前缀匹配的时候才会走索引，缺失前缀不会走索引（最左前缀原则）
  

B+树索引与Hash索引的区别

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

从上面的图来看，B+树索引和哈希索引的明显区别是：

• 如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
• 从示意图中也能看到，如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
• 同理，哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；
• 哈希索引也不支持多列联合索引的最左匹配规则；
• B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

char和varchar的区别

• VarChar：变长字符串，占用空间相对较少，会额外消耗1或2个字节存储字符串长度。
  缺点：update的时候操作比较麻烦，当update后需要占用额外空间，并且当前页没有额外空间的话，InnoDB引擎就会分页。
  适合场景：
  字符串列的最大长度比平均长度大很多
  列的更新很少，所以碎片不是问题
  使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储

• Char：定长字符串，MySQL总是根据定义的字符串长度分配足够的空间
  适合场景：
  适合存储手机号、身份证号、Md5等定长类型的字段

普通索引与唯一索引的选择

• 查询效率:两者在查询的速度上相差无几。普通索引在查询的时候变了树找到对应key之后还要继续向后遍历找寻所以相同key的记录。但数据存储和读取是基于数据页的，所以内存中操作效率比较快，两者查询效率相差无几。
• update效率:对应普通索引来说，update操作可以先将更新操作写到buffer change中，之后读取对应数据的时候再统一更新；但是对于唯一索引来说，更新操作由于要判断索引是否冲突，所以需要将对应数据页全部加载到内存，就没有必要使用buffer change。所以对于数据量比较大，且多写少读的表使用buffer change对效率提升比较大。buffer change可以通过参数innodb_change_buffer_max_size 来动态设置，大小表示占buffer pool的百分比。
• change buffer：change buffer是将更新操作记录在内存中，当下次查询该数据页的时候，就将查出来的数据和change buffer的操作进行一次merege，完成更新操作，这样减少了磁盘的IO。change buffer虽然是保存在内存中的，但是断电或者宕机不会丢失，因为change buffer在事务提交的时候会写入redoLog中。

MySQL选错索引索引

在执行SQL语句的时候，优化器会根据语句执行时扫描行数、是否排序、是否使用临时表等因素选择最好的索引。但有时候优化器会选错索引，这时候怎么解决呢？

• 如果你发现 explain 的结果预估的 rows 值跟实际情况差距比较大，可以使用analyze table t命令来重新统计索引信息。如果只是索引统计不准确，通过 analyze 命令可以解决很多问题，但是前面我们说了，优化器可不止是看扫描行数。
• 通过force index(a)强制走指定的索引

索引失效

• 使用了函数计算：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
  mysql> select count(*) from tradelog where month(t_modified)=7我们可以把 SQL 语句改成基于字段本身的范围查询，这样就可以用上 t_modified 索引的快速定位能力了。
  mysql> select count(*) from tradelog where (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

• 对于不改变有序性的函数，优化器也会放弃索引。比如，对于 select * from tradelog where id + 1 = 10000 这个 SQL 语句，这个加 1 操作并不会改变有序性，但是 MySQL 优化器还是不能用 id 索引快速定位到 9999 这一行。所以，需要你在写 SQL 语句的时候，手动改写成 where id = 10000 -1 才可以。

• 多个字段的联合索引：没有遵循最左前缀原则索引会失效

• 不等(!=,<>)，null判断(is null,is not null)会导致索引失效

• 隐式转换：触发隐式转换会放弃索引。select * from tradelog where tradeid=110717;这个sql的tradeid是varchar(32)类型且有索引，但上述SQL发生隐式转换等价于select * from tradelog where CAST(tradid AS signed int) = 110717;，所以放弃了索引。

事务及锁原理

事务四大特性：原子性、隔离性、一致性、持久性
隔离级别：读未提交、读已提交、可重复读

• 读未提交（一般不会使用）：可以读取事务未提交的数据，会产生脏读。
• 读已提交：一个事务只能读到另一个已经提交的事务修改的数据，并且其他事务对该数据进行一次修改并提交后，该事务都能查询到最新的值。不会出现脏读，会出现不可重复读、幻读。
• 可重复读（mysql默认隔离级别）：一个事务第一次读取过某一条数据后，即使其他事务修改了该数据的值并提交，该事务之后再读该数据时，读到的任是第一次读到的值，而不是每次都读到不同的数据（既每个事务之间是隔离的）。不会出现脏读和不可重复读，但会出现幻读。
• 串行化：加锁，让每一次写操作都是串行的，不会出现脏读、幻读（对多行数据而言）、不可重复读（对一行数据前后两次读取不一样），会影响性能。

事务隔离的原理
每个事务都有一个事务号（transactionID），对一条数据的修改会形成一条版本链存储在undolog日志中，回滚指针指向上一条被事务修改的数据。读已提交和可重复读通过版本链和readview实现（MVCC—多版本并发控制）。

• 读已提交原理：mysql内部有一个readview保存活跃的事务id，读取某一条数据的时候只能够读取自己版本号修改的数据，或者是不在readview中的不活跃事务修改的数据（当有事务提交就会把readview中自己的事务id去掉）。因为每次select都会更新一下readview，所有可能两次select中有事务提交了，那么下一次select就会看到提交了的事务的修改（不可重复读）。
  
• 可重复读原理：读取某一条数据还是只能读取自己版本号修改的数据，或者是已提交事务修改的数据（数据trx_id不在readview中的数据），但是可重复读的隔离级别下，不会每次select都更新readview，所有不会出现不可重复读现象。
• 对于更新数据：更新数据（update语句）都是先读后写的，而这个读，只能读当前的值，称为“当前读”，忽略readview的版本问题，直接读取当前值进行更新。除了 update 语句外，select 语句如果加锁（lock in share mode），也是当前读

锁

• 读锁：共享锁（不阻塞其他事务获取读锁，但不能获取写锁）
  写锁：排它锁（其他事务不能获取读锁和写锁）
  select：不加锁（select操作不需要获取锁，不会被读锁、写锁阻塞）
• 读操作：对于普通的select语句，InnDB不会加任何锁
• select…lock in share mode（当前读）：将查找到的数据加上一个读锁，允许其他事务继续获取这些记录的读锁，不能回去这些记录的写锁（会阻塞）
• select…for update：将查找到的数据加上一个写锁，不允许其他事务获取这些记录的写锁和读锁。
• 写操作：DELETE、INSERT、UPDATE操作都会加写锁

事务提交时，释放锁，所以为了提高效率，你可以将最可能产生锁冲突的锁往最后放，这样锁住的时间就比较小，对并发影响较小。
在读已提交的隔离级别下，只对查询出来的行加锁（行锁）
可重复读的隔离级别下，通过加间隙锁防止了在写（update）的情况下产生幻读，利用MVCC防止了读（select）的时候产生幻读。当不走索引的时候，会对整个表加锁，其他事务无法修改，防止幻读。

锁的类型

• 共享锁：表级共享锁、行级共享锁，共享锁之间不互斥
• 排他锁：表级排他锁、表级共享锁，排它锁之间、排它锁和共享锁之间互斥
• 意向锁：InnoDB自动加的锁，目的是为了解决加表锁的效率问题。当对一行加排它锁或者共享锁的时候，InnoDB会自动为这个表加上意向排他锁或者意向共享锁。
  当要对一个表加表锁的时候，先判断是否有表锁，再判断是否有意向锁。如果有意向写锁，就表明有行锁，表锁需要阻塞。有了意向锁就不用遍历全表查询是否有行级写锁了，增加了表锁的加锁效率。
  

加锁时机

1. insert时全表锁，update是行级锁(非绝对-成功使用索引时锁行，否则锁表)，select不加写锁，但可以手动加锁
2. InnoDB行锁是通过给索引上的索引项加锁来实现的，innodb一般情况下走索引或者主键更新都是锁行，其余都是锁表
3. InnoDB加锁 锁的是表或者索引，当走索引命中的时候就是锁的索引，当不走索引时就会锁全表

死锁
我们知道数据库的行锁在使用到的时候才会对某一行加锁，当事务提交的时候才释放锁。所以相互等待可能造成死锁。
解决死锁的策略

• 直接进入等待，直到等待超时，超时时间设置innodb_lock_wait_timeout
• 死锁检查：发现死锁后，主动回滚死锁链中某一个事务，让其他事务继续执行。将参数innodb_deadlock_detect设置为no

加锁规则

• 全局锁：通过语句Flush tables with read lock对整个数据库加锁，整个数据库处于只读状态，一般在整库备份的时候使用。客户端断开连接自动释放锁。

• 表级锁：①表锁，lock tables … read/write显示加锁，unlock tables释放锁，客户端断开连接也会释放锁。②元数据锁（MDL）：当在访问一个表的时候会自动上锁。当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。读锁与读锁不互斥，写锁与读锁 读锁与写锁互斥。在事务提交的时候释放锁。

• 

• 行锁：行锁是在引擎层由各个引擎自己实现的，但不是所有的引擎都支持行锁。在增删改某一行记录的时候会对该行甚至访问的行上行锁。行锁是在需要上锁的时候才上，在事务提交的时候释放。如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

• 间隙锁：为了解决幻读，InnoDB引入了间隙锁，锁的范围就是两值之间的空隙（只在可重复读下有间隙锁）。跟间隙锁冲突的是向间隙中插入数据，间隙锁和间隙锁之间不冲突。
  间隙锁和行锁合称next-key lock，是前闭后开的区间，通过间隙锁和MVCC解决了幻读问题。
  间隙锁的引入会导致同样的语句锁住的范围更大，会影响并发度。对于业务不要求可重复读的情况下，可以开启读已提交+binglog的binlog_format=row模式，避免间隙锁提高效率
  间隙锁导致的死锁：
  对于下表，如果用select * from t for update要把整个表所有记录锁起来，就形成了7个next-key lock，分别是(-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。
  1、sessionA执行select … for update语句，由于id=9这一行并不存在，因此会加上间隙锁(5,10)
  2、sessionB执行select … for update语句，同样会加上间隙锁(5,10)，间隙锁之间不会冲突
  3、sessionB试图插入一行(9,9,9)，被sessionA的间隙锁挡住了，只好进入等待
  4、sessionA试图插入一行(9,9,9)，被sessionB的间隙锁挡住了
  两个session进入互相等待状态，形成了死锁。

• next-key lock加锁规则：
  ①原则1：加锁的基本单位是next-key lock，next-key lock是前开后闭区间。
  ②原则2：查找过程中访问到的对象才会加锁。
  ③优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
  ④索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。

3范式

1NF: 即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只有数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF

2NF: 表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现，既所有属性依赖于主键，不允许存在部分属性依赖主键

3NF: 即表中不要有冗余数据, 就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放. 比如下面的设计就是不满足3NF