Mysql复习

本文主要是介绍Mysql复习，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、一条sql执行的步骤

1、连接mysql服务端

先在程序中加入mysql驱动依赖（用于连接mysql）

注意：此时在客户端和mysql服务端，都存在各自的数据库连接池，主要用来限制和管理连接。客户端的连接池常见有DBCP、C3P0、Druid。

2、mysql接收sql语句，并进行解析

客户端通过连接发送sql语句到mysql，mysql有专门的线程监听sql请求，然后将sql传给SQL接口，由于此时这些语句mysql不能进行识别，所以需要sql解析器将sql语句进行解析。

3、选择最优执行计划

解析完后查询优化器会生成所有执行路径（执行计划），然后选择出一条最优路径。

4、由执行器调用存储引擎进行执行执行计划

此时执行器会调用存储引擎去执行相应的执行计划（由存储引擎去调用内存、磁盘中的数据）

在mysql中，sql接口、sql解析器、查询优化器都是通用的，只有存储引擎是不一样的。最常用的存储引擎就是Innodb。

二、了解Innodb中的Buffer pool，以及redo log(重做)、undo log（回滚）、binlog等日志的作用

以一条sql为例：update  user set  name='xxxx'  where id = 10

1、Buffer pool缓冲区

进行数据的修改时会对缓冲区中的数据进行加独占锁，防止其他线程干扰。

（多个线程访问同一个buffer pool时需要进行加锁（对缓存页、各种链表等资源进行加锁），等一个线程执行完成后释放锁资源后下一个线程才能使用）

默认128M，通过innodb_buffer_pool_size配置其大小。

对于标记某个页用（space，page_no）其中space表示表空间id，page_no表示页号。

Innodb可以设置多个buffer pool，而每个Buffer Pool由多个chunk（默认128M，可以通过innodb_buffer_pool_chunk_size 来设置，可以通过chunk来动态的扩容单个buffe pool 的大小）。

1、Buffer pool的内部结构

Buffer pool中存在多个缓冲页（默认16k）+多个描述数据（用来描述对应缓冲页的）。

在Buffer pool中维护着几个链表：free链表（空缓存页链表）、flush链表（记录被修改过的脏缓冲页）、lru链表（管理已用的缓冲页）。所以lru链表是包含flush链表的。

1）、free链表

管理空的缓存页，需要申请缓存页时会去free链表取。（该链表有个基础节点专门用来记录free链的页数量等数据）。

我们进行sql操作时，如果需要某个记录，此时先会从buffer pool查看是否有缓存，mysql会维护一个hash表（key：表空间+数据页号，value：缓存页地址），此时根据这个hash结构进行查询是否有（此时只有一些行数据，怎么定位到他是哪个表空间、数据页号？）。如没有再去磁盘加载数据页（磁盘中用数据页进行存储）。

2）、flush链表

管理已修改的缓存页节点，同样有个基础节点记录该链表的信息。每次刷盘都是将flush链表的缓存页刷到磁盘中。

3）、lru链表

管理已用的缓存页，如果free没空缓存页了，此时就会淘汰lru（最近最少使用）的尾部的部分缓存页。

那么此时进行淘汰的？
什么节点放在链表头，什么节点放在链表尾？mysql就是基于lru算法（最近最少使用）进行改造的。下面说说改造的部分：

（1）、mysql存在预读机制，如果某个时机触发了预读机制，即本来现在我需要读数据页1，而预读机制把数据页2、3都读进来了，如果是传统lru那123都会放到链表头，但23却是没用的。还有如果进行全表扫描，我们需要的数据可能是部分，却把整个表的数据都加载进来了。（简单讲就是加载了一些没用的数据页）

此时mysql对lru设置了冷热区域，冷占37%（可以参数控制），以后每次新加载的缓存页都放在冷链头部，然后如果一个缓存页在被加载的1s（可以参数设置）之后还有访问则放到热数据的头部（即lru头部）。如果热数据被访问是直接调到lru前面？答案是否定的（热链数据多频繁调动消耗内存），mysql中热链后3/4被调用才会到lru链头，前1/4不会动。

上面我们将lru链的节点如何合理分配说明了，那么对lru尾部进行刷盘操作存在两个触发机制：1、mysql会有一个定时任务，会定时进行刷盘。2、如果free链没有空页且要申请页时会触发

---

1、数据预热：可以先用select+使用索引预加载数据到buffer pool。

5.6开始可以dump出buffer pool的数据（space+pageNo），重启后load这些数据。

mysql后台有很多线程例如日志线程、读写线程、刷脏页线程、dump线程等。当然可以调整这些线程的个数，例如调整写线程的个数。

