【MySql】3- 实践篇(一)

1. 普通索引和唯一索引的选择

假设你在维护一个市民系统每个人都有一个唯一的身份证号而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名就会执行类似这样的 SQL 语句

select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';

所以你一定会考虑在id_card 字段上建索引

由于身份证号字段比较大我不建议你把身份证号当做主键那么现在有两个选择要么给 id_card 字段创建唯一索引要么创建一个普通索引。

如果业务代码已经保证了不会写入重复的身份证号那么这两个选择逻辑上都是正确的。

从性能的角度考虑你选择唯一索引还是普通索引呢选择的依据是什么呢

其实这两类索引在查询能力上是没差别的主要考虑的是对更新性能的影响。所以建议你尽量选择普通索引。

先举例说明

假设字段 k 上的值都不重复。从这两种索引对查询语句和更新语句的性能影响来进行分析

1.1 查询过程

假设,执行查询语句为:

select id from T where k=5

这个查询语句在索引树上查找的过程先是通过 B+ 树从树根开始按层搜索到叶子节点也就是图中右下角的这个数据页然后可以认为数据页内部通过二分法来定位记录。

• 对于普通索引来说查找到满足条件的第一个记录 (5,500) 后需要查找下一个记录直到碰到第一个不满足 k=5 条件的记录。
• 对于唯一索引来说由于索引定义了唯一性查找到第一个满足条件的记录后就会停止继续检索。

这个不同带来的性能差距是微乎其微。

InnoDB 的数据是按数据页为单位来读写的。也就是说当需要读一条记录的时候并不是将这个记录本身从磁盘读出来而是以页为单位将其整体读入内存。在 InnoDB 中每个数据页的大小默认是 16KB。

因为引擎是按页读写的所以说当找到 k=5 的记录的时候它所在的数据页就都在内存里了。那么对于普通索引来说要多做的那一次“查找和判断下一条记录”的操作就只需要一次指针寻找和一次计算。

如果 k=5 这个记录刚好是这个数据页的最后一个记录那么要取下一个记录必须读取下一个数据页这个操作会稍微复杂一些。但是对于整型字段一个数据页可以放近千个 key因此出现这种情况的概率会很低。所以计算平均性能差异时仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计。

1.2 更新过程

1.2.1 change buffer

当需要更新一个数据页时如果数据页在内存中就直接更新而如果这个数据页还没有在内存中的话在不影响数据一致性的前提下InnoDB 会将这些更新操作缓存在 change buffer 中这样就不需要从磁盘中读入这个数据页了。

在下次查询需要访问这个数据页的时候将数据页读入内存然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

虽然名字叫作 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_max_size 来动态设置。这个参数设置为 50 的时候表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

如果要在这张表中插入一个新记录 (4,400) 的话InnoDB 的处理流程是怎样的?

这个记录要更新的目标页在内存中。这时InnoDB 的处理流程如下

• 对于唯一索引来说找到 3 和 5 之间的位置判断到没有冲突插入这个值语句执行结束
• 对于普通索引来说找到 3 和 5 之间的位置插入这个值语句执行结束。

这个记录要更新的目标页不在内存中。这时InnoDB 的处理流程如下

• 对于唯一索引来说需要将数据页读入内存判断到没有冲突插入这个值语句执行结束
• 对于普通索引来说则是将更新记录在 change buffer语句执行就结束了。

将数据从磁盘读入内存涉及随机 IO 的访问是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问所以对更新性能的提升是会很明显的。

1.2.2 change buffer 的使用场景

merge 的时候是真正进行数据更新的时刻而 change buffer 的主要目的就是将记录的变更动作缓存下来所以在一个数据页做 merge 之前change buffer 记录的变更越多也就是这个页面上要更新的次数越多收益就越大。

• 对于写多读少的业务来说页面在写完以后马上被访问到的概率比较小此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
• 对于一个业务的更新模式是写入之后马上会做查询那么即使满足了条件将更新先记录在 change buffer但之后由于马上要访问这个数据页会立即触发 merge 过程。这样随机访问 IO 的次数不会减少反而增加了 change buffer 的维护代价。所以对于这种业务模式来说change buffer 反而起到了副作用

1.3 索引选择和实践

