小言_互联网的博客

MySQL底层知识总结

2022-11-04 07:19 270人阅读  评论(0)

MySQL数据库配置主从

三大日志

-binlog 归档日志

-redolog 重做日志

-undolog

docker run --name mysql102 -p 33062:3306 -e MYSQL_ROOT_PASSWORD=123 -d mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

GRANT REPLICATION SLAVE ON *.* to 'rep1'@'%' identified by '123';
FLUSH PRIVILEGES

docker exec -it mysql101 /bin/bash
cd /etc/mysql/my.cnf
先cat mysqld.cnf 复制内容
在 vi mysqld.cnf
粘贴 修改内容
[mysqld]
log-bin=/var/lib/mysql/binlog
server-id=33061
binlog-do-db = db01
保存退出


docker cp mysqld.cnf mysql101:/etc/mysql/mysql.conf.d/
docker restart mysql101

回到用户目录，vi mysqld.cnf
[mysqld]
server-id=33062
保存退出
docker cp mysqld.cnf mysql102:/etc/mysql/mysql.conf.d/
docker restart mysql102



回到用户目录
docker exec -it mysql102 /bin/bash
mysql -uroot -p
输入数据库密码 我设置的是123

change master to master_host='192.168.183.129',master_port=33061,master_user='rep1',master_password='123',master_log_file='binlog.000001',master_log_pos=154;

start slave;

 show slave status\G

显示

设置成功

主从一致原理

就是主机记录sql操作的binlog二进制文件，传输给从机，从机解析binlog文件并执行一次文件里的命令，所以主从机的数据是一致，但有一种情况不一致，就是执行的sql命令存在uuid类似的函数，每次执行uuid函数，得到的值都不一样，所以就会发生主从机数据不一致。

怎么解决主从数据不一致发生的情况，在配置主从数据库时，将binlog_format设置为ROW 就可以了，

即 在主机中，修改 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件，将 binlog_format 改为 ROW

发生主从不一致时binlog_format=STATEMENT，这个代表从机直接执行binlog文件的sql语句

binlog_format=ROW 表从机根据主机更新的表数据，自己执行相应增删改查语句，比如主机插入uuid函数，得到一个随机值aaa，从机是对uuid这个列值设置值aaa,这就保证数据的一致性

主从复制的作用功能优点：数据库使用主从配置，读写分离，对大量数据做好备份，保证数据的安全性，即使主数据库宕机，也能使用从数据库先顶替，增强了数据库的高可用性

回表

1.索引结构 B+Tree

前者是B-Tree（B树），后者是B+Tree(B+树)

区别：

1.b树中，所有节点都会带有指向具体记录的指针，即所有节点都存数据

B+树只有叶子节点会带有指向具体记录的指针，即只有叶子节点存数据

2.B树中不同叶子之间没有连在一起，

B+树所有叶子节点通过链表指针连接在一起（连在一起后可以像有序链表那样快速查找，增加效率）

3.b树可能在非叶子节点就拿到指针（数据），搜索效率不稳定，b+树只能在叶子节点获取到指针（数据），搜索效率稳定

B+树的其他优点：

1.b+树中，非叶子节点不带有指向具体记录的指针，所以非叶子节点中可以存储更多的索引项，

可以有效降低树的高度，提高搜索的效率

2.B+Tree 中，叶子结点通过指针连接在一起，范围扫描的需求实现起来将非常容易，而 B-Tree，范围扫描则需要不停的在叶子结点和非叶子结点之间移动，效率低。

一个三层B+树可以存大概2100万数据，1170* 1170 * 16

b+树一般2到4层,就可以满足千万级的数据存储

2.两类索引

按照物理存储方式，可以分为聚簇索引和非聚簇索引

主键索引一定是聚簇索引，聚簇索引不一定是主键索引

主键索引都是聚簇索引，非主键索引就是非聚簇索引

主键索引和非主键索引都是用B+Tree数据结构储存，

区别是在叶子节点存储的内容不同

主键索引的叶子节点储存的是一行完整的数据，非主键索引的叶子结点存储的是主键值，非主键索引的对应的列值

查询时

  1. 如果是通过主键索引来查询数据，例如 select * from user where id=100 ，那么此时只需要 搜索主键索引的 B+Tree 就可以找到数据。
  2. 如果是通过非主键索引来查询数据，例如 select * from user where username=‘javaboy’ ， 那么此时需要先搜索 username 这一列索引的 B+Tree，搜索完成后得到主键的值，然后再去搜索 主键索引的 B+Tree，就可以获取到一行完整的数据。

第二种查询方式就是回表操作，第一次搜索B+树拿到主键值后再去搜索主键索引的B+树

一般非主键索引查询都是回表，但是有例外，就是查询复合非主键索引（数据库客户端工具设置联合索引），就不是回表，其用了索引覆盖扫描（覆盖索引），直接过滤记录并返回命中的结果，不需要再回表

sql语句前面加上explain 就可以查看执行语句的步骤 Extra列的值为 Using index 就是用覆盖索引

explain 中的不同项：

type：type 值为 index，表示全索引扫描，就是把整个索引过一遍就行（注意是索引不是整个表）；type 值为 all，表示全表扫描，即不会使用索引。