---

checkPoint：用于刷盘脏页、缩短恢复时间。（缓冲池不够时会将脏页刷新到磁盘）

每个页有LSN，整个mysql实例也有一个全局LSN（也就是checkPoint，该lsn也是所有lsn最大的值），页更新时其页lsn也会更新。数据库宕机恢复是会读取全局lsn去日志中恢复数据（恢复lsn之前的数据）。注意：所有lsn都会被记录到redo log重做日志中。

在flush list中（管理脏页，即已使用的页）只存页第一次修改时的lsn，页刷到磁盘时，全局lsn才会被记录到redo log中。（redo宕机后重启的数据恢复原理）

        脏页的刷盘机制中，我们之前提过free list满时会将lru list末尾页刷进磁盘；重做日志不够用时，从flush 列表中选择一些页，强制刷盘；Master Thread以每秒或每十秒的速度从缓冲池的脏页列表（flush list）中刷新一定比例的页回磁盘。这个过程是异步的，不会阻塞查询线程。（脏页是同时存在flush、lru上的，有时从lru上刷盘，有时从flush上刷盘，大部分在flush上）

---

doublewrite机制：即一个页被写了两次，防止磁盘中页损坏不能恢复。

就是在内存中有一个doublewrite buffer（非mysql内），刷盘脏页时先拷贝一份到doblewrite buffer中，然后等将脏页写入磁盘后再则可以删除doublewrite buffer的数据。

2、undo log 回滚日志

内部结构、触发时机、作用

作用：保证数据的原子性，保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

---

用于事务回滚时，如果执行了一个insert语句，此时你在日志里就会记录insert语句的一些信息。如果需要回滚则执行该insert语句的逆向恢复delete操作即可。select不用。

每条日志的结构：

 如果要回滚，则拿着这条日志对某表的某些字段值进行回滚操作。

3、redo log 重做日志

1、作用及概述

记录对数据的修改、保证事务提交后修改的数据不会丢失、用于事务前滚恢复。

防止宕机等情况下的数据丢失。其中在Innodb中有个Redo log Buffer用来暂时存放redo日志信息等（后续将日志信息刷到磁盘）。所以一些修改操作都会写到redo log中。

作用：确保事务的持久性。redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

mysql重启时，会根据redo log恢复Buffer pool里的数据。

2、日志

redo 的日志大概格式：对xx表空间中的xxx数据页中的偏移量为xxx的地方更新了xxx数据。

（表空间号+数据页号+偏移量+修改几个字节的值+具体的值）

redo日志数据并不是一条一条的写入日志文件的，在mysql中用一个数据结构redo log block存放多行日志，刷盘的基本单位是以block为单位的（512k）。

redo log buffer就是这些日志行数据、block存在的内存缓存区（innodb中），redo log buffer默认16M（可参数调整）

---

redo刷盘时机：

那么redo log Buffer的日志信息缓冲什么时候进行刷盘操作的？

在刷盘过程中，一步用户空间缓冲区的数据不会直接刷新到磁盘中，而会经过操作系统缓冲区（OS Buffer），在由OS缓冲区调用fsync()将其刷进磁盘中的文件，

1）、mysql是通过innodb_flush_log_at_trx_commit参数来决定刷盘时机

当该参数为0（延迟写），每1s将redo log Buffer的数据刷到OS cache中，然后OS会即时的刷到磁盘中。为1（实时写）时每次提交事务都会即时的刷进磁盘。为2时（延时刷）每次事务提交都刷进os cache中，然后每隔1s将os cache的数据刷到磁盘中。

注意：OS刷到磁盘是花费时间比较长的，所以延迟刷的效率是高于实时写（0丢失，但写入性能低），低于延迟写（性能高但丢失数据多）。但默认使用的是实时写1。

延迟写，mysql奔溃而操作系统没崩溃时，会丢失1s数据。而延时刷，在这种情况下是不会丢失数据的。

2）、redo log buffer日志数据占了一半以上，就会触发刷盘。

3）、mysql关闭时

4）、后台线程每隔1s进行定时刷盘

---

磁盘中log文件个数？
默认情况下目录就只有连两个文件：ib_logfile0和ib_logfile1,每个48MB。写满第一个就写第二个，第二个写满了就覆盖第一个的内容。

---

注意：redo log、undo、binlog都是在事务提交成功前就操作完的（日志数据可能存在内存、os cache、磁盘中）

4、binlog

做一些归档的日志，注意用于主从复制。

作用：用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。用于数据库的基于时间点的还原。