如果所有的更新后面都马上伴随着对这个记录的查询那么你应该关闭 change buffer。而在其他情况下change buffer 都能提升更新性能。

在实际使用中普通索引和 change buffer 的配合使用对于数据量大的表的更新优化还是很明显的。
特别地在使用机械硬盘时change buffer 这个机制的收效是非常显著的。所以当你有一个类似“历史数据”的库并且出于成本考虑用的是机械硬盘时那你应该特别关注这些表里的索引尽量使用普通索引然后把 change buffer 尽量开大以确保这个“历史数据”表的数据写入速度。

1.4 change buffer 和 redo log

在表上执行这个插入语句

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

假设当前 k 索引树的状态查找到位置后k1 所在的数据页在内存 (InnoDB buffer pool) 中k2 所在的数据页不在内存中。如图 所示是带 change buffer 的更新状态图。

分析这条更新语句它涉及了四个部分内存、redo logib_log_fileX、 数据表空间t.ibd、系统表空间ibdata1。

这条更新语句做了如下的操作按照图中的数字顺序

1. Page 1 在内存中直接更新内存
2. Page 2 没有在内存中就在内存的 change buffer 区域记录下“我要往 Page 2 插入一行”这个信息;
3. 将上述两个动作记入 redo log 中图中 3 和 4。

做完上面这些事务就可以完成了。
所以执行这条更新语句的成本很低就是写了两处内存然后写了一处磁盘两次操作合在一起写了一次磁盘而且还是顺序写的。

图中的两个虚线箭头是后台操作不影响更新的响应时间。

后续处理: 现在要执行 select * from t where k in (k1, k2)。两个读请求的流程图如下:

如果读语句发生在更新语句后不久内存中的数据都还在那么此时的这两个读操作就与系统表空间ibdata1和 redo logib_log_fileX无关了。所以我在图中就没画出这两部分

从图中可以看到

1. 读 Page 1 的时候直接从内存返回。WAL 之后如果读数据是不是一定要读盘是不是一定要从 redo log 里面把数据更新以后才可以返回其实是不用的。可以看一下上图的这个状态虽然磁盘上还是之前的数据但是这里直接从内存返回结果结果是正确的。
2. 要读 Page 2 的时候需要把 Page 2 从磁盘读入内存中然后应用 change buffer 里面的操作日志生成一个正确的版本并返回结果。

可以看到直到需要读 Page 2 的时候这个数据页才会被读入内存。

所以如果要简单地对比这两个机制在提升更新性能上的收益的话redo log 主要节省的是随机写磁盘的 IO 消耗转成顺序写而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

---

思考
change buffer 一开始是写内存的那么如果这个时候机器掉电重启会不会导致 change buffer 丢失呢change buffer 丢失可不是小事儿再从磁盘读入数据可就没有了 merge 过程就等于是数据丢失了。会不会出现这种情况呢

问题的答案是不会丢失。虽然是只更新内存但是在事务提交的时候我们把 change buffer 的操作也记录到 redo log 里了所以崩溃恢复的时候change buffer 也能找回来。

merge 的过程是否会把数据直接写回磁盘?

merge 的执行流程是这样的

1. 从磁盘读入数据页到内存老版本的数据页
2. 从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个依次应用得到新版数据页
3. 写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。

到这里 merge 过程就结束了。这时候数据页和内存中 change buffer 对应的磁盘位置都还没有修改属于脏页之后各自刷回自己的物理数据就是另外一个过程了。

---

2. MySQL为何有时会选错索引?

不知道有没有碰到过这种情况一条本来可以执行得很快的语句却由于 MySQL 选错了索引而导致执行速度变得很慢

先建一个简单的表表里有 a、b 两个字段并分别建上索引

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

然后往表 t 中插入 10 万行记录取值按整数递增即(1,1,1)(2,2,2)(3,3,3) 直到 (100000,100000,100000)。

用存储过程来插入数据的代码如下方便复现

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

接下来我们分析一条 SQL 语句

mysql> select * from t where a between 10000 and 20000;

explain 命令看到的这条语句的执行情况。

确实符合预期key 这个字段值是’a’表示优化器选择了索引 a。

在已经准备好的包含了 10 万行数据的表上我们再做如下操作。

session A 开启了一个事务。随后session B 把数据都删除后又调用了 idata 这个存储过程插入了 10 万行数据。

