MySQL实战问题01 索引相关

1. 普通索引和唯一索引，应该怎么选择？

查询过程

select id from T where k=5

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

查询过程使用普通索引和唯一索引带来的性能差距微乎其微

• 因为innodb的数据是按数据页为单位来读写的, 每个数据页大小默认是16kb.
• 找到 k=5 的记录的时候，普通索引相比唯一索引来说，只是多做的一次“查找和判断下一条记录”的操作，只需要一次指针寻找和一次计算
• 若k=5 这个记录刚好是这个数据也的最后一条记录, 那么需要取下一个记录. 必须读取下一个数据页. 这个操作会复杂一些.

更新过程

什么是change buffer?

当更新一个数据页时, 若这个数据也在内存中, 就直接更新, 但是如果这个数据页不在内存中, 在不影响数据一致性的前提下. innodb会将这些更新操作缓存在change buffer中, 这样就不需要从磁盘中读入这个数据页了. 下次查询的时候, 将数据页读入内存, 然后执行change buff中与这个页相关的操作.

虽然叫做change buff, 实际上他是可持久化的数据. change buffer 在内存中有拷贝. 也会被写入到磁盘上.

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作.

如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率.

什么条件下可以使用change buffer呢?

只有普通索引可以使用change buffer

change buffer 的使用场景

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

对于写多读少的业务, change buffer使用效果更好. eg 账单类 日志类系统.

反之一个业务更新后会立刻会做查询, 虽然更新会先记录会记录到change buffer 中, 但是由于查询会立刻触发merge过程. 这样随机访问io的次数不会减少, 反而增加了change buffer 的维护代价.

索引选择和实践

• 普通索引和唯一索引在查询能力上基本是没有差别的. 主要考虑的更新性能的影响. 所以尽量选择普通索引.
• 若更新后立马查询, 建议关闭change buffer

change buffer 和 redo log

简单对比两个机制在提升性能上的收益话. redo log 主要节省的是随机写磁盘的io消耗, 而change buffer 主要节省的是随机读磁盘的io消耗.

2. 如何给字符串加索引?

创建字符串索引的两种方法:

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

查看索引的命令是:

show index from TABLE;

两个索引的区别是:

• index1, 包含了每个记录的整个字符串.
• index2, 对于每个记录都是只取前6个字节.

由于index2索引结构中每个字符串只取前六个字节. 所以占用空间会小, 这是前缀索引的优势.

eg. 分别看看两个索引定义下, 查询是怎么执行的.

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

if use index1 (整个字符串):

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

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

if use index2(前6个字符):

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

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

计算列区分度.

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

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

前缀索引除可能会增加扫描行数, 影响到性能还有什么影响?

看个例子

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

相比之前的例子, 这个语句只要求返回id 和 email.

如果使用index1. 可以利用覆盖索引, 从index查到结果后, 直接返回, 不需要回表.

如果使用index2. 取到id之后, 还需要再回表判断email的值.

即使将index2的定义修改为email(18)的前缀索引. 还是会需要回表. 因为系统并不能确定前缀索引的定义是否截断

如果使用了前缀索引就用上覆盖索引对查询性能的优化了.

其他方式:

有什么方法可以既占用空间小, 也能达到相同的效率?

倒叙存储:

如果你存储身份证号的时候把它倒过来存，每次查询的时候，你可以这么写：

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

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

哈希字段

以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引

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

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

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

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

使用倒序存储和使用 hash 字段这两种方法的异同点。

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