我们同样来看看binlog何时进行刷盘？
mysql也存在一个sync_binlog来决定binlog日志的刷盘策略，为0时，实时写进OS Cache，然后每段时间（可能是1s）刷新到磁盘；为1时，事务提交前实时刷到磁盘；当为N时，表示每提交N个事务才会写入磁盘。5.7后默认为1。

三、事务

事务原子性：redo保证，持久性：redo+undo，一致性：undo，唯一性：锁机制

---

要注意一个事务真正提交之前是要完成redo、binlog、undo等日志操作。

那么事务提交的真正时机是什么时候（事务提交成功标志）？

那么在上面redo 、bionlog日志都刷盘完了（也许还在OS中），此时会将binlog文件名+这次更新的文件Offset（偏移量）记录到redo log日志，并最后加上Commit标志。至此才叫一个事务的真正提交成功。如果没加commit标识也算没提交成功。

既然基于在redo记录binlog偏移量+commit标志代表事务提交成功，那么我们下一步需要知道的是这个有什么用？事务回滚原理是怎么实现的（基于undo？还是redo+binlog）？事务提交后mysql宕机，新数据没刷到磁盘，此时怎么恢复数据的？

---

mysql事务存在的问题

1、脏写

事务A改了c值，B也改了c值，然后A回滚c值（AB都没提交），导致把B的修改也回滚了。

（就是把别人的修改值回滚没了）

2、脏读

A去改c值，B去读c，然后A回滚，导致B读到的是脏数据。（读了回滚前的脏数据）

3、不可重复读（值被删除或者修改，导致前后读到的不同）（此时是已解决读未提交的）

A先读c值，B去修改c值后提交，A再次去读取时发现c值变了（读了提交前后的数据）

4、幻读（值增加了，导致前后读到的不同）

A查询一批数据，一开始是10条，过段时间再查询，发现是12条了。（多读出记录来）

---

mysql使用事务隔离级别解决

RU解决脏写（读未提交）

RC解决脏读（读已提交）

RR解决不可重复读和部分情况下的幻读（可重复读）

serializable解决所有幻读（串行化）

不可重复读重点在于update和delete，而幻读的重点在于insert

---

RR、RC级别的实现原理？RR能解决幻读？

说原理前需要先了解undo log版本链、ReadView机制、MVCC机制

1、undo log版本链

我们缓存页的每条数据存在两个隐藏字段，一个是trx_id，一个是roll_pointer。其中trx_id是最近一次更新这条数据的事务id，roll_point指向更新这个事务之前的undo log节点。

而利用roll_point字段就可以形成一条undo log版本链（该版本链节点信息包含：值、事务id、指针）

2、基于undo log版本链实现的ReadView机制

简单讲就是你执行一个事务的时候，会给你生成一个ReadView，里面有4个东西比较关键：

1）、m_ids，记录在mysql中哪些事务还没提交

2）、min_trx_id，m_ids中的最小值

3）、max_tri_id，就是mysql下一个要生成的事务id，就是最大id（我猜mysql生成的事务id是呈增加趋势的）

4）、creator_trx_id，当前事务id。
然后事务访问该记录时，会根据这四个参数去判断要去使用版本链的哪个版本节点（具体可以看我文章）。

总结：1）、自己开启的事务，不能读取比自己早开启的事务但没提交修改的值
            2）、不能读取比自己晚开启的事务修改的值（如果晚开启的事务提交了且m_ids没有则可以读取）

（即不能读取比自己先开启/后开启的事务修改的值）
通过ReadView（用这些数据来判断undo log多版本链的事务id是发生在本身事务的前还是后，事务提交了没等情况）和undo log多版本链来控制实现，这样就只能读到事务开启前已经固定的数据了
注意这里的ReadView是事务开启时就生成的，在事务期间其他事务提交或开启了是不会进行实时更新的，此时的ReadView是旧的数据。

3、RC级别的实现原理

要达到的效果只会读到已提交事务的值，这样就能解决脏读。

RC利用undo log版本链+每次操作时更新ReadView（这样其他提交了的事务修改信息就会实时更新）的机制去实现读已提交。

MVCC机制就是基于ReadView机制+undo log多版本链条实现的

4、RR级别的实现原理

RR级别是可重复读的、且一些情况下解决了幻读

1）、RR解决不可重复读问题

就是不要读到其他已提交事务修改的值。

此时只要在第一次操作的时候生成readView，之后操作都不更新readView，这样其他已提交事务更新的值就不会被读取到了。

在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。

READ COMMITTED —— 每次读取数据前都生成一个ReadView

REPEATABLE READ —— 在第一次读取数据时生成一个ReadView
2）、基于undo log版本链+readview的MVCC机制、锁分别保证某些情况下的幻读。

