mysql知识

一.MYSQL的体系结构
https://www.cnblogs.com/binyang/p/11260138.html

二.Innodb的体系架构
1.后台线程. 内存池
内存池会有缓冲池,用来缓存磁盘中的数据,方便快速的进行读取,同时对磁盘文件的数据修改也是在缓冲池缓冲(LRU算法),内存池还有重做日志缓冲. 后台线程用来维护内存池的数据是最新的,正确的.

三.文件
1.参数文件. 数据库在启动的时候会先读取一些初始化参数,比如内存结构多大等,就会读取参数文件.
2.日志文件
2.1 错误日志, 对数据库启动,关闭都做了信息,记录了所有的错误信息
2.2 慢查询日志. 开启慢查询后,日志会记录超过慢查询时间的sql语句.
2.3 查询日志
2.4 二进制日志 记录了对mysql数据执行更改操作的所有操作.
3. 表结构定义文件 以frm为后缀名的文件,记录了这个表的结构定义.
以上各个存储引擎都是通用的.
4.表空间文件. 独立表空间 .idb
5.重做日志文件

一条查询语句mysql语句的执行过程?

1. 跟数据库建立连接。

2. 查询缓存
   MySQL 内部自带了一个缓存模块。缓存的作用我们应该很清楚了，把数据以 KV 的形式放到内存里面，可以加快数据的读取速度
   ,key就是我们需要查询的sql语句,value就是我们查询的结果.但是mysql不推荐使用,8.0版本还删除, sql必须完全匹配.第二个是表里面 任何一条数据发生变化的时候，这张表所有缓存都会失效. 所以缓存这一块,最好通过orm的框架来做,比如mybatis的缓存机制,或者交给专门做缓存服务的,比如redis.

3. 语法解析和预处理(Parser & Preprocessor).
   通过解析器进行语法树,.比如单词拼写有没有问题,引号加了没有. 然后在进行预处理,检查字段,表这些数据是否写对了没有.
   4.通过优化器等到执行计划
   5… 调用执行引擎执行执行计划 ， 返回结果

一条update语句的执行过程?

https://zhuanlan.zhihu.com/p/98529990

四.表
1.分区表.
mysql支持水平分区,不支持垂直.而且是局部分区,就是一个分区中存放了数据了也存放了索引.
分区类型:1.range分区 .按行记录中某个列值所处哪个位置进行分区.
List分区 :分区列的值还散列的,不是连续的. 比如1分区 ,3,6,9…
hash分区:
key:和hash分区类似,只是利用mysql提供的函数进行分区.

mysql数据存储的最小单位是页

InnoDB存储引擎也有自己的最小储存单元——页（Page），一个页的大小是16K。

面试官：MySQL索引为什么用B+树

https://zhuanlan.zhihu.com/p/69137713
https://juejin.im/book/5bffcbc9f265da614b11b731/section/5c061ac46fb9a049e66003f2
https://blog.csdn.net/ljfphp/article/details/80029968

五.索引 索引是对数据库表中一列或多列的值进行排序的一种结构.
2.innodb的索引
一.B+索引 B+树就是一种专门为数据库或者其他文件系统中做查找的平衡查找树. 它是从B树演变过来的.
B+树和b树的区别: 就是说非叶子结点的关键字也会出现叶子结点中，就是说B+树的叶子结点会包含全部的关键字信息，B树的关键字是不重复的。 然后非叶子结点只会存储关键字信息，但是不会存储关键字对应的数据记录信息。 它的数据记录信息全部都会在叶子结点存储。b树在叶子结点和非叶子结点都会存储。
2.聚集索引 锁引项的排序方式和表中数据记录排序方式一致的索引（这里不懂先放着，一会举例），每张表只能有一个聚集索引，聚集索引的叶子节点存储了整个行数据。 所以innodb的数据是根据主键的顺序进行存放的,而聚集索引就是根据主键构造的一颗B+树.
辅助索引:辅助索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，还存储了一个指向改行数据的聚集索引建的书签。
联合索引:联合索引就是对表上的多个项进行索引.
覆盖索引:就是从辅助索引中就可以得到查询的结果,不需要再继续查询聚集索引中的记录.
二.哈希索引
自适应哈希索引 槽数的取值一般是略大于2倍缓冲池页数量的质数. innodb在启动的时候会分配一个1399个槽的哈希表,用来哈希查询所在缓冲池中的页. innodb的存储引擎的表空间都有一个space_id,用户要查询的应该是某个表空间上的某个连续16kb的页,然后根据space_id和offset,将这两个数据加工后作为hash值.然后再定位到

对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据的指针。

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