此时session B 的查询语句 select * from t where a between 10000 and 20000 就不会再选择索引 a 了。
通过慢查询日志(slow log)和强制设置使用索引a的方式来验证,sql如下:

set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/

• 第一句是将慢查询日志的阈值设置为 0表示这个线程接下来的语句都会被记录入慢查询日志中
• 第二句Q1 是 session B 原来的查询
• 第三句Q2 是加了 force index(a) 来和 session B 原来的查询语句执行情况对比。

日志显示如下:

可以看到Q1 扫描了 10 万行显然是走了全表扫描执行时间是 40 毫秒。
Q2 扫描了 10001 行执行了 21 毫秒。也就是说我们在没有使用 force index 的时候MySQL 用错了索引导致了更长的执行时间。

2.1 优化器的逻辑

选择索引是优化器的工作。

优化器选择索引的目的是找到一个最优的执行方案并用最小的代价去执行语句。在数据库里面扫描行数是影响执行代价的因素之一。扫描的行数越少意味着访问磁盘数据的次数越少消耗的 CPU 资源越少。扫描行数并不是唯一的判断标准优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

2.1.1 扫描行数是怎么判断的?

MySQL 在真正开始执行语句之前并不能精确地知道满足这个条件的记录有多少条而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”

一个索引上不同的值越多这个索引的区分度就越好。而一个索引上不同的值的个数我们称之为“基数”cardinality。也就是说这个基数越大索引的区分度越好。可以使用 show index 方法看到一个索引的基数

如上图所示就是表 t 的 show index 的结果 。虽然这个表的每一行的三个字段值都是一样的但是在统计信息中这三个索引的基数值并不同而且其实都不准确。

MySQL 是怎样得到索引的基数的呢
使用采样统计的方法。

要采样统计是因为把整张表取出来一行行统计虽然可以得到精确的结果但是代价太高了

采样统计的时候InnoDB 默认会选择 N 个数据页统计这些页面上的不同值得到一个平均值然后乘以这个索引的页面数就得到了这个索引的基数。

数据表是会持续更新的索引统计信息也不会固定不变。所以当变更的数据行数超过 1/M 的时候会自动触发重新做一次索引统计。

在 MySQL 中有两种存储索引统计的方式可以通过设置参数 innodb_stats_persistent 的值来选择

• 设置为 on 的时候表示统计信息会持久化存储。这时默认的 N 是 20M 是 10。
• 设置为 off 的时候表示统计信息只存储在内存中。这时默认的 N 是 8M 是 16。

由于是采样统计所以不管 N 是 20 还是 8这个基数都是很容易不准的。

索引统计只是一个输入对于一个具体的语句来说优化器还要判断执行这个语句本身要扫描多少行。下面是优化器预估的这两个语句的扫描行数

rows 这个字段表示的是预计扫描行数。

其中Q1 的结果还是符合预期的rows 的值是 104620但是 Q2 的 rows 值是 37116偏差就大了。而上述中我们用 explain 命令看到的 rows 是只有 10001 行是这个偏差误导了优化器的判断。

优化器为什么放着扫描 37000 行的执行计划不用却选择了扫描行数是 100000 的执行计划呢

因为如果使用索引 a每次从索引 a 上拿到一个值都要回到主键索引上查出整行数据这个代价优化器也要算进去的。如果选择扫描 10 万行是直接在主键索引上扫描的没有额外的代价。

优化器会估算这两个选择的代价从结果看来优化器认为直接扫描主键索引更快。当然从执行时间看来这个选择并不是最优的。

使用普通索引需要把回表的代价算进去

MySQL 选错索引这件事儿还得归咎到没能准确地判断出扫描行数

2.1.2 重新统计索引信息

analyze table t 命令可以用来重新统计索引信息

在实践中如果你发现 explain 的结果预估的 rows 值跟实际情况差距比较大可以采用这个方法来处理。

---

基于表 t看看另外一个语句

mysql> select * from t where (a between 1 and 1000)  and (b between 50000 and 100000) order by b limit 1;

这个查询没有符合条件的记录因此会返回空集合。

