MySQL进阶学习

---

MySQL体系结构

连接层：接收客户的连接、认证授权（判断用户名和密码的正确性）
服务层：sql接口、解析器
引擎层：索引是在引擎层实现的，不同的存储引擎索引的结构也不同，InnoDB 5.5以后默认的存储引擎
存储层：存储数据库的相关数据

什么是事务

DML（增删改）操作遵循ACID（原子性、一致性、隔离性、持久性）模型，DQL（查询操作）

事务的四大特性

原子性：事务中的操作为一个整体，要么都做，要么都不做.即一旦事务出错，就回滚事务
一致性：执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。写入物理数据库一部分，中途中断，属于不一致状态
隔离性：一个事务的执行不能被其他事务干扰。
持久性：一个事物一旦提交，它对数据库中数据的改变就应该是永久性的。

事务的隔离级别

并发事务问题
1、脏读：一个事务读取了另外一个事务未提交的数据。
2、不可重复读：一个事务读取同一行数据，因其他事务修改这一行数据并已提交，导致多次读取结果不同。
3、幻读：一个事务读取了别的事务插入的数据，导致前后读取的数据总量不一致。

隔离级别	脏读	不可重复读	幻读
read uncommitted(读未提交)	√	√	√
read committed(读已提交)	×	√	√
repeatable read(可重复读)	×	×	√
serializable(串行化)	×	×	×

语法： set session transaction isolation level read uncommitted;

常见的存储引擎

InnoDB：支持事务、行级锁、外键
MyISAM：不支持事务、外键，支持表锁
Memory

存储引擎如何选择

InnoDB：支持事务、外键。对事务的完整性有较高要求，并发条件下要求数据的一致性，除新增和查询外，还包含很多更新和删除操作
MyISAM：主要以新增和查询操作为主，对事物的完整性、并发性要求不高，偶尔丢几条数据也没关系的情况下（操作日志、评论）
Memory：保存在内存中，访问速度快，通常用于缓存，无法保证数据的安全性，一般被redis替代

什么是索引

帮助MySQL高效获取数据的数据结构（有序）

索引结构

B+Tree：大部分引擎都支持B+树索引
Hash索引：底层使用哈希表实现，不支持范围查询，只有精确匹配
R-Tree：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型
Full-Text：全文索引，快速全文匹配

InnoDB存储引擎为什么选择B+Tree索引结构

相对于二叉树，相同数据量下，层级更少，搜索效率更高

相对于B-Tree，无论叶子节点还是非叶子节点，都会保存数据，这样会导致一个节点/一页（16kb）存储的key值减少，相同数据量下，树的高度增加，性能降低，B+Tree的叶子节点是个双向链表，支持范围查询和排序操作

相对Hash索引，只支持精确匹配，不支持范围查询和排序操作

索引分类

主键索引（primary）：默认创建，只能有一个
唯一索引（unique）：避免同一个表某数据列中数据重复，可以有多个
全文索引（fulltext）：全文查找文本中的关键词

InnoDB索引的存储形式

聚集索引：一般是主键，叶子节点保存了行数据，必须有且只有一个
二级索引：叶子节点保存的是对应的主键，可以存在多个

索引失效情况

对列进行运算或使用函数时
数据类型不匹配（“”）
where中使用or，只要一列没有索引，那么其他列都会失效
使用<>、like以%开头

锁

1、全局锁

对整个数据库实例进行加锁，加锁后整个实例就处于只读状态。

语法：flush tables with read lock;

2、表级锁

表锁分为两类：

2.1、表共享读锁：不会阻塞其他客户端的读，但会阻塞写

语法：lock tables table_names read;

2.2、表独占写锁：既会阻塞其他客户端的读，又会阻塞写

语法：lock tables table_names write;

2.3、元数据锁（DML）：加锁过程系统自动控制，当有活动事务时，不可以对元数据（表结构）进行写入操作，为了避免DML与DDL冲突，保证读写的正确性

2.4、意向锁：为了避免DML在执行时，加的行锁与表锁的冲突，使用意向锁减少了对表数据逐行检查是否加锁

3、行级锁

3.1、行锁：两种类型

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
排他锁（X）：允许排他锁的事务去更新数据，阻止其他事务获得相同数据及的共享锁和排他锁

通过唯一索引进行检索时，自动优化为行锁，不通过索引条件检索数据，就会升级为表锁

3.2、间隙锁

索引上的等值查询（唯一索引），给不存在的记录加锁时，自动优化为间隙锁