mysql

最新推荐文章于 2023-07-09 12:47:43 发布

## name

最新推荐文章于 2023-07-09 12:47:43 发布

阅读量149

点赞数

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/nzgry_csdn/article/details/112155261

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

mysql

存储函数

语法：

创建：create function 函数名(参数名参数类型, ···) returns 返回值类型函数体；
使用：select 函数名(参数列表)；
查看函数信息：show create function 函数名\G；
查看所有函数：show function status\G；
删除函数：drop function 函数名；

使用示例：

# 创建
CREATE FUNCTION f1(num INT)
RETURNS VARCHAR(255) 
RETURN (SELECT COUNT(*) FROM t1 LIMIT num);
# 使用
SELECT f1(10);
# 删除
DROP FUNCTION f1;

存储过程

语法：

创建：

create procedure 名称(out/in/inout 参数名 数据类型) 特征 过程体；

查看所有：show procedure status；
删除xxx：drop procedure xxx；

语法说明：每个参数由三部分组成参数输入输出类型、参数名和参数的数据类型，其中输入输出类型有三种：

in：输入参数，外部传入的参数，可以在存储过程中使用；
out：可以在存储过程中改变并且返回，不能被传入；
inout：调用时指定，可以被改变和返回；

使用示例：

# 创建
DELIMITER @
CREATE PROCEDURE p1(IN tid INT, OUT res VARCHAR(30), INOUT maxid INT)
BEGIN
SELECT c1 INTO res FROM t1 WHERE id = tid;
SELECT COUNT(*) INTO maxid FROM t1 WHERE id <= maxid;
END; @
DELIMITER ;
# 使用
SET @maxid = 123;
CALL p1(122, @rr, @maxid);
SELECT @rr, @maxid;

触发器

语法：

创建：create trigger 名字 before/after insert/update/delete on 表名 for 执行间隔触发器执行的SQL；
查看所有触发器：show triggers \G；
删除触发器：drop trigger xxx；

mysql调优

mysql优化思路：

截取SQL语句，识别有问题的SQL；
确认语句执行；
语句分析、优化、验证优化；
一般优化执行慢和运行频率高的SQL；
可以使用pt-query-digest工具定位有问题的SQL；

定位低效率SQL

两种定位方式：

执行以下命令，开启慢查询日志：

#显示是否开启了慢查询日志
show variables like 'slow_query_log'; 
#开启慢查询日志
set global slow_query_log = on 
#慢查询日志输出位置
set global slow_query_log_file = '/data/mysql/slowsql.log' 
set global log_queries_not_using_indexes = on 
#设置耗时查询时间阈值
set global long_query_time = 0.1

使用druid数据源自带的监控；
使用profile查询SQL执行时间：

# 显示状态
SHOW VARIABLES LIKE '%profil%';
# 开启
SET profiling=1;
# 执行SQL······
SELECT * FROM t_movie WHERE id = 122;
# 显示SQL执行的时间
SHOW profiles;
# 显示CPU、IO消耗
SHOW profile CPU,BLOCK IO io FOR query 1;
# 关闭
SET profiling=0;

explain分析SQL

explain结果列说明：

select_type：查询类型，包括simple、primary（最外面的查询）、union（union的第二个或更后面）、dependent union（union的第二个或更后面）、union result、subquery（第一个子查询）、dependent subquery（第一个子查询）、derived（from子句的子查询）
table：查询引用的表；
type：重要字段，性能由高到低为（一般要保证至少到达range）
system：const的特例，表中只有一行；
const：只有一个匹配行；
eq_ref：一对一的主键连接查询；
ref：一对多的主键连表查询；
ref_or_null：包含null的主键连表查询，可以看出索引字段尽量不要有null；
index_merge：联合索引；
unique_subquery：子查询使用了唯一索引；
index_subquery：子查询使用了非唯一索引；
range：只检索给定范围的行，即按范围查询；
index：扫描了整个索引树；
all：全表扫描；
possible_keys：使用了那个索引的名字；
key：实际使用的索引名；
key_len：使用的索引键的长度；
ref：与key一起表示使用了什么索引；
rows：查询至少必须检查的行数，对innodb是估计值；
extra：查询的详细信息：

