一、mysql基础篇

1 DDL-数据库操作

• 查询所有数据库

  SHOW DATABASES
  

• 查询当前数据库

  SELECT DATABASE()
  

• 创建

  CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]
  # IF NOT EXISTS 如果数据库不存在才创建
  

• 删除

  DROP DATABASE [IF EXISTS] 数据名
  # IF EXISTS  如果数据库存在才删除
  

• 使用

  USE 数据库名
  

2 DDL-表操作

2.1 查询表

• 查询当前数据库所有表

  SHOW TABLES
  

• 查询表结构

  DESC 表名
  

• 查询指定的建表语句

  SHOW CREATE TABLE 表名
  

2.2 创建表

CREATE TABLE 表名 (
  字段1 字段1类型 [COMMENT 字段1注释],
  字段2 字段2类型 [COMMENT 字段2注释]
) [COMMENT 表注释]

2.3 数据类型

2.3.1 数值类型

2.3.2 字符串类型

2.3.2 日期时间类型

2.4修改表

• 添加字段

  ALTER TABLE 表名 ADD 字段名 类型（长度）[COMENT 注释] [约束]
  

• 修改字段

  ALTER TABLE 表名 CHANGE 就字段名 新字段名 类型（长度）[COMMENT 注释] [约束]
  

• 删除字段

  ALTER TABLE 表名 DROP 字段名
  

• 修改表名

  ALTER TABLE 表名 RENAME TO 表名
  

• 删除表

  DROP TABLE [IF EXISTS] 表名
  

• 删除指定表，并重新创建该表

  TRUNCATE TABLE 表名
  

3 DML-插入

3.1 添加数据

3.1.1.给指定字段添加数据

INSERT INTO 表名 (字段名1, 字段名2) VALUES (值1, 值2)

3.1.2.给全部字段添加数据

INSERT INTO 表名 VALUES (值1, 值2, ...)

3.1.3.批量添加数据

INSERT INTO 表名 (字段名1, 字段名2) VALUES (值1, 值2), (值1, 值2)
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...)

3.2 修改数据

UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [WHERE 条件]

3.3 删除数据

DELETE FROM 表名 [WHERE 条件]

4 DQL 查询

4.1 DQL 语法

SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数

4.1 基本查询

4.1.1 查询多个字段

SELECT 字段1, 字段2, 字段3... FROM 表名

SELECT * FROM 表名

4.1.2 设置别名

SELECT 字段1 [AS 别名1] 字段2[AS 别名2]... FROM 表名

4.1.3 去除重复记录

SELECT DISTINCT 字段列表 FROM 表名 

4.1.4 条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表

4.1.5 聚合函数

4.1.6 分组查询

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后结果过滤条件]

where与having区别

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
• 判断条件不同：where不能对聚合函数进行判断，而having可以。

# 查询平均年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
SELECT workaddress, count(*) address_count from emp where age < 45 group by workaddress having address_count >= 3

4.1.7 排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2

排序方式：

• ASC：升序
• DESC：降序

4.1.8 分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数

起始索引从0开始，起始索引 = （查询页码 - 1） * 每页显示记录数

分页查询是数据库的方言，不同的数据库有不同的实现，mysql中是LIMIT

如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10

5 DCL 管理用户

5.1 查询用户

USE mysql;
SELECT * FROM user

5.2 创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'

5.3 修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码'

5.4 删除用户

DROP USER '用户名'@'主机名'

5.2 权限控制

5.2.1 查询权限

SHOW GRANTS FOR '用户名'@'主机名'

5.2.2 授予权限

GRANT 权限列表 ON 数据库.表名 TO '用户名'@'主机名'

5.2.3 撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'

6 函数

6.1 字符串函数

6.2 数值函数

6.3 日期函数

6.4 流程函数

流程控制函数也是很常用的一类函数，可以在sql语句中实现田间筛选，从而提高语句的效率

7 约束

常见约束：

7.1 添加外键约束

# 建表时添加外键
CREATE TABLE 表名 (字段名 数据类型，... [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名))

# 已经存在的表添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)

# 删除外键
ALTER TABLE 表名 DROP FOREIGN 外键名称

7.2 外键删除更新行为

# 例：更新时更新外键子表记录，删除时也删除外键子表的记录
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名（主表字段名）ON UPDATE CASCADE ON DELETE CASCADE

# 例：更新删除时将子表中关联字段值设置为null
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名（主表字段名）ON UPDATE SET NULL ON DELETE SET NULL

8 多表查询

8.1 内连接（结果为两表的交集）

8.1.1隐式内连接

SELECT 字段列表 FROM 表1，表2 WHERE 条件 ...

8.1.2显式内连接

SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 连接条件 ...

8.2 外连接

8.2.1 左外连接

SELECT 字段列表 FROM 表1 LEFT OUTER JOIN 表2 ON 条件
相当于表1（左表）的所有数据包含表1和表2交集部分的数据

8.2.2 右外连接

SELECT 字段列表 FROM 表1 RIGHT OUTER JOIN 表2 ON 条件
相当于查询表2（右表）的所有数据 包含表1和表2交集部分的数据

8.3 自连接

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...
自连接查询，可以时内连接查询也可以时外连接查询

8.4 联合查询

