mysql 高级第四天:
回顾:
- 索引的使用
a. 全值匹配我最爱
b. 最佳左前缀原则
c. 计算、函数导致索引失效
d. 范围条件右边的列索引失效
e. 不等于(!= 或者<>)索引失效
f. is not null无法使用索引,is null可使用索引
g. like以通配符%开头索引失效
h. 类型转换导致索引失效
2. 关联优化:
left join
在从表{被驱动表}建立索引!
inner join
mysql 优化器会自动识别谁是被驱动表!
3. 排序、分组优化
总结:
my.cnf 的配置文件中 mysql5.7 使用的是单路
提高order by 的排序速度:
增大sort_buffer_size参数的设置 1M-8M
增大max_length_for_sort_data参数的设置 1024-8192字节
减少select 后面的查询的字段。 禁止使用select *
4. 如果有子查询和left Join
优先选择 left Join
NOT IN --> LEFT JOIN xxx ON xx WHERE xx IS NULL
5. 索引覆盖
见图:
6. 如何优化sql 语句:
步骤:
看where 后面的条件; 尽量添加索引
看on 条件 在被驱动表建立索引,尽量少使用子查询尽量使用左关联
group by order by 尽量使用索引! 细节问题:如果在where出现范围的话,与排序字段有冲突的情况下!
参考索引使用进行修改!
7. 慢查询了解
重点:
- 完成mysql 高级讲解
2. 讲mycat
1. 视图;
a. 有sql 语句封装的虚拟表
b. 提高复用性
c. CREATE [OR REPLACE] VIEW <view_name>
AS
<SELECT 语句>;
OR REPLACE --表示替换以有的视图
2. 主从复制;
基本原理:
io线程从主服务器读取二进制日志文件,sql 线程读取中继日志文件,并执行。保证主服务器与从服务器的数据一致性!
步骤:
1. 需要一个主机,一个从机!
我拷贝的!
修改ip 地址!
LINUX 有自己的uuid 唯一标识! 如果我们是拷贝的!那么这个uuid 是不会发生变化的!
一台:166 从
一台:168 主
2. 在主机添加配置数据
3. 从机添加配置数据
4. 重启服务器并关闭防火墙
5. 在主机上建立帐户并授权slave并记录当前文件名并且记录位置!
6. 在Linux从机上配置需要复制的主机
CHANGE MASTER TO MASTER_HOST='主机ip地址',
MASTER_USER='X',MASTER_PASSWORD='X',
MASTER_LOG_FILE='mysqlbin.具体数字',MASTER_LOG_POS=具体值;
修改之后:
CHANGE MASTER TO MASTER_HOST='192.168.200.168',
MASTER_USER='slave',MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysqlbin.000001',MASTER_LOG_POS=590;
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs;
these UUIDs must be different for replication to work.
如果出现这个错误,需要改一下uuid 值即可!
因为修改了uuid,所以我们mysql 主机的文件名称可能发生变化!
注意文件名称:课件有坑,大家下载最新的课件!
start slave; 开启主从复制!
show slave status \G;
主要看IO,SQL 线程是否是yes!
stop slave; 停止主从复制!
7. 测试
3. mycat:
数据库中间件
mycat:读写分离,数据分片,整合多个数据源
原理:拦截sql 语句,进行分析!
4. mycat 安装:
168:主 mycat
主要配置文件:
①schema.xml:定义逻辑库,表、分片节点等内容
②rule.xml:定义分片规则
③server.xml:定义用户以及系统相关变量,如端口等
配置:
server.xml:
schema.xml
启动mycat:
./mycat console : 推荐这种方式启动
登录mycat!
管理者:mysql -umycat -p123456 -P 9066 -h 192.168.200.168
服务器:mysql -umycat -p123456 -P 8066 -h 192.168.200.168
<property name="serverPort">8066</property> <property name="managerPort">9066</property>
5. mycat 进行读写分离
a. 搭建好主从!
b. 修改配置文件!
6. mycat 垂直拆分:
垂直 分库 ,水平 分表!
a. 先停止主从复制,停止mycat!
b. 修改配置文件!
schema.xml
c. 在两个节点上创建两个数据库
dn1 :168
dn2 : 166
CREATE DATABASE orders;
在dn1 上创建:
#订单表 rows:600万
CREATE TABLE orders(
id INT AUTO_INCREMENT,
order_type INT,
customer_id INT,
amount DECIMAL(10,2),
PRIMARY KEY(id)
);
#订单详细表 rows:600万
CREATE TABLE orders_detail(
id INT AUTO_INCREMENT,
detail VARCHAR(2000),
order_id INT,
PRIMARY KEY(id)
);
#订单状态字典表 rows:20条
CREATE TABLE dict_order_type(
id INT AUTO_INCREMENT,
order_type VARCHAR(200),
PRIMARY KEY(id)
);
在dn2 上创建:
CREATE TABLE customer(
id INT AUTO_INCREMENT,
NAME VARCHAR(200),
PRIMARY KEY(id)
);
d. 启动mycat!
dn1,dn2 都在mycat中配置好!
7. 水平分表:
分表:按照某个字段的某种规则来分散到多个库之中!
讨论,研究一下分表的规则!
修改配置文件:
①schema.xml:定义逻辑库,表、分片节点等内容
②rule.xml:定义分片规则
保证两个节点中都有表{orders}
重启:mycat
使用mycat 进行链接 在mycat 的客户端中进行插入数据!
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(1,101,100,100100);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020);
总结:
垂直分库:
1. 在配置文件中进行配置:
2. 在两个节点上创建两个数据库
3. 相关的表在一个数据库中。没用相关的表在另一个库中!
水平分表:
1. 找到分表的规则! 客户Id
2. 在每个节点上 创建对应的表
3. 修改配置文件 ①schema.xml ②rule.xml
E-R 表操作:
可以使用join 关联!
1. 配置schema.xml
2. 每个节点上都应该有对应的表
3. 重启mycat,插入数据,测试!
全局表:
字典表: 有可能跟客户表有关系,也有可能跟订单表有关系! 称之为全局表!
总结:
1. 在每个节点上都应该有这个表的存储!
2. 每个节点中的数据都应该保持一致!
mycat 要想做关联查询: 要么E-R 表操作!要么就做全局表! 通过全局表+基于 E-R 关系的分片策略
全局序列:
建议使用数据库!
总结:
1. mysql 视图;
2. mysql 主从复制
3. 会使用mycat 做读写分离
4. mycat 垂直分库,水平分表:
5. E-R表,全局表
6. 使用数据方式生成全局序列!