mysql整理

mysql 常见面试

索引失效：

1. 使用了 select *
2. 索引列上有计算，索引会失效。（where height+1=7）
3. 索引列上加了函数，索引也会失效（ where SUBSTR(height,1,1)=8）
4. 字符类型没加引号
5. 用is null和is not null 没注意字段是否允许为空
6. like查询左边有%

创建索引的条件

1. 常用查询字段建索引
2. 排序、分组和联合查询字段建索引
   

mysql 基础

查询请求执行过程

链接管理
每当有一个客户端进程连接到服务器进程时，服务器进程都会创建一个线程专门处理与这个客户端的交互。
当该客户端退出时会与服务器断开连接服务器并不会立即把与该 客户端交互的线程销毁 而是把它缓存起来 在另一个新的客户端再进行连接时，把这个缓存的线 程分配给该新客户端.这样就不用频繁地创建和销毁线程，从而节省了开销.
从这一点大家也能看出， MSQL 服务器会为每一个连接进来的客户端分配一个线程，
但是线程分配得太多会严重影响系 统性能 所以我们也需要限制可以同时连接到服务器的客户端数量，

解析与优化
查询缓存、语法解析、查询优化

1. 查询缓存：

MySQL 服务器程序处理查询请求的过程也是这样 会把刚刚处理过的查询请求和结果缓存起来 如果下 次有同样的请求过来，直接从缓存中查找结果就好了，就不用再去底层的表中查找了
这个查询缓存可以在不同的客户端之间共享
如果客户端A 刚刚发送个查询请求，而客户端B 之后发送了同样的查询请求，那么客户端 的这次查询就可以直接使用查询缓存中的数据了
当开启Query Cache之后，MySQL 会对接收到的每一个SELECT语句通过特定的hash算法计算该Query的hash值，然后通过该hash值到Query Cache中去匹配。
MySQL 8.0版本中，彻底移除了Query Cache

2. 语法解析：

客户端程序发送过来的请求只是段文本。所以 MySQL 服务器程序首先会对这段文本进行分析（词法解析，语法解析，语义解析）

3. 查询优化

优化的结果就是生成一个执行计划，
执行计划表明了应该用哪些索引执行查询，以及表之间的连接顺序
我们可以使用 EXPLAIN 语句来查看某个语句的执行计划

存储引擎
MySQL 服务器把数据的存储和提取操作都封装到了一个名为存储引擎的模块中

存储引擎 MyISAM和InnoDB区别
是否⽀持⾏级锁 : MyISAM 只有表级锁，⽽InnoDB ⽀持⾏级锁
是否支持事务：MyISAM** 强调的是性能，每次查询具有原⼦性,但是不提供事务⽀持。是InnoDB 提供事务⽀持事务
是否⽀持外键：** MyISAM不⽀持，⽽InnoDB⽀持。
是否⽀持MVCC ：仅 InnoDB ⽀持。多版本控制，

mysql 索引

MYSQL性能优化
尽量选择小的列
将where中频繁使用的建立索引
select子句中避免使用 *
只需要一行数据是使用Limit 1 Limit限制查询结果返回的值
explain 查看执行计划，从而知道如何处理sql语句，分析查询语句
提高group by语句的效率，先过滤，后分组

SQL语句-创建索引
CREATE INDEX IDX_COOR_KEEPER_ID
ON TK_CUSTOMER_ORG_ORDER COOR_KEEPER_ID)

删除索引 drop idnex IDX_COOR_KEEPER_ID

创建自增字段
create sequence ubr_log_info_id
increment by 1
start with 1
nomaxvale
cache 20;

insert into TK_UBR_LOG_INFO （id，starus）values（ubr_log_info_id.nextval，‘999’）

索引的演进

二分查找
二分查找会把查找的候选数据缩小一半，这样效率提升了一倍，
如果让我设计一个数据库索引的话，
选择有序的数组作为数据库索引的存储结构。
使用有序数组做索引的话，查询效率可能比较高，但是更新的效率就很低了，因为要维护数组的index角标。所以有序数组只适合存储静态的数据

二叉查找树（BST Binary Search Tree)
左子树所有的节点都小于父节点，右子树所有的节点都大于父节点。

好处：
二叉查找树兼顾了有序数组和链表的优点：二叉查找树既能够实现快速查找，又能够实现快速插入。
坏处：
我们的二叉树变成了一个链表结构，因为左右子树深度差太大，这棵树的左子树根本没有节点——也就是它不够平衡。

平衡二叉树（AVL 树）（左旋、右旋）
左右子树深度差绝对值不能超过 1。

