MYSQL

MYSQL基础

一、事务的基本要素（ACID）

事务（Transaction）是数据库区别于文件系统的重要特性之一，事务会把数据库从一种一致性状态转换为另一种一致性状态。在数据库提交时，可以确保要么所有修改都已保存，要么所有修改都不保存。一个事务对应一个完整的业务

**原子性（Atomicity）：**强调事务的不可分割.
**一致性（Consistency）：**事务的执行的前后数据的完整性保持一致.
**隔离性（Isolation）：**一个事务执行的过程中,不应该受到其他事务的干扰
**持久性（Durability）：**事务一旦结束,数据就持久到数据库

二.事务的并发问题

**1、脏读：**两个并发事务A,B ， 事务B读取了事务A尚未提交的数据。

**2、不可重复读：**一个事物的两次读期间，另一个事务改变了值
A第一次读是1
B改成2了
A再读就是2

**3、幻读：**一个事物两次读期间，另一个事务插入新的数据。

**总结：**不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。

第一类丢失更新： A事务撤销时，把已经提交的B事务的更新数据覆盖了。

第二类丢失更新 ：A事务覆盖B事务已经提交的数据，造成B事务所做操作丢失

三.数据库中的锁

表锁： 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
（在MySQL里，表锁不会有死锁发生，因为再加表锁前，要先解锁已有的表锁。
表锁会锁定表以及表里所有数据，对该表的任何请求（尤其是写请求）几乎都可能会被阻塞，所以说冲突更多。）

行锁： 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
（行锁要确定到哪一行，而表锁就是直接全部锁定不需要知道哪一行，所以表锁加锁快。确定锁哪一行的开销肯定比锁整张表的开销大）

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

共享锁（share lock）： 读锁，读取操作创建的锁。
一旦上锁，任何事务（包括当前事务）无法对其修改，其他事务可以并发读取数据，也可在对此数据再加共享锁

语法：SELECT … LOCK IN SHARE MODE;

排他锁： 写锁
如果事务对数据A加上排他锁后，则其他事务不可并发读取数据，也不能再对A加任何类型的锁。获准排他锁的事务既能读数据，又能修改数据。

语法：SELECT … FOR UPDATE

Innodb的行锁是怎么实现的

死锁，怎么解决

锁优化

四.事务隔离级别

1.读未提交
最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读
（采用一级封锁协议–写前+X锁，读不+锁，禁止了第一类丢失更新，可能会导致脏读，幻读，不可重复读）

2.读已提交
允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生
（采用MVCC锁或二级封锁协议–写前+X锁，事物结束才释放（写数据只会锁住相应的行），读前+S锁，读完就释放，可阻止脏读和第一类丢失更新，但可能会幻读或不可重复读）

3.可重复读
无论事务查询同一条数据多少次，该数据改了多少次，都只查询到事务开始之前的数据值。（除非数据是被本身事务自己所修改）可以阻止脏读和不可重复读，但幻读仍有可能发生。
（通过MVCC锁或三级封锁协议–写前+X锁，事务结束才释放，读前+S锁，事物结束才释放，可阻止脏读和不可重复读，但可能会幻读，也阻止了第一类/第二类丢失更新）
间隙锁保证了某个间隙内的数据在锁定情况下不会发生任何变化。但是当使用唯一索引来搜索唯一行的语句时，不需要间隙锁定。

4.可串行化
最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。
（读写数据都会锁住整张表，隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，可防止脏读，不可重复读，幻读，第一，第二类丢失更新）

问：为什么可重复读可以禁止第二类更新丢失，而读已提交不可以？
答：可重复读使用的是三级封锁协议，读已提交用的是二级封锁协议，
区别就在于三级封锁协议读数据前+S锁，直到事务结束才释放S锁
而二级在读数据前+S锁，但是读完就释放S锁了

五.MVCC多版本并发控制

MVCC是一种多版本并发控制机制。
多版本的并发控制相对于传统的基于锁的并发控制主要特点是读不上锁，这种特性对于读多写少的场景，大大提高了系统的并发度，因此大部分关系型数据库都实现了MVCC。

MVCC实现原理：

MVCC 解决脏读：
当我们读取某个数据库在时间点的快照时，只能看到时间点之前提交更新的结果，不能看到时间点之后事务提交的更新结果。这样就避免了脏读问题。