key：这个表示 MySQL 决定采用哪个索引来优化对该表的访问，PRIMARY 表示利用主键索引， NULL 表示不用索引。

key_len：这个表示 MySQL 使用的键长度，因为我们的主键类型是 INT 且非空，所以值为 4。

Extra：这个中的 Using index 表示优化器只需要通过访问索引就可以获取到需要的数据（不需要回表），Using where 表示需要过滤

三大日志

binlog 恢复数据库

开启binglog 可以用于恢复数据库，只要数据库密码没改，和git 一样，恢复到之前操作的状态来恢复数据

聚合函数count

InnoDB 引擎中

对于 select count(1) from user; 这个查询来说，InnoDB 引擎会去找到一个最小的索引树去遍历 （不一定是主键索引），但是不会读取数据，而是读到一个叶子节点，就返回 1，最后将结果累加。

对于 select count(id) from user; 这个查询来说，InnoDB 引擎会遍历整个主键索引，然后读取 id 并返回，不过因为 id 是主键，就在 B+ 树的叶子节点上，所以这个过程不会涉及到随机 IO（并不需要回表等操作去数据页拿数据），性能也是 OK 的。

对于 select count(username) from user; 这个查询来说，InnoDB 引擎会遍历整张表做全表扫描， 读取每一行的 username 字段并返回，如果 username 在定义时候设置了 not null，那么直接统计 username 的个数；如果 username 在定义的时候没有设置 not null，那么就先判断一username 是否为空，然后再统计。

最后再来说说 select count(* ) from user; ，这个 SQL 的特殊之处在于它被 MySQL 优化过，当 MySQL 看到 count(* ) 就知道你是想统计总记录数，就会去找到一个最小的索引树去遍历，然后统计记录数。如果只有主键索引，就用主键索引遍历，如果出了主键索引还有非主键索引（普通索引）就用普通索引遍历（普通索引树更小）

count(1)≈ count(*)>count(主键索引)>count(普通索引),

MyISAM 引擎中，直接用count(*),MyISAM 把表中的行数直接存在磁盘中了，需要的时候直接读取出来就行了,速度非常快

InnoDB引擎和MyISAM引擎用统计函数的区别：

MyISAM不支持事务，InnoDB 支持事务，为了支持事务，InnoDB 引入了 MVCC 多版本并发控制，所以在数据读取的时候可能会有脏读、幻读以及不可重复读等问题。 所以，InnoDB 需要将每一行数据拿出来，判断该行数据对当前会话是否可见，如果可见，就统计该行数据，否则不予统计

MyISAM引擎和InnoDb引擎的区别：

索引下推

MySQL 5.6之后引入的，减少回表次数

例： select * from user where username='1' and age=99;

5.6之前执行此语句，先查 username索引，找到username=1 的主键值，然后回表获取该主键值的所有数据，再判断age=99，不等于99，继续查找uesrname=1 的主键值，回表，只等满足条件返回数据

5.6之后有索引下推，执行此语句，先查username 索引，找到username=1 的主键值，再判断该主键中age=99,如果不等于就跳过此索引，继续重复操作，找到满足username=1&age=99 的主键值，回表，根据主键索引找到其他所有数据，返回数据

索引下推减少了username=1 &age !=99 主键值的回表操作

多版本并发控制（事务）

事务的隔离级别

  1. 序列化(serializable):最高级别，数据库操作只能一个一个的按顺序执行

  2. 可重复读(repeatable read)：默认级别，一个事务的操作读不到其他事务提交的数据，即一个事务执行多次相同的select操作，结果都是一样，其他事务对数据的改变，那个事务不可见，只有同一个事务对数据改变，操作结果才会跟着变化

  3. 提交读(read committed)：一个事务的操作可以读到其他事务提交的数据，读不到未提交的数据。即一个事务执行多次相同的select操作，结果会被其他事务提交的数据改变。

  4. 未提交读(read uncommitted)：最低级别，一个事务可以读到其他事务还未提交的数据，如果这个事务使用其他事务未提交的变化作为计算的基础，然后那些未提交的变化被它们的父事务撤销，这就导致了大量的数据变化。

默认隔离级别为可重复读

数据库客户端工具SQLlog,开启事务语句为

begin;或satart transaction;

sql语句;

commit/end;

未提交读出现的问题-脏读、不可重复读、幻读

脏读：一个事务读到另一个事务还没有提交的数据

例：

SQLyog 一个连接等于一个事务，多个事务需要新建连接

一个简单表数据，有 javaboy 和 itboyhub 两个用户，两个人的账户各有 1000 人民币。

事务一（别执行）

START TRANSACTION;
UPDATE account set balance=balance+100 where name='javaboy';
UPDATE account set balance=balance-100 where name='itboyhub';
COMMIT;

事务二（别执行）

START TRANSACTION;
SELECT * from account;
COMMIT;

执行步骤：先切换事务二的事务隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

事务二 执行第一行语句START TRANSACTION;表开启事务，没执行COMMIT 语句就表示未提交事务

事务一执行 第一行和第二行语句，表开启事务，执行更改操作，但是没执行COMMIT 语句就表示未提交事务

事务二执行第二条语句，结果就就是javaboy账户的钱加了100

