MySQL

MySQL相关知识02

MySQL的主从、备份、集群等

Posted by ZBX on April 19, 2020

主从复制原理、作用和实现

主从复制的类型 ： 基于语句的复制、基于行的复制。MySQL默认使用基于语句的复制，当基于语句的复制会引发问题的时候就会使用基于行的复制，MySQL会自动进行选择。

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

  • binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
  • I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
  • SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

就是从库同步主库数据的过程是串行化的，也就是说主库上并行的操作，在从库上会串行执行。

问题： 主库突然宕机，然后恰好数据还没同步到从库，那么有些数据可能在从库上是没有的，有些数据可能就丢失了。

解决方案：

  • 半同步复制 ：主库写入binlog日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的relay log之后，接着会返回一个ack给主库，主库接收到至少一个从库的ack之后才会认为写操作完成了。
  • 并行复制：从库开启多个线程，并行读取relay log中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。

总体概览

  1. Connectors 指的是不同语言中与SQL的交互

  2. Management Serveices & Utilities： 系统管理和控制工具

  3. Connection Pool 连接池

    管理缓冲用户连接，线程处理需要缓存的需求。负责监听对MySQL Server的各种请求，接收连接请求，转发所有连接到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。

  4. SQL Interface: SQL接口

    接受用户的SQL命令，并且返回用户需要查询的结果。

  5. Parser: 解析器

    SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。

  6. Optimizer: 查询优化器

    SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求的 query（sql语句） ，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果

  7. Cache和Buffer： 查询缓存

    是将客户端提交 给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值 做一个对应。

  8. 存储引擎接口

    1. 实现其底层数据存储引擎的插件式管理

SQL语句执行得很慢的原因

偶尔很慢的原因

1、在刷新脏页

​ 更新字段时不会马上同步持久化，而是把更新的记录会写入到redo log中。再等到空闲时由redo log把最新的数据同步到磁盘中。

  • redo log写满了
  • 内存不够了

2、获取不到锁（表锁或者行锁）

​ 如果要判断是否真的在等待锁，我们可以用 show processlist这个命令来查看当前的状态。

一直很慢的原因

1、Explain查看下

  • 没有则建立索引；
  • 是否查询中使用到了索引，利用覆盖索引和最左匹配原则，查询列和索引列一致；
  • 是否在索引列上做了任何计算；
  • 使用了模糊查询中的左模糊和全模糊；
  • != 、<>或者is not null
  • 字符串不加单引号索引失效
  • 少用or,用它连接时会索引失效

Explain性能分析

使用Explain可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。分析查询语句或是表结构的性能瓶颈。

  • id ： 查询的序列号
    • id相同，执行顺序由上至下
    • id不同，id越大优先级越高
  • select_type
  • table
  • type
    • system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
  • possible_keys
  • key
  • key_len
  • ref
  • rows
  • Extra

查询优化

  1. 永远小表驱动大表
  2. ORDER BY关键字优化
    1. ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序
    2. 尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
  3. GROUP BY关键字优化
    1. groupby实质是先排序后进行分组，遵照索引建的最佳左前缀

往表里插入1000W数据

建表

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deptName` varchar(30) DEFAULT NULL,
  `address` varchar(40) DEFAULT NULL,
  `ceo` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `emp`;
create table `emp` (
  `id` int(11) not null auto_increment,
  `empno` int not null,
  `name` VARCHAR(20) default null,
  `age` int(3) default null,
  `deptId` int(11) default null,
  primary key(`id`)
)engine=InnoDB auto_increment= 1 default charset = utf8;

设置参数log_bin_trust_function_creators

show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators = 1;

创建函数保证每条数据都不同

  1. 随机产生字符串
Delimiter $$
create function rand_string(n INT) returns varchar(255)
begin
    declare chars_str varchar(100) default
    'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    declare return_str varchar(255) default '';
    declare i INT default 0;
    while i < n do
        set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52),1));
        set i = i + 1;
    end while;
return return_str;
end $$
# 删除用 drop function
  1. 随机产生部门编号
delimiter $$
create function rand_num(from_num int, to_num int) returns int(11)
begin
	declare i int default 0;
	set i = floor(from_num + RAND() * (to_num - from_num + 1));
	return i;
end $$

创建存储过程

  1. 创建往emp表中插入数据的存储过程

    delimiter $$
create procedure insert_emp(start int, max_num int)
begin
	declare i int default 0;
	set autocommit = 0;
	repeat
	set i = i + 1;
	insert into emp (empno, name, age, deptid) values ((start+i), rand_string(6), rand_num(30,50), rand_num(1,10000) );
	until i = max_num
	end repeat;
	commit;
end $$

  2. 创建往dept表中插入数据的存储过程

delimiter $$
create procedure `insert_dept`(max_num int)
begin
	declare i int default 0;
	set autocommit = 0;
	repeat
	set i = i +1;
	insert into dept(deptname, address,ceo)  values(rand_string(8), rand_string(10),rand_num(1,500000));
	until i = max_num
	end repeat;
	commit;
end $$

调用存储过程

delimiter ;
call insert_dept(10000);
call insert_emp(100000,500000);

批量删除某个表上的所有索引

#查询索引
select index_name from information_schema.STATISTICS where table_name = 't_emp' and table_schema='mydb' and index_name <> 'PRIMARY' and seq_in_index = 1;

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$$
FEATURED TAGS
Ethereum EVM BlockChain Security 翻译 SGX Smart Contract MySQL Redis Java 面试 Spring
FRIENDS