mvcc解决不可重复读：
事务A第一次查询：
id name version
1 peiyuan 1

事务B对这个表数据进行修改
id name version
1 xiaoming 2

事务A在进行一次查询：
id name version
1 peiyuan 1

因为事务A的对应版本号为1,无法查到对应版本号为2的’xiaoming’数据。

MVCC的优缺点：
优点：
在读取数据的时候,innodb几乎不用获得任何锁, 每个查询都通过版本检查,只获得自己需要的数据版本,从而大大提高了系统的并发度.

缺点：
为了实现多版本,innodb必须对每行增加相应的字段来存储版本信息,同时需要维护每一行的版本信息,而且在检索行的时候,需要进行版本的比较,因而降低了查询的效率;
innodb还必须定期清理不再需要的行版本,及时回收空间,这也增加了一些开销

当前读和快照读

快照读：
读的是历史数据
select id from 表 这种简单的查询

快照读解决幻读：
此时books表中有5条数据，版本号为1
事务A，系统版本号2：select * from books；因为1<=2所以此时会读取5条数据。
事务B，系统版本号3：insert into books …，插入一条数据，新插入的数据版本号为3，而其他的数据的版本号仍然是2，插入完成之后commit，事务结束。
事务A，系统版本号2：再次select * from books；只能读取<=2的数据，事务B新插入的那条数据版本号为3，因此读不出来，解决了幻读的问题。

当前读：
读取的是记录数据的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录

当前读是基于 行锁 + 间隙锁 来实现的，适用于 insert，update，delete， select … for update， select … lock in share mode 语句

例：
一个事务要update一个记录，另一个事务已经delete这条数据并且commit了，这样就会产生冲突，所以update的时候肯定要知道最新的信息。
update的时候会执行当前读，把返回的数据加锁。加锁是防止别的事务在这个时候对这条记录有其他操作，默认加的是排他锁，也就是你读都不可以，这样就可以保证数据不会出错了。

当前读基于 行锁+间隙锁 实现：

根据主键id，不只是有五个行锁，还会有六个间隙锁，左开右闭原则，（-∞，5]（5，10]（10，15]（15，20]（20，25]（25，+supernum]

例如 select * from table where id = 10 for update; 等值条件，id是存在的，加行锁就可以了

select * from table where id = 7 for update; 等值条件，id不存在，加（5，10] 间隙锁，这范围间不允许插入数据，直到这个事务提交完成释放锁

select * from table where id > 24; 范围条件，加间隙锁

mysql查询操作默认执行快照读操作，修改数据会执行当前读操作。

六.MYSQL索引数据结构

MYSQL为什么要建索引？
创建索引的最主要的原因就是可以大大加快数据的检索速度。
如果不使用索引，mysql必须从第一条记录开始然后读完整个表直到找出相关的行

MYSQL索引的数据结构？
B+树

为什么用B+树（结合B树，红黑树，hash来说）？
B+树特点：每层的节点数目非常多，层数少，目的就是为了减少磁盘IO次数
为什么不用B树？
**1.**B+树只有叶节点存放数据，其余节点用来索引，而B树是所有节点都会存放数据和索引，这无疑增大了节点大小，也就增加了IO次数，B+树的节点小，磁盘的IO次数也就少；（B+ 树非叶子节点上是不存储数据的，仅存储索引，而 B 树节点中不仅存索引，也会存储数据。）
**2.**B+树的数据都存储在叶子结点，扫一遍叶子结点就能得到全部数据，就能进行区间访问了，但是B树因为其分支节点同样存储的数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，在数据库中基于范围的查询是非常频繁的，所以B+树更适合在区间查询情况。

为什么不用红黑树？
红黑树是平衡二叉树的一种，是瘦高的，在大规模数据存储时，红黑树由于树的深度过大，会造成磁盘IO读写过于频繁，进而导致效率低下

为什么不用Hash？
**1.**哈希表的虽然可以快速的精确查询，但是不支持范围查询
**2.**如果只查找一个值的话，hash是一个很好的选择，但是数据库经常会选择多条，由于B+树索引有序，并且又有链表相连，它的查询效率比hash就快很多。而且数据库中的索引一般是在磁盘上，数据量大的情况可能无法一次装入内存，B+树的设计可以允许数据分批加载，同时树的高度较低，提高查找效率。

七.explain是如何解析sql的