---

事务可以进行组提交（多个事务一起提交）

mysql除了支持本地事务，还支持分布式事务XA（开启——xa start ‘事务名’；结束——xa end ‘事务名’；提交——xa prepare ‘事务名’；回滚——xa rollback ‘事务名’）

本地事务：开启——begin；提交——commit；rollback——回滚。

四、事务提交后，该将Buffer pool中的新数据刷到磁盘了

事务都提交完了，最后我是不是需要将Buffer pool中的新数据刷到磁盘中。mysql会有一个IO线程在某个时间去进行异步刷盘操作。（没刷新mysql就宕机了，此时可以根据前面的redo日志进行恢复）

五、磁盘中如何存放数据的？

1、数据页

默认16k，mysql的数据是一行一行的往数据页中存储的，一个页不够了则会存到下一个页（溢出页）（这也是行溢出现象）。

一个数据页中包含：多个行数据、文件头、数据页头、最小记录、最大记录、数据行数、空闲空间、数据页目录、文件尾部等。

可以对页进行压缩，压缩完的页对应的表叫做压缩表。

一般size>页大小的一半，则触发行溢出现象。超过8k则会将超出的部分存到其他页（而不是16k放满再存到其他页）

2、表空间

表空间就是我们创建的那一个个表，在物理上对应各个表名.ibd磁盘文件。一个表空间有多个数据页。

3、数据区

一个数据区有64个连续的数据页。256个数据区又被划分为一个组。

每个数据页中由多个行数据构成，页中有一个行格式（存放行数据的存储格式）

4、行数据格式

每行的数据存放格式：

变长字段的长度列表，null值列表，数据头，隐藏字段，column01的值，column02的值，column03的值........

变长字段列表：用来记录那些可变字段 

null列表：记录为null的字段 

数据头：记录delete_mask(标记该行数据是否被删除，所以我们删除一行数据时不是立即从磁盘中清除的)、数据类型、下一条数据的指针等信息

 注意：除了上面的几个列表，mysql还会自己新增一些隐藏字段（例如DB_ROW_ID字段，没主键时自定义为主键；db_trx_id:事务id，表示哪个事务更新的；db_roll_ptr:回滚指针，用来指针回滚的）

---

mysql对于数据页是随机读写，对redo、undo、binlog等日志是顺序读写。

十、两个mysql的生产案例

aa

六、锁机制

1、latch锁（轻量级锁）：（用来控制内存中数据结构、临界资源的并发访问，即类似我们java中的锁，锁住的对象是临界区）（latch锁的加锁释放锁过程是很快的，毫秒级别的，基本可以忽略latch锁）

2、 lock锁：（锁的是数据库层面的锁，innodb的锁住的对象是行，保护的是数据库中的内容） lock锁的持续时间是整个事务，如果事务没被提交，会一直持有。

其实两种锁的应用层面是不同的，latch我们可以理解为java中我们对某个对象的并发安全控制，而lock是对于行数据的保护。

lock锁中存在多种类型锁

show engine innodb status可以查看mysql中的锁情况

1）X锁（排它锁）

对某行进行增删改时需要对该行记录加排他锁。select要加上排他锁可以在语句最后加上for update，不过此时如果其他会话也是可以通过mvcc机制读这条记录的。

2）S锁（共享锁）

加上共享锁：sql后追加lock in share mode。

在页中会维护一个bitmap（位图减少内存），这个位图的位置对应各行数据，位图某位为1时表示这行数据被加锁了。

3）、意向锁

 意向锁都是表级别的。innodb不仅仅有行锁，还有意向锁等表锁。

如果我们要像表a的第n行加一个x锁，此时第一步就是要对表a加一个IX锁，然后在对行n加X锁。S和IS类似。为什么之前说意向锁是不互斥的，就是因为意向锁顾名思义就是我之后有要加某个锁的意向，即先预先说明下我下面可能要加X锁或者S锁。

意向锁和行锁中：只要意向共享锁+共享锁、意向独占锁+独占锁（互斥锁）是不互斥的。

行锁中：只要共享锁+共享锁不互斥。

加锁是需要进行层级加锁的，先对表加锁、然后是行数据。

4）、自增锁

对于自增列的并发处理的，其实实际上就是latch锁，执行完语句就释放，不会因为事务是否提交才释放锁。

---

锁的算法

锁的算法分为：记录锁（锁单个行记录，Record Lock）、范围锁（锁某个范围，不包含本身，Gap Lock）、范围+记录锁（Next-key Lock）。

Next-key Lock锁和前面两个锁算法不兼容

