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 --;
}
}
}

General recommendation:

You can get a brief recommendation about MySQL by using the Perl script of mysql-tuner

How to use processlist?

select client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip from information_schema.processlist ) as connect_info group by client_ip order by client_num desc;

Why there are so many sleep processes?

By default, mysqld will timeout database connections based on two server options:

Both are 28,800 seconds(8 hours) by default.

You must set these as follows in my.cnf (take effect after mysqld is restarted)

[mysqld]
interactive_timeout=180
wait_timeout=180

If you don’t wakt to restart mysql, then run these tow commands

SET GLOBAL interactive_timeout = 180;
SET GLOBAL wait_timeout = 180;

The problem happens when a floated element is within a container box, that element does not automatically force the container’s height adjust to the floated element. When an element is floated, its parent no longer contains it because the float is removed from the flow. You can use 2 methods to fix it:

  • {clear: both}
  • clearfix

Here is the css code:

.clearfix:after {
    content: ".";
    display: block;
    clear: both;
    visibility: hidden;
    line-height: 0;
    height: 0;
}

.clearfix {
    display: inline-block;
}

html[xmlns] .clearfix {
    display: block;
}

* html .clearfix {
    height: 1%;
}

How does it work

Well you just add that to your CSS file and then you can use it as you would with normal class attribute like

<div class="clearfix">Content</div>

You can test the result using the below code snippet

<div class="clearfix" >
  <div style="float: left;">left</div>
  <div style="float: right;">right</div>
</div>


Refer here

Backup our sytem

sudo rsync -aAXv --delete --dry-run --exclude=/dev/* --exclude=/proc/* --exclude=/sys/* --exclude=/tmp/* --exclude=/run/* --exclude=/mnt/* --exclude=/media/* --exclude="swapfile" --exclude="lost+found" --exclude=".cache" --exclude="Downloads" --exclude=".ecryptfs" /source /destination

Change the partition format if needed

df -T /run/media/alu/alu

sudo unmount /dev/sdv1
sudo mkfs.ext4 /dev/sdb1 -L 'ALU'

lsblk

Restore the system

mkdir -p /mnt/usb/
mount /dev/sda1 /mnt/usb
rsync -aAXv --delete --exclude="lost+found" /mnt/usb/ /mnt/system

If you use pem file to access to remote ec2 address, usually it’s like below

ssh -i ubuntu.pem ubuntu@ip_address

But how to access the remote ec2 using just one line? For example, use sftp, ssh, scp command. You can achieve that by the following:

cat ~/.ssh/id_rsa.pub | ssh -i aws.pem ubuntu@ip_address “cat – >> ~/.ssh/authorized_keys”