多路平衡查找树（Balanced Tree） - B Tree
跟 AVL 树一样，B 树在枝节点和叶子节点存储键值、数据地址、节点引用。
每个树节点，都尽可能多的存放更多的子节点的引用（关键字），这样我们的指针树也就越多，可以有更多的分叉（路数）
我们的树就不再是二叉了，而是多叉，或者叫做多路。

B+ 树（升级版多路平衡查找树）
B+树比B树升级在哪了？通过B+树的数据结构图可以一窥端倪

这里要找到1，只需要进行3次IO操作，比AVL树效率要高不少。
MySQL 中的 B+Tree 的特点：
B+Tree 的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。搜索到关键字不会直接返回，会到最后一层的叶子节点。
B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构

我们来研究下3层的B+树可以存多少数据？
一张 2000 万左右的表，查询数据最多需要访问 3 次磁盘
所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储

MySQL 中的 B+Tree 的优点：
每个节点存储更多关键字;路数更多
扫库、扫表能力更强(如果我们要对表进行全表扫描，只需要遍历叶子节点就可以 了，不需要遍历整棵 B+Tree 拿到所有的数据)
B+Tree 的磁盘读写能力相对于 B Tree 来说更强(根节点和枝节点不保存数据区， 所以一个节点可以保存更多的关键字，一次磁盘加载（IO）的关键字更多)
排序能力更强(因为叶子节点上有下一个数据区的指针，数据形成了链表)
范围查询更快：比如要查询从 22 到 60 的数据，当找到 22 之后，只 需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，这样就极大地提高了区间查询效率(不需要返回上层父节点重复遍历查找)。
效率更加稳定(B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的)
————————————————

二分查找：（1-100 区取50，判断后，再去一半的值）
二叉树查找：插入数据，一直大，会变成链表。
AVL平衡二叉树： 分裂，合并，包括平衡。
多路平衡查找：B Tree
mysql 的 B+树

查看有多少个数据页
show variables like 'innodb_page_size';

MySQL 的数据都是文件的形式存放在磁盘中的位置
SHOW VARIABLES LIKE 'datadir';
每张 InnoDB 的表（红色）有两个文件(.frm 和.ibd)

.frm 是 MySQL 里面表结构定义的文件，    

回表

回表,
select name from student where name = 'zhangsan'

但是如果是下面这种写法，就需要进行回表操作了，
因为辅助索引中，没有age的值，只好拿着主键值去主键索引中查询，最终取得数据
select name,age from student where name = '张三'

索引的创建与使用

什么情况下需要建索引？
经常用于查询的字段
经常用于连接的字段建立索引，可以加快连接的速度
经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度

索引的创建
在用于 where 判断 order 排序和 join 的（on）、group by 字段上创建索引
索引的个数不要过多。（一般不超过10个） – 浪费空间，更新变慢
离散度（区分度）低的字段，例如性别，不要建索引。
离散度太低，导致扫描行数过多。有可能不会用到索引（未来走索引，不是对表进行新增一个字段保存分行机构ID，而是对where 条件进行 in 改成 = 好进行匹配 支行所在机构）
频繁更新的值，不要作为主键或者索引。-- 导致页分裂
组合索引把散列性高（区分度高）的值放在前面。
能创建复合索引的时候，就不要创建单列索引。

什么时候用不到索引
索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式、计算（+ - * /）：
字符串不加引号，出现隐式转换
like 条件中前面带% – 前面带%那不相当于所有数据都符合条件吗
负向查询，NOT 不能： != （<>）和 NOT IN 在某些情况下可以：

基本信息表查询，最左匹配

核心：
尽量用一个两个复杂的多字段索引坑 80%的查询，
在用一个两个辅助索引坑剩下的，从而保证查询速度和性能

总结；索引首先要判断，搜索条件是否生成合适的扫描区间
exception 单表访问方法
const， 使用聚簇索引，
ref， 通过二级索引查到聚簇索引，再通过聚簇索引查询到数据
ref_or_null， 通过二级索引查询，查询列值中为null 的记录。
range， 这个是，索引的范围区间查询。
index， 这个是，扫描全部二级索引。
all， 全表扫描

注意事项
索引合并
SELECT * FROM single_table where keyl = 'a' AND key3 = 'b';
一般情况：会对key1 生成扫描区间，或者对key3生成扫描区间。然后再进行单点扫描查。
还有一种情况：索引合并指的就是对从不同索引中扫描到的记录的 id 值取交集

MySQL的存储结构

mysql的存储结构分为：表空间、段、簇（区）、页、行。

• 表空间 Table Space

表空间可以看做是 InnoDB 存储引擎逻辑结构的最高层

• 段 Segment

表空间是由各个段组成的，常见的段有数据段、索引段、
创建一个索引会创建两个段，
一个是索引段:leaf node segment，
一个是数据段: non-leaf node segment。

• 簇(区) Extent

一个段(Segment)又由很多的簇(也可以叫区)组成，
每个区的大小是 1MB(64 个连续的页)。

• 页 Page

簇是由连续的页(Page) 组成的空间，一个簇中有 64 个连续的页。
一个表空间最多拥有 2^32 个页，默认情况下一个页的大小为 16KB，也就是说一个表空间最多存储 64TB 的数据。

mysql日志

mysql中的日志种类** **二进制日志binlog（归档日志）和 事务日志： redo log（重做日志）和 undo log（回滚日志）
慢查询日志（slow query log）

更新语句的基本流程和查询语句一致的，也就是说，它也要经过解析器、优化器的处理，最后交给执行器。

缓冲池 Buffer Pool

对于InnoDB存储引擎来说，数据都是放在磁盘上的，存储引擎要操作数据, 必须先把磁盘里面的数据加载到内存里面才可以操作。

InnoDB设计了一个内存的缓冲区。读取数据的时候，先判断是不是在这个内存区域里面，如果是，就直接读取，然后操作，不用再次从磁盘加载。如果不是，读取后就写到这个内存的缓冲区。这个内存区域有个专属的名字，叫Buffer Pool
InnoDB里面有专门的后台线程把Buffer Pool的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。

总结一下：Buffer Pool的作用是为了提高读写的效率。

中继日志（relay log）

随机IO：
如果我们所需要的数据是随机分散在磁盘上不同页的不同扇区中，那么找到相应的数据需要等到磁臂旋转到指定的页，然后盘片寻找到对应的扇区，才能找到我们所需要的一块数据，一次进行此过程直到找完所有数据，这个就是随机IO,读取数据速度较慢。
顺序IO：
假设我们已经找到了第一块数据，并且其他所需的数据就在这一块数据后边，那么就不需要重新寻址，可以依次拿到我们所需的数据，这个就叫顺序IO。

————————————————

redo log

重做日志（redo log）
确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

1. 执行Update操作
2. 先将原始数据读从磁盘读取到内存，修改内存中的数据。
3. 生成一条重做日志写入redo log buffer ，纪录数据被修改后的值。
4. 当事物提交时，需要将redo log buffer中的内容刷新到redo log file。
5. 事物提交后，也会将内存中修改的数据写入到磁盘。

为什么需要写Redo Log Buffer 和 Redo Log Flle? 而不是直接持久化到磁盘？
如果直接写入到磁盘，影响性能。

redo log是 InnoDB 引擎特有的。
redo log主要记录的是某个数据页做了什么修改，bin log记录的是语句的原始逻辑，比如更新了某一行的某个字段。
redo log是循环写的，数据会被覆盖。bin log是追加写，一个文件写满，就写下一个文件。
两者是如何配合完成两阶段提交的。

redo log 的特点

redo log是InnoDB存储引擎实现的，并不是所有存储引擎都有。支持崩溃恢复是InnoDB的一个特性。
redo log不是记录数据页更新之后的状态，而是记录的是"在某个数据页上做了什么修改”。属于物理日志。
redo log的大小是固定的，前面的内容会被覆盖,一旦写满，就会触发buffer pool 到磁盘的同步，以便腾出空间记录后面的修改

redo log叫做 重做日志，是用来实现事务的 持久性。

start transaction;
select balance from bank where name="zhangsan";
// 生成 重做日志 balance=600
update bank set balance = balance - 400; 
// 生成 重做日志 amount=400
update finance set amount = amount + 400;
commit;

mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Boffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步。

所以引入了redo log来记录已成功提交事务的修改信息，并且会把redo log持久化到磁盘，系统重启之后在读取redo log恢复最新数据。

redo log 重做日志。 日志保证持久性。
因为 redo 重做日志 所有操作，先到 redo log buffer ，最后会落到磁盘。持久性。
redo log重做日志的组成
一是内存中的重做日志缓存，叫做redo log buffer
二是重做日志文件，叫做redo log file
MySQL中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到Buffer Pool中。后续的查询都是先从Buffer Pool中找，没有命中再去硬盘加载，减少硬盘IO开销，提升性能。

更新表数据的时候，也是如此，发现Buffer Pool里存在要更新的数据，就直接在Buffer Pool里更新。然后会把在某个数据页上做了什么修改记录到重做日志缓存(redo log buffer)里，接着刷盘到redo log文件里。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面。

Undo log 撤销日志或回滚日志