RR事务隔离级别用的锁算法大部分使用next-key锁算法（范围+记录），这个算法有一个问题就是插入的性能优点慢。有小部分情况下会进行优化使用record锁（语句使用的索引是唯一索引，且只返回一条记录的情况下）。

RC事务级别的锁大部分是记录锁。

---

RR级别下通过next-key锁算法解决部分情况下的幻读？幻读是其他事务进行插入导致的（例如本来只有一个6，一段时间后发现有2个6了）。

next-key算法会锁住记录本身+前一个记录+后一个记录。例如现在4、6、8、10。现在插入一个6的记录，那么（4，6]、（6，8）就会被加锁，而如果在页中有6的记录了，新的6如果插入会放在原6后面。这时[6,8)这个区间已经被锁了（没有6则锁的是前面的记录），其他线程就不会插入导致幻读现象。

上面的情况是基于索引的，没索引时为了防止幻读此时会锁住整个表。

---

1. RR隔离级别，如果事务中都是快照读，或者全都是当前读，都不会产生幻读。只有当前读和快照读混用，才会产生幻读。（一下读MVCC快照的数据，一下读当前数据，这样就无法保证了）
2. MVCC保证快照读不会幻读（如果没做修改操作，都是去读MVCC的快照，如果只读快照就只有一份数据所以不会出现幻读现象）
3. next-key lock（间隙锁）保证当前读（读取当前最新的数据）不会产生幻读（间隙锁在事务期间锁住一个范围，导致其他事务无法插入就不会出现幻读）

---

死锁

解决死锁：1、配置超时  --innodb_lock_timeout 超过时间就自动放弃。2、自动死锁检测：wait-for graph。（会根据锁的信息链+事务等待链推断出是不是存在死锁回路。如果存在则回滚某个事务（通过undo的量来判断回滚的事务，回滚量比较少的））

 show engine innodb status：只会显示最后一次死锁的信息。如果要记录所有死锁信息：开启参数innodb_print_all_deadlocks 。

七、索引及其查询原理

前置知识：

1、数据页

在磁盘文件中，存在多个数据页，而每个数据页都有两个指针，分别指向上一个、下一个数据页的物理地址。而数据页内部有多个数据行，每个行数据也有一个指针指向下一个数据行。

而且每个数据页中又会维护一个页目录，这个目录记录着行数据的主键、主键对应的槽位位置。（槽位放着各个数据行）我们如果查询数据时如果定位到某个数据页时，就会根据这个页目录根据主键进行二分查找，然后快速定位到对应槽位。（非主键会经过一次回表后找到其主键）

2、页分裂

一个页不够了则需要再弄一个页，而数据页中的索引是递增有序的，如果此时页A满了且其最后的主键为10，现在要插入主键为8的数据，此时8小于10应该插到页A的，而不是插入到新的页B。这时就出现了页分裂问题。

此时mysql为了解决页分裂，就将8放到页A中，然后溢出的行数据就放到新的页B。

---

主键索引

除了上面说的每个页维护一个页目录（主键：槽位），mysql还对各个页也维护一个主键目录（每个页中最小主键：页号，页号会指向相应的页地址）。

那么我们现在有一个主键6，先回去主键目录定位到属于哪个页，然后在进入页中通过页目录快速定位到数据槽位。

那么此时又有一个问题，如果有10000个页，那此时主键目录不是很长，那此时一个主键例如9669进行，岂不是要进行全部扫描了？

mysql中会将这些目录存进数据页中，该页被称为索引页。一个索引页不够就存多个索引页。

索引页太多也会查询过慢，所以索引页以B+树的形式依次排序。

（注意叶子节点是双向链表），而主键索引树的叶子节点直接存储数据也被称为聚集索引。

非主键索引树的叶子节点存放的是非主键字段（可能也是组合索引字段）+主键+指针。

注意：主键或非主键索引链表都是有序的

---

二级索引（辅助索引）

在二级索引中，叶子节点的索引页（数据页）存放的是主键+name数据。

如果我们此时根据name来查找数据，此时就会先在name对应的B+树找到相应的主键，然后拿着这个主键再去主键的索引树找到相应的真实数据。（这个过程就叫回表）

---

索引如何建立？sql如何尽量使用索引？

1、索引不是越多越好，索引、索引树维护起来是比较耗性能的，数据量少时用索引可能性能低于全表扫描。

2、where后面一般要用=进行等值匹配。且联合索引注意字段名称、顺序（即要符合最左原则）。

3、最左前缀匹配。用like ‘aa%’ 不用 like  ‘%aa’。

4、范围查找第一个用的到索引，后面的用不到。class_name>'1班'  and class_name<'5班'