不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，即读取了其他事务已经提交的事务。

同上例，先恢复数据，事务二执行开启事务和查询语句，事务一执行所有语句，事务二再执行查询语句，两次结果不一样，第一次1000，第二次1100

幻读：不同隔离级别幻读情况不同，如未提交读隔离级别一个事务读到另一个事务未提交的数据，然后对这个数据进行增删改无效（有zhangsan数据无法删除，无zhangsan数据不能插入zhangsan）。总的来说，就是其他事务更改的新数据在当前事务无法操作 查询每个范围的数据，多次查询结果不同

例：

事务一(别执行)

START TRANSACTION;
insert into account(name,balance) values('zhangsan',1000);
COMMIT;

事务二（别执行）

START TRANSACTION;
SELECT * from account;
delete from account where name='zhangsan';
COMMIT;

执行步骤：执行事务二的第一行和第二行数据，得到数据只有javaboy和itboyhub，

执行事务二的前两行，再执行事务二的第二行，出现脏读，读到zhangsan 的数据，再执行事务二的第三行语句，进行删除就会出现问题，此时就产生了幻觉， 明明有个 zhangsan，却无法删除

各种隔离级别解决出现的问题

提交读解决了脏读问题，未解决不可重复读和幻读，幻读中还是上一个例子，事务二的删除语句改为插入语句insert into account(name,balance) values('zhangsan',1000);

同样执行步骤，再删除语句那步改为再查询一次，读不到未提交的数据zhangsan，在执行插入语句，因为name字段唯一无法插入，这就是提交读的幻读问题，没有zhangsan数据却无法插入zhangsan

可重复读进一步解决了不可重复读的问题，但幻读依然未解决，幻读例子和提交读差不多，就是执行事务一的所有语句，提交事务一。之后相同，插入时也会出错

序列化解决了所有问题，但是效率低，开启其他事务会阻塞，必须当前事务提交执行完，才能开启其他事务。



快照读：在可重复读的隔离级别下，事务启动的时候，就会针对当前库拍一个照片（快照），快照读读取到的数据要么就是拍照时的数据，要么就是当前事务自身插 入/修改过的数据。

快照读是一种一致性不加锁的读，也是InnoDB存储引擎并发高的核心原因之一

当前读：在可重复读隔离级别下，读取最新数据，而不是历史版本的数据，即在可重复隔离级别使用当前读可以读到其他事务已提交的数据（提交读）

for update 会给数据加一个排他锁，其他事务无法操作此数据，必须等事务执行完，释放锁其他事务才能操作成功，不然就是一直等待

undo log :在数据改变之前先把旧的数据记录下来，是数据库事务回滚的底层原理

增删改会用到数据库回滚，所以会记录在undo log 中，查询操作不涉及回滚，不需要记录到undolog

行格式： COMPACT、REDUNDANT、DYNAMIC、 COMPRESSED 等，每种行格式都有以下数据列

重点关注DB_ROW_ID 、 DB_TRX_ID 、 DB_ROLL_PTR 三列

DB_ROW_ID:行ID,用来唯一标识一行数据。如果用户在创建表的时候没有设置主键，那么系统会根据该列建立主键索引。

DB_TRX_ID:事务ID,当我们要开启一个事务的时候，会向 InnoDB 的事务系统申请一个事务 id，这个事务 id 是一个严格递增且唯一的数字，当前数据行是被哪个事务修改的，就会把对应的事务 id 记录在当前行中。

DB_ROLL_PTR:回滚指针，指向undolog 日志的地址，通过undolog 回滚恢复数据

MVCC :英文全称是 Multi-Version Concurrency Control，中文译作多版本并发控制。MVCC 的核心思路就是保存数据行的历史版本，通过对数据行的多个版本进行管理来实现数据库的并发控制。

当我们开启一个事务的时候，首先会向 InnoDB 的事务系统申请一个事务 id，这 个 id 是一个严格递增的数字，在当前事务开启的一瞬间系统会创建一个数组，数组中保存了目前所有已开启但未提交的事务id,也叫活跃事务id，这些数组中的事务不可见。可重复读隔离级别，开启事务事务后，数组不变，提交读级别，每次有事务开启，就会更新数组

**数据库事务的原理：**MyISAM不支持事务，InnoDB支持事务，InnoDB通过MVCC多版本并发控制来支持事务的，在MVCC中，InnoDB存储数据会存储当前数据被哪个事务修改的事务id,在开启一个事务时，会向InnoDB事务管理系统申请一个事务id,这个事务id是严格自增长的，通过比较其他数据的事务id大小关系，来判断其他数据对当前操作可不可见。

当当前事务想要去查看某一行数据的时候，会先去查看该行数据的事务id
01. 如果这个值等于当前事务 id，说明这就是当前事务修改的，那么数据可见。
02.这个值在数组中，表未提交（除了等于自身id），不可见
03.这个值小于自身事务id且不在数组中，表在执行该事务前，已提交，数据可见
04.这个值大于自身事务id,要么未提交不可见要么已提交但是是在该事务开启之后再提交的还是不可见，所以数据不可见

MVCC例子:

事务一：开始事务，执行查询语句 select * from user ,结果只有一个id为1，name为javaboy,age=99,不提交事务

