MYSQL-高级

索引

索引概述

索引: 索引(index)是帮助MYSQL 高效获取的数据结构。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现查找算法，这些数据结构就是索引。

引出

无索引	有索引

在SQL: select * from user where age = 45 这条语句上,在没有索引时,查询数据时会进行全表扫描,这种效率是极低的,而当建立了索引时,维护了一个二叉树(用于举例),因为age=45,此时会将45 与该节点（36）进行比较,在这条SQL 中,要命中也只进行了3 次比较,就获取到了数据

上面的二叉树只是一个示意图,并不是真实的索引结构

索引的优势
1. 类似于书籍的目录索引，提高数据检索效率，降低数据库的IO 成本
2. 通过索引列对数据进行排序，降低数据排序的成本，降低CPU 的消耗
索引的劣势
1. 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是占用空间的
2. 虽然索引大大提高了效率，同时却也降低了更新表的速度，如对表进行insert、update、delete，因为更新表时，MYSQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

树

二叉树

二叉树	红黑树

二叉树缺点: 顺序插入时,会形成一个链表,查询性能大大降低。大数据量情况下,层级较深,检索速度较慢

通过红黑树解决二叉树的平衡问题,红黑树在大数据量情况下,层级较深,检索速度慢。因此后面出现B-TREE

索引结构

MYSQL 目前提供的索引
- BTREE 索引：最常见的索引类型，大部分索引都支持B树索引
- HASH 索引：只有Memory 引擎支持，使用场景简单
- R-tree 索引：空间索引是MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据结构，通常使用较少
- Full-text 索引：全文索引也是MyISAM 的一个特殊索引类型，主要用于全文索引，InnoDB 从MYSQL5.6 版本开始支持全文索引

BTREE 结构

树的度数指的是一个节点的子节点个数

BTree 又叫多路平衡搜索树，一颗m 叉的BTree 特性如下：

树中每个节点最多包含m 个孩子
除根节点与叶子节点外,每个节点至少有ceil(m/2) 个孩子
若根节点不是叶子节点，则至少有两个孩子
所有的叶子节点都在同一层
每个非叶子节点由n 个key 与n+1 个指针组成,其中[ceil(m/2)]<= n <= m-1

`BTree`

查看Btree的分裂过程: https://www.cs.usfca.edu/~galles/visualization/BTree.html

B+Tree 结构

B+Tree 为BTree 的变种，B+Tree 与BTree 的区别为:

n 叉B+Tree 最多含有n 个key,而BTree 最多含有n-1 个key
B+Tree 的叶子节点保存所有的key 信息,依key 大小顺序排列

所有的非叶子节点都可以看作是key 的索引部分

经典`B+Tree`	`MYSQL` 中对`B+Tree` 进行了优化

MYSQL 索引数据结构对经典的B+Tree 进行了优化,在原B+Tree 的基础上,增加了一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree，提高区间访问的性能

Hash 索引

哈希索引就是采用一定的hash 算法,将键值换算成新的hash 值,映射到对应的槽位上，然后存储在hash 表中

哈希碰撞: 如果两个(或者多个)键值,映射到一个相同的槽位上,它们就产生了hash 冲突(也成为hash 碰撞),可以通过链表来解决

哈希索引

特点
- Hash 索引只能用于对等比较=,in，不支持范围查询(between,>,<,...)
- 无法利用索引完成排序操作
- 查询效率高,通常(只要不出现哈希碰撞)只需要一次检索就可以了,效率通常要高于B+tree 索引

B+Tree 相对于BTree 的区别
- B+Tree 所有的数据都会出现在叶子节点
- 叶子节点形成一个单向链表

索引分类

分类

分类	含义	特点	关键词
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	`primary`
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	`unique`
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	`fulltext`

索引语法

基础语法

-- 创建索引
create index idx_test_name on tableName (table-key);

-- 查看索引
show index from tableName;

-- 删除索引
drop index index_name on tableName;

练习

name 字段为姓名字段，该字段的值可能会重复，为该字段创建索引

1	create index idx_user_name on t_user(name);

phone 手机号字段的值,是非空,且唯一的，为该字段创建唯一索引

1	create unique index idx_user_phone on t_user(phone);

为profession，age,status 创建联合索引

1	create index idx_user_pro_age_status on t_user(profession,age,status);

为email 建立合适的索引来提升查询效率

1	create index idx_user_email on t_user(email);

查看当前表有那些索引信息

在InnoDB 存储引擎中,根据索引的存储形式，又可以分为如下两种

分类	含义	特点
聚集索引	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有，而且只能有一个
二级索引(也叫非聚簇索引)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则

如果存在主键，主键索引就是聚集索引
如果不存在主键，将使用第一个唯一索引作为聚集索引
如果表没有主键，或没有合适的唯一索引，则InnoDB 会自动生成一个rowid 作为隐藏的聚集索引

聚集索引与非聚集索引的区别

select * from user where name = 'Arm'

执行流程

思考那个执行效率高
- select * from user where id = 10 (效率高)
- select * from user where name = 'Arm'(需要回表)

为什么 InnoDB 存储引擎选择使用 B+tree 索引

二叉树和红黑树

二叉树和红黑树

树形结构的缺点
- 二叉树缺点: 顺序插入时,会形成一个链表，查询性能大大降低。大数据情况下,层级较深，检索速度慢
- 红黑树: 大数据量情况下,层级较深,检索速度慢
选择B+tree 的原因
- 相对于二叉树，层绞更少，搜索效率高
- 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据,只能增加树的高度，导致性能降低
- 相对于Hash 索引，B+tree 支持范围匹配以及排序操作

