MySQL -- 自增主键
自增不连续表初始化1234567CREATE TABLE `t` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `c` INT(11) DEFAULT NULL, `d` INT(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `c` (`c`)) ENGINE=InnoDB;
自增值123456789101112131415INSERT INTO t VALUES (null,1,1);-- AUTO_INCREMENT=2,表示下一次插入数据时,如果需要自动生成自增值,会生成id=2mysql> SHOW CREATE TABLE t;+-------+---------------------------------------------+| Table | Create Table |+-------+---------------------------------------------+| t | CREATE TABL ...
MySQL -- Memory引擎
数据组织表初始化1234CREATE TABLE t1 (id INT PRIMARY KEY, c INT) ENGINE=Memory;CREATE TABLE t2 (id INT PRIMARY KEY, c INT) ENGINE=InnoDB;INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);INSERT INTO t2 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
执行语句123456789101112131415161718192021222324252627282930313233-- 0在最后mysql> SELECT * FROM t1;+----+------+| id | c |+----+------+| 1 | 1 || 2 | 2 || 3 | 3 || 4 | 4 || 5 | 5 || 6 | 6 || 7 ...
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 * FROM ...
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-Loop J ...
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 By ...
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
123456789m ...
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(11 ...