事务二：执行update语句 update user set age=age+1 where id =1; （update操作会隐式开启事务，更新完后会事务自动提交 ），在执行查询语句select * from user，age变为100，

事务一：在执行一次查询操作，age为99，符合预期（可重复读隔离级别读不到其他事务提交的数据），在执行update语句 update user set age=age+1 where id =1;，在查询，这时age结果为101.

综上，更新其实是在 100 的基础上更新的，这个也好理解，要是在 99 的基础上更新，那么就会丢失掉 100 的那次更新，显然是不对的。

MySQL 中的 update 就是先读再更新，读的时候默认就是当前读，即会加锁。所以在上面的案例中，如果 B 会话中显式的开启了事务并且没有没有提交， 那么 A 会话中的 update 语句就会被阻塞。

MVCC的作用：一行记录存在多个版本。实现了读写并发控制，读写互不阻塞； 同时 MVCC 中采用了乐观锁，读数据不加锁，写数据只锁行，降低了死锁的概率；并且还能据此实现快照读。

防止事务隐式提交导致回滚失败，开启事务后，在事务里只写增删改查 （INSERT/DELETE/UPDATE/SELECT），就不会导致事务隐式提交

最左匹配原则

username和age联合，根据联合索引的第一字段匹配，会用到索引覆盖，username like 'zhang%'根据最左匹配原则不会全表，其他like会造成全表扫描,不要用select *,容易全表扫描

username和age联合，username在最左，所以username有序，age无序

前缀索引（含数据查找规则）

列值太长了，通过前面几个字符就能锁定数据，或者数据范围，就可以建立前缀索引

例子：对uuid 函数建立前缀索引

SELECT COUNT(DISTINCT user_uuid) / COUNT(*) FROM system_user;

结果为1，即全列选择性为1 ，说明这一列的值都是唯一不重复的

然后就是找到最短前缀索引的全列选择性为1，left(user_uuid,前缀长度)

SELECT COUNT(DISTINCT LRFT(user_uuid,6)) / COUNT(distinct user_uuid) FROM system_user;结果为0.9068

SELECT COUNT(DISTINCT LRFT(user_uuid,7)) / COUNT(distinct user_uuid) FROM system_user;结果为0.9938，之后也是依次增加长度直到结果为1，user_uuid字段第10个字符，全列选择性为1,所以用user_uuid字段查询时，只需要输入前十个字符就可以唯一定位到一条具体的记录了。

创建前缀索引：

alter table system_user add index user_uuid_index(user_uuid(10));

查看索引：show index from system_user;

执行查询语句应用到前缀索引的底层步骤

select * from system_user where user_uuid='39352f81-165e-4405-9715- 75fcdf7f7068';

  1. 从user_uuid_index 索引中找到第一个值为39352f81-1（user_uuid的前缀是个字符）

的记录

  1. 由于user_uuid 是二级索引，叶子节点保存的是主键值，所以此时拿到了主键id为1.

  2. 拿着主键id 去回表，在主键索引上找到id为1 的行的完整记录，返回给server层

  3. server 层判断其user_uuid 是不是39352f81-165e-4405-9715- 75fcdf7f7068（所以该执行语句的Extra 为Using where）， 如果不是，这行记录丢弃，如果是，就将该记录加入结果集

  4. 索引叶子节点上数据之间是由单向链表维系的，所以接着第一步查找的结果，继续向后读取下一条记录，然后重复2,3,4步，直到在user_uuid_index 上取到的值不为39352f81-1时，循环结束。

结果建立的前缀索引是前缀索引的选择性为1，即能唯一定位数据，就不需要第5步了，前缀索引选择性小于1,就需要第五步

注意：用了前缀索引就用不了覆盖索引

MySQL的锁

S 锁，英文为 Shared Lock，中文译作共享锁，也叫读锁，即 Read Lock。S 锁之间是互不阻塞的，S锁和X锁互斥。当事务读取一条记录时，需要先获取该记录的 S 锁。

例：事务 T1 对记录 R1 加上了 S 锁，那么事务 T1 可以读取 R1 这一行记录，但是不 能修改 R1，其他事务 T2 可以继续对 R1 添加 S 锁，但是不能添加 X 锁，只有当 R1 上面的 S 锁释放了，才能加上 X 锁。

获取S锁方法(在事务中)：select * from user where id=1 lock in share mode

X 锁，英文为 Exclusive Lock，中文译作排他锁，也叫写锁，即 Write Lock。X 锁是具有排他性的，即一个写锁会阻塞其他的 X 锁和 S 锁。当事务需要修改一条记录时，需要先获取该记录的 X 锁。

例子：事务 T1 对记录 R1 加上了 X 锁，那么事务 T1 即可以读取 R1 也可以修改 R1，而 其他事务则不能对 R1 再添加任何锁，直到 T1 释放了 R1 上的锁。

获取X锁的方法：select * from user where id=1 for update

update语句也会先获取X锁，会被其他持有S、X锁的事务阻塞

MDL锁：全称为 meta data lock，中文称作元数据锁，这是从 MySQL5.5 开始引入的锁，MDL 是为了解决 DDL 操作和 DML 操作之间的一致性。从锁的作用范围上来说，MDL 算是一种表级锁，是一个 server 层的锁。

