纵欲二月(2019)

欲望使我痛苦不堪。

春节来到,饮食不当,肠胃坏掉,噩梦来撩。

去年没赚什么钱,自然没多少物质回馈父母,所以我得在家多呆几天,陪陪他们。

可是啊可是,我就一周的假期,不上班我就没有钱,没有钱我怎么有脸在家鬼混啊。

假期结束,我就又回到大上海开始两点一线的生活了。


聚会

年前参加了初中同学聚会,距离初中毕业已然过了九个春夏。上一次聚会我还在读大学呢,这次都工作一年多了。

大多同学许多年未见,一些更是自毕业后再无接触。人生能再有几个九年,不过这个九年大家似乎外貌变化不太大,一些女生就例外了,女大十八变啊。

大家各行各业的都有,交谈起来不免觉得自己同学都好优秀,我怎么混成了这样,哭唧唧。同学中有护士、医生、律师、海龟、警察等等。还有自己开舞蹈工作室的,有在清迈开民宿的,有自己开厂当老板的,我的天啊。还有北航读书的、北邮读研的,卧槽还有清华大学本硕的,真是个女学霸,厉害!

当然,也找到了几个同行。顿时觉得,我要是在老家发展,那这资源岂不丰富!不行啊,我还得赚钱买房娶媳妇啊。

这么聚聚挺好的,再过几年都成家后怕是抽不出空来咯。

聚餐后 KTV 喝酒喝多了,一个个真特么能喝!

过年

年三十一家人吃了年夜饭,后又去爬了个小山消食,一家人走走挺好的。

接着就是去亲戚家拜年咯,一家一天,吃吃喝喝,基本就没有米饭什么事了。

我家顶楼是个阳光棚,买了烧烤架以及一些简单的东西,在自家楼上也可以吃吃烧烤,看看阴天,或者是听听滴水声。

春节在中国这么重要的节日,我竟然不知道要写些什么,光在家嗑核桃嗑傻了吧。

说到嗑核桃,核桃是不是吃多上火?嘴角后来长了好大一个疮,都多久没长过了,现在都还有印子。

痛苦

太痛苦了,一回到上海肠胃就出问题了,不知道是冻着了还是之前吃狠了,急性肠胃炎啊。

真是吃什么拉什么,都到了什么程度你晓得不,我晚上回去怕自己虚脱,直接拿细盐冲开水往嘴里怼。

后来喝了一星期的藿香正气水,吃了一星期的消炎药,早上一杯燕麦,中午基本不吃,晚上吃水煮小馄饨,慢慢好转。

为什么我不去医院?去医院不得要花大钱啊,哭唧唧。我相信自己的身体,它能抗!

抗完肠胃抗流感。

什么情况啊,真是屋漏偏逢连夜雨!

对不起可爱的室友们了,空调我已经偷摸着开了好几个晚上了,哈哈。

船到桥头自然直 彩虹总在风雨后


JVM 常用调优参数

记录下 JVM 常用的一些调优参数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
// 常见参数
-Xms1024m 初始堆大小
-Xmx1024m 最大堆大小 一般将 Xms 和 Xmx 设置为相同大小,防止堆扩展,影响性能。
-XX:NewSize=n:设置年轻代大小
-XX:NewRatio=n:设置年轻代和年老代的比值.如:为 3,表示年轻代与年老代比值为 1:3,年轻代占整个年轻代年老代和的 1/4
-XX:SurvivorRatio=n:年轻代中 Eden 区与两个 Survivor 区的比值.注意 Survivor 区有两个.如: 3,表示 Eden:Survivor=3:2,一个 Survivor 区占整个年轻代的 1/5
-XX:MaxPermSize=n:设置持久代大小
-XX:+HeapDumpOnOutOfMemoryError OOM 时自动保存堆文件,可以用 visualvm 分析堆文件

// 收集器设置
-XX:+UseSerialGC:设置串行收集器
-XX:+UseParallelGC:设置并行收集器
-XX:+UseParalledlOldGC:设置并行年老代收集器
-XX:+UseConcMarkSweepGC:设置并发收集器

// 垃圾回收统计信息
-XX:+PrintGC
-XX:+PrintGCDetails
-XX:+PrintGCTimeStamps
-Xloggc:filename

// 并行收集器设置
-XX:ParallelGCThreads=n:设置并行收集器收集时使用的CPU数
-XX:MaxGCPauseMillis=n:设置并行收集最大暂停时间
-XX:GCTimeRatio=n:设置垃圾回收时间占程序运行时间的百分比.公式为 1/(1+n)

// 并发收集器设置
-XX:+CMSIncrementalMode:设置为增量模式.适用于单 CPU 情况.
-XX:ParallelGCThreads=n:设置并发收集器年轻代收集方式为并行收集时,使用的 CPU 数.并行收集线程数.

LeetCode 之反转链表(Reverse Linked List)

前言

反转链表也是常见的面试算法题了。

何为链表?

链表(Linked list)是一种常见的基础数据结构,是一种线性表,但是并不会按线性的顺序存储数据,而是在每一个节点里存到下一个节点的指针(Pointer)。由于不必须按顺序存储,链表在插入的时候可以达到O(1)的复杂度,比另一种线性表顺序表快得多,但是查找一个节点或者访问特定编号的节点则需要O(n)的时间,而顺序表相应的时间复杂度分别是O(logn)和O(1)。

正文

我们先来看下题目描述:

1
2
3
4
5
6
7
8
反转一个单链表。

示例:
输入: 1->2->3->4->5->NULL
输出: 5->4->3->2->1->NULL

进阶:
你可以迭代或递归地反转链表。你能否用两种方法解决这道题?

小时候都玩过玩具蛇吧,那种可以一节一节拼接的。

我们可以想象下现在面前就有这么一条“蛇”,我们试着把它重新组装一遍,我们简单地 边拆边装

先把它尾巴拆了放一边,再接着拆它的倒数第二块同时把它安装到拆下来的尾巴那,以此下去……

到最后把“蛇头”也给装好,就完事了。

这道题的解题思路也就是这样,边拆边装

你可以迭代或递归地反转链表。你能否用两种方法解决这道题?

贴出两种解决方案代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
public class ReverseLinkedList {

public class ListNode {
int val;
ListNode next;

ListNode(int x) {
val = x;
}
}

/**
* 递归
*
* @param head
* @return
*/
public ListNode reverseList(ListNode head) {
ListNode reverseList = null;
return helper(head, reverseList);
}

private ListNode helper(ListNode head, ListNode reverseList) {
if (head == null) // 反转结束
return reverseList;
// 节点指针变换
ListNode tempNode = head.next;
head.next = reverseList;
return helper(tempNode, head);
}

/**
* 迭代
*
* @param head
* @return
*/
public ListNode reverseList1(ListNode head) {

ListNode newHead = null;
while (head != null) { // 遍历
ListNode next = head.next;
head.next = newHead;
newHead = head;
head = next;
}
return newHead;
}
}

两者都是先用一个空链表然后再进行一步步得组装。

就是指针指来指去,有点绕,借助实物理解起来会容易很多。

