Java - MySQL
image
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
等学习网站或项目中的知识点，均为自己手打键盘系列且内容会根据继续学习情况而不断地调整和完善！