MDL 加锁过程是系统自动控制，无法直接干预，也不需要直接干预，当我们对一个表数据做增删改查操作的时候，会自动加MDL 读锁；当我们要更新表结构的时候，自动加 MDL 写锁。MDL锁也是读读共享，读写互斥，写写互斥，所以在事务中，事务一执行表数据的增删改查，事务二执行更改表结构（alter 加减列）语句会阻塞。另外，所有的 DDL 语句都会导致事务隐式提交，所以设计 DML 数据操作的事务时，尽量不要在 DML 中包含 DDL 语句

MySQL5.6 开始引入了 Online DDL，与MDL相似，只是不是更新表结构，就不会对增删改查阻塞。

行级锁（读锁和写锁）

行级锁在使用的时候并不是直接锁掉这行记录,而是锁索引
如果一条sql用到了主键索引(mysql主键自带索引),mysql会锁住主键索引;
如果一条sql操作了非主键索引,mysql会先锁住非主键索引,再锁定主键索引.

表锁（MDL锁）

顾名思义，表锁就是一锁锁一整张表，在表被锁定期间，其他事务不能对该表进行操作，必须等当前表的锁被释放后才能进行操作。表锁响应的是非索引字段，即全表扫描，全表扫描时锁定整张表，sql语句可以通过执行计划看出扫描了多少条记录。
多表查询通常是锁表；

binlog 与redolog，undolog

binlog,中文称为归档日志，

是MySQL Server 层的日志，而不是存储引擎自带的日志，它记录了所有的DDL 和DML （不包含数据查询语句）语句，而且是以事件形式记录，还包含语句所执行的消耗的时间等，

binlog 是一种逻辑日志，它记录的是一条sql语句的原始逻辑，例：给某个字段加一，

binlog 文件写满后，会自动切换到下一个日志文件继续写，而不会覆盖以前的日志。

配置binglog 文件时可以指定文件的有效期，到期后binlog日志文件会自动删除，可以避免占用较多的存储空间。

开启binlog ,大概会有1%的性能损耗，可以接受。

binlog 两个重要的应用：

  1. MySQL 主从复制时：在主机上开启 binlog，主机将 binlog 同步给从机，从 机通过 binlog 来同步数据，进而实现主机和从机的数据同步。
  2. MySQL 数据恢复，通过使用 mysqlbinlog 工具再结合 binlog 文件，可以将 数据恢复到过去的某一时刻。

redolog,中文称重做日志，是InnoDB 引擎提供的，它是一种物理日志，记录在某个数据页上做了什么修改（即记录的是具体数据，比如age由88变为99，即记录age=99），它是循环写入的，后面写入的可能会覆盖前面写入的，redolog写数据到磁盘是顺序IO.

redo log 本身又分为：

  1. 日志缓冲（redo log buffer)，该部分日志是易失性的。

  2. 重做日志(redo log file)，这是磁盘上的日志文件，该部分日志是持久的。

MySQL 每执行一条 DML 语句，先将记录写入 redo log buffer ，后续在某个时间点再一次性将多个操作记录写到 redo log file ，这种先写日志再写磁盘的技术就是 MySQL 里经常说到的 WAL(Write-Ahead Logging) 技术 （预写日志）。

undolog：操作数据时，记录原数据和操作后的数据。用来事务回滚，原理是

在数据改变之前先把旧的数据记录下来，回滚时根据旧数据生成对应的sql语句，把数据还原回来。

增删改会用到数据库回滚，所以会记录在undo log 中，查询操作不涉及回滚，不需要记录到undolog

两阶段提交：

步骤：更新记录R->InnoDB将该行记录加载到Buffer Pool->将R行的旧值写入undolog->

更新内存中的数据->执行器写入redolog(prepare) (一阶段提交) ->执行器写binlog ->执行器写入redolog(commit)(二阶段提交)

总结：第一阶段先提交redolog, 处于 prepare 状态，在提交binlog，第二阶段再提交redolog，处于commit状态。

优点：1. 一阶段提交之后崩溃了，即 写入 redo log，处于 prepare 状态 的 时候崩溃了，此时： 由于 binlog 还没写，redo log 处于 prepare 状态还没提交，所以崩溃恢复的时候，这个事务会回滚，此时 binlog 还没写，所以也不会传到备库。

  1. 假设写完 binlog 之后崩溃了，此时： redolog 中的日志是不完整的，处于 prepare 状态，还没有提交，那么恢复的时 候，首先检查 binlog 中的事务是否存在并且完整，如果存在且完整，则直接提交事务，如果不存在或者不完整，则回滚事务。

  2. 假设 redolog 处于 commit 状态的时候崩溃了，那么重启后的处理方案 同情况二。

两阶段提交，在那个阶段崩溃因为事务不完整都可以事务回滚，保证数据的一致性

反面例子：先写binlog再写redolog 或先写redolog再写binlog