explain关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理SQL语句的。可以分析查询语句或是表结构的性能瓶颈。它的语法就是：explain + sql语句。它能做很多事情，比如：表的读取顺序；数据读取操作的操作类型；哪些索引可以使用；哪些索引被实际使用；表之间的引用；每张表有多少行被优化器查询。

怎么查看SQL语句有没有用到索引

使用explain分析，在sql语句前加上explain
例：explain select * from 表 where id=1

只需要注意一个最重要的type 的信息很明显的提现是否用到索引：

type结果值从好到坏依次是：

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

得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

possible_keys：sql所用到的索引

key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL

rows: 显示MySQL认为它执行查询时必须检查的行数

八.order by原理

order by语句用于指定的列对结果集进行排序。
1.asc 升序，可以省略，是数据库默认的排序方式
2.desc 降序

单个结果
例：select name from 表 order by 成绩 desc;//降序

**order by的原则：**写在最前面的字段，他的优先级最高，优先执行他的内容。

多个结果
成绩： 1 2 6 4 5
年龄：10，11，8，7，9

select * from 表 order by 年龄，成绩；
年龄 成绩
7 4
8 6
9 5
10 1
11 2

order by工作原理：
全字段排序：
MySQL 会给每个线程分配一块内存用于排序，称为sort_buffer，一般带有order by 的SQL语句 会初始化sort_buffer，放入需要返回的字段(比如 name,age)。查找一条记录就放入到sort_buffer,继续找，直到找完符合条件的为止。对sort_buffer中的数据按照字段如name快速排序(归并排序);
排序这个动作可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。

缺点: 如果查询要返回的字段很多的话，那么sort_buffer 要放的字段数太多，要分成很多个临时文件来归并排序。

rowid排序:
参数 max_length_for_sort_data 是MySQL 中专门控制用于排序的行数据的长度的。
如果单行的长度超过这个值，MySQL就认为单行太大，需要换一个算法。
新的算法只将要排序的字段和主键id 放入sort_buffer中，最后排完序根据主键id的值回表去取对应的字段返回给客户端。

比较:
MySQL担心排序内存太小，才会采用rowid算法。虽然可以一次排序更多行，但是需要回表取数据。

MySQL认为内存足够大，会优先选择全字段排序，把需要的字段都放到sort_buffer中，这样排序后就可以直接从内存返回查询结果了，不用再回表。
这就体现了MySQL的一个设计思想: 如果内存够，就要多利用内存，尽量减少磁盘访问。

Group by

group_by是对数据按照哪个字段进行分组，或者是哪几个字段进行分组。

单个字段

多个字段

常配合聚合函数使用：count() , sum() , avg() , max() , min()

九.MySQL主从复制

原理：
MySQL主从复制是一个异步的复制过程，主库发送更新事件到从库，从库读取更新记录，并执行更新记录，使得从库的内容和主库保持一致

主从复制过程：
增删改操作----->主库通过sql线程把操作写入binlog------>通过主库的IO线程写入到从库------->从库的IO线程写入到relay日志（相当于buffer）-------->通过从库的sql线程同步

优点：
1.读写分离
2.在从服务器上可以执行查询操作，降低主服务器的压力
3.主出问题就切换到从服务器

复制的最大问题：
延迟

解决方案：
分库，将一个主库拆分为多个主库，每个主库的写并发就减少了几倍，此时主从延迟可以忽略不计。

主从复制的目的，什么要做主从复制

十.MySQL存储引擎 innodb myisam

MySQL中主要的存储引擎是Innodb和Myisam

区别：
1.innodb是支持事务的存储引擎，支持行级锁（通过索引项加锁来使用，只有通过索引条件，innodb采用行锁，否则用表锁）innodb适合大数据，高并发或插入和更新操作比较多的应用；

myisam是非事务的存储引擎，强调的是性能，支持表级锁，适合小数据，低并发或查询频繁的应用

2.myisam比innodb的执行速度快
InnoDB需要维护MVCC一致； 虽然你的场景没有，但他还是需要去检查和维护

Innodb通过为每一行记录添加两个额外的隐藏的值来实现 MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时删除。但是 InnoDB 并不存储这些事件发生时的实际时间，它只存储这些事件发生时的系统版本号。这是一个随着事务的创建而不断增长的数字。每个事务在事务开始时会记录它自己的系统版本号。每个查询必须去检查每行数据的版本号与事务的版本号是否相同。

