首页 / MYSQL / 简单分析MySQL中的primarykey功能

简单分析MySQL中的primarykey功能

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了简单分析MySQL中的primarykey功能，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9863字，纯文字阅读大概需要15分钟。

内容图文

在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区别

内容总结

以上是互联网集市为您收集整理的简单分析MySQL中的primarykey功能全部内容，希望文章能够帮你解决简单分析MySQL中的primarykey功能所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/584417.html

来源：【匿名】

【上一篇】MySQL中DATE_FORMATE函数使用时的注意点【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【简单分析MySQL中的primarykey功能】教程文章相关的互联网学习教程文章

mysqlsla 分析mysql慢查询日志

发现有一个工具mysqlsla，分析查询日志比 mysqldumpslow分析的会更清晰明了！安装mysqlsla：下载mysqlsla-2.03.tar.gz[root@yoon export]# tar -xvf mysqlsla-2.03.tar.gz[root@yoon export]# yum install *DBI*[root@yoon mysqlsla-2.03]# perl Makefile.PLCan‘t locate Time/HiRes.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/...

MySql按指定天数进行分组数据统计分析 2【代码】【图】

上次的随笔1中写的分组方式，经分析，是从前往后进行分组，即若选择2014的数据进行统计每11天为一组的话，1的分组方式，按照2014-01-01——2014-01-11为一组，之后每11天为一组。但如果想按以最近11天为一组的话，就要使用下面的SQL进行查询，SQL中的关键是groupNum的获取，YEAR(date)* 1000 为区分年份；( DAYOFYEAR(date)+ 11 - MOD(DAYOFYEAR(CURDATE()) ,11)) DIV 11 下划线部分为增加的偏移量，以满足按照最近11天进行分组注...

MySql 三大知识点，索引、锁、事务，原理分析【图】

1.索引索引，类似书籍的目录，可以根据目录的某个页码立即找到对应的内容。索引的优点：1. 天生排序，2. 快速查找。索引的缺点：1. 占用空间，2. 降低更新表的速度。注意点：小表使用全表扫描更快，中大表才使用索引。超级大表索引基本无效。索引从实现上说，分成 2 种：聚集索引和辅助索引（也叫二级索引或者非聚集索引）从功能上说，分为 6 种：普通索引，唯一索引，主键索引，复合索引，外键索引，全文索引。详细说说 6 种索引：...

MYSQL索引分析和优化设计方案

一、什么是索引？索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时 MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。假设我们创建了...

[转]mysql索引结构原理、性能分析与优化

第一部分：基础知识索引官方介绍索引是帮助MySQL高效获取数据的数据结构。笔者理解索引相当于一本书的目录，通过目录就知道要的资料在哪里，不用一页一页查阅找出需要的资料。唯一索引(unique index) 强调唯一，就是索引值必须唯一。创建索引：create unique index 索引名 on 表名(列名);alter table 表名 add unique index 索引名 (列名);删除索引：drop index 索引名 on 表名;alter table 表名 drop index 索引名;主键主键就...

MySQL 多表查询实现分析【代码】

1、查看第一个表 mytable 的内容： mysql> select * from mytable; +----------+------+------------+-----------+ | name | sex | birth | birthaddr | +----------+------+------------+-----------+ | abccs |f | 1977-07-07 | china | | mary |f | 1978-12-12 | usa | | tom |m | 1970-09-02 | usa | +----------+------+------------+-----------+ 2、创建第二个表 title （包括作者、文章标题、发表日期）: mysql> create ta...

深入分析MySQL ERROR 1045 (28000)【图】

这几天在MySQL新建用户后，出现访问拒绝的问题，错误码为ERROR 1045(28000)。在网上搜索了很久，找到了很多解决办法，但很遗憾的是这么多办法没有一个能解决该问题。虽然出现的错误码28000很多人都遇到过，但原因也有所不同，有的是mysql.user表中没有信息，有的是root用户没有密码（那就不用密码登录），而使用mysql-5.6.19时，mysql.user有用户信息，root用户没有密码，采用的方法是root用户登录时输入空密码，登录成功。使用roo...

MYSQL数据表损坏的原因分析和修复方法小结(推荐)

1.表损坏的原因分析以下原因是导致mysql 表毁坏的常见原因： 1、服务器突然断电导致数据文件损坏。 2、强制关机，没有先关闭mysql 服务。 3、 mysqld 进程在写表时被杀掉。 4、使用myisamchk 的同时，mysqld 也在操作表。 5、磁盘故障。 6、服务器死机。 7、 mysql 本身的bug 。 2.表损坏的症状一个损坏的表的典型症状如下： 1 、当在从表中选择数据之时，你得到如下错误： Incorrect key file for table: ‘...‘. Try to ...

mysql优化--explain分析sql语句执行效率

Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。Explain语法：explain select … from … [where …]例如：explain select * from news;输出：+----+-------------+-------+-------+-------------------+---------+---------+-------+------| id | select_type | table | typ...