还有一个进阶版本的反转链表 II,看题:

1
2
3
4
5
6
7
8
9
反转从位置 m 到 n 的链表。请使用一趟扫描完成反转。

说明:
1 ≤ m ≤ n ≤ 链表长度。

示例:

输入: 1->2->3->4->5->NULL, m = 2, n = 4
输出: 1->4->3->2->5->NULL

区别就是这里不是反转所有的结点了,只需要反转指定位置之间的结点了,重点就是确认反转的指针位置。然后反转的操作还是与上面一样。

代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
public class ReverseLinkedListII {

public class ListNode {
int val;
ListNode next;

ListNode(int x) {
val = x;
}
}

public ListNode reverseBetween(ListNode head, int m, int n) {
if (head == null)
return null;
// 新建一个节点并指向 head
ListNode dummy = new ListNode(0);
dummy.next = head;
ListNode pre = dummy;
// pre 为需要反转的前节点
for (int i = 0; i < m - 1; i++)
pre = pre.next;

// 需要反转的节点 双指针
ListNode start = pre.next;
ListNode then = start.next;

// 反转节点
for (int i = 0; i < n - m; i++) {
start.next = then.next;
then.next = pre.next;
pre.next = then;
then = start.next;
}
return dummy.next;
}
}

注释齐全,一目了然。


MySQL 中常用 SQL 的优化

前言

之前介绍了 MySQL 中怎么样通过索引来优化查询。日常开发中,除了使用查询外,我们还会使用一些其他的常用 SQL,比如 INSERT、GROUP BY 等。对于这些 SQL 语句,我们该怎么样进行优化呢?接下来将针对这些 SQL 语句介绍一些优化的方法。

大批量插入数据

当用 load 命令导入数据的时候,适当的设置可以提高导入的速度。

对于 MyISAM 存储引擎的表,可以通过以下方式快速的导入大量的数据。

1
2
3
ALTER TABLE tbl_name DISABLE KEYS;
loading the data
ALTER TABLE tbl_name ENABLE KEYS;

DISABLE KEYSENABLE KEYS 用来打开或者关闭 MyISAM 表非唯一索引的更新。在导入大量的数据到一个非空的 MyISAM 表时,通过设置这两个命令,可以提高导入的效率。对于导入大量数据到一个空的 MyISAM 表,默认就是先导入数据然后才创建索引的,所以不用进行设置。

下面例子中,用 LOAD 语句导入数据耗时 115.12 秒:

1
2
3
mysql> load data infile '/home/mysql/film_test.txt' into table film_test2;
Query OK,529056 rows affected(1 min 55.12 sec)
Records: 529056 Deleted: 0 Skipped: 0 Warning: 0

而用 alter table tbl_name disable keys 方式总耗时 6.34+12.25 = 18.59 秒,提高了 6 倍多。

1
2
3
4
5
6
7
8
9
mysql> alter table film_test2 disable keys;
Query OK,0 rows affected(0.00 sec)

mysql> load data infile '/home/mysql/film_test.txt' into table film_test2;
Query OK,529056 rows affected(6.34 sec)
Records: 529056 Deleted: 0 Skipped: 0 Warnings: 0

mysql> alter table film_test2 enable keys;
Query OK,0 rows affected(12.25 sec)

上面是对 MyISAM 表进行数据导入时的优化措施,对于 InnoDB 类型的表,这种方式并不能提高导入数据的效率,可以有以下几种方式提高 InnoDB 表的导入效率。

(1)因为 InnoDB 类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺序排列,可以有效地提高导入数据的效率。

例如,下面文本 film_test3.txt 是按表 film_tst4 的主键存储的,那么导入的时候共耗时 27.92 秒。

1
2
3
mysql> load data infile '/home/mysql/film_test3.txt' into table film_test4;
Query OK,1587168 rows affected(22.92 sec)
Records: 1587168 Deleted: 0 Skipped: 0 Warning: 0

而下面的 film_test4.txt 是没有任何顺序的文本,那么导入的时候共耗时 31.16 秒。

1
2
3
mysql> load data infile '/home/mysql/film_test4.txt' into table film_test4;
Query OK,1587168 rows affected(31.16 sec)
Records: 1587168 Deleted: 0 Skipped: 0 Warning: 0

从上面的例子可以看出当被导入的文件按表主键顺序存储的时候比不按主键顺序存储的时候快 1.12 倍。

(2)在导入数据前执行 SET UNIQUE_CHECKS=0,关闭唯一性校验,在导入结束后执行 SET UNIQUE_CHECK=1,恢复唯一性校验,可以提高导入的效率。

例如,当 UNIQUE_CHECKS=1 时:

1
2
3
mysql> load data infile '/home/mysql/film_test3.txt' into table film_test4;
Query OK,1587168 rows affected(22.92 sec)
Records: 1587168 Deleted: 0 Skipped: 0 Warning: 0

当 SET UNIQUE_CHECKS=0 时:

1
2
3
mysql> load data infile '/home/mysql/film_test3.txt' into table film_test4;
Query OK,1587168 rows affected(19.92 sec)
Records: 1587168 Deleted: 0 Skipped: 0 Warning: 0

可见比 UNIQUE_CHECKS=0 的时候比 SET UNIQUE_CHECKS=1 的时候要快一些。

(3)如果应用使用自动提交的方式,建议在导入前执行 SET AUTOCOMMIT=0,关闭自动提交,导入结束后再执行 SET AUTOCOMMIT=1,打开自动提交,也可以提高导入的效率。

例如,当 AUTOCOMMIT=1 时:

1
2
3
mysql> load data infile '/home/mysql/film_test3.txt' into table film_test4;
Query OK,1587168 rows affected(22.92 sec)
Records: 1587168 Deleted: 0 Skipped: 0 Warning: 0

当 AUTOCOMMIT=0 时:

1
2
3
mysql> load data infile '/home/mysql/film_test3.txt' into table film_test4;
Query OK,1587168 rows affected(20.87 sec)
Records: 1587168 Deleted: 0 Skipped: 0 Warning: 0

对比一下可以知道,当 AUTOCOMMIT=0 时比 AUTOCOMMIT=1 时导入数据要快一些。

优化 INSERT 语句

当进行数据 INSERT 的时候,可以考虑采用以下几种优化方式。

  • 如果同时从同一客户插入很多行,尽量使用多个值表的 INSERT 语句,这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗,使得效率比分开执行的单个 INSERT 语句快(在一些情况中几倍)。下面是一次插入多值的一个例子:
    1
    insert into test values(1,2),(1,3),(1,4)...
  • 如果从不同客户插入很多行,能通过使用 INSERT DELAYED 语句得到更高的速度。DELAYED 的含义是让 INSERT 语句马上执行,其实数据都被放在内存的队列中,并没有真正写入磁盘,这比每条语句分别插入要快得多;LOW_PRIORITY 刚好相反,在所有其他用户对表的读写完后才进行插入;
  • 将索引文件和数据文件分在不同的磁盘上存放(利用建表中的选项);
  • 如果进行批量插入,可以增加 bulk_insert_buffer_size 变量值的方法来提高速度,但是,这只能对 MyISAM 表使用;
  • 当从一个文本文件装载一个表时,使用 LOAD DATA INFILE。这通常比使用很多 INSERT 语句快 20 倍。