（第一个走索引，第二个不走）

5、order by、group by 可以利用建索引，利用索引的排序性质使得性能提高。（没用索引就会走全表扫描、临时表等耗时操作）

6、尽量不要对字段基数低的字段建索引（例如这个字段只要0、1，如果建了索引那么二分查找的效率就和全表扫描差不多了）

尽量选择字段类型比较小的字段设计索引，例如tinyint字段类型小。搜索的性能小。如果必须要用大类型，例如varchar（255）此时可以用前20做为索引即可。

尽量不要用函数、计算等操作，否则大部分情况下用不到索引的。

尽量使用索引覆盖（不用进行回表）

八、执行计划+explain+sql优化

如果有表a、b，此时mysql会优先选择数据较大、有用到索引的表为内表（当然也不是一定，因为mysql会根据数据各种情况计算成本的），数据量小、没索引的为外表。此时比较次数会较少。

explain用来显示sql语句的执行计划。

其中select_type、type、key、ref、extra是比较重要的指标

1、看ref建立索引或者修改sql

const（唯一索引）、ref（非唯一索引）、range（范围索引）性能较高（此时都是走正常索引的）；index性能较差（尽量避免，没走正常索引，但用到了二级索引的叶子节点数据，此时遍历二级索引的所有叶子节点）；all全表扫描，性能最差，需要优化; 存在多个二级索引树先筛选再求并集/交集，然后进行回表操作（这样可以尽可能的提升性能）

2、看key使用了哪些索引，看是否需要优化

3、看Extra看是否进行排序、分组操作

filesort：表示进行了排序，此时会用到排序内存，可以适当配置排序内存进行优化

temporary：分组时就常使用临时表，此时可以调整临时表的默认内存大小

上面的排序内存、临时表等如果内存大小不够用，就会借用磁盘进行使用，那么就会降低性能。

九、主从、高可用

1、普通主从

slave从机只做备份，不提供读取，主机提供读写

‘2、读写分离的主从

master只做写，slave只做读。

3、主从之间的数据同步原理

mysql做增删改操作时会记录到binlog日志，从库会有个IO线程和主库做TCP连接，master一个IO dump线程通过tcp连接将binlog日志传给从库IO线程，然后从库将读取到的日志写入本地的relay日志文件，最后从库有一个sql线程回去读取relay日志内容进行日志重做。

主库IO dump线程——（通过TCP连接传送binlog日志信息）——》从库IO线程——（写入本地的relay日志文件）——》从库sql线程进行日志重做。

4、数据延迟问题？

主机宕机，有些数据还没到从库，此时如何解决？
此时我们需要尽可能缩短延迟时间

1）、从库并行多线程复制数据

从mysql5.7开始就支持并行复制，可以在从库中设置slave_parallel_workers > 0, 然后把slave_parallel_type设置为logical_clock。这样就设置成功了。

2）、半同步

默认主从的数据传输是异步的，可以配置为半同步模式。

半同步：当主库每提交一个事务后，不会立即返回，而是等待其中一个从库接收到Binlog并成功写入Relay-log中才返回客户端，所以这样就保证了一个事务至少有两份日志，一份保存在主库的Binlog，另一份保存在其中一个从库的Relay-log中，从而保证了数据的安全性和一致性。

另外，在半同步复制时，如果主库的一个事务提交成功了，在推送到从库的过程当中，从库宕机了或网络故障，导致从库并没有接收到这个事务的Binlog，此时主库会等待一段时间（这个时间由rpl_semi_sync_master_timeout的毫秒数决定），如果这个时间过后还无法推送到从库，那MySQL会自动从半同步复制切换为异步复制，当从库恢复正常连接到主库后，主库又会自动切换回半同步复制。

半同步复制的“半”体现在，虽然主从库的Binlog是同步的，但主库不会等待从库执行完Relay-log后才返回，而是确认从库接收到Binlog，达到主从Binlog同步的目的后就返回了，所以从库的数据对于主库来说还是有延时的，这个延时就是从库执行Relay-log的时间。所以只能称为半同步。

需要在主从机上配插件进行配置。

3）、使用shading-jdbc、mycat等中间件配置同一连接内数据都从主库查询，这样数据就会实时更新。

5、高可用

6、分库分表

十、sql编程

1、service mysqld start开启mysql

创建、删除一个数据库：create database/schema  test;     drop  database/schema  tets；

一个数据库对应一个数据库实例（单进程），一个数据库对应datadir参数的目录下的一个文件夹，每张表又对应一组文件（5.7对应三个文件，8.0对应一个文件）。

