MySQL面试题，为什么我推荐 update 语句的 where 条件加上主键？

公告：“业余草”微信公众号提供免费CSDN下载服务(只下Java资源)，关注业余草微信公众号，添加作者微信：xttblog2，发送下载链接帮助你免费下载！
本博客日IP超过2000，PV 3000 左右，急需赞助商。
极客时间所有课程通过我的二维码购买后返现24元微信红包，请加博主新的微信号：xttblog2，之前的微信号好友位已满，备注：返现
受密码保护的文章请关注“业余草”公众号，回复关键字“0”获得密码
所有面试题(java、前端、数据库、springboot等)一网打尽，请关注文末小程序

腾讯云】1核2G5M轻量应用服务器50元首年，高性价比，助您轻松上云

前面我推荐了极客时间的 Java 高并发课程，很多人根据这篇文章《2019 Java 高并发学习路线图和必会的 50 道面试题！》的介绍买了课程，我希望大家能够认真的看！别把钱浪费了！

今天，我来说另外一个面试题。为什么推荐 MySQL 的 update 语句中 where 条件要有主键？

看到这个问题的朋友，我相信不少人有疑问，我 where 不加主键还不能更新了？

不是的，能更新，也能使用。但是我不建议你们这样做。因为我们大多数人使用 MySQL 都使用的是 innodb 存储引擎，它是支持事务的。如果你的 where 条件不加主键，那么 innodb 的行级锁就可能变成表级锁。如果升级为表级锁，那么并发性就将大打折扣了。

之前也有网友在我的文章里评论说，行锁升级为表锁与事务的隔离级别有关，因为事务的隔离性是靠加锁来实现的，而加锁不当势必会影响并发。

不一样的锁，支持的并发也是不一样的。而最终加什么样的锁，与索引也有莫大的关系，因此，可以说采用什么样的索引决定了支持多少并发。

常用的索引有三类：主键、唯一索引、普通索引。主键我就不再细说，自带最高效的索引属性；唯一索引指的是该属性值重复率为 0，一般可作为业务主键，例如订单号；普通索引 与前者不同的是，属性值的重复率大于 0，不能作为唯一指定条件，例如购买用户的姓名。今天我主要想说的是“普通索引对并发的影响”。

没有索引的情况，我就不说了，那对并发来说肯定是灾难，死锁估计是常有的事。

为什么我推荐 update 中 where 条件加入主键呢？

因为主键是唯一索引，你用其他唯一索引也可以，但是一般的表，可能只有主键才是唯一的。所以，我建议你更新的时候，记住加上主键就行了。

你只需要记住主键和唯一索引是行锁，其他索引并不一定是行锁，很可能是表锁。这样，死锁的概率就非常的高，并发也就随之下降。

下面我们通过一个简单的例子来看一下，普通索引的情况。

相关建表语句，索引，和数据如下所示：

DROP TABLE IF EXISTS `xttblog_order`;
CREATE TABLE `xttblog_order` (
`ID`  int(5) NOT NULL AUTO_INCREMENT ,
`ORDER_NO`  varchar(16) NOT NULL ,
`CREATE_TIME` datetime NOT NULL DEFAULT now(),
`UPDATE_TIME` datetime NULL DEFAULT now(),
PRIMARY KEY (`ID`)
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

ALTER TABLE `xttblog_order` ADD INDEX index_create_time ( `CREATE_TIME` );

INSERT INTO `xttblog_order`(`ORDER_NO`,`CREATE_TIME`,`UPDATE_TIME`) VALUE ('00000001','2019-03-01 14:00:00',null);
INSERT INTO `xttblog_order`(`ORDER_NO`,`CREATE_TIME`,`UPDATE_TIME`) VALUE ('00000002','2019-03-01 14:00:00',null);
INSERT INTO `xttblog_order`(`ORDER_NO`,`CREATE_TIME`,`UPDATE_TIME`) VALUE ('00000003','2019-03-01 14:00:00',null);
INSERT INTO `xttblog_order`(`ORDER_NO`,`CREATE_TIME`,`UPDATE_TIME`) VALUE ('00000004','2019-03-01 14:00:00',null);
INSERT INTO `xttblog_order`(`ORDER_NO`,`CREATE_TIME`,`UPDATE_TIME`) VALUE ('00000005','2019-03-02 14:00:00',null);
INSERT INTO `xttblog_order`(`ORDER_NO`,`CREATE_TIME`,`UPDATE_TIME`) VALUE ('00000006','2019-03-02 14:00:00',null);
INSERT INTO `xttblog_order`(`ORDER_NO`,`CREATE_TIME`,`UPDATE_TIME`) VALUE ('00000007','2019-03-02 14:00:00',null);
INSERT INTO `xttblog_order`(`ORDER_NO`,`CREATE_TIME`,`UPDATE_TIME`) VALUE ('00000008','2019-03-02 14:00:00',null);

然后取消事务自动提交 set autocommit = off;

当我们表里面创建时间重复率比较高的时候。分别开启两个窗口，两个事务。

-- 窗口 1 中的事务
BEGIN;
SELECT t.* FROM `xttblog_order` t WHERE t.`create_time` = '2019-03-01 14:00:00' FOR UPDATE;

-- 窗口 2 中的事务
BEGIN;
SELECT t.* FROM `xttblog_order` t WHERE t.`create_time` = '2019-03-02 14:00:00' FOR UPDATE;

为了演示，你可以把数据量加多点，比如 03-01 和 03-02 的数据各 10 万条。

依次执行两个窗口中的 SQL，你会发现，其中一个窗口中的更新失败了。提示：

1205 – Lock wait timeout exceeded; try restarting transaction

看似这两个事务不互相干，但是在其中一个事务中更新自己锁定的数据失败后，应该能说明在此时引发了表锁。这是在非主键索引或者说是唯一索引，并且索引数据重复量比较高的情况下，你的更新发生量表锁。并发能力就会大大下降！

你们可以试一下，如果此时使用主键或唯一索引会不会这样。

在我们的电商系统中，这样的代码并不少。在一些热门商品和秒杀、优惠、打折等活动中经常会发生一些莫名其妙的异常，导致用户体验大打折扣。

上面的测试数据，你把它们全部删除，然后再新增一些数据，这些数据中在 create_time 重复率为 0 的情况下，你会发现两个事务就都能成功了。这说明它们这时用的应该是行级锁，效率更高。

以上，测试说明在更新数据时，尽量使用主键或唯一索引。但是唯一索引并不少每个表都有的，而主键必须是每个表都必须有的。所以，我建议你们在更新数据时，都带上主键！

最后，欢迎关注我的个人微信公众号：业余草（yyucao）！可加作者微信号：xttblog2。备注：“1”，添加博主微信拉你进微信群。备注错误不会同意好友申请。再次感谢您的关注！后续有精彩内容会第一时间发给您！原创文章投稿请发送至532009913@qq.com邮箱。商务合作也可添加作者微信进行联系！

本文原文出处：业余草： » MySQL面试题，为什么我推荐 update 语句的 where 条件加上主键？