MySql知识点小记

1.Mysql架构：分为三层，客户端，mysqlserver，存储引擎。

客户端：向数据库发送请求。（jdbc,navicat等）。

mysqlserver：自上向下为：

连接器（管理用户连接 show processlist）

分析器（语法词法的分析。SQL、AST抽象语法树  Apache里面有一个calcite语法解析器，自己构建存储引擎的时候需要构建自己的语法解析器）

优化器（优化SQL的语句，CBO 基于成本优化，RBO基于规则优化）

执行器（执行sql语句）

存储引擎：show engines; 存储引擎对应数据文件在磁盘的组织形式。

MySql 5.5.5之后默认存储引擎为InnoDB（聚集索引，对应数据文件类型为.ibd），之前为MyISAM（非聚集索引，对应数据文件为 .MYD数据   .MYI索引）。InnoDB并不是mysql自带的引擎。

InnoDB：支持事物。MyISAM：不支持事物。

2.事物

事物的最终目的，为了保证数据的一致性。

事物必须满足数据库的4大特性：（ACID），A  原子性。C 一致性。 I 隔离性。 D 持久性。

AID的存在最终都是为了C，为了保证数据的一致性。

mysql的隔离级别：读未提交，读已提交，重复读，串行化。

3.锁

共享锁：读锁  lock in shard mode   可以一起读，但是不能写

排它锁：写锁  for update   别的事物不能获得任何锁

独占锁：（MyISAM）类似排它锁。只是存储引擎不同

间隙锁：锁定一个范围的数据（开区间）

自增锁：

next-key：锁定一个范围（闭区间）可以理解为行锁加间隙锁

InnoDB加锁是给索引加锁，如果不使用索引，行锁将无效，转化为表锁。

死锁机制，1.等待超时，释放undo里面资源少的那一个事物。

                  2.wait forgraphy  一个图。数据自动检测死锁。

4.日志

MySql的重要日志。

bin-log:日志备份有3种  row(基于行的复制(row-based replication,RBR)),statement(基于SQL语句的复制(statement-based replication,SBR)),mixed(混合记录模式)

慢查询日志:

redo:物理日志，实现持久性。用于前滚。一个数据页。

undo:逻辑日志，原子性的实现原理，用于回滚，回滚为一行数据。MVCC（多版本并发控制）

逻辑日志和物理日志的区别。逻辑日志，行为单位存储数据操作，物理日志，页为单位存储数据操作。（WAL  write ahead log）。redo和undo日志都是InnoDB的

内存和磁盘之间数据交互以一个数据页为单位。

tips:

用户态和内核态之间有一个0拷贝，kafka就是使用的0拷贝。

有一个参数  innodb_flush_log_at_trx_commit  默认值为1.可以设置为 0  2用于管理redo log写入磁盘的方式。涉及数据安全性和性能问题

5.索引

5.1.索引：一种用于数据查询的数据结构。

索引的实现：数据结构的选择，hash，二叉树，二叉查询树（BST），AVL（二叉平衡树），红黑树，B树，B+树。逐步升级

hash：k-v的结构，hash算法设计的不好容易导致hash冲突，浪费资源（优化：高位参与运算）。只能用于等值查询，不能用于范围查询。InnoDB支持自适应hash。

二叉树，BST，递增的情况下会出现树不平衡，导致树高过深。

AVL（二叉平衡树，根据三个创建人名字命名）：通过左右旋转的方式来保持树的平衡。（最长子树-最短子树）不大于1。也会导致树高过深。

红黑树：AVL的基础上升级，最长子树不大于最短子树的2倍即可，减少旋转次数，但是也会有树高过深的情况。（红黑树还有几个限制条件）

以上的树型数据结构，缺点都在于树深过深，原因在于分叉太少，都是2分叉。而树深过深会导致IO次数增加，消耗资源增加。

B树：多叉平衡树，B树虽然是多叉，看似满足条件，但是B树节点上也会存放data（数据），如果data过大。会导致查询IO消耗增大。

B+树：所有的非叶子节点不存储data，只存储data的地址指针。只有叶子节点存储data。（2中查找方式）。插入数据的时候会涉及分裂和合并。

在B+tree上有两个头指针，一个指向根节点，一个指向关键字最小的叶子节点，而且所有的叶子节点（数据节点）之间是一种链式环结构。

1.对于主键的范围查找和分页查找。

2.从根节点开始，进行随机查找。

5.2.索引的类型

主键索引：通过主键来查找数据。（主键，非NULL，唯一）

辅助索引：1.回表：例如通过辅助索引查询主键，然后在通过主键查询需要的数据。（2次查询B+树）。2.索引覆盖：如果sql语句查询的内容刚好是辅助索引对应的内容，则将直接返回数据，不再去查询B+树（一次查询）。（例：select id from tbl where name = ""）直接返回id。using index

唯一索引：唯一键作为索引，和主键类似，但是主键非null，唯一键索引允许一个null值。

全文索引：

组合索引：多个字段组成一个索引。最左匹配原则：索引执行时从左边开始，不能跳跃。遇到范围查询非等值查询，索引失效

例：组合索引（name,age）

1.select * from table where name = ? and age = ?

2.select * from table where name = ?

3.select * from table where age = ?

4.select * from table where age = ? and name = ?

语句1.2.4索引有效，3索引失效。（4有效是因为MySQL的优化器会进行优化处理，颠倒顺序。索引下推。）

索引下推：5.6之前：先根据name把所有的 数据查询回来，然后在server层进行age字段的筛选。

5.6及以上版本：从存储引擎拉取数据的时候，会根据name,age两个字段做筛选，将符合的数据返回。

6.MySql优化

新版的mysql，（count(1), count(*), count(id)性能上没有任何的区别）

sql优化：执行计划。EXPLAIN。关键字段（id、type（至少保证在range以上））。官网。

集群：主从复制、读写分离、分库分表。