对于union查询，就是吧多次查询的结果合并起来，形成一个新的查询结果集

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重

SELECT 字段列表 FROM 表A UNION [ALL] SELECT 字段列表 FROM 表B

8.5 子查询

sql语句中嵌套SELECT语句，称为嵌套查询，又称子查询

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2)

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个

根据子查询结果不同，分为

• 标量子查询（子查询结果为单个值）
• 列子查询（子查询结果为一列）
• 行子查询（子查询结果为一行）
• 表子查询（子查询结果为多行多列）

9 事务

事务时一组操作集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

9.1 事务操作

查询设置事务提交方式 （0:手动提交，1:自动提交）

SELECT @@autocommit
SET @@autocommit = 0

提交事务

COMMIT

回滚事务

ROLLBACK

开启事务

START TRANSACTION 或 BEGIN

开启事务

COMMIT

9.2 事务四大特性

• 原子性：事务时不可分割的最小操作单元，要么全部成功，要么全部失败
• 一致性：事务完成时，必须使所有的数据都保持一致状态
• 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
• 持久性：事务一旦提交或回滚，它对数据库中的数据的改表就是永久的

9.3 事务并发问题

9.4 事务隔离级别

# 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION

# 设置事务隔离级别
# SESSION 会话级别，仅对当前连接的客户端有效
# GLOBAL 所有的会话都有效
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

二、进阶篇

1 索引结构

2 索引分类

在InnoDB引擎冲，根据索引发的存储形式，又可以分为以下两种：

3 索引语法

3.1 创建索引

CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name,...)

3.2 查看索引

SHOW INDEX FROM table_name      

3.3 删除索引

DROP INDEX index_name ON table_name

4 sql性能分析

4.1 查看执行频次

查看mysql连接成功后，通过show [session ｜ global] status 命令可以提供服务器状态信息，通过如下指令，可以查看当前数据库的 INSERT UPDATE DELETE SELECT的访问次数

SHOW GLOBAL STATUS LIKE 'Com______'

4.2 慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time, 单位：秒，默认10秒）的多余sql语句日志

mysql的慢查询日志默认没有开启，需要在mysql的配置文件（etc/my.cnf）中配置

# 开启慢查询日志开关
show_query_log = 1
# 设置慢查询日志的时间为2秒，sql与语句的之间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time = 2

4.3 profile详情

show profiles 能够在做sql优化时帮助了解时间都消耗到哪里去了，通过have_profiling参数，能够看到当前mysql是否支持profile操作：

SHOW @@have_profiling

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

SET profiling = 1

profile详情

执行一系列的业务情况sql的操作，然后通过如下指令查看执行的耗时：

# 查看每一条sql的耗时基本情况
SHOW profiles

# 查看指定query_id的sql语句各个阶段的耗时情况
SHOW profile for query query_id

#查看制定query_id的sql语句cpu的使用情况
show profile cpu for query query_id

4.4 explain执行计划

EXPLAIN或者DESC命令获取mysql如何执行SELECT 语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序

# 直接在selete语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件

EXPLAIN 执行计划各字段含义：
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 的值越大越好。

5 索引的使用

5.1 最左前缀法则

如果索引了多列（联合索引），要遵循最左前缀法则，最左前缀法则 指的是查询从索引的最左列开始，并且不跳过索引的列，如果跳跃某一列，索引部分失效（后面的字段索引失效）

5.2 索引失效情况

5.2.1 范围查询

联合索引中，出现范围查询（>,<）,范围查询右侧的列索引失效（尽量使用 <= 与 >= 规避失效）

5.2.2 索引列运算

不要在索引列上进行运算操作，索引失效，如：

SELECT * FROM tb_user WHERE substring（phone，10，2）= '15'

5.2.3 字符串不加引号

字符串类型字段使用时名，不加引好，索引将失效

5.2.4 模糊查询

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

5.2.5 OR 连接的条件

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

5.2.6 数据分布影响

如果mysql评估使用索引比全表更慢，则不使用索引

5.3 SQL提示

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

use index：建议索引

explain select * from tab_user use index(idx_user_pro) whrere profession = '软件工程'

ignore index：忽略索引

explain select * from tab_user ignore index(idx_user_pro) whrere profession = '软件工程'

force index：强制使用索引

explain select * from tab_user force index(idx_user_pro) whrere profession = '软件工程'

5.4 覆盖索引&回表查询

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

注：

using index condition：查找使用列索引，但是需要回表查询数据
using where；using index；查找使用列索引，到那时需要的数据都在索引列中能找到，索引不需要回表查询数据

5.5 前缀索引

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

create index idx_xxxx on table_name(column(n)) # 前n个字符

5.6 单列索引与联合索引

单列索引：即单个索引只包含单个列

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

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

5.7 索引设计原则

1. 针对于数据量比较大，且查询比较频繁的表建立索引
2. 针对常作为条件（where）、排序（orbey by）、分组（group by） 操作的字段建立索引
3. 尽量选择区分高的列作为索引，尽量建立唯一索引，区分度越越高，建立前缀索引
4. 如果时字符窜类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回查询，提高效率
6. 要控制索引的数量，索引并不是越多越好，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
7. 如果索引不能存储null值，请在创建表的时候使用not null约束它，当优化器知道每列是否包含null值时，它可以更好的确定那个索引最有效的用于查询