用辅助索引的话myisam快
InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针

3.存储空间：
innodb需要更多的内存来存储，他会在主内存中建立专用的缓冲池，用于高速缓冲数据和索引；
myisam可被压缩，存储空间小，支持三种不同的存储方式，静态表，动态表，压缩表

4.索引和数据在磁盘上的存储结构
myisam：
role.frm：表结构文件
role.MYD：数据文件（MyISAM Data）
role.MYI：索引文件（MyISAM Index）

通过索引查找数据的流程：先从索引文件中查找到索引节点，从中拿到数据的文件指针，再到数据文件中通过文件指针定位了具体的数据。

innodb：
user.frm：表结构文件
user.ibd：索引和数据文件（InnoDB Data）

索引结构是在同一个树节点中同时存放索引和数据

5.索引结构：
InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和(主键)索引绑在一起的(表数据文件本身就是按B+Tree组织的一个索引结构)，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。(因此，主键不应该过大，因为主键太大，其他索引也都会很大。)

MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针

6.innodb支持外键
mysql 输入:show engines;可以看到MyISAM是默认的engine，它是不支持foreign key的，使用外键需要在创建表时指定engine=innodb;

十一.聚簇索引和非聚簇索引

聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的索引顺序与数据物理排列顺序无关。

例：翻到新华字典的汉字“裴”那一页就是P开头的部分，这就是物理存储顺序（聚簇索引）；而不用你到目录，找到汉字“裴”所在的页码，然后根据页码找到这个字（非聚簇索引）

聚簇索引的叶节点就是数据节点。非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块

聚簇索引：每个索引下存储的直接是数据
非聚簇索引：每个索引下存储的是数据地址

聚簇索引比非聚簇索引查询效率快很多

为什么建议用自增主键作为索引

InnoDB使用聚集索引，数据存储在索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果这个页满了，就开辟一个新的页（节点）。

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。

如果使用非自增主键（如果身份证号或学号，UUID等），由于每次插入主键的值都是随机的操作，每次新纪录都要被插到现有索引页得中间某个位置
MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构。

十二.主键索引，唯一索引，普通索引，联合索引

1.主键索引
是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值
在一张表中只能定义一个主键索引，主键用于唯一标识一条记录
alter table ‘table_name’ add primary key(‘column’);

2.唯一索引
与普通索引类似，不同的是索引列的值必须唯一，允许有空值
create unique index index_name on 表 (ID)

3.普通索引
create unique index index_name on 表 (name)

4.联合索引
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用，遵循最左前缀原则
create index index_name on 表 （ID,NAME）

最左前缀原则
如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。遇到范围查询就不往下走了(< > like between)
where a=1,b=2,c>3,d=4 走不到d

select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引

查询在什么时候不走（预期中的）索引？（索引失效）
1.如果条件中有or，即使其中有条件带索引也不会走索引（要想使用or,又想让索引生效，只能将or条件中的每个列都加上索引）
2.like查询以%开头索引失效（以%结尾索引可以使用）
3.如果列类型是字符串，查询字段没加引号（’ '），索引失效
4.出现is null ， is not null 索引失效
5.在索引列上做任何操作（计算，函数，类型转换）时索引失效
6.违反最左前缀原则索引失效
7.mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

什么时候会用到索引？
1.经常需要搜索的列上
2.作为主键的列
3.经常用在连接的列上
4.经常要排序的列上
5.经常用where的列上

十三.Innodb三大特性

插入缓存，两次写，自适应哈希索引

（一）插入缓存（解决了非聚簇索引写性能的问题）
对于非聚簇索引的插入和更新，不是每一次直接插入索引页中，而是首先判断插入的非聚簇索引页是否在缓冲池中，如果在，则直接插入，否则先放入一个插入缓冲区中，就可以将同一个索引项中的多个插入合并到一个IO操作中，大大提高了性能

插入缓冲使用的条件：
1.索引是辅助索引
2.索引不是唯一索引（如果索引唯一，整个索引数据被切分为两部分，无法保证唯一性）

插入缓冲的缺点：
1.可能导致数据库宕机后实例恢复时间变长
2.在写密集的情况下，插入缓冲会占用过多的缓冲池内存

（二）两次写
两次写给innodb带来的是可靠性，主要用来解决部分写失效，两次写由两部分组成，一部分是内存中的两次写缓冲区，大小为2M，另一部分是物理磁盘上的共享表空间中连续的128页，大小也为2M