假设我们要向表中插入一条记录 R，如果是先写 binlog 再写 redolog，那么假设 binlog 写完后崩溃了，此时 redolog 还没写。那么重启恢复的时候就会出问题： binlog 中已经有 R 的记录了，当从机从主机同步数据的时候或者我们使用 binlog 恢复数据的时候，就会同步到 R 这条记录；但是 redolog 中没有关于 R 的记 录，所以崩溃恢复之后，插入 R 记录的这个事务是无效的，即数据库中没有该行 记录，这就造成了数据不一致。 相反，假设我们要向表中插入一条记录 R，如果是先写 redolog 再写 binlog，那 么假设 redolog 写完后崩溃了，此时 binlog 还没写。那么重启恢复的时候也会 出问题：redolog 中已经有 R 的记录了，所以崩溃恢复之后，插入 R 记录的这个 事务是有效的，通过该记录将数据恢复到数据库中；但是 binlog 中还没有关于 R 的记录，所以当从机从主机同步数据的时候或者我们使用 binlog 恢复数据的时 候，就不会同步到 R 这条记录，这就造成了数据不一致。

小表驱动大表

in和exists ,in是先执行in里面的语句，exists是先执行exists外面的语句

小表驱动大表效率更高。核心的原因在于，搜索被驱动的表的时候，一般都是有索引的，而索引的搜索就要快很多，搜索次数也少。

被驱动表上没有可用索引的情况，MySQL 使用了一种名为Block Nested-Loop Join（简称BNL）的算法，该算法的步骤

1.把E表的数据读入线程内存join_buffer中

2.扫描D表，把D表中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回

如果 join_buffer 足够大，一次性就能读取所有数据到内存中，那么大表驱动 小表还是小表驱动大表都无所谓了。

如果 join_buffer 大小有限，那么建议小表D驱动大表E，这样即使要分块读取， 读取的次数也少一些。

建议多表联合查询的时候，建议小表驱动大表

MyCat

作为客户端访问数据库的中转站，有一个逻辑数据库，实际数据还是再数据库中，通过这个中转站实现读写分离，写的请求访问主数据库，读的请求访问从数据库

作用：MyCat 作为一个分布式数据库中间，屏蔽了数据库集群的操作，让我们操作数据库集群就像操作单机版数据库一样方便

linux安装和配置MyCat

解压压缩包 tar -zxvf …

设置配置 vi …/conf/schema.xml

启动 …/bin/mycat start 重启 restart

数据库分库分表规则

  • 垂直切分：垂直切分之后，每张表都是完整的；但是库不完整，要多个库合在一起，才能组成完成的库。
  • 水平切分：水平切分之后，每张表都不完整了，但是每一个库中的表的数量是完整的。

数据库分片规则

在 vi …/conf/schema.xml 中配置，由17种。global,sharding-by-intfile

主键自增

两种思路，1. 设置一个公共地方存放主键自增的id,其他数据从公共地方拿id,2. 设置主键自增步长

设置主键自增步长

具体方法

1.数据库自己搞定的方式

设置自增步长，set @@auto_increment_increment=9

创建表时，auto increment=30,设置主键起始值

如果时分库分表，设置三个库中表的自增起始值为1，2，3，然后自增步长都是3，就可以实现自增了，但是不推荐

2.MySQL+MyCat+ZooKeeper

MyCat对主键自增有5种方案(01.通过本地文件实现 02. 通过数据库实现 03. 通过本地时间戳实现 04. 通过分布式 ZK ID 生成器实现 05. 通过 ZK 递增方式实现)，

选择第4种方案，通过分布式ZK ID生成器实现

Docker安装ZooKeeper,

创建目录mkdir zk,再创建文件 vi docker-compose.yaml

将配置内容复制进去

version: '3.7'

# 给zk集群配置一个网络，网络名为zk-net
networks:
  zk-net:
    name: zk-net

# 配置zk集群的
# container services下的每一个子配置都对应一个zk节点的docker container
services:
  zk1:
    # docker container所使用的docker image
    image: zookeeper
    hostname: zk1
    container_name: zk1
    # 配置docker container和宿主机的端口映射
    ports:
      - 2181:2181
      - 8081:8080
    # 配置docker container的环境变量
    environment:
      # 当前zk实例的id
      ZOO_MY_ID: 1
      # 整个zk集群的机器、端口列表
      ZOO_SERVERS: server.1=0.0.0.0:2888:3888;2181 server.2=zk2:2888:3888;2181 server.3=zk3:2888:3888;2181
    # 将docker container上的路径挂载到宿主机上 实现宿主机和docker container的数据共享
    volumes:
      - ./zk1/data:/data
      - ./zk1/datalog:/datalog
    # 当前docker container加入名为zk-net的隔离网络
    networks:
      - zk-net

  zk2:
    image: zookeeper
    hostname: zk2
    container_name: zk2
    ports:
      - 2182:2181
      - 8082:8080
    environment:
      ZOO_MY_ID: 2
      ZOO_SERVERS: server.1=zk1:2888:3888;2181 server.2=0.0.0.0:2888:3888;2181 server.3=zk3:2888:3888;2181
    volumes:
      - ./zk2/data:/data
      - ./zk2/datalog:/datalog
    networks:
      - zk-net

  zk3:
    image: zookeeper
    hostname: zk3
    container_name: zk3
    ports:
      - 2183:2181
      - 8083:8080
    environment:
      ZOO_MY_ID: 3
      ZOO_SERVERS: server.1=zk1:2888:3888;2181 server.2=zk2:2888:3888;2181 server.3=0.0.0.0:2888:3888;2181
    volumes:
      - ./zk3/data:/data
      - ./zk3/datalog:/datalog
    networks:
      - zk-net

