分类 标签 存档 社区 博客 友链 GitHub 订阅 搜索

Java - MySQL

162 浏览

ZERO

    持续更新 请关注:https://zorkelvll.cn/blogs/zorkelvll/articles/2018/12/25/1545743442945

背景

     本文主要是记录在学习 Java - MySQL 过程中的一些知识点备忘!

20181225

1、存储引擎 - MyISAM 与 InnoDB

MyISAM 更适合读密集的表,InnoDB 更适合写密集的表;

MyISAM:5.5 版之前的默认数据库引擎

  • 不支持行锁(只有表锁)
  • 不支持事务
  • 不支持外键
  • 不支持崩溃后的安全恢复
  • 在表有读取查询的同时,支持往表中插入新纪录
  • 支持 BLOB 和 TEXT 的前 500 个字符索引,支持全文索引
  • 支持延迟更新索引,极大地提升了写入性能
  • 对于不会进行修改的表,支持压缩包,极大地减少了磁盘空间的占用

锁是计算机协调多个进程或纯线程并发访问某一资源的机制

表级索:每次操作锁住整张表;开销小,加锁块;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低

行级锁:每次操作锁住一行数据;开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高

InnoDB:5.5 版之后的默认数据库引擎,其最大特色就是支持了 ACID 兼容的事务功能

  • 支持事务
  • 支持行锁,采用 MVCC 来支持高并发,有可能死锁
  • 支持外键
  • 支持崩溃后的安全恢复
  • 5.5 不支持全文索引,5.6 开始支持 FULLTEXT 索引(CHAR\VARCHAR\TEXT)

count 运算上的区别:MyISAM 缓存有表 meta-data(行数等),因此在做 COUNT(*) 时对于一个结构很好的查询是不需要消耗多少资源的;而对于 InnoDB 来说,则没有这种缓存

是否支持事务和崩溃后的安全恢复:MyISAM 强调的是性能,每次查询具有原子性,其执行速度比 InnoDB 类型更快,但不提供事务支持。但是 InnoDB 提供事务、外部键等高级数据库功能,具有事务、回滚和崩溃恢复能力的事务安全型表

一般来说,如果需要事务支持,并且有较高的并发读取频率 (MyISAM 的表锁的粒度太大,所以当该表写并发量较高时,要等待的查询就会很多了),InnoDB 是不错的选择。如果你的数据量很大(MyISAM 支持压缩特性可以减少磁盘的空间占用),而且不需要支持事务时,MyISAM 是最好的选择。

2、字符集及校对规则

字符集指的是一种从二进制编码到某类字符符号的映射,校对规则则是指某种字符集下的排序规则;MySQL 中每一种字符集均会对应一系列的校对规则

MySQL 采用类似继承的方式指定字符集的默认值,每个数据库以及每张数据表均有自己的默认值,逐层继承!

3、索引

MySQL 索引使用的数据结构主要有 BTree 索引和哈希索引;对于哈希索引来说,底层的数据结构就是哈希表,因此在绝大多数需求为单条记录查询的时候,可以选择哈希索引,查询性能最快,其他大部分场景建议选择 BTree 索引

MySQL 的 BTree 索引使用的是 B 树中的 B+Tree,且对于两种存储引擎的实现方式是不同的:

MyISAM:B+Tree 叶节点的 data 域存放的是数据记录的地址;在索引检索的时候,首先按照 B+Tree 搜索算法搜索索引,如果指定的 Key 存在则取出其 data 域的值,然后以 data 域的值为地址读取相应的数据记录!被称为 “非聚簇索引”!

InnoDB: 其数据文件本身就是索引文件。相比 MyISAM,索引文件和数据文件是分离的,其表数据文件本身就是按 B+Tree 组织的一个索引结构,树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键,因此 InnoDB 表数据文件本身就是主索引。被称为 “聚簇索引”!而其余的索引都作为辅助索引,辅助索引的 data 域存储相应记录主键的值而不是地址,这也是与 MyISAM 不同的地方。

在根据主索引搜索时,直接找到 key 所在的节点即可取出数据;在根据辅助索引查找时,则需要先取出主键的值,在走一遍主索引。因此,在设计表的时候,不建议使用过长的字段作为主键,也不建议使用非单调的字段作为主键,这样会造成主索引频繁分裂。

索引失效的情况:

条件中有 OR、LIKE 查询以 “%” 开头、使用了函数、……

4、查询缓存的使用

开启缓存配置,在 my.cnf 中加入以下配置,重启 MySQL 开启查询缓存

query_cache_type=1
query_cache_size=60000

MySQL 执行以下命令也可以开启查询缓存

set global  query_cache_type=1;
set global  query_cache_size=600000;

5、事务机制

关系型数据库需要遵循 ACID(原子性、一致性、隔离性、持久性)规则

  • 原子性:事务是最小的执行单位,不允许分割。事务的原子性确保动作要么全部完成,要么完全不起作用
  • 一致性:执行事务前后,数据保持一致
  • 隔离性:并发访问数据库时,一个用户的事务不能被其他事务所干扰,各并发事务之间数据库是独立的
  • 持久性:一个事务被提交之后,它对数据库中数据的改变是持久的,即使数据库发生故障也不应该对其有任何影响

为了达到上述事务特性,数据库定义了几种不同级别的事务隔离级别:

  • READ_UNCOMMITTED 未授权读取:最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读
  • READ_COMMITED 授权读取:允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复仍有可能发生
  • REPEATABLE_READ 可重复读:对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生
  • SERIALIZABLE 串行:最高的隔离级别,完全服从 ACID 的隔离级别,所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰。也就是说,该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能,通常情况下也不会用到该级别

MySQL 默认采用的是可重复读隔离级别,Oracle 默认采用的是授权读取隔离级别

事务隔离机制的实现基于锁机制和并发调度,其中并发调度使用的是 MVVC(多版本并发控制),通过保存修改的旧版本信息来支持并发一致性读和回滚等特性

6、大表优化

限定数据的范围、读写分离、缓存、垂直分区、水平分区

说明:本 JavaGuide 系列博客为来源于https://github.com/Snailclimb/JavaGuide 等学习网站或项目中的知识点,均为自己手打键盘系列且内容会根据继续学习情况而不断地调整和完善!

评论  
留下你的脚步
推荐阅读