部分写失效—当数据库正从内存向磁盘写一个数据页时，数据库宕机，从而导致这个页只写了部分数据

两次写原理：
1.当刷新缓冲池脏页时，并不直接写入数据文件，而是先拷贝到内存中的两次写缓冲区
2.接着从两次写缓冲区分两次写入磁盘共享表空间中，每次写1M
3.第二步完成后，再将两次写缓冲区写入数据文件

这样就可以解决部分写失效问题，因为在磁盘共享表空间中已有数据页副本拷贝，如果数据库在页写入数据文件的过程中宕机，在实例恢复时，可以从共享表空间中找到该页的 副本 ，然后把他拷贝覆盖原有的数据页。

（三）自适应哈希索引
原理：
innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以提升速度。

具体就是：
自适应哈希索引功能被打开，经常访问的二级索引数据会自动被生成到哈希索引里去，自适应哈希索引是通过缓冲池的B+树构造来的

特点：
1.无序，没有树高
2.降低对二级索引树的频繁访问资源

缺陷：
1.自适应哈希索引会占用innodb的缓冲池
2.自适应哈希索引只适合搜索等值的查询，对其他查找类型不适用

注意：
自适应哈希索引是用过缓冲池的B+树构造来的，因此建立的速度很快，而且不需要对整张表构建哈希索引，innodb会自动根据访问的频率和模式来自动的为某些热点页建立哈希索引

十四.事务日志

innodb事务日志包括redo log和undo log。

undo log：
undo日志用于存放数据修改被修改前的值，假设修改表中 id=2的行数据，把Name=’B’ 修改为Name = ‘B2’ ，那么undo日志就会用来存放Name=’B’的记录，如果这个修改出现异常，可以使用undo日志来实现回滚操作，保证事务的一致性。

redo log：
将最新的数据备份到一个地方，用于记录修改后最新的数据，顺序记录

假设修改表中 id=2的行数据，把Name=’B’ 修改为Name = ‘B2’ ，那么redo日志就会用来存放Name=’B2’的记录，如果这个修改在清理磁盘文件时出现异常，可以使用redo log实现重做操作，保证事务的持久性。

假设在事务提交后突然发生了某个故障，导致内存中的数据都失效了。解决这个问题的方法就是在事务完成之前把该事务的所有页面都刷新到磁盘和redo log。

整页面刷新问题：
1.刷新一个完整的数据页太浪费了，有时候仅仅修改一个字节，而不得不将一个完成的数据页(16k)从内存中刷新到磁盘；
2.随机IO刷起来比较慢，一个事物可能包含多条语句，涉及多个页面的修改，而这些页面不相邻的话就会进行很多随机IO。
redo log，是一个顺序IO，可以提高事务提交的速度；

redo日志与整页面刷新相比的好处是：
redo日志占用空间非常小；
redo日志是顺序写入磁盘的。

binlog：
二进制文件，他记录了MySQL所有数据的变更，并以二进制的形式存储在磁盘上，通过binlog日志我们可以进行数据库的读写分离、数据增量备份以及服务器宕机时的数据恢复。
（server层面）

1. 读写分离-----主从复制

2.增量备份
定期备份虽然可以在服务器发生宕机的时候快速的恢复数据，但不能做到实时，所以在发生宕机的时候，也会损伤一部分数据，如果这个时候开启了binlog日志，那么可以通过binlog来对没有做备份的这一阶段损失的数据进行恢复。（Redis有两种持久化方式，分别是AOF和RDB。RDB就类似于MySQL的全量复制，AOF就类似于MySQL的binlog。）

开启binlog 也要开启定期备份
因为binlog的数据量非常大，另外就是使用binlog做数据的恢复性能会非常低。因为binlog是对操作的记录，比如某一时刻，我先插入了一条数据，然后将这条数据删除了，本身数据是没了，但有两条操作。如果是全量备份，肯定没有这条数据，如果使用binlog需要执行一条插入和一条删除操作，因此性能和文件大小都是比较大的。

开启定期备份：
crontab -e

sh /usr/your/path/mysqlBackup.sh //每分钟执行一次

开启binlog
（1）查看当前binlog是否开启，执行show variables like “%bin%”
（2）改配置文件 find / -name my.cnf 查找my.cnf
（3）在[mysqld] 下面增加