常用优化手段

尽量去除连表操作；
尽量减少操作到的列的数目，明确要查询的列，少用select * ；
改进表结构，精简数据类型和约束条件，字段长度在业务范围内尽量短，字段尽量设置默认值；
使用连接来代替子查询（从本人经验看这个不一定，实际中可以对比测试下在决定）；
多张大表关联，可以先分页(筛选出需要关联的数据)再关联；
合理的构建索引，SQL中有意合理的利用索引；
删除重复、不用的索引，过多的索引会影响insert和update的效率；
减少SQL语句要扫描的行的数量；
尽量避免使用游标；
尽量避免使用大的事务操作，提高系统并发能力；
尽量避免向客户端返回大数据量，若数据量过大，要考虑业务逻辑设计是否合理，是否有可代替的方案；
delete、insert、update能批量操作就尽量批量操作；
考虑分库分表；
持久化要求不高的部分数据使用redis；
读多写少的数据或者数据量大的数据使用mongodb；

索引优化

优化概述

从explain的type中可以查看是否使用了索引，一般至少要保证range级别；从extra字段中可以得知排序是否使用了索引（避免出现filesort），是否使用了覆盖索引（尽量要有using index）、是否有临时表等信息；

常见索引优化手段：

group by和order by的表达式尽量只涉及一个列，尽量要有索引；
where中尽量不用不等于、null，不对字段进行函数操作等，这些操作会使索引失效；
in、not in要慎用，否则会导致全表扫描，可以考虑用exist或between代替；
确保表关联的字段数据类型尽量简单、一致且有索引；
使用union代替or，or可能会导致索引失效；
避免使用like，确实要用可以用左匹配的like ‘%abc’；如确实需要like ‘%abc%’，可以考虑使用搜索引擎代替；

在范围查询中，使用索引不一定就比全表扫描效率高，比如当范围查询返回结果较多时，在不是覆盖索引的情况下会检索索引树和实际数据，反而全表检索只需要检索一次就可以返回结果，这种情况mysql的查询优化器会根据返回结果的大小自动选择全表扫描或者使用索引；

联合索引生效原则：

所有列精确匹配索引一定生效
最左列精确匹配，右边列使用范围查询、非等查询、不使用右边列，索引一样生效。
最左列使用范围查询时：
- 如果是覆盖查询，索引一律生效
- 如果不是覆盖查询，当查询优化器预计范围匹配记录较大的时候，索引不生效；当查询优化器预计范围匹配记录较小的时候，索引生效。
如果最左列没使用精确或者范围查询，联合索引不可能生效。
like 查询跟单列索引类似，也是最左匹配原则。

索引覆盖

聚簇索引和非聚簇索引：

InnoDB聚簇索引（即叶子节点存储了行记录的索引）：

如果表定义了PK（主键），那么PK就是聚簇索引；
如果表没定义PK，那么第一个unique就使聚簇索引；
如上面的都没有，那么会有一个隐藏的ROWID作为聚簇索引；

其他普通索引都是非聚簇索引，非聚簇索引的叶子节点存储的行记录的头指针；

回表查询：即第一次通过普通索引定位到主键值，然后第二次再通过聚集索引定位到具体行的查询就是回表查询；

索引覆盖：索引覆盖其实就是一种避免回表查询的优化策略，具体做法就是将要查询的数据作为索引列建立普通索引，使得可以直接在索引中返回需要的数据。

引擎选择

InnoDB是mysql的默认事务型引擎，一般默认优先考虑使用InnoDB；InnoDB支持事务，隔离级别可设定，支持行级锁，崩溃恢复能力也很好，但是效率不如MyISAM；