优化 GROUP BY 语句

默认情况下,MySQL 对所有 GROUP BY col1,col2…的字段进行排序。这与在查询中指定 ORDER BY col1,col2…类似。因此,如果显式包括一个包含相同的列的 ORDER BY 子句,则对 MySQL 的实际执行性能没有什么影响。

如果查询包括 GROUP BY 但用户想要避免排序结果的消耗,则可以指定 ORDER BY NULL 禁止排序,如下面的例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> explain select id, sum(moneys) from sales2 group by id\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using temporary; Using filesort
1 row in set(0.00 sec)

mysql> explain select id, sum(moneys) from sales2 group by id order by null\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using temporary

从上面的例子可以看出第一个 SQL 语句需要进行“filesort”,而第二个 SQL 由于 ORDER BY NULL 不需要进行“filesort”,而 filesort 往往非常耗费时间。

优化 ORDER BY 语句

在某些情况中,MySQL 可以使用一个索引来满足 ORDER BY 子句,而不需要额外的排序。WHERE 条件和 ORDER BY 使用相同的索引,并且 ORDER BY 的顺序和索引顺序相同,并且 ORDER BY 的字段都是升序或者都是降序。

例如,下列 SQL 可以使用索引:

1
2
3
SELECT * FROM t1 ORDER BY key_part1,key_part2,...;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

但是在以下几种情况下则不使用索引:

1
2
3
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;  --order by 的字段混合 ASC 和 DESC
SELECT * FROM t1 WHERE key2=constant ORDER BY key1; --用于查询行的关键字与 ORDER BY 中所使用的不相同
SELECT * FROM t1 ORDER BY key1, key2; --对不同的关键字使用 ORDER BY

优化嵌套查询

MySQL 4.1 开始支持 SQL 的子查询。这个技术可以使用 SELECT 语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的 SQL 操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,子查询可以被更有效率的连接(JOIN)替代。

在下面的例子中,要从 sales2 表中找到那些在 company2 表中不存在的所有公司的信息:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> explain select * from sales2 where company_id not in (select id from company2)\G;
**************************** 1. row *************************************
id: 1
select_type: PRIMARY
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
**************************** 2. row *************************************
id: 2
select_type: DEPENDENT SUBQUERY
table: company2
type: index_subquery
possible_keys: ind_company2_id
key: ind_company2_id
key_len: 5
ref: func
rows: 2
Extra: Using index

2 rows in set(0.00 sec)

如果使用连接(JOIN)来完成这个查询工作,速度将会快很多。尤其是当 company2 表中对 id 建有索引的话,性能将会更好,具体查询如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> explain select * from sales2 left join company2 on sales2.company_id = company2.id where sales2.company_id is null\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: sales2
type: ref
possible_keys: ind_sales2_companyid_moneys
key: ind_sales2_companyid_moneys
key_len: 5
ref: const
rows: 1
Extra: Using where
**************************** 2. row *************************************
id: 1
select_type: SIMPLE
table: company2
type: ref
possible_keys: ind_company2_id
key: ind_company2_id
key_len: 5
ref: sakila.sales2.company_id
rows: 1
Extra:
2 rows in set(0.00 sec)

从执行计划中可以明显看出查询扫描的记录范围和使用索引的情况都有了很大的改善。

连接(JOIN)之所以更有效率一些,是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

优化 OR 条件

对于含有 OR 的查询子句,如果要利用索引,则 OR 之间的每个条件列都必须用到索引;如果没有索引,则应该考虑增加索引。

例如,首先使用 show index 命令查看表 sales2 的索引,可知它有 3 个索引,在 id、year 两个字段上分别有 1 个独立的索引,在 company_id 和 year 字段上有 1 个复合索引。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
mysql> show index from sales2\G;
**************************** 1. row *************************************
Table: sales2
Non_unique: 1
Key_name: ind_sales2_id
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 1000
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
**************************** 2. row *************************************
Table: sales2
Non_unique: 1
Key_name: ind_sales2_year
Seq_in_index: 1
Column_name: year
Collation: A
Cardinality: 250
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
**************************** 3. row *************************************
Table: sales2
Non_unique: 1
Key_name: ind_sales2_companyid_moneys
Seq_in_index: 1
Column_name: company_id
Collation: A
Cardinality: 1000
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
**************************** 4. row *************************************
Table: sales2
Non_unique: 1
Key_name: ind_sales2_companyid_moneys
Seq_in_index: 2
Column_name: year
Collation: A
Cardinality: 1000
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
4 rows in set(0.00 sec)

然后在两个独立索引上面做 OR 操作,具体如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> explain select * from sales2 where id = 2 or year = 1998\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: sales2
type: index_merge
possible_keys: ind_sales2_id,ind_sales2_year
key: ind_sales2_id,ind_sales2_year
key_len: 5,2
ref: NULL
rows: 2
Extra: Using union(ind_sales2_id,ind_sales2_year); Using where
1 row in set(0.00 sec)

可以发现查询正确的用到了索引,并且从执行计划的描述中,发现 MySQL 在处理含有 OR 子句的查询时,实际是对 OR 的各个字段分别查询后的结果进行了 UNION。

但是当在建有复合索引的列 company_id 和 moneys 上面做 OR 操作的时候,却不能用到索引,具体结果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> explain select * from sales2 where company_id = 3 or moneys = 100\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: ind_sales2_companyid_moneys
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set(0.00 sec)

本文大多摘自《深入浅出MySQL》。


MySQL 中的索引问题

前言

索引是数据库优化中最常用也是最重要的手段之一,通过索引通常可以帮助用户解决大多数的 SQL 性能问题。接下来将对 MySQL 中的索引的分类、存储、使用方法做详细的介绍。

索引的存储分类

MyISAM 存储引擎的表的数据和索引是自动分开存储的,各自是独立的一个文件;InnoDB 存储引擎的表的数据和索引是存储在同一个表空间里面,但可以有多个文件组成。

MySQL 中索引的存储类型目前只有两种(BTREE 和 HASH),具体和表的存储引擎相关:MyISAM 和 InnoDB 存储引擎都只支持 BTREE 索引;MEMORY/HEAP 存储引擎可以支持 HASH 和 BTREE 索引。

MySQL 目前不支持函数索引,但是能对列的前面某一部分进索引,例如 name 字段,可以只取 name 的前 4 个字符进行索引,这个特性可以大大缩小索引文件的大小,用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。下面是创建前缀索引的一个例子:

1
2
3
mysql> create index ind_company2_name on company2(name(4));
Query OK,1000 rows affected(0.03 sec)
Records: 1000 Duplicates: 0 Warnings: 0

索引的使用

索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高 SELECT 操作性能的最佳途径。

查询要使用索引最主要的条件是查询条件中需要使用索引关键字,如果是多列索引,那么只有查询条件使用了多列关键字最左边的前缀时,才可以使用索引,否则将不能使用索引。