log-bin = /var/log/mysql-bin.log

expire-logs-days = 14

max-binlog-size = 500M

server-id = 1

（4）重启 service mysql restart

3.服务器宕机时的数据恢复
（1）指定位置恢复：
mysqlbinlog 文件名 start position 100 stop position 200 | mysql -u用户名 -p密码 数据库名

（2）指定时间恢复：
/usr/bin/mysqlbinlog --start-datetime=“2021-1-1 1:00:00” --stop-datetime=“2021-1-1 5:00:00” /var/lib/mysql/mysql-bin.000001 |msyql -uroot -p

binlog有三种模式：ROW（行模式）, Statement（语句模式）, Mixed（混合模式）

行模式： 记录那条数据修改了，记录的是这条记录的全部数据，即使只更新了一个字段，binlog里也会记录所有字段的数据

Statement（语句模式）: 每一条会修改数据的sql都会记录在binlog中。

bin log命令：
1.binlog文件会随服务的启动创建一个新文件

2.通过flush logs 可以手动刷新日志，生成一个新的binlog文件

3.通过show master status 可以查看binlog的状态

4.通过reset master 可以清空binlog日志文件

5.通过mysqlbinlog 工具可以查看binlog日志的内容
mysqlbinlog 文件名 start position 100 stop position 200

6.通过执行dml，mysql会自动记录binlog

redo log和binlog区别：
1.redo log是属于innoDB层面，binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。
2.redo log是物理日志，记录该数据页更新的内容；binlog是逻辑日志，记录的是这个更新语句的原始逻辑
3.redo log是循环写，日志空间大小固定；binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。
4.binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。

事务的实现原理

十五.MySQL持久化

mysql的持久化机制是通过redolog实现的,先写入redolog缓冲区,再写入redologfile,再写入磁盘文件三步来实现的。

十六.where和have区别

where是一个约束声明，是在分组之前进行约束；having是一个过滤声明，是在分组之后进行过滤；
having后面可以使用聚合函数，where后面不可以使用聚合函数
聚合函数：count，sum，AVG，max等

十七.慢查询

慢查询就是记录MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time这个值的SQL会被记录到慢查询日志中，long_query_time的默认值为10，就是说这条语句运行了10S以上，就会被记录（等于10S不会被记录）。默认情况下，MySQL不开启慢查询日志，需要手动开启，如果不是调优的情况下一般不开启，因为会有性能影响。（慢查询支持将日志记录写入文件，也支持将日志记录写入数据库表）

slow_query_log:是否开启慢查询日志，1开启，0关闭

long_query_time的值也是可以手动修改的，修改之后，它的值还是10，这就需要重新连接或者打开一个新的会话才能看到修改值

查询有多少条慢查询记录：
show global status like ‘%slow_queries%’;

定位慢查询：
查看慢查询日志，确定已经执行完的慢查询
show processlist查看正在执行的慢查询

慢查询日志怎么开？如何查看历史的慢查询语句？
开启：show variables like ‘%quer%’;
查看慢查询存放日志： show variables like ‘slow_query_log_file’;

slow_query_log # 是否开启慢查询日志，默认OFF，开启则设置为 ON。
slow_query_log_file # 慢查询日志文件存储位置。
log_queries_not_using_indexes # 是否把没有使用到索引的SQL记录到日志中，默认OFF，开启则设置为 ON。
long_query_time # 超过多少秒的查询才会记录到日志中，注意单位是秒。

十八.SQL查询过程

1.先连接数据库，建立连接
2.查询缓存，查找到了直接返回，没找到就执行下一步
大多情况下不会使用查询缓存，因为查询缓存的失效非常频繁，只要有对一个表的更新，这个表上的所有缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低
MySQL8.0版本直接把查询缓存的这个模块删除了
3.分析器，如果没有命中查询缓存，就要开始真正执行语句了，MySQL对这条SQL语句解析，并生成一棵对应的解析树，然后对它验证和解析查询
4.优化器，优化器是在表里面有多个索引的时候，决定使用哪个索引，或者说在一个语句中有多表关联的时候，决定各个表的连接顺序。优化器阶段完成后，这个语句的方案就定下来了，然后进入执行器阶段
5.执行器，开始执行的时候，要先判断这个表有没有执行查询的权限，如果没有，就返回没有权限的错误；如果有，就继续执行，打开表的时候，执行器会根据表的引擎定义，去使用这个引擎提供的接口