MyISAM引擎不支持事务，只支持表级锁，故障恢复能力较差，但是数据可压缩，拥有较好的读写能力，一般可以用于只读不写的场景，或者像日志收集类只写不读的应用；

配置优化

# 指mysql暂时停止回答新请求之前的短时间内多少个请求可以存在堆栈中
# 超过这个数量的连接将不被授予连接资源
# 默认50，可以适当调大
back_log=50
# 默认8小时，可以适当调小，减少wait的连接数，如果系统使用的人少，可以适当调大
wait_timeout=28800
# 最大连接数，并发较大时，可以适当调大
max_connections=151

# MyISAM参数
# 索引缓冲区大小，适当调大，一般设置物理内存的30~40%
key_buffer_size=8M
# 查询缓存的内存大小，适当调大，推荐16M或32M
read_buffer_size=128K
# 打开查询缓存，默认关闭
query_cache_type=ON
# 查询缓存大小，适当调大，如16M
query_cache_size=1M
# 查询缓存大小限制，只有1M以下才会被缓存
query_cache_limit=1M
# 指定在网络传输中一次消息传输量的最大值
max_allowed_packet = 1M

# InnoDB参数
# 索引和数据缓冲区的大小，一般可以设置为物理内存的60%~70%
innodb_buffer_pool_size=128M
# 缓冲池实例个数，可以多设置几个，如4个、8个
innodb_buffer_pool_instances=1
# 0代表每秒写入到日志并同步到磁盘，数据库故障会丢失1秒左右事务数据。
# 1为每执行一条SQL后写入到日志并同步到磁盘，效率较低。
# 2代表只把日志写入到系统缓存区，再每秒同步到磁盘，如果服务器故障，可能会丢失事务数据。
# 对数据安全性要求不是很高的推荐设置2
innodb_flush_log_at_trx_commit=1
# 开启每个表独立表空间
innodb_file_per_table=ON

# memory存储引擎表的最大容量
max_heap_table_size=16M
# 临时表使用的内存大小，如果sql中有高级的group by，或者有比较大临时表，可以适当调大
tmp_table_size=16M
# 连接操作的内存缓存大小，每个连接独享的
join_buffer_size=256KB
# 排序缓存大小，每个连接独享
sort_buffer_size=256KB
# 查询缓存的内存最大大小，每个连接独享
read_rnd_buffer_size=256KB
# 缓存线程的空间大小，可以适当调大
thread_cache_size=9
# ······

单机数据库策略

早期服务器资源昂贵，响应速度要求低，于是有三范式的设计——最大程度的牺牲查询效率和查询时间来节省磁盘空间；三范式：

数据字段不能继续拆分；
非主键必须依赖全部主键，不能部分依赖；
非主键不能依赖非主键字段；

随着用户逐渐增加，并发渐渐增加，于是有了设计满足反范式——最大程度的牺牲磁盘空间来提高查询效率和查询时间；有了索引——按照一定规则（排序）的数据结构设计的，实现可以快速定位磁盘文件的一批数据文件（mysql是b+tree）；

单机结构的数据库最终的技术瓶颈限制在物理上限，需要通过高可用的分布式数据库集群来解决，后面简单介绍了几种MySQL集群。

读写分离：MySQL主从复制、Galera集群本身都不支持读写分离，官方提供的MySQL Router也不支持，要实现读写分离有两种方式：

使用中间件，如：mycat；
程序自己实现，如：使用spring的AOP；

主从复制

原理

mysql主从复制的步骤为：

主节点开启记录写操作命令的本地二进制日志文件，将压缩的sql语句放到本地日志中；
从节点通过I/O线程实时的去主节点监听日志，并告诉主服务器最后一次读取更新的位置，发现更新时就抓取到本机的中继日志；
通过sql线程监听中继日志，每有新增的SQL语句将在本地客户端执行；最终实现主从数据同步。（发现日志是否有新增是根据position是否发生改变，position记录了SQL执行的步骤数，position增加了，需要执行position增加的这段SQL语句）。