• 如果使用索引 a 进行查询那么就是扫描索引 a 的前 1000 个值然后取到对应的 id再到主键索引上去查出每一行然后根据字段 b 来过滤。显然这样需要扫描 1000 行。
• 如果使用索引 b 进行查询那么就是扫描索引 b 的最后 50001 个值与上面的执行过程相同也是需要回到主键索引上取值再判断所以需要扫描 50001 行。

执行 explain 结果确认使用的是哪种索引

mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

可以看到返回结果中 key 字段显示这次优化器选择了索引 b而 rows 字段显示需要扫描的行数是 50198

2.2 索引选择异常和处理

大多数时候优化器都能找到正确的索引但偶尔还是会碰到我们上面举例的这两种情况
原本可以执行得很快的 SQL 语句执行速度却比预期的慢很多。

• 一种方法是采用 force index 强行选择一个索引。

MySQL 会根据词法解析的结果分析出可能可以使用的索引作为候选项然后在候选列表中依次判断每个索引需要扫描多少行。如果 force index 指定的索引在候选索引列表中就直接选择这个索引不再评估其他索引的执行代价。

使用不同索引的语句执行耗时

不过使用 force index一来这么写不优美二来如果索引改了名字这个语句也得改显得很麻烦。而且如果以后迁移到别的数据库的话这个语法还可能会不兼容。使用 force index 最主要的问题还是变更的及时性

• 第二种方法就是可以考虑修改语句引导 MySQL 使用我们期望的索引

比如在这个例子里显然把“order by b limit 1” 改成 “order by b,a limit 1” 语义的逻辑是相同的。

改之后的效果

这种修改并不是通用的优化手段只是刚好在这个语句里面有 limit 1因此如果有满足条件的记录

• 第三种方法是在有些场景下可以新建一个更合适的索引来提供给优化器做选择或删掉误用的索引。

---

3. 如何给字符串字段加索引?

几乎所有的系统都支持邮箱登录如何在邮箱这样的字段上建立合理的索引是我们今天要讨论的问题。

假设现在维护一个支持邮箱登录的系统用户表是这么定义的

mysql> create table SUser(
ID bigint unsigned primary key,
email varchar(64), 
... 
)engine=innodb; 

由于要使用邮箱登录所以业务代码中一定会出现类似于这样的语句

mysql> select f1, f2 from SUser where email='xxx';

如果 email 这个字段上没有索引那么这个语句就只能做全表扫描。同时MySQL 是支持前缀索引的可以定义字符串的一部分作为索引。默认地如果创建索引的语句不指定前缀长度那么索引就会包含整个字符串。

比如这两个在 email 字段上创建索引的语句

mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));

• 第一个语句创建的 index1 索引里面包含了每个记录的整个字符串
• 第二个语句创建的 index2 索引里面对于每个记录都是只取前 6 个字节。

两个索引示意图如下:
email 索引结构

email(6) 索引结构

由于 email(6) 这个索引结构中每个邮箱字段都只取前 6 个字节即zhangs所以占用的空间会更小这就是使用前缀索引的优势。但这同时带来的损失是可能会增加额外的记录扫描次数。

执行如下sql:

select id,name,email from SUser where email='zhangssxyz@xxx.com';

• 如果使用的是 index1即 email 整个字符串的索引结构执行顺序是这样的

1. 从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录取得 ID2 的值
2. 到主键上查到主键值是 ID2 的行判断 email 的值是正确的将这行记录加入结果集
3. 取 index1 索引树上刚刚查到的位置的下一条记录发现已经不满足 email='zhangssxyz@xxx.com’的条件了循环结束。

这个过程中只需要回主键索引取一次数据所以系统认为只扫描了一行。

• 如果使用的是 index2即 email(6) 索引结构执行顺序是这样的

1. 从 index2 索引树找到满足索引值是’zhangs’的记录找到的第一个是 ID1
2. 到主键上查到主键值是 ID1 的行判断出 email 的值不是’zhangssxyz@xxx.com’这行记录丢弃
3. 取 index2 上刚刚查到的位置的下一条记录发现仍然是’zhangs’取出 ID2再到 ID 索引上取整行然后判断这次值对了将这行记录加入结果集
4. 重复上一步直到在 idxe2 上取到的值不是’zhangs’时循环结束。

在这个过程中要回主键索引取 4 次数据也就是扫描了 4 行。