undo log 记录了事务发生之前的数据状态，分为insert undo log和update undo log。如果修改数据时出现异常，可以用undo log来实现回滚操作 （保持原子性）
可以理解为undo log记录的是反向的操作，比如insert会记录delete, update 会记录update原来的值，跟redolog记录在哪个物理页面做了什么操作不同，所以叫做逻辑格式的日志。

有了这些日志之后，我们来总结一下一个更新操作的流程，这是一个简化的过程。 name 原值是 xhc

update user set name = '中华第一帅' where id=1;


1,事务开始，从内存（buffer pool）或磁盘（data file）取到包含这条数据的数据页，
	返回给Server的执行器；
2,Server的执行器修改数据页的这一行数据的值为 ‘中华第一帅’;
3,记录 name=xhc 至 undo log;
4,记录 name=中华第一帅 至 redo log;
5,记录bin log；
6,调用存储引擎接口，记录数据页到Buffer Pool （修改name=中华第一帅）;
7,事务提交。
————————————————

回滚日志（undo log）
保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读
在事务中使用的每一条 INSERT 都对应了一条 DELETE，每一条 UPDATE 也都对应一条相反的 UPDATE 语句。
Undo Logi 通过两个隐藏列tx_id (最近一次提交事务的ID)和 roll_pointer (上个版本的地址)，建立一个版本链，实现回滚到上一个版本。

undo log 叫做 回滚日志，用于记录数据被修改前的信息。
undo log 记录事务修改之前版本的数据信息，因此假如由于系统错误或者rollback操作而回滚的话可以根据undo log的信息来进行回滚到没被修改前的状态。
undolog是用来回滚数据的用于保障未提交事务的原子性

undo log 回滚日志。 日志保证原子性。（要么成功，要么失败）
根据上面流程可以得出如下结论：
1.每条数据变更(insert/update/delete)操作都伴随一条undo log的生成,并且回滚日志必须先于数据持久化到磁盘上
2.所谓的回滚就是根据回滚日志做逆向操作，比如delete的逆向操作为insert，insert的逆向操作为delete，update的逆向为update等。

big log

binlog记录了数据库表结构和表数据变更,比如update/delete/insert/truncate/create。它不会记录select，因为这个没有对表进行变更
binlog以事件的形式记录了所有的DDL和DML语句,可以用来做主从复制和数据恢复。跟redo log不一样，它的文件内容是可以追加的，没有固定大小限制。
主要的两个作用：复制和恢复数据

• MySQL在公司使用的时候往往都是一主多从结构的，从服务器需要与主服务器的数据保持一致，这就是通过binlog来是实现的（数据库的主从复制），它的原理就是从服务器读取主服务器 的binlog,然后执行一遍。
• 数据库的数据被干掉了，我们可以通过binlog来对数据进行恢复。因为binlog记录了数据库表的变更，所以我们可以用binlog进行复制和恢复数据
• 
  

二进制日志（binlog）：
用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。可以简单认为记录的就是sql语句
如果不小心整个数据库的数据被删除了，能使用redo log文件恢复数据吗？
redo log文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从redo log文件里擦除。
事务执行过程中，先把日志写到binlog cache,事务提交的时候，再把binlogcache写到binlog文件中
至于什么时候刷新到磁盘，可以sync_binlog配置参数指定。？

• 0(延迟写)每次提交事务都不会刷盘，由系统自己决定什么时候刷盘，可能会丢失数据。
• 1(实时写)每次提交事务，都会刷盘，性能较差。
• N(延迟写)提交N个事务后，才会刷盘。

mysql锁技术以及MVCC基础

MVCC基础
MVCC (MultiVersion Concurrency Control) 叫做多版本并发控制。
MVCC在mysql中的实现依赖的是undo log与read view

• undo log :中记录某行数据的多个版本的数据。
• read view :用来判断当前版本数据的可见性

数据库的事务
事物的四⼤特性(ACID)：
原子性（一次操作不能拆分），
一致性（要么成功，要么失败），
隔离性（两个事务隔离），
持久性（持久到库中）。

• 事务的 原子性 是通过 undo log 来实现的（回滚，日志，）
• 事务的 持久性 性是通过 redo log 来实现的（操作落到磁盘，记录事务一次完整操作）
• 事务的 隔离性 是通过 (读写锁+MVCC)来实现的
• 而事务的终极大 boss 一致性是通过原子性，持久性，隔离性来实现的！！！
  

隔离性实现

Mysql 隔离级别有以下四种（级别由低到高）：

• READ UNCOMMITED (读未提交)
• READ COMMITED (读已提交)
• REPEATABLE READ (可重复读)
• SERIALIZABLE (序列化)