6 SQL优化

6.1 插入数据

• 批量插入

  insert into tb_test values (1, 'tom'),(2, 'cat'),(3, 'jerry')
  

• 手动提交事务

  start transacttion；
  insert into tb_test values (1, 'tom'),(2, 'ee'),(2, 'jerr')
  insert into tb_test values (1, 'tom'),(2, 'ee'),(2, 'jerr')
  insert into tb_test values (1, 'tom'),(2, 'ee'),(2, 'jerr')
  commit;
  

• 主键顺序插入

  主键乱序插入：43 2 4 1 21 54 31
  主键顺序插入：1 2 3 4 5 31 43 54
  

• 大批量数据插入

  如果一次性需要插入大批量数据，时用insert 语句插入性能较低，此时可以使用mysql数据库提供的load指令进行插入

  # 客户端连接服务端时，脚上参数 --local-infile
  mysql --local-infile -u root -p
  # 设置全局参数local——infile为1，开启从本地加载文件导入数据的开关
  set global local——infile=1
  #执行load指令将准备好的数据，加载到表结构中
  load data local infile '/root.sql.log' into table `tb_user` fields terminated by ',' lines terminated by '\n'
  

6.2 order by排序优化

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

# 根据age，phone进行降序一个升序，一个降序
explain select id,age,phone from tb_user order by age asc, phone desc
# 创建索引
create index idx_user_age_phone_ad on tb_user(age asc, phone desc)
#根据age，phone进行降序一个升序，一个降序
explain select id,age,phone from tb_user order by age asc, phone desc

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
• 尽量使用覆盖索引
• 多字段排序，一个升序一个降序，此时需要注意联合索引在创建 时的规则（ASC/DESC）
• 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)

6.3 group by 分组优化

# 删除掉目前的联合索引 index_user_pro_age-sta
drop index idx_user_pro_age_sta on tb_user
# 执行分组操作，根据profession字段分组
explain select profession , count(*) from tb_user group by profession
# 创建索引
create index idx_user_pro_age_sta on tb_user(profession, age, status)
# 执行分组操作，根据profession字段分组
explain select profession, count(*) from tb_user group by profession
# 执行分组操作，根据profession字段分组
explain select profession, count(*) from tb_user group by profession, age

分组操作时，可以通过索引开提高效率

分组操作时，索引的使用也是满足最左前缀法则的

6.4 limit 分页优化

通过覆盖索引 + 子查询的方式实现优化

# 1
select * from tb_sku where id in (select id from tb_sku order by id limit 90000000, 10)
# 2
select s.* from tb_sku s, (select id from tb_sku order by id limit 90000000,10) a where s.id = a.id

6.5 count优化

Misaim 引擎把一个表的总行数存在了磁盘上，因此执行count（*）的时候直接返回这个数，效率高

innoDB 引擎就麻烦了，它执行count（*）的时候，需要把数据一行一行的从引擎里面读出来，然后累计计数

优化思路：自己计数，如redis中

用法：count（*）、 count（主键）、count（字段）、count（1）

• count （主键）
  InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)。
• count（字段）
  没有not null 约束：InnoDB 引1擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null， 计数累加。
  有not null约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
• count (1)
  InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1〞进去，直接按行进行累加。
• count ()*
  InnoDB引l擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count(字段)＜count(主键 id) <count(1)~ countig( * ))，所以尽量使用 count()。*

6.6 update优化

update student set no = '3232432' where id = 1

update student set np = '134324' where name = 'weiyixia'

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

7 视图

7.1 基本语法

• 创建

  CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
  

• 查询

  查看创建视图语句：SHOW CREATE VIEW 视图名称；
  查看视图数据：SELECT * FROM 视图名称...；
  

• 修改视图

  方式一：CREATE IOR REPLACE] VEW 视图名称（列名列表】 AS SELECT语句 LWTH I CASCADED I LOCALJ CHECK OPTION]
  方式二：ALTER VEW 视图名称（列名列表】 AS SELECT语句 IWTH I CASCADED I LOCALJ CHECK OPTION ]
  

• 删除

  DROP VEW NF EXISTS1 视图名称 「视图名称了…
  

8 存储

8.1 基本语法

• 创建

  CREATE PROCEDURE 存储过程名称（[参数列表]）
  BEGIN 
  --SQL语句
  END;
  

• 调用

  CALL 名称([参数])
  

• 查看

  SELECT * FROM INFORMATION SCHEMA.ROUTINES WHERE ROUTINE_ SCHEMA = 'xxx'；
  --查询指定数据库的存储过程及状态信息
  SHOW CREATE PROCEDURE 存储过程名称；—一查询某个存储过程的定义
  

• 删除

  DROP PROCEDURE CIF EXISTS〕 存储过程名称；
  

9 触发器

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特
性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句
级触发。

9.1 基本语法

• 语法

  CREATE TRIGGER trigger name
  BEFORE/AFTER INSERT/UPDATE/DELETE
  ONtbl name FOR EACH ROW --行级触发器
  BEGIN
  trigger stmt;
  END:
  

• 查看

  SHOW TRIGGERS
  