mysql支持的复制类型有：

基于语句的复制，即从服务器执行与主服务器上一样的SQL；
基于行的复制，即把改变的内容复制过去；
混合复制，即默认采用语句复制，当语句无法精确复制时采用行复制；

主从复制常用拓扑结构：

一主多从：从节点之间互不通讯，只与主节点通讯，该结构适用于读多写少的情况，但存在slave过多时，master会成为扩展的瓶颈；
互为主从（主动）：两台都既是master又是slave，且读写操作都随机在两台上进行，该结构适用于读写均衡，但存在写冲突的可能；
互为主从（被动）：两台都既是master又是slave，但写操作只在一台上进行，只有这台宕机写操作才会转到另一台；
级联复制：即从节点也可以有从节点（打开log-slave-update选项），可以减轻master的同步压力；

复制体系结构的基本原则：

每个slave只能有一个master；
每个slave只能有一个唯一的服务器ID；
每个master可以有很多slave；
slave可以是slave的slave；

复制的最大问题（复制延时）：首先同步本身就会有一定的延时，并且当写请求很多时，延时问题会更加严重，甚至可能会达到分钟级的，同时slave的增加还会进一步加重这个问题，所以主从结构也只适用于中小型的应用，不适合高并发的场景；

安装

先按单机模式安装mysql，这里以mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz版本的mysql为例：

安装依赖：yum -y install perl perl-devel autoconf libaio perl-Data-Dumper
添加用户和用户组：groupadd mysql -> useradd -g mysql mysql
创建文件/etc/my.cnf

[mysql] 
# 设置mysql客户端默认字符集 
default-character-set=utf8
socket=/data/mysql-5.7.27-linux-glibc2.12-x86_64/lock/mysql.sock
[mysqld] 
#skip-name-resolve 
#设置3306端口 
port=3306  
socket=/data/mysql-5.7.27-linux-glibc2.12-x86_64/lock/mysql.sock
# 设置mysql的安装目录 
basedir=/data/mysql-5.7.27-linux-glibc2.12-x86_64
# 设置mysql数据库的数据的存放目录 
datadir=/data/mysql-5.7.27-linux-glibc2.12-x86_64/data
# 允许最大连接数 
max_connections=200 
# 服务端使用的字符集默认为8比特编码的latin1字符集 
character-set-server=utf8 
# 创建新表时将使用的默认存储引擎 
default-storage-engine=INNODB 
#lower_case_table_name=1

创建data和lock文件，并将其改为mysql用户所有(chown -R mysql:mysql ./data)；
初始化mysql，执行：./bin/mysqld --initialize --user=mysql（初始化完成后，会打印初始密码）
配置mysqld服务，执行：cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld
将mysql的bin目录配置到环境变量
启动mysql服务：service mysqld start
使用上面打印的初始密码登录mysql
设置密码：SET PASSWORD = PASSWORD('123456');
允许远程访问：grant all privileges on *.* to root@"%" identified by '123456' with grant option;
刷新权限：flush privileges;安装完成。

配置主从关系：

配置主服务器，修改/etc/my.cnf文件；

# 在[mysqld]节点下加入以下两行
server-id=1  # 集群中识别的id
log_bin=mysql-bin  # 启用二进制日志

重启mysql（service mysqld restart）；
登录主节点并执行：flush tables with read lock（锁表，只允许读取）；
查看master状态：show master status（记下file和position）；
解除锁定（待从服务器配置完成后）：unlock tables，主服务器配置完成；
配置从服务器，在/etc/my.cnf的mysqld节点下增加一行：sever-id=2；
重启mysql；
登录mysql并执行下面的命令挂接到主节点：

# 这里的mysql-bin.000001和154就是上面看到的file和position
change master to master_host='hadoop01', master_port=3306, master_user='root', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=154;
# 此过程如果出错，要先停止服务（stop slave;reset slave），再重新配置