隔离性是要管理多个并发读写请求的访问顺序
READ UNCOMMITTED

READ COMMITTED
其他事务能读到已提交的修改变化。在很多场景下这种逻辑是可以接受的。
但是该级别会产生不可重读以及幻读问题
READ COMMITTED 级别下的MVCC机制有关系，
在该隔离级别下每次 select的时候 新生成一个版本号，所以每次select的时候读的不是一个副本而是不同的副本。

REPEATABLE READ(Mysql默认隔离级别)可重复读
只读当前事务开启前的 读视图

SERIALIZABLE 串行化
该隔离级别理解起来最简单，实现也最单。在隔离级别下除了不会造成数据不一致问题，没其他优点。

Mysql的事务与锁知识(一)之 MVCC

MVCC的效果： 我可以査到在我这个事务开始之前已经存在的数据，即使它在后面被修改或者删除了。而在我这个事务之后新增的数据，我是查不到的。
所以我们才把这个叫做快照，不管别的事务做任何增删改查的操作，它只能看到第一次查询时看到的数据版本。

InnoDB为每行记录都实现了两个隐藏字段：
DB_TRX_ID : 6字节：事务ID,数据是在哪个事务插入或者修改为新数据的，就记录为当前事务ID。
DB_ROLL_PTR : 7字节：回滚指针（我们把它理解为删除版本号，数据被删除或记录为旧数据的时候，记录当前事务ID,没有修改或者删除的时候是空）

问题一： InnoDB中，一条数据的旧版本，是存放在哪里的呢？
答案是：undo logo 。 因为修改了多次, 这些undo log会形成一个链条，叫做undo log链，所以前面我们说的DB_ROLL_PTR,它其实就是指向undo log链的指针。

问题二： 每个不同时间点的事务，它们去undo log链找数据的时候，拿到的数据是不一样的。在这个undo log链里面，一个事务怎么判断哪个版本的数据是它应该读取的呢？
按照前面说的MVCC的规则，必须根据事务id做一系列比较。所以，我们必须要有一个数据结构，把本事务ID、活跃事务ID、当前系统最大事务ID存起来，这样才能实现判断。这个数据结构就叫Read View （可见性视图），每个事务都维护一个自己的Read View。
————————————————

注意:
RR(可重复读)中Read View 是事务第一次査询的时候建立的。
RC(读已提交)的Read View 是事务每次查询的时候建立的。

mysql什么是事务

事务的四⼤特性ACID
原子，一致，持久，隔离，

能否简单解释下 脏读、不可重复读、幻读的意思
脏读: 事务中的修改即使没有提交，其他事务也能看⻅，事务可以读到未提交的数据称为脏读
不可重复读: 同个事务前后多次读取，不能读到相同的数据内容，中间另⼀个事务也操作了该同⼀数据
幻读：当某个事务在读取某个范围内的记录时，另外⼀个事务⼜在该范围内插⼊了新的记录，当之前的事务
再次读取该范围的记录时，发现两次不⼀样，产⽣幻读

常⻅的隔离级别由低到⾼有哪⼏种
Read Uncommitted(未提交读，读取未提交内容)，事务可以读到为提交的数据称为脏读
Read Committed(提交读，读取提交内容)，⼀个事务开始后只能看⻅已经提交的事务所做的修改，在事务中执⾏两次同样的查询可能得到不⼀样的结果，也叫做不可重复读
Repeatable Read(可重复读,mysql默认的事务隔离级别)，使⽤ MMVC机制 实现可重复读
Serializable(可串⾏化)，强制所有事务串⾏执⾏，所以并发效率低

事务是逻辑上的⼀组操作，要么都执⾏，要么都不执⾏。
事物的四⼤特性
原⼦性（Atomicity）： 事务是最⼩的执⾏单位，不允许分割，
⼀致性（Consistency）： 执⾏事务前后，数据保持⼀致，多个事务对同⼀个数据读取的结果是相同的；
隔离性（Isolation）： 并发访问数据库时，⼀个⽤户的事务不被其他事务所⼲扰，各并发事务之间数据库是独⽴的；
持久性（Durability）： ⼀个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发⽣故障也不应该对其有任何影响。

事务并发问题
脏读：事务A读取事务B更新的数据，事务B进行回滚操作，事务A读取的数据就是脏数据
不可重复读：事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据进行更新并提交，导致事务A多次读取同一数据，结果不一致。
幻读：修改数据时，突然更新一条数据，没有修改，像幻觉一样。
事务隔离级别
读未提交：如果一个事务已经开始写数据，则另外一个事务不允许同时进行写操作，但允许其他事务读此行数据，
读已提交：如果是一个读事务(线程)，则允许其他事务读写，如果是写事务将会禁止其他事务访问该行数据，该隔离级别避免了脏读，但是可能出现不可重复读
可重复读：可重复读取是指在一个事务内，多次读同一个数据，在这个事务还没结束时，其他事务不能访问该数据(包括了读写)
串行化：提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行

