mysql 自增id 主键 与 普通key,查询速度 与 分页优化

探讨了在MySQL中使用自增长ID作为主键与普通key的查询性能差异,发现普通key在特定条件下查询速度更快,揭示了覆盖索引的概念及其在分页查询优化中的应用。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

自增长id,通常作为唯一标识。可以用于分页查询的优化。

这里记录一下自增id,看看是作为 primary key 查询更快、还是 普通key更快。

 

环境:mysql5.7

单表数据量:11773632 (1千万左右) 、字段数量20个。

 

1、id主键查询: 跳跃500万,查询10条数据。

可以看得出,走主键索引,查询所有列 与 只查id列,耗时基本相同。都是15秒左右。

 

2、id普通key查询:

走普通key索引,查询所有列没有走索引,属于全表扫描,速度很慢。但只查询id列时,速度确很快。

只查询id列,主键索引耗时 15秒、普通索引耗时 2秒。

1千万的数据,普通key速度快了 7倍!!

 

这种场景下,我们只需要根据查询条件得到10个id,10条数据一页。再根据id查询主表,得到最终数据。

这种优化,就类似于二级索引表一样。

 

个人分析结果:

主键索引中包含所有字段信息,所以查询较慢。

普通key索引包含信息少,并且查询内容就是索引本身,不需要二次查询。

“覆盖索引”就是这么个意思,查询条件与结果都在一个“索引表”中,所以速度快。

 

补充: 非主键自增ID,建表时可以这样:

 `id` bigint(20) NOT NULL AUTO_INCREMENT,
  KEY `i_id` (`id`)

 

分页优化的sql,主要有这3种形式,速度基本一致。

-- 1、2.085s
select * from t_bd_extension_show where id<=(select id from t_bd_extension_show order by id desc limit 5000000,1) order by id desc limit 10;

-- 2、2.071s
select * from t_bd_extension_show where id in(select t1.id from (select id from t_bd_extension_show order by id desc limit 5000000,10) t1);

-- 3、2.067s
SELECT t1.* FROM t_bd_extension_show t1 JOIN (select id from t_bd_extension_show order by id desc limit 5000000,10) t2 ON t1.id = t2.id;

第1种、外部还需要where一次,通常不推荐。

查询条件没变化时,子查询起始 id可以直接通过Table得到。(这样就不需要limit跳跃几百万的数据,也不需要知道查询条件的总数)

能够做成最简单的分页 ,只能上一页或下一页,所以速度也是最快的。

 

第2种 、第3种类似,所有条件都在子查询中处理。主查询只需要匹配id即可。

要求相对简单,个人推荐第3种。

 

 

 

 

 

 

