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

1. check db the long running transaction
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;
2. Rebuild index
alter table T engine=InnoDB;
3. Innodb buffer pool
buffer pool composite
4. Double write
3 steps of double write

Refer:

1. InnoDB关键特性之double write

The problem is described at Word Ladder II .

Algorithm 1

public List<List<String>> findLadders(String beginWord, String endWord, List<String> wordList) {
List<List<String>> res = new ArrayList<>();
List<String> list = new LinkedList<>();
Map<String, List<String>> children = new HashMap<>();

if (! wordList.contains(endWord)) {
return Collections.emptyList();
}

Queue<String> q = new LinkedList<>();
Set<String> visited = new HashSet<>();
Set<String> unvisited = new HashSet<>(wordList);
q.offer(beginWord);
unvisited.remove(beginWord);

boolean found = false;

while (! q.isEmpty()) {
int size = q.size();

for (int k = size - 1; k >= 0; k --) {
String word = q.poll();
for (int i = 0; i < word.length(); i++) {
char[] chs = word.toCharArray();
char c0 = chs[i];
for (char c = 'a'; c <= 'z'; c ++) {
chs[i] = c;
String newStr = new String(chs);

if (unvisited.contains(newStr)) {//only valid when the new str is dict
if (! visited.contains(newStr)) {
visited.add(newStr);
q.offer(newStr);
}

// here we construct an adjcement graph in the BFS process, each
// pointing from achived word to original word
if (children.containsKey(newStr)) {
children.get(newStr).add(word);
} else {
List<String> l = new ArrayList<>();
l.add(word);
children.put(newStr, l);
}

if (newStr.equals(endWord)) {
found = true;
}
}



} // a-z

chs[i] = c0;
}// fist index ---- last index
} // for each string

if (found) {
break;
}
unvisited.removeAll(visited); // clear the stack
visited.clear();
}

backTrace(endWord, beginWord, children, list, res); // back

return res;
}


private void backTrace(String cur, String start, Map<String, List<String>> children, List<String> list, List<List<String>> res) {
if (Objects.equals(cur, start)) {
list.add(0, cur);
res.add(new ArrayList<>(list));
list.remove(0);
return;
}

list.add(0, cur);

if (children.get(cur) != null) {
for (String str : children.get(cur)) {
backTrace(str, start, children, list, res);
}
}

list.remove(0);

}

// use dfs, that comes from beginWord to endWord
public void dfs(String word, String endWord, Map<String, List<String>> from, List<String> curr, List<List<String>> ans) {
if (Objects.equals(word, endWord)) {
ArrayList<String> oneAns = (ArrayList) ((ArrayList)curr).clone();
ans.add(oneAns);
return;
}

if (! from.containsKey(word)) {
return;
}

for (String w :
from.get(word)) {
curr.add(w);
dfs(w, endWord, from, curr, ans);
curr.remove(w);
}

}

Algorithm 2

public List<List<String>> findLadders(String beginWord, String endWord, List<String> wordList) {
List<List<String>> rst = new ArrayList<>();

HashSet<String> dict = new HashSet<>(wordList);

if (!dict.contains(endWord)) {
return Collections.emptyList();
}

Map<String, List<String>> childrenMap = new HashMap<>();
Map<String, Integer> distanceMap = new HashMap<>();

Queue<String> q = new LinkedList<>();
dict.add(beginWord);
q.offer(beginWord);
distanceMap.put(beginWord, 0);

for (String s : dict) {
childrenMap.put(s, new ArrayList<>());
}

while (!q.isEmpty()) {
String str = q.poll();
List<String> list = transform(dict, str);
Objects.requireNonNull(list).forEach(s -> {
childrenMap.get(str).add(s);
if (! distanceMap.containsKey(s)) {
distanceMap.put(s, distanceMap.get(str) + 1);
q.offer(s);
}
});
}

bfs(beginWord, endWord, childrenMap, distanceMap, rst);

return rst;
}

private List<String> transform(Set<String> dict, String word) {
List<String> candidates = new ArrayList<>();
StringBuilder sb = new StringBuilder(word);
for (int i = 0; i < sb.length(); i++) {
char tmp = sb.charAt(i);
for (char c = 'a'; c <= 'z'; c++) {
if (tmp == c) continue;
sb.setCharAt(i, c);
String newWord = sb.toString();
if (dict.contains(newWord)) {
candidates.add(newWord);
}
}
sb.setCharAt(i, tmp);
}
return candidates;
}

public void bfs(String start, String end, Map<String, List<String>> childrenMap, Map<String, Integer> distanceMap, List<List<String>> rst) {
Queue<List<String>> q = new LinkedList<>();
List<String> list = new ArrayList<>();
list.add(end);
q.offer(new ArrayList<>(list));

while (!q.isEmpty()) {
int size = q.size();
while (size > 0) {
list = q.poll();
String str = list.get(0);

for (String s : childrenMap.get(str)) {
list.add(0, s);

if (s.equals(start)) {
rst.add(new ArrayList<>(list));
} else if (distanceMap.containsKey(s) && distanceMap.get(str) - 1 == distanceMap.get(s)) { // check str and s is an adjcement
q.offer(new ArrayList<>(list));
}

list.remove(0);
}

size --;
}
}
}