SQL 性能分析

显示当前session 中所有统计参数的值

# [session|global] 命令可以提供服务器状态信息，通过如下指令，可以查看当前数据库的 insert,update,delete,select 的访问频次 _(7个)
show global status  like 'Com_______';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 0     |
| Com_import    | 0     |
| Com_insert    | 0     |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 2     | # 查询两次
| Com_signal    | 0     |
| Com_update    | 0     |
| Com_xa_end    | 0     |
+---------------+-------+
11 rows in set (0.00 sec)

查看Innodb 对于增删改查影响

1	show global statys like 'Innodb_rows_%';

慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位: 秒,默认10s)的所有SQL 语句的日志

MYSQL 的慢查询日志默认没有开启，需要在MYSQL 的配置文件(/etc/my.cnf)中配置如下信息:

查看是否开启

# 可以看出,默认关闭
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.00 sec)

开启慢查询开关

# 开启 MYSQL 慢查询开关
slow_query_log=1
# 设置慢日志的时间为 2s，SQL 语句执行时间超过 2s，就会视为慢查询,记录慢查询日志
long_query_time=2

配置文件保存退出后,重启MYSQL 服务器进行测试,查看慢日志文件中记录的值信息/var/lib/mysql/localhost-slow.log

1 2	# 重启 mysqld 服务 systemctl restart mysqld

profile 详情

show profiles 能够在做SQL 优化时帮助我们了解时间都耗费哪里去了

查看当前数据库是否支持

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)

# YES: 支持

默认profiling 是关闭的，可以通过set 语句在session/global 级别开启profiling

# 查看 profiling 是否开启
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

# 开启
set profiling = 1;

详情

执行一系列的业务 SQL 的操作,然后通过如下指令查看指令的执行耗时

-- 查看每一条 SQL 的耗时情况
show profiles;

-- 插卡指定 query_id 的 SQL 语句各个阶段的耗时情况
show profile for query_id;

-- 查看指定 query_id 的 SQL 语句 CPU 的使用情况
show profile cpu for query query_id;

explain

是什么

使用desc/explain 关键字可以模拟优化器执行SQL 查询语句，从而知道MYSQL 是如何处理SQL 语句的。
怎么用?

| explain/desc |

| :———————————————————-: |
| |

字段解释
- id
  
  select 查询的序列号，表示查询中执行select 子句或者是操作表的顺序(id 相同,执行顺序从上到下,id 不同,值越大,越先执行)
- select_type
  表示select 的类型,常见的取值有:
  - SIMPLE(简单表,即不使用表连接或者子查询)
  - PRIMARY(主查询,即外层的查询)
  - UNION(UNION 中的第二个或者后面的查询语句)
  - SUBQUERY(SELECT/WHERE 之后包含了子查询)
- type
  
  表示连接类型，性能由好到差的连接类型为NULL，system，const,eq_ref,ref,range,index,all
- possible_key：显示可能应用在这张表上的索引,一个或多个
- key: 实际使用的索引,如果为NULL，则没有使用索引
- key_len: 表示索引中使用的字节数,该值为索引字段中最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
- rows: MYSQL 认为必须要执行查询的行数,在InnoDB 引擎的表中,是一个估计值,可能并不是准确的
- filtered: 表示返回结果的行数占需读取行数的百分比,filtered 的值越大越好

索引-使用规则

百万数据插入表中时,该表需要建立主键(索引产生)

最左前缀法则

如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

与位置(位置: 最左侧的键,只需要存在即可)无关

分析，经过`索引`

跳过联合索引中最左侧字段时,`索引失效`	缺少除了最左侧或者跳跃外,省略字段时,依然经过索引

范围查询

TODO: 如何确定是那个字段没有进入索引范围的？

联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效

解决方案: 将`>/<` 修改为`>= / <=(业务允许时)`

索引列运算：不要在索引列上进行运算操作,否则索引将失效

列运算导致索引失效（ `explain select * from t_user where substring(phone,10,2)='15';`）

字符串不加引号：字符串类型字段使用时,不加引号,索引将失效

字符串类型字段使用时,不加引号,`索引将失效`

注意

模糊查询: 如果仅仅是尾部模糊查询匹配,索引不会失效,如果是头部模糊匹配，索引失效

模糊查询失效情况

or 连接条件

用 or 分割开的条件,如果or 前的条件中的列有索引,而后面的列中没有索引，那么涉及的索引都不会被用到

`phone: idx_user_ohone age:null => 索引失效`

要解决or 连接索引不失效,只需要为其添加索引即可

解决`or` 索引失效

数据分布影响: 如果MYSQL 评估使用索引比全表更慢,则不使用索引

预估以下范围小于该表，则选择索引	数据分布导致索引失效

SQL 提示：是优化数据库的一个重要手段,简单来说，就是在SQL 语句中加入一些认为的提示来达到优化操作的目的

问题引出: 为表字段`profession` 创建索引,查询时依然使用联合索引

use index(使用索引)

1	explain select * from t_user use index(idx_user_profession) where profession='化工';

使用指定索引

ignore index(忽略某个索引)

1	explain select * from t_user ignore index(idx_user_pro_age_status) where profession='化工';

忽略某个索引

force index(强制使用某个索引)

1	explain select * from t_user force index(idx_user_profession) where profession='化工';

强制使用某个索引