使用索引

在 MySQL 中,下列几种情况下有可能使用到索引。

(1)对于创建的多列索引,只要查询的条件中用到了最左边的列,索引一般就会被使用,举例说明如下。

首先按 company_id,moneys 的顺序创建一个复合索引,具体如下:

1
2
3
mysql> create index ind_sales2_companyid_moneys on sales2(company_id, moneys);
Query OK,1000 rows affected(0.03 sec)
Records: 1000 Duplicates: 0 Warnings: 0

然后按 company_id 进行表查询,具体如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> explain select * from sales2 where company_id = 2006\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: sales2
type: ref
possible_keys: ind_sales2_companyid_moneys
key: ind_sales2_companyid_moneys
key_len: 5
ref: const
rows: 1
Extra: Using where
1 row in set(0.00 sec)

可以发现即便 where 条件中不是用的 company_id 与 moneys 的组合条件,索引仍然能用到,这就是索引的前缀特性。但是如果只按 moneys 条件查询表,那么索引就不会被用到,具体如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> explain select * from sales2 where moneys = 1\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set(0.00 sec)

(2)对于使用 like 的查询,后面如果是常量并且只有 % 号不在第一个字符,索引才可能会被使用,来看下面两个执行计划:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> explain select * from company2 where name like '%3'\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: company2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set(0.00 sec)

mysql> explain select * from company2 where name like '3%'\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: company2
type: range
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 11
ref: NULL
rows: 103
Extra: Using where
1 row in set(0.00 sec)

可以发现第一个例子没有使用索引,而第二例子就能够使用索引,区别就在于“%”的位置不同,前者把“%”放到第一位就不能用到索引,而后者没有放到第一位就使用了索引。

另外,如果 like 后面跟的是一个列的名字,那么索引也不会被使用。

(3)如果对大的文本进行搜索,使用全文索引而不用使用 like ‘%…%’。

(4)如果列名是索引,使用 column_name is null 将使用索引。如下例中查询 name 为 null 的记录就用到了索引:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> explain select * from company2 where name is null\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: company2
type: ref
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 11
ref: const
rows: 1
Extra: Using where
1 row in set(0.00 sec)

存在索引但不使用索引

在下列情况下,虽然存在索引,但是 MySQL 并不会使用相应的索引。

(1)如果 MySQL 估计使用索引比全表扫描更慢,则不使用索引。例如如果列 key_part1 均匀分布在 1 和 100 之间,下列查询中使用索引就不是很好:

1
SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90;

(2)如果使用 MEMORY/HEAP 表并且 where 条件中不使用“=”进行索引列,那么不会用到索引。heap 表只有在“=”的条件下才会使用索引。

(3)用 or 分割开的条件,如果 or 前的条件中的列有索引,而后面的列中没有索引,那么涉及到的索引都不会被用到,例如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show index from sales\G;
**************************** 1. row *************************************
Table: sales
Non_unique: 1
Key_name: ind_sales_year
Seq_in_index: 1
Column_name: year
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
1 row in set(0.00 sec)

从上面可以发现只有 year 列上面有索引,来看如下的执行计划:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> explain select * from sales where year = 2001 or country = 'China'\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: sales
type: ALL
possible_keys: ind_sales_year
key: NULL
key_len: NULL
ref: NULL
rows: 12
Extra: Using where
1 row in set(0.00 sec)

可见虽然在 year 这个列上存在索引 ind_sales_year,但是这个 SQL 语句并没有用到这个索引,原因就是 or 中有一个条件中的列没有索引。

(4)如果不是索引列的第一部分,如下例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> explain select * from sales2 where moneys = 1\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set(0.00 sec)

可见虽然在 money 上面建有复合索引,但是由于 money 不是索引的第一列,那么在查询中这个索引也不会被 MySQL 采用。

(5)如果 like 是以 % 开始,例如:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> explain select * from company2 where name like '%3'\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: company2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set(0.00 sec)

可见虽然在 name 上建有索引,但是由于 where 条件中 like 的值的“%”在第一位了,那么 MySQL 也不会采用这个索引。

(6)如果列类型是字符串,那么一定记得在 where 条件中把字符常量值用引号引起来,否则的话即便这个列上有索引,MySQL 也不会用到的,因为,MySQL 默认把输入的常量值进行转换以后才进行检索。如下面的例子中 company2 表中的 name 字段是字符型的,但是 SQL 语句中的条件值 294 是一个数值型值,因此即便在 name 上有索引,MySQL 也不能正确地用上索引,而是继续进行全表扫描。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> explain select * from company2 where name = 294\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: company2
type: ALL
possible_keys: ind_company2_name
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set(0.00 sec)

mysql> explain select * from company2 where name = '294'\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: company2
type: ref
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 23
ref: const
rows: 1
Extra: Using where
1 row in set(0.00 sec)

从上面的例子中可以看到,第一个 SQL 语句中把一个数值型常量赋值给了一个字符型的列 name,那么虽然在 name 列上有索引,但是也没有用到;而第二个 SQL 语句就可以正确使用索引。

查看索引使用情况

如果索引正在工作,Handler_read_key 的值将很高,这个值代表了一个行被索引值读的次数,很低的值表明增加索引得到的性能改善不高,因为索引并不经常使用。

Handler_read_rnd_next 的值高则意味着查询运行低效,并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描,Handler_read_rnd_next 的值较高,则通常说明表索引不正确或写入的查询没有利用索引,具体如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show status like 'Handler_read%';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| Handler_read_first | 0 |
| Handler_read_key | 5 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 2055 |
+------------------------+----------+

6 rows in set(0.00 sec)

从上面的例子中可以看出,目前使用的 MySQL 数据库的索引情况并不理想。

本文大多摘自《深入浅出MySQL》。


MySQL 中优化 SQL 语句的一般步骤

前言

当面对一个有 SQL 性能问题的数据库时,我们应该从何处入手来进行系统的分析,使得能够尽快定位问题 SQL 并尽快解决问题。

show status

通过 show status 命令了解各种 SQL 的执行频率。

