MySQL+Mybatis

原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；由undo log日志保证
一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；一般由代码层面来保证
隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；由MVCC和锁来保证
持久性（Durabilily）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响；由内存+redo log来保证

只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的！

3、事务中并发一致性的问题

脏读（Dirty read）: T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。

丢失修改（Lost to modify）:T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。

不可重复读（Unrepeatable read）: T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

幻读（Phantom read）: T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

不可重复读和幻读有什么区别呢？

不可重复读的重点是内容修改；
幻读的重点在于记录新增和删除；

4、事务的隔离级别

读取未提交(READ UNCOMMITTED) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
读取已提交(READ COMMITTED) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。（是Oracle的默认隔离级别。）
可重复读(REPEATABLE READ) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。（是MySQL的默认隔离级别。）
可串行化(SERIALIZABLE) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL的锁

1、按锁的粒度分类：

表级锁：开销小，加锁快；锁定的粒度大，锁冲突的概率较高，并发度低，不会出现死锁；MyISAM 和 InnoDB 引擎都支持表级锁。
行级锁：开销大，加锁慢；锁定的粒度小，锁冲突的概率较低，并发度高，能大大的减少数据库操作的冲突。只有InnoDB 引擎支持行级锁，且默认为行级锁。

2、按锁的类型分类：

读写锁：

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：

共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取。
排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

意向锁：

未来的某个时刻，事务可能要加共享或者排它锁时，先提前声明一个意向。意向锁是表级锁，意向锁之间是互相兼容的。

意向锁是由储存引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些加共享锁，加共享锁前必须先取得该表的 IS 锁。
意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是怎么解决效率低的问题？

如果一个事务A获取到某一行的排他锁，并未提交,这时候表上就有意向排他锁和这一行的排他锁。这时候事务B想要获取这个表的共享锁，此时因为检测到事务A持有了表的意向排他锁，因此事务A必然持有某些行的排他锁，也就是说事务B对表的加锁请求需要阻塞等待，不再需要去检测表的每一行数据是否存在排他锁啦。

3、InnoDB中行锁定的方式

记录锁 ：仅仅锁住索引记录的一行，在单条索引记录上加锁。锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。
- 如：select c1 from t where c1 = 10 for update，如果c1字段是主键或者是唯一索引的话，这个SQL会加一个记录锁
间隙锁 ：也叫区间锁, 仅仅锁住一个索引区间（不包括双端端点）；间隙锁可用于防止幻读，保证索引间的不会被插入数据
临键锁 ：临键锁是记录锁与与间隙锁的结合，是个左开右闭的区间；
自增锁：是一种特殊的表级别锁，值专门针对AUTO_INCREMENT类型的列。如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

4、乐观锁、悲观锁

乐观锁：总是假设最好的情况，每次拿数据的时候，都认为别人不会修改。但是在更新数据的时候，会判断再次期间有没有人去修改这个数据。乐观锁适用于读操作多的场景，这样可以提高程序的吞吐量。

可以基于CAS（Java层面）和版本号（数据库层面）进行实现

悲观锁：认为被它保护的数据是极其不安全的，每时每刻都有可能变动，一个事务拿到悲观锁后，其他任何事务都不能对该数据进行修改，只能等待锁被释放才可以执行。悲观锁适用于写操作多的场景，这样可以确保数据的顺序执行。

数据库中的行锁，表锁，读锁，写锁，以及syncronized实现的锁均为悲观锁

MySQL的储存引擎

MySQL 存储引擎采用的是插件式架构，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

MyISAM与InnoDB的对比：

行级锁：MyISAM 只有表级锁；InnoDB 支持行级锁和表级锁，默认为行级锁。
事务：MyISAM 不提供事务支持；InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交(commit)和回滚(rollback)事务的能力。
外键：MyISAM 不支持，而 InnoDB 支持。
数据库异常崩溃后的安全恢复：MyISAM 不支持，而 InnoDB 支持，依赖于 redo log。
MVCC：MyISAM 不支持，而 InnoDB 支持。
索引实现方式：俩者都是使用 B+Tree 作为索引结构；但是两者的实现方式不太一样。InnoDB：其数据文件本身就是索引文件，储存在 .idb 的文件中。
MyISAM：索引文件和数据文件是分离的，索引储存在 .myi 的文件中，数据储存在 .myd 的文件中

索引

1、索引的概念：

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。

索引的作用就相当于书的目录。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

2、索引的优缺点：

优点：

使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点：

创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
索引需要使用物理文件存储，也会耗费一定空间。

3、MySQL索引的B+树

要介绍B+树索引，就不得不提二叉查找树，平衡二叉树和B树这三种数据结构。B+树就是从他们仨演化来的。

二叉查找树：

任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点。

缺点：当数据sahib有序递增，此时二叉树就变为了链表，查询就会变成全表扫描，导致查询的效率降低。

平衡二叉树：

在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过1。

缺点：二叉树的节点将会非常多，高度也会及其高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低。

B树

B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。

基于这个特性，B树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。

B+树：

1、B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。 （因为在数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。）

2、因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的；各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。

4、索引的类型

主键索引：

数据表的主键列使用的就是主键索引。

一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在null值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

在Innodb下主键索引是聚集索引，在Myisam下主键索引是非聚集索引

辅助索引（二级索引）：

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

唯一索引(Unique Key) ：

唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。

普通索引(Index) ：

普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。

前缀索引(Prefix) ：

前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。

全文索引(Full Text) ：

全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

联合索引：

是指对表上的多个列进行索引。联合索引也是一棵B+树，不同的是联合索引的键值数量不是1，而是大于等于2。

5、聚集索引与非聚集索引

聚集索引：

聚集索引即索引结构和数据一起存放的索引。InnoDB储存引擎中的主键索引属于聚集索引。在 MySQL 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

优点：

聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

缺点：

依赖于有序的数据：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

非聚集索引：

非聚集索引即索引结构和数据分开存放的索引。二级索引属于非聚集索引。非聚集索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

优点：

更新代价比聚集索引要小。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的

缺点：