十九.join

INNER JOIN：每个表都存在至少一个匹配时，就返回行。 交集
select * from 表A inner join 表 B on A.id=B.id;

LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行

A表
id name
1 o
2 p
3 q

B表
id name
1 z
2 k
4 L

select * from 表A left join 表 B on A.id=B.id;
表
Aid Aname Bid Bname
1 o 1 z
2 p 2 k
3 q null null

RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行

FULL JOIN: 只要其中一个表中存在匹配，就返回行。 并集

二十.SQL优化

1.主从复制
2.分库分表
3.explain分析

SQL约束

in和exists区别

in语句是把外表和内表做hash连接
exists语句是对外表做loop循环，每次循环再对内表进行查询

drop delete truncate 区别

DML：
经常用到的 SELECT、UPDATE、INSERT、DELETE。 主要用来对数据库的数据进行一些操作。

DDL：
创建表的时候用到的一些sql，比如说：CREATE、ALTER、DROP等。DDL主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上

二十一.连接数据库的方式

1.jdbc连
（1）装载sqlserver驱动对象
DriverManager.registerDriver(new SQLServerDriver());
（2）通过JDBC建立数据库连接
Connection con =DriverManager.getConnection(“jdbc:sqlserver://192.168.2.6:1433;DatabaseName=customer”, “sa”, “123”);
（3）要执行SQL语句，必须获得java.sql.Statement实例，所以创建Statement
PreparedStatement对象可以防止sql注入，Statement对象不行
（4）执行SQL语句
Statement接口提供了三种执行SQL语句的方法：executeQuery 、executeUpdate和execute
a. ResultSet executeQuery(String sqlString)：执行查询数据库的SQL语句，返回一个结果集（ResultSet）对象。
b. int executeUpdate(String sqlString)：用于执行INSERT、UPDATE或 DELETE语句以及SQL DDL语句，如：CREATE TABLE和DROP TABLE等
c. execute(sqlString):用于执行返回多个结果集、多个更新计数或二者组合的语句
（5）关闭JDBC对象
操作完成以后要把所有使用的JDBC对象全都关闭，以释放JDBC资源

2.mybatis
在src下新建配置文件MyBatisconfig.xml
3.使用Hibernate
在src下新建配置文件hibernate.cfg.xml

二十二.间隙锁

假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

mysql> select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的：
防止幻读，以满足相关隔离级别的要求。对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；

分库分表

水平拆分

垂直拆分

回表

有一张用于用户登录的user表：

字段名 类型 说明
id bigint(20) 主键ID
username varchar(20) 用户名
password varchar(20) 密码
假如现在有一个用户名为admin，密码为123的用户要登录，那我会先找出username为admin的那条用户数据

SELECT * FROM user WHERE username = ‘admin’
1
再根据查出来的user信息去对比密码是否正确
这时你发现username字段是唯一的又经常作为where条件所以可以给username字段建一个索引，于是就给username建了一个普通的B+Tree索引。这时候就出问题的，因为MySQL的InnoDB使用聚簇索引，具体的数据只和主键索引放在一起，其他的索引只存储了数据的地址（主键id）。比如上面的例子中，我根据username索引找到的只是一个username为admin这条数据的id而不是这条数据信息，所以要找到整条数据信息要根据得到的id再去找。看完上面的流程，你应该已经发现问题了，我要通过username找到id，再根据id找整条数据，这里有两个查找过程，这是影响效率的。就像上面的两个查找过程就是回表了。

解决办法
使用覆盖索引可以解决上面所说的回表的问题。还是拿上面上面登录的例子来说，其实登录只需要判断用户名和密码，如果user表中有其他用户信息也是不需要的那我们能不能只查询一次就找到这个用户名对应的密码呢。这个是可以的，上面所说的分两步查找，第一步根据username查找是肯定不能少的，那我们只要把password和索引username放到一起就可以了。我们可以建立一个（username、password）的组合索引，这里username一定要放在前面，然后我们把sql语句改一下

SELECT username, password FROM user WHERE username = ‘admin’
1
或

SELECT password FROM user WHERE username = ‘admin’
1
这样建立组合索引后根据username查找password，只要一步查找就可以查找到，因为password已经是username索引的一部分了，直接可以查出来，不再需要通过id找对应的整条数据。覆盖索引就是覆盖了多个列（字段）的索引。