MySQL -- 内部临时表
UNIONUNION语义：取两个子查询结果的并集，重复的行只保留一行 表初始化1234567891011121314CREATE TABLE t1(id INT PRIMARY KEY, a INT, b INT, INDEX(a));DELIMITER ;;CREATE PROCEDURE idata()BEGIN DECLARE i INT; SET i=1; WHILE (i<= 1000) DO INSERT INTO t1 VALUES (i,i,i); SET i=i+1; END WHILE;END;;DELIMITER ;CALL idata(); 执行语句12345678910(SELECT 1000 AS f) UNION (SELECT id FROM t1 ORDER BY id DESC LIMIT 2);mysql> EXPLAIN (SELECT 1000 AS f) UNION (SELECT id FROM t1 ORDER BY id DESC LIMIT 2);+----+------------...
MySQL -- 用户临时表
临时表 VS 内存表 内存表，指的是使用Memory引擎的表，建表语法：CREATE TABLE ... ENGINE=Memory 所有数据都保存在内存中，系统重启时被清空，但表结构还在 临时表，可以使用各种引擎 如果使用的是InnoDB或者MyISAM引擎，数据需要写到磁盘上 当然也可以使用Memory引擎 特征 session A session B CREATE TEMPORARY TABLE t(c int) ENGINE=MyISAM;（创建临时表） SHOW CREATE TABLE t;(Table ‘test.t’ doesn’t exist) CREATE TABLE t(id INT PRIMARY KEY) ENGINE=InnoDB;（创建普通表） SHOW CREATE TABLE t;（显示临时表） SHOW TABLES;（显示普通表） INSERT INTO t VALUES (1); SELECT * FROM t;(返回1) SELECT * FROM t;(Empty set) ...
MySQL -- JOIN优化
表初始化1234567891011121314151617181920212223CREATE TABLE t1(id INT PRIMARY KEY, a INT, b INT, INDEX(a));CREATE TABLE t2 LIKE t1;DROP PROCEDURE idata;DELIMITER ;;CREATE PROCEDURE idata()BEGIN DECLARE i INT; SET i=1; WHILE (i <= 1000) DO INSERT INTO t1 VALUES (i,1001-i,i); SET i=i+1; END WHILE; SET i=1; WHILE (i <= 1000000) DO INSERT INTO t2 VALUES (i,i,i); SET i=i+1; END WHILE;END;;DELIMITER ;CALL idata(); Multi-Range ReadMRR的目的：尽量使用顺序读盘 回表1SELECT * F...
MySQL -- JOIN
表初始化123456789101112131415161718192021222324CREATE TABLE `t2` ( `id` INT(11) NOT NULL, `a` INT(11) DEFAULT NULL, `b` INT(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`)) ENGINE=InnoDB;DROP PROCEDURE IF EXISTS idata;DELIMITER ;;CREATE PROCEDURE idata()BEGIN DECLARE i INT; SET i=1; WHILE (i <= 1000) DO INSERT INTO t2 VALUES (i,i,i); SET i=i+1; END WHILE;END;;DELIMITER ;CALL idata();CREATE TABLE t1 LIKE t2;INSERT INTO t1 (SELECT * FROM t2 WHERE id<=100); Index Nested-Loo...
MySQL -- 全表扫描
Server层12-- db1.t有200GBmysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file 查询数据 InnoDB的数据是保存在主键索引上，全表扫描实际上是直接扫描表t的主键索引 获取一行，写到net_buffer中，默认为16K，控制参数为net_buffer_length 重复获取行，直到写满net_buffer，然后调用网络接口发出去 如果发送成功，就清空net_buffer，然后继续取下一行并写入net_buffer 如果发送函数返回EAGAIN或者WSAEWOULDBLOCK，表示本地网络栈socket send buffer写满 此时，进入等待，直到网络栈重新可写，再继续发送 一个查询在发送数据的过程中，占用MySQL内部的内存最大为net_buffer_length，因此不会达到200G socket send buffer也不可能达到200G，如果socket send buffer被写满，就会暂停读取数据 1234567-- 16384...
MySQL -- KILL + 客户端
KILL KILL QUERY THREAD_ID 终止这个线程中正在执行的语句 KILL [ CONNECTION ] THREAD_ID 断开这个线程的连接，如果该线程有语句在执行，先停止正在执行的语句 锁等待表初始化1234567CREATE TABLE `t` ( `id` INT(11) NOT NULL, `c` INT(11) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;INSERT INTO t VALUES (1,1); 操作次序 session A session B session C BEGIN; UPDATE t SET c=c+1 WHERE id=1; UPDATE t SET c=c+2 WHERE id=1;(Blocked) SHOW PROCESSLIST; KILL QUERY 24; ERROR 1317 (70100): Query execution was interrupted 1234567...
MySQL -- 数据恢复
DELETE 使用DELETE语句误删除了数据行，可以使用Flashback通过闪回把数据恢复 Flashback恢复数据的原理：修改binlog的内容，然后拿到原库重放 前提：binlog_format=ROW和binlog_row_image=FULL 针对单个事务 对于INSERT语句，将Write_rows event改成Delete_rows event 对于DELETE语句，将Delete_rows event改成Write_rows event 对于UPDATE语句，binlog里面记录了数据行修改前和修改后的值，对调两行的位置即可 针对多个事务 误操作 (A)DELETE (B)INSERT (C)UPDTAE Flashback (REVERSE C)UPDATE (REVERSE B)DELETE (REVERSE A)INSERT 不推荐直接在主库上执行上述操作，避免造成二次破坏 比较安全的做法是先恢复出一个备份或找一个从库作为临时库 在临时库上执行上述操作，然后再将确认过的临时库的数据，恢复到主库 预防措施 sql_safe_updates=ON，下列情况...
MySQL -- 故障诊断
SELECT 1SELECT 1只能说明数据库进程还在，但不能说明数据库没有问题 1234567-- innodb_thread_concurrency表示并发线程数量mysql> SHOW VARIABLES LIKE '%innodb_thread_concurrency%';+---------------------------+-------+| Variable_name | Value |+---------------------------+-------+| innodb_thread_concurrency | 16 |+---------------------------+-------+ 表初始化12345678910-- innodb_thread_concurrency默认为0，表示不限制并发线程数量，建议设置范围64~128SET GLOBAL innodb_thread_concurrency=3;CREATE TABLE `t` ( `id` INT(11) NOT NULL, `c` INT...
MySQL -- 读写分离
读写分离架构客户端直连 Proxy 对比 客户端直连 少了一层Proxy转发，查询性能稍微好一点 整体架构简单，排查问题方便 需要了解后端部署细节，在出现主从切换、库迁移时，客户端有感知，需要调整数据库连接信息 一般伴随着一个负责管理后端的组件，例如ZooKeeper Proxy – 发展趋势 对客户端友好，客户端不需要关注后端细节，但后端维护成本较高 Proxy也需要高可用架构，带Proxy的整体架构相对复杂 过期读由于主从延迟，主库上执行完一个更新事务后，立马在从库上执行查询，有可能读到刚刚的事务更新之前的状态 解决方案强制走主库 将查询请求做分类 必须要拿到最新结果的请求，强制将其发送到主库上 可以读到旧数据的请求，将其发到从库上 如果完全不能接受过期读，例如金融类业务，相当于放弃读写分离，所有的读写压力都在主库上 SLEEP 主库更新后，读从库之前先SLEEP一下，类似于SELECT SLEEP(1) 基于的假设：大多数主从延时在1秒内 卖家发布商品后，用Ajax直接把客户端输入的内容作为“新的商品”显示在页面上，而非真正的做数据库查询 等卖家再次刷新页面，其...
MySQL -- 主从切换
一主多从 虚线箭头为主从关系，A和A'互为主从，B、C、D指向主库A 一主多从的设置，一般用于读写分离，主库负责所有的写入和一部分读，其它读请求由从库分担 主库故障切换A'成为新的主库，B、C、D指向主库A' 基于位点的切换B原先是A的从库，本地记录的也是A的位点，但相同的日志，A的位点与A'的位点是不同的 12345678-- 节点B设置为节点A'的从库CHANGE MASTER TOMASTER_HOST=$host_nameMASTER_PORT=$portMASTER_USER=$user_nameMASTER_PASSWORD=$passwordMASTER_LOG_FILE=$master_log_nameMASTER_LOG_POS=$master_log_pos 寻找位点 很难精确，只能大概获取一个位置 由于在切换过程中不能丢数据，在寻找位点的时候，总是找一个稍微往前的位点，跳过那些已经在B执行过的事务 常规步骤 等待新主库A'将所有relaylog全部执行完 在A'上执行SHOW MASTER STATUS，得到...