覆盖索引: 尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到),减少select *

using index condition: 查找使用了索引，但是需要回表查询数据

using where;using index: 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要徽标查询数据

覆盖索引

思考题

一张表，有四个字段(id,username,password,status) 由于数据量大，需要对以下SQL 语句进行优化，该如何进行才是最优方案

1	select id,username,password,status from t_user where username ='coder-itl';

答: 对username,password 建立联合索引

辅助索引下挂的是`id`

前缀索引

当字段类型为字符串(varchar,text 等)时,有时候需要索引很长的字符串,这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

1 2	-- n: 代表选取的字符个数 create index idx_xx on table_name(column(n));

前缀长度

可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的

1
2
3

-- 某个字段的选择性计算公式
select count(distinct email)/count(*) from t_user;
select count(distinct substring(email,1,5))/count(*) from t_user;

单列索引和联合索引

单列索引: 即一个索引只能包含单个列

联合索引: 即一个索引包含了多个列

在业务场景中，如果存在多个查询条件，考虑针对查询字段建立索引时,建议建立联合索引，而非单列索引

单列索引和联合索引

联合索引的`B+Tree` 结构

trace分析器执行计划

设置格式

1 2	set optimizer_trace="enabled=on",end_markers_in_json=on; set optimizer_trace_max_mem_size=100000;

执行sql

1	select xxx from xxx;

检查infomation_schema.optimizer_trace 就可以知道mysql 是如何执行的

1	select * from information_schema.optimizer_trace\G;

索引使用

索引失效情况

范围查询，字段运算，字符串不加单引号(根据数据的类型)

name字段是索引列，而 createtime 不是索引列，中间是 or 进行连接是不走索引的

以 % 开头的 like模糊索引查询,索引失效，仅仅是尾部模糊匹配，索引就不会失效

如果使用索引，发现全表扫描更快，就不走索引

is null,is not null 有时索引失效

in 走索引,not in索引失效

使用覆盖索引避免索引失效

单列索引和复合索引如何选择?

尽量选择复合索引

索引设计原则

设计原则
- 针对于数据量较大，且查询比较频繁的表建立索引
- 针对于常作为查询条件(where)，排序(order by)，分组(group by)操作的字段建立索引
- 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
- 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提升查询效率
- 要控制索引的数量,索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
- 如果索引列不能存储null 值，请在创建表时使用NOT NULL 约束它。当优化器知道每列是否包含NULL 值时，它可以更好地确定哪个索引最有效的用于查询

SQL优化

大批量插入数据

主键顺序插入

1
2

因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率
如果 InnoDB 表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率

大批量数据加载使用步骤

# 客户端连接服务端时,加上参数 --local-infile
mysql --local-infile -u root -p
# 设置全局参数 local_infile = 1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
# 执行 load 指令将准备好的数据，加载到表结构中

1
2
3

load data local infile '文件路径' into table '数据表' fields terminated by ',' lines terminated by '\n';
# Eg
load data local infile '/root/sqlScript/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n';

数据脚本模型	待插入表结构	执行单一插入操作，百万条数据耗时大约半小时

关闭唯一索引校验

# 导入前
set unique_checks = 0;

# 执行导入结束后
set unique_checks = 1;

手动提交事务

# 导入数据前
set autocommit = 0;
# 导入数据结束后
set autocommit = 1;

优化 Insert 语句

如果需要同时对一张表插入很多数据，应该尽量使用多个值表的insert 语句，这种方式可以大大的所见客户端与数据库之间的连接，关闭等消耗

原始方式

1
2
3

insert into tb_test values(1,'test01');
insert into tb_test values(2,'test02');
insert into tb_test values(3,'test03');

优化后

1	insert into tb_test values(1,'test01'),(2,'test02'),(3,'test03');

手动提交事务

start transition;
    insert into tb_test values(1,'test01');
    insert into tb_test values(2,'test02');
    insert into tb_test values(3,'test03');
commit;

数据有序插入

原始

insert into tb_test values(3,'test03');
insert into tb_test values(4,'test04');
insert into tb_test values(1,'test01');
insert into tb_test values(2,'test02');

优化后

insert into tb_test values(1,'test01');
insert into tb_test values(2,'test02');
insert into tb_test values(3,'test03');
insert into tb_test values(4,'test04');

主键优化

数据组织方式

在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organzied table IOT)

逻辑结构

逻辑存储结构

页分裂

页可以为空，也可以填充一般，也可以填充100%，每个页包含了2-N 行数据(如果一个数据较大，会行溢出),根据主键排列

主键顺序插入

主键顺序插入，当一页存满后，申请下一页

主键乱序插入

乱序

当存储50 这个元素时，不会直接插入在第三页中,而是在第一页的50% 处，在第一页超出50% 的元素移动到第三页中,再将50 这个元素插入，重新设置链表，排列成顺序链表，此时，导致页分裂

页分裂

页合并

当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且他的空间变得允许被其他记录声明使用

删除`13-16,实则被标记`

当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%)，InnoDB 会开始寻找最靠近的页(前或后)看看时候可以将两个页合并以优化空间使用

页合并

在标记的页中，在50% 处，开始寻找前后靠近的页，在第一页中不满足和并要求，而在下一页中满足合并，那么将该页中的数据移动到标记处，在将新插入的元素放入空闲页中，完成页合并

MERGE_THRESHOLD: 合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

主键设计原则

满足业务需求的情况下，尽量降低主键的长度

