Mysql

Mysql的体系结构

 存储引擎：

 innodb：

一种兼顾高性能和高可靠性的通用引擎，Mysql5.5后默认使用innodb存储引擎。

特点：

• DML操作遵循ACID的模型，支持事务；
• 行级锁
• 支持外键

 每一个innodb引擎创建的表都具有对应的xx.ibd表空间文件来存储表结构、数据和索引。 

 MylSAM：

 Memory：

 上面三个引擎的区别！

 innodb主要区别就是事务，行锁和外键。

各引擎的使用场景：

 后两者分别被Mongodb和Redis给取代了。

索引

索引：一种帮助Mysql高效获取数据的数据结构

优点：加快查询的速度，减少了数据库的IO成本

缺点：索引列占用部分空间，并且降低了增删改的效率。

不同存储引擎可以支持不同的索引 。索引默认都是B+tree索引

 B-Tree（多路平衡查找树）

初始化设置每个节点最大存储key为4，则指针最大为4+1=5，当节点添加的key个数超过4后，中间的key向上分裂！，每个节点都存储了数据和键值。

B+Tree：

非子节点存储键值和指针，子节点才存储数据。所有查询到要到子节点返回数据。

  Hash：

利用hash算法将键值映射到对应的位置上，并储存在hash表中。

特点：

• 只能用于精确查找，不能范围比较查询
• 无法利用索引完成排序操作
• 查询效率高，通常只用查询一次（无hash冲突）

innodb为什么采用B+Tree？

1. 相对于二叉树，层级更少，效率高
2. B+Tree的非叶子节点不存放数据，因此每一页存储的键值和指针比B-Tree更多，同数据量下，树的层级更小，搜索效率更高

索引分类： 

 InnoDB主键索引的B+Tree高度对应储存的行数据量

每一个节点都会存储到一页中

 索引增删sql：

 联合索引的最左前缀法则：

 索引失效情况：

1. 字符串不加单引号，索引失效
2. 头部模糊查询（like '%关键词'），索引失效
3. or 分割开的条件中的部分列没有索引，sql涉及的索引全部失效
4. 如果走全表比索引快，索引失效
5. 隐式转换：操作符左右两边的数据类型不一致。

 为什么不推荐select * ？

因为select * 的sql往往不是覆盖索引查询，需要进行回表查询，导致性能降低。

前缀索引：

当字符串的（varchar、text等）长度较大时，创建索引会浪费大量磁盘IO，降低效率。因此可以对字符串的一部分前缀创建索引，减小索引结构体积，提高效率。

加了个（n）而已

 但是！由于截取了字符串的部分，因此一定会进行回表查询！！

索引的设计原则：

1. 针对数据量大，且查询频繁的表建立索引。
2. 针对常用于查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引。
4. 字符串长，尽量建立前缀索引
5. 尽量使用联合索引代替单例索引，联合索引可以覆盖索引，节省储存空间，避免回表查询。
6. 控制索引数量，维护需要代价
7. 创建表时尽量使用NOT NULL约束它。便于优化器选择最优索引进行查询。

SQL优化

数据插入优化

手动创建事务！

主键优化

主键顺序插入性能高于乱序插入，因为乱序插入会产生页分裂现象，页间的指针需要重新调整。

 Order by 优化

 假设age和phone两个字段进行排序：

两个字段都是升序或者降序：创建索引默认都是升序

 一个升一个降：

• 多个字段建议创建联合索引，注意降序和升序规则，遵守最左前缀法则，按索引顺序
• 如果还是出现filesort，数据量大时可以增加排序缓存区的大小sort_buffer_size(默认256k)

group by 优化与 order by类似

limit 优化：

大数据量下如：查询2000000后面10条记录的信息

通过覆盖索引加子查询进行优化：

 count优化：

 update优化

行级锁是对索引添加的锁，不是对记录添加的锁，如果根据非主键/索引字段进行更新，行锁或变为表锁！

总结：SQl优化主要对针对索引进行的优化

存储过程：