MySQL 客户端连接成功后,通过 show[session|global] status 命令可以提供服务器状态信息,也可以在操作系统上使用 mysqladmin extended-status 命令获得这些消息。show[session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级(当前连接)的统计结果和 global 级(自数据库上次启动至今)的统计结果。如果不写,默认使用参数是“session”。

下面的命令显示了当前 session 中所有统计参数的值:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> show status like 'Com_%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_event | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 1 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
......

Com_xxx 表示每个 xxx 语句执行的次数,我们通常比较关心的是以下几个统计参数。

  • Com_select:执行 SELECT 操作的次数,一次查询只累加 1。
  • Com_insert:执行 INSERT 操作的次数,对于批量插入的 INSERT 操作,只累加一次。
  • Com_update:执行 UPDATE 操作的次数。
  • Com_delete:执行 DELETE 操作的次数。

上面这些参数对于所有存储引擎的表操作都会进行累计。下面这几个参数只是针对 InnoDB 存储引擎的,累加的算法也略有不同。

  • Innodb_rows_read:执行 SELECT 操作查询返回的行数。
  • Innodb_rows_inserted:执行 INSERT 操作插入的行数。
  • Innodb_rows_updated:执行 UPDATE 操作更新的行数。
  • Innodb_rows_deleted:执行 DELETE 操作删除的行数。

通过以上几个参数,可以很容易地了解当前数据库的应用是以插入更新为主还是以查询操作为主,以及各种类型的 SQL 大致的执行比例是多少。对于更新操作的计数,是对执行次数的计数,不论提交还是回滚都会进行累加。

对于事务型的应用,通过 Com_commit 和 Com_rollback 可以了解事务提交和回滚的情况,对于回滚操作非常频繁的数据库,可能意味着应用编写存在问题。

此外,以下几个参数便于用户了解数据库的基本情况。

  • Connections:试图连接 MySQL 服务器的次数。
  • Uptime:服务器工作时间。
  • Slow_queries:慢查询的次数。

定位 SQL 语句

可以通过以下两种方式定位执行效率较低的 SQL 语句。

  • 通过慢查询日志定位那些执行效率较低的 SQL 语句,用 --log-slow-queries[=file_name] 选项启动时,mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。
  • 慢查询日志在查询结束以后才记录,所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题,可以使用 show processlist 命令查看当前 MySQL 在进行的线程,包括线程的状态、是否锁表等,可以实时地查看 SQL 的执行情况,同时对一些锁表操作进行优化。

EXPLAIN

通过 EXPLAIN 分析低效 SQL 的执行计划

通过以上步骤查询到效率低的 SQL 语句后,可以通过 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序,比如想计算 2006 年所有公司的销售额,需要关联 sales 表和 company 表,并且对 moneys 字段做求和(sum)操作,相应 SQL 的执行计划如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> explain select sum(moneys) from sales a,company b where a.company_id = b.id and a.year = 2006\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
**************************** 2. row *************************************
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: ind_company_id
key: ind_company_id
key_len: 5
ref: sakila.a.company_id
rows: 1
Extra: Using where; Using index
2 rows in set (0.00 sec)

每个列的简单解释如下:

  • select_type:表示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个 SELECT)等。
  • table:输出结果集的表。
  • type:表示表的连接类型,性能由好到差的连接类型为 system(表中仅有一行,即常量表)、const(单表中最多有一个匹配行,例如 primary key 或者 unique index)、eq_ref(对于前面的每一行,在此表中只查询一条记录,简单来说,就是多表连接中使用 primary key 或者 unique index)、ref(与 eq_ref 类似,区别在于不是使用 primary key 或者 unique index,而是使用普通的索引)、ref_or_null(与 ref 类似,区别在于条件中包含对 NULL 的查询)、index_merge(索引合并优化)、unique_subquery(in 的后面是一个查询主键字段的子查询)、index_subquery(与 unique_subquery 类似,区别在于 in 的后面是查询非唯一索引字段的子查询)、range(单表中的范围查询)、index(对于前面的每一行,都通过查询索引来得到数据)、all(对于前面的每一行,都通过全表扫描来得到数据)。
  • possible_keys:表示查询时,可能使用的索引。
  • key:表示实际使用的索引。
  • key_len:索引字段的长度。
  • rows:扫描行的数量。
  • Extra:执行情况的说明和描述。

确定问题并采取相应的优化措施

经过以上步骤,基本就可以确认问题出现的原因。此时可以根据情况采取相应的措施,进行优化提高执行的效率。

在上面的例子中,已经可以确认是对 a 表的全表扫描导致效率的不理想,那么对 a 表的 year 字段创建索引,具体如下:

1
2
3
mysql> create index ind_sales2_year on sales2(year);
Query OK,1000 rows affected(0.03 sec)
Records:1000 Duplicates: 0 Warnings: 0

创建索引后,再看一下这条语句的执行计划,具体如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> explain select sum(moneys) from sales2 a,company2 b where a.company_id = b.id and a.year = 2006\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: ind_sales2_year
key: ind_sales2_year
key_len: 2
ref: const
rows: 1
Extra: Using where
**************************** 2. row *************************************
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: ind_company_id
key: ind_company_id
key_len: 5
ref: sakila.a.company_id
rows: 1
Extra: Using where; Using index
2 rows in set (0.00 sec)

可以发现建立索引后对 a 表需要扫描的行数明显减少(从 1000 行减少到 1 行),可见索引的使用可以大大提高数据库的访问速度,尤其在表很庞大的时候这种优势更为明显。

本文大多摘自《深入浅出MySQL》。


MySQL 中分布式事务的使用

前言

MySQL 从 5.0.3 开始支持分布式事务,当前分布式事务只支持 InnoDB 存储引擎。一个分布式事务会涉及多个行动,这些行动本身是事务性的。所有行动都必须一起成功完成,或者一起被回滚。

分布式事务的原理

在 MySQL 中,使用分布式事务的应用程序涉及一个或多个资源管理器和一个事务管理器。

  • 资源管理器(RM)用于提供通向事务资源的途经。数据库服务器是一种资源管理器。该管理器必须可以提交或回滚由 RM 管理的事务。例如,多台 MySQL 数据库作为多台资源管理器或者几台 MySQL 服务器和几台 Oracle 服务器作为资源管理器。
  • 事务管理器(TM)用于协调作为一个分布式事务一部分的事务。TM 与管理每个事务的 RMs 进行通讯。一个分布式事务中各个单个事务均是分布式事务的“分支事务”。分布式事务和各分支通过一种命名方法进行标识。

MySQL 执行 XA MySQL 时,MySQL 服务器相当于一个用于管理分布式事务中的 XA 事务的资源管理器。与 MySQL 服务器连接的客户端相当于事务管理器。

要执行一个分布式事务,必须知道这个分布式事务涉及到了哪些资源管理器,并且把每个资源管理器的事务执行到事务可以被提交或回滚时。根据每个资源管理器报告的有关执行情况的内容,这些分支事务必须作为一个原子性操作全部提交或回滚。要管理一个分布式事务,必须要考虑任何组件或连接网络可能会故障。

用于执行分布式事务的过程使用两阶段提交,发生时间在由分布式事务的各个分支需要进行的行动已经被执行之后。

  • 在第一阶段,所有的分支被预备好。即它们被 TM 告知要准备提交。通常,这意味着用于管理分支的每个 RM 会记录对于被稳定保存的分支的行动。分支指示是否它们可以这么做。这些结果被用于第二阶段。
  • 在第二阶段,TM 告知 RMs 是否要提交或回滚。如果在预备分支时,所有的分支指示它们将能够提交,则所有的分支被告知要提交。如果在预备时,有任何分支指示它将不能提交,则所有分支被告知回滚。

在有些情况下,一个分布式事务可能会使用一阶段提交。例如,当一个事务管理器发现,一个分布式事务只由一个事务资源组成(即单一分支),则该资源可以被告知同时进行预备和提交。

分布式事务的语法

分布式事务(XA 事务)的 SQL 语法主要包括:

XA {START|BEGIN} xid [JOIN|RESUME]