因为二级索引叶子节点挂的是`id`,越长越耗费存储空间

插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT 自增主键
尽量不要使用UUID 做主键或者其他自然主键，如身份证
业务操作时，避免对主键的修改

优化 order by语句

Using filesort: 通过表的索引或全表扫描，读取满足条件的数据行，然后再排序缓冲区sort buffer 中完成排序操作，所有不是通过索引直接排序结果的排序都叫FileSort 排序
Using index: 通过有序索引顺序扫描直接返回有序数据，这种情况叫using index，不需要额外排序，操作效率高

优化

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
尽量使用覆盖索引(字段)
多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)

如果不可避免的出现filesort,大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认: 256k)

1 2	-- 查看默认的排序缓冲区大小 show variables like 'sort_buffer_size';

优化 group by 语句

如果查询包含group by 但是用户想要避免排序结果的消耗，则可执行order by 禁止排序

1
2
3

select  xxx from xxx group by xxx;
优化后
select  xxx from xxx group by xxx order by null;

在分组操作时，可以通过索引来提高效率
分组操作时，索引的使用也是满足最左前缀法则的

分页查询的优化

一般分页查询时，通过创建覆盖索引能够比较好的提高性能。一个常见又非常头疼的问题就是limit 2000000,10 此时就需要MYSQL 排序前200000010 记录，仅仅返回2000000-200000010 的记录，其他丢弃记录，查询排序的代价非常大

优化思路一:

在索引上完成排序分页操作，最后根据主键关联回原表查需所需要的其他列内容

1	select * from tb_test,(select xxx from xxx order by xxx limit 2000000,10) a where xxx = xxx;

优化思路二

该方案适用于主键自增的表，可以把limit 查询转换成某个位置的查询

1	select * from xxx where xx > xxx limit 10;

count 优化

count(主键): InnoDB 引擎会遍历整张表，把每一行的主键id 值都取出来，返回给服务层。服务层拿到逐渐后，直接按行进行累加(主键不可能为NULL)
count(字段)
- 没有not null 约束： InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null,不为null，计数累加。
- 有 not null 约束: InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
count(*): InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话count(字段)id),所以尽量使用count(*)

count

update 优化

优化

InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

视图

视图是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图查询中使用的表，并且是在使用视图时动态生成的
通俗的讲，视图只保存了查询的SQL 逻辑，不保存查询的结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL 查询语句上

锁

是什么？

锁是计算机协调多个进程或线程并发访问某一资源的机制，在数据库中，除传统的计算机资源的争用外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

分类： MYSQL 中的锁，按照粒度分，分为以下三类

全局锁: 锁定数据库中的所有表

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML 的写语句，DDL 语句，以及更新操作的事务提交语句都将被阻塞

其典型的使用场景是做全库的逻辑备份，对素有的表进行锁定，从而获取一致性视图，保证数据的完整性。

未加锁的备份	加锁后

锁的添加与释放

特点

如果在主库上备份，那么在备份期间都不能执行更新操作，业务基本上就是停滞
如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志binlog，会导致主从延迟

在InnoDB 引擎中，我们可以在备份时加上参数--single-transaction 参数来完成不加锁的一致性数据备份

1	mysqldump --single-transaction -uroot -proot coderitl>D:/coderitl.sql

表级锁: 每次操作锁住整张表

表级锁: 每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高,并发度最低。应用在MyISAM,InnoDB,BOB 等存储引擎中

表级锁分类

表锁

表共享读锁

会阻塞其他客户端的`DDL/DML` 操作,不会影响`DQL（读）`	阻塞

表独占写锁

加写锁后，仅允许自己的客户端执行写(独占写),阻塞其他客户端的读写

语法

加锁: lock table 表名 read/write
释放锁: unlock tables / 客户端断开连接

-- 加锁
lock table course read;
-- 释放锁
unlock tables;

元数据锁

MDL(元数据锁简称) 加锁过程是系统和自动控制，无需显示使用，在访问一张表的时候会自动加上。MDL 锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。

对一张表进行增删改查的时候,加MDL 读锁(共享);当对表结构进行变更操作的时候，加MDL 写锁(排他锁)

元数据锁

意向锁

引出：行锁与表锁的冲突

意向共享锁: 与表锁共享锁(read)兼容,与表锁排他锁互斥
意向排他锁: 与表锁共享锁(read)及排他锁(write)都互斥。意向锁之间不会互斥。

行级锁: 每次操作锁住对应的行数据

行级锁: 每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB 存储引擎中。

InnoDB 的数据是基于索引组织的，行锁是通过索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类:

行锁(Record Lock): 锁定单个行记录的锁，防止其他事务对此进行update 和delete.在RC,RP 隔离级别下都支持。

行锁: 锁定单个行记录的锁

间隙锁：锁定索引记录间隙(不含该记录),确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在PR 隔离级别下都支持。

间隙锁

临键锁: 行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR 隔离级别下支持。

行锁: InnoDB 实现了以下两种类型的行锁

共享锁(S): 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
排他锁(X): 允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁

行锁

事务原理

事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功，要么同时失败。
特性
- 原子性(Atomicity): 事务是不可分割的做小操作单元，要么全部成功，要么全部失败
- 一致性(Consistency): 事务完成时，必须使所有的数据都保持一致状态
- 隔离性(Isolation): 数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
- 持久性(Durability): 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

原理

分为两个方面

redo log(重做日志)：(保障持久性)

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成: 重做日志缓冲(redo log buffer)以及重做日志文件(redo log file)，前者是在内存中，后者是在磁盘中。当事务提交之后会把所有的修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用。