查看表定义信息：show create  表名

2、各种日志（错误日志、慢查询日志）

1）、错误日志地址：查看log_error参数。启动mysql或者运行过程如果发生错误利用错误日志文件进行排查。

2）、慢查询日志地址：超过某个时间阈值的sql语句记录到文件。（默认名：机器名-slow.log，名字可以参数配置；阈值可以参数配置，默认10秒；地址可以查询slow_query_log_file参数；5.7默认不开启），慢查询日志一般用来调优慢sql。

如果慢查询日志太大，此时要对没用了的信息进行删除，首先要对原文件进行备份，让后重写生成一个慢日志文件（flush slow logs）。

慢查询日志还可以配置没索引就被记录、扫描行数多就记录等配置。

也可以配置慢查询日志到表，不过比较消耗性能。

有一些情况下sql执行时间很长，但没有记录到慢查询日志中：
        并发情况下，会话1对某个数据进行for update操作，然后会话2要对这个数据进行更新操作，但是此时该数据已经被会话1进行加锁了，所以会话2只能等待。等到会话2执行完成后，即使会话2的sql执行很长也不会记录到慢查询日志中。

        慢查询日志的记录中会有query_time和lock_time两个信息，分别代表整个过程的时间、过程中数据被加锁了等待时间。而慢查询时间阈值是根据两者之差去记录的。

所以并发下的锁等待时间是不计入慢查询时间阈值的。

3）、通用日志

记录数据库的所有操作，默认名机器名.log （可以配置general_log参数），由于是将所有操作都记录，所以开启了的性能会明显下降。如果不是非必须场景一般不要开启。

总结：一般启动或运行错误看错误文件，性能问题看慢查询文件，审计等场景看通用文件。

---

一般对于符号类型建议使用有符号类型（Signed，默认就是有符号类型）。

数字类型对应的一些函数：
1）、floor（）：向下（小）取整。

2）、round（）：向上取整

3）、rand（）：随机获取0-1的值。

如果要去n-m的值：floor( n + rand() * (m-1) ) 例如1-100  ：select floor（1+rand（）*99）；

其中可以看到存储的类型有分字符、二进制的类别。例如varchar（10）表示可以存10个字符。而binary（10）表示存10个字节。

 md5（）：加密函数

 concat()：拼接字符串

length（）：查看有多少个字节 （括号里面填充的是字符串，如果不是会进行隐形转换）  charilength（）：查看多少个字符

upper、lower：转换为大小写

concat_ws():根据某字符进行连接：

repeat（）：产生重复的字符

lpad、rpad：左填充和右填充

字符串类型其实还有一种枚举类型————ENUM & SET。 可以起到约束的作用：例如性别约束为男女，插入其他的就会报错。

now（）：查看当前时间。now（6）：表示显示毫秒的长度（5.6开始支持）

 一般都是使用datetime或者timestamp这两种类型。这两个类型是有区别的：

unix_timestamp():表示某个时间到1970-1-1 的秒数（这会转换为int类型）。

所以有些人在设计时间是喜欢将时间字段设置为int类型，但一般不建议这么做，所以一般还是使用datetime或者timestamp（2038年会被用完），不过还是建议使用datetime。

不过timestamp是可以跨时区的，如果业务跨不同时区则可以用timestamp。timestamp会根据时区来相应调整时间。

分区表（不是分库分表）：即我们看到的数据库可能是一张表，但是mysql会将这张大表分解为多张小表进行存储。5.7只支持水平分区，即根据某个字段按照某种规则进行分区。注意分区字段要么是索引、要么是索引的一部分，所以说mysql是局部分区（例如上面的col3不是索引，所以创建分区表会报错）

 1nf：就是每张表都要有主键，且所有属性依赖于主键

2nf：没有部分依赖（即没有属性依赖于主键的一部分）

3nf：没有传递依赖。

临时表：

想要创建一个tmp临时表： create temporary table tmp (a int);

注意这种临时表和我们讲group by时的临时表（内存表，在sql执行期间mysql隐式创建的）是不一样的。这种临时表是手工创建的。创建完临时表后如果查看表是看不到这个表的，但是可以对该临时表进行插入等操作：

 这种临时表是会话级别的，其他会话是不会收到影响的。这种临时表的内容是存放在mysql的data目录下的一个临时表空间文件（ibtmp1），每次重启mysql会初始化清除掉临时表空间的数据。

---

select + 函数（可以使用后面from获取到的字段）/字段（从from后面获取到的字段）+from +表名+where +group by +having +order by +limit

1、order by

ASE:从大到到小，降序。默认

DESC：升序。