Hash索引有以下一些限制： 由于索引仅包含hash code和记录指针，所以，MySQL不能通过使用索引避免读取记录，即每次使用哈希索引查询到记录指针后都要回读元祖查取数据。不能使用hash索引排序。Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的。Hash索引只支持等值比较，例如使用=，IN( )和<=>。对于WHERE price>100并不能加速查询。访问Hash索引的速度非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。如果哈希冲突很多的话，一些索引维护操作的代价也会很高。当从表中删除一行时，存储引擎要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。
InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”，由Mysql自动管理，不需要DBA人为干预。默认情况下为开启，我们可以通过参数innodb_adaptive_hash_index来禁用此特性。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于缓冲池中的B+ Tree索引上再创建一个哈希索引，这样就上B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。

三.全文索引.
全文索引就是可以将存储于数据库的某个列值中的任意内容查找出来的技术.  它是通过倒排索引来实现的, 倒排索引,就是,辅助表中存放单词和单词自身所在的一个文档或者多个文档所在位置之间的映射.通过关联数组来实现.

四.事务
事务就是一组操作,要么全部执行要么全部不执行. 然后他会将数据库从一个一致性的状态正确转化到另一种一致性的状态. 最经典的例子就是银行转账; 它会满足四个特性:acid
a原子性: 事务的所有操作要么全部执行成功,要么就全部执行不成功.哪怕一个sql语句执行失败,数据库就需要回滚到没有执行事务的时刻.
c一致性 一致性就是事务从一个正确的状态达到另一个正确的状态. 这个状态需要符合本身数据库的约束,比如有些字段它有唯一的约束,大小的约束. 还需要我们现实生活中业务逻辑的约束,比如转账不能无缘无故地加钱.
i隔离性 隔离性就是执行的事务它不会受其他正在执行事务的影响. 最理想的情况就是事务之间是一种按照串行方式执行. 但是这样就会大大地影响到数据库的并发性能,并不符合我们开发需求. 我们只有放宽数据库对隔离性的要求,才能提供数据库的并发性能, 但是同时也因为隔离性的降低,导致可能出现一系列的数据一致性问题. 比如脏读,不能重复读,幻读这些问题. 对此数据库给我们提供多种4种的隔离级别,读已提交,读未提交,可重复读,串行化. 隔离级别越高,数据一致性越强,但是并发性能就会越差. 隔离级别是通过数据库并发控制来实现的，msql并发控制方法目前主要有2种：基于事务锁的并发控制，也就是两阶段锁协议(2PL)；多版本并发控制(MVCC)；
二阶段锁：每个数据库对象都有两把锁，共享锁，和排他锁，二阶段锁就是一个事务在第一个阶段只能不断的获取锁，第二阶段只能由持有锁的事务不断地释放锁。 就是获取锁和释放锁的阶段不能交替地进行。
脏读:

  读已提交:
	
	幻读:

MYsql的四种隔离级别：
1.读未提交：读不加锁，写入枷锁
2.读已提交：读加锁，只是在每个操作之后释放锁
3.可重复读：读取数据需要在事务提交之后才释放锁。
4。串行化：就是在事务开始的时候获取更大力度的锁，比如表锁，来防止其他事务的插入操作。  
  d持久性

mysql锁机制

https://blog.csdn.net/qq_34337272/article/details/80611486
InnoDB存储引擎的锁算法的一些规则如下所示，后续章节会给出对应的实验案例和详细讲解。在不通过索引条件查询时，InnoDB 会锁定表中的所有记录。所以，如果考虑性能，WHERE语句中的条件查询的字段都应该加上索引。InnoDB通过索引来实现行锁，而不是通过锁住记录。因此，当操作的两条不同记录拥有相同的索引时，也会因为行锁被锁而发生等待。由于InnoDB的索引机制，数据库操作使用了主键索引，InnoDB会锁住主键索引；使用非主键索引时，InnoDB会先锁住非主键索引，再锁定主键索引。当查询的索引是唯一索引(不存在两个数据行具有完全相同的键值)时，InnoDB存储引擎会将Next-Key Lock降级为Record Lock，即只锁住索引本身，而不是范围。InnoDB对于辅助索引有特殊的处理，不仅会锁住辅助索引值所在的范围，还会将其下一键值加上Gap LOCK。InnoDB使用Next-Key Lock机制来避免Phantom Problem（幻读问题）

作者：历小冰
链接：https://zhuanlan.zhihu.com/p/66676020

mysql MVCC多版本并发控制

https://zhuanlan.zhihu.com/p/64576887
https://segmentfault.com/a/1190000012650596

mysql乐观锁

日志

