脚本宝典收集整理的这篇文章主要介绍了【MySQL】MySQL(四)存储引擎、索引、锁、集群,脚本宝典觉得挺不错的,现在分享给大家,也给大家做个参考。
体系结构的概念
MySQL的体系结构
引擎的概念
MySQL存储引擎的概念
MySQL支持的存储引擎
特性 | MyISAM | InnoDB | MEMORY |
---|---|---|---|
存储限制 | 有(平台对文件系统大小的限制) | 64TB | 有(平台的内存限制) |
事务安全 | 不支持 | 支持 | 不支持 |
锁机制 | 表锁 | 表锁/行锁 | 表锁 |
B+Tree索引 | 支持 | 支持 | 支持 |
哈希索引 | 不支持 | 不支持 | 支持 |
全文索引 | 支持 | 支持 | 不支持 |
集群索引 | 不支持 | 支持 | 不支持 |
数据索引 | 不支持 | 支持 | 支持 |
数据缓存 | 不支持 | 支持 | N/A |
索引缓存 | 支持 | 支持 | N/A |
数据可压缩 | 支持 | 不支持 | 不支持 |
空间使用 | 低 | 高 | N/A |
内存使用 | 低 | 高 | 中等 |
批量插入速度 | 高 | 低 | 高 |
外键 | 不支持 | 支持 | 不支持 |
-- 标准语法
SHOW ENginES;
-- 查询数据库支持的存储引擎
SHOW ENGINES;
@H_406_344@
-- 表含义:
- support : 指服务器是否支持该存储引擎
- transactions : 指存储引擎是否支持事务
- XA : 指存储引擎是否支持分布式事务处理
- Savepoints : 指存储引擎是否支持保存点
-- 标准语法
SHOW TABLE statUS From 数据库名称;
-- 查看db9数据库所有表的存储引擎
SHOW TABLE STATUS From db9;
-- 标准语法
SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据表名称';
-- 查看db9数据库中stu_score表的存储引擎
SHOW TABLE STATUS FROM db9 WHERE NAME = 'stu_score';
-- 标准语法
CREATE TABLE 表名(
列名,数据类型,
...
)ENGINE = 引擎名称;
-- 创建db11数据库
CREATE DATABASE db11;
-- 使用db11数据库
USE db11;
-- 创建engine_test表,指定存储引擎为MyISAM
CREATE TABLE engine_test(
id INT Primary KEY AUTO_INCREMENT,
NAME VARCHAR(10)
)ENGINE = MYISAM;
-- 查询engine_test表的引擎
SHOW TABLE STATUS FROM db11 WHERE NAME = 'engine_test';
-- 标准语法
ALTER TABLE 表名 ENGINE = 引擎名称;
-- 修改engine_test表的引擎为InnoDB
ALTER TABLE engine_test ENGINE = INNODB;
-- 查询engine_test表的引擎
SHOW TABLE STATUS FROM db11 WHERE NAME = 'engine_test';
-- 创建db12数据库
CREATE DATABASE db12;
-- 使用db12数据库
USE db12;
-- 创建student表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
age INT,
score INT
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,98),(NULL,'李四',24,95),
(NULL,'王五',25,96),(NULL,'赵六',26,94),(NULL,'周七',27,99);
-- 标准语法
CREATE [unique|FULLTEXT] INDEX 索引名称
[USING 索引类型] -- 默认是B+TREE
ON 表名(列名...);
-- 为student表中姓名列创建一个普通索引
CREATE INDEX idx_name ON student(NAME);
-- 为student表中年龄列创建一个唯一索引
CREATE UNIQUE INDEX idx_age ON student(age);
-- 标准语法
SHOW INDEX FROM 表名;
-- 查看student表中的索引
SHOW INDEX FROM student;
-- 普通索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名);
-- 组合索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);
-- 主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(主键列名);
-- 外键索引(添加外键约束,就是外键索引)
ALTER TABLE 表名 ADD CONSTRaiNT 外键名 FOReiGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
-- 唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);
-- 全文索引(mysql只支持文本类型)
ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);
-- 为student表中name列添加全文索引
ALTER TABLE student ADD FULLTEXT idx_fulltext_name(name);
-- 查看student表中的索引
SHOW INDEX FROM student;
-- 标准语法
DROP INDEX 索引名称 ON 表名;
-- 删除student表中的idx_score索引
DROP INDEX idx_score ON student;
-- 查看student表中的索引
SHOW INDEX FROM student;
-- 创建product商品表
CREATE TABLE product(
id INT PRIMARY KEY AUTO_INCREMENT, -- 商品id
NAME VARCHAR(10), -- 商品名称
price INT -- 商品价格
);
-- 定义存储函数,生成长度为10的随机字符串并返回
DELIMITER $
CREATE FUNCTION rand_string()
RETURNS VARCHAR(255)
BEGIN
DECLARE Big_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ';
DECLARE small_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 1;
WHILE i <= 10 DO
SET small_str =CONCAT(small_str,SubSTRING(big_str,FLOOR(1+RAND()*52),1));
SET i=i+1;
END WHILE;
RETURN small_str;
END$
DELIMITER ;
-- 定义存储过程,添加100万条数据到product表中
DELIMITER $
CREATE PROCEDURE pro_test()
BEGIN
DECLARE num INT DEFAULT 1;
WHILE num <= 1000000 DO
INSERT INTO product VALUES (NULL,rand_string(),num);
SET num = num + 1;
END WHILE;
END$
DELIMITER ;
-- 调用存储过程
CALL pro_test();
-- 查询总记录条数
SELECT COUNT(*) FROM product;
-- 查询product表的索引
SHOW INDEX FROM product;
-- 查询name为OkIKDLVwtG的数据 (0.049)
SELECT * FROM product WHERE NAME='OkIKDLVwtG';
-- 通过id列查询OkIKDLVwtG的数据 (1毫秒)
SELECT * FROM product WHERE id=999998;
-- 为name列添加索引
ALTER TABLE product ADD INDEX idx_name(NAME);
-- 查询name为OkIKDLVwtG的数据 (0.001)
SELECT * FROM product WHERE NAME='OkIKDLVwtG';
/*
范围查询
*/
-- 查询价格为800~1000之间的所有数据 (0.052)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000;
/*
排序查询
*/
-- 查询价格为800~1000之间的所有数据,降序排列 (0.083)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000 ORDER BY price DESC;
-- 为price列添加索引
ALTER TABLE product ADD INDEX idx_price(price);
-- 查询价格为800~1000之间的所有数据 (0.011)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000;
-- 查询价格为800~1000之间的所有数据,降序排列 (0.001)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000 ORDER BY price DESC;
查找顺序:
模拟查找15的过程 :
1.根节点找到磁盘块1,读入内存。【磁盘I/O操作第1次】
比较关键字15在区间(<17),找到磁盘块1的指针P1。
2.P1指针找到磁盘块2,读入内存。【磁盘I/O操作第2次】
比较关键字15在区间(>12),找到磁盘块2的指针P3。
3.P3指针找到磁盘块7,读入内存。【磁盘I/O操作第3次】
在磁盘块7中找到关键字15。
-- 分析上面过程,发现需要3次磁盘I/O操作,和3次内存查找操作。
-- 由于内存中的关键字是一个有序表结构,可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个BTree查找效率的决定因素。BTree使用较少的节点个数,使每次磁盘I/O取到内存的数据都发挥了作用,从而提高了查询效率。
通常在B+Tree上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对B+Tree进行两种查找运算:
实际情况中每个节点可能不能填充满,因此在数据库中,B+Tree的高度一般都在24层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的,也就是说查找某一键值的行记录时最多只需要13次磁盘I/O操作。
索引的设计可以遵循一些已有的原则,创建索引的时候请尽量考虑符合这些原则,便于提升索引的使用效率,更高效的使用索引。
在mysql建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配, 对列name列、address和列phone列建一个联合索引
ALTER TABLE user ADD INDEX index_three(name,address,phone);
联合索引index_three实际建立了(name)、(name,address)、(name,address,phone)三个索引。所以下面的三个SQL语句都可以命中索引。
SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三';
SELECT * FROM user WHERE name = '张三' AND address = '北京';
SELECT * FROM user WHERE name = '张三';
上面三个查询语句执行时会依照最左前缀匹配原则,检索时分别会使用索引
(name,address,phone)
(name,address)
(name)
进行数据匹配。
索引的字段可以是任意顺序的,如:
-- 优化器会帮助我们调整顺序,下面的SQL语句都可以命中索引
SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三';
Mysql的优化器会帮助我们调整where条件中的顺序,以匹配我们建立的索引。
联合索引中最左边的列不包含在条件查询中,所以根据上面的原则,下面的SQL语句就不会命中索引。
-- 联合索引中最左边的列不包含在条件查询中,下面的SQL语句就不会命中索引
SELECT * FROM user WHERE address = '北京' AND phone = '12345';
之前我们学习过多线程,多线程当中如果想保证数据的准确性是如何实现的呢?没错,通过同步实现。同步就相当于是加锁。加了锁以后有什么好处呢?当一个线程真正在操作数据的时候,其他线程只能等待。当一个线程执行完毕后,释放锁。其他线程才能进行操作!
那么我们的MySQL数据库中的锁的功能也是类似的。在我们学习事务的时候,讲解过事务的隔离性,可能会出现脏读、不可重复读、幻读的问题,当时我们的解决方式是通过修改事务的隔离级别来控制,但是数据库的隔离级别呢我们并不推荐修改。所以,锁的作用也可以解决掉之前的问题!
锁机制 : 数据库为了保证数据的一致性,而使用各种共享的资源在被并发访问时变得有序所设计的一种规则。
举例,在电商网站购买商品时,商品表中只存有1个商品,而此时又有两个人同时购买,那么谁能买到就是一个关键的问题。
以上过程中,使用锁可以对商品数量数据信息进行保护,实现隔离,即只允许第一位用户完成整套购买流程,而其他用户只能等待,这样就解决了并发中的矛盾问题。
在数据库中,数据是一种供许多用户共享访问的资源,如何保证数据并发访问的一致性、有效性,是所有数据库必须解决的一个问题,MySQL由于自身架构的特点,在不同的存储引擎中,都设计了面对特定场景的锁定机制,所以引擎的差别,导致锁机制也是有很大差别的。
存储引擎 | 表级锁 | 行级锁 | 页级锁 |
---|---|---|---|
MyISAM | 支持 | 不支持 | 不支持 |
InnoDB | 支持 | 支持 | 不支持 |
MEMORY | 支持 | 不支持 | 不支持 |
BDB | 支持 | 不支持 | 支持 |
-- 创建db13数据库
CREATE DATABASE db13;
-- 使用db13数据库
USE db13;
-- 创建student表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
age INT,
score INT
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,99),(NULL,'李四',24,95),
(NULL,'王五',25,98),(NULL,'赵六',26,97);
-- 标准语法
SELECT语句 LOCK IN SHARE MODE;
-- 窗口1
/*
共享锁:数据可以被多个事务查询,但是不能修改
*/
-- 开启事务
START TRANSACTION;
-- 查询id为1的数据记录。加入共享锁
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
-- 查询分数为99分的数据记录。加入共享锁
SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE;
-- 提交事务
COMMIT;
-- 窗口2
-- 开启事务
START TRANSACTION;
-- 查询id为1的数据记录(普通查询,可以查询)
SELECT * FROM student WHERE id=1;
-- 查询id为1的数据记录,并加入共享锁(可以查询。共享锁和共享锁兼容)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
-- 修改id为1的姓名为张三三(不能修改,会出现锁的情况。只有窗口1提交事务后,才能修改成功)
UPDATE student SET NAME='张三三' WHERE id = 1;
-- 修改id为2的姓名为李四四(修改成功,InnoDB引擎默认是行锁)
UPDATE student SET NAME='李四四' WHERE id = 2;
-- 修改id为3的姓名为王五五(注意:InnoDB引擎如果不采用带索引的列。则会提升为表锁)
UPDATE student SET NAME='王五五' WHERE id = 3;
-- 提交事务
COMMIT;
-- 标准语法
SELECT语句 FOR UPDATE;
-- 窗口1
/*
排他锁:加锁的数据,不能被其他事务加锁查询或修改
*/
-- 开启事务
START TRANSACTION;
-- 查询id为1的数据记录,并加入排他锁
SELECT * FROM student WHERE id=1 FOR UPDATE;
-- 提交事务
COMMIT;
-- 窗口2
-- 开启事务
START TRANSACTION;
-- 查询id为1的数据记录(普通查询没问题)
SELECT * FROM student WHERE id=1;
-- 查询id为1的数据记录,并加入共享锁(不能查询。因为排他锁不能和其他锁共存)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
-- 查询id为1的数据记录,并加入排他锁(不能查询。因为排他锁不能和其他锁共存)
SELECT * FROM student WHERE id=1 FOR UPDATE;
-- 修改id为1的姓名为张三(不能修改,会出现锁的情况。只有窗口1提交事务后,才能修改成功)
UPDATE student SET NAME='张三' WHERE id=1;
-- 提交事务
COMMIT;
-- 创建product表
CREATE TABLE product(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
price INT
)ENGINE = MYISAM; -- 指定存储引擎为MyISAM
-- 添加数据
INSERT INTO product VALUES (NULL,'华为手机',4999),(NULL,'小米手机',2999),
(NULL,'苹果',8999),(NULL,'中兴',1999);
-- 标准语法
-- 加锁
LOCK TABLE 表名 READ;
-- 解锁(将当前会话所有的表进行解锁)
UNLOCK TABLES;
-- 窗口1
/*
读锁:所有连接只能读取数据,不能修改
*/
-- 为product表加入读锁
LOCK TABLE product READ;
-- 查询product表(查询成功)
SELECT * FROM product;
-- 修改华为手机的价格为5999(修改失败)
UPDATE product SET price=5999 WHERE id=1;
-- 解锁
UNLOCK TABLES;
-- 窗口2
-- 查询product表(查询成功)
SELECT * FROM product;
-- 修改华为手机的价格为5999(不能修改,窗口1解锁后才能修改成功)
UPDATE product SET price=5999 WHERE id=1;
-- 标准语法
-- 加锁
LOCK TABLE 表名 WRITE;
-- 解锁(将当前会话所有的表进行解锁)
UNLOCK TABLES;
-- 窗口1
/*
写锁:其他连接不能查询和修改数据
*/
-- 为product表添加写锁
LOCK TABLE product WRITE;
-- 查询product表(查询成功)
SELECT * FROM product;
-- 修改小米手机的金额为3999(修改成功)
UPDATE product SET price=3999 WHERE id=2;
-- 解锁
UNLOCK TABLES;
-- 窗口2
-- 查询product表(不能查询。只有窗口1解锁后才能查询成功)
SELECT * FROM product;
-- 修改小米手机的金额为2999(不能修改。只有窗口1解锁后才能修改成功)
UPDATE product SET price=2999 WHERE id=2;
悲观锁的概念
乐观锁的概念
悲观锁和乐观锁使用前提
乐观锁的实现方式
版本号
-- 创建city表
CREATE TABLE city(
id INT PRIMARY KEY AUTO_INCREMENT, -- 城市id
NAME VARCHAR(20), -- 城市名称
VERSION INT -- 版本号
);
-- 添加数据
INSERT INTO city VALUES (NULL,'北京',1),(NULL,'上海',1),(NULL,'广州',1),(NULL,'深圳',1);
-- 修改北京为北京市
-- 1.查询北京的version
SELECT VERSION FROM city WHERE NAME='北京';
-- 2.修改北京为北京市,版本号+1。并对比版本号
UPDATE city SET NAME='北京市',VERSION=VERSION+1 WHERE NAME='北京' AND VERSION=1;
时间戳
表锁和行锁
InnoDB锁优化建议
尽量通过带索引的列来完成数据查询,从而避免InnoDB无法加行锁而升级为表锁。
合理设计索引,索引要尽可能准确,尽可能的缩小锁定范围,避免造成不必要的锁定。
尽可能减少基于范围的数据检索过滤条件。
尽量控制事务的大小,减少锁定的资源量和锁定时间长度。
在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率。
对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁的产生。
官网:http://www.mycat.io/
下载地址 : http://dl.mycat.io/
选择1.6.7.1的版本,下载到D盘,安装包入下图:
上传:使用SecureCRT的SFTP命令,将文件发送到Linux虚拟机root目录下:
sftp> put D:Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz
解压:解压mycat.tar.gz并查看
tar -zxvf mycat.tar.gz
cd mycat
ll
授权:设置mycat权限
chmod -R 777 mycat
环境变量:配置环境变量
vi /etc/profile
// 添加
export MYCAT_HOME=/root/mycat
// 使环境变量生效
source /etc/profile
启动mycat
// 进入bin目录
[root@localhost]# cd /root/mycat/bin
// 执行启动命令
[root@localhost bin]# ./mycat start
查看:检测端口监听状况,Mycat的端口号是8066
[root@localhost bin]# netstat -ant|grep 8066
连接:使用sqlyog连接Mycat
连接后显示:
修改配置网卡
// 重启网络
service network restart
//查看ip地址
ip addr
修改mysql配置文件,更改uuid
// 编辑配置文件
vi /var/lib/mysql/auto.cnf
// 将server-uuid更改
启动MySQL并查看
//将两台服务器的防火墙关闭
Systemctl stop firewalld
//启动两台服务器的mysql
service mysqld restart
//启动两台服务器的mycat
cd /root/mycat/bin
./mycat restart
//查看监听端口
netstat -ant|grep 3306
netstat -ant|grep 8066
//使用sqlyog测试连接
主从复制的概念
主服务器的配置
// 编辑mysql配置文件
vi /etc/my.cnf
//在[mysqld]下面加上:
log-bin=mysql-bin # 开启复制操作
server-id=1 # master is 1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
// 登录mysql
mysql -u root -p
// 去除密码权限
SET GLOBAL validate_password_policy=0;
SET GLOBAL validate_password_length=1;
// 创建用户
CREATE USER 'user'@'%' IDENTIFIED BY 'gonghr';
// 授权
GRANT ALL ON *.* TO 'user'@'%';
// 重启mysql
service mysqld restart
// 登录mysql
mysql -u root -p
// 查看主服务器配置
show master status;
从服务器的配置
// 编辑mysql配置文件
vi /etc/my.cnf
// 在[mysqld]下面加上:
server-id=2
// 登录mysql
mysql -u root -p
// 执行
use mysql;
drop table slave_master_info;
drop table slave_relay_log_info;
drop table slave_worker_info;
drop table innodb_index_stats;
drop table innodb_table_stats;
source /usr/share/mysql/mysql_system_tables.sql;
// 重启mysql
service mysqld restart
// 重新登录mysql
mysql -u root -p
// 执行
change master to master_host='主服务器ip地址',master_port=3306,master_user='user',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=4642;
// 重启mysql
service mysqld restart
// 重新登录mysql
mysql -u root -p
// 开启从节点
start slave;
// 查询结果
show slave statusG;
//Slave_IO_Running和Slave_SQL_Running都为yes才表示同步成功。
测试
-- 主服务器创建db1数据库,从服务器会自动同步
CREATE DATABASE db1;
-- 从服务器创建db2数据库,主服务器不会自动同步
CREATE DATABASE db2;
启动失败的解决方案
启动失败:Slave_IO_Running为 NO
方法一:重置slave
slave stop;
reset slave;
start slave ;
方法二:重设同步日志文件及读取位置
slave stop;
change master to master_log_file=’mysql-bin.000001’, master_log_pos=1;
start slave ;
读写分离的概念
在主服务器上修改server.XMl
<user name="root" defaultAccount="true">
<proPErty name="password">123456</property>
<property name="schemas">HEIMADB</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>
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="HEIMADB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- 主服务器进行写操作 -->
<writeHost host="hostM1" url="localhost:3306" user="root"
password="123456">
<!-- 从服务器负责读操作 -->
<readHost host="hostS1" url="192.168.203.135:3306" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
配置详解
schema标签逻辑库的概念和mysql数据库中Datebase的概念相同,我们在查询这两个逻辑库中的表的时候,需要切换到该逻辑库下才可以查到所需要的表。
dataNode属性:该属性用于绑定逻辑库到某个具体的database上。
dataNode标签: dataNode标签定义了mycat中的数据节点,也就是数据分片。一个dataNode标签就是一个独立的数据分片。
name属性:定义数据节点的名字,这个名字需要是唯一的,我们需要在table标签上应用这个名字,来建立表与分片对应的关系。
dataHost属性:该属性用于定义该分片属于那个数据库实例,属性值是引用datahost标签定义的name属性。
database属性:该属性用于定义该分片属于那个具体数据库实例上的具体库,因为这里使用两个纬度来定义分片,就是:实例+具体的库。因为每个库上建立的表和表结构是一样的。所以这样做就可以轻松的对表进行水平拆分。
dataHost标签:该标签在mycat逻辑库中也是作为最底层的标签存在,直接定义了具体的数据库实例、读写分离配置和心跳语句。
balance属性: 负载均衡类型 balance=0: 不开启读写分离,所有读操作都发送到当前可用的writeHost上。 balance=1: 全部的readHost与Stand by writeHost都参与select语句的负载均衡 balance=2: 所有的读操作都随机在writeHost,readHost上分发。 balance=3: 所有的读请求都随机分配到writeHost对应的readHost上执行,writeHost不负担读压力。
switchType属性: -1:表示不自动切换。 1 :默认值,表示自动切换 2:表示基于MySQL主从同步状态决定是否切换,心跳语句: show slave status. 3:表示基于mysql galary cluster的切换机制,适合mycat1.4之上的版本,心跳语句show status like "%esrep%";
writeHost标签,readHost标签:这两个标签指定后端数据库的相关配置给mycat,用于实例化后端连接池。唯一不同的是,writeHost指定写实例、readHost指定读实例,组合这些读写实例来满足系统的要求。
测试
// 重启mycat
cd /root/mycat/bin
./mycat restart
// 查看端口监听
netstat -ant|grep 8066
-- 创建学生表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10)
);
-- 查询学生表
SELECT * FROM student;
-- 添加两条记录
INSERT INTO student VALUES (NULL,'张三'),(NULL,'李四');
-- 停止主从复制后,添加的数据只会保存到主服务器上。
INSERT INTO student VALUES (NULL,'王五');
-- 主服务器:查询学生表,可以看到数据
SELECT * FROM student;
-- 从服务器:查询学生表,可以看到数据(因为有主从复制)
SELECT * FROM student;
-- 从服务器:删除一条记录。(主服务器并没有删除,mycat中间件查询的结果是从服务器的数据)
DELETE FROM student WHERE id=2;
分库分表的概念
Mycat水平拆分
修改主服务器的server.xml
0:本地文件方式
在mycat/conf/sequence_conf.properties文件中: GLOBAL.MINDI=10000最小值 GLOBAL.MAXID=20000最大值,建议修改到9999999999
1:数据库方式
分库分表中保证全局主键自增唯一,但是需要执行mycat函数,配置sequence_db_conf.properties
2:时间戳方式
mycat实现的时间戳,建议varchar类型,要注意id的长度
<!-- 修改主键的方式 -->
<property name="sequnceHandlerType">0</property>
#default global sequence
GLOBAL.HISIDS= # 可以自定义关键字
GLOBAL.MINID=10001 # 最小值
GLOBAL.MAXID=20000 # 最大值
GLOBAL.CURID=10000
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="HEIMADB" checkSQLschema="false" sqlMaxLimit="100">
<table name="product" primaryKey="id" dataNode="dn1,dn2,dn3" rule="mod-long"/>
</schema>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataNode name="dn3" dataHost="localhost1" database="db3" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- write -->
<writeHost host="hostM1" url="localhost:3306" user="root"
password="123456">
<!-- read -->
<readHost host="hostS1" url="192.168.203.135:3306" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- 数据库的数量 -->
<property name="count">3</property>
</function>
测试
-- 创建product表
CREATE TABLE product(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
price INT
);
-- 添加6条数据
INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'苹果手机',6999);
INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'华为手机',5999);
INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'三星手机',4999);
INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'小米手机',3999);
INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'中兴手机',2999);
INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'OOPO手机',1999);
-- 查询product表
SELECT * FROM product;
-- 在不同数据库中查询product表
SELECT * FROM product;
-- 在不同数据库中查询product表
SELECT * FROM product;
Mycat垂直拆分
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="HEIMADB" checkSQLschema="false" sqlMaxLimit="100">
<table name="product" primaryKey="id" dataNode="dn1,dn2,dn3" rule="mod-long"/>
<!-- 动物类数据表 -->
<table name="dog" primaryKey="id" autoIncrement="true" dataNode="dn4" />
<table name="cat" primaryKey="id" autoIncrement="true" dataNode="dn4" />
<!-- 水果类数据表 -->
<table name="apple" primaryKey="id" autoIncrement="true" dataNode="dn5" />
<table name="banana" primaryKey="id" autoIncrement="true" dataNode="dn5" />
</schema>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataNode name="dn3" dataHost="localhost1" database="db3" />
<dataNode name="dn4" dataHost="localhost1" database="db4" />
<dataNode name="dn5" dataHost="localhost1" database="db5" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- write -->
<writeHost host="hostM1" url="localhost:3306" user="root"
password="123456">
<!-- read -->
<readHost host="hostS1" url="192.168.203.135:3306" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
测试
-- 创建dog表
CREATE TABLE dog(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO dog(id,NAME) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'哈士奇');
-- 查询dog表
SELECT * FROM dog;
-- 创建cat表
CREATE TABLE cat(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO cat(id,NAME) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'波斯猫');
-- 查询cat表
SELECT * FROM cat;
-- 创建apple表
CREATE TABLE apple(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO apple(id,NAME) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'红富士');
-- 查询apple表
SELECT * FROM apple;
-- 创建banana表
CREATE TABLE banana(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO banana(id,NAME) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'香蕉');
-- 查询banana表
SELECT * FROM banana;
-- 查询dog表
SELECT * FROM dog;
-- 查询cat表
SELECT * FROM cat;
-- 查询apple表
SELECT * FROM apple;
-- 查询banana表
SELECT * FROM banana;
-- 查询dog表
SELECT * FROM dog;
-- 查询cat表
SELECT * FROM cat;
-- 查询apple表
SELECT * FROM apple;
-- 查询banana表
SELECT * FROM banana;
以上是脚本宝典为你收集整理的【MySQL】MySQL(四)存储引擎、索引、锁、集群全部内容,希望文章能够帮你解决【MySQL】MySQL(四)存储引擎、索引、锁、集群所遇到的问题。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。