XA START xid 用于启动一个带给定 xid 值的 XA 事务。每个 XA 事务必须有一个唯一的 xid 值,因此该值当前不能被其他的 XA 事务使用。

xid 是一个 XA 事务标识符,用来唯一标识一个分布式事务。xid 值由客户端提供,或由 MySQL 服务器生成。xid 值包含 1~3 个部分:

xid: gtrid [, bqual [, formatID ]]

  • gtrid 是一个分布式事务标识符,相同的分布式事务应该使用相同的 gtrid,这样可以明确知道 xa 事务属于哪个分布式事务。
  • bqual 是一个分支限定符,默认值是空串。对于一个分布式事务中的每个分支事务,bqual 值必须是唯一的。
  • formatID 是一个数字,用于标识由 gtrid 和 bqual 值使用的格式,默认值是 1。

下面其他 XA 语法中用到的 xid 值,都必须和 START 操作使用的 xid 值相同,也就是表示对这个启动的 XA 事务进行操作。

1
2
XA END xid [SUSPEND [FOR MIGRATE]]
XA PREPARE xid

使事务进入 PREPARE 状态,也就是两阶段提交的第一个提交阶段。

1
2
XA COMMIT xid [ONE PHASE]
XA ROLLBACK xid

这两个命令用来提交或者回滚具体的分支事务。也就是两阶段提交的第二个提交阶段,分支事务被实际的提交或者回滚。

1
XA RECOVER  返回当前数据库中处于 PREPARE 状态的分支事务的详细信息。

分布式的关键在于如何确保分布式事务的完整性,以及在某个分支出现问题时的故障解决。XA 的相关命令就是提供给应用如何在多个独立的数据库之间进行分布式事务的管理,包括启动一个分支事务、使事务进入准备阶段以及事务的实际提交回滚操作等,如下所示的例子演示了一个简单的分布式事务的执行,事务的内容是在 DB1 中插入一条记录,同时在 DB2 中更新一条记录,两个操作作为同一事务提交或者回滚。

↓↓分布式事务例子↓↓

session_1 in DB1 session_2 in DB2
在数据库 DB1 中启动一个分布式事务的一个分支事务,xid 的 gtrid 为“test”,bqual 为“db1”: 在数据库 DB2 中启动分布式事务“test”的另外一个分支事务,xid 的 gtrid 为“test”,bqual 为“db2”:
mysql> xa start ‘test’,’db1’; mysql> xa start ‘test’,’db2’;
Query OK,0 rows affected(0.00 sec) Query OK,0 rows affected(0.00 sec)
分支事务 1 在表 actor 中插入一条记录: 分支事务 2 在表 film_actor 中更新了 23 条记录:
mysql> insert into actor (actor_id, first_name, last_name) values (301, ‘Simon’, ‘Tom’); mysql> update film_actor set last_update = now() where actor_id = 178;
Query OK,1 row affected(0.00 sec) Query OK,23 rows affected(0.04 sec) Rows matched:23 Changed:23 Warnings:0
对分支事务 1 进行第一阶段提交,进入 prepare 状态: 对分支事务 2 进行第一阶段提交,进入 prepare 状态:
mysql> xa end ‘test’,’db1’; mysql> xa end ‘test’,’db2’;
Query OK,0 rows affected(0.00 sec) Query OK,0 rows affected(0.00 sec)
mysql> xa prepare ‘test’,’db1’; mysql> xa prepare ‘test’,’db2’;
Query OK,0 rows affected(0.02 sec) Query OK,0 rows affected(0.02 sec)
用 xa recover 命令查看当前分支事务状态: 用 xa recover 命令查看当前分支事务状态:
mysql> xa recover \G mysql> xa recover \G
formatID: 1 formatID: 1
gtrid_length: 4 gtrid_length: 4
bqual_length: 3 bqual_length: 3
data: testdb1 data: testdb2
1 row in set(0.00 sec) 1 row in set(0.00 sec)
两个事务都进入准备提交阶段,如果之前遇到任何错误,都应该回滚所有的分支,以确保分布式事务的正确。 两个事务都进入准备提交阶段,如果之前遇到任何错误,都应该回滚所有的分支,以确保分布式事务的正确。
提交分支事务 1: 提交分支事务 2:
mysql> xa commit ‘test’,’db1’; mysql> xa commit ‘test’,’db2’;
Query OK,0 rows affected(0.03 sec) Query OK,0 rows affected(0.03 sec)
两个事务都到达准备提交阶段后,一旦开始进行提交操作,就需要确保全部的分支都提交成功。 两个事务都到达准备提交阶段后,一旦开始进行提交操作,就需要确保全部的分支都提交成功。

存在的问题

虽然 MySQL 支持分布式事务,但是在测试过程中,还是发现存在一些问题。

如果分支事务在达到 prepare 状态时,数据库异常重新启动,服务器重新启动以后,可以继续对分支事务进行提交或者回滚的操作,但是提交的事务没有写 binlog,存在一定的隐患,可能导致使用 binlog 恢复丢失部分数据。如果存在复制的数据库,则有可能导致主从数据库的数据不一致。

如果分支事务的客户端连接异常中止,那么数据库会自动回滚未完成的分支事务,如果此时分支事务已经执行到 prepare 状态,那么这个分布式事务的其他分支可能已经成功提交,如果这个分支回滚,可能导致分布式事务的不完整,丢失部分分支事务的内容。

如果分支事务在执行到 prepare 状态时,数据库异常,且不能再正常启动,需要使用备份和 binlog 来恢复数据,那么那些在 prepare 状态的分支事务因为并没有记录到 binlog,所以不能通过 binlog 进行恢复,在数据库恢复后,将丢失这部分的数据。

总结

MySQL 的分布式事务还存在比较严重的缺陷,在数据库或者应用异常的情况下,可能会导致分布式事务的不完整。如果应用对于数据的完整性要求不是很高,则可以考虑使用。如果应用对事务的完整性有比较高的要求,则不太推荐使用分布式事务。

以上大多摘自《深入浅出MySQL》


MySQL 中 MyISAM 和 InnoDB 存储引擎的区别

前言

和大多数数据库不同,MySQL 中有一个存储引擎的概念,针对不同的存储需求可以选择最优的存储引擎。

概述

插件式存储引擎是 MySQL 数据库最重要的特性之一,用户可以根据应用的需要选择如何存储和索引数据、是否使用事务等。MySQL 默认支持多种存储引擎,以适用于不同领域的数据库应用需要,用户可以通过选择使用不同的存储引擎提高应用的效率,提供灵活的存储,用户甚至可以按照自己的需要定制和使用自己的存储引擎,以实现最大程度的可定制性。

MySQL 5.0 支持的存储引擎包括 MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED 等,其中 InnoDB 和 BDB 提供事务安全表,其它存储引擎都是非事务安全表。

特性

下面列出几种常见的存储引擎,并对比之间的区别。