查看slave状态：show slave status\G；
重启mysql；
主从关系配置完成。
若要配置互为主从关系，只需要两个服务器再做一次相反的配合即可。

注意：此时从数据库还可以新增数据，但是会破坏主从环境，要恢复环境，需要先停止从服务（stop slave），再人为恢复从数据库与主数据库一致，即根据主节点show master status的结果重新搭建从服务器。

实际的mysql读写分离是由客户端实现的，由客户端解析SQL语句，然后发送到不同服务器执行；

Galera

简介

概述：Galera Cluster for MySQL是一套基于同步复制的多主MySQL集群解决方案，其主要特点有：

同步复制，主备无延迟，在数据库挂掉后，数据不会丢失，不需要binlog；
多主架构，真正的多点读写集群，任意节点都可以读写，任何时候的读写数据都是最新的；
支持多主同时读写，保证数据一致性；
真正的行级别的并发复制；
集群中的各个节点都保存全量的数据；
自动添加新节点和剔除故障节点；
对用户透明，用户任意连一台机器就可以操作，跟操作单机一样；

Galera复制过程（二阶段提交算法）：

客户端发起commit时，所有本事务的操作会被收集到写入集中；
写入会随后会被复制到其他节点上；
写入会在每个节点上进行确认，都确认时才会提交事务，否则回滚；

galera缺点：

需要为原生mysql打补丁；
只支持innodb引擎；
至少三个节点；

使用Galera需要给MySQL打wsrep补丁，且只支持Maria、xtradb、innodb的存储引擎，打好补丁直接启动MySQL即可；

donor和joiner：新接入的节点叫joiner，给joiner提供复制的节点叫donor；在生产环境中，建议设置一个专用的donor，这个专用的donor不执行任何来自客户端的SQL请求，这对数据一致性、安全性、高可用都很重要；

安装

galera的安装需要另外的安装包，这里使用三台服务器，以mysql-wsrep-5.7.31-25.23-linux-x86_64.tar.gz+galera-3-25.3.31-linux-x86_64.tar.gz为例安装（因为我前面已经安装了一个MySQL了，所以这里需要改下配置文件路径和端口）；

安装依赖yum -y install lsof
所有节点解压安装包，在安装包目录下添加my.cnf配置文件；

[mysql]
default-character-set=utf8
socket=/data/mysql/mysql-wsrep-5.7.31-25.23-linux-x86_64/lock/mysql.sock

[mysqld]
port=3307
basedir=/data/mysql/mysql-wsrep-5.7.31-25.23-linux-x86_64
socket=/data/mysql/mysql-wsrep-5.7.31-25.23-linux-x86_64/lock/mysql.sock
datadir=/data/mysql/mysql-wsrep-5.7.31-25.23-linux-x86_64/data
character-set-server=utf8
default-storage-engine=INNODB

# 对应上面拷贝的libgalera_smm.so文件
wsrep_provider=/data/mysql/mysql-wsrep-5.7.31-25.23-linux-x86_64/lib/plugin/libgalera_smm.so
wsrep_cluster_address=gcomm://hadoop01:4567,hadoop02:4567,hadoop03:4567
binlog_format=ROW
# 节点名称,不同节点的名称应该不同
wsrep_node_name=node1

创建对应的data和lock文件，并将其改为mysql用户所有；
修改./support-files/mysql.server；

# 这两个目录设置为正确的路劲
basedir=/data/mysql/mysql-wsrep-5.7.31-25.23-linux-x86_64
datadir=
# 添加这行
myconf=/data/mysql/mysql-wsrep-5.7.31-25.23-linux-x86_64/my.cnf

# 找到这行，改为正确的目录
# conf=/etc/my.cnf
conf=/data/mysql/mysql-wsrep-5.7.31-25.23-linux-x86_64/my.cnf

