mysql primarykey_简单分析MySQL中的primarykey功能

最新推荐文章于 2024-05-14 17:47:05 发布
最新推荐文章于 2024-05-14 17:47:05 发布
阅读量228 收藏
点赞数
文章标签： mysql primarykey
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39903571/article/details/113909958
版权

在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 #1062 –Duplicate entry ''1'' for key ''PRIMARY''

MySQL里Create Index 能否创建主键 Primary Key

mysql 复制表结构和数据实例代码

PHP读MYSQL中文乱码的快速解决方法

php mysql连接数据库实例

JDBC 连接MySQL实例详解

MySQL截取和拆分字符串函数用法示例

简单谈谈MySQL中的int(m)

Mysql5.6启动内存占用过高解决方案

MySQL多线程复制遇到Error_code: 1872的解决方案

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

weixin_39903571
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
简单分析MySQL中的primary key功能
09-10
MySQL中的Primary Key是一种重要的数据库约束，它用于唯一标识表中的每一行数据，并且不允许有重复值。在数据库设计中，合理地使用Primary Key对于优化查询性能、保证数据完整性和提高数据库系统的整体效率至关重要...
MySQL主键（primary key）的作用
DATA8866的博客
05-01 9177
主要的作用主要确定该数据的唯一性。比如说ID=1,NAME=张三。我们要在数据库中，找到这条数据可以使用select * from 表 where id=1 这样就可以把张三查找出来了。而这个张三，也可以出现同名，所有用ID来做主键。而你说的insert into 是插入操作。当ID设置为了主键，再插入一个相同的主键值，就为报错误，并不会更新，你想要个更新就必须执行UPDATE。...
mysql的primary keys_简单分析MySQL中的primary key功能
weixin_33345090的博客
01-19 449
在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 In...
mysql中key 、primary key 、unique key 与index区别_mysql primary key和key
最新发布
2401_85014148的博客
05-14 1435
（因为虽然索引的多列组合是唯一的，比如ID+NAME是唯一的，但是每一个单独的列依然可以有重复的值，因为只要ID+NAME是唯一的即可）而Index则处于实现层面，比如可以对表的任意列建立索引，那么当建立索引的列处于SQL语句中的Where条件中时，就可以得到快速的数据定位，从而快速检索。则根据索引的原理，全NULL值不被记录在索引上，所以插入全NULL值时，可以有重复的，而其他的则不能插入重复值。于是乎，在设计表的时候，Key只是要处于模型层面的，而当需要进行查询优化，则对相关列建立索引即可。
mysql primary key key
han_dongwei的专栏
04-25 1053
primary key是主键 一般为自动增长并且是非空 int类型的，主要用来保证数据的唯一性 ； Key是索引约束，对表中字段进行约束索引的，都是通过primary foreign unique等创建的。 常见有foreign key，外键关联用的； MYSQL建立数据表时，KEY的设置有什么用？ key是指关键字，在每一个表所有记录中唯一，普通的表一般可以省去这一项。 另外，设置了
[Mysql] primary key 简明
...
12-28 1135
数据库创建过程中，建立了若干个column之后，就需要选择其中的一个column为primary key 本文简单地说明：1.什么是primary key 2.如何选择一个好的primary key 目录 1.情景 2.什么是primary key 3.选择一个好的primary key 1.情景 现在我们正在数据库中创建一张最常见用户表usertable用来存储用户的账号和密码...
MySQL数据库——MySQL主键（PRIMARY KEY）
热门推荐
Itmastergo的博客
04-19 1万+
主键（PRIMARY KEY）的完整称呼是“主键约束”，是MySQL 中使用最为频繁的约束。一般情况下，为了便于 DBMS 更快的查找到表中的记录，都会在表中设置一个主键。
MySQL中Multiple primary key defined报错的解决办法
09-09
在MySQL数据库中，主键（Primary Key）是用于唯一标识表中每一行数据的重要属性。一个表只能有一个主键，并且主键的值必须是唯一的，不允许为空。当尝试为表定义多个主键或者主键设置不当时，系统可能会抛出...
mysql中key 、primary key 、unique key 与index区别
09-10
在MySQL数据库中，`key`、`primary key`、`unique key`和`index`都是与数据表结构和查询性能紧密相关的概念。了解它们的区别对于优化数据库操作至关重要。 1. **Key** - Key是一种索引约束，它限制了表中特定列的...
MySQL---主键约束（primary key）
wuyomhchang的博客
04-11 5639
主键遵守规则 每个表只能定义一个主键。 主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在两行数据有相同的主键值。这是唯一性原则。 一个列名只能在复合主键列表中出现一次。 复合主键不能包含不必要的多余列。当把复合主键的某一列删除后，如果剩下的列构成的主键仍然满足唯一性原则，那么这个复合主键是不正确的。这是最小化原则。 特点 自带非空和唯一约束 用来标志当前记录的唯一性，区别于其他记录 一般是单个字段，也可以是联合多个字段
MySQL约束概述和MySQL主键（PRIMARY KEY）
m0_37449634的博客
12-15 791
在 MySQL 中，约束是指对表中数据的一种约束，能够帮助数据库管理员更好地管理数据库，并且能够确保数据库中数据的正确性和有效性。例如，在数据表中存放年龄的值时，如果存入 200、300 这些无效的值就毫无意义了。因此，使用约束来限定表中的数据范围是很有必要的。
mysql自增主键和mybatis自增主键不一致问题(已解决)：
qq_43473129的博客
09-28 1259
mysql自增主键和mybatis自增主键不一致问题(已解决)： 在插入数据前，先进行查询主键 <insert id="insertSelective"> <selectKey keyProperty="zxywzj" resultType="Integer"> select LAST_INSERT_ID() </selectKey> insert into tb_name (<include refid="i
数据库系统原理与应用教程（029）—— MySQL 的数据完整性（二）：定义主键（primary key）
睿思达DBA
07-17 2429
主键约束
MySQL主键约束详解：保障数据完整性与性能优化
霍格沃兹测试学院的博客
01-13 2644
主键（PRIMARY KEY）的完整称呼是“主键约束”，是 MySQL 中使用最为频繁的约束。一般情况下，为了便于 DBMS 更快的查找到表中的记录，都会在表中设置一个主键。MySQL是一种广泛使用的开源关系型数据库管理系统，其支持多种数据约束，其中主键约束是其中最重要的之一。本文将深入讨论MySQL主键约束的定义、优势、限制、创建和管理，以及在实践中的最佳实践。
java primary_PrimaryKey.java
weixin_30582289的博客
02-26 193
/** Copyright (C) 2016 The Android Open Source Project** Licensed under the Apache License, Version 2.0 (the "License");* you may not use this file except in compliance with the License.* You may obta...
mysql的primary key_简单分析MySQL中的primary key功能
weixin_28730403的博客
01-18 1039
在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 In...
PLSQL Tools for query primary key and foreign key of a table
engchina的专栏
02-03 395
SET VERIFY OFF SET SERVEROUTPUT ON DECLARE V_TABLE_NAME VARCHAR2(100); -- 表名 V_PK_NAME VARCHAR2(100); -- 主キー制約の名前 V_COLUMNS VARCHAR2(3000); -- 列名の取得(列1,列2,・・) BEGIN -- 表名の取得(キーボードから入力)
mysql PRIMARY KEY
09-04
MySQL中的PRIMARY KEY（主键）是一种约束，它用于唯一标识表中的每一行数据。每个表只能有一个主键，主键的值必须是唯一且不为空的。 在MySQL中，可以使用以下两种方式定义主键： 1. 在创建表时定义主键： ``` ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值