Mysql中对primary key一点选择改变

在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中测试：

$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：

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：

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

$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：

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在选择方式上也在慢慢智能了。

观察性能：

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 |

+——————————–+———-+———-+————+————–+—————+

对比性能：

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。

时间： 2024-09-08 10:04:10

Mysql中对primary key一点选择改变的相关文章

简单分析MySQL中的primary key功能_Mysql

在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 pref

mysql中ON DUPLICATE KEY UPDATE语法分析

[ON DUPLICATE KEY UPDATE用武之地] 如果表里没有则insert,若有了则update就需要用到mysql ON DUPLICATE KEY UPDATE语法;就样就可以在代码里少写if语句来判断了 在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE:如果不会导致唯一值列重复的问题,则插入新行. [实例讲解]     表结构       

mysql中key 、primary key 、unique key 与index区别_Mysql

mysql中索引是非常重要的知识点,相比其他的知识点,索引更难掌握,并且mysql中的索引种类也有很多,比如primary key .unique key 与index等等,本文章向大家介绍mysql中key .primary key .unique key 与index区别.  一.key与primary key区别 CREATE TABLE wh_logrecord ( logrecord_id int(11) NOT NULL auto_increment, user_name varch

mysql-Mysql中index与key的区别

问题描述 Mysql中index与key的区别 -------------------------+ | user | CREATE TABLE user (id int(11) NOT NULL,name varchar(50) default NULL,companyId int(11) default NULL, PRIMARY KEY (id), KEY company_user_id (companyId) ) ENGINE=InnoDB DEFAULT CHARSET=gbk | 上

MySQL中复制数据表中的数据到新表中的操作教程_Mysql

MySQL是不支持SELECT - INTO语法的,使用INSERT INTO - SELECT替代相同用法,下面我们我们这里简答分一下新表存在和不存在两种情况,具体使用不同的语句.1.新表不存在复制表结构即数据到新表 create table new_table select * from old_talbe; 这种方法会将old_table中所有的内容都拷贝过来,用这种方法需要注意,new_table中没有了old_table中的primary key,Extra,auto_incremen

Using INSERT IGNORE with MySQL to prevent duplicate key errors

An error will occur when inserting a new record in MySQL if the primary key specified in the insert query already exists. Using the "IGNORE" keyword prevents errors from occuring and other queries are still able to be run. Why? Although you shou

mysql中key 、primary key 、unique key 与index区别

问题描述 请详细说明下 mysql中key .primary key .unique key 与index区别 解决方案 参考下这个链接:http://zccst.iteye.com/blog/1697043

Mysql中unique与primary约束的区别分析

定义了UNIQUE约束的字段中不能包含重复值,可以为一个或多个字段定义UNIQUE约束,因此,UNIQUE即可以在字段级也可以在表级定义,在UNIQUE约束的字段上可以包含空值. ORACLE自动会为具有PRIMARY KEY约束的字段(主码字段)建立一个唯一索引和一个NOT NULL约束,定义PRIMARY KEY约束时可以为它的索引: UNIQUED 可空,可以在一个表里的一个或多个字段定义:   PRIMARY KEY不可空不可重复,在一个表里可以定义联合主键:简单的说, primary

vs2010 sql2008-违反了primary key约束pk_logininfo,不能在对象dbo_logininfo中插入重复键.

问题描述 违反了primary key约束pk_logininfo,不能在对象dbo_logininfo中插入重复键. 保存时出错! private void employeeInfoBindingNavigatorSaveItem_Click(object sender, EventArgs e) { this.Validate(); try { BankDataContext c = new BankDataContext(); LoginInfo t = new LoginInfo();