# 找到这行，改为下面的形式
# $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null &
$bindir/mysqld_safe --defaults-file="$myconf" --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null &

拷贝mysql.server文件：cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld3307
将galera下的文件拷贝到mysql-wsrep的lib/plugin目录下

cp ../galera-3-25.3.31-linux-x86_64/bin/garbd ./lib/plugin/
cp ../galera-3-25.3.31-linux-x86_64/lib/libgalera_smm.so ./lib/plugin/

准备一个初始化文件init-pass.sql：

alter user 'root'@'localhost' identified by '123456';

初始化./bin/mysqld --defaults-file=./my.cnf --init-file=/home/init-pass.sql --initialize --user=mysql（注意这个init-pass.sql要使用绝对路径）
节点1启动：service mysqld3307 start --wsrep_cluster_address=gcomm://
其他节点启动：service mysqld3307 start
登录mysql（mysql -uroot -P3307 -h127.0.0.1 -p123456），设置允许远程访问；
搭建完成；

查看集群：SHOW STATUS LIKE 'wsrep_%'、SHOW VARIABLES like 'wsrep_%';

状态参数说明：

集群完整性
- wsrep_cluster_state_uuid:在集群所有节点的值应该是相同的,有不同值的节点,说明其没有连接入集群.
- wsrep_cluster_conf_id:正常情况下所有节点上该值是一样的.如果值不同,说明该节点被临时”分区”了.当节点之间网络连接恢复的时候应该会恢复一样的值.
- wsrep_cluster_size:如果这个值跟预期的节点数一致,则所有的集群节点已经连接.
- wsrep_cluster_status:集群组成的状态.如果不为”Primary”,说明出现”分区”或是”split-brain”状况.
节点状态检查:
- wsrep_ready: 该值为ON,则说明可以接受SQL负载.如果为Off,则需要检查wsrep_connected.
- wsrep_connected: 如果该值为Off,且wsrep_ready的值也为Off,则说明该节点没有连接到集群.(可能是wsrep_cluster_address或wsrep_cluster_name等配置错造成的.具体错误需要查看错误日志)
- wsrep_local_state_comment:如果wsrep_connected为On,但wsrep_ready为OFF,则可以从该项查看原因.
复制健康检查:
- wsrep_flow_control_paused:表示复制停止了多长时间.即表明集群因为Slave延迟而慢的程度.值为0~1,越靠近0越好,值为1表示复制完全停止.可优化wsrep_slave_threads的值来改善.
- wsrep_cert_deps_distance:有多少事务可以并行应用处理.wsrep_slave_threads设置的值不应该高出该值太多.
- wsrep_flow_control_sent:表示该节点已经停止复制了多少次.
- wsrep_local_recv_queue_avg:表示slave事务队列的平均长度.slave瓶颈的预兆.

MySQL Router

介绍

MySQL Proxy目前官网还只有内测版，不推荐使用，作为替代，MySQL Router（官方推荐）可以在MySQL服务器之间提供透明路由和负载均衡，有效提高MySQL服务的高可用性和可伸缩性，其具体功能描述如下：

透明路由：隐藏多个MySQL示例，使得客户端只需要连接到Router即可，而不必关心具体MySQL服务器；
元数据缓存：缓存MySQL服务器列表或者Cluster的拓扑状态，能动态感知集群状态，客户端无需关心；
简单重定向，MySQL Router的工作流程如下：
- 客户端连接到Router，Router检查可用的MySQL服务器列表；
- Router打开一个可用的MySQL服务器连接，然后转发客户端和服务器之间数据（并不检查数据包内容）；
- 如果服务器故障，Router将断开连接，当客户端重连时，Router将切换到另一个可用的MySQL；

安装配置

配置文档：https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-conf-options.html

安装

更改安装包的拥有者为mysql；
切换到mysql用户解压即可，接着可以修改配置如下：

