MySQL总结
欢迎光临我的博客[http://poetize.cn]，前端使用Vue2，聊天室使用Vue3，后台使用Spring Boot
Mysql
中文乱码 修改字符集
vim /etc/my.cnf
在最后加入中文字符集配置:character_set_server=utf8
重启:systemctl restart mysqld
修改已存在数据库的字符集:alter database saradb character set 'utf8';
修改已存在表的字符集:alter table user convert to character set 'utf8';
用户与权限管理
创造用户:create user ld identified by 'aaa';
查看当前用户权限:show grants;
设置权限:
grant select,insert,delete,drop on saradb.* to ld@localhost;
grant all privileges on *.* to ld@'%' identified by 'aaa';
修改当前用户的密码:set password=password('aaa');
修改某个用户的密码:update mysql.user set password=password('aaa') where user='ld';
修改用户名:update mysql.user set user='ld' where user='sara';
删除用户:drop user ld;
关闭数据库服务的防火墙:service iptables stop
mysql 2058错误:
GRANT ALL ON *.* TO 'root'@'%';
flush privileges;
ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'aaa';
flush privileges;
Docker 安装 Mysql
docker run -di --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=aaa mysql
docker exec -it mysql bash:伪终端
ps -ef | grep -i Mysql:查看Mysql进程
mkdir -p /data/mariadb/data
docker run -di --name mariadb -p 3306:3306 -e MYSQL_ROOT_PASSWORD=aaa -v /data/mariadb/data:/var/lib/mysql mariadb
sql检验原则
group by使用原则:select 后面只能放函数和group by后的字段
show variables like 'sql_mode';
select @@sql_mode;
改变全局sql_mode,对于新建的数据库有效:
set @@sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
已存在的数据库,需要在对应的数据下执行:
set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql逻辑架构
1. 程序访问与连接池沟通
2. 缓存,缓冲查询
3. SQL接口分析sql语句
4. 解析器复杂sql语句解析
5. 优化器优化,生成执行计划
6. 存储引擎按计划分类型执行
7. 存入缓存
8. 返回结果
查看sql执行周期
启用查询缓存:
/etc/my.cnf配置文件新增一行:query_cache_type=1
开启执行周期:
show variables like '%profiling%';
set profiling=1;
show profiles;
sql语句
select distinct
select_list
from
left_table join_type join right_table on join_condition
where
where_condition
group by
group_by_list
having
having_condition
limit
limit_number
存储引擎
MyISAM:不支持外键与事务,表锁,只缓存索引,不缓存真实数据(节省资源)
InnoDB:支持外键与事务,行锁(适合高并发),不仅缓存索引,还缓存真实数据
查看mysql当前默认的存储引擎:show variables like '%storage_engine%';
sql性能下降原因
数据过多:分库分表
join关联太多表:sql优化
没有充分利用索引:建立索引(效果最好)
服务器调优:调整my.cnf
join
from tableA inner join tableB on join_condition:内连接(交集)
from tableA left join tableB on join_condition:左外连接(A表全集)
from tableA left join tableB on join_condition where tableB.id is null:左外连接(A表独有)
union:联合查询,自动去重(影响性能),必须保证字段数与顺序一致(select a.*,b.*)
union all:联合查询,不去重,必须保证字段数与顺序一致(select a.*,b.*)
USE mydb;
CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
empno INT NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,NULL,100010);
七种join:
1. 所有有门派的人员信息( A、B两表共有)
select * from t_emp a inner join t_dept b on a.deptId = b.id;
2. 列出所有用户,并显示其机构信息(A的全集)
select * from t_emp a left join t_dept b on a.deptId = b.id;
3. 列出所有门派(B的全集)
select * from t_dept b
4. 所有不入门派的人员(A的独有)
select * from t_emp a left join t_dept b on a.deptId = b.id where b.id is null;
5. 所有没人入的门派(B的独有)
select * from t_dept b left join t_emp a on a.deptId = b.id where a.deptId is null;
6. 列出所有人员和机构的对照关系(AB全有)
#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
#left join + union(可去除重复数据)+ right join
SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id
7. 列出所有没入派的人员和没人入的门派(A的独有+B的独有)
SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id` IS NULL
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;
索引
优势:查询快,排序快
缺点:更新慢,占用空间
索引结构:平衡二叉树(时间复杂度logN)
BTree
B+Tree(内存占用率低,磁盘读写代价更低,查询效率更加稳定)
数组:查找N,插入1
有序数组(二分查找法):查找logN,插入N
链表:查找N,插入1
有序链表:查找N,插入N
二叉树(一般情况):查找logN,插入logN
二叉树(最坏情况):查找N,插入N
平衡树:查找logN,插入logN
哈希表:查找1,插入1
mysql索引分类:
单值索引key:即一个索引只包含单个列,一个表可以有多个单列索引
唯一索引unique:索引列的值必须唯一,但允许有空值
主键索引primary key:设定为主键后数据库会自动建立索引,innodb为聚簇索引
复合索引key (key1,key2):即一个索引包含多个列
创建:CREATE [UNIQUE] INDEX [indexName] ON table_name(column);
删除:DROP INDEX [indexName] ON table_name;
ALTER TABLE table_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
ALTER TABLE table_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
ALTER TABLE table_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。
ALTER TABLE table_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ,用于全文索引。
需要创建索引的情况:
频繁作为查询条件的字段
查询中与其他表关联的字段
排序的字段
统计的字段
分组的字段
Explain执行计划
id:每个id号码,表示一次独立的查询。一个sql的查询次数越少越好。
type显示的是访问类型,是较为重要的一个指标,结果值从最好到最坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system > const > eq_ref > ref > range > index > ALL
eq_ref:唯一性索引扫描,常用于主键或唯一索引扫描
ref:非唯一性索引扫描
range:范围索引扫描,一般在where语句中出现between,<,>,in等的查询
index:覆盖索引扫描
all:遍历全表
index_merge:组合索引扫描,通常出现在有or的关键字的sql中
Using filesort:order by未用上索引,文件排序,性能低
Using temporary:group by未用上索引,文件排序,包含filesort,性能极低
USing join buffer:两个表关联,关联字段一定要建立索引
单表索引优化
最佳左前缀法则:如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
过滤性最好的字段在索引字段顺序中,位置越靠前越好。
索引失效:
1. 在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
2. 范围查询索引右边的索引失效,存储引擎不能使用索引中范围条件右边的列。可能出现范围查询的字段应该放在复合索引中靠后的位置
3. <>不等于,is not索引失效
4. like '%abc'索引失效('abc%'不会失效)
关联,子查询索引优化
驱动表:前面的表,无法避免关联查询
被驱动表:后面的表,关联字段需要建立索引
1. 保证被驱动表的join字段已经被索引
2. left join时,选择小表作为驱动表,大表作为被驱动表。
3. inner join时,mysql会自动把具有索引的表作为被驱动表,如果没有索引,则大结果集的表选为被驱动表。
4. 子查询尽量不要放在被驱动表,有可能使用不到索引。
5. 能够直接多表关联的尽量直接关联,不用子查询。
查询员工姓名与员工ceo姓名
快(直接关联):
EXPLAIN SELECT SQL_NO_CACHE a.`name`,c.`name` ceo
FROM emp a
LEFT JOIN dept b ON a.`deptId`=b.`id`
LEFT JOIN emp c ON b.`ceo`=c.`id`;
慢(子查询):
EXPLAIN SELECT SQL_NO_CACHE a.`name`,(SELECT c.name FROM emp c WHERE c.id=b.`ceo`) ceo
FROM emp a LEFT JOIN dept b ON a.`deptId`=b.`id`;
6. 尽量不要使用not in或者not exists,用left outer join on xxx is null 替代
取所有不为掌门人的员工,按年龄分组 ,每个年龄段多少人
SELECT SQL_NO_CACHE age,count(*)
FROM emp a
WHERE id NOT IN(SELECT ceo FROM dept WHERE ceo IS NOT NULL)
group by age
having count(*)<10000
SELECT SQL_NO_CACHE age,count(*)
FROM emp a LEFT OUTER JOIN dept b ON a.id =b.ceo
WHERE b.ceo IS NULL
group by age
having count(*)<10000
#1. 列出自己ceo比自己年龄小的人员
EXPLAIN SELECT SQL_NO_CACHE a.`name`,a.`age`,c.`name` ceo,c.`age` ceoage
FROM emp a
LEFT JOIN dept b ON a.`deptId`=b.`id`
LEFT JOIN emp c ON b.`ceo`=c.`id`
WHERE a.`age`>c.`age`;
#2. 列出所有年龄低于自己门派平均年龄的成员
EXPLAIN SELECT SQL_NO_CACHE a.`name`,a.`age`,b.avgage
FROM emp a
INNER JOIN
(SELECT deptId,AVG(age) avgage
FROM emp
WHERE deptId IS NOT NULL
GROUP BY deptId) b
ON a.`deptId`=b.`deptId`
WHERE a.`age`<b.avgage;
CREATE INDEX idx_deptId_age ON emp(deptId,age);
#3. 列出至少有两个年龄大于40的成员的门派
EXPLAIN SELECT SQL_NO_CACHE b.`deptName`,COUNT(*)
FROM emp a
INNER JOIN dept b ON a.`deptId`=b.`id`
WHERE a.`age`>40
GROUP BY a.`deptId`
HAVING COUNT(*)>1;
CREATE INDEX idx_deptId_age ON emp(deptId,age);
#4. 显示每个门派年龄最大的人
EXPLAIN SELECT SQL_NO_CACHE b.`name`,b.`age`
FROM emp b
INNER JOIN
(SELECT a.`deptId`,MAX(a.`age`)maxage
FROM emp a
WHERE a.`deptId` IS NOT NULL
GROUP BY a.`deptId`)aa
ON b.`deptId`=aa.deptId AND b.`age`=aa.maxage;
排序分组优化
order by不过滤不索引,要想用上索引必须有过滤条件
order by字段顺序必须与索引顺序一致
order by要么都是升序,要么都是降序
当范围条件和group by或者order by的字段出现二选一时,优先观察条件字段的过滤数量,
如果过滤的数据足够多,而需要排序的数据并不多时,优先把索引放在范围字段上
双路排序:从磁盘取排序字段,在buffer进行排序,再从磁盘取其他字段。取一批数据,要对磁盘进行了两次IO扫描,效率低
单路排序:从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出(效率高)
单路排序缺点:
取出的数据的总大小超出了sort_buffer的容量,
导致每次只能取sort_buffer容量大小的数据,进行排序(创建tmp文件,多路合并),
排完再取取sort_buffer容量大小,再排...从而多次I/O。
单路排序优化:增大sort_buffer_size参数的设置,增大max_length_for_sort_data参数的设置,减少select后面的查询的字段。
group by使用索引的原则几乎跟order by一致,唯一区别是group by即使没有过滤条件用到索引,也可以直接使用索引。
清除索引
DELIMITER $$
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT '';
DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND index_name <>'PRIMARY';
DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2;
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index<>'' DO
SET @str = CONCAT("drop index ",_index," on ",tablename );
PREPARE sql_str FROM @str;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index='';
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END$$
CALL proc_drop_index("dbname","tablename");
慢查询日志
运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10秒以上的语句。
查看:SHOW VARIABLES LIKE '%slow_query_log%';
开启:set global slow_query_log=1;
永久开启:
将如下两行配置进my.cnf文件
slow_query_log =1
slow_query_log_file=/var/lib/mysql/sara-slow.log
时间查看:SHOW VARIABLES LIKE 'long_query_time%';
时间修改:set global long_query_time=1;
日志分析工具:mysqldumpslow
-s: 是表示按照何种方式排序
r: 返回记录
c: 访问次数
t: 查询时间
-t:即为返回前面多少条的数据;
-g:后边搭配一个正则匹配模式,大小写不敏感的
得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/sara-slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/sara-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/sara-slow.log
另外建议在使用这些命令时结合 | 和 more 使用 ,否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/sara-slow.log | more
视图
创建:
create view view_name as
select * from emp;
更新:
CREATE OR REPLACE VIEW view_name AS
select * from emp;
查询:
select * from view_name
将一段查询sql封装为一个虚拟的表,封装复杂sql语句,提高复用性
主从复制(具有延时问题,必须主从复制配置搭配好才能创造数据库)
1. 在从服务器可以执行查询工作(即我们常说的读功能),降低主服务器压力(主库写,从库读,降压)
2. 在从主服务器进行备份,避免备份期间影响主服务器服务(确保数据安全)
3. 当主服务器出现问题时,可以切换到从服务器(提升性能)
MySQL复制过程分成三步:
1 master将改变记录到二进制日志(binary log)。这些记录过程叫做二进制日志事件,binary log events
2 slave将master的binary log events拷贝到它的中继日志(relay log)
3 slave重做中继日志中的事件,将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的
配置:主从配置都在my.cnf配置文件的[mysqld]结点下
主机:
主服务器唯一ID:server-id=1
二进制日志储存路径:log-bin=自己本地的路径/data/mysqlbinlog
设置不要复制的数据库:binlog-ignore-db=mysql
设置需要复制的数据库:binlog-do-db=需要复制的主数据库名字
主机上建立帐户并授权slave:GRANT REPLICATION SLAVE ON *.* TO 'sara'@'%' IDENTIFIED BY 'aaa';
查询master的状态:show master status;
从机:
从服务器唯一ID:server-id=2
开启中继日志:relay-log=mysql-relay
从机上配置需要复制的主机:CHANGE MASTER TO MASTER_HOST='主机IP',MASTER_USER='sara',MASTER_PASSWORD='aaa',MASTER_LOG_FILE='binlog名字',MASTER_LOG_POS=Position数字;
启动从服务器复制功能:start slave;
查询slave的状态:show slave status\G;
停止从服务复制功能:stop slave;
重新配置从服务器:stop slave;reset master;
关闭防火墙:systemctl stop firewalld
Mycat(读写分离,数据分片,多数据源整合)
读写分离,负载均衡
Mycat的原理中最重要的一个动词是“拦截”,
它拦截了用户发送过来的 SQL 语句,
首先对 SQL 语句做了一些特定的分析:如分片分析、路由分析、读写分离分析、缓存分析等,
然后将此 SQL 发往后端的真实数据库,
并将返回的结果做适当的处理,最终再返回给用户
schema.xml:定义逻辑库,表、分片节点等内容
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
<dataNode name="dn1" dataHost="host1" database="saradb"/>
<dataHost name="host1" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.111.222:3306" user="root" password="aaa">
<readHost host="hostS1" url="192.168.111.223:3306" user="root" password="aaa"/>
</writeHost>
</dataHost>
</mycat:schema>
负载均衡类型,目前的取值有4种：
1. balance="0",不开启读写分离机制,所有读操作都发送到当前可用的writeHost上。
2. balance="1",全部的readHost与stand by writeHost参与select语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与M2互为主备),正常情况下,M2,S1,S2 都参与select语句的负载均衡。
3. balance="2",所有读操作都随机的在writeHost、readhost上分发。
4. balance="3",所有读请求随机的分发到readhost执行,writerHost不负担读压力
rule.xml:定义分片规则
server.xml:定义用户以及系统相关变量,如端口等
<user name="root">
<property name="password">654321</property>
<property name="schemas">TESTDB</property>
</user>
启动:
控制台启动:去mycat/bin目录下mycat console
后台启动:去mycat/bin目录下mycat start
登录:
管理:mysql -uroot -paaa -P9066 -h192.168.111.223
数据:mysql -uroot -paaa -P8066 -h192.168.111.223
分库
分库:将同一个库(lddb)的表(其他表,customer)放在不同主机的数据库,减轻数据库压力
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<!-- 分库:customer表将会创建在host2主机上,其他表将会创建在host1主机上 -->
<!-- 两个主机都需要手动创建数据库 -->
<table name="customer" dataNode="dn2"></table>
</schema>
<dataNode name="dn1" dataHost="host1" database="lddb_1"/>
<!-- 分库 -->
<dataNode name="dn2" dataHost="host2" database="lddb_2"/>
<dataHost name="host1" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.111.222:3306" user="root" password="aaa">
<readHost host="hostS1" url="192.168.111.223:3306" user="root" password="aaa"/>
</writeHost>
</dataHost>
<!-- 分库 -->
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostm2" url="192.168.111.223:3306" user="root" password="aaa"></writeHost>
</dataHost>
</mycat:schema>
分表
分表:将同一个表(order)的数据根据表的字段(customer_id)通过函数运算放入不同主机的数据库,减轻数据库压力
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="order" dataNode="dn1,dn2" rule="mod_rule"></table>
</schema>
</mycat:schema>
<tableRule name="mod_rule">
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>
跨库join
配置ER表:为了相关联的表的行尽量分在一个库下
<table name="order" dataNode="dn1,dn2" rule="mod_rule" >
<childTable name="order_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>
全局表:设定为全局的表,会直接复制给每个数据库一份,所有写操作也会同步给多个库。
<table name="dict_order_type" dataNode="dn1,dn2" type="global"></table>
设置全局序列
数据分表,如果自增主键会造成主键重复,需要设置全局序列(数据库方式)
利用数据库一个表来进行计数累加。
在数据库中建立一张表,存放sequence名称(name),sequence当前值(current_value),步长(increment int类型每次读取多少个sequence)
mycat会预加载一部分号段到mycat的内存中,这样大部分读写序列都是在内存中完成的。
如果内存中的号段用完了,mycat会再向数据库要一次。
创建MYCAT_SEQUENCE表(dn1数据库中)
CREATE TABLE MYCAT_SEQUENCE (
NAME VARCHAR(50) NOT NULL,
current_value INT NOT NULL,
increment INT NOT NULL DEFAULT 100,
PRIMARY KEY(NAME)
) ENGINE=INNODB;
#插入了一个名为ORDER的SEQUENCE,当前值为400000,步长为100。
INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ORDER', 400000, 100);
SELECT * FROM MYCAT_SEQUENCE
TRUNCATE TABLE MYCAT_SEQUENCE(直接删除数据,不带回滚)
创建存储函数:必须在同一个数据库中创建(dn1数据库中)
DELIMITER $$
CREATE FUNCTION mycat_seq_currval(
seq_name VARCHAR(50)
) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
DECLARE retval VARCHAR(64);
SET retval="-999999999,null";
SELECT CONCAT(CAST(current_value AS CHAR),",",CAST(increment AS CHAR)) INTO retval
FROM MYCAT_SEQUENCE WHERE NAME = seq_name;
RETURN retval;
END $$
DELIMITER;
DELIMITER $$
CREATE FUNCTION mycat_seq_setval(
seq_name VARCHAR(50),
VALUE INTEGER
) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE SET current_value = VALUE WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER;
DELIMITER $$
CREATE FUNCTION mycat_seq_nextval(
seq_name VARCHAR(50)
) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE SET current_value = current_value + increment WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER;
修改mycat的sequence_db_conf.properties配置(配置数据库的节点,即全局序列储存在哪个数据库):
ORDER=dn1:意思是ORDER(必须大写)这个序列储存在dn1数据库节点上
修改mycat的server.xml配置(将全局序列改为数据库方式):
<property name="sequnceHandlerType">1</property>
插入语句:
insert into order(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDER,1000,101,102);