mysql分库分表

分库：根据用户，用户ID标识，用户A的所有数据都落到库A 上。
分表：单表数据量大，进行表主键ID，进行拆分存到不同的表上。

写读时间差，一个数据写入后，不会立即查询。
写读时间差现象”是蚂蚁架构师们根据实践统计总结的，他们发现大部分情况下，一个数据被写入后，都会过足够长的时间后才会被访问。

高并发下怎样优雅的保证扣减库存数据的正确性

库存超发：

1. 同步代码块，Synchronized，单个jvm 实例， 集群就不能这么用了。
2. redis，分布式锁，缺点：每次扣减也要去问问 redis
3. 直接数据库扣减：正解

update coupon set stock=stock - #{num} where id = #{couponId} and stock>0
//测试如果num大于已有库存，则会变负数
update coupon set stock=stock - #{num} where id = #{couponId} and （stock - #{num})>=0
或者
update coupon set stock=stock - #{num} where id = #{couponId} and stock >= #{num}
//修复了负数问题

如果上面出现，ABA问题。一定价格 versoin 版本号
update coupon set stock=stock-1,version=version+1 where id = #{couponId} and stock>0 and versoin=#{oldVersion}

大厂面试题，高并发库存扣减超卖问题解决，多种sql适合场景
高并发库存扣减超卖问题，很多人加了乐观锁版本号去解决，那下面三种有什么区别，分别适合哪些场景使用
1）update product set stock=stock-1 where id = 1 and stock>0

2）update product set stock=stock-1 where stock=#{原先查询的库存} and id = 1 and stock>0

3）update product set stock=stock-1,versioin = version+1 where id = 1 and stock>0 and version=#{原先查询的版本号}
答案 : 核心是解决超卖的问题，就是防止库存为负数

方案一：id是主键索引的前提下，如果每次只是减少1个库存，则可以采用上面的方式，只做数据安全
校验，可以有效减库存，性能更高，避免大量无用sql，只要有库存就也可以操作成功.
场景：高并发场景下的取号器，优惠券发放扣减库存等

方案二：使用业务自身的条件做为乐观锁，但是存在ABA问题，对比方案三的好处是不用增加version版本字段。
如果只是扣减库存且不在意ABA问题时，则可以采用上面的方式，但业务性能相对方案一就差了点，因为库存变动后sql就会无效

方案三：增加版本号主要是为了解决ABA问题，数据读取后，更新前数据被别人篡改过，version只能做递增
场景：商品秒杀、优惠券方法，需要记录库存操作前后的业务

可以用redis 以 list 保存商铺梳理，最后同步到 数据库。
强一致性。 只能写sql 语句实现强一致性问题。

存储类型

数据库选型

列式数据库：
HBASE，海量存储，聚合分析实时性高。（mysql是行数据，HBASE是列数据）劣势：查询条件有限
文档型数据库：
MongoDB，表字段可扩展，无需定义，读写性能优越。劣势：无法join，内存消耗大。

mysql 分库分表（重要）

分库分表。 分库分表中间件全部可以归结为两大类型：

• CLIENT模式；客户模式：开源社区的 sharding-jdbc，sharding-sphere 已经支持了proxy模式
• PROXY模式：代理模式：代表有阿里的cobar，民间组织的MyCAT

单个sharding column分库分表 ；
多个sharding column分库分表；
sharding column分库分表 + ES检索
阿里：选用orderid分表，那我用userid来查询的很多，那不是所有的分表都要查？怎么处理
选择合适的sharding column
选择方法：分析你的API流量，将流量比较大的API对应的SQL提取出来，将这些SQL共同的条件作为sharding column。
为用户提供服务的，以用户信息为 sharding column
冗余全量表和冗余关系表选择
即order_id（订单号），user_id（用户ID），merchant_code（商家ID）。

sharding column分库分表 + ES检索（模糊查询）
sharding column + es的模式，将分库分表所有数据全量冗余到es中，将那些复杂的查询交给es处理

全文索引思路（HBase）
可能参与条件检索的字段索引到ES中，所有字段的全量数据保存到HBase中，这就是经典的ES+HBase组合方案，即索引与数据存储隔离的方案。

锁，悲观锁 for update，乐观锁 version。