定义：将多个SQL语句分装到一起交给MySQL执行，类似事务

其实存储过程就是直接在Mysql层里封装了逻辑和方法，类似java的方法。实际开发中一般不用。

 Mysql系统变量

作用：通过修改系统变量的值来开启或则关闭某些功能，如自动提交事务@@Autocommit

全局变量作用范围为所有会话。

 用户变量：

作用：类似于java的成员变量，不知道有啥用

 局部变量

作用：不知道有啥用

 触发器：

行级触发器，在每行数据增删改查之后

 Mysql锁（重点）

1. 全局锁：锁数据库中所有表，粒度最大 
2. 表级锁
3. 行级锁

全局锁：加锁后，所有实例只能读，不能写！！

应用场景：全库备份

 由于锁的操作太重，会产生问题：

 InnoDB引擎可以通过快照功能来实现不加锁备份！

表级锁

表锁分两类，读锁和写锁，和reentrantlock的读写锁类似。

加锁:lock table 表名... read/write

释放锁：unlock tables

元数据锁（mdl）：（没太懂，看书）

mysql5.5引入，增删改查加MDL读锁，对表结构变更操作加MDL写锁

元数据锁主要是防止DML和DDL的冲突，mdL在Alter修改表结构的时候会自动获取。读写锁和mdl互斥。

 意向锁

在添加行锁的时候，会自动给表加上相对应的意向锁。

 在添加表锁的时候，会先获取意向锁，获取不到则一直阻塞。

行级锁

InnoDB引擎

逻辑存储结构 

表空间（每个表都有）-> 段（数据段、索引段、回滚段）->区（1区64页）->页（1页16行）->行（储存数据）

InnoDB架构

内存结构：

 

 磁盘结构

后台线程

 事务的原理

• 原子性（undo log 回滚日志）
• 隔离性（锁、MVCC）
• 持久性（redo log 重做日志）
• 一致性（undo log + redo log）

redo log 类似（数据备份），在数据修改数据时同步记录修改信息，再保存到日志文件中，再脏页刷新到磁盘失败时，进行数据恢复。保证磁盘刷新成功。

undo log：来保证事务的原子性，主要是在提交失败回滚时可以恢复到原状态。

 MVCC（多版本并发控制）

当前读（锁定读）：读取的数据是最新的，需要通过对select SQL语句加lock in share mode（共享锁）实现。

快照读：一般的select语句，不加锁，会去读取行的一个快照数据。有可能是历史数据版本。

InnoDB对MVCC的实现

MVCC的实现依赖于：隐藏字段、Read View、undo log日志。

表行数据记录中的三个隐藏字段：

• DB_TRX_ID:表示当前记录最后一次更新的事务id
• DB_ROLL_PTR:回滚指针，指向该行的undo log，意思就是指向该记录上一版本的地址
• DB_ROW_ID：当没有主键或者唯一索引时，自动利用行ID生成聚簇索引。

undo log（回滚日志）

• 在数据增删改的时候产生的日志，主要用于数据回滚或者快照读。
• insert语句的undo log会在事务提交后删除，因此初始回滚指针为null，update和delete的undo log会保留到undo log版本链，用于mvcc实现，等待purge线程最后删除

 Readview

Readview（快照），每次执行select语句都会生成，保存了当前数据库系统中正在活跃的事务（未提交）的id，其中包含四个核心字段。

m_ids：当前活跃中的事务id

min_trx_id：最小活跃事务id

max_trx_id：预分配事务id，当前最大事务id+1

creator_trx_id：ReadView创建者的事务id

MVCC实现原理

快照读记录获取规则：

1. trx.id（准备读取记录的事务id）== creator_trx_id（当前快照读的事务id）? ->可访问
2. trx.id < min_trx_id（最小活动事务id）-> 可访问
3. trx.id > max_trx_id （预分配事务id） ？ -> 不可访问
4. min_trx_id <= trx_id <= max_trx_id  且trx.id不在m_ids中 -> 可访问

在RR中只会在第一次select生成readview，之后都会复用该readview，因此实现可重复读。