MySQL为什么推荐自增主键ID为主键及MySQL的调优

本文探讨了InnoDB中页大小设置的重要性,推荐自增ID作为主键的理由,以及MySQL引擎选择、索引优化策略,包括B+树和索引数据结构的选择。此外,还涉及了如何根据业务场景选择合适的引擎和设计原则,如行锁与表锁的区别,以及如何避免过度索引和性能瓶颈。

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

页的概念

在计算机里,无论是内存还是磁盘,操作系统都是按页的大小进行读取的(页大小通常为 4 kb),磁盘每次读取都会预读,会提前将连续的数据读入内存中,这样就避免了多次 IO,这就是计算机中有名的局部性原理,即我用到一块数据,很大可能这块数据附近的数据也会被用到,干脆一起加载,省得多次 IO 拖慢速度, 这个连续数据有多大呢,必须是操作系统页大小的整数倍。

所以MySQL 的页,默认值为 16 KB,也就是说对于 B+ 树的节点,最好设置成页的大小(16 KB),这样一个 B+ 树上的节点就只会有一次 IO 读。

那有人就会问了,这个页大小是不是越大越好呢,设置大一点,节点可容纳的数据就越多,树高越小,IO 不就越小了吗,这里要注意,页大小并不是越大越好,InnoDB 是通过内存中的缓存池(pool buffer)来管理从磁盘中读取的页数据的。页太大的话,很快就把这个缓存池撑满了,可能会造成页在内存与磁盘间频繁换入换出,影响性能。

为啥推荐自增 id 作为主键

  • 第一个原因就是自增主键一般都会设置为int,占用4个字节,所有可以这样说,InnoDB的B+树最多为4叉。

  • 第二个原因就是,假如以其他属性为主键,可能由于这个数的随机性造成大量页的分裂和合并导致性能急剧下降,但是如果是自增ID,他这个值一定是比之前数都大的,所以在插入时要么放到元素个数还没满的已存在节点,要么放入新建的节点中,就不存在上述问题了。

MySQL调优

引擎选择
InnoDB 和 MyISAM区别:
1、InnoDB支持主外键、事务;
2、InnoDB是行锁,操作时候只锁一行数据,适合高并发;MyISAM是表索;
3、InnoDB不仅缓存索引,还缓存真实数据;MyISAM只缓存索引;
4、InnoDB需要表空间大;
5、InnoDB关注事务,MyISAM关注性能(查)

使用选择:

MYISAM小巧玲珑, 节约空间, 速度快
INNODB安全性好, 有事务的处理和多表多用户的操作

行锁升级表锁

表的设计
按照三范式规则

索引优化

  • 索引数据结构
    不使用hash表是因为不方便进行范围查找
    不使用二叉树的原因是树的深度太高了
    不使用b树的原因是有更好的b+树
    首先要知道在b树和b+树中, 数据都是放在叶子上的, 而且已过父节点可以有多个子节点
    b树虽然已过节点可以有多个分支, 树的深度降低不少, 但是在进行范围查找的时候还是不如b+树
    因为b+树在叶子节点上是连续的

在适当情况添加索引:
索引不是越多越好, 他只会提高查找效率, 删除修改, 增加的效率只会降低
不要给经常变更的数据加索引
小数据量有没有必要加索引
索引一般用在查询上, 在增加删除修改方便索引的效率是降低的, 所以用在查多改少的情况
如果某一个字段选择性很少, 比如性别, 类型, 他们的取值范围很小, 也不适合添加索引

sql语句优化

  • 减少selec *的使用
  • 不使用NOT IN和<>操作
  • 在SQL文里面对某个字段经过运算后再与一个常量比较:那么这将会对运行性能产生很大的影响
  • 一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 和like “%aaa” 不会使用索引而like “aaa%”可以使用索引。
  • mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

设计连接池实现优化
我们普通执行一个数据库操作, 获得数据库连接->执行->释放

如果多次进行连接释放这是很浪费资源的