特点 MyISAM InnoDB MEMORY MERGE NDB
存储限制 64TB 没有
事务安全 支持
锁机制 表锁 行锁 表锁 表锁 行锁
B 树索引 支持 支持 支持 支持 支持
哈希索引 支持 支持
全文索引 支持
集群索引 支持
数据缓存 支持 支持 支持
索引缓存 支持 支持 支持 支持 支持
数据可压缩 支持
空间使用 N/A
内存使用 中等
批量插入的速度
支持外键 支持

本文重点介绍最常遇到的两种存储引擎:MyISAM 和 InnoDB。

MyISAM

MyISAM 是 MySQL 的默认存储引擎。MyISAM 不支持事务、也不支持外键,其优势是访问的速度快,对事务完整性没有要求或者以 SELECT、INSERT 为主的应用基本上都可以使用这个引擎来创建表。

每个 MyISAM 在磁盘上存储成 3 个文件,其文件名都和表名相同,但扩展名分别是:

  • .frm(存储表定义)
  • .MYD(MYData,存储数据)
  • .MYI(MYIndex,存储索引)

数据文件和索引文件可以放置在不同的目录,平均分布 IO,获得更快的速度。

MyISAM 类型的表可能会损坏,原因可能是多种多样的,损坏后的表可能不能访问,会提示需要修复或者访问后返回错误的结果。MyISAM 类型的表提供修复的工具,可以用 CHECK TABLE 语句来检查 MyISAM 表的健康,并用 REPAIR TABLE 语句修复一个损坏的 MyISAM 表。表损坏可能导致数据库异常重新启动,需要尽快修复并尽可能地确认损坏的原因。

MyISAM 的表又支持 3 种不同的存储格式,分别是:

  • 静态(固定长度)表
  • 动态表
  • 压缩表

其中,静态表是默认的存储格式。静态表中的字段都是非变长字段,这样每个记录都是固定长度的,这种存储方式的优点是存储非常迅速,容易缓存,出现故障容易恢复缺点是占用的空间通常比动态表多。静态表的数据在存储的时候会按照列的宽度定义补足空格,但是在应用访问的时候并不会得到这些空格,这些空格在返回给应用之前已经去掉。

InnoDB

InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比 MyISAM 的存储引擎,InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

自动增长列

InnoDB 表的自动增长列可以手工插入,但是插入的值如果是空或者 0,则实际插入的将是自动增长后的值。

可以通过 ALTER TABLE *** AUTO_INCREMENT = n 语句强制设置自动增长列的初始值,默认从 1 开始,但是该强制的默认值是保留在内存中的,如果该值在使用之前数据库重新启动,那么这个强制的默认值就会丢失,就需要在数据库启动以后重新设置。

可以使用 LAST_INSERT_ID() 查询当前线程最后插入记录使用的值。如果一次插入了多条记录,那么返回的是第一条记录使用的自动增长值。

对于 InnoDB 表,自动增长列必须是索引。如果是组合索引,也必须是组合索引的第一列,但是对于 MyISAM 表,自动增长列可以是组合索引的其它列,这样插入记录后,自动增长列是按照组合索引的前面几列进行排序后递增的。

外键约束

MySQL 支持外键的存储引擎只有 InnoDB,在创建外键的时候,要求父表必须有对应的索引,子表在创建外键的时候也会自动创建对应的索引。

在创建索引的时候,可以指定在删除、更新父表时,对子表进行的相应操作,包括 RESTRICT、CASCADE、SET NULL 和 NO ACTION。其中 RESTRICT 和 NO ACTION 相同,是指限制在子表有关联记录的情况下父表不能更新;CASCADE 表示父表在更新或者删除时,更新或者删除子表对应记录;SET NULL 则表示父表在更新或者删除的时候,子表的对应字段被 SET NULL。选择后两种方式的时候要谨慎,可能会因为错误的操作导致数据的丢失。

当某个表被其他表创建了外键参照,那么该表的对应索引或者主键禁止被删除。

存储方式

InnoDB 存储表和索引有以下两种方式。

  • 使用共享表空间存储,这种方式创建的表的表结构保存在 .frm 文件中,数据和索引保存在 innodb_data_home_dirinnodb_data_file_path 定义的表空间中,可以是多个文件。
  • 使用多表空间存储,这种方式创建的表的表结构仍然保存在 .frm 文件中,但是每个表的数据和索引单独保存在 .ibd 中。如果是个分区表,则每个分区对应单独的 .ibd 文件,文件名是“表名+分区名”,可以在创建分区的时候指定每个分区的数据文件的位置,以此来将表的 IO 均匀分布在多个磁盘上。

要使用多表空间的存储方式,需要设置参数 innodb_file_per_table,并重新启动服务后才可以生效,对于新建的表按照多表空间的方式创建,已有的表仍然使用共享表空间存储。如果将已有的多表空间方式修改回共享表空间的方式,则新建表会在共享表空间中创建,但已有的多表空间的表仍然保存原来的访问方式。所以多表空间的参数生效后,只对新建的表生效。

多表空间的数据文件没有大小限制,不需要设置初始大小,也不需要设置文件的最大限制、扩展大小等参数。

对于使用多表空间特性的表,可以比较方便地进行单表备份和恢复操作,但是直接复制 .ibd 文件是不行的,因为没有共享表空间的数据字典信息,直接复制的 .ibd 文件和 .frm 文件恢复时是不能被正确识别的,但可以通过以下命令:

1
2
ALTER TABLE tbl_name DISCARD TABLESPACE;
ALTER TABLE tbl_name IMPORT TABLESPACE;

将备份恢复到数据库中,但是这样的单表备份,只能恢复到表原来在的数据库中,而不能恢复到其他的数据库中。如果要将单表恢复到目标数据库,则需要通过 mysqldumpmysqlimport 来实现。

注意:即便在多表空间的存储方式下,共享表空间仍然是必须的,InnoDB 把内部数据词典和工作日志放在这个文件中。

适用环境

在选择存储引擎时,应根据应用特点选择合适的存储引擎,对于复杂的应用系统可以根据实际情况选择多种存储引擎进行组合。

  • MyISAM:默认的 MySQL 插件式存储引擎。如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常适合的。MyISAM 是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。
  • InnoDB:用于事务处理应用程序,支持外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询以外,还包括很多的更新、删除操作,那么 InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定,还可以确保事务的完整提交(Commit)和回滚(Rollback),对于类似计费系统或者财务系统等对数据准确性要求比较高的系统,InnoDB 都是合适的选择。

本文大多摘自《深入浅出MySQL》。


跳动一月(2019)

年前一个月,怎么能不跳动?

各大厂爆料不断,裁员信号到处闪烁,人心惶惶。

看到最多的一句话是:2019,保住饭碗!

裁团风波此起彼伏,有赞年会公然宣布 996,便利蜂裁员新招层出不穷,新东方的释放自我,微信、陌陌的令人羡慕……

据说 2018 是未来五年经济最好的一年,呜呼哀哉!

这是一个危机四伏的时代,同样也是一个充满机遇的时代,市场的一番大清洗将会把未来带向何处呢?

还是要保持自身的竞争力,技多不压身,仗技走天涯,学习思考不能断!

另外,保持健康最重要!2019 还是要坚持锻炼,自律。


我是 2018 年 3 月底来到现在这家公司的,也快满一年了。