• 删除

  DROP TRIGGER IsChema name.Jtrioger name; --如果没有指定 schema name，默认为当前数据库。
  

10 锁

• 锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、1/0）的争用以外，数据也是

一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访

问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

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

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

  表级锁：每次操作锁住整张表。

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

三、运维篇

1 日志

1.1 错误日志

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

show variables like '%log_error%'

1.2 二进制日志

二进制日志 (BINLOG）记录了所有的DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但不包括数据查询(SELECT、SHOW)
语句。
作用：①.灾难时的数据恢复；②.MySQL的主从复制。在MySQL8版本中，默认二进制日志是开启着的，涉及到的参数如下：

show variables like '%log_bin%'

日志格式

MySQL服务器中提供了多种格式来记录二进制日志，具体格式及特点如下：

show variables like "%binlog format%';

1.3 查询日志

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

show variables like "%general%';

修改MySQL的配置文件/etc/my.cnt 文件，添加如下内容：

＃该选项用来开启查询日志，可选值：0或者1；0代表关闭，1代表开启
generallog=1
#设置日志的文件名，如果没有指定，默认的文件名为 host name.log
general log file=mysal query.log

1.4 慢查询日志

慢查询日志记录了所有执行时间超过参数 long query time 设置值并且扫描记录数不小于 min examined row limit
的所有的SQL语句的日志，默认末开启。long_query_time 默认为 10秒，最小为 0，精度可以到微秒。

#慢查询日志
slow _query_log=1
＃执行时间参数
long query time=2

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用log_ slow admin_ statements和
更改此行为 log_queries_ not_using_indexes， 如下所述。

#记录执行较慢的管理语句
loo slow admin statements =1
＃记录执行较慢的未使用索引的语句
loa gueries not usina indexes = 1

2 主从复制

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

MvSQL 复制的有点主要包含以下三个方面：
1.主库出现问题，可以快速切换到从库提供服务。
2.实现读写分离，降低主库的访问压力。
3.可以在从库中执行备份，以避免备份期问影响主库服务

2.1 主库配置

1. 修改配置文件 /etc/my.cnf

   #mysal服务10，保证整个集群环境中唯一，取值范围：7-232-1，默认为1
   server-id=1
   ＃是否只读,1代表只读，0代表读写
   read-only=0
   #忽略的数据，指不需要同步的数据库
   #binlog-ianore-db=mysal
   #指定同步的数据库
   #binlog-do-db=dbo1
   

2. 重启mysql

3. 登录mysql，创建远程连接的账号，并授予主从复制权限

   ＃创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务
   CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
   ＃为'itcast'@'%' 用户分配主从复制权限
   GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%'
   

4. 通过指令，查看二进制日志坐标

   show master status;
   

   字段含义说明：
   file：从哪个日志文件开始推送日志文件
   position ：从哪个位置开始推送日志
   sbinlog_ ignore db：指定不需要同步的数据库

2.2 从库配置

1. 修改配置文件 /etc/my.cnf

   #mysal服务10，保证整个集群环境中唯一，取值范围：1-232-1，和主库不一样即可
   server-id=2
   ＃是否只读,1代表只读，0代表读写
   read-only=1
   

2. 重新启动MvSQL服务

3. 登录mysql，设置主库配置

   CHANGE REPLICATION SOURCE TO SOURCE_HOST='xxx.xxx', SOURCE_USER='xxx', SOURCE_PASSWORD='xxx', SOURCE_LOG_FILE='xXX', SOURCE_LOG_POS=Xxx;
   

   上述是8.0.23中的语法。如果mysql是 8.0.23之前的版本，执行如下SQL：

   CHANGE MASTER TO MASTER_HOST='xxx.xx.xxx.xxx', MASTER_USER='xXX', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='xxx', MASTER_LOG_POS=xxx;
   

   参数名	含义	8.0.23之前
   SOURCE_HOST	主库IP地址	MASTER_HOST
   SOURCE_USER	连接主库的用户名	MASTER_USER
   SOURCE_PASSWORD	连接主库的密码	MASTER_PASSWORD
   SOURCE_LOG_FILE	binlog日志文件名	MASTER_LOG_FILE
   SOURCE_LOG_POS	binlog日志文件位蛋	MASTER_LOG_POS

4. 开启同步操作

   start replica;  #8.0.22之后
   start slave; # 8.0.22之前
   

5. 查看主从同步状态

   show replica status; # 8.0.22 之后
   show slave status; # 8.0.22 之前
   # replica_IO_Runing 与 replica_SQL_Runing 为yes就是开启成功
   

   注：设置读取主库的端口

   stop replica;
   change master to master_port=3307;
   start replica;
   

3 分库分表

拆分策略：

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

    1.每个库的表结构都不一样

    2.每个库数据也不一样

    3.所有库的并集是全量数据

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

    1.每个表的结构都是不一样的

    2.每个表的数据也不一样，一般通过一列（主键/外键）关联

    3.所有表的并集是全量数据

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

    1.每个库的表结构都一样

    2.每个库的数据都不一样

    3.所有库的并集是全量数据

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

    1.每个库的表结构一样

    2.每个表的数据都不一样

    3.所有表的并集是全量数据

3.1 mycat安装 （1.6版本）

• 解压

  tar -xzvf Mycat-server-1.3.0.3-release-20150527095523-linux.tar.gz -C /usr/local/
  

• 设置环境变量

  #设置环境变量
  export MYCAT_HOME=/home/mycat
  PATH=$PATH:$MYCAT_HOME/bin
  
  source .bash_profile
  
  #测试是否生效
  echo $MYCAT_HOME
  

• 安装jdk（java）

  tar -xzvf jdk-17_linux-x64_bin.tar.gz
  
  mv jdk-17.0.2 /home/jdk
  
  # 配置环境变量
  vim /etc/profile
  
  JAVA_HOME=/home/jdk/jdk-17.0.2
  PATH=$JAVA_HOME/bin:$PATH
  
  source /etc/profile
  

• 修改wrapper.xml配置文件

bin：存放可执行文件，用于启动停止mycat

conf：存放mycat的配置文件

lib：存放mycat的项目依赖包

logs：存放mycat的日志文件

启动服务

# 切换到mycat安装目录，执行如下指令，启动mycat
# 启动
bin/mycat start
# 停止
bin/mycat stop
# mycat启动会占用8066端口
#查看日志
tail logs/wrapper.log

3.2 mycat配置

3.2.1 schema.xml配置

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

主要包含以下三组标签：

• schema标签

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

  schema标签用于定义mycat实力中的逻辑库，一个mycat实例中，可以有多个逻辑库，可以通过schema标签划分不同的逻辑库。mycat中的逻辑库的概念，等同于mysql中的database概念，需要操作每个逻辑库下的表时，也需要切换逻辑库（use xxx）

  # 核心属性
  name: 指定义的逻辑库库名
  checkSQLschema: 在sql语句操作时指定了数据库名称，执行时是否自动去除；true: 自动去除；false：不自动去除
  sqlMaxLimit: 如果未指定limit进行查询，列表查询模式查看多少条记录
  

• table标签

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

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

• datanode标签

  <dataNode name="dn11" dataHot="dhost1" database="db01"></dataNode>
  <dataNode name="dn12" dataHot="dhost2" database="db01"></dataNode>
  <dataNode name="dn13" dataHot="dhost3" database="db01"></dataNode>
  name: 定义数据节点名称
  dataHost: 数据库实例主机名称，引用自dataHost标签中name属性
  database: 定义分片所属数据库 (物理数据库)
  

• datahost标签

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

  <dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
    writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"        slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="jdbc:mysql://127.0.0.1:3308" user="root"   password="123456" />
  </dataHost>
  
  name：唯一标识，供上层标签使用
  maxCon/minCon：最大连接数/最小连接数
  balance：负载均衡策略，取值 0,1,2,3
  writeType：写操作分发方式（0：写操作转发到第一个writeHost，第一个挂了，切换到第二个;1：写操作随机分发到配置的WriteHost）
  dbDriver：数据库驱动，支持 native、jdbc
  

3.2.2 rule.xml 配置

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

<tableRule name="auto-sharding-long">
    <rule>
        <columns>id</columns>
        <algorithm>rang-long</algorithm>
    </rule>
</tableRule>

<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
    <property name="mapFile">autopartition-long.txt</property>
</function>

3.2.3 server.xml

server.xml配置文件包含了Mycat的系统配置信息，主要有两个重要的标签：system、user。

system标签

<system>
  <property name="nonePasswordLogin">0</property>
    <property name="useHandshakeV10">1</property>
    <property name="useSqlStat">0</property>
</system>

user标签

<user name="root" defaultAccount="true"> #name: 用户名
        <property name="password">123456</property> 密码
        <property name="schemas">DB01</property> 该用户可以访问逻辑库，多个逻辑库之间逗号分隔
        <!-- 表级 DML 权限设置 -->
        <!--        
        <privileges check="false"> 是否开启DML权限检查，默认为false
            <schema name="TESTDB" dml="0110" > schema配置指定逻辑库权限，默认为false
                <table name="tb01" dml="0000"></table>  table配置指定逻辑表的权限，就近原则
                <table name="tb02" dml="1111"></table> dml：对应iusd（增删改查）的权限                  </schema>
        </privileges>       
         -->
</user>

<user name="user"> 
    <property name="password">123456</property>
    <property name="schemas">DB01</property>
    <property name="readOnly">true</property> #是都制度，默认为false
</user>

3.3 垂直分库

3.3.1 垂直拆分

将单个数据库中的不同表分别存放在不同的数据库中（不对表中数据结构与数据改变，只是将表分散到不同数据库中）。

# schema.xml
<schema name="SHOPPING" checkSQLschema="true" sqlMaxLimit="100" rule="只有涉及分表时才用rule属性，分库不用">
  <table name="tb_goods_base" dataNode="dn1" primaryKey="主键" /> 
  <table name="tb_goods_branch" 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_goods_item" dataNode="dn2" primaryKey="id" />
  <table name="tb_goods_master" dataNode="dn2" primaryKey="order_id" />
  <table name="tb_goods_blog" dataNode="dn2" primaryKey="out_trade_id" />
  
  <table name="tb_goods_address" dataNode="dn3" primaryKey="id" />
  <table name="tb_goods_user" dataNode="dn3" primaryKey="id" />
  <table name="tb_areas_provinces" dataNode="dn3" primaryKey="id" />
  <table name="tb_areas_city" dataNode="dn3" primaryKey="id" />
  <table name="tb_areas_region" dataNode="dn3" primaryKey="id" />
</schema>
    
<dataNode name="dn1" dataHost="dhost1" database="数据库名" />
<dataNode name="dn2" dataHost="dhost2" database="数据库名" />
<dataNode name="dn3" dataHost="dhost3" database="数据库名" />

# server.xml
<user name="root" defaultAccount="true">
        <property name="password">123456</property>
        <property name="schemas">SHOPPING</property>
        
        <!-- 表级 DML 权限设置 -->
        <!--        
        <privileges check="false">
            <schema name="TESTDB" dml="0110" >
                <table name="tb01" dml="0000"></table>
                <table name="tb02" dml="1111"></table>
            </schema>
        </privileges>       
         -->
</user>

3.3.1 全局表配置

在多个业务模块汇中都可能会遇到，可以将其设置为全局表，利于业务操作（每个数据库中都会有存在这几张表，并且里面存储的数据是一样的）。将type设置为global表示就是设置为全局表

<table name="tb_areas_provinces" dataNode="dn1,dn2,dn3" primaryKey="id" type="global" />
<table name="tb_areas_city" dataNode="dn1,dn2,dn3" primaryKey="id" type="global" />
<table name="tb_areas_region" dataNode="dn1,dn2,dn3" primaryKey="id" type="global" />

3.4 水平分表

对数据库中某张表拆分成多个表，数据插入时分散的拆入表中。

3.4.1 水平拆分

# schema.xml
<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
    <table name="tb_log" dataNode="dn4,dn5,dn6" rule="mod-long" />
</schema>
    
<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

# server.xml
<user name="root" defaultAccount="true">
        <property name="password">123456</property>
        <property name="schemas">SHOPPING,ITCAST</property>
        
        <!-- 表级 DML 权限设置 -->
        <!--        
        <privileges check="false">
            <schema name="TESTDB" dml="0110" >
                <table name="tb01" dml="0000"></table>
                <table name="tb02" dml="1111"></table>
            </schema>
        </privileges>       
         -->
</user>

3.5 分片规则（水平分表）

3.5.1 范围分片

根据指定的字段及配置的范围与数据节点的对应情况，来决定还数据属于哪一个分片。

# schema.xml
<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
    <table name="tb_log" dataNode="dn4,dn5,dn6" rule="auto-sharding-long" />
</schema>
    
<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

# rule.xml
<tableRule name="auto-sharding-long">
        <rule>
            <columns>id</columns>
            <algorithm>rang-long</algorithm>
        </rule>
</tableRule>
<function name="rang-long"
        class="io.mycat.route.function.AutoPartitionByLong">
        <property name="mapFile">autopartition-long.txt</property>
</function>

# autopartition-long.txt
0-500M=0 // 存储到节点0
500M-1000M=1  // 存储到节点1
1000M-1500M=2  // 存储到节点2

3.5.2 取模分片

根据指定的字段值与节点数量进行求模运算，根据运算结果，来决定改数据属于哪一个分片。

# schema.xml
<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
    <table name="tb_log" dataNode="dn4,dn5,dn6" rule="mod-long" />
</schema>
    
<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

# rule.xml
<tableRule name="mod-long">
        <rule>
            <columns>id</columns>
            <algorithm>mod-long</algorithm>
        </rule>
</tableRule>
<function name="mod-long"
        class="io.mycat.route.function.PartitionByMod">
        <property name="count">3</property> // 这里写节点数量
</function>

3.5.3 一致性hash分片

所谓的一致性哈希，相同的哈希因子计算总是划分到相同的分区表中，不会因为分区节点的增加而改变原来数据的分区位置。

# schema.xml
<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
    <table name="tb_log" dataNode="dn4,dn5,dn6" rule="sharding-by-murmur" />
</schema>
    
<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

# rule.xml
<tableRule name="sharding-by-murmur">
        <rule>
            <columns>id</columns>
            <algorithm>murmur</algorithm>
        </rule>
</tableRule>
<function name="murmur"
        class="io.mycat.route.function.PartitionByMurmurHash">
        <property name="seed">0</property><!-- 默认是0 -->
        <property name="count">3</property>基本只需要修改这个参数<!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
        <property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 -->
        <!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替 -->
    </function>

3.5.4 枚举分片

通过配置文件中配置可能的枚举值，指定数据分布到不同数据节点上，本规则适用于按照省份、性别、状态拆分数据业务。

# schema.xml
<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
    <table name="tb_log" dataNode="dn4,dn5,dn6" rule="sharding-by-intfile" />
</schema>
    
<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

# rule.xml
<tableRule name="sharding-by-intfile">
        <rule>
            <columns>sharding_id</columns>
            <algorithm>hash-int</algorithm>
        </rule>
</tableRule>
<function name="hash-int"
        class="io.mycat.route.function.PartitionByFileMap">
        <property name="defaultNode">2</property> 默认节点 （超出设置的只默认往设置的节点写入）
        <property name="mapFile">partition-hash-int.txt</property>
</function>

# partition-hash-int.txt
1=0
2=1
3=2 

3.5.5 应用指定算法

运行阶段由应用自主决定陆游到那个分片，直接根据字符串（必须是数字）计算分片号。

# schema.xml
<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
    <table name="tb_log" dataNode="dn4,dn5,dn6" rule="sharding-by-substring" />
</schema>
    
<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

# rule.xml
<tableRule name="sharding-by-substring">
        <rule>
            <columns>id</columns> // 使用id字段分片
            <algorithm>sharding-by-substring</algorithm>
        </rule>
</tableRule>
<function name="sharding-by-substring"
        class="io.mycat.route.function.PartitionDirectBySubString">
        <property name="startIndex">0</property> //开始索引
        <property name="size">2</property> // 截取长度
        <property name="partitionCount">3</property> // 分片数量
        <property name="defaultPartition">2</property> // 默认分片
</function>

3.5.6 固定hash算法

该算法类似于十进制的求模运算，但是为二进制的操作，例如，取id的二进制低10位与1111111111进行位&运算。

特点：

• 如果是求模，连续的值，分别分配到各个不同的分片；但是此算法会将连续的值可能分配到相同的分片，降低事务处理的难度。
• 可以均匀分配，也可以非均匀分配
• 分片字段必须为数字类型

# schema.xml
<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
    <table name="tb_log" dataNode="dn4,dn5,dn6" rule="sharding-by-long-hash" />
</schema>
    
<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

# rule.xml
<tableRule name="sharding-by-long-hash">
        <rule>
            <columns>id</columns> // 使用id字段分片
            <algorithm>sharding-by-long-hash</algorithm>
        </rule>
</tableRule>
<function name="sharding-by-long-hash"
        class="io.mycat.route.function.PartitionByLong">
        // 约束：分片长度，默认最大1024。Count、length数组长度必须一致
        <property name="partitionCount">2,1</property>
        <property name="partitionLength">256,512</property>
</function>

3.5.7 字符串hash解析

截取字符串中的制定位置的字符串，进行hash算法，算出分片

# schema.xml
<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
    <table name="tb_log" dataNode="dn4,dn5,dn6" rule="sharding-by-stringhash" />
</schema>
    
<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />

# rule.xml
<tableRule name="sharding-by-stringhash">
        <rule>
            <columns>name</columns> // 使用id字段分片
            <algorithm>sharding-by-stringhash</algorithm>
        </rule>
</tableRule>
<function name="sharding-by-stringhash"
        class="io.mycat.route.function.PartitionByString">
        <property name="partitionLength">512</property>
        <property name="partitionCount">2</property>
        <property name="hashSlice">0:2</property> // hash运算位，格式 start：end
                                                        // 0 在end中出现代表str.length()
                                                        //  -1 代表str.length() - 1
                                                        // 大于0代表数字本身
</function> 

3.5.8 按天分片

# schema.xml
<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
    <table name="tb_datepart" dataNode="dn4,dn5,dn6" rule="sharding-by-date" />
</schema>
    
<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

# rule.xml
<tableRule name="sharding-by-date">
        <rule>
            <columns>create_time</columns> // 使用create_time字段分片
            <algorithm>sharding-by-date</algorithm>
        </rule>
</tableRule>
<function name="sharding-by-date"
        class="io.mycat.route.function.PartitionByDate">
        // 约束：分片长度，默认最大1024。Count、length数组长度必须一致
        <property name="dateFormat">yyyy-MM-dd</property>
        <property name="sBeginDate">2022-01-01</property>
        <property name="sEndDate">2022-01-30</property>
        <property name="sPartionDay">10</property> // 从开始时间开始，每10天一个分片，到达结束时间之后，会重复开始分片插入
</function>

注：配置表的dataNode的分片，必须和分片规则一致，例如2022-01-01 到 2022-12-31，每10天一个分片，一共需要37个分片。如果没有37个分片将会报错。

3.5.9 按自然月分片

使用场景位按照月份来分片，每个自然月为一个分片

# schema.xml
<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
    <table name="tb_datepart" dataNode="dn4,dn5,dn6" rule="sharding-by-month" />
</schema>
    
<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

# rule.xml
<tableRule name="sharding-by-month">
        <rule>
            <columns>create_time</columns> // 使用create_time字段分片
            <algorithm>partbymonth</algorithm>
        </rule>
</tableRule>
<function name="partbymonth"
        class="io.mycat.route.function.PartitionByMonth">
        <property name="dateFormat">yyyy-MM-dd</property>
        <property name="sBeginDate">2022-01-01</property>
        <property name="sEndDate">2022-03-31</property> // 从开始时间开始，一个月为一个分片，到达结束时间之后，会重复开始分片插入
</function>

注：配置表的dataNode的分片，必须和分片规则数量一致，例如 2022-01-01 到 2022-12-31，一共需要12个分片

3.6 mycat管理及监控

3.6.1 管理工具

mycat默认开通2个端口，可以在server.xml中进行修改

• 8066 数据访问端口，即进行DML和DDL操作
• 9066 数据库端口，即mycat服务器管理控制功能，用于管理mycat的整个集群状态

mysql -h 192.168.0.1 -p 9066 -uroot -p123456

3.6.2 监控 mycat-eye

Mycat-web(Mycat-eye)是对mycat-server提供监控服务，功能不局限于对mycat-server使用。他通过JDBC连接对Mycat、Mysal监控，监控远程服务器(目前仅限于linux系统)的cpu、内存、网络、磁盘。
Mycat-eye运行过程中需要依赖zookeeper，因此需要先安装zookeeper

4 读写分离

读写分离,简单地说是把对数据库的读和写操作分开,以对应不同的数据库服务器。主数据库提供写操作，从数据库提供读操作，这样能有效地减轻单台数据库的压力。
通过MyCat即可轻易实现上述功能，不仅可以支持MySQL，吐可以支持Oracle和SQL Server。

4.1 一主一从读写分离

• mycat控制后台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控制

# schema.xml
<schema name="ITCAST_RW" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7">
    < !--<table name="sku" dataNode="dn7" primaryKey="id" /> -->
</schema>

<dataNode name="dn7" dataHost="dhost7" database="itcast" />

<dataHost name="dhost7" maxCon="1000" minCon="10" balance="1"
    writeType="0" dbType="mysql" dbDriver="jdbc">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="jdbc:mysql://127.0.0.1:3306?userSSL=false" user="root" password="123456">
            <readHost host="slave" url="jdbc:mysql://127.0.0.1:3306?userSSL=false" user="root" password="123456"></readHost>
        </writeHost>
</dataHost>

# server.xml
<user name="user" defaultAccount="true"> 
    <property name="password">123456</property>
    <property name="schemas">SHOPPING,ITCAST,ITCAST_RW</property>
</user>

balance: 负载均衡策略，目前取值有4种

4.2 双主双从

4.2.1 双主双从搭建

• 主库配置（两台主库配置一样，就是server-id不一样）

  1. 修改配置文件 /etc/my.cnf

     # mysql 服务器id，保证整个集群环境中唯一，默认1
     server-id=1
     # 制定同步的数据库
     binlog-do-db=db01
     binlog-do-db=db02
     binlog-do-db=db03
     #在作为从数据库的时候，有写入操作也要更新二进制文件
     log-slave-updates
     

  2. 重启mysql服务器

     systemctl restart mysqld
     

  3. 两台主库创建账户并授权

     #创建itcat用户，并设置密码，该用户可以在任意主力连接改mysql服务
     CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456'
     # 为`itcat`@`%` 用户分配主从配置权限
     GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%'
     

     通过指令查看两台主库的二进制日志坐标

     show master status
     

• 从库配置

  1.修改配置文件 /etc/my.cnf

  # mysql 服务器id，保证整个集群环境中唯一，默认1
  server-id=2
  

  2.重启mysql服务器

  systemctl restart mysqld
  

• 两台从库配置关联的主库

  CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx',MASTER_USER='xxx',MASTER_PASSWORD='xxx',MASTER_LOG_FILE='xxx',MASTER_LOG_POS=xxx;
  

  需要注意slave1对应的是master1，skave2对应的是master2

  启动两台从库主从复制，查看从库状态

  start slave;
  show slave status \G;
  # 当 slave_IO_Running与slave_SQL_Running都为yes表示成功
  

• 两台主库相互复制

  master2复制master1，master1复制master2

  CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx',MASTER_USER='xxx',MASTER_PASSWORD='xxx',MASTER_LOG_FILE='xxx',MASTER_LOG_POS=xxx;
  

  启动两台主库主从复制，查看主库状态

  start slave;
  show slave status \G;
  # 当 slave_IO_Running与slave_SQL_Running都为yes表示成功
  

4.2.2 双主双从读写分离

mycat控制后台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控制，通过write Type及switchType来完成失败自动切换的。

# schema.xml
<schema name="ITCAST_RW2" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7">
    < !--<table name="sku" dataNode="dn7" primaryKey="id" /> -->
</schema>

<dataNode name="dn7" dataHost="dhost7" database="db01" />

<dataHost name="dhost7" maxCon="1000" minCon="10" balance="1"
    writeType="0" switchType="1" dbType="mysql" dbDriver="jdbc">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master1" url="jdbc:mysql://127.0.0.1:3306?userSSL=false" user="root" password="123456">
            <readHost host="slave1" url="jdbc:mysql://127.0.0.2:3306?userSSL=false" user="root" password="123456"></readHost>
        </writeHost>
        <writeHost host="master2" url="jdbc:mysql://127.0.0.3:3306?userSSL=false" user="root" password="123456">
            <readHost host="slave2" url="jdbc:mysql://127.0.0.4:3306?userSSL=false" user="root" password="123456"></readHost>
        </writeHost>
</dataHost>

# server.xml
<user name="user" defaultAccount="true"> 
    <property name="password">123456</property>
    <property name="schemas">SHOPPING,ITCAST,ITCAST_RW2</property>
</user>

#参数说明
balance=“1”
代表 全部的readHost 与 stand by writeHost 参与select 语句的负载均衡，简单的说，当双主双从模式(M1->S1， M2->52，并且M1与M2 互为主备），正常情況下，M2,S1,S2 都参与select 语句的负载均衡；

writeType
0：写操作都转发到第1台writeHost, writeHost1挂了，会切换到writeHost2 上；
1：所有的写操作都随机地发送到配置的writeHost上；

switchType
    -1: 不自动切换
    1:自动切换