简单聊聊 MyCat 分库分表

MyCat 是基于服务器代理模式的数据库分库的中间件,原理是对 SQL 进行转发,具体的架构图如下:

image-20200919120231986

我们知道,数据的拆分必然会对事物的原子性带来影响,那如果保证在分库的同时,又能保证事务的原子性呢?

如何解决分布式事务

XA 协议

XA协议是一个开源的事务协议,相关文【XA 事务处理】

XA 协议在第二段协议 commit 事提交务后,如果出现了有的机器事务没有提交成功,有的机器已经提交成功,这时已经提交的数据,数据就无法再次回滚,这样就造成了数据不一致的问题。

image-20200919224824416

三段提交协议

阅读更多

MySQL 分库分表的方式

对于分库分表来说,具体有两种方式:垂直拆分和水平拆分
垂直拆分主要是业务的细化和独立,和业务联系比较密切。所以本文只讨论更通用的水平拆分。

为什么分库分表

  1. 降低单机 MySQL 的性能
  2. 降低单表或者单库的数据量,减少数据库的查询压力
  3. 突破单机的容量限制

    分库分表的方式

  4. 范围区分(range):按月\按区\按其他的等特殊的属性维度进行分片
  5. 预定义范围:预估有多少数据的容量,对数据进行范围的分配,0-100->A 101-200->B
  6. 取模 Hash:对指定的字段进行取模运算,匹配对应的库和表。

分库分表带来的问题

  1. 数据的维护成本高
  2. 跨库的业务join
  3. 分布式事务的性能低下
  4. 自增 id 的生成问题
  5. 非分片字段查询的轮询的浪费
  6. 多节点排序问题

分库分表的中间件

对于分库分表的中间件有很多,Shardingsphere,Tddl,MyCat,cobar。从架构上分,主要分为两种:JDBC应用方式Proxy模式

JDBC应用模式是基于客户端的分片,有客户端根据Sql和规则,决定具体执行的 sql 的服务器。代表有Shardingsphere,Tddl

image-20200919120019296

JDBC应用模式 优点:

阅读更多

MySQL 索引失效问题

索引失效的情况:

  1. 使用 like ‘%abc’或者like ‘%abc%’
  2. 查询列参与了函数计算(并没有使用函数索引)
  3. 数据不够离散,扫描的行数和加载索引的成本超过了全表扫描
  4. 联合索引没有使用最左匹配,或者在范围运算(>,<,<>)等运算的后面
  5. where中索引列有运算

除了上面的几个明显的问题外,还有索引的选择问题。MySQL 在执行一段 sql 的时候,会先决定使用哪一个索引,如果 选了一个性能比较差的索引,即使走了索引,也会带来性能问题。

对上面的第 4 条做一个例子说明:

  1. 定义 abcd 字段一个联合索引
  2. 如果使用 a>0 and b=1 .. 则 a 本身走索引,但 a 后面的字段都不走索引
  3. a=1 and b=1 and c>1 and d=1 这个例子 只有 d 不走索引,如果 索引顺序更改为 abdc 则都会走索引。

准备工作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
create database ITTest;
use ITTest;
CREATE TABLE tbl_user (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
userName varchar(100) not null comment '姓名',
userAge int default 0 comment '年龄',
userStatus int default 0 comment '用户状态 0 有效,1 无效',
userSex int default 0 comment '性别 0 男 1 女',
birthDate DATE COMMENT '生日',
createTime DATETIME DEFAULT NOW() COMMENT '创建时间',
dataChangeLastTime DATETIME DEFAULT NOW() ON UPDATE NOW() COMMENT '最后更新时间'
);

alter table tbl_user add index idx_userName(userName);
alter table tbl_user add index idx_userSex(userSex);
alter table tbl_user add index idx_userStatus(userStatus);
alter table tbl_user add INDEX idx_union_userStatus_userSex_birthDate(userStatus,userSex,birthDate );

insert into tbl_user(userName,userAge,userStatus,userSex,birthDate)
values
('user001',rand()*100,rand(),rand(),curdate()),
('user002',rand()*100,rand(),rand(),curdate()),
('user003',rand()*100,rand(),rand(),curdate()),
('user004',rand()*100,rand(),rand(),curdate()),
('user005',rand()*100,rand(),rand(),curdate()),
('user006',rand()*100,rand(),rand(),curdate()),
('user007',rand()*100,rand(),rand(),curdate()),
('user008',rand()*100,rand(),rand(),curdate()),
('user009',rand()*100,rand(),rand(),curdate())