mysql：
InnoDB：支持事务，支持外键，聚簇索引，支持表锁行锁。
mysql 事务支持：读未提交（脏读），读已提交（不可重复读），可重复读（幻读），串行化，
脏读：读取到别的事务未提交的数据
不可重复度：事务重复读取，第一次和第二次的值不一致
幻读：事务B对数据进行 insert，事务A两次读取的数据不一致

快照读：mvcc，多版本视图控制，
当前读：争抢锁（行锁，间隙锁，表锁）

什么情况下是，快照读，当前读。
快照读：事务A，不能读取事务B，提交的数据。只能读取当前事务的数据。（发生在，select 语句）
当前读：select for update、for lock，update，insert，delete、

表锁：select * from user for udpate；
间隙锁：select * from user where name=‘123’ for update；（左开右闭，只对insert 有影响。会造成 black insert 操作）
行锁：select * from user where id = ‘123’ for update；

实际场景：如果要加锁，会先查询出加锁的数据，然后循环 用主键唯一行锁标记。

聚簇索引，非聚簇索引
聚簇索引：就是索引和数据存在一起，（主键就是聚簇索引。）
非聚簇索引：存的是 索引和唯一索引的

索引优化：
全职匹配：查询条件全部用到。
最左匹配：索引是 a b 查询调试是 where b a 会被优化并使用到最左匹配。慎用or，把or 拆分子查询
不做索引列转换：字符串转换，avg
覆盖索引：只返回索引数据，把返回的数据。例： 查主键ID
join，小表做驱动表，

exeplain

ALL 全表没走索引，index 全索引扫描，range 索引范围查询，
ref 非唯一索引查询 eq_ref 唯一索引，system，const：常量查询

mysql的性能（百万级别。）
主键：ms级别
唯一建查询：10ms
非唯一索引：100ms
五索引查询：1s

mysql应用优化：批量insert 批量update， 读索引操作（把查询的返回数据创建一个联合索引）
批量写：for 循环写，修改成 insert values
批量写和循环写对比：sql编译，网络消耗，磁盘寻址
mysql client——》mysql server——》磁盘IO
网络消耗， sql编译 磁盘IO（磁盘寻址，磁盘写入）

mysql链接配置优化：
max_connection = 默认 100 （单台可配置到 1000）

mysql缓存配置优化：（这两个参数重要）
innodb_buffer_pool_size = 1GB 缓存池大小，设置为当前服务器内存的 60%-80%
innodb_log_buffer_size=16M 确保有足够大的日志缓冲区，保证脏数据写入日志文件前，可以继续mysql事务操作

4核8G。

mysql 磁盘配置优化：（指标表数据和索引存储空间的）
innodb_file_per_table=1 （设置一张表的数据存储在 1 个 db文件上。）
innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;auto extend；指定表数据和索引存储空间，可以是一个或多个文件，最后一个文件必须是自动扩充。 （自动增长为 8M）

mysql 事务提交优化：
innodb_flush_log_at_trx_commit =2

mysql语句

sql执行：
windows 下查看 my.ini 文件：SELECT @@datadir;
查看版本号：select VERSION();

使用命令查看是否打开binlog模式：
show variables like 'log_bin';

查看binlog日志文件列表：
show binary logs ;
Datetime 和 Timestamp 之间抉择
切记不要用字符串存储日期，DateTime 类型是没有时区信息的，所以用 Timestamp 作为保存时间字段。

SQL语句-查询锁表标识，进程，（待整理）

select * from V$_LOCK

根据查询，整理出 update 语句
select ‘ALTER SYSTEM’ || b.sid|| ','|| b.sertat
from V$PROCESS a ,V$SESSION B,V$SQL c
where a.addr = b.paddr
and b.sql_id = c.sql_id

SQL语句-查询

select * from tk_branch_new  
start with Branch_id in ('3') 
connect by PAR_BRANCH_ID  = prior    BRANCH_ID

```java select a.naem,b.name from tk_customer_info a, tk_customer_org_order b where a.ci_id = b.customer_id and ( exists（select 1 from tk_customer_label_code e where e.customer_id = a.ci_id and e.label_code = '05'）) ```
```java select province,COUNT(DISTINCT phone) from parent_user.u_login_log where c_date >='2021-01-29 00:00:00' and c_date <='2021-02-04 23:59:59' group by province ```

SELECT DISTINCT
 pe.id,
            fr.fertilizer_name
        FROM
            t_fertilizer fr
                RIGHT JOIN t_programme_fertilizer pf ON pf.fertilizer_id = fr.id
                RIGHT JOIN t_programme pe ON pe.id = pf.programme_id
        WHERE
            pe.id =84

SQL语句-修改
-- 新增两个字段后， 给新增的两个字段赋值操作