#[DEFAULT]
# 日志路径
#logging_folder =
# 插件路径
#plugin_folder = /usr/local/lib/mysqlrouter
# 配置路径
#config_folder = /etc/mysql
# 运行时状态路径
#runtime_folder = /var/run
# 数据文件路径
#data_folder = /var/lib
#keyring_path = /var/lib/keyring-data
#master_key_path = /var/lib/keyring-key

#[logger]
#level = INFO
#filename = mysqlrouter.log
#timestamp_precision = second

# 路由策略配置，可以配置多个，basic_failover名字随意
[routing:basic_failover]
# To be more transparent, use MySQL Server port 3306
bind_address = 0.0.0.0
bind_port = 7001
routing_strategy = round-robin
# mode = read-write
destinations = hadoop01:3306,hadoop02:3306

# If no plugin is configured which starts a service, keepalive
# will make sure MySQL Router will not immediately exit. It is
# safe to remove once Router is configured.
[keepalive]
interval = 60

mode参数，有两个可选值，具体含义如下：

read-write：首个可用算法，即优先使用第一个server，当第一个不可达时，则使用第二个，依次进行，当最后一个server不可用时，不会重新循环，而是不在提供服务了，只有重启Router才能继续使用；
read-only：轮询算法，会与每个server保持心跳探测，依次选择server新建连接，当某个server不可达时，则选择下一个，当所有server都不可达时，则暂停服务，此时当server重新加入时，就可以重新使用了；（该方式存在的问题是，Router不会将已有的连接迁移到新加入的server，也就是说某个server宕机恢复后，如果没有新连接，那个这个server在这段时间就是空闲的；对此，可以给连接池设置一个maxAge参数）

routing_strategy参数，8.0.4之后可以使用这个代替mode参数（这两个参数不能同时配置），该参数有4个可选值：

round-robin：每个新连接都会连接到下一个可用的server；
round-robin-with-fallback：每个新连接会连接到下一个备用server，如果备用server不可达，这个就相当于round-robin；
first-available：每个新连接会连接到第一个可用的server（相当于改进版的read-write）；
next-available：类似mode参数的read-write；

启动：./bin/mysqlrouter --config ./mysqlrouter.conf

mycat中间件

彻底开源的面向企业应用开发的大数据库集群，性能极高的数据库中间件技术；具有读写分离、支持横向扩展、水平扩展、整合多种资源等特点；其原理是：拦截客户端的SQL语句，根据数据库集群的配置逻辑计算存储在哪个数据库，从而实现读写分离和分布式计算；

安装：解压即用；

核心概念：

逻辑库：基于后端高可用分布式读写分离的数据库水平分片集群，提供一个虚拟的逻辑数据库，对于用户而言，和直接使用真实数据库是一样的；
逻辑表：用户直接操作的表，又分分片表和非分片表；
- 分片表：单个逻辑表数据超过一定程度时，需要使用分片表的结构对整体数据进行切分处理；（注意：在分片表格中，一旦进行关联查询，在表格设计有漏洞的情况下，会造成mycat中间件的底层跨库操作，一旦数据库关联内容不在同一个数据库中，会无法使用数据）
- 非分片表：数据量不大，直接保存到一个真实的数据库处理；

配置文件（两个）：

server.xml：配置当前mycat启动的资源设定（例如端口、线程登录名、密码等）；
schema.xml：逻辑库，逻辑表，分片表，ER表的配置，底层链接的实际数据库信息。

schema.xml关键标签说明（默认配置就具备读写分离和高可用故障转移的功能，同时要具备故障转移的功能需要配置多个writeHost）

