MySQL学习笔记：实战篇

唯一索引和普通索引的区别

• change buffer ：更新记录时，如果该数据页在内存中，则直接更新。否则写入 change buffer。

  • 等到下次查询访问该数据页时，再执行 change buffer来保正数据逻辑正确。
  • 后台线程会定期merge change buffer 的内容到数据页
  • 数据库正常关闭时也会merge
  • 适用于普通索引。在写多读少的场景下更优

• 查询过程：一般来说两者没多大性能差别

  • 唯一索引具有唯一性，查询到第一个满足的记录后就会停止检索
  • 普通索引找到第一个记录后，还需要向后遍历判断是否还存在满足的记录

• 更新过程

  • 查询过程：
    • 数据页在内存中
      • 唯一索引：判断唯一性，插入值
      • 普通索引：插入值
    • 数据页不在内存中
      • 唯一索引：读数据页到内存，判断唯一性，插入值
      • 普通索引：写入 change buffer

一般选用普通索引配合 change buffer使用。在写完记录就伴随着查该记录的情况，应该关闭 change buffer

使用 explain命令解析SQL语句的执行
通过慢查询日志（slow log）可以查看SQL具体执行情况

MySQL如何选择索引

MySQL的索引由优化器来进行选择

优化器的逻辑

优化器会根据扫描行数、是否使用临时表、是否排序等因此判断

Mysql有事索引统计不准确，对预扫描行数评估有误，就会使用错误的索引。我们可以通过 explain 命令来分析语句，如果预估的 rows 和实际情况相差较大，可以使用 analyze tbale t 来修正一下。

索引选择的异常和处理

• 采用 force index 强行选择一个索引。
• 我们可以修改查询语句，引导MySQL使用我们期待的索引
• 在有些场景下，可以新建一个更加合适的索引，或者删掉误选的索引

给字符串加索引

前缀索引

• 截取字符串部分长度构建索引。节省空间
• 查询命中率会降低，有时候需要查询多次
• 可以依次选取不同长度的前缀来比较表中有多少不同记录。根据该值来确定前缀长度
• 使用前缀索引，就不会使用覆盖索引对查询的优化。

其他方式

字符串太长，前缀区分度太低的时候，我们需要以下方式：

• 倒序存储。将前缀区分度不高的数据倒序，利用后缀判断，入身份证号
• 使用hash。在表上再建一个字段，保存hash值。

使用上述两种方法，都不支持范围查询。