MYSQL相关知识
MySQL:
1.SQL 语句基本的执行链路?
客户端 -> Server(连接器 分析器 优化器 执行器) -> 存储引擎
2.redo log 和 binlog
redo log: 存储引擎层的日志。 InnoDB 引擎特有的一套日志系统， MylSAM没有哦。
Write-Ahead Logging策略，先写日志，再写磁盘, 确保异常重启数据不丢失。
记录某个数据页做了什么操作。
循环写。
binlog: Server层的日志
记录某个ID行做了什么操作。
追加写。
3.事务：一组操作。 4个特性，隔离级别，并发问题。
set autocommit=1
start transaction
xx
commit/rollback
隔离级别：可重复读， 一致性视图解决方案。 MylSAM引擎不支持。
#事务自动和手动提交
SHOW VARIABLES LIKE '%AUTOCOMMIT%'; -- ON 1, OFF 0
4.索引分类：
理解索引的模型 ： 哈希， 有序数组， N叉树， 跳表， LSM树
为什么不用二叉树？
为什么不用B树？
为什么用B+树？ B+ 树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数
主键索引，普通索引
回表 索引覆盖可规避回表
回表是指，InnoDB 在普通索引 a 上查到主键 id 的值后，再根据一个个主键 id 的值到主键索引上去查整行数据的过程。
覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。
页分裂，页合并
支持最左前缀
联合索引： MySQL5.6后出现的索引下推策略可减少回表次数 like走索引下推
5.锁设计： 处理并发问题。
范围分类： 全局锁， 表级锁， 行锁。
全局锁：锁住整个数据库， 只允许查询操作。 MylSAM引擎中全库备份的时候会用到。
表级锁： 两种锁。
表级锁一：表锁。 显式使用： lock tables ..read/write
表级锁二：元数据锁， MDL锁。 隐式使用： 访问表时系统默认会自动加上。 线程1读写表过程中， 线程2变更表结构，MDL锁会阻塞。
行数据的读写操作：自动加MDL读锁。
表结构的操作：自动加MDL写锁。
行级锁： 相对于表锁，有效的减少了锁冲突，提升业务并发度。 MylSAM引擎不支持。
两阶段锁协议 ： 事务提交后，行锁才能释放。
死锁：
#开启死锁检测 ON
SHOW VARIABLES LIKE '%innodb_deadlock_detect%';
#锁等待时间
SHOW VARIABLES LIKE '%innodb_lock_wait_timeout%';
死锁的预防： 控制并发度。 表的设计上进行优化： 比如一个账户设计成10个账户。
视图： MYSQL两种视图： 1. 虚拟表， 2.InnoDB引擎下MVCC的一致性视图。 支持读已提交和可重复读隔离级别下实现的。 事务期间定义：我看到哪些数据。
MVCC下快照工作原理：数据表中的每一行记录，其实可能有多个版本 (row)，每个版本有自己的 row_trx_id，这个id是严格递增的。
MVCC下的读是事务一致性读。
MVCC下的写是读最新版本数据然后写操作。 更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”。
串：一致性读，当前读，行锁。
6.索引选择
普通索引和唯一索引
InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将整页读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。
查询过程：选择普通还是唯一性能几乎一样。
更新过程：普通索引性能好， 因为唯一索引还需要判断是否唯一。
change buffer： 减少读磁盘IO. 普通索引更新会用到change buffer, 而唯一索引更新无法用到,因为新增和更新前需要把数据页先读进内存。
redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。小结
树的理解：https://database.51cto.com/art/201911/605881.htm
磁盘包括磁道和扇区, 扇区是最小单元, 大小是512字节 , 由于扇区太小, 为了读取方便, 将相邻扇区组合成"块" , 大小4K 。 4K是磁盘得一次IO。
MYSQL一次会从磁盘读取B+树的一个节点到内存中。 故读取节点越少，IO的次数就越少，数据查询的效率就更快。
Spring和SpringMVC这两个框架，Spring是父容器，SpringMVC是其子容器，子容器可以看见父容器中的注册的Bean，反之就不行。
子容器可以访问父容器对象，而父容器不可以访问子容器对象。
明确边界，Spring根容器负责所有其他非controller的Bean的注册，而SpringMVC只负责controller相关的Bean的注册。
如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。
show VARIABLES LIKE '%innodb_change_buffer_max_size%';
SET GLOBAL innodb_change_buffer_max_size=0;
7. MySQL 选错了索引问题 为什么？怎么解决？
(1). 索引统计信息不准确, 解决： 【analyze table xx】
(2). 优化器误判的情况, 解决：应用端用 【select * from t_user force index(idx_name) where name='test'】 来强行指定某个索引 或者 增加或删除索引绕过该问题。
MySQL 优化器优化时会考虑的因素： 1.扫描行数 2.是否使用临时表 3. 是否需要排序
扫描行数是"采样统计"得到的，结果不精确。 修正办法是, 执行命令：【analyze table xx】 重新统计索引信息;
8. MYSQL 给字符串（手机号，邮箱）建立合理索引
（1）. MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。
【alter table t_user add index index1(email)】
【alter table t_user add index index2(email(6))】
使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。 区分度越高越好。
索引长度越长，占用磁盘空间越大，搜索效率越低。
（2）. 倒序存储
【select field_list from t where id_card = reverse('input_id_card_string')】
9. MYSQL 数据库表的空间回收
InnoDB 的数据是按页存储的，如果我们删掉了一个数据页上的所有记录，会怎么样？答案是，整个数据页就可以被复用了。
区别记录的复用和数据页的服用。
delete 命令是不能回收表空间的。
删除会留下空洞， 插入出现页分裂也会留下空洞。如何收缩表空间从而去除空洞？ 重建表。 新建表A, 将表B读出来插入表A, 删除表B。
区别重建表3种： optimize table、analyze table 和 alter table
analyze table : 只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
optimize table t 等于 recreate+analyze。
alter table： 就是recreate, 对表数据重建。
# 表大小统计SQL
SELECT
table_name AS '表名',
concat(truncate(data_length/1024/1024,2),' MB') AS '大小'
FROM information_schema.tables
WHERE
table_schema = 'ydzj_pd'
and table_name='t_hplc_family_import'
SELECT
table_name AS '表名',
concat(truncate(data_length/1024/1024,2),' MB') AS '大小'
FROM information_schema.tables
WHERE
table_schema = 'ydzj_pd'
group by TABLE_NAME
order by data_length desc
10. count(*)很慢问题
MyISAM 引擎把一个表的总行数存在了磁盘上，
InnoDB 引擎把数据一行一行地从引擎里面读出来，然后累积计数。
由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。
解决办法：
基本思路：你需要自己找一个地方，把操作记录表的行数存起来。
11. order by 工作原理
sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。
但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。
12. 我查这么多数据，会不会把数据库内存打爆？
select “结果集”存在哪里呢？ MySQL 是“边读边发的”。
实际上，服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的：
(1)获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。
(2)重复获取行，直到 net_buffer 写满，调用网络接口发出去。
(3)如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。
(4)如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。
内存的数据页是在 Buffer Pool (BP) 中管理的，在 WAL 里 Buffer Pool 起到了加速更新的作用。而实际上，Buffer Pool 还有一个更重要的作用，就是加速查询。
Buffer Pool 对查询的加速效果，依赖于一个重要的指标，即：内存命中率。
【show engine innodb status】 结果中，查看一个系统当前的 BP 命中率。
InnoDB 内存管理用的是最近最少使用 (Least Recently Used, LRU) 算法，这个算法的核心就是淘汰最久未使用的数据。
由于 InnoDB 对 LRU 算法做了改进，冷数据的全表扫描，对 Buffer Pool 的影响也能做到可控。
13. join可不可以用
【select * from t1 straight_join t2 on t1.a=t2.a】 t1 是驱动表，t2 是被驱动表。
在这个流程里：对驱动表 t1 做了全表扫描，这个过程需要扫描 100 行；
而对于每一行 R，根据 a 字段去表 t2 查找，走的是树搜索过程。由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行，也是总共扫描 100 行；
所以，整个执行流程，总扫描行数是 200。
怎么选择驱动表？
使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；如果使用 join 语句的话，需要让小表做驱动表。
eg:
被驱动表的行数是 M。
每次在被驱动表查一行数据，要先搜索索引 a，再搜索主键索引。
每次搜索一棵树近似复杂度是以 2 为底的 M 的对数，记为 log2M，
所以在被驱动表上查一行的时间复杂度是 2*log2M。
在这个 join 语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。
驱动表的行数是 N，执行过程就要扫描驱动表 N 行，
然后对于每一行，到被驱动表上匹配一次。
因此整个执行过程，近似复杂度是 N + N*2*log2M。
N 对扫描行数的影响更大，因此应该让小表来做驱动表。
14. 临时表
【create temporary table】
区别 临时表 和 内存表？
临时表写数据的时候是写到磁盘上的。
内存表只在内存中。
sort buffer、内存临时表和 join buffer。
这三个数据结构都是用来存放语句执行过程中的中间数据，以辅助 SQL 语句的执行的。
其中，我们在排序的时候用到了 sort buffer，
在使用 join 语句的时候用到了 join buffer。
explatin 后 extra列有Using temporary，表示使用了临时表；
MySQL 什么时候会使用内部临时表？
1.如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；
2.join_buffer 是无序数组，sort_buffer 是有序数组，临时表是二维表结构；
3.如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如我们的例子中，union 需要用到唯一索引约束， group by 还需要用到另外一个字段来存累积计数。