脏页产生	出现`redo log`

undo log

回滚日志，用于记录数据被修改前的信息，作用包含两个: 提供回滚和MVCC(多版本并发控制)

undo log 和redo log 记录物理日志不一样，它是逻辑日志，可以认为当delete 一条记录时，undo log 中会记录一条对应的insert 记录，反之亦然，当update 一条记录时，它记录一条对应相反的update 记录。当执行rollback 时，就可以从undo log 中的逻辑记录读取到响应的内容并进行回滚

undo log 销毁: undo log 在事务执行时产生，事务提交时,并不会立即删除undo log，因为这些日志可能还用于MVCC

undo log 存储: undo log 采用段的方式进行管理和记录，存放在前面介绍的rollback segement 回滚段中，内部还包含1024 个undo log segement

MVCC-基本概念

当前读

读取的时记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如select .... lock in share mode(共享锁),select ... for update,update,insert,delete(排他锁)都是一种当前读
快照读

简单的select(不加锁) 就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
- Read Committed: 每次select，都生成一个快照读
- Repeatable Read: 开启事务后第一个select 语句才是快照读的地方
- Serializable: 快照读退化为当前读

MVCC

全称(Multi-Version Concurrency Control),多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MYSQL 实现MVCC 提供了一个非阻塞读功能。MVCC 的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log 日志，readView

记录中的隐藏字段

隐藏字段	含义
`DB_TRX_ID`	最近修改事务`ID`，记录插入这条记录或最后一次修改记录的事务`ID`
`DB_ROLL_PTR`	回滚日志，指向这条记录的上一个版本，用于配合`undo log`,指向上一个版本
`DB_ROW_ID`	隐藏主键，如果表机构没有指定主键，将会生成该隐藏字段

系统数据库

数据库

数据库	含义
`mysql`	存储`MYSQL` 服务器正常运行所需要的各种信息(时区、主从、用户、权限等·)
`infomation_schema`	提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等
`perfromance_schema`	为`MYSQL` 服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数
`sys`	包含了一系列方便`DBA` 和开发人员利用`preformance_sechma` 性能数据库进行性能调优和诊断的视图

日志

错误日志

错误日志是MYSQL 中最重要的日志之一，它记录了当mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

该日志默认是开启的，默认存放目录/var/log，默认的日志文件名mysqld.log。查看日志位置

1	show variables like '%log_error%';

二进制日志

二进制日志(binlog)记录了所有的DDL(数据定义语言) 语句和DML(数据操纵语言) 语句，但不包括数据查询(select,show)语句

作用

灾难时的数据恢复
MYSQL 的主从复制。在MYSQL8 版本中，默认二进制日志是开启的，涉及到参数如下:

1 2	-- 查看位置 show variables like '%log_bin%';

格式

日志格式	含义
`statement`	基于`SQL` 语句的日志记录，记录的是`SQL` 语句，对数据进行修改的`SQL` 都会记录在日志文件中
`row`	基于行的日志记录，记录的是每一行的数据变更(`默认`)
`mixed`	混合了`statement` 和`row` 两种格式，默认采用`statement`，在某些特殊情况下会自动转换为`row` 进行记录

1 2	-- 查看默认的日志格式 show variables like '%binlog_format%';

日志查看

由于日志是以二进制方式存储，不能直接读取，需要通过以二进制日志查询工具mysqlbinlog 来查看,具体语法如下:

mysqlbinlog [参数选项] logfilename

参数选项:
	-d: 指定数据库名称，只列出指定的数据库相关操作
	-o: 忽略掉日志中的前 n 行命令
	-v: 将行(行(row))事件(数据变更) 重构为 SQL 语句
	-w: 将 行事件 (数据变更) 重构为 SQL 语句,并输出注释信息
	
# 查看
mysqlbinlog -v binlog.000001

此位置下的`binlog.00000x`

修改日志显示格式

# 修改配置文件
vim /etc/my.cnf

# 末尾添加 以 SQL 语句形式显示
binlog_format=STATEMENT

# 重启服务
systemctl restart mysqld

日志删除

对于比较繁忙的业务系统，每天生成的binlog 数据巨大，如果长时间不清除，将会占用打量磁盘空间。可以通过以下几种方式清理日志:

指令	含义
`reset master`	删除全部`binlog` 日子，删除之后，日志编号，将从`binlog.000001` 重新开始
`purge master logs to 'binglog.******'`	删除`******` 编号之前的所有日志
`purge master logs before 'yyy-mm-dd hh24:mi:ss'`	删除日志为`yyyy-mm-dd hh24:mi:ss` 之前产生的所有日志

也可以在 mysql 的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除

1 2	# 查看默认的过期时间（默认时间: 30天） show variables like '%binlog_expire_logs_seconds%';

查询日志

查询日志记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL 语句。默认情况下，查询日志是为开启的。如果需要开启查询日志，可以设置以下配置:

1 2	-- 查看查询日志开启状态(默认关闭) show variables like '%general%';

开启

# 修改配置
vim /etc/my.cnf

# 该选项用来开启查询日志,可选值: 0 或者 1,0: off,1: on
general_log=1
# 设置日志的文件名,如果没有指定,默认的文件名为 host_name.log
general_log_file=mysql_query.log

# 重启服务
systemctl restart mysqld

开启前	开启后	`mysql_query.log` 记录内容为`crud,ddl操作`

慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time 设置值并且扫描记录数不小于min_examined_row_limit 的所有的SQL 语句的日志，默认未开启。long_query_time 默认为10s，最小为0，精度可以到微妙

# 开启慢查询日志记录
slow_query_log=1
# 超过2s视为慢查询
log_query_time=2

查看慢查询是否被记录

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用log_slow_admin_statements 和更改此行为

log_queries_noe_using_indexes

# 记录执行比较慢的管理语句(修改 my.cnf)
log_slow_admin_statements=1
# 记录执行较慢的未使用索引的语句
log_queries_not_using_indexes=1

主从复制

概述

主从复制是指将主句库的DDL 和DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步

MYSQL 支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。
主从复制的好处
1. 主库(主库: master)出现问题，可以快速切换到从库提供服务
2. 实现读写分离，降低主库的访问压力
3. 可以在从库(从库: Slave)中执行备份，以避免备份期间影响到主库服务

主从复制的原理

主从复制的原理

从上图看，复制分成三步:

Master 主库在事务提交时，会把数据变更记录在二进制日志文件Binlog 中
从库读取主库的二进制文件Binlog，写入到从库的中继日志Relay Log
Slave 重做中继日志中的事件，将改变反映给它自己的数据

搭建

服务器准备

安装`MYSQL80`

# 开放指定的 3306 端口
firewall-cmd --zone=public --add-port=3306/tcp -permanent
firewall-cmd -reload

# 关闭服务器的防火墙
systemctl stop firewalld
systemctl disable firewalld

主库配置

修改主库的配置文件

# mysql 服务 ID,保证整个集群环境中唯一,取值范围: 1-2^32-1，默认为 1
server-id=1
# 是否只读,1: 只读,0: 读写
read-only=0


# 忽略的数据,指不需要同步的数据库 [可选]
# binlog-ignore-db=mysql
# 指定同步的数据库
# binlog-do-db=coderitl

重启MYSQL 服务

1	systemctl restart mysqld

# 创建用户 coder-itl,并设置密码为: Root@root,该用户可以在任意主机连接该 MYSQL 服务
CREATE USER 'coder-itl'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@root';
# 为 `coder-itl`@'%' 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'coder-itl'@'%';

通过指令,查看二进制日志坐标

1	show master status;

`File Position`

字段含义
- file: 从那个日志文件开始推送日志文件
- position：从那个位置开始推送日志
- binlog_ignore_db: 指定不需要同步的数据库

不要在主库执行其他任何语句

从库配置

修改从库下的配置文件

# mysql 服务 ID,保证整个集群环境中唯一,取值范围: 1-2^32-1，和主库不一样即可
server-id=2
# 是否是只读,1: 只读,0: 读写
read-only=1

重启从库的MYSQL 服务

1	systemctl restart mysqld

# 从库执行 (MYSQL version 8.0.23+)
CHANGE replication source TO 
	source_host = '192.168.2.3',
	source_user = 'coder-itl',
	source_password = 'Root@root',
	source_log_file = 'binlog.000011',
	source_log_pos = 671;

#  (MYSQL version 8.0.23-)
CHANGE MASTER TO 
	MASTER_HOST = '192.168.2.3',
	MASTER_USER = 'coder-itl',
	MASTER_PASSWORD = 'Root@root',
	MASTER_LOG_FILE = 'binlog.000011',
	MASTER_LOG_POS = 671;

参数名	含义	`8.0.23` 之前
`source_host`	主库`IP` 地址	`master_host`
`source_user`	连接主库的用户名(`coder-itl`)	`master_user`
`source_password`	连接主库的密码(`Root@root`)	`master_password`
`source_log_file`	`binlog` 日志文件名(`binlog.000011(主库获取)`)	`master_log_file`
`source_log_pos`	`binlog` 日志文件位置(`671: 主库获取`)	`master_log_pos`

在从库上开启同步操作

1 2	start replica; # mysql 8.0.22 之后 start slave; # mysql 8.0.22 之前

从库执行查看主从同步状态

1 2	show replica status\G; # mysql 8.0.22 之后 show slave status\G; # mysql 8.0.22 之前

查看从库相对于主库是否正常

`观察Slave_IO_Running 和 Slave_SQL_Running` 为`YES` 即为正常

测试主从是否同步

正常同步主库

上述步骤执行完毕查看时，如果出现Slave_IO_Running 为No，并出现日志错误(index not found)解决方案

Slave_IO_Running: No

# 1. 登录MYSQL主库服务器，停止从库同步
stop slave;
# 2. 主库中关闭当前的二进制日志文件并创建一个新文件
flush logs;
# 3. 查看主库状态,主要查看日志文件和位置
show master status\G;

主库执行

# 4. 登录 MYSQL 从库服务器
change master to master_log_file='主库中获取到 binlog.000012',master_log_pos=156;
# 启动从库
start slave;
# 查看从库信息
show slave status\G;

测试是否同步

主从执行流程

疑问

只读从库写入了数据？	主从数据发生了不一致

由于从库登录的是root 用户,拥有的权限比较大,所以出现上述情况

测试test 用户

使用其他用户登录

分库分表

单数据库出现的瓶颈
随着互联网及移动互联网的发展,应用系统的数据量也是指数式增长,若采用单数据库进行数据存储，存在以下性能瓶颈
1. IO瓶颈: 热点数据太多,数据库缓存不足,产生大量磁盘IO，效率较低。请求数据太多,带宽不够,网络IO 瓶颈
2. CPU 瓶颈: 排序、分组、连接查询、聚合统计等SQL 会耗费大量的CPU 资源，请求数太多，CPU 出现瓶颈
分库分表的中心思想就是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。