update log  t2   inner join   student as t1 
on t1.id = t2.user_id
set t2.province = t1.province, t2.corp = t1.corp;

复制表数据

create table tk_customer_0314 as select * from tk_customer_info;

父子结构查询。

select branch_id,branch_name
from tk_branck
start with branch_id = '3'
connect by prior branch_id=par_branch_id;

分页数据

select * from(
select tmp.* , Rownum page from(
select * from table) tmp)
where page<=10 and page >0;

日期判断

and to_Char(create_date ,'yyyy-MM') = '2020-07'

mybatis

{}和${}的区别
{} 可以有效的防止SQL注入，提高系统安全性；
${} 不能防止SQL 注入
like 怎么拼接sql语句。CONCAT(’%’,#{question},’%’) 使用CONCAT()函数，（推荐）

MyBatis 工作原理

1. 读取 MyBatis 配置文件：mybatis-config.xml 为 MyBatis 的全局配置文件，配置了 MyBatis 的运行环境等信息，例如数据库连接信息。

2. 加载映射文件。映射文件即 SQL 映射文件，该文件中配置了操作数据库的 SQL 语句，需要在 MyBatis 配置文件 mybatis-config.xml 中加载。mybatis-config.xml 文件可以加载多个映射文件，每个文件对应数据库中的一张表。

3. 构造会话工厂：通过 MyBatis 的环境等配置信息构建会话工厂 SqlSessionFactory。

4. 创建会话对象：由会话工厂创建 SqlSession 对象，该对象中包含了执行 SQL 语句的所有方法。

5. Executor 执行器：MyBatis 它将根据 SqlSession 传递的参数动态地生成需要执行的 SQL 语句，同时负责查询缓存的维护。

6. MappedStatement 对象：在 Executor 接口的执行方法中有一个 MappedStatement 类型的参数，该参数是对映射信息的封装，用于存储要映射的 SQL 语句的 id、参数等信息。

7. 输入参数映射：输入参数类型可以是 Map、List 等集合类型，也可以是基本数据类型和 POJO 类型。输入参数映射过程类似于 JDBC 对 preparedStatement 对象设置参数的过程。

8. 输出结果映射：输出结果类型可以是 Map、 List 等集合类型，也可以是基本数据类型和 POJO 类型。输出结果映射过程类似于 JDBC 对结果集的解析过程。

在mapper中如何传递多个参数
@Param注解传参法@Param("userName")String name

public User selectUser(@Param("userName") String name, int @Param("deptId") deptId);

<select id="selectUser" resultMap="UserResultMap">
    select * from user
    where user_name = #{userName} and dept_id = #{deptId}
</select>

Map传参法

public User selectUser(Map<String, Object> params);

<select id="selectUser" parameterType="java.util.Map" resultMap="UserResultMap">
    select * from user
    where user_name = #{userName} and dept_id = #{deptId}
</select>

Java Bean传参法

public User selectUser(User user);

<select id="selectUser" parameterType="com.jourwon.pojo.User" resultMap="UserResultMap">
    select * from user
    where user_name = #{userName} and dept_id = #{deptId}
</select>

Mybatis如何执行批量操作
使用foreach标签

<!-- 批量保存(foreach插入多条数据两种方法)
       int addEmpsBatch(@Param("emps") List<Employee> emps); -->
<!-- MySQL下批量保存，可以foreach遍历 mysql支持values(),(),()语法 --> //推荐使用

<insert id="addEmpsBatch">
    INSERT INTO emp(ename,gender,email,did)
    VALUES
    <foreach collection="emps" item="emp" separator=",">
        (#{emp.eName},#{emp.gender},#{emp.email},#{emp.dept.id})
    </foreach>
</insert>

<select id="getOrder" parameterType="int" resultMap="orderResultMap">
	select * from orders where order_id=#{id}
</select>
 
<resultMap type="com.jourwon.pojo.Order" id="orderResultMap">
    <!–用id属性来映射主键字段–>
    <id property="id" column="order_id">
    
    <!–用result属性来映射非主键字段，property为实体类属性名，column为数据库表中的属性–>
	<result property ="orderno" column ="order_no"/>
	<result property="price" column="order_price" />
</reslutMap>

mysql linux 常用命令

查看 linux 服务 MySQL 是否安装及正常启动
service mysqld status
ps -ef | grep mysqld
链接 linux mysql 服务查看数据库及表
mysql -h localhost -u root -p

show databases;
create database name;
use databasename;
show tables;
describe tablename;

查询表中一条记录。
select * from user LIMIT 1

sql 语句的书写顺序：
select->distinct->from->join->on->where->groupby->having->orderby->limit