启动docker-compose, /opt/docker-compose up -d， /opt/是docker-compose的安装位置

有时需要加权限，chmod +x docker-compose，再启动docker-compose

输入网址192.168.183.129：8081/commands 出现

就安装ZooKeeper成功了

(不需要执行，记一下暂停docker-compose命令,/opt/docker-compose down)

然后再进入mycat ，vi conf/server.xml,vi conf/myid.properties,vi sequence_conf.properties改相应配置就好了

再mycat数据库创建新表，设置主键自增，就会自动做好自增了，适合用在myCat分库分表的时候

3.UUID 缺点比较致命，不推荐，字符串太长，无序，信息不安全，会暴露MAC地址,这个漏洞曾被用于寻找梅丽莎病毒的制作者位置

4.最佳解决方案-雪花算法（SNOWFLAKE）

雪花算法是由 Twitter 公布的分布式主键生成算法，它能够保证不同进程主键的 不重复性，以及相同进程主键的有序性。在同一个进程中**，它首先是通过时间位保证不重复，如果时间相同则是通过序列位保证**。 同时由于时间位是单调递增的，且各个服务器如果大体做了时间同步，那么生成 的主键在分布式环境可以认为是总体有序的，这就保证了对索引字段的插入的高效性。

例如 MySQL 的 Innodb 存储引擎的主键。使用雪花算法生成的主键，二进制表 示形式包含 4 部分，从高位到低位分表为：1bit 符号位、41bit 时间戳位、10bit 工作进程位以及 12bit 序列号位。

符号位 (1bit) 预留的符号位，恒为零。

时间戳位 (41bit) 41 位的时间戳可以容纳的毫秒数是 2 的 41 次幂，一年所使用的毫秒数是：365 * 24 * 60 * 60 * 1000。通过计算可知： Math.pow(2, 41) / (365 * 24 * 60 * 60 * 1000L); 结果约等于 69.73 年。 ShardingSphere 的雪花算法的时间纪元从 2016 年 11 月 1 日零点开始，可以使 用到 2086 年，相信能满足绝大部分系统的要求。

工作进程位 (10bit) 该标志在 Java 进程内是唯一的，如果是分布式应用部署应保证每个工作进程的 id 是不同的。该值默认为 0，可通过属性设置。

序列号位 (12bit) 该序列是用来在同一个毫秒内生成不同的 ID。如果在这个毫秒内生成的数量超过 4096 (2 的 12 次幂)，那么生成器会等待到下个毫秒继续生成。

这么多位都是为了保证雪花算法生成的主键唯一且是自增的

5.雪花算法的应用实现-美团的leaf(重要)

Leaf 是美团开源的分布式 ID 生成系统，最早期需求是各个业务线的订单 ID 生 成需求。

目前 LEAF 的使用有两种不同的思路，号段模式和 SNOWFLAKE 模式，你可以 同时开启两种方式，也可以指定开启某种方式（默认两种方式为关闭状态）。

用git拉去代码 git clone git@github.com:Meituan-Dianping/Leaf-git

在leaf.properties 配置

如果使用号段模式，需要数据库支持；如果使用 SNOWFLAKE 模 式，需要 Zookeeper 支持。

5.1 号段模式

号段模式还是基于数据库，但是思路有些变化，如下：

1.利用 proxy server 从数据库中批量获取 id，每次获取一个 segment (step 决 定其大小) 号段的值，用完之后再去数据库获取新的号段，可以大大的减轻数 据库的压力。

2.各个业务不同的发号需求用 biz_tag 字段来区分，每个 biz-tag 的 ID 获取相 互隔离，互不影响。

3.如果有新的业务需要扩区 ID，只需要增加表记录即可。 如果使用号段模式，我们首先需要创建一张数据表，脚本如下

