Use procedure to batch init data

create 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 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);

when to use join

在“可以使用被驱动表的索引”的前提下:
1.使用join语句,性能比强行拆成多个单表执行SQL语句的性能要好
2. 使用join语句的话,需要让小表做驱动表

判断要不要使用join语句时,就要看explain结果里面,Extra字段里面有没有出现“Block Nested Loop”字样

我们可以通过设置 join_buffer_size 来控制buffer的大小

如果使用Block Nested-Loop Join 算没法,扫描行数就会过多。尤其是大表上的join操作,这样可能要扫描被驱动表很多次,会占用大量的系统资源,这时候,join尽量不要用

在决定哪个表做驱动表的时候,应该是两个表按照各自的过滤条件过滤,过滤完成后,计算参与join的各个字段的总数据量,数据量小的那个表,就是“小表”,应该作为驱动表

awk

awk ‘{print}’ coins.txt
awk ‘{print $1}’ coins.txt
awk ‘{print $2}’ coins.txt
awk ‘{print $3}’ coins.txt
awk ‘{print $4}’ coins.txt
awk ‘{print $5}’ coins.txt
awk ‘{print $1,$2,$5}’ coins.txt
awk ‘{print $1 “\t” $2,$5}’ coins.txt
awk ‘{print $1 “\t” $2 “\t” $5}’ coins.txt
awk ‘{print NR “\t” $1 “\t” $2 “\t” $5}’ coins.txt
awk ‘{print NF $0}’ coins.txt
awk ‘{print NF “\t” $0}’ coins.txt
awk ‘{print NR “\t” $0}’ coins.txt
awk ‘{print NF “\t” $0}’ coins.txt
awk ‘$3=1981{print $0}’ coins.txt
awk ‘$1=”gold”{print $0}’ coins.txt
awk ‘$4=”American”{print $0}’ coins.txt
awk ‘NF=5{print $0}’ coins.txt
awk ‘{print $1, $2}’
awk ‘BEGIN{fs=”,”}{print $1, $2}’
awk ‘BEGIN{FS=”,”}{print $1, $2}’
awk ‘BEGIN{OFS=”,”}{print $1, $2}’
awk ‘BEGIN{OFS=”,”; FS=”,”}{print $1, $2}’
awk ‘BEGIN{OFS=”\t”; FS=”,”}{print $1, $2}’
awk ‘{ print NR, $0}’ coins.txt data.txt
awk ‘{ print NR, FILENAME, $0}’ coins.txt data.txt
awk ‘{ $3=”xxxx” print $0}’ coins.txt
awk ‘{ $3=”xxxx”; print $0}’ coins.txt
awk ‘{ print $-1}’ coins.txt
awk ‘{ print $NF}’ coins.txt
awk ‘{ print $(NF-1)}’ coins.txt
awk ‘{ print $(NR-1)}’ coins.txt
awk ‘{a=1;b=3; print a+b}’
awk ‘{a=1;b=3; print a b}’
awk ‘{a=1;b=3; print a+b}’
awk ‘{a=1;b=3;c=4; print a b+c}’
awk ‘/abc/{print $0}’ regex.txt
vim regex.txt
awk ‘/ac/{print $0}’ regex.txt
awk ‘/a.c/{print $0}’ regex.txt
awk ‘/a.c/{print $0}’ regex.txt
awk ‘/a\/c/{print $0}’ regex.txt
awk ‘/a\c/{print $0}’ regex.txt

sed