传统情况
我们先回顾一下,在没有 "立刻加列" 功能时,加列操作是怎么完成的.我们也借此来熟悉一下本期的图例：
如上一期图解所讲,当改变数据行的长度,就需要 重建表空间（图中灰蓝的部分为发生变更的部分）
数据字典中的列定义也会被更新
以上操作的问题在于 每次加列 操作都需要重建表空间,这就需要大量 IO以及大量的时间
立刻加列
"立刻加列" 的过程如下图：
"立刻加列" 时,只会变更数据字典中的内容,包括：
在列定义中增加 新列的定义
增加 新列的默认值
"立刻加列"?后,当要读取表中的数据时：
以上过程描述了 如何读取?在 "立刻加列" 之前写入的数据,其实质是：在读取数据的过程中,"伪造"?了一个新列出来
那么如何读取?在 "立刻加列" 之后?写入的数据呢 ? 过程如下图：
通过判断?数据行的头信息中的instant?标志位,可以知道该行的格式是 "新格式"：该行头信息后有一个新字段?"列数"
通过读取?数据行的?"列数"?字段,可以知道 该行数据中多少列有 "真实" 的数据,从而按列数读取数据
通过上图可以看到：读取?在"立刻加列"?前/后写入的数据是不同的流程
通过以上的讨论,我们可以总结?"立刻加列"?之所以高效的原因是：
在执行?"立刻加列"?时,不变更数据行的结构
读取 "旧" 数据时,"伪造"?新增的列,使结果正确
写入 "新" 数据时,使用了新的数据格式（增加了instant标志位 和?"列数"?字段),以区分新旧数据
读取 "新" 数据时,可以如实读取数据
那么?我们是否能一直 "伪造"?下去?"伪造"?何时会被拆穿 ?
考虑以下场景：
用 "立刻加列" 增加列 A
写入数据行 1
用 "立刻加列" 增加列?B
删除列?B
我们推测一下 "删除列 B" 的最小代价：需要修改 数据行中的instant标志位或?"列数"?字段,这至少会影响到?"立刻加列"?之后写入的数据行,成本类似于重建数据
从以上推测可知：当出现 与?"立刻加列"?操作不兼容 的 DDL 操作时,数据表需要进行重建,如下图所示：
扩展思考题：是否能设计其他的数据格式,取代instant标志位和?"列数"?字段,使得 加列/删列 操作都能 "立刻完成" ?（提示：考虑 加列?- 删列?- 再加列 的情况）
使用限制
在了解原理之后,我们来看看?"立刻加列"?的使用限制,就很容易能理解其中的前两项：
"立刻加列"?的加列位置只能在表的最后,而不能加在其他列之间
在元数据中,只记录了 数据行 应有多少列,而没有记录 这些列 应出现的位置.所以无法实现指定列的位置
"立刻加列"?不能添加主键列
加列 不能涉及聚簇索引的变更,否则就变成了 "重建" 操作,不是 "立刻" 完成了
"立刻加列"不支持压缩的表格式
按照 WL 的说法："COMPRESSED is no need to supported"（没必要支持不怎么用的格式）
总结回顾
我们最后提醒一下大家上面的讨论：
"立刻加列" 之所以高效的原因是：
在执行 "立刻加列" 时,不变更数据行的结构
写入 "新" 数据时,使用了新的数据格式?(增加了?instant 标志位?和 "列数" 字段),以区分新旧数据
"立刻加列"?的 "伪造" 手法,不能一直维持下去.当发生?与 "立刻加列" 操作不兼容?的 DDL?时,表数据就会发生重建
回到之前遗留的两个问题：
"立刻加列" 是如何工作的 ?
我们已经解答了这个问题
所谓 "立刻加列" 是否完全不影响业务,是否是真正的 "立刻" 完成 ?
可以看到：就算是 "立刻加列",也需要变更 数据字典,那么 该上的锁还是逃不掉的.也就是说 这里的 "立刻" 指的是 "不变更数据行的结构",而并非指 "零成本地完成任务"
方法一,从已有大数据表中检索大量数据插入到目标表里;
方法二,编写存储过程,利用循环向数据表中插入大量的固定或有规律变化或随机变化的虚拟数据;
方法三,通过应用程序端编程向目标表插入大量的数据,手法与方法二类似.
第一段：关键字执行顺序
①.、查询中用到的关键词主要包含六个,并且他们的顺序依次为 ：
select--from--where--group by--having--order by 其中select和from是必须的,其他关键词是可选的.
这六个关键词的执行顺序,与sql语句的书写顺序并不是一样的,而是按照下面的顺序来执行
from--where--group by--having--select--order by.
注意：虽然select在having后执行,但是mysql中仍然可以在having中使用select语句定义的别名.
原因分析：mysql在查询的时候会产生一个临时表,所有的字段名称(别名)在临时表中已经产生,产生了临时表之后才会进行having操作.也就是说mysql内部有一定的解析顺序,解析顺序select优先于having.今天这一节我个人认为是mysql可能没有将这一点做规范.Oracle中having无法使用select语句内的别名.
insert into是mysql的基本插入语句.replace into 是mysql中的代替插入语句,可以理解为insert into的升级版.replace into在执行的时候,首先会根据指定的主键或者唯一索引判断当前表中是否存在指定的主键或索引,如果主键或唯一索引已经存在,则先将对应的索引的数据删除,然后在索引位置插入replace into中包含的数据.如果主键和唯一索引没有存在,则直接在索引位置插入replace into中包含的数据.merge into 是oracle数据库中的代替插入语句.实现方式和replace into类似.执行效率：如果指定索引位置没有数据,insert into和replace into执行效率相差无几,二者效率相同.如果指定位置索引已经存在,insert into语句不能正常执行,replace into语句可以正常执行.注意：虽然replace into比较好使用,但是也存在一定风险：replace每次要重新分配自增id;replace中执行delete时, 在有外键的情况下会很麻烦;如果delete时定义的有触发器, 则会被执行;副作用也会被传播到replica slave.推荐使用INSERT INTO ... ON DUPLICATE KEY UPDATEON DUPLICATE KEY UPDATE是mysql特有的一个sql关键字,只能在mysql中使用.
在MySQL数据库中,如果在insert语句后面带上ON DUPLICATE KEY UPDATE 子句,而要插入的行与表中现有记录的惟一索引或主键中产生重复值,那么就会发生旧行的更新；如果插入的行数据与现有表中记录的唯一索引或者主键不重复,则执行新纪录插入操作.
比如有这样一张表：
create table func(id int primary key,count int,birthday date);
提示两行受到影响,说明先执行的删除操作,然后插入新的数据.
观察到没有任何新数据插入,count字段被更新.(count字段更新是因为刚刚的插入语句中书写了update count = count◆1;)
如果不希望任何字段更新,可以写成如下格式：
第二段：mysql内置函数
内置函数众多,不需要每一个都掌握,熟练掌握几个在以后使用即可.可以查看mysql官方文档学习内置函数.
第三段：其它注意事项
①.、在已经存在的表中添加外键.(仅作为了解内容)
alter table tb_name add constraint fk_name foreign key (tb_name.id) references tb_stu(id);
例如：alter table emp add constraint forkey foreign key(dept_id) references dept(id);
该语句是在 tb_name表上添加一个外键约束,引用 tb_stu的主键,fk_name是约束的名字.
删除约束：alter table tb_name drop constraint fk_name ；
在不要求吞吐速度而对数据的正确性和安全性要求较高时,推荐使用外键.
如果面对高吞吐量,要求优先保证读取效率时,则不推荐使用外键.
原句：delete from employee where id = 1;
别名：delete e from employee as e where id =1;
用shell脚本通过while循环批量生成mysql测试数据的方法.
①.、很多时候需要在mysql表中插入大量测试数据,下面分享一个用shell脚本通过while循环批量生成mysql测试数据的方法,你只需要根据你自己的表结构来生成sql语句即可.
复制代码代码如下:
#!/bin/bash
i=1;
MAX_INSERT_ROW_COUNT=$1;
while [ $i -le $MAX_INSERT_ROW_COUNT ]
do
d=$(date ◆%M-%d\ %H\:%m\:%S)
echo "INSERT HELLO $i @@ $d"
i=$(($i◆1))
done
exit 0
复制代码代码如下:sh create-data.sh 10000.（参数10000是要生成的数据条数.)
MySQL如何快速插入大量数据
这几天尝试了使用不同的存储引擎大量插入MySQL表数据,主要试验了MyISAM存储引擎和InnoDB.
下面是实验过程：
第一段：InnoDB存储引擎.
创建数据库和表
Sql代码
CREATE DATABASE ecommerce;
CREATE TABLE employees (
id INT NOT NULL,
birth TIMESTAMP,
job_code INT NOT NULL,
store_id INT NOT NULL
)
partition BY RANGE (store_id) (
partition p0 VALUES LESS THAN (10000),
);
创建存储过程
use ecommerce;
CREATE PROCEDURE BatchInsert(IN init INT, IN loop_time INT)
BEGIN
DECLARE Var INT;
DECLARE ID INT;
SET Var = 0;
SET ID = init;
WHILE Var loop_time DO
insert into employees(id,fname,lname,birth,hired,separated,job_code,store_id) values(ID,CONCAT('chen',ID),CONCAT('haixiang',ID),Now(),Now(),Now(),1,ID);
SET ID = ID ◆ 1;
SET Var = Var ◆ 1;
END WHILE;
END;
调用存储过程插入数据
第二段：MyISAM存储引擎
创建表
CREATE TABLE ecommerce.customer (
birth DATE,
sex INT(1),
avatar BLOB,
regtime DATETIME,
modifytime TIMESTAMP NOT NULL,
PRIMARY KEY (id)
)ENGINE = MyISAM ROW_FORMAT = DEFAULT
partition BY RANGE (id) (
partition p0 VALUES LESS THAN (100000),
DROP PROCEDURE IF EXISTS ecommerce.BatchInsertCustomer;
CREATE PROCEDURE BatchInsertCustomer(IN start INT,IN loop_time INT)
SET ID= start;
WHILE Var loop_time
DO
insert into customer(ID,email,name,password,phone,birth,sex,avatar,address,regtime,lastip,modifytime)
SET ID= ID ◆ 1;
ALTER TABLE customer DISABLE KEYS;
ALTER TABLE customer ENABLE KEYS;
通过以上对比发现对于插入大量数据时可以使用MyISAM存储引擎,如果再需要修改MySQL存储
引擎可以使用命令：
ALTER TABLE t ENGINE = MYISAM;