<think>好的,我现在需要帮助用户了解MySQL中自ID的使用方法和实现原理。首先,我得回顾一下自己之前学过的相关知识,确保信息的准确性。记得自ID主要用于主键,自动生成唯一标识符,方便数据插入和管理。 用户提到了几个引用,我需要参考这些内容来组织回答。比如引用3提到自ID是自动生成唯一整数值的机制,通常在插入数据时自动分配。而引用4和引用5可能涉及自锁的模式和MyBatis如何获取自主键。另外,引用1和2提到了事务回滚可能导致ID跳跃的问题,这也是需要注意的地方。 首先,我应该从基本概念开始,解释什么是自ID,然后说明如何在建时使用AUTO_INCREMENT关键字。接着,可以举一个建的例子,比如创建用户主键设置为自。然后,插入数据时不需要指定主键,数据库会自动处理。 接下来,实现原理部分需要详细一些。根据引用3和4,自ID的原理涉及自计数器的存储,可能在内存中管理,但在MySQL 8.0之前是持久化到磁盘的。还要提到不同的锁模式,比如innodb_autoinc_lock_mode参数的不同设置,这影响并发插入时的ID分配方式。例如,模式1在确定数量的插入时批量获取ID,减少锁竞争,而模式2则是完全无锁,可能导致ID不连续。 然后,需要讨论注意事项,比如ID不连续的情况。事务回滚会导致自值不会回滚,所以会出现跳跃,这点引用2有提到。还有分库分时的管理问题,每个有自己的计数器,可能导致重复,需要特殊处理。批量插入时,如果语句不确定行数,可能需要更多的锁,影响性能,引用4中的模式1和模式0的区别可以在这里展开。 最后,应用场景部分可以提到主键、唯一索引、分页优化等,但要注意分页优化时使用自ID可能的问题,比如ID不连续导致分页不准确。 在组织内容时,要确保使用正确的LaTeX格式,比如行内公式用$...$,独立公式用$$...$$。同时,生成相关问题,帮助用户深入理解。例如,自ID用完怎么办?如何解决分库分ID冲突?不同锁模式的区别等。 需要检查是否有遗漏的信息,比如MyBatis如何获取自主键,可能在实现原理或应用场景中提到,比如useGeneratedKeys的使用,引用5中的方法。这部分可以在实现原理或应用场景中简要说明,比如在插入后获取生成的ID值。 最后,确保回答结构清晰,分点明确,引用正确标注,如引用[^1]、[^3]等。同时,语言要口语化,避免过于技术化的术语,但又要准确。</think>### MySQLID使用方法及实现原理 #### 一、基本概念 MySQLID(Auto Increment)是一种自动为主键列生成唯一递整数值的机制,常用于简化数据插入操作并确保主键唯一性[^3]。 #### 二、使用方法 1. **建时定义自主键** 通过`AUTO_INCREMENT`关键字指定主键属性: ```sql CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) ); ``` 2. **插入数据** 插入时**无需指定自列**,数据库自动分配ID: ```sql INSERT INTO users (name) VALUES ('Alice'); -- id自动生成 ``` 3. **获取自ID** 通过`LAST_INSERT_ID()`或编程接口获取最新生成的ID: ```sql SELECT LAST_INSERT_ID(); -- 返回最后一次插入的自ID ``` Java中可通过`Statement.RETURN_GENERATED_KEYS`获取(如MyBatis的`useGeneratedKeys`属性)[^5]。 --- #### 三、实现原理 1. **自计数器** - 每个的自ID值通过计数器维护,计数器存储在内存中(MySQL 8.0前)或持久化到`mysql.innodb_table_stats`系统(8.0+)[^3]。 - 计数器更新规则:每次插入操作后自1。 2. **锁机制并发控制** 通过`innodb_autoinc_lock_mode`参数控制锁策略: - **模式0**:语句级锁,保证ID绝对连续(性能较差)[^4]。 - **模式1(默认)**:对不确定行数的插入(如`INSERT ... SELECT`)使用语句级锁,其他情况批量预分配ID[^4]。 - **模式2**:无锁模式,ID可能不连续但并发性能最高[^4]。 3. **ID分配流程** - 批量插入时预分配ID段(如插入5行,一次性分配5个ID)。 - 事务回滚时已分配的ID**不会回滚**,导致ID跳跃[^2]。 --- #### 四、注意事项 1. **ID不连续场景** - 事务回滚(如插入失败后ID仍递)。 - 批量插入预分配未完全使用(如预分配100个ID,实际插入50行,剩余50个丢弃)。 2. **分库分管理** 多个或数据库实例需通过以下方式避免ID冲突: - 设置不同起始值:`AUTO_INCREMENT = 1000` - 使用全局ID生成器(如Snowflake算法) 3. **自ID耗尽** - `INT`类型最值为4294967295,耗尽后插入会报错。 - 解决方案:升级为`BIGINT`类型(范围:$2^{63}-1$)。 --- #### 五、应用场景 1. **主键唯一标识**:保证数据行的唯一性[^3]。 2. **分页优化**:通过自ID实现高效分页查询(如`WHERE id > 1000 LIMIT 10`)。 3. **关联设计**:外键引用自ID确保数据一致性。 ---
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值