简单分析MySQL中的primary key功能
时间:2015-05-11 01:30 来源:linux.it.net.cn 作者:IT
MySQL中的primary key功能,包括讲到了其对InnoDB使用的影响
在5.1.46中优化器在对primary key的选择上做了一点改动：
Performance: While looking for the shortest index for a covering index scan, the optimizer did not consider the full row length for a clustered primary key, as in InnoDB. Secondary covering indexes will now be preferred, making full table scans less likely。
该版本中增加了find_shortest_key函数，该函数的作用可以认为是选择最小key length的
索引来满足我们的查询。
该函数是怎么工作的：
代码如下:
What find_shortest_key should do is the following. If the primary key is a covering index
and is clustered, like in MyISAM, then the behavior today should remain the same. If the
primary key is clustered, like in InnoDB, then it should not consider using the primary
key because then the storage engine will have to scan through much more data.
调用Primary_key_is_clustered()，当返回值为true，执行find_shortest_key：选择key length最小的覆盖索引（Secondary covering indexes），然后来满足查询。
首先在5.1.45中测试：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$mysql -V
mysql Ver 14.14 Distrib 5.1.45, for unknown-linux-gnu (x86_64) using EditLine wrapper
root@test 03:49:45>create table test(id int,name varchar(20),name2 varchar(20),d datetime,primary key(id)) engine=innodb;
Query OK, 0 rows affected (0.16 sec)
root@test 03:49:47>insert into test values(1,'xc','sds',now()),(2,'xcx','dd',now()),(3,'sdds','ddd',now()),(4,'sdsdf','dsd',now()),(5,'sdsdaa','sds',now());
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
root@test 03:49:51>
root@test 03:49:51>insert into test values(6,'xce','sdsd',now()),(7,'xcx','sdsd',now()),(8,'sdds','sds',now()),(9,'sdsdsdf','sdsdsd',now()),(10,'sdssdfdaa','sdsdsd',now());
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
创建索引ind_1：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
root@test 03:49:53>alter table test add index ind_1(name,d);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@test 03:50:08>explain select count(*) from test;
+—-+————-+——-+——-+—————+———+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———+———+——+——+————-+
| 1 | SIMPLE | test | index | NULL | PRIMARY | 4 | NULL | 10 | Using index |
+—-+————-+——-+——-+—————+———+———+——+——+————-+
1 row in set (0.00 sec)
添加ind_2：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
root@test 08:04:35>alter table test add index ind_2(d);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@test 08:04:45>explain select count(*) from test;
+—-+————-+——-+——-+—————+———+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———+———+——+——+————-+
| 1 | SIMPLE | test | index | NULL | PRIMARY | 4 | NULL | 10 | Using index |
+—-+————-+——-+——-+—————+———+———+——+——+————-+
1 row in set (0.00 sec)
上面的版本【5.1.45】中，可以看到优化器选择使用主键来完成扫描，并没有使用ind_1，ind_2来完成查询；
接下来是：5.1.48
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
$mysql -V
mysql Ver 14.14 Distrib 5.1.48, for unknown-linux-gnu (x86_64) using EditLine wrapper
root@test 03:13:15> create table test(id int,name varchar(20),name2 varchar(20),d datetime,primary key(id)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
root@test 03:48:04>insert into test values(1,'xc','sds',now()),(2,'xcx','dd',now()),(3,'sdds','ddd',now()),(4,'sdsdf','dsd',now()),(5,'sdsdaa','sds',now());
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
root@test 03:48:05>insert into test values(6,'xce','sdsd',now()),(7,'xcx','sdsd',now()),(8,'sdds','sds',now()),(9,'sdsdsdf','sdsdsd',now()),(10,'sdssdfdaa','sdsdsd',now());
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
创建索引ind_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
root@test 03:13:57>alter table test add index ind_1(name,d);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@test 03:15:55>explain select count(*) from test;
+—-+————-+——-+——-+—————+——-+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+——-+———+——+——+————-+
| 1 | SIMPLE | test | index | NULL | ind_1 | 52 | NULL | 10 | Using index |
+—-+————-+——-+——-+—————+——-+———+——+——+————-+
root@test 08:01:56>alter table test add index ind_2(d);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
添加ind_2：
root@test 08:02:09>explain select count(*) from test;
+—-+————-+——-+——-+—————+——-+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+——-+———+——+——+————-+
| 1 | SIMPLE | test | index | NULL | ind_2 | 9 | NULL | 10 | Using index |
+—-+————-+——-+——-+—————+——-+———+——+——+————-+
1 row in set (0.00 sec)
版本【5.1.48】中首先明智的选择ind_1来完成扫描，并没有考虑到使用主键（全索引扫描）来完成查询，随后添加ind_2，由于 ind_1的key长度是大于ind_2 key长度，所以mysql选择更优的ind_2来完成查询，可以看到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
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
55
56
57
58
59
60
61
62
63
5.1.48
root@test 08:49:32>set profiling =1;
Query OK, 0 rows affected (0.00 sec)
root@test 08:49:41>select count(*) from test;
+———-+
| count(*) |
+———-+
| 5242880 |
+———-+
1 row in set (1.18 sec)
root@test 08:56:30>show profile cpu,block io for query 1;
+——————————–+———-+———-+————+————–+—————+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+——————————–+———-+———-+————+————–+—————+
| starting | 0.000035 | 0.000000 | 0.000000 | 0 | 0 |
| checking query cache for query | 0.000051 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| Table lock | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000015 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000015 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 1.178452 | 1.177821 | 0.000000 | 0 | 0 |
| end | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000040 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000086 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000006 | 0.000000 | 0.000000 | 0 | 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
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
55
56
57
58
59
60
61
62
63
5.1.45
root@test 08:57:18>set profiling =1;
Query OK, 0 rows affected (0.00 sec)
root@test 08:57:21>select count(*) from test;
+———-+
| count(*) |
+———-+
| 5242880 |
+———-+
1 row in set (1.30 sec)
root@test 08:57:27>show profile cpu,block io for query 1;
+——————————–+———-+———-+————+————–+—————+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+——————————–+———-+———-+————+————–+—————+
| starting | 0.000026 | 0.000000 | 0.000000 | 0 | 0 |
| checking query cache for query | 0.000041 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| Table lock | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000015 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 1.294178 | 1.293803 | 0.000000 | 0 | 0 |
| end | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000040 | 0.000000 | 0.001000 | 0 | 0 |
| logging slow query | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000080 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
+——————————–+———-+———-+————+————–+—————+
从上面的profile中可以看到在Sending data上，差异还是比较明显的，mysql不需要扫描整个表的页块，而是扫描表中索引key最短的索引页块来完成查询，这样就减少了很多不必要的数据。
PS:innodb是事务引擎，所以在叶子节点中除了存储本行记录外，还会多记录一些关于事务的信息（DB_TRX_ID ,DB_ROLL_PTR 等)，因此单行长度额外开销20个字节左右，最直观的方法是将myisam转为innodb，存储空间会明显上升。那么在主表为t(id,name,pk(id)),二级索引ind_name(name,id),这个时候很容易混淆，即使只有两个字段，第一索引还是比第二索引要大（可以通过innodb_table_monitor观察表的的内部结构）在查询所有id的时候,优化器还是会选择第二索引ind_name。
(责任编辑：IT)
| MySQL中的primary key功能,包括讲到了其对InnoDB使用的影响 在5.1.46中优化器在对primary key的选择上做了一点改动： Performance: While looking for the shortest index for a covering index scan, the optimizer did not consider the full row length for a clustered primary key, as in InnoDB. Secondary covering indexes will now be preferred, making full table scans less likely。 该版本中增加了find_shortest_key函数，该函数的作用可以认为是选择最小key length的 索引来满足我们的查询。 该函数是怎么工作的：
代码如下:
What find_shortest_key should do is the following. If the primary key is a covering index
and is clustered, like in MyISAM, then the behavior today should remain the same. If the primary key is clustered, like in InnoDB, then it should not consider using the primary key because then the storage engine will have to scan through much more data.
调用Primary_key_is_clustered()，当返回值为true，执行find_shortest_key：选择key length最小的覆盖索引（Secondary covering indexes），然后来满足查询。 首先在5.1.45中测试：
创建索引ind_1：
添加ind_2：
上面的版本【5.1.45】中，可以看到优化器选择使用主键来完成扫描，并没有使用ind_1，ind_2来完成查询； 接下来是：5.1.48
创建索引ind_1：
版本【5.1.48】中首先明智的选择ind_1来完成扫描，并没有考虑到使用主键（全索引扫描）来完成查询，随后添加ind_2，由于 ind_1的key长度是大于ind_2 key长度，所以mysql选择更优的ind_2来完成查询，可以看到mysql在选择方式上也在慢慢智能了。 观察性能：
对比性能：
从上面的profile中可以看到在Sending data上，差异还是比较明显的，mysql不需要扫描整个表的页块，而是扫描表中索引key最短的索引页块来完成查询，这样就减少了很多不必要的数据。 PS:innodb是事务引擎，所以在叶子节点中除了存储本行记录外，还会多记录一些关于事务的信息（DB_TRX_ID ,DB_ROLL_PTR 等)，因此单行长度额外开销20个字节左右，最直观的方法是将myisam转为innodb，存储空间会明显上升。那么在主表为t(id,name,pk(id)),二级索引ind_name(name,id),这个时候很容易混淆，即使只有两个字段，第一索引还是比第二索引要大（可以通过innodb_table_monitor观察表的的内部结构）在查询所有id的时候,优化器还是会选择第二索引ind_name。 (责任编辑：IT) |