池化技术: 准备一些预先的资源,需要执行了直接用预先准备好的就行

可以设置最小连接数啊最大连接数啊等待超时啥的

如果想自定义一个连接池的话直接实现DAtaSource接口即可, 使用了这些数据库连接池之后,我们在项目中开发就不需要编写连接数据库的代码了

或者使用市面上优秀数据源DBCP和C3P0和德鲁伊, 但是他们的本质还是实现了DataSource接口

<think>好的,用户想知道如何在MySQL中为表设置自主键AUTO_INCREMENT。首先,我需要回顾一下自主键的基本概念。AUTO_INCREMENT属性可以让字段的值自动递,通常用于主键,确保每行有唯一标识。根据引用[1],InnoDB处理自的方式涉及锁机制,这可能会影响性能,但具体设置步骤可能不在那里。 接下来,创建表时的语法是关键。用户需要在定义表的时候,在整数类型的字段后面添加AUTO_INCREMENT,并指定为主键。例如,像引用[2]中的示例,使用CREATE TABLE语句时,将字段定义为INT AUTO_INCREMENT PRIMARY KEY。这应该是最直接的方法。 然后,如果是已有的表,用户可能需要修改现有字段。这时候要用ALTER TABLE来添加自属性。需要注意的是,修改主键可能需要先删除旧的主键,再添加新的自主键。同时,自字段通常需要是整数类型,比如INT或BIGINT,并且必须为主键或唯一索引的一部分。 用户可能还会关心一些细节,比如自的起始值和步长。默认情况下,AUTO_INCREMENT从1开始,每次加1,但可以通过ALTER TABLE来修改起始值。例如,ALTER TABLE table_name AUTO_INCREMENT=1000;。引用[4]提到可以设置负值,但实际应用中可能不太常见,不过需要确保字段类型支持负数。 另外,需要注意引擎的问题。虽然示例中用的是InnoDB,但不同存储引擎对AUTO_INCREMENT的处理可能略有不同。根据引用[1]和[3],InnoDB的自机制在事务回滚时不会重用值,而MyISAM可能会有不同的行为。因此,建议用户确认表的存储引擎是否正确。 最后,验证设置是否成功也很重要。用户可以通过插入数据并观察主键是否自动递来检查。如果遇到错误,比如重复键,可能需要检查是否有现有的数据冲突,或者是否正确地设置了主键约束。 总结步骤应该是:创建表时定义自主键,或者修改现有表结构添加自属性,设置起始值(可选),并验证功能。同时提醒用户注意引擎差异和可能的性能影响,尤其是在高并发插入时,InnoDB的自锁机制可能会有影响,但引用[1]可能提供了化建议。</think>在 MySQL 中为表设置自主键的步骤如下: ### 一、创建新表时设置 ```sql CREATE TABLE 表名 ( id INT AUTO_INCREMENT PRIMARY KEY, 列名1 数据类型, 列名2 数据类型 ) ENGINE=InnoDB; ``` 要点说明: 1. `AUTO_INCREMENT` 必须与**整数类型**(INT/BIGINT 等)字段组合使用 2. `PRIMARY KEY` 约束确保主键唯一性[^3] 3. InnoDB 引擎的自计数器存储在内存中,重启服务会查询当前最大值重新初始化[^1] ### 二、修改现有表结构 ```sql ALTER TABLE 表名 MODIFY COLUMN 列名 INT AUTO_INCREMENT PRIMARY KEY; ``` 注意: 1. 修改前需确保字段是整数类型 2. 已有数据不能有重复值 3. 可配合 `AUTO_INCREMENT=起始值` 设置初始值 ### 三、进阶配置 ```sql -- 设置自初始值 ALTER TABLE 表名 AUTO_INCREMENT = 1000; -- 查看当前自值 SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名'; ``` 特殊说明:自字段允许设置负数,但需字段类型支持[^4]
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

lhj_loveFang_1105

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值