MySQL server has gone away报错原因分析/【代码】

在平时和开发的交流以及在论坛回答问题的或称中会发现这个问题被问及的频率非常高。程序中报错： MySQL server has gone away 是什么意思？如何避免？因此，感觉有必要总结一下发生这个问题的原因。今天正好看到一篇外文blog总结的比较好，就翻译过来了原文：http://ronaldbradford.com/blog/sqlstatehy000-general-error-2006-mysql-server-has-gone-away-2013-01-02/ 原因1. MySQL 服务宕了判断是否属于这个原因的方法很简单，...

向MySQL数据库插入数据出现乱码的情况分析【代码】【图】

（1）第一种情况在新建数据库时（2）第二种情况就是，IDE环境里面配置编码设置为UTF-8（3）第三种情况就是连接数据库时，没有设置编码。这个是最常规的。这个看起来很容易解决，但是需要注意MySQL版本的问题，比如说5.6版本链接URL设置编码的尾缀可能就是：?useOldAliasMetadataBehavior=true&useUnicode=true&CharsetEncode=utf8&useSSL=false；而之前的版本是：?useUnicode=true&characterEncoding=UTF-8。　　如果因为这种疏忽搞...

MySQL Order By Rand()效率分析

最近由于需要大概研究了一下MYSQL的随机抽取实现方法。举个例子，要从tablename表中随机提取一条记录，大家一般的写法就是：SELECT * FROM tablename ORDER BY RAND() LIMIT 1。但是，后来我查了一下MYSQL的官方手册，里面针对RAND()的提示大概意思就是，在ORDER BY从句里面不能使用RAND()函数，因为这样会导致数据列被多次扫描。但是在MYSQL 3.23版本中，仍然可以通过ORDER BY RAND()来实现随机。但是真正测试一下才发现这样效率...

mysql慢查询分析工具比较与实战【代码】

00 前言在进行mysql性能优化的时候，第一个想到的便是查看慢sql。但是对于慢sql有没有什么好的工具进行分析呢？推荐两个工具mysqldumpslow及pt-query-digest。mysqlslowdump较为简单，常用命令：#得到返回记录最多的20个sql mysqldumpslow -s r -t 20 slowSQl.log# 得到平均访问次数最多的20条sql mysqldumpslow -s ar -t 20 slowSQl.log 如果linux上没有安装mysqldumpslow，yum install安装下就行了。本文主要说下pt-query-digest...

kettle连接mysql数据库并进行数据分析【图】

1、数据库链接驱动如果没有安装对应的数据库链接驱动，在数据库链接的过程中，可能会报某个数据库连接找不到的异常，因此需要下载对应驱动后（安装步骤可以参见“怎么在官网上下载java连接mysql的驱动jar包？”），放入kettle的lib文件夹。2、建立转换本文中使用的kettle版本，启动Spoon.bat或者Spoon.sh脚本。在文件->新建装换。新建转换后在左边的主对象树中建立DB连接用以连接数据库。建立数据库连接的过程与其他数据库管理软件...

MySQL之架构简单分析【代码】【图】

上图为MySQL的简易架构图，给您有一个大概的概念，下面我将为您进行进一步的分析。连接器：当连接MySQL数据库时，等待的将是MySQL服务端的连接器；连接器的职责是和客户端建立连接、获取权限、维持和管理连接。客户端连接命令一般是如下所示（建议：不要在命令中显示添加登入密码）：mysql -h$ip -p$port -u$user-p$password查询缓存：建立完连接后，就可执行select语句。执行就会走向查询缓存。MySQL拿到这个查询请求后，就会先到...

首页 / MYSQL / 简单分析MySQL中的primarykey功能

简单分析MySQL中的primarykey功能

内容导读

内容图文

您可能感兴趣的文章:

内容总结

内容备注

内容手机端

【简单分析MySQL中的primarykey功能】教程文章相关的互联网学习教程文章

mysqlsla 分析mysql慢查询日志

MySql按指定天数进行分组数据统计分析 2【代码】【图】

MySql 三大知识点，索引、锁、事务，原理分析【图】

MYSQL索引分析和优化设计方案

[转]mysql索引结构原理、性能分析与优化

MySQL 多表查询实现分析【代码】

深入分析MySQL ERROR 1045 (28000)【图】

MYSQL数据表损坏的原因分析和修复方法小结(推荐)

mysql优化--explain分析sql语句执行效率

MySQL server has gone away报错原因分析/【代码】

向MySQL数据库插入数据出现乱码的情况分析【代码】【图】

MySQL Order By Rand()效率分析

mysql慢查询分析工具比较与实战【代码】

kettle连接mysql数据库并进行数据分析【图】

MySQL之架构简单分析【代码】【图】

MYSQL - 相关标签

功能 - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程