MySQL详解（五）——高级 3.0

最新推荐文章于 2024-01-29 21:41:00 发布

wzq_55552

最新推荐文章于 2024-01-29 21:41:00 发布

阅读量335

点赞数 1

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

本文链接：https://blog.csdn.net/qq_43409401/article/details/128690837

版权

mysql 同时被 2 个专栏收录

6 篇文章 2 订阅

订阅专栏

数据库

1 篇文章 1 订阅

订阅专栏

查询截取分析

慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。

由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

开启

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件（IO）

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启

SHOW VARIABLES LIKE ‘%slow_query_log%’;

开启了慢查询日志只对当前数据库生效，set global slow_query_log=1;

如果MySQL重启后则会失效。

如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）

修改my.cnf文件，[mysqld]下增加或修改参数
slow_query_log 和slow_query_log_file后，然后重启MySQL服务器。也即将如下两行配置进my.cnf文件：

slow_query_log =1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log

关于慢查询的参数slow_query_log_file ，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log（如果没有指定参数slow_query_log_file的话）

参数设置

那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？

这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，命令：SHOW VARIABLES LIKE ‘long_query_time%’;

在这里插入图片描述

可以使用命令修改，也可以在my.cnf参数里面修改。

假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，
在mysql源码里是判断大于long_query_time，而非大于等于。

#查看当前多少秒算慢
SHOW VARIABLES LIKE 'long_query_time%';

#设置慢的阙值时间,修改为阙值>1秒钟的就是慢sql
set  long_query_time=1

实验一条慢sql

在这里插入图片描述

跟踪日志信息

在这里插入图片描述

查询当前系统中有多少条慢查询记录：

show global status like ‘%Slow_queries%’;

在这里插入图片描述

配置方式设置：

my.cnf
【mysqld】下配置：

slow_query_log=1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3
log_output=FILE

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。

#mysqldumpslow指令功能
mysqldumpslow --help

#c是次数，-t指前3
mysqldumpslow -s c -t 3 -a xxx-slow.log
#t是时间
mysqldumpslow -s t -t 3 -a xxx-slow.log

找运维人员开启慢查询日志，然后过一段时间通过mysqldumpslow工具查询最慢，查询次数最多的一些sql，进行explain优化，建立索引。

SHOW PROCESSLIST

能干什么：查询所有用户正在干什么

kill [id] 杀死正在使用的用户

可解决死锁问题

视图

将一段查询sql封装为一个虚拟的表。

这个虚拟表只保存了sql逻辑，不会保存任何查询结果。

作用：

1、封装复杂sql语句，提高复用性

2、逻辑放在数据库上面，更新不需要改发布程序，改视图sql就行，面对频繁的需求变更更灵活

适用场景：

1、很多地方可以共用的一组查询结果

2、报表

语法：

#创建
CREATE VIEW view_name 
AS
SELECT column_name(s)
FROM table_name
WHERE condition

#查询
select * from view_name 
 
#更新
CREATE OR REPLACE VIEW view_name 
AS
SELECT column_name(s)
FROM table_name
WHERE condition

mysql的视图中不允许有from 后面的子查询（5.5版本才会），但oracle可以。

主从复制

slave会从master读取binlog来进行数据同步，最大问题就是从机读取binlog的sql进行执行会有延时，数据会不一致，slave机用来读，master机用来写，Mysql主从复制业务开始最好插入后不要立马查询相关的。读写分离。

master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events，日志存放增删改的sql，类似redis的AOF。
slave将master的binary log events拷贝到它的中继日志（relay log）
slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的

在这里插入图片描述

redis主机压数据修改操作，立马同步到从机。CP

基本原则

每个slave只有一个master
每个slave只能有一个唯一的服务器ID(service-id)
每个master可以有多个salve

一主一从常见配置

一、主机配置：

mysql版本一致且后台以服务运行
主从都配置在[mysqld]结点下，都是小写，主机修改my.ini配置文件

在这里插入图片描述

主服务器唯一ID，server-id=1
启用二进制日志：mysqlbin是二进制文件名，log-bin=自己本地的路径/data/mysqlbin
设置不要复制的数据库：binlog-ignore-db=mysql
设置需要复制的数据库：binlog-do-db=需要复制的主数据库名字
设置logbin格式：binlog_format=STATEMENT（默认）

statemet：日志记录sql，使用类似日期函数从机可能存在数据不一致问题

row：日志记录每行数据的变化，不会出现数据不一致问题，但是数据量多

mixed：判断sql是否含有函数，有则用row模式，没有则用statemet模式，sql有系统变量的话，也会出现主从复制数据不一致问题

二、从机配置：

mysql主从复制起始时，从机不继承主机数据
从机配置文件修改my.cnf的[mysqld]栏位下

server-id = 2，relay-log=mysql-relay（中继日志）

三、

因修改过配置文件，请主机+从机都重启后台mysql服务

linux重启：systemctl restart mysqld
主机从机都关闭防火墙，windows手动关闭，关闭虚拟机linux防火墙：systemctl stop firewalld

四、在Windows主机上建立帐户并授权slave

#所有权限
GRANT REPLICATION SLAVE ON *.* TO 'zhangsan'@'从机器数据库IP' IDENTIFIED BY '123456';

查询master的状态：show master status;

在这里插入图片描述

记录下File和Position的值

执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

五、在Linux从机上配置需要复制的主机

CHANGE MASTER TO MASTER_HOST='192.168.124.3',
MASTER_USER='zhangsan',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysqlbin.具体数字',MASTER_LOG_POS=具体值;

启动从服务器复制功能：start slave;

show slave status\G;

下面两个参数都是Yes，则说明主从配置成功！

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

在这里插入图片描述

主机新建库、新建表、insert记录，从机复制

如何停止从服务复制功能：stop slave;

如何重新配置主从：stop slave; reset master;

Mycat分库分表

数据中间件

功能：

1、读写分离

2、数据分片：垂直拆分、水平拆分、垂直+水平拆分

3、多数据源整合

在这里插入图片描述

Mycat 的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的 SQL 语句，首先对 SQL 语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此 SQL 发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。

schema.xml

负载均衡类型，目前的取值有4 种：

balance=“0”, 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
balance=“1”，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。
balance=“2”，所有读操作都随机的在 writeHost、readhost 上分发。
balance=“3”，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力

怎么分库，垂直拆分？

#客户表  rows:20万 
CREATE TABLE customer(
    id INT AUTO_INCREMENT,
    NAME VARCHAR(200),
    PRIMARY KEY(id)
);
 
 
#订单表   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)
);
 
 
# 不能跨库join，orders、orders_detail和dict_order_type不能分库，其它表可以分到其他节点的库
select o.*,od.detail,d.order_type 
from orders  o 
inner join orders_detail  od on o.id =od.order_id  
inner join dict_order_type d on o.order_type=d.id  
where o.customer_id=xxxx