CREATE DATABASE leaf
CREATE TABLE `leaf_alloc` (
`biz_tag` varchar(128) NOT NULL DEFAULT '',
`max_id` bigint(20) NOT NULL DEFAULT '1',
`step` int(11) NOT NULL,
`description` varchar(256) DEFAULT NULL,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`biz_tag`)
) ENGINE=InnoDB;
insert into leaf_alloc(biz_tag, max_id, step, description)
values('leaf-segment-test', 1, 2000, 'Test leaf Segment Mode Get
Id')

这张表中各项字段的含义如下：

biz_tag：业务标记（不同业务可以有不同的号段序列）

max_id：当前号段下的最大 id

step：每次取号段的步长

description：描述信息

update_time：更新时间

配置完成后，启动项目，访问 http: /localhost:8080/api/segment/get/leaf-segment-test 路径 （路径最后面的 leaf-segment-test 是业务标记），即可拿到 ID。

优点

Leaf 服务可以很方便的线性扩展，性能完全能够支撑大多数业务场景。

ID 号码是趋势递增的 8byte 的 64 位数字，满足上述数据库存储的主键要求。

容灾性高：Leaf 服务内部有号段缓存，即使 DB 宕机，短时间内 Leaf 仍能正 常对外提供服务。 可以自定义 max_id 的大小，非常方便业务从原有的 ID 方式上迁移过来。

缺点

ID 号码不够随机，能够泄露发号数量的信息，不太安全。 DB 宕机会造成整个系统不可用。

5.2 雪花模式

SNOWFLAKE 模式需要配合 Zookeeper 一起，不过 SNOWFLAKE 对 Zookeeper 的依赖是弱依赖，把 Zookeeper 启动之后，我们可以在 SNOWFLAKE 中配置 Zookeeper 信息，如下：

leaf.snowflake.enable=true
leaf.snowflake.zk.address=192.168.91.130
leaf.snowflake.port=2183

然后重新启动项目，启动成功后，通过如下地址可以访问到 ID：

http://localhost:8080/api/snowflake/get/test

6.Redis 生成

这个主要是利用 Redis 的 incrby 来实现，这个我觉得没啥好说的。

7.ZooKeeper(不推荐)

索引的分类

1.按照功能来划分

1.普通索引 2.唯一性索引 3.主键索引 4.全文索引

普通索引就是最最基础的索引，这种索引没有任何的约束作用，它存在的主要意 义就是提高查询效率。

唯一性索引则在普通索引的基础上增加了数据唯一性的约束，一张表中可以同时 存在多个唯一性索引

主键索引则是在唯一性索引的基础上又增加了不为空的约束（换言之，添加了唯 一性索引的字段，是可以包含 NULL 值的），即 NOT NULL+UNIQUE ，一张表 里最多只有一个主键索引，当然一个主键索引中可以包含多个字段。

全文索引其实我们很少在 MySQL 中用，如果项目中有做全文索引的需求，一般 可以通过 Elasticsearch 或者 Solr 来做，目前比较流行的就是 Elasticsearch 了。

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引。

MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引。

CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
#主键索引
PRIMARY KEY (`id`),
#普通索引
KEY `name` (`name`)
#唯一性索引
UNIQUE KEY `name` (`name`)
#全文索引
FULLTEXT KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

2.按物理实现划分

聚集索引（有的人也称之为“聚簇索引”）

非聚集索引（有的人也称之为“非聚簇索引”）

当我们基于 InnoDB 引擎创建一张表的时候，都会创建一个聚集索引，每张表都 有唯一的聚集索引：

  1. 如果这张表定义了主键索引，那么这个主键索引就作为聚集索引。
  2. 如果这张表没有定义主键索引，那么该表的第一个唯一非空索引作为聚集索 引。
  3. 如果这张表也没有唯一非空索引，那么 InnoDB 内部会生成一个隐藏的主键 作为聚集索引，这个隐藏的主键是一个 6 个字节的列，该列的值会随着数据 的插入自增。

聚集索引最主要的优势就是查询快。如果要查询完整的数据行，使用非聚集索引 往往需要回表才能实现，而使用聚集索引则能一步到位。

不过聚集索引也有一些劣势：

  1. 聚集索引可以减少磁盘 IO 的次数，这在传统的机械硬盘中是很有优势的，不 过要是固态硬盘或者内存（有时候为了提高操作效率，数据库服务器会整一 个比较大的内存），这个优势就不明显了。
  2. 聚集索引在插入的时候，最好是主键自增，自增主键插入的时候比较快，直 接插入即可，不会涉及到叶子节点分裂等问题（不需要挪动其他记录）；而 其他非自增主键插入的时候，可能要插入到两个已有的数据中间，就有可能 导致叶子节点分裂等问题，插入效率低（要挪动其他记录）。如果聚集索引 在插入的时候不是自增主键，插入效率就会比较低。

非聚集索引我们一般也称为二级索引或者辅助索引，对于非聚集索引，数据库会 有单独的存储空间来存放。非聚集索引查找数据需要回表

一张表只能有一个聚集索引，但可以有多个非聚集索引。使用聚集索引的时候， 数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚 集索引低。

数据库批量插入

数据库配置文件 url地址 加上rewriteBatchedStatements=true，打开批处理

设置batchSize，for循环拼接SQL语句，即将一万条数据拼接位一条sql,效率更快

另外一种是执行一万次插入SQL语句。

MyBatis 动态拼接拼接一条sql 用forEach

使用一次mapper.xml去执行sql语句都会建立一次SqlSession ，执行一万次太耗时间

如果数据特别大，还可以多次批量插入，一次存一万，存10次

        int i = 0;  
        //设置批量处理的数量             
        int batchSize = 5000;       
        stmt = con.prepareStatement("insert into mysqltest (id,name) "
                + "values (?,?)");
        // 关闭事务自动提交 ,这一行必须加上
        con.setAutoCommit(false);
        for (int j = 0; j < 50005; j++){
            ++i;
            stmt.setInt(1, j);  
            stmt.setString(2, "name");  
            stmt.addBatch();  
            if ( i % batchSize == 0 ) {
                stmt.executeBatch();
                con.commit();
            }
        }

转载：https://blog.csdn.net/Xs943/article/details/127654981
查看评论
* 以上用户言论只代表其个人观点，不代表本网站的观点或立场