跟聚集索引一样，非聚集索引也依赖于有序的数据
可能会二次查询（回表）：这应该是非聚集索引最大的缺点了。当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询

非聚集索引一定回表查询吗?

6、覆盖索引、回表

覆盖索引：

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。

再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引，那么直接根据这个索引就可以查到数据，也无需回表。

回表：

如果一个查询语句的执行，需要检索两遍索引，这个过程称为回表。（首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录）。

7、联合索引、最左前缀匹配原则

联合索引：

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。

最左前缀匹配原则：

在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询。如 >、<、between 和 以%开头的like查询 等条件，才会停止匹配。

所以，我们在使用联合索引时，可以将区分度高的字段或场用于查询的字段放在索引的最左边，这也可以过滤更多数据。

8、索引下推

索引下推是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

9、创建索引的注意事项：

1.选择合适的字段创建索引：

不为 NULL 的字段
被频繁查询的字段
被作为条件查询的字段
频繁需要排序的字段
被经常频繁用于连接的字段

2.被频繁更新的字段应该慎重建立索引。

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

3.尽可能的考虑建立联合索引而不是单列索引。

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

4.注意避免冗余索引 。

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

5.考虑在字符串类型的字段上使用前缀索引代替普通索引。

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

10、使用索引的一些建议

对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
应尽量避免在 where 子句中对字段进行 null 值判断。
应尽量避免在 where 子句中使用 != 或 <> 操作符。
应尽量避免在 where 子句中使用 or 来连接条件，尽量使用union all代替
in 和 not in 也要慎用，否则会导致全表扫描。对于连续的数值，能用 between 就不要用 in 了。如果是子查询，可以用exists代替in。
应尽量避免在where子句中对字段进行函数操作
不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算

11、MySQL 如何为表字段添加索引

1.添加 PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加 UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

3.添加 INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加 FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

数据类型

1、整型

tinyint、smallint、mediumint、int、bigint 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。