Explain 查看索引使用情况

image-20200909081934653

阅读更多

MySQL 主从同步

为什么需要主从复制

  1. 分别读写数据库的时候,把读和写分开,能够有效的提高数据库的负载
  2. 保证数据的高可用,一旦有一台数据库服务器宕机,不会对数据产生太大的影响
  3. 可以横向扩展,实现数据库的水平扩容

主从同步的原理

主从复制的根本原理是从 master 服务器上面的数据,通过一定的方式同步到 slave 服务器上面。基本过程如下图:

主从复制

  1. 主服务器在修改的数据的时候,会产生一个 bin log
  2. 从服务器上面启动一个 I/O thread,通过配置好的用户名和密码, 连接到主服务器上面请求读取二进制日志,然后把读取到的二进制日志写到本地的一个Realy log(中继日志)里面。
  3. 从服务器上面同时开启一个 SQL thread 定时检查 Realy log(这个文件也是二进制的),如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。

上面的 3 个过程是 MySQL 主从同步的大概流程,其中 binlogrelay log 的读写都是顺序 IO,性能很高。

Relay log转换成数据的过程是一个比较耗时的过程,一般出现了数据延迟的时候,基本都是这里的问题。

搭建主从同步

阅读更多

MySQL 事务日志

什么是事务日志?

事务要保证 ACID 的完整性必须依靠事务日志做跟踪:

  1. 每一个操作在真正写入数据数据库之前,先写入到日志文件中

  2. 如要删数据会先在日志文件中将此行标记为删除,但是数据库中的数据文件并没有发生变化。

  3. 只有在(包含多个 sql 语句)整个事务提交后,再把整个事务中的 sql 语句批量同步到磁盘上的数据库文件。

  4. 在事务引擎上的每一次写操作都需要执行两遍如下过程:

    • 先写入日志文件中

      写入日志文件中的仅仅是操作过程,而不是操作数据本身,所以速度比写数据库文件速度要快很多。

    • 然后再写入数据库文件中

      写入数据库文件的操作是重做事务日志中已提交的事务操作的记录

事务日志

事务的日志主要分为三类:redo log,undo logbinlog

日志组

在写日志的时候,单个日志如果过大,对于读写和同步都会产生影响,所以在日志变大的时候,需要对日志进行一个分组。

日志提高事务的效率和安全性保证

阅读更多

MySQL linux下的安装

  1. 下载包

    1
    https://repo.huaweicloud.com/mysql/Downloads/MySQL-8.0/mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
  2. 解压

    1
    2
    3
    4
    $ tar -xvf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz 
    # 移动到local/mysql目录下
    $ sudo mv mysql-8.0.20-linux-glibc2.12-x86_64 /usr/local/mysql

  3. 创建 data 目录

    1
    mkdir data
  4. 创建 MySQL 用户 并升级权限

    1
    2
    3
    useradd mysql
    chown -R mysql:mysql /usr/local/mysql
    chmod -R 755 /usr/local/mysql
  5. 初始化

    1
    ./mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql

    image-20200823152508875

    注意上面的红框是个初始的登录密码。

  6. 启动并修改密码

    1
    2
    cd support-files
    ./mysql.server start
  7. 连接修改密码

    在连接 MySQL 的时候出现依赖错误。

    1
    ./mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory

    image-20200823152726962

    这里是少了一个依赖导致的,可以使用 ldd mysql 来查看 MySQL 对应的依赖:

    image-20200823153059784

    从上图中可以看到,其中libtinfog.so.5依赖没有。

    这个文件一般在 /etc/lib64/,如果没有需要重现下载,或者拷贝一个。

    image-20200823153649214

    我本地是有的 6.0 ,直接创建一个同步链接就可以了:

    1
    sudo ln -s /usr/lib64/libtinfo.so.6.1 /usr/lib64/libtinfo.so.5
  1. 开始连接

    1
    ./mysql -uroot -p
  2. 修改密码和允许远程连接

    1
    2
    3
    4
    5
    6
    # 修改密码
    mysql>ALTER USER USER() IDENTIFIED BY 'yourpass';
    mysql>flush privileges
    mysql>use mysql;
    msyql>update user set user.Host='%' where user.User='root';
    mysql>flush privileges;
  3. 修改完还是无法连接

    测试是否是防火墙拦截了,先停止防火墙试试

    1
    2
    3
    4
    5
    # 火墙的状态
    $ firewall-cmd --state
    running
    $ systemctl stop firewalld

  4. 停止火墙后能够正常连接,说明是 MySQL 端口没有在防火墙中信任

    1
    2
    firewall-cmd --add-port=3306/tcp --permanent 开放某一个端口号
    systemctl restart firewalld 启动防火墙