垂直拆分

垂直拆库: 以表为依据，根据业务将不同表拆分到不同库中

特点

每个库的表结构都不一样
每个库的数据也不一样
所有库的并集是全量数据

垂直分库

垂直分表: 以字段为依据，根据字段属性将不同字段拆分到不同表中

特点

每个表的结构都不一样
每个表的数据也不一样，一般通过一列(主键/外键)关联
所有表的并集是全量数据

垂直分表

水平拆分

水平分库: 以字段为依据，按照一定策略，将一个库的数据拆分到多个库中

特点

每个库的表结构都一样
每个库的数据都不一样
所有库的并集是全量数据

水平分库

水平分表: 以字段为依据，按照一定策略，将一个表的数据拆分到多个表中

特点:

每个表的结构都一样
每个表的数据都不一样
所有表的并集是全量数据

水平分表

MyCat

下载

文档: http://www.mycat.org.cn/

下载页: http://www.mycat.org.cn/mycat1.html

下载链接: http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz

①：点击使用`mycat1.6`	②: 点击下载

服务器环境说明

服务器	安装软件	说明
192.168.2.3	`JDK,Mycat`	`MyCat` 中间件服务器
192.168.2.3	`MYSQL`	分片服务器
192.168.247.129	`MYSQL`	分片服务器
192.168.247.132	`MYSQL`	分片服务器

安装

上传Mycat 至服务器(192.168.2.3)

`mycat` 上传服务器

解压到指定目录

`tar -zxvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /usr/local`

目录说明
- bin: 存放可执行文件,用于启动停止mycat
- conf: 存放mycat 的配置文件
- lib: 存放mycat 的项目依赖包(jar)
- logs: 存放mycat 的日志文件

删除并重新下载mysql 连接驱动jar

#mycat: /usr/local/mycat/lib 
wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.28/mysql-connector-java-8.0.28.jar
# 赋予权限
chmod 777 mysql-connector-java-8.0.28.jar

Mycat 概述

结构

快速入门

需求

由于tb_order 表中数据量很大，磁盘IO 及容量都达到了瓶颈，现在需要对tb_order 表进行数据分片，分为三个数据节点，每一个节点主机位于不同的服务器上,具体结构，如下:

水平分表实现	`schema.xml`

服务器环境

服务器环境

检查所有服务器防火墙是否关闭

防火墙状态

在三台服务器上创建同一个数据库

1 2	# 创建数据库不要执行其他任何操作在 mysql create database db01;

mycat 分片配置

sechema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
		<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" splitTableNames ="true"/>
	</schema>
	<dataNode name="dn1" dataHost="localhost1" database="db01" />
	<dataNode name="dn2" dataHost="localhost2" database="db01" />
	<dataNode name="dn3" dataHost="localhost3" database="db01" />
	
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		
		<writeHost host="hostM1" url="jdbc:mysql://192.168.2.3:3306/?characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=UTC&amp;rewriteBatchedStatements=true" user="root"
				   password="root">
		</writeHost>
		
	</dataHost>
	
	<dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		
		<writeHost host="hostM1" url="jdbc:mysql://192.168.247.129:3306/?characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=UTC&amp;rewriteBatchedStatements=true" user="root"
				   password="root">
		</writeHost>
		
	</dataHost>
	
	<dataHost name="localhost3" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		
		<writeHost host="hostM1" url="jdbc:mysql://192.168.247.132:3306/?characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=UTC&amp;rewriteBatchedStatements=true" user="root"
				   password="root">
		</writeHost>
		
	</dataHost>
	
</mycat:schema>

在bin 目录执行启动mycat start

查看日志是否启动

红色信息内容: 驱动将自动加载注册,启动成功占用端口:`8066`

分片测试

1	mysql -h 192.168.2.3 -P 8066 -uroot -proot

登录`MyCat`

查看表，并创建真实结构

create table tb_order(
   id bigint(20) not null,
   title varchar(100) not null,
   primary key(id)
 )engine=innodb default charset=utf8;

`mycat` 创建表结构	登录其他服务器查看(大小写敏感)

在已登录的mycat 数据库服务器添加数据

`mycat` 插入数据时必须提供列

1
2
3

insert into TB_ORDER(id,title)  values(1,'aa');
insert into TB_ORDER(id,title)  values(2,'bb');
insert into TB_ORDER(id,title)  values(3,'cc');

以下数据库中的数据分布规则是由于`rule="auto-sharding-long"`导致的

mycat 配置

schema.xml

schema.xml 作为MyCat 中最重要的配置文件之一，涵盖了MyCat 的逻辑库、逻辑表、分片规则、分片节点及数据源的配置

主要包含以下三组标签:

schema 标签
datanode 标签
datahost 标签

schema 标签

1
2
3

<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
    <table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" splitTableNames ="true"/>
</schema>

schema 标签用于定义MyCat 实例中的逻辑库，一个MyCat 实例中，可以有多个逻辑库，可以通过schema 标签来划分不同的逻辑库。

MyCat 中的逻辑库的概念，等同于MySQL 中的database 概念，需要操作某个逻辑库下的表时，也需要切换逻辑库。

核心属性:

name: 指定自定义的逻辑库名

checkSQLschema: 在SQL 语句操作时指定了数据库名称，执行时是否自动去除