通过这个对比可以发现使用前缀索引后可能会导致查询语句读数据的次数变多。

使用前缀索引定义好长度就可以做到既节省空间又不用额外增加太多的查询成本。

实际上在建立索引时关注的是区分度区分度越高越好。因为区分度越高意味着重复的键值越少。因此可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

首先可以使用下面这个语句算出这个列上有多少个不同的值

mysql> select count(distinct email) as L from SUser;

然后依次选取不同长度的前缀来看这个值比如我们要看一下 4~7 个字节的前缀索引可以用这个语句

mysql> select 
  count(distinct left(email,4)as L4,
  count(distinct left(email,5)as L5,
  count(distinct left(email,6)as L6,
  count(distinct left(email,7)as L7,
from SUser;

当然使用前缀索引很可能会损失区分度所以需要预先设定一个可以接受的损失比例比如 5%。
然后在返回的 L4~L7 中找出不小于 L * 95% 的值假设这里 L6、L7 都满足你就可以选择前缀长度为 6。

---

3.1 前缀索引对覆盖索引的影响

先来看看这个 SQL

sql1 : select id,email from SUser where email='zhangssxyz@xxx.com';

sql2 : select id,name,email from SUser where email='zhangssxyz@xxx.com';

sql1 只要求返回 id 和 email 字段。

• 如果使用 index1即 email 整个字符串的索引结构的话可以利用覆盖索引从 index1 查到结果后直接就返回了不需要回到 ID 索引再去查一次。而如果使用 index2即 email(6) 索引结构的话就不得不回到 ID 索引再去判断 email 字段的值。
• 即使将 index2 的定义修改为 email(18) 的前缀索引这时候虽然 index2 已经包含了所有的信息但 InnoDB 还是要回到 id 索引再查一下因为系统并不确定前缀索引的定义是否截断了完整信息。

使用前缀索引就用不上覆盖索引对查询性能的优化了这也是你在选择是否使用前缀索引时需要考虑的一个因素。

3.2 其他方式

遇到前缀的区分度不够好的情况时怎么办呢
比如我们国家的身份证号一共 18 位其中前 6 位是地址码所以同一个县的人的身份证号前 6 位一般会是相同的。

按照前面说的方法可能需要创建长度为 12 以上的前缀索引才能够满足区分度要求。
但是索引选取的越长占用的磁盘空间就越大相同的数据页能放下的索引值就越少搜索的效率也就会越低。

• 第一种方式是使用倒序存储。
  如果你存储身份证号的时候把它倒过来存每次查询的时候你可以这么写

mysql> select field_list from t where id_card = reverse('input_id_card_string');

由于身份证号的最后 6 位没有地址码这样的重复逻辑所以最后这 6 位很可能就提供了足够的区分度。当然了实践中你不要忘记使用 count(distinct) 方法去做个验证。

• 第二种方式是使用 hash 字段。
  可以在表上再创建一个整数字段来保存身份证的校验码同时在这个字段上创建索引。

mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

然后每次插入新记录的时候都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。

mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

这样索引的长度变成了 4 个字节比原来小了很多。

两种的方法的比较

• 相同点

都不支持范围查询。

• 不同点

1. 从占用的额外空间来看倒序存储方式在主键索引上不会消耗额外的存储空间而 hash 字段方法需要增加一个字段。当然倒序存储方式使用 4 个字节的前缀长度应该是不够的如果再长一点这个消耗跟额外这个 hash 字段也差不多抵消了。
2. 在 CPU 消耗方面倒序方式每次写和读的时候都需要额外调用一次 reverse 函数而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话reverse 函数额外消耗的 CPU 资源会更小些。
3. 从查询效率上看使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率但是概率非常小可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式也就是说还是会增加扫描行数。

---

问题
如果在维护一个学校的学生信息数据库学生登录名的统一格式是”学号 @gmail.com", 而学号的规则是十五位的数字其中前三位是所在城市编号、第四到第六位是学校编号、第七位到第十位是入学年份、最后五位是顺序编号。系统登录的时候都需要学生输入登录名和密码验证正确后才能继续使用系统。就只考虑登录验证这个行为的话你会怎么设计这个登录名的索引呢

---

来源 《MySQL实战45讲》 林晓斌