阅读更多

探讨 MySQL 的锁

上篇博客说了 MVCC 解决了 MySQL 在可重复的隔离情况下幻读的问题,这篇博客主要探讨下,在修改的时候,如何解决幻读的问题。

MySQL 在控制并发的时候,同样采用了锁的机制。从读写上面分,有读写和写锁,从结构上分,有行锁和表锁.行锁又分为行锁、间隙锁和 Next Key

读锁和写锁

读锁 :共享锁 ,S 锁

写锁:排它锁 ,X 锁

select :不加锁,加锁后,也可以使用 select 查询数据

怎么加锁

select ...lock in share mode 加读锁

阅读更多

探讨 MySQL 事务隔离原理

数据库的事务一共有四个特性:

  1. 原子性:代表事务是一个动作,要么同时成功,要么同时失败
  2. 一致性:事务开始和结束数据完整性没有发生破坏
  3. 隔离性:两个事务动作相互独立,不受干扰
  4. 持久性:事务完成后,能够保存到数据库。

那 MySQL 是如何保证这个四个特性的呢?

为了弄明白这几个特性,我们需要先看下事务的隔离级别。

事务隔离级别

事务隔离级别分为 4 种,分别如下:

事务隔离级别 脏读 不可重复读 幻读
读未提交(read-uncommitted)
读已提交(read-committed)
可重复读(repeatable-read)
串行化(serializable)

上面的四种隔离级别,是通用的规则,在每一种不同的数据库中有不同的实现。

例如 MySQL 默认的事务隔离级别是 可重复读,但不会产生幻读的问题

阅读更多

一文彻底了解Mysql索引

在不借助DB引擎情况下,想完成数据的持久化存储,最简单的方法写一个文件存在本地,读取的时候加载文件到内存,然后进行筛选。 存储一个user在本地。

1
2
3
4
id|userName|userCode|sex|phoneNumber
1,xiaoming,001,m,126xxx
2,xiaohong,002,w,123xxx
...

上面满足了我们的需求,看到这种数据格式,查询的时间复杂度为O(n).

而且在每次读取文件的时候,需要把所有的数据都加载到内存(全表扫描),对系统的IO读写也很高。

解决上面的问题的最先想到的办法,再增加加一个文件(索引文件),根据字段(id)构建一个Hash表映射,哈希表中存储的是哈希值和数据对应的地址。 由于没有具体的数据,所以这个文件小的多,加载的成本比源文件低。

每次查询先hash对应得字段的值,然后直接在Hask表中找到地址,加载数据返回查询结果,时间复杂度为O(1)这个就是哈希索引雏形。

hash的加入解决查询的问题,但是局限性也很多,如果精确查询id=1能够发挥hash表的优势,查询中如果有>,<<>等查询的时候,就无法使用Hash索引。

能够提高搜索效率的数据结构除了hash表外,还有树形结构,二叉搜索树,B树等。

采用二叉树时候,为了避免查询的深度太深,采用平衡二叉树。 假设user表有10条数据,然后把id作为索引构建成一个二叉平衡树,创建好的数据结构作为索引存储到文件中。

阅读更多

B-Tree和B+树

B树和B+树是很多数据库索引采用的数据结构,为什么会使用B树,而不采用更常见的二叉树的呢?

举个例子,有这么几个数字:1,2,3,4,5,6,7,8,9,0,分别生成AVL树,B树

AVL树结构
B树

二叉树生出的树的度为4,而3阶B树高度只是3.如果B树的阶数再多的话,就可以获得更小的高度度。

B树

(6阶的B树)

树的度带来更深入查询,会带来更多的IO读写。

除了二叉树的深度太深的原因,二叉树对于操作系统IO的读取也不是特别的友好。二叉树的节点过于简单,信息过于少。当操作系统进行IO操作的时候,最少读取的字节数是4K,为了保证IO的读取性能,也可能进行预读下个节点等等。

如果二叉树的一个节点只有1KB的话,操作系统每次读取二叉树的时候,只有1KB的有效信息,那这次IO操作的剩余的3KB就是无效的读取。

B树

B树又称为多路平衡查找树,在上面看到了B树的简单结构,在真正的文件存储结构如下:

阅读更多