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()
	declare i int;
	set i=1;
	while (i<=1000) DO
		insert into t2 values (i,i,i);
		set i=i+1;
	end while;

delimiter ;
call idata();

create table t1 like t2;
insert into t1 (select * from t2 where id <=100);

when to use join

2. 使用join语句的话,需要让小表做驱动表

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

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

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



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


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


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

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)) {

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

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

} // a-z

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

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

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.add(0, cur);

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



// 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();

if (! from.containsKey(word)) {

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


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<>();
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 -> {
if (! distanceMap.containsKey(s)) {
distanceMap.put(s, distanceMap.get(str) + 1);

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)) {
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<>();
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));


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)


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>

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'


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/ | ssh -i aws.pem ubuntu@ip_address “cat – >> ~/.ssh/authorized_keys”