MySQL高级
MySQL逻辑架构
MySQL的架构可以分为四层: 1. 连接层; 2. 服务层(进行SQL语句相关操作); 3. 引擎层(支持可插拔的引擎); 4. 存储层
MyISAM引擎和InnoDB引擎的对比
| — | MyISAM | InnoDB |
|---|---|---|
| 主键,外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 锁 | 支持表级锁,即锁住整个表 | 支持行级锁,对同表的其他行没有影响,适合高并发 |
| 缓存 | 只缓存索引,不缓存真实数据 | 不仅缓存索引, 还会缓存真实数据,所以对内存需求高 |
| 表空间 | 小 | 大 |
| 关注点 | 性能 | 事务 |
SQL语句的执行流程
SQL的执行是从FROM开始的
以查询语句为例
- 检查权限
- 查询缓存
- 缓存没命中, 分析器提取FROM查询表,提取SELECT语句类型,优化器决定判断条件的执行顺序,执行器执行SQL,调用引擎获取数据
以更新(增,删,改)为例
- 检查权限
- 查询缓存
- 调用引擎接口,修改数据,写入内存,记录日志
- 执行器接收通知,调用引擎,提交内容
事务
spring事务传播机制
在spring的注解声明式事务中, 有四大重要属性 1. 事务的传播行为, 2. 事务的隔离级别, 3. 事务的超时值, 4. 事务的只读标志, 这些属性在TransactionDenfition接口中被定义
spring中定义的7种事务传播行为
事务传播行为指的是事务方法A被另一个事务方法B调用的时候,这个事务方法A应该如何进行
- PROPAGATION_REQUIRED: 表示当前方法必须运行在事务中,如果当前事务存在,方法会在该事务中进行, 如果不存在, 新建事务
- PROPAGATION_SUPPORTS: 表示当前方法不需要事务上下文, 但是如果存在当前事务, 那么方法还是在事务中进行
- PROPAGATION_MANDATORY: 表示该方法必须在事务中执行, 如果不存在事务, 抛出异常
- PROPAGATION_REQUIRED_NEW: 表示当前方法必须运行在它自己的事务中,一个新的事务将被启动, 如果存在当前事务,在方法执行期间, 当前事务会被挂起, 先执行自己的事务
- PROPAGATION_NOT_SUPPORTED: 表示该方法不应该运行在事务中,如果存在当前事务, 在该方法运行期间, 当前事务会被挂起.
- PROPAGATION_NEVER: 表示当前方法不应该运行在事务上下文中, 如果当前正有一个事务在运行, 则会抛出异常
- PROPAGATION_NESTED: 表示如果当前已经存在一个事务, 那么该方法会在嵌套事务内运行, 嵌套的事务可以独立于当前事务进行单独的提交或回滚. 如果当前事务不存在, 它会新建事务.
事务隔离级别
可重复读是mysql的默认事务隔离级别,它保证了不会脏读和不可重复读, 但是不能解决幻读. 还有其他三种, 分别是读未提交, 读已提交,和串行化.
事务的只读标志
当方法被声明为readonly只读时, 相当于数据库开启了只读数据库, 这时候不会有其他修改操作, 保证了数据的一致性.
MySQL之关系型数据库三大范式
- 第一范式: 字段是原子性不可分的
- 第二范式: 有主键,非主键字段依赖主键
- 第三范式: 非主键字段之间不能相互依赖, 不存在传递依赖, 即数据不冗余
SQL调优
索引
索引一般可以加开数据的检索速度,加速表与表之间的链接
使用索引有以下几个原则:
- 当插入数据为表数据的百分之10以上时, 要先删除索引提高插入效率, 然后在重建索引
- 避免在索引上使用函数以及计算, 如果索引使用了计算, 优化器将不再使用索引, 索引将失效
- 尽量避免在索引上使用not,!=,<>, 索引只知道什么在表中而不知道什么不在表中, 上述符号会进行全表扫描
- 检索中不要对索引进行处理, 比如TRIM, TO_DATE, 类型转换, 这会破坏索引
- 避免在索引上使用IS NULL 或 IS NOT NULL, 避免在索引上使用任何为空的列. 这样无法使用此索引.因为空值不在索引中
SQL语句优化
- 建立合理的索引,首先考虑where,order by的列上建索引. 不要在索引上使用计算
- WHERE中的链接顺序, 将过滤量大的条件写在where的末尾
- 在limit分页查询时, 可以记录上一页的最大值, 并使用最大值作为索引查询的参数. 因为索引并不知道第几条记录在哪, 它会重新计算一次
- 可以让数据部分冗余, 避免join查询
- 尽量不使用
SELECT * - 使用JOIN代替子查询
- 使用联合UNION来代替临时表
- 可以适当反范式, 减少表的关联更好的优化查询效率,但会导致数据冗余, 修改需要更多代价
- 用EXISTS替代IN
B+树结构特点
- 非叶子节点不保存数据
- 每个节点保存多个元素, 减少时间复杂度
- 所有的叶子节点构成有序链表, 便于范围查询