mysql会给排序分配一个单独的内存进行操作。可以查看sort_buffer_size，可以适当调整下大小使得性能提高。

2、limit

取前n条数据：limit  n；
limit 10,10；  从第10条开始取，一共取10条。（实际就是取10条数据，然后将最后10条返回）

如果limit 的深度很深的话（例如limit 10000，10），这样的效率是比较慢的（会取10010条数据，再将最后10条返回）

如果深度较深的话性能会降低，那么此时就可以用索引去优化：

3、group by  分组

例如订单表中，我们需要查看每个月的订单总金额，此时就得使用group by进行查询了。

分组也可以接多个字段：group by  字段1，字段2；

则表示字段1和字段2相同的放在一组,一般分组后是要对每组的数据进行聚合运算，例如sum、count等。

group by的优化（非索引方式）

 进行数据分组，每组的数据都会存放到临时表内存（mysql会独立一个临时表内存用于分组操作等）中。那么此时可以对于临时表进行优化

临时表有一个参数tmp_table_size(默认16M)，可以用show variables like '%tmp%'; 查看

4、count(*)、count(1)、count（字段）

count(字段)：返回该字段非null值的个数

count（*）和count(1)（或者count（数字）的结果都一样，count 1、2、3、。。都一样）：表示返回记录行数。

5、having ：过滤

主要要区分having和where的区别。

 而having表示对select后面聚合之后的值进行过滤（例如这里使用了聚合之后的值count）。（where不能使用聚合后的值）

如果将上面改为下面：

会报字段不存在的错误，之前的表已经被group by进行分组了，最后只剩下month、count、sum这三个列，所以最后进行having会报字段不存在。而having一般是使用select后面的字段。改成这样才对：

group_concat(price,'：',1):可以对price进行分组后以：号隔开

---

子查询：
 

1、in （推荐用）

即存在某个子查询返回的结果，匹配子集中的任一元素

2、any、all、some等

3、exists

主要区别in 和 exists

in和exists的结果一般的相同的，但是其运行机制不同

in的写法：（独立子查询：in后面的子查询和外面的没有关联）

 修改为existst的sql：（相关子查询，里面的子查询需要关联外面的进行过滤）（即将in语句逻辑改到子查询中取）

其实用exists和in都可以实现一样的效果，那么什么时候用in、什么时候用exists？
外层查询表小于子查询表（数据少于），则用exists，外层查询表大于子查询表，则用in，如果外层和子查询表差不多，则爱用哪个用哪个。

n的逻辑：子查询一次查完，查出的结果一条一条的去外查询表中匹配扫描（被扫描表：外表。扫描次数：子表行数）

existst逻辑：外查询表每条数据都去子查询中关联执行。（被扫描表：子表，扫描次数：外表行数）

如果要效率最高，此时要求扫描次数少，那么in时适用于子表较小的时候，即外层查询表大于子查询表；而同样exists适用于外表较小，即外层查询表小于子查询表。

not in  和  not existst？
not exists性能是高于not  in的，not in是用不到索引的，因为not in实质上等于!= and != ···，因为!=不会使用索引，故not in不会使用索引。

总结：

exists和in区别总结：
 in：独立子查询，适用于外表大于子表的情况，not in不能使用索引，not in会出现子查询中有null则查出的结果永远为空的情况。

existst：关联子查询，适用于外表小于子表的情况。不会出现not in 子查询null查询结果空的问题。

not的推荐使用not exists，in和exists看场景使用。

---

联表

1、inner join /join  内联 （求交集）

2、outer  join  外联  

分为左联left join/left outer join、右连 right join/right outer join

3、union：拼接两者的结果

笛卡尔积: select  *  from a, b

---

Prepare  sql

可以防止sql注入，动态查询条件。

sql注入：比如我们现在有一个语句：select * from  a  where  id = 100；

此时被人找到接口，将sql修改为：select * from  a  where  id = 100 or  1=1；此时不管怎样，都会查出所有数据。

---

存储过程、自定义函数、触发器、视图、事件event

十一、存储引擎

Inoodb支持事务，myisam不支持事务。

5.5默认myisam（不支持事务、表锁、容易丢失数据、性能低，所以一般不要使用）

现在基本都是默认innodb（支持事务、行锁、性能高）

mysql也不是只用innodb，例如mysql的临时表操作就会用到memory存储引擎。

        虽然说一般用innodb存储引擎，事实上就是以innodb为主体，一些操作如果其他存储引擎好mysql内部还是会用到其他的存储引擎的，例如这里的临时表操作mysql内部就会用到memory存储引擎，所以该引擎不能被禁用。

这篇关于Mysql复习的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！