11.1 查询过程
假设，执行查询的语句是 select id from test where k=5。
对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一 个不满足k=5条件的记录。
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
那么，这个不同带来的性能差距会有多少呢？答案是， 微乎其微。
你知道的，InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB
因为引擎是按页读写的，所以说，当找到 k=5 的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。
当然，如果 k=5 这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。
但是，我们之前计算过，对于整型字段，一个数据页可以放近千个 key，因此出现这种情况的概率会很低。所以我们计算平均性能差异时，仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计。
11.2 更新过程
为了说明普通索引和唯一索引对更新语句性能的影响这个问题，介绍一下 change buffer。
当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话， 在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中 ，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了 访问这个数据页 会触发 merge 外，系统有 后台线程会定期 merge。在 数据库正常关闭（shutdown） 的过程中，也会执行 merge 操作。
如果能够将更新操作先记录在 change buffer， 减少读磁盘 ，语句的执行速度会得到明显的提升。而且， 数据读入内存是需要占用 buffer pool 的，所以这种方式还能够 避免占用内存 ，提高内存利用率。
那么，什么条件下可以使用 change buffer 呢？
对干唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入 (4.400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。
因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。
change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数innodb change buffer maxsize 来动态设置。这个参数设置为 50 的时候，表示 changebuffer 的大小最多只能占用 buffer pool 的 50%。
如果要在这张表中插入一个新记录 (4,400) 的话，InnoDB的处理流程是怎样的？
第一种情况是，这个记录要更新的目标页在内存中。这时：
对干唯一索引来说，找到 3 和 5 之间的位置，判断为没有冲突，插入这个值，语句执行结束
对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。
这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的CPU时间。
第二种情况是，这个记录要更新的目标页不在内存中。这时：
对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束;
对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。
将数据从磁盘读入内存涉及随机 I/O 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。
案例：
某个业务的库内存命中率突然从 99% 降低到了 75%，整个系统处于阻塞状态，更新语句全部堵住。而探究其原因后，发现这个业务有大量插入数据的操作，而他在前一天把其中的某个普通索引改成了唯一索引。
11.3 change buffer的使用场景
change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。那么，现在有一个问题就是：普通索引的所有场景，使用 change buffer 都可以起到加速作用吗？
因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。
因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，之后由干马上要访问这个数据页，会立即触发 merge 过程，这样随机访问 I/O 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，changebuffer 反而起到了副作用。
普通索引和唯一索引应该怎么选择？其实，这两类索引在查询能力上是没差别的，主要考虑的是对 更新性能 的影响。所以，建议你 尽量选择普通索引 。
在实际使用中会发现， 普通索引 和 change buffer 的配合使用，对于 数据量大 的表的更新优化还是很明显的。
如果所有的更新后面，都马上 伴随着对这个记录的查询，那么你应该关闭change buffer 。而在其他情况下，change buffer 都能提升更新性能。
由于唯一索引用不上 change buffer 的优化机制，因此如果 业务可以接受 ，从性能角度出发建议优先考虑非唯一索引。但是如果"业务可能无法确保"的情况下，怎么处理呢？
首先， 业务正确性优先 。我们的前提是“业务代码已经保证不会写入重复数据”的情况下，讨论性能问题。如果业务不能保证，或者业务就是要求数据库来做约束，那么没得选，必须创建唯一索引。 这种情况下，本节的意义在于，如果碰上了大量插入数据慢、内存命中率低的时候，给你多提供一个排查思路。
然后，在一些“归档库 ”的场景，你是可以考虑使用唯一索引的。比如，线上数据只需要保留半年， 然后历史数据保存在归档库。这时候，归档数据已经是确保没有唯一键冲突了。要提高归档效率， 可以考虑把表里面的唯一索引改成普通索引。
12. 其它查询优化策略
12.1 EXISTS 和 IN 的区分
问题：
不太理解哪种情况下应该使用 EXISTS，哪种情况应该用 IN。选择的标准是看能否使用表的索引吗?
回答:
索引是个前提，其实选择与否还是要看表的大小。你可以将选择的标准理解为 小表驱动大表。在这种方式下效率是最高的。
比如下面这样：
SELECT * FROM A WHERE cc IN (SELECT cc FROM B) SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc = A.cc)
当 A 小于 B 时，用 EXISTS。因为 EXISTS 的实现，相当于外表循环，实现的逻辑类似于：
for i in A for j in B if j.cc == i.cc then ...
当 B 小于 A 时用 IN，因为实现的逻辑类似于：
for i in B for j in A if j.cc == i.cc then ...
结论：哪个表小就用哪个表来驱动，A 表小就用 EXISTS ，B 表小就用 IN
12.2 COUNT(*) 与 COUNT(具体字段) 效率
问：在MySQL中统计数据表的行数，可以使用三种方式 SELECT COUNT(*)、SELECT COUNT(1) 和 SELECT COUNT(具体字段)，使用这三者之间的查询效率是怎样的？
答：
前提：如果你要统计的是某个字段的非空数据行数，则另当别论，毕竟比较执行效率的前提是结果一样才可以。
环节1：COUNT(*) 和 COUNT(1) 都是对所有结果进行 COUNT，COUNT(*) 和 COUNT(1) 本质上并没有区别（二者执行时间可能略有差别，不过你还是可以把它俩的执行效率看成是相等的）。如果有 WHERE 子句，则是对所有符合筛选条件的数据行进行统计；如果没有 WHERE 子句，则是对数据表的数据行数进行统计。
**环节2：**如果是 MvlSAM 存储引擎，统计数据表的行数只需要 O(1)的复杂度，这是因为每张MvlSAM 的数据表都有一个 meta 信息存储了 row_count 值，而一致性则由表级锁来保证。
如果是 InnoDB 存储引擎，因为 InnoDB 支持事务，采用行级锁和 MVCC 机制，所以无法像 MyISAM 一样，维护一个 row_count 变量，因此需要采用扫描全表，是 O(n) 的复杂度，进行循环+计数的方式来完成统计。
**环节3：**在 InnoDB 引擎中，如果采用 COUNT(具体字段) 来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引。对于 COUNT(*) 和COUNT(1) 来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。
如果有多个二级索引，会使用 keylen 小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。
12.3 关于 SELECT(*)
在表查询中，建议明确字段，不要使用 * 作为查询的字段列表，推荐使用 SELECT <字段列表> 查询。原因：
MySQL 在解析的过程中，会通过 查询数据字典 将"*"按序转换成所有列名，这会大大的耗费资源和时间。
无法使用 覆盖索引
12.4 LIMIT 1 对优化的影响
针对的是会扫描全表的SQL语句，如果你可以确定结果集只有一条，那么加上 LIMIT 1 的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。
如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上 LIMIT 1了。
12.5 多使用COMMIT
只要有可能，在程序中尽量多使用 COMMIT，这样程序的性能得到提高，需求也会因为 COMMIT 所释放的资源而减少。
COMMIT 所释放的资源:
回滚段上用于恢复数据的信息
被程序语句获得的锁
redo / undo log buffer 中的空间
管理上述 3 种资源中的内部花费
13. 淘宝数据库，主键如何设计的?
聊一个实际问题：淘宝的数据库，主键是如何设计的？
某些错的离谱的答案还在网上年复一年的流传着，甚至还成为了所谓的 MySQL 军规。其中，一个最明显的错误就是关于 MySQL 的主键设计。
大部分人的回答如此自信：用8字节的 BIGINT 做主键，而不要用INT。错!
这样的回答，只站在了数据库这一层，而没有 从业务的角度 思考主键。主键就是一个自增 ID 吗？站在 2022 年的新年档口，用自增做主键，架构设计上可能 连及格都拿不到。
13.1 自增ID的问题
自增 ID 做主键，简单易懂，几乎所有数据库都支持自增类型，只是实现上各自有所不同而已。自增 ID 除了简单，其他都是缺点，总体来看存在以下几方面的问题：
1. 可靠性不高
存在自增ID回溯的问题，这个问题直到最新版本的MySQL 8.0才修复。
2. 安全性不高
对外暴露的接口可以非常容易猜测对应的信息。比如：/User/1/ 这样的接口，可以非常容易猜测用户ID的值为多少，总用户数量有多少，也可以非常容易地通过接口进行数据的爬取。
3. 性能差
自增 ID 的性能较差，需要在数据库服务器端生成。
4. 交互多
业务还需要额外执行一次类似 last_insert_id() 的函数才能知道刚才插入的自增值，这需要多一次的网络交互。在海量并发的系统中，多1条SQL，就多一次性能上的开销。
5. 局部唯一性
最重要的一点，自增 ID 是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，在任意服务器间都是唯一的。对于目前分布式系统来说，这简直就是噩梦。
13.2 业务字段做主键
为了能够唯一地标识一个会员的信息，需要为 会员信息表 设置一个主键。那么，怎么为这个表设置主键，才能达到我们理想的目标呢? 这里我们考虑业务字段做主键。 表数据如下:
在这表里，哪个字段比较合适呢?
选择卡号(cardno)
会员卡号（cardno）看起来比较合适，因为会员卡号不能为空，而且有唯一性，可以用来标识一条会员记录。
mysql> CREATE TABLE demo.membermaster -> ( -> cardno CHAR(8) PRIMARY KEY, -- 会员卡号为主键 -> membername TEXT, -> memberphone TEXT, -> memberpid TEXT, -> memberaddress TEXT, -> sex TEXT, -> birthday DATETIME -> ); Query OK, 0 rows affected (0.06 sec)
不同的会员卡号对应不同的会员，字段“cardno”唯一地标识某一个会员。如果都是这样，会员卡号与会员一一对应，系统是可以正常运行的。
但实际情况是， 会员卡号可能存在重复使用 的情况。比如，张三因为工作变动搬离了原来的地址，不再到商家的门店消费了 (退还了会员卡)，于是张三就不再是这个商家门店的会员了。但是，商家不想让 这个会 员卡空着，就把卡号是“10000001”的会员卡发给了王五。
从系统设计的角度看，这个变化只是修改了会员信息表中的卡号是“10000001”这个会员 信息，并不会影响到数据一致性。也就是说，修改会员卡号是“10000001”的会员信息， 系统的各个模块，都会获取到修改后的会员信息，不会出现“有的模块获取到修改之前的会员信息，有的模块获取到修改后的会员信息， 而导致系统内部数据不一致”的情况。因此，从 信息系统层面 上看是没问题的。
但是从使用系统的业务层面 来看，就有很大的问题 了，会对商家造成影响。
比如，我们有一个销售流水表(trans)，记录了所有的销售流水明细。2020 年 12 月 01 日，张三在门店购买了一本书，消费了 89 元。那么，系统中就有了张三买书的流水记录，如下所示:
接着，我们查询一下 2020 年 12 月 01 日的会员销售记录:
mysql> SELECT b.membername,c.goodsname,a.quantity,a.salesvalue,a.transdate -> FROM demo.trans AS a -> JOIN demo.membermaster AS b -> JOIN demo.goodsmaster AS c -> ON (a.cardno = b.cardno AND a.itemnumber=c.itemnumber); +------------+-----------+----------+------------+---------------------+ | membername | goodsname | quantity | salesvalue | transdate | +------------+-----------+----------+------------+---------------------+ | 张三 | 书 | 1.000 | 89.00 | 2020-12-01 00:00:00 | +------------+-----------+----------+------------+---------------------+ 1 row in set (0.00 sec)
如果会员卡“10000001”又发给了王五，我们会更改会员信息表。导致查询时:
mysql> SELECT b.membername,c.goodsname,a.quantity,a.salesvalue,a.transdate -> FROM demo.trans AS a -> JOIN demo.membermaster AS b -> JOIN demo.goodsmaster AS c -> ON (a.cardno = b.cardno AND a.itemnumber=c.itemnumber); +------------+-----------+----------+------------+---------------------+ | membername | goodsname | quantity | salesvalue | transdate | +------------+-----------+----------+------------+---------------------+ | 王五 | 书 | 1.000 | 89.00 | 2020-12-01 00:00:00 | +------------+-----------+----------+------------+---------------------+ 1 row in set (0.01 sec)
这次得到的结果是：王五在 2020 年 12 月 01 日，买了一本书，消费 89 元。显然是错误的!结论：千万不能把会员卡号当做主键。
选择会员电话或身份证号
会员电话可以做主键吗？不行的。在实际操作中，手机号也存在 被运营商收回 ，重新发给别人用的情况。
那身份证号行不行呢？好像可以。因为身份证决不会重复，身份证号与一个人存在一一对 应的关系。可问题是，身份证号属于 个人隐私 ，顾客不一定愿意给你。要是强制要求会员必须登记身份证号，会把很多客人赶跑的。其实，客户电话也有这个问题，这也是我们在设计会员信息表的时候，允许身份证号和电话都为空的原因。
所以，建议尽量不要用跟业务有关的字段做主键。毕竟，作为项目设计的技术人员，我们谁也无法预测在项目的整个生命周期中，哪个业务字段会因为项目的业务需求而有重复，或者重用之类的情况出现。
经验:
刚开始使用 MySQL 时，很多人都很容易犯的错误是喜欢用业务字段做主键，想当然地认为了解业务需求，但实际情况往往出乎意料，而更改主键设置的成本非常高。
13.3 淘宝的主键设计
在淘宝的电商业务中，订单服务是一个核心业务。请问， 订单表的主键 淘宝是如何设计的呢？是自增ID吗？
打开淘宝，看一下订单信息：
从上图可以发现，订单号不是自增ID!我们详细看下上述4个订单号:
1550672064762308113 1481195847180308113 1431156171142308113 1431146631521308113
订单号是 19 位的长度，且订单的最后 5 位都是一样的，都是 08113。且订单号的前面 14 位部分是单调递增的。
大胆猜测，淘宝的订单 ID 设计应该是：订单ID = 时间 + 去重字段 + 用户ID后6位尾号
这样的设计能做到全局唯一，且对分布式系统查询及其友好。
13.4 推荐的主键设计
非核心业务：对应表的主键自增 ID，如告警、日志、监控等信息。
核心业务：主键设计至少应该是全局唯一且是单调递增。全局唯一保证在各系统之间都是唯一的，单调递增是希望插入时不影响数据库性能。
这里推荐最简单的一种主键设计：UUID。
UUID的特点:
全局唯一，占用 36 字节，数据无序，插入性能差。
认识UUID:
为什么UUID是全局唯一的?
为什么UUID占用36个字节?
为什么UUID是无序的?
MySQL数据库的UUID组成如下所示：
UUID = 时间 + UUID 版本（16字节）- 时钟序列（4字节） - MAC 地址（12字节）
我们以 UUID 值：e0ea12d4-6473-11eb-943c-00155dbaa39d 举例
1. 为什么UUID是全局唯一的？
在 UUID 中时间部分占用 60 位，存储的类似 TIMESTAMP 的时间戳，但表示的是从1582-10-15 00:00:00.00 到现在的 100 ns 的计数。可以看到 UUID 存储的时间精度比 TIMESTAMPE 更高，时间维度发生重复的概率降低到1/100ns。
时钟序列是为了避免时钟被回拨导致产生时间重复的可能性。MAC地址用于全局唯一。
2. 为什么UUID占用36个字节？
UUID 根据字符串进行存储，设计时还带有无用"-"字符串，因此总共需要36个字节。
3. 为什么UUID是随机无序的呢？
因为 UUID 的设计中，将时间低位放在最前面，而这部分的数据是一直在变化的，并且是无序。
改造UUID
若将时间高低位互换，则时间就是单调递增的了，也就变得单调递增了。MySQL 8.0 可以更换时间低位和时间高位的存储方式，这样 UUID 就是有序的UUID了。
MySQL 8.0 还解决了 UUID 存在的空间占用的问题，除去了 UUID 字符串中无意义的"-"字符串，并且将字符串用二进制类型保存，这样存储空间降低为了16字节。
可以通过 MySQL 8.0 提供的 uuid_to_bin 函数实现上述功能，同样的，MySQL 也提供了 bin_to_uuid 函数进行 转化：
SET @uuid = UUID(); SELECT @uuid,uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);
通过函数 uuid_to_bin(@uuid,true) 将 UUID 转化为有序 UUID 了。全局唯一 + 单调递增，这不就是我们想要的主键！
13.5 有序 UUID 性能测试
16 字节的有序 UUID，相比之前 8 字节的自增ID，性能和存储空间对比究竟如何呢？
我们来做一个测试，插入 1 亿条数据，每条数据占用 500 字节，含有 3 个二级索引，最终的结果如下所示：
从上图可以看到插入 1 亿条数据有序 UUID 是最快的，而且在实际业务使用中有序 UUID 在 业务端就可以生成 。还可以进一步减少 SQL 的交互次数。
另外，虽然有序 UUID 相比自增 ID 多了 8 个字节，但实际只增大了 3G 的存储空间，还可以接受。
在当今的互联网环境中，非常不推荐自增 ID 作为主键的数据库设计。更推荐类似有序 UUID 的全局唯一的实现。
另外在真实的业务系统中，主键还可以加入业务和系统属性，如用户的尾号，机房的信息等。这样的主键设计就更为考验架构师的水平了。
如果不是 MySQL8.0 肿么办?
手动赋值字段做主键！
比如，设计各个分店的会员表的主键，因为如果每台机器各自产生的数据需要合并，就可能会出现主键重复的问题。
可以在总部 MySQL 数据库中，有一个管理信息表，在这个表中添加一个字段，专门用来记录当前会员编号的最大值。
门店在添加会员的时候，先到总部 MySQL 数据库中获取这个最大值，在这个基础上加 1，然后用这个值作为新会员的“id”同时，更新总部 MySQL 数据库管理信息表中的当前会员编号的最大值。
这样一来，各个门店添加会员的时候，都对同一个总部 MySQL 数据库中的数据表字段进行操作，就解决了各门店添加会员时会员编号冲突的问题。