https://blog.csdn.net/hanpeiyu1995/article/details/90044300
https://www.cnblogs.com/drizzle-xu/p/9869406.html
redo日志和binlog https://zhuanlan.zhihu.com/p/98529990 MySQL update执行流程到 redo log深入理解
redo日志: redo log 是 InnoDB 引擎特有的,它是通过
https://www.jianshu.com/p/2f1585c7f2f3 redo日志和undo日志问题答疑
https://www.jianshu.com/p/907f9002442e 一文带你看懂binlog和redo log
MySQL 的整体架构其实有两块：一块是 Server 层，还有一块是 引擎层，负责存储相关。前面我们提到的 redo log 是InnoDB 引擎持有的，而 Server 层也有自己的日志，叫 binlog（归档日志）。因为单单通过binlog日志 没有 crash-safe 的能力（因为是 Server 层与引擎层是两个独立的模块）

在MySQL5.1之前，所有的binlog都是基于SQL语句级别的。但是应用这种格式的binlog进行数据恢复时，如果SQL语句带有rand或uuid等函数，可能导致恢复出来的数据与原始数据不一致。因此从5.1版本开始，MySQL引入了binlog_format参数，该参数有三种可选值：statement、row和mixed：

statement就是之前的格式，基于SQL语句来记录
row记录的则是行的更改情况，可以避免之前提到的数据不一致的问题
但是row格式有一个不好的地方就是当修改的行数很多时，生成的binlog占用很大的空间，占用大量空间的同时还会耗费大量IO资源，因此MySQL又提供了一种折中的方案——mixed。在mixed模式下，MySQL默认仍然采用statement格式进行记录，但是一旦它判断可能会有数据不一致的情况发生，则会采用row格式来记录

redo日志:

undo日志:https://blog.csdn.net/lhanson/article/details/82752883

存储过程

1.sql语句需要先编译后执行,存储过程是一组具有名称的 SQL 语句集合,可以完成特定的功能. 普通SQL语句在创建过程时进行分析和编译。 存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的存储计划，再接下来调用的时候只需要提供指定名称+相关的参数就能执行, 这样可以提高执行性能,也能减少网络的传输. 具有更强的安全性,存储过程调用时,传入的相关参数会当作文本值来处理,而不会当作执行代码处理,所以可以避免sql注入.

触发器

https://www.cnblogs.com/fps2tao/p/10400936.html

数据库三大范式

https://zhuanlan.zhihu.com/p/63146817.
https://zhuanlan.zhihu.com/p/76796394

数据库的约束

https://www.zhihu.com/search?type=content&q=mysql%E7%BA%A6%E6%9D%9F

mysql如何进行优化

索引优化

1.有or必全有索引;
2.复合索引未用左列字段;
3.like以%开头;
4.需要类型转换;
5.where中索引列有运算;
6.where中索引列使用了函数;
7.如果mysql觉得全表扫描更快时（数据少）;

1.limit的用法

LIMIT是MySQL内置函数，其作用是用于限制查询结果的条数。
1)其语法格式如下:
LIMIT[位置偏移量,]行数
其中，中括号里面的参数是可选参数，位置偏移量是指MySQL查询分析器要从哪一行开始显示，索引值从0开始，即第一条记录位置偏移量是0，第二条记录的位置偏移量是1,依此类推…，第二个参数为“行数”即指示返回的记录条数。
位置偏移量可以理解为跳过前xx条记录(元组).

2)基本用法
/当没有指定位置偏移量时，只取4条时，可以这样写/
SELECT * FROM YourTableName LIMIT 4;
/当指定了位置偏移量时，从第3条起取4条时，可以这样写/
/因为索引是从0开始计数的，所以第3条对应的索引就是2/
SELECT * FROM YourTableName LIMIT 2,4;

3)应用场合:分页
// 后台计算出页码、页数(页大小)
int curPage = 2;
int pageSize = 10;
int startRow = (curPage - 1) * pageSize;
SELECT * FROM YourTableName LIMIT startRow,pageSize;

limit的优化

mysql大数据量使用limit分页，随着页码的增大，查询效率越低下。
当一个表数据有几百万的数据的时候成了问题！
如 select * from table limit 0,10 这个没有问题 当 limit 200000,10 的时候数据读取就很慢
原因本质： 1）limit语句的查询时间与起始记录（offset）的位置成正比 2）mysql的limit语句是很方便，但是对记录很多:百万，千万级别的表并不适合直接使用。
例如： limit10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行，问题就在这里。 ​ LIMIT 2000000, 30 扫描了200万+ 30行，怪不得慢的都堵死了，甚至会导致磁盘io 100%消耗。 ​ 但是: limit 30 这样的语句仅仅扫描30行。

优化手段
干掉或者利用 limit offset,size 中的offset
不是直接使用limit，而是首先获取到offset的id然后直接使用limit size来获取数据