int(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。

2、浮点数

float 和 double为浮点类型，decimal为高精度小数类型。都可以指定列宽，例如 double(18, 9) 表示总共 18 位，取 9 位存储小数部分，剩下 9 位存储整数部分。

3、字符串

主要有 char和 varchar 两种类型。

varchar：长度是可变的，不会保留字符串末尾的空格；适用于存储多字节的字符、经常不更新的字符串、长度波动大的字符串。
char：长度是固定的，会保留字符串末尾的空格；适用于存储短的字符串、经常更新的字符串、长度波动不大的数据。

4、时间和日期

MySQL 提供了两种相似的日期时间类型: datetime和 timestamp。

datetime：能够保存从 1001 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间。它与时区无关。
timestamp：和 UNIX 时间戳相同，只能表示从 1970 年到 2038 年，使用 4 个字节，它和时区有关。默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。

MVCC

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读

1、当前读和快照读

当前读

像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

快照读

像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

2、MVCC解决的问题

数据库并发场景有三种, 分别为：

读-读：不存在任何问题，也不需要并发控制
读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。所以MVCC可以为数据库解决以下问题

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

3、InnoDB 对 MVCC 的实现

MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性。如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改。

隐藏字段：

在内部，InnoDB 存储引擎为每行数据添加了三个隐藏那个字段：

DB_TRX_ID（6字节）：表示最后一次插入或更新该行的事务 id。此外，delete 操作在内部被视为更新，只不过会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除
DB_ROLL_PTR（7字节） 回滚指针，指向该行的 undo log 。如果该行未被更新，则为空
DB_ROW_ID（6字节）：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引

ReadView：

主要是用来做可见性判断，里面保存了 “当前对本事务不可见的其他活跃事务”

undo-log：

undo log 主要有两个作用：

当事务回滚时用于将数据恢复到修改前的样子
另一个作用是 MVCC ，当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现非锁定读

在 InnoDB 存储引擎中 undo log 分为两种：

insert undo log ：指在 insert 操作中产生的 undo log。因为 insert 操作的记录只对事务本身可见，对其他事务不可见，故该 undo log 可以在事务提交后直接删除。不需要进行 purge 操作
update undo log ：update 或 delete 操作中产生的 undo log。该 undo log可能需要提供 MVCC 机制，因此不能在事务提交时就进行删除。提交时放入 undo log 链表，等待 purge线程 进行最后的删除

性能优化

1、Mysql慢查询的定位和分析

1、慢SQL定位：需要在MySQL的配置文件中（/etc/my.conf）开启慢查询日志的功能，这样Mysql会把查询时间大于你配置文件中设置时间的SQL记录下来，并且保存到一个专门的文件中。

slow_query_log = ON
long_query_time = 5
slow_query_log_file = /opt/soft/mysql/log/slow.log
log_queries_not_using_indexes=on

2、SQL性能分析（使用explain关键字）：

列名	描述
id	select标识符, id相同时执行顺序从上到下, 在所有组中, id值越大, 优先级越高, 越先执行
select_type	select关键字所对应的查询类型
table	这条查询所涉及到的表
partitions	匹配的分区信息
type	针对单表的访问类型
possible_keys	该条查询所可能用到的索引
key	实际使用的索引
key_len	实际使用索引的长度
ref	显示关联的字段；如果使用常数等值查询，则显示const；如果是连接查询，则会显示关联的字段.
rows	预估所需要读取的数据条数（该值越小约好）
filtered	该表经过过滤条件后，所剩条数的百分比
extra	额外的信息

type类型的取值（性能依次提升）：

1、all： 全表扫描.

2、index：全索引扫描. index与all区别为index只遍历索引树, 通常比all快, 因为索引文件通常比数据文件小.

3、range：只检索给定范围的行, 使用一个索引来检索行, 可以在key列中查看使用的索引, 一般出现在where条件中

4、ref：非唯一性索引扫描, 返回匹配某个单独值的所有行。

5、eq_ref：类似ref，区别在于使用的索引是唯一索引, 对于每个索引键值, 表中只有一条记录匹配, 常见于主键或唯一索引扫描.

6、const：基于主键或唯一索引查询，只返回一条数据；

7、system: 查询数据的表中只有一行数据

extra类型的取值：

库表设计

1、数据库命令规范

所有数据库对象名称必须使用小写字母并用下划线分割
所有数据库对象名称禁止使用 MySQL 保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）
数据库对象的命名要能做到见名识意，并且最后不要超过 32 个字符
临时库表必须以 tmp_为前缀并以时间戳为后缀，备份表必须以 bak_为前缀并以时间戳为后缀
所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）

2、数据库基本设计规范

没有特殊要求的情况下，所偶有的包必须使用InnoDB的储存引擎
数据库和表的字符集统一使用 UTF8
所有表和字段都需要添加注释
尽量控制单表数据量的大小,建议控制在 500 万以内。
谨慎使用 MySQL 分区表
尽量做到冷热数据分离,减小表的宽度
禁止在表中建立预留字段
禁止在数据库中存储图片,文件等大的二进制数据
禁止在线上做数据库压力测试
禁止从开发环境,测试环境直接连接生产环境数据库

3、数据库字段设计规范

优先选择符合存储需要的最小的数据类型

原因：

列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的 IO 次数也就越多，索引的性能也就越差。

a.将字符串转换成数字类型存储,如:将 IP 地址转换成整形数据

MySQL 提供了两个方法来处理 ip 地址：

inet_aton 把 ip 转为无符号整型 (4-8 位)
inet_ntoa 把整型的 ip 转为地址

b.对于非负型的数据 (如自增 ID,整型 IP) 来说,要优先使用无符号整型来存储

无符号相对于有符号可以多出一倍的存储空间

同财务相关的金额类数据必须使用 decimal 类型

Decimal 类型为精准浮点数，在计算时不会丢失精度，占用空间由定义的宽度决定，每 4 个字节可以存储 9 位数字，并且小数点要占用一个字节，可用于存储比 bigint 更大的整型数据。

非精准浮点：float,double
精准浮点：decimal

使用 TIMESTAMP(4 个字节) 或 DATETIME 类型 (8 个字节) 存储时间

4、索引设计规范

限制每张表上的索引数量,建议单张表索引不超过 5 个
禁止给表中的每一列都建立单独的索引
每个 Innodb 表必须有个主键
如何选择索引列的顺序

区分度最高的放在联合索引的最左侧
尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好）
使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）

对于频繁的查询优先考虑使用覆盖索引，覆盖索引的好处：

避免 Innodb 表进行索引的二次查询
可以把随机 IO 变成顺序 IO 加快查询效率

MySQL 基本架构

连接器： 身份认证和权限相关(登录 MySQL 的时候)。
查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器： 按照 MySQL 认为最优的方案去执行。
执行器： 执行语句，然后从存储引擎返回数据。

总结：

MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用，redolog 只有 InnoDB 有。
引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。
查询语句的执行流程如下：权限校验（如果命中缓存）--->查询缓存--->分析器--->优化器--->权限校验--->执行器--->引擎
更新语句执行流程如下：分析器---->权限校验---->执行器--->引擎---redo log(prepare 状态)--->binlog--->redo log(commit状态)

MySQL的日志

MySQL 日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中，比较重要的还要属 binlog（归档日志）和 redo log（重做日志/事务日志）和 undo log（回滚日志）。

1、Buffer Pool

Buffer Pool 是 InnoDB 存储引擎中的一个内存组件，里面缓存了磁盘上的真实数据，Java系统对数据库的增删改操作，主要是在这个缓存中执行的操作。

Buffer Pool的默认大小是128M。在实际的生产环境中可以通过参数innodb_buffer_pool_size对 buffer pool进行调整。

2、数据页

数据库的核心数据模型是表 + 字段 + 行的概念。数据库对数据抽象出一个数据页的概念，将很多行的数据放到一个数据页中。即磁盘中有很多数据页，每个数据页存放很多行数据。

当要更新一行数据的时候，数据库会找到这行数据所在的数据页，然后从磁盘文件中把这行数据所在的数据页加载到 Buffer Pool 中。即Buffer Pool中存放的是一个一个的数据页。

3、缓存页

默认情况下，磁盘中存放的数据页的大小是16KB，即一页数据包含16KB的内容。

Buffer Pool中存放的一个一个的数据页，通常叫做缓存页。（Buffer Pool是一个缓冲池，里面的数据是从磁盘缓存到内存中的）

Buffer Pool中默认情况下，一个缓存页的大小和磁盘上一个数据页的大小是一一对应的，都是16KB。

4、 `redo log`（重做日志/事务日志）

它是物理日志，是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。
它保证事务的持久性
它记录的是数据修改之后的值，不管事务是否提交都会记录下来。

5、`undo log`（回滚日志）

它记录的是数据被修改前的样子，主要用于事务在发生异常时，可以回滚事务。
它保证事务的原子性。
每次写入数据或者修改数据之前都会把修改前的信息记录到 undo log，并将undo log保存到磁盘。

6、binlog（归档日志）

它会记录对这张表的整个操作，并且是顺序写；为主从复制和数据恢复提供了保证。
它保证了数据的一致性。
它是逻辑日志，记录内容是SQL语句原文，比如执行一条update T set update_time=now() where id=1。属于MySQL Server 层。不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。

常见的问题

1、一个完整SQL查询语句的书写顺序

-- "mysql语句编写顺序"
1 select distinct *
2 from 表(或结果集)
3 where …
4 group by …having…
5 order by …
6 limit start,count
-- 注：1、2属于最基本语句，必须含有。
-- 注：1、2可以与3、4、5、6中任一搭配，也可以与3、4、5、6中多个同时搭配。

2、一个完整的SQL语句执行顺序

3、自增、UUID、雪花算法ID对比

自增 ID：有序 ID

优势：性能好、存储内容少、不会有页裂变问题、易读
劣势：分布式存储比较麻烦、mysql 有自增的 id 锁会有一定性能损耗（相对的）、容易被猜测数据被爬虫爬取

要支持分布式也可以，就是分表分库的时候设置不同的起始递增的位置
比如：表 A1 起始 101000000000，表 A2 起始 102000000000，这样 ID 就不会相同，预留 10 亿的数据，前面三位作为区分不通表或者机器

UUID：无序随机 ID，时间戳 + 随机数 + 机器 MAC 等信息

优势：全网唯一、适合分布式分库分表存储、ID 没有规律不会被恶意猜测
劣势：性能 / 性能 / 性能、随机导致数据库存储大量的页 (插入性能会有比较大的影响，数据量非常大的时候查询也会有影响)、存储内容也较大（多了一倍多的存储空间）

关于 UUID 还有一种有序 UUID，有序 UUID 会有更好的性能，Hibernate 依赖包中有关于有序 UUID 的实现类：org.hibernate.id.UUIDHexGenerator。

如果项目中一定要使用 UUID 的话建议使用有序 UUID。

雪花算法：Snowflake，时间戳 + 随机数 + 服务器标记

优势：性能较好、有序、全局唯一适合分布式场景
劣势：存储内容较长、前端 Long 转换有精度丢失问题（需要实现 Json 转换解决精度问题）、服务器时钟回拨问题导致重复 ID (概率较低)、插入性能没有自增 ID 好

mybatisPlus的 IdWorker.getId() 就是使用雪花算法

4、mysql 插入数据后返回自增主键

select LAST_INSERT_ID() as id;
使用max(id)；不适用于高并发场景

5、MySQL的主键ID为啥要自增

前提是InnoDB 引擎，避免页分裂。
底层的数据结构是B+Tree，左节点<根节点<右节点，一个数据页存满了，mysql 就会去申请一个新的数据页来存储数据。

MyCat

1、作用

读写分离：

数据分片：

多数据源整合 ：

2、原理：

Mycat 的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的 SQL 语句，首先对 SQL 语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此 SQL 发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。

这种方式把数据库的分布式从代码中解耦出来，程序员察觉不出来后台使用 Mycat 还是 MySQL。

3、安装、启动

安装：

解压缩文件拷贝到 linux 下 /usr/local/ （ 解压后即可使用）

启动：
- 控制台启动：去 mycat/bin 目录下执行 ./mycat console
- 后台启动：去 mycat/bin 目录下 ./mycat start

4、核心概念

逻辑库：

schema,数据库中间件可以被当做一个或者多个数据库集群构成的逻辑库。

逻辑表：

table, 在MyCat中，对于应用来说，读写数据的表就是逻辑表。逻辑表可以进行分片，也可以不分。

分片表：order表按照规则将数据分到dn1和dn2两个节点 (`<table name="order" dataNode="dn1"/>`)
非分片表：order表只将数据分到dn1节点
ER表：基于E-R关系进行分片，子表的记录与其关联的父表的记录保存在同一个分片上，这样关联查询就不需要跨库进行查询
全局表：应用中可能存在一些数据变动很小的表，如字典表等，这些表的数据量不会很大，也很稳定。全局表存在于所有分片上，方便业务表与其进行关联查询，所有的分片都保存了一份全局表的数据。

分片节点（dataNode）：

将一个大表拆分到多个分片数据库节点上，每个分片上面的数据库就是一个分片节点。

节点主机（dataHost）:

一个或者多个分片节点所在的主机就是节点主机，同一个节点主机上面可以有多个分片数据库。

分片规则 (rule)：

就是拆分的算法, 如何来拆分呢? 就要用到分片规则, mycat有10中分片规则, 基本上我们的应用场景都包含在内了, 这方面mycat做的很赞!

5、schema.xml配置文件

作用：

涵盖了MyCat的逻辑库、表、分片规则、分片节点及数据源的配置。

配置参数：

1、schema 标签：

<schema name="ITCAST" checkSQLschema="false" sqlMaxLimit= "100">
	<table name="TB_TEST" dataNode="dn1 ,dn2 ,dn3" rule="auto-sharding-long" />
</schema>

schema 标签用于定义 MyCat实例中的逻辑库 , 一个MyCat实例中 , 可以有多个逻辑库 , 可以通过 schema 标签来划分不同的逻辑库。MyCat中的逻辑库的概念，等同于MySQL中的database概念 , 需要操作某个逻辑库下的表时 , 也需要切换逻辑库。

use ITCAST;

schema 标签的属性如下 :

name：指定逻辑库的库名 , 可以自己定义任何字符串 ;
checkSQLschema： 取值为 true / false

如果设置为true时 , 如果我们执行的语句为 "select * from ITCAST.TB_TEST;" , 则MyCat会自动把schema字符去掉 , 把SQL语句修改为 "select * from TB_TEST;"。可以避免SQL发送到后端数据库执行时 , 报table不存在的异常。
不过当我们在编写SQL语句时 , 指定了一个不存在schema, MyCat是不会帮我们自动去除的 ,这个时候数据库就会报错 ,所以在编写SQL语句时 ,最好不要加逻辑库的库名 , 直接查询表即可。

sqlMaxLimit：当该属性设置为某个数值时 ,每次执行的SQL语句如果没有加上limit语句 , MyCat也会自动在limit语句后面加上对应的数值。

也就是说，如果设置了该值为100 ，则执行 select * fromTB_TEST 与 select * from TB_TEST limit 100 是相同的效果。
所以在正常的使用中 , 建立设置该值 , 这样就可以避免每次有过多的数据返回。

2、schema 标签的子标签table：

<table name="TB_TEST" dataNode="dn1 ,dn2 ,dn3" rule="auto-sharding-long" />

table 标签定义了MyCat中逻辑库schema下的逻辑表 , 所有需要拆分的表都需要在table标签中定。

table标签属性如下：

name：定义逻辑表的表名 , 在该逻辑库下必须唯一。
dataNode：定义的逻辑表所属的dataNode , 该属性需要与dataNode标签中的name属性的值对应。如果一张表拆分的数据，存储在多个数据节点上，多个节点的名称使用","分隔。
rule：该属性用于指定逻辑表的分片规则的名字 , 规则的名字是在rule.xml文件中定义的 , 必须与 tableRule标签中name属性对应。
ruleRequired：该属性用于指定表是否绑定分片规则 , 如果配置为true, 但是没有具体的rule, 程序会报错。
primaryKey：逻辑表对应真实表的主键

如 : 分片规则是使用主键进行分片 , 使用主键进行查询时 , 就会发送查询语句到配置的所有的datanode上; 如果使用该属性配置真实表的主键 , 那么MyCat会缓存主键与具体datanode的信息 , 再次使用主键查询就不会进行广播式查询了 , 而是直接将SQL发送给具体的datanode。

type：该属性定义了逻辑表的类型，目前逻辑表只有全局表和普通表。
- 全局表：type的值是 global , 代表全局表。
- 普通表：无
autoIncrement：

mysql对非自增长主键，使用last_insert_id() 是不会返回结果的，只会返回0。所以，只有定义了自增长主键的表，才可以用last_insert_id()返回主键值。 mycat提供了自增长主键功能，但是对应的mysql节点上数据表，没有auto_increment,那么在mycat层调用last_insert_id()也是不会返回结果的。
如果使用这个功能，则最好配合数据库模式的全局序列。使用 autoIncrement="true" 指定该表使用自增长主键 ,这样MyCat才不会抛出 "分片键找不到" 的异常。
autoIncrement的默认值为false。

needAddLimit：指定表是否需要自动在每个语句的后面加上limit限制 , 默认为true。

3、dataNode 标签

<dataNode name="dn1" dataHost= "host1" database="db1" />

dataNode标签中定义了MyCat中的数据节点 , 也就是我们通常说的数据分片。一个dataNode标签就是一个独立的数据分片。

dataNode具体的属性：

属性	含义	描述
name	数据节点的名称	需要唯一 ; 在table标签中会引用这个名字 , 标识表与分片的对应关系
dataHost	数据库实例主机名称	引用自 dataHost 标签中name属性
database	定义分片所属的数据库

4、dataHost 标签

<dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"slaveThreshold= "100">
	<heartbeat>select user ()</heartbeat>
	<writeHost host= "hostM1" url= "192.168.192.147:3306" user="root" password= ""></writeHost>
</dataHost>

该标签在MyCat逻辑库中作为底层标签存在 , 直接定义了具体的数据库实例、读写分离、心跳语句。

dataHost 标签的属性：

属性	含义	描述
name	数据节点名称	唯一标识，供上层使用
maxCon	最大连接数	内部的writeHost、 readHost都会使用这个属性
minCon	最小连接数	内部的writeHost、 readHost都会使用这个属性
balance	负载均衡类型	0：不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。 1：全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。 2：所有读操作都随机的在 writeHost、readhost 上分发。 3：所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力
writeType	写操作分发方式	0 : 写操作都转发到第1台writeHost，writeHost1挂了，会切换到writeHost2上; 1 : 所有的写操作都随机地发送到配置的 writeHost 上 ;
dbType	后端数据库类型	mysql, mongodb , oracle等
dbDriver	数据库驱动	目前可选值有 native和JDBC。 native：执行的是二进制的MySQL协议，可以使用MySQL和MariaDB。其他类型数据库需要使用JDBC （需要在MyCat/lib目录下加入驱动jar）
switchType	数据库切换策略	switchType = -1：不自动切换 switchType = 1：自动切换（默认） switchType = 2：基于MySql主从同步的状态来决定是否切换，心跳语句: show slave status switchType = 3: 基于mysql galary cluster的切换机制，心跳语句： show status like 'wsrep%'

5、dataHost 标签的子标签heartbeat

配置MyCat与后端数据库的心跳，用于检测后端数据库的状态。heartbeat用于配置心跳检查语句。

# MySQL中可以使用  
select user()
# Oracle中可以使用  
select 1 from dual

6、dataHost 标签的子标签writeHost、readHost

指定后端数据库的相关配置，用于实例化后端连接池。

writeHost指定写实例， readHost指定读实例。

在一个dataHost中可以定义多个writeHost和readHost。如果writeHost指定的后端数据库宕机，那么这个writeHost绑定的所有readHost也将不可用。

writeHost、readHost标签的属性：

属性名	含义	取值
host	实例主机标识	对于writeHost一般使用 M1 ；对于readHost ，一般使用 S1；
url	后端数据库连接地址	如果是native ，一般为 ip:port ; 如果是JDBC, 一般为jdbc:mysql://ip:port/
user	数据库用户名	root
password	数据库密码	123456
weight	权重	在readHost中作为读节点权重
usingDecrypt	密码加密	0 否（默认） , 1 是

6、server.xml配置文件

作用：

定义用户以及系统相关变量，如端口等

参数配置：

1、user标签

主要配置MyCat的用户、密码以及权限等信息

<user name="root">
  <!-- 用户密码 -->
  <property name="password">123456</property>
  <!-- 用户可访问的逻辑数据库有test_db,test_db2,test_db3三个，多个逻辑库使用英文逗号分隔开-->
  <property name="schemas">test_db,test_db2,test_db3</property>
  <!-- 用户的读写权限 -->
  <property name="readOnly">true</property>
  <!-- 限制前端的连接数量，如果为0或者没有配置表示不限制 -->
  <property name="benchmark">1000</property>
  <!-- 开启密码加密功能 默认值为0，表示不进行加密 -->
  <property name="usingDecrypt">1</property>
</user>

2、system标签

主要配置系统参数等，如下是一些常用的参数

<system>
  <!-- 字符集，需要保证MyCat字符集与数据库字符集一致 -->
  <property name="charset">utf8</property>
  <!-- SQL解析器 -->
  <property name="defaultSqlParser">druidparser</property>
  <!-- MyCat系统可用的线程数量 -->
  <property name="processors">1</property>
  <!-- 指定MyCat全局序列的类型，0表示本地文件方式，1表示数据库方式 ， 2表示时间戳方式  -->
  <property name="sequenceHandlerType">0</property>
   <!-- 1为开启实时统计、0为关闭 -->
  <property name="useSqlStat">0</property>
   <!-- 1为开启全局表一致性检测、0为关闭 --> 
  <property name="useGlobleTableCheck">0</property> 
   <!-- MyCat的端口，默认为8066 --> 
  <property name="serverPort">8066</property> 
   <!-- MyCat管理端口，默认为9066  --> 
  <property name="managerPort">9066</property>
   <!--  服务监听的IP地址，默认为0.0.0.0 --> 
  <property name="bindIp">0.0.0.0</property>
  <!-- 指定连接的空闲时间的超时长度，如果某个连接的空闲时间超时长度大于idleTimeout，则该连接会被回收，默认30分钟 -->
  <property name="idleTimeout">300000</property>
  <!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
  <property name="handleDistributedTransactions">0</property>
  <!-- 配置是否启用非堆内存处理跨分片结果集。 1开启   0关闭 -->
  <property name="useOffHeapForMerge">1</property>
  <!--是否采用zookeeper协调切换  -->
  <property name="useZKSwitch">true</property>
</system>

主从复制、读写分离

1、主从复制的用途

读写分离：

在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

数据实时备份，当系统中某个节点出现故障的时候，方便切换
架构扩展

业务量越来越大，导致I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

2、主从复制的概念

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

3、主从复制的形式

一主一从：

一主多从，提高系统的读性能：

一主一从和一主多从是最常见的主从架构，实施起来简单并且有效，不仅可以实现HA，而且还能读写分离，进而提升集群的并发能力。

多主一从（从5.7开始支持）：

多主一从可以将多个mysql数据库备份到一台存储性能比较好的服务器上。

双主复制：

双主复制，也就是互做主从复制，每个master既是master，又是另外一台服务器的slave。这样任何一方所做的变更，都会通过复制应用到另外一方的数据库中。

级联复制：

级联复制模式下，部分slave的数据同步不连接主节点，而是连接从节点。因为如果主节点有太多的从节点，就会损耗一部分性能用于replication，那么我们可以让3~5个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响。

4、一主一从的原理

MySQL主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个(I/O thread, SQL thread)运行在从节点，如下图所示：

主节点 binary log dump 线程作用：

当从节点连接主节点时，主节点会创建一个log dump 线程，用于发送bin-log的内容。在读取bin-log中的操作时，此线程会对主节点上的bin-log加锁，当读取完成，甚至在发动给从节点之前，锁会被释放。

从节点I/O线程作用：

当从节点上执行start slave命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log。I/O线程接收到主节点binlog dump 进程发来更新之后log，保存在本地relay-log中。

从节点SQL线程作用：

SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

对于每一个主从连接，都需要三个进程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个binary log dump 进程，而每个从节点都有自己的I/O进程，SQL进程。从节点用两个线程将从主库拉取更新和执行分成独立的任务，这样在执行同步数据任务的时候，不会降低读操作的性能。比如，如果从节点没有运行，此时I/O进程可以很快从主节点获取更新，尽管SQL进程还没有执行。如果在SQL进程执行之前从节点服务停止，至少I/O进程已经从主节点拉取到了最新的变更并且保存在本地relay日志中，当服务再次起来之后，就可以完成数据的同步。

5、一主一从的搭建

1、修改主机配置：（vim /etc/my.cnf）

#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
#设置logbin格式
binlog_format=STATEMENT

binlog 的三种格式：

Statement（Statement-Based Replication,SBR）：每一条会修改数据的 SQL 都会记录在 binlog 中。
Row（Row-Based Replication,RBR）：不记录SQL语句上下文信息，仅保存哪条记录被修改。
Mixed（Mixed-Based Replication,MBR）：Statement 和 Row 的混合体。

2、修改从机配置（vim /etc/my.cnf）

#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay

3、主机、从机重启 MySQL 服务，并检查是否启动

# 重启
systemctl restart mysqld 
# 查看状态
systemctl status mysqld 
# 启动
systemctl start mysqld 
# 停止
systemctl stop mysqld

4、主机、从机都关闭防火墙

# 查看防火墙状态
systemctl status firewalld
# 开启防火墙
systemctl start firewalld
# 关闭防火墙
systemctl stop firewalld

5、在主机上建立帐户并授权 slave

#在主机MySQL里执行授权命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';

6、查询master的状态

show master status;

记录下File和Position的值
执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化

7、在从机上配置需要复制的主机

#复制主机的命令
CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;

8、启动从服务器复制功能、查看服务器状态

# 启动从服务器复制功能
start slave
# 查看从服务器状态（\G代表列的方式展示）
show slave status\G;

如果上图红框中两个参数都是Yes，则说明主从配置成功！

9、主机新建库、新建表、insert 记录，从机复制

10、如何停止从服务复制功能、如何重新配置主从

# 如何停止从服务复制功能
stop slave;

# 如何重新配置主从
stop slave; 
reset master;

6、利用mycat在一主一从的基础上搭建读写分离

1、安装mycat

解压后即可使用：

下载mycat压缩包
解压缩文件拷贝到 linux 下 /usr/local/

3个配置文件：

schema.xml ：定义逻辑库，表、分片节点等内容
rule.xml：定义分片规则
server.xml：定义用户以及系统相关变量，如端口等

2、配置并启动mycat

1、修改配置文件server.xml

修改用户信息，与 MySQL 区分，如下：

…
<user name="mycat">
 <property name="password">123456</property>
 <property name="schemas">TESTDB</property>
</user>
…

2、修改配置文件 schema.xml

删除 <schema> 标签间的表信息， <dataNode> 标签、<dataHost> 标签只留一个， <writeHost> 、<readHost>只留一对

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema
    xmlns:mycat="http://io.mycat/">
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
    <dataNode name="dn1" dataHost="host1" database="testdb" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="192.168.140.128:3306" user="root" password="">
            <!-- can have multi read hosts -->
            <readHost host="hostS1" url="192.168.140.127:3306" user="root" password="" />
        </writeHost>
    </dataHost>
</mycat:schema>

修改<dataHost>的balance属性，通过此属性配置读写分离的类型

负载均衡类型，目前的取值有4 种：（常用1和3）

balance="0" ：不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
balance="1" ：全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。
balance="2" ：所有读操作都随机的在 writeHost、readhost 上分发。
balance="3" ：所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力

3、验证主、从数据库访问情况

4、启动程序

控制台启动：去 mycat/bin 目录下执行 ./mycat console
后台启动：去 mycat/bin 目录下 ./mycat start

7、双主双从的搭建

1、双主机配置

master1的配置（vim /etc/my.cnf ）：

#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates 
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2 
# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=1

master2的配置（vim /etc/my.cnf ）：

#主服务器唯一ID
server-id=3 #启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates 
#表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2 
# 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=2

2、双从机配置：

slave1的配置（vim /etc/my.cnf ）：

#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay

slave2的配置（vim /etc/my.cnf ）：

#从服务器唯一ID
server-id=4 #启用中继日志
relay-log=mysql-relay

3、双主机、双从机都重启 mysql 服务

# 重启
systemctl restart mysqld 
# 查看状态
systemctl status mysqld 
# 启动
systemctl start mysqld 
# 停止
systemctl stop mysqld

4、双主机、双从机都关闭防火墙

# 查看防火墙状态
systemctl status firewalld
# 开启防火墙
systemctl start firewalld
# 关闭防火墙
systemctl stop firewalld

5、在两台主机上建立帐户并授权 slave（在mysql命令里面）

#在主机MySQL里执行授权命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';

查询Master1和Master2的状态

show master status;

Master1：

Master2：

分别记录下File和Position的值

执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

6、在从机上配置需要复制的主机

#复制主机的命令
CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;

Slava1 复制 Master1，Slava2 复制 Master2

7、启动两台从服务器复制功能

start slave;

8、查看从服务器状态

# 查看从服务器状态（\G代表列的方式展示）
show slave status\G;

下面两个参数都是Yes，则说明主从配置成功！

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

9、2台主机的搭建

Master2 复制 Master1 ， Master1 复制 Master2

10、启动两台主服务器复制功能

start slave;

11、查看从服务器状态

# 查看从服务器状态（\G代表列的方式展示）
show slave status\G;

下面两个参数都是Yes，则说明主从配置成功！

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

12、Master1 主机新建库、新建表、insert 记录，Master2 和从机复制

13、如何停止从服务复制功能、如何重新配置主从

# 如何停止从服务复制功能
stop slave;

# 如何重新配置主从
stop slave; 
reset master;

8、利用mycat在双主双从的基础上搭建读写分离

1、修改配置文件server.xml

修改用户信息，与 MySQL 区分，如下：

…
<user name="mycat">
 <property name="password">123456</property>
 <property name="schemas">TESTDB</property>
</user>
…

2、修改配置文件 schema.xml

删除 <schema> 标签间的表信息， <dataNode> 标签、<dataHost> 标签只留一个， <writeHost> 、<readHost>只留一对

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
	
    <dataNode name="dn1" dataHost="host1" database="testdb" />
	
	<dataHost name="host1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100" >
		<!-- 心跳配置 -->
		<heartbeat>select user()</heartbeat>
		
		<!-- M1配置 -->
		<writeHost host="hostM1" url="192.168.140.128:3306" user="root" password="">
			<!-- S1配置 -->
			<readHost host="hostS1" url="192.168.140.127:3306" user="root" password="" />
		</writeHost>
		
		<!-- M2配置 -->
		<writeHost host="hostM2" url="192.168.140.126:3306" user="root"password="">
			<!-- S2配置 -->
			<readHost host="hostS2" url="192.168.140.125:3306" user="root" password="" />
		</writeHost>
	
	</dataHost>
</mycat:schema>

修改<dataHost>的balance属性，通过此属性配置读写分离的类型

负载均衡类型，目前的取值有4 种：（常用1和3）

balance="0" ：不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
balance="1" ：全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。
balance="2" ：所有读操作都随机的在 writeHost、readhost 上分发。
balance="3" ：所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力

writeType="0": 所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个

writeType="1"，所有写操作都随机的发送到配置的 writeHost，1.5 以后废弃不推荐

writeHost，重新启动后以切换后的为准，切换记录在配置文件中:dnindex.properties 。

switchType="1":

1 ：默认值，自动切换。

-1 ：表示不自动切换

2 ：基于 MySQL 主从同步的状态决定是否切换。

3、验证主、从数据库访问情况

4、启动程序

控制台启动：去 mycat/bin 目录下执行 ./mycat console
后台启动：去 mycat/bin 目录下 ./mycat start

5、验证读写分离

①在写主机 Master1 数据库表 mytbl 中插入带系统变量数据，造成主从数据不一致

INSERT INTO mytbl VALUES(3,@@hostname);

②在 Mycat 里查询 mytbl 表 , 可以看到查询语句在 Master2 （ host81 ）、 Slava1 （ host80 ）、 Slava2 （ host82 ）主从三个主机间切换

6、抗风险能力

①停止数据库 Master1

②在Mycat里插入数据依然成功，Master2自动切换为写主机

INSERT INTO mytbl VALUES(3,@@hostname);

③启动数据库Master1

在Mycat里查询mytbl表,可以看到查询语句在Master1（host79）、Slava1（host80）、Slava2（host82）主从三个主机间切换

Master1 、 Master2 互做备机，负责写的主机宕机，备机切换负责写操作，保证数据库读写分离高

可用性

MyBatis框架

1、#{} 和 ${} 的区别

#{}：#{}是预编译处理，mybatis在对#{}进行处理的时候会替换成？，并使用PreparedStatement的set方法来赋值；可以有效的防止SQL注入，提高系统安全性

${}：$ {}是字符串替换，MyBatis在处理 $ { } 时，就是把 ${ } 替换成变量的值，会出现sql注入的问题

2、MyBatis是如何实现分页的

一般分页分为2类：

逻辑分页：先查询出所有的数据并将其缓存到内存中，在根据业务相关的需求从内存中筛选出合适的数据。
物理分页：直接利用数据库所提供的limit关键字进行分页。

mybatis中一共提供了3种分页的方式：

直接在select语句上增加数据库提供的分页关键字（limit），然后在程序中传递当前的页，已展示每页展示的条数
使用mybatis提供的RowBounds对象实现逻辑内存的分页；一次性加载所有符合条件的数据，在根据分页的参数在内存中进行分页
通过mybatis中的Interceptor拦截器来实现，通过拦截需要分页的select语句，在语句中动态拼接SQL；像常用的PageHelper插件等

3、Mapper接口的工作原理

在 MyBatis 的初始化过程中，每个一个 XML 映射文件中的<select />、<insert />、<update />、<delete />标签，会被解析成一个 MappedStatement 对象，对应的 id 就是 XML 映射文件配置的 namespace+'.'+statementId，这个 id 跟 Mapper 接口中的方法进行关联。

同一个 Mapper 接口中为什么不能定义重载方法？

因为 Mapper 接口中的方法是通过 接口名称+'.'+方法名 去找到对应的 MappedStatement 对象，如果方法名相同，则对应的 MappedStatement 对象就是同一个，就存在问题了，所以同一个 Mapper 接口不能定义重载的方法

每个 Mapper 接口都会创建一个动态代理对象（JDK 动态代理），代理类会拦截接口的方法，找到对应的 MappedStatement 对象，然后执行数据库相关操作。执行逻辑如下：

其中 MapperProxy 为 Mapper 接口的动态代理对象的代理类

4、MyBatis的缓存

mybatis中提供查询缓存，用于减轻数据库的压力，提高数据库的性能。mybatis提供了一级缓存和二级缓存。

一级缓存：是SQLSession级别缓存，在操作数据库时都需要构造SQLSession对象，在对象中有一个数据结构（HashMap）用于存储缓存数据，不同的SQLSession之间的缓存数据区域是互不影响的。

一级缓存的作用域是在同一个SQLSession，在同一个SQLSession中执行相同的两次SQL，第一次执行完毕在后会将数据写到缓存中，第二次从缓存中进行后去就不在数据库中查询，从而提高了效率。

当一个SQLSession结束后该SQLSession中的一级缓存也就不存在了，mybatis默认开启一级缓存。

二级缓存：是mapper级别缓存，多个SQLSession去操作同一个mapper的SQL语句，多个SQLSession操作都会存在二级缓存中，多个SQLSession共用二级缓存，二级缓存是跨SQLSession的。

二级缓存是多个SQLSession共享的，作用域是mapper下的同一个namespace。不同的SQLSession两次执行相同的namespace下的SQL最终能获取相同的SQL语句结果。

mybatis默认是没有开启二级缓存的，需要在全局配置文件中配置开启二级缓存，并且映射的java类需要实现序列化。

缓存查询的顺序：

先查询二级缓存，因为二级缓存中可能会有其他程序已经查出来的数据，可以拿来直接使用。
如果二级缓存没有命中，再查询一级缓存
如果一级缓存也没有命中，则查询数据库
SqlSession关闭之后，一级缓存中的数据会写入二级缓存

5、Mybatis的优点、缺点

优点：

1、与JDBC相比，减少了50%以上的代码量。

2、基于SQL语句的编程，相对比较灵活。

3、可以兼容多种的数据库，例如MySQL、Oracle等。

4、可以和spring进行很好的兼容。

5、提供了丰富的标签，例如动态SQL语句标签、ResultMap标签等。

缺点：

1、对SQL的编写量大，并且需要有丰富的SQL编写能力

2、SQL语句和具体的数据库有很大的依赖，导致对数据库的移植性很差，不能随意的切换数据库

6、嵌套查询和嵌套结果区别

7、Mybatis加载Mapper配置文件的方式

package、resource、url、mapperClass通过if判断依次进行校验加载，只会加载一个

8、mybatis执行一个sql语句的过程

//1、加载xml配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("config.xml");
//2、根据配置文件创建SqlSessionFactory 会话工厂
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(resourceAsStream);
//3、通过SqlSessionFactory 创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//通过sqlsession获取要执行的Mapper对象
HeroMapper mapper = sqlSession.getMapper(HeroMapper.class);
//4、执行Mapper对象中的方法，从数据库中获取数据
Hero hero = mapper.selectById(1);
//5、处理返回结果并关闭连接

9、MyBatis中常用的转义字符

1、eq ： equal等于
2、ne： not equal不等于
3、gt ： greater than大于
4、lt： less than小于
5、ge ： greater than or equal 大于等于
6、le ： less than or equal 小于等于
7、in ： in 包含（数组）
8、isNull ： 等于null
9、between ： 在2个条件之间(包括边界值)
10、like ： 模糊查询，使用concat方法将%与参数进行连接

10、MyBatis的insert插入后获取自增id

方式一：推荐

Order实体类：

@Data
public class OrderInfo {
    /**
     * 主键ID
     */
    private Integer id;
    /**
     * 委托人单位
     */
    private String clientCompany;
    /**
     * 委托人
     */
    private String clientUser;

}

Mapper.xml代码模块：

    <insert id="insert" parameterType="cn.net.wanji.testingcontent.entity.OrderInfo">

        <selectKey order="AFTER" resultType="java.lang.Integer" keyProperty="id">
            select LAST_INSERT_ID()
        </selectKey>

        insert into order_info (client_company, client_user)
        values ( #{clientCompany}, #{clientUser})
    </insert>

selectKey标签的属性：

order：MySQL数据库中，需要设置为after；Oracle需要设置为before
keyProperty：表中id 在实体类对应的属性

Service代码模块：

orderInfoMapper.insert(order);
Integer orderId = order.getId();

方式二：不推荐，返回的值可能是成功的行数，不稳定

<insert id="add" useGeneratedKeys="true" keyProperty="id" parameterType="cn.net.wanji.testingcontent.entity.OrderInfo">
    insert into order_info (client_company, client_user)
        values ( #{clientCompany}, #{clientUser})
</insert>

怀揣着梦想出发

关注

0
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
MySQL+Mybatis

MySQL主要分为Server层和引擎层，Server层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用，redolog只有InnoDB有。引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory等。查询语句的执行流程如下权限校验（如果命中缓存）--->查询缓存--->分析器--->优化器--->权限校验--->执行器--->引擎更新语句执行流程如下分析器---->权限校验---->执行器--->引擎---re。...
复制链接

扫一扫