宝马娱乐在线城-宝马娱乐在线

来自 互联网络 2020-04-27 09:09 的文章
当前位置: 宝马娱乐在线城 > 互联网络 > 正文

MySQL中主键与rowid的使用陷阱总结

总结一下MySQL数据库查看无主键表的一些sql,一起来看看吧~

mysql select _rowid from redis_backup_result limit 5;+--------+| _rowid |+--------+| 117 || 118 || 119 || 120 || 121 |+--------+5 rows in set (0.00 sec)

概述

问题2、rowid和主键有什么关联关系

如果有多条"非空唯一索引",根据索引索引的先后顺序,选择第一个定义的非空唯一索引为主键。

问题1、如何感受到rowid的存在

2、查看无主键表

rowid调试参考了丁奇的博客

总结

mysql select backup_date from redis_backup_result limit 10;+-------------+| backup_date |+-------------+| 2018-08-14 || 2018-08-14 || 2018-08-14 || 2018-08-15 || 2018-08-15 || 2018-08-15 || 2018-08-15 || 2018-08-15 || 2018-08-15 || 2018-08-15 |+-------------+10 rows in set (0.00 sec)
--查看表主键信息 SELECT t.TABLE_NAME, t.CONSTRAINT_TYPE, c.COLUMN_NAME, c.ORDINAL_POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t, INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c WHERE t.TABLE_NAME = c.TABLE_NAME AND t.CONSTRAINT_TYPE = 'PRIMARY KEY' AND t.TABLE_NAME = 'TABLE_NAME' AND t.TABLE_SCHEMA = 'TABLE_SCHEMA';

但是比较好奇这个解析的逻辑,看起来是SQL解析了第一行,然后输出了count(*)的操作,显然这是从执行计划中无法得到的信息。

1、查看表主键信息

从以上可以看到,MySQL对于主键有一套维护机制,而一些常见的索引也会产生相应的影响,比如唯一性索引、非唯一性索引、覆盖索引等都是辅助索引,从存储的角度来说,二级索引列中默认包含主键列,如果主键太长,也会使得二级索引很占空间。

如果表定义时没有显示定义主键,则会按照以下方式选择或创建主键:

宝马娱乐在线城,光说不练假把式,我们可以做一个测试来说明。

1) 先判断表中是否有"非空的唯一索引",如果有

这就引出行业里非常普遍的主键性能问题,这不是一个单一的问题,需要MySQL方向持续改造的,将技术价值和业务价值结合起来。我看到很多业务中设置了自增列,但是大多数情况下,这种自增列却没有实际的业务含义,尽管是主键列保证了ID的唯一性,但是业务开发无法直接根据主键自增列来进行查询,于是他们需要寻找新的业务属性,添加一系列的唯一性索引,非唯一性索引等等,这样一来我们坚持的规范和业务使用的方式就存在了偏差。

如果主键索引只有一个索引键,那么可以使用_rowid来显示主键,实验测试如下:

为了验证,我们可以使用_rowid的方式来做初步的验证。

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

自己仔细看了下SQL,发现是没有加group by,我们随机查出10条数据。

2) 如果表中无"非空唯一索引",则自动创建一个6字节大小的指针作为主键。

create table test_inc(id int) engine=innodb;

可以发现,上面的_rowid与id的值相同,因为id列是表中第一个唯一且NOT NULL的索引。

2)通过ps -ef|grep mysql得到对应的进程号,使用gdb来开始做下调试配置,切记!此处应该是自己的测试环境。

如果仅有一条"非空唯一索引",则该索引为主键

从另外一个维度来说,我们对于主键的理解是有偏差的,我们不能单一的认为主键就一定是从1开始的整数类型,我们需要结合业务场景来看待,比如我们的身份证其实就是一个不错的例子,把证号分成了几个区段,偏于检索和维护;或者是外出就餐时得到的流水单号,它都有一定的业务属性在里面,对于我们去理解业务的使用是一种不错的借鉴。

--删除测试表DROP TABLE IF EXISTS t1;--创建测试表CREATE TABLE `t1` ( `id` int(11) NOT NULL, `c1` int(11) DEFAULT NULL, UNIQUE uni_id (id), INDEX idx_c1(c1)) ENGINE = InnoDB CHARSET = utf8;--插入测试数据INSERT INTO t1 (id, c1) SELECT 1, 1;INSERT INTO t1 (id, c1) SELECT 2, 2;INSERT INTO t1 (id, c1) SELECT 4, 4;--查看数据和_rowidSELECT *, _rowid FROM t1;

除了规范,从存储方式上来说,在InnoDB存储引擎中,表都是按照主键的顺序进行存放的,我们叫做聚簇索引表或者索引组织表,表中主键的参考依据如下:

在Innodb存储引擎中,每张表都会有主键,数据按照主键顺序组织存放,该类表成为索引组织表 Index Ogranized Table

mysql select backup_date ,count(*) piece_no from redis_backup_result;+-------------+----------+| backup_date | piece_no |+-------------+----------+| 2018-08-14 | 40906 |+-------------+----------+1 row in set (0.03 sec)

3、无主键表

mysql SELECT @rowno:=@rowno+1 as rowno,r._rowid from redis_backup_resultr ,(select @rowno:=0) t limit 20;+-------+--------+| rowno | _rowid |+-------+--------+| 1 | 117 || 2 | 118 || 3 | 119 || 4 | 120 || 5 | 121 || 6 | 122 || 7 | 123 || 8 | 124 || 9 | 125 || 10 | 126 || 11 | 127 || 12 | 128 || 13 | 129 || 14 | 130 || 15 | 131 || 16 | 132 || 17 | 133 || 18 | 134 || 19 | 135 || 20 | 136 |+-------+--------+20 rows in set (0.00 sec)
--查看无主键表SELECT table_schema, table_name,TABLE_ROWSFROM information_schema.tablesWHERE (table_schema, table_name) NOT IN (SELECT DISTINCT table_schema, table_nameFROM information_schema.columnsWHERE COLUMN_KEY = 'PRI')AND table_schema NOT IN ('sys', 'mysql', 'information_schema', 'performance_schema');
mysql show create table test_incG*************************** 1. row *************************** Table: test_incCreate Table: CREATE TABLE `test_inc` ( `id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

通过这个案例,可以很明显发现是第1行的记录,然后做了count(*)的操作。

3)我们做下基本检验,得到建表语句,保证测试是预期的样子。

mysql insert into test_inc values(4),(5),(6); Query OK, 3 rows affected (0.07 sec)Records: 3 Duplicates: 0 Warnings: 0

写一个完整的语句,如下:

显式的创建主键Primary key。 判断表中是否有非空唯一索引,如果有,则为主键。 如果都不符合上述条件,则会生成6个字节的bigint unsigned值。

记得有一天统计备份数据的时候,写了一条SQL,当看到执行结果时才发现SQL语句没有写完整,在完成统计工作之后,我准备分析下这条SQL语句。

前言

如何感受到rowid的存在; rowid和主键有什么关联关系; 在主键的使用中存在哪些隐患; 如何来理解rowid的潜在瓶颈并调试验证。

而有了主键之后,上面这个瓶颈似乎就不存在了。

由此,我们可以看到rowid自增后,还是存在使用瓶颈,当然这个概率是很低的,需要自增列的值到281万亿,这是一个相当庞大的数值了,从功能上来说,应该抛出写入重复值的错误更为合理。

问题4、如何来理解rowid的潜在瓶颈并进行调试验证

本文由宝马娱乐在线城发布于互联网络,转载请注明出处:MySQL中主键与rowid的使用陷阱总结

关键词: