简单分析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 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。

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索mysql
mysql primary key、mysql修改primary key、mysql设置primary key、mysql删除primary key、mysql primarykey，以便于您获取更多的相关知识。

时间： 2024-07-28 15:38:10

简单分析MySQL中的primary key功能_Mysql的相关文章

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 pref

简单介绍MySQL中索引的使用方法_Mysql

数据库索引是一个数据结构,提高操作的速度,在一个表中可以使用一个或多个列,提供两个快速随机查找和高效的顺序访问记录的基础创建索引. 在创建索引时,它应该被认为是将SQL查询的那些列上创建一个或多个索引的列. 实际上,指数也保持主键或索引字段和指针的实际表中每条记录的表型. 用户无法看到索引,它们只是用来加快查询速度,将用于数据库搜索引擎找到的记录速度非常快. INSERT和UPDATE语句表上的索引需要更多的时间,成为快速对这些表的SELECT语句.究其原因是,当进行插入或更新,数据库以及需要惰

分析MySQL中优化distinct的技巧_Mysql

有这样的一个需求:select count(distinct nick) from user_access_xx_xx; 这条sql用于统计用户访问的uv,由于单表的数据量在10G以上,即使在user_access_xx_xx上加上nick的索引, 通过查看执行计划,也为全索引扫描,sql在执行的时候,会对整个服务器带来抖动: root@db 09:00:12>select count(distinct nick) from user_access; +--------+ | count(dis

简单讲解MySQL中的多源复制_Mysql

 近日ORACLE发布几个新的功能在最新的Mysql5.7.2的版本上,由此有了此篇文章.大多数的改善是在数据库性能和复制相关的功能上,这个新版本会带给我们不可思议的效果. 在这篇文章里,我将要用一些简单的步奏来尝试了解这新的多源复制工作原理以及我们怎样进行自己的测试.需要说明的是,这还是一个开发版本,不是给生产环境准备的.因此这篇文章是打算给那些想了解此新功能的人,看看它是如何在应用中工作的,都是在临时环境中进行相关操作. 什么是多源复制? 首先,我们需要清楚 multi-master 与mu

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中的cardinality异常_Mysql

前段时间,一大早上,就收到报警,警告php-fpm进程的数量超过阈值.最终发现是一条sql没用到索引,导致执行数据库查询慢了,最终导致php-fpm进程数增加.最终通过analyze table feed_comment_info_id_0000 命令更新了Cardinality ,才能再次用到索引. 排查过程如下: sql语句: select id from feed_comment_info_id_0000 where obj_id=101 and type=1; 索引信息: show in

分析MySQL中索引引引发的CPU负载飙升的问题_Mysql

收到一个mysql服务器负载告警,上去一看,load average都飙到280多了,用top一看,CPU跑到了336%,不过IO和内存的负载并不高,根据经验,应该又是一起索引引起的惨案了. 看下processlist以及slow query情况,发现有一个SQL经常出现,执行计划中的扫描记录数看着还可以,单次执行耗时为0.07s,还不算太大.乍一看,可能不是它引发的,但出现频率实在太高,而且执行计划看起来也不够完美: mysql> explain SELECT count(1) FROM a

mysql中自增auto_increment功能的相关设置及问题_Mysql

mysql中的自增auto_increment功能相信每位phper都用过,也都知道如何设置字段为自增字段,但并不是所有phper都知道auto_increment的起始值和递增量是如何设置的!本文就为大家分享一下mysql字段自增功能的具体查看及设置方法. 涉及知识介绍: mysql服务器维护着2种mysql的系统参数(系统变量):全局变量(global variables)和会话变量(session variables).它们的含义与区别如其各占的名称所示,session variables

简单介绍MySQL中的事务机制

  从一个问题开始 最近银行这个事情闹的比较厉害啊,很多储户的钱放在银行,就不翼而飞了,而银行还不管不问,说是用户的责任,打官司,用户还能输了,这就是"社会主义".咱还是少发牢骚,多种树,莫谈国事. 说到银行存钱,就不得不说一下从银行取钱这件事情,从ATM机取钱这件简单的事情,实际上主要分为以下几个步骤: 登陆ATM机,输入密码; 连接数据库,验证密码; 验证成功,获得用户信息,比如存款余额等; 用户输入需要取款的金额,按下确认键; 从后台数据库中减掉用户账户上的对应金额; ATM吐出