true: 自动去除
false: 不自动去除

目前配置的是: `checkSQLschema: true`,操作必须先执行:`use database-name`

sqlMaxLimit: 如果未指定limit 进行查询,列表查询模式查询多少条记录

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

核心属性

name: 定义逻辑表表名，在该逻辑库下唯一
dataNode: 定义逻辑表所属的dataNode，该属性需要与dataNode 标签中name 对应；多个dataNode 逗号分割
rule: 分片规则名称，分片规则名字是在rule.xml 中定义的
primaryKey: 逻辑表对应真实表的主键
type: 逻辑表的类型,目前逻辑表只有全局表和普通表，如果未配置，就是普通表；全局表，配置为global

1	<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" splitTableNames ="true"/>

dataNode 标签

1
2
3

<dataNode name="dn1" dataHost="localhost1" database="db01" />
<dataNode name="dn2" dataHost="localhost2" database="db01" />
<dataNode name="dn3" dataHost="localhost3" database="db01" />

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

核心属性
1. name: 定义数据节点名称
2. dataHost: 数据库实例的主机名称，引用自dataHost 标签中的name 属性
3. database: 定义分片所属数据库

dataHost 标签

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

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
          writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
    <heartbeat>select user()</heartbeat>

    <writeHost host="hostM1" 
               url="jdbc:mysql://192.168.2.3:3306/?characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=UTC&amp;rewriteBatchedStatements=true" 
               user="root"
               password="root">
    </writeHost>

</dataHost>

核心属性
1. name: 唯一标识，供上层标签使用
2. maxCon/minCon: 最大连接数/最小连接数
3. balance: 负载均衡策略,取值0,1,2,3
4. writeType: 写操作分发方式(0: 写操作转发到第一个writeHost，第一个挂了，切换到第二个，1:写操作随即分发到配置的writeHost)
5. dbDriver: 数据库驱动，支持native、jdbc

rule.xml

rule.xml 中定义了所有拆分表的规则，在使用过程中可以灵活使用分片算法，或者对同一个分片算法使用不同的参数，他让分片过程可配置化。主要包含两类标签: tableRule、Function

`rule.xml`

server.xml

`user`

MyCat 分片(分库分表)

垂直拆分

拆分过程分析

配置schema.xml

`schema.xml`

<!-- 垂直分库: schema.xml -->
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!-- 逻辑库配置 -->
    <schema name="SHOPPING" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
        <!-- TODO: 落在第一个分片中 -->
        <table name="tb_goods_base" dataNode="dn1" primaryKey="id" />
        <table name="tb_goods_brand" dataNode="dn1" primaryKey="id" />
        <table name="tb_goods_cat" dataNode="dn1" primaryKey="id" />
        <table name="tb_goods_desc" dataNode="dn1" primaryKey="goods_id" />
        <table name="tb_goods_item" dataNode="dn1" primaryKey="id" />

        <!-- 落在第二个分片中 -->
        <table name="tb_order_item" dataNode="dn2" primaryKey="id" />
        <table name="tb_order_master" dataNode="dn2" primaryKey="order_id" />
        <table name="tb_order_pay_log" dataNode="dn2" primaryKey="out_trade_no" />

        <!-- 落在第三个分片中 -->
        <table name="tb_user" dataNode="dn3" primaryKey="id" />
        <table name="tb_user_address" dataNode="dn3" primaryKey="id" />
        <table name="tb_user_provinces" dataNode="dn3" primaryKey="id" />
        <table name="tb_user_city" dataNode="dn3" primaryKey="id" />
        <table name="tb_areas_region" dataNode="dn3" primaryKey="id" />

    </schema>

    <dataNode name="dn1" dataHost="localhost1" database="shopping" />
    <dataNode name="dn2" dataHost="localhost2" database="shopping" />
    <dataNode name="dn3" dataHost="localhost3" database="shopping" />

    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>

        <writeHost host="hostM1" url="jdbc:mysql://192.168.2.3:3306/?characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=UTC&amp;rewriteBatchedStatements=true" user="root"
                   password="root">
        </writeHost>

    </dataHost>

    <dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>

        <writeHost host="hostM1" url="jdbc:mysql://192.168.247.129:3306/?characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=UTC&amp;rewriteBatchedStatements=true" user="root"
                   password="root">
        </writeHost>

    </dataHost>

    <dataHost name="localhost3" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>

        <writeHost host="hostM1" url="jdbc:mysql://192.168.247.132:3306/?characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=UTC&amp;rewriteBatchedStatements=true" user="root"
                   password="root">
        </writeHost>

    </dataHost>

</mycat:schema>

<!-- server.xml -->

<user name="root" defaultAccount="true">
    <property name="password">root</property>
    <property name="schemas">SHOPPING</property>
</user>
<!-- TODO: user 是自行创立的用户还是全局用户 -->
<user name="test">
    <property name="password">test</property>
    <property name="schemas">SHOPPING</property>
    <property name="readOnly">true</property>
</user>

重启mycat 服务

1
2
3

cd /usr/local/mycat
bin/mycat restart
tail -f logs/wrapper.log

1	mysql -h 192.168.2.3 -P 8066 -u root -proot

在mycat 创建逻辑表对应的表结构
在mycat 插入逻辑表对应的数据

水平分表

在业务系统中，有一张表(日志表),业务系统每天都会产生大量的日志数据，单台服务器的数据存储及处理能力是有限的，可以对数据库表进行拆分。

水平拆分分析