<!-- 逻辑库，name：逻辑库名(server.xml配置的schemas)，checkSQLschema：自动拼接逻辑库名(默认false)， sqlMaxLimit：一次查询最大返回数据条数，dataNode：配置的真实数据节点的name-->
<schema name="test" checkSQLschema="true" sqlMaxLimit="100">
    <!-- 逻辑表，name：数据库表名称，dataNode：一个或多个数据库节点名称以,分割，rule：分片计算规则，primaryKey：主键，type：表类型 -->
    <table name="p1" dataNode="dn1" rule="auto-sharding-long">
        <!-- 从表（用于配置ER分片表，该表中必须要有关联字段），name：从表名称，primaryKey：主键，joinKey：外键，parentKey：主表中的关联字段 -->
        <childTable name="orders" primaryKey="ID" joinKey="customer_id" parentKey="id">
    </table>
</schema>
<!-- 数据节点，有分片表时要配置多个，name：节点名称，dataHost：配置的数据库名称，database：真实的数据库名 -->
<dataNode name="dn1" dataHost="localhost1" database="test" />
<!-- 配置真实数据库，
balance：（配置读写分离）
    0：不开启读写分离，所有的读写操作都在第一个writeHost标签完成；
    1：全部的readHost和备用writeHost都参与读数据的平衡，如果读的请求过多，负责写的第一个writeHost也分担一部分（默认）；
    2：所有的读操作都随机的在所有的writeHost和readHost中进行
    3：所有的读操作，都到writeHost对应的readHost上进行（备用writeHost不参加了）,在集群中没有配置ReadHost的情况下,读都到第一个writeHost完成
writeType：控制当前datahost维护的数据库集群的写操作
    0：所有的写操作都在第一个writeHost标签的数据库进行
    1：所有的写操作，都随机分配到所有的writeHost（mycat1.5完全不建议配置了）
switchType：故障切换的逻辑
    -1：故障不切换(第一个writeHost进行写操作,故障后,当前分片不可用)
    1：故障切换，当前写操作的writeHost故障，进行切换，切换到下一个writeHost；原有的writeHost恢复之后,自动添加到当前集群（默认）
-->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="hadoop01:3306" user="root" password="123456">
        <readHost host="hostS2" url="hadoop02:3306" user="root" password="123456"/>
    </writeHost>
</dataHost>

分片表配置说明：

使用分片表的数据库需要关闭主从复制（stop slave;reset slave）；
配置多个dataHost数据节点；
配置table标签中的rule属性；rule的取值来源于rule.xml；
rule配置说明；（除了对整数的分片处理，还有对任何数据的hash取余算法sharding-by-murmur）

<!--name：table中rule的值，columns：字段名，algorithm：计算函数-->
<tableRule name="auto-sharding-long">
    <rule>
        <columns>id</columns>
        <algorithm>rang-long</algorithm>
    </rule>
</tableRule>
<!-- 计算函数，可以在属性中定义辅助文件或者参数 -->
<function name="rang-long" class="org.opencloudb.route.function.AutoPartitionByLong">
    <property name="mapFile">autopartition-long.txt</property>
</function>

最后配置为多个dataHost即可。

分片时关联查询数据不全的问题解决方案：

全局表：即每一个分片库中都有一个完整的表；当分片表需要和一个数据量不大的表做关联查询的时候，需要将这个数据量不大的表配置为全局表（通过配置table标签的**type=“global” dataNode="**所有分片节点"）。
ER分片表：该表会通过关联字段获取父表相关数据，然后通过父表的分片规则进行计算，最终将相关的数据分配到同一个真实数据库中，从而避免了跨库操作（通过childTable标签配置，注意：使用ER分片表时，计算分片的字段要使用关联字段，否则插入操作可能会报错）。

## name

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql

mysql目录mysql存储函数存储过程触发器mysql调优定位低效率SQLexplain分析SQL常用优化手段索引优化优化概述索引覆盖引擎选择配置优化单机数据库策略主从复制原理安装mycat中间件存储函数语法：创建：create function 函数名(参数名参数类型, ···) returns 返回值类型函数体；使用：select 函数名(参数列表)；查看函数信息：show create function 函数名\G；查看所有函数：show function status\G；删
复制链接

扫一扫

专栏目录