很荣幸,被评为了 2018 年度最佳新人奖,也作为其中之一主持人主持了 2018 年的公司年会。

想想距离之前在大学做主持,已经过去了 5 年了,真是好快啊!

意料之中,抽奖无缘。一张来伊份购物卡,阳光普照。

年会结束跟同事又聚了下,说了不少东西。赚钱真不容易啊!

2019 年,毕业就快 2 年了,现在还是没有攒什么钱,对比别人真的感觉好失败。

打工没什么激情,创业没那个勇气,好难啊。


前段时间去看了场直火帮的现场,都市的夜晚是年轻人的狂欢。

白日里压抑的所有情绪都在夜晚爆发,肆意尖叫。

现场还是很不一样的,音乐鼓点直击心脏,就像是个泵在血海中抽放。

适当调节生活也是相当不错的。


在这里我要说说《大江大河》这部电视剧,真的很不错!

那几天一有空就看,晚上下班早早地就洗漱好躲被窝里看,本来被子就薄,还漏一个大口子,甚是好看甚是好看。

短短的电视剧中反映了很多东西,我真的是喜欢这类的作品。

例如:路遥《平凡的世界》,陈忠实《白鹿原》,余华《活着》、《兄弟》等作品。

现在怕是出不了这类大作了,时代不同了。


再过几天就回家过春节了,说到过年也是不怎么提得起兴致啊。

跟个国庆一样,回家呆一周就又得回来过上班日子了。

“回家就跟回娘家一样”难怪我爸如是说道……

谁想呢,我也不想啊,好难啊。

长大了,好多事情就自然而然变了。

小时候的过年总是伴着地上厚厚的积雪,一捆烟花棒,一盒火柴或是口袋里塞着个黑口打火机。

不一样咯。


最后,一段《站在悬崖边的我》

我站在悬崖边,视线慢慢下移
只看见一片山,飘着那一层云
都说居高思危,我却有点神往

那是一张床垫,还是一张薄纸
那下面是什么,是那汪洋大海
还是坚硬的沙石,血肉模糊

天色越来越暗,回去的路越来越糊
我站在悬崖边,开始犹豫

前方需要勇气,可能粉身碎骨
后方道路平坦,也许一世平庸

左右思索着,天却已经全黑了
我伸手,看不清楚自己的手指
一阵眩晕,脚下一滑,身体前倾

我还没来得及惊呼
眼睛就睁开了

站在悬崖边的我


LeetCode 之全排列(Permutations)

全排列问题在这里有两个版本,其中略有差异。看完就会感觉似曾相识,一种莫名的熟悉感从心底喷涌上来。

第一个版本:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
给定一个没有重复数字的序列,返回其所有可能的全排列。

示例:

输入: [1,2,3]
输出:
[
[1,2,3],
[1,3,2],
[2,1,3],
[2,3,1],
[3,1,2],
[3,2,1]
]

有什么感觉?

这不就是暗箱摸球,箱子里有不同颜色的球 n 个,列出你可能会摸出球的所有顺序,不放回。

先贴上大神的详细解析:链接

利用 List.add(int index, E element) 方法可以将元素插入指定的位置,可以满足题意。

整体分析:根据可插入的地方不同从而展开不同的分支,典型的树形结构。

代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
public List<List<Integer>> permute(int[] nums) {

List<List<Integer>> permutations = new ArrayList<>();
if (nums.length == 0)
return permutations;
helper(nums, 0, new ArrayList<>(), permutations);
return permutations;

}

/**
*
* @param nums
* 原数组
* @param start
* 选择填充数字下标
* @param permutation
* 单个集合
* @param permutations
* 目标返回集合
*/
private void helper(int[] nums, int start, List<Integer> permutation, List<List<Integer>> permutations) {

// 满足条件添加 返回
if (permutation.size() == nums.length) {
permutations.add(permutation);
return;
}
// 分别插入不同的位置
for (int i = 0; i <= permutation.size(); i++) {
// 避免在原集合上操作 需新集合
List<Integer> newPermutation = new ArrayList<>(permutation);
newPermutation.add(i, nums[start]);
helper(nums, start + 1, newPermutation, permutations);
}
}

注意每次插入前要 new 一个新集合对象,不能在原集合对象上操作。

第二个版本:

1
2
3
4
5
6
7
8
9
10
11
给定一个可包含重复数字的序列,返回所有不重复的全排列。

示例:

输入: [1,1,2]
输出:
[
[1,1,2],
[1,2,1],
[2,1,1]
]

与第一个版本区别是:该版本数组中可以有重复元素,且返回的集合中不能有重复的元素(即重复的集合排列顺序)。

去重,我首先想到的是这样:不管你序列中有没有重复的数字,我就当满足条件的时候判断下是否已经存过了该排列顺序不就行了。

然后我就将使用过的数字全都按顺序拼接成字符串,too young …

指定位置插入元素,List 很好实现,可是 String 怎么办。方法肯定有啊,比如每个元素之间用自定义分隔符拼接,再转成数组找到指定的位置插入,再转。只要功夫深,铁杵磨成针。可是麻烦啊,算暴力解法,遂弃之。

解题代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
public List<List<Integer>> permuteUnique(int[] nums) {
List<List<Integer>> permuteUniques = new ArrayList<>();
if (nums == null || nums.length == 0)
return permuteUniques;
// 要去重 先排序
Arrays.sort(nums);
boolean[] used = new boolean[nums.length];
dfs(nums, used, permuteUniques, new ArrayList<>());
return permuteUniques;
}

/**
*
* @param nums
* 原数组
* @param used
* 标记数字使用状态
* @param permuteUniques
* 目标集合
* @param permuteUnique
* 单个集合
*/
private void dfs(int[] nums, boolean[] used, List<List<Integer>> permuteUniques, List<Integer> permuteUnique) {
if (permuteUnique.size() == nums.length) {
permuteUniques.add(new ArrayList<>(permuteUnique));
return;
}
for (int i = 0; i < nums.length; i++) {
if (used[i]) // 已经标记过的略过
continue;
// 数字有重复的 说明刚释放的值与该值一样 略过
if (i > 0 && nums[i - 1] == nums[i] && !used[i - 1])
continue;
used[i] = true; // 标记使用
permuteUnique.add(nums[i]); // 该数字加入集合
// 重复操作 选择剩余数字
dfs(nums, used, permuteUniques, permuteUnique);
// 当出栈时将最后一个数从集合中删除 同时该数恢复未使用状态 继续操作
used[i] = false;
permuteUnique.remove(permuteUnique.size() - 1);
}

}

由于要去重,所以先将数组排序。与第一版本的大致思路一样,遍历数组将未标记的值插入。

1
2
if (i > 0 && nums[i - 1] == nums[i] && !used[i - 1]) // 数字有重复的 说明刚释放的值与该值一样 略过
continue;

这行代码至关重要,与出栈时候的 used[i] = false; 相对应,实现了重复操作不执行的功能。

同样,permuteUniques.add(new ArrayList<>(permuteUnique)); 也是需要 new 一个新对象塞入。