「笔记」MySQL 实战 45 讲 - 实践篇（二）

Sql Bad Case

• 条件字段函数操作
  • 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能
  • 栗子：month () 函数、where id + 1 = 10000 等
• 隐式类型转换
  • 在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字
  • 栗子：select “10” > 9（返回 1 代表做数字比较
• 隐式字符编码转换
  • utf8mb4 是 utf8 的超集
  • 栗子
    • select * from trade_detail where tradeid=$L2.tradeid.value; （原SQL
    • select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;
      • CONVERT ( ) 函数：把输入的字符串转成 utf8mb4 字符集
      • 连接过程中要求在被驱动表的索引字段上加函数操作（导致对被驱动表做全表扫描的原因
  • 破局之道
    • 统一字符集（若数据量较大且业务上暂时不允许做 DDL
    • 修改SQL：…. where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

Slow Situation

• 查询长时间不返回

  • 等 MDL 锁

    

    • 现在有一个线程正在表 t 上请求或者持有 MDL 写锁，把 select 语句堵住

  • 查获加表锁的线程 id

    

• 等 FLUSH

  • Waiting for table flush 状态示意图

    

    • MySQL 里面对表做 flush 操作的用法

       # 只关闭表 t
       flush tables t with read lock;
       # 关闭 MySQL 里所有打开的表
       flush tables with read lock
      

  • 等行锁

    • 加锁读方式：select * from t where id=1 lock in share mode（for update
    • 查看锁等待信息：select * from t sys.innodb_lock_waits where locked_table=xxx

• 查询慢

  • 扫描行数多
    • 栗子：select * from t where c=50000 limit 1;
      • 字段 c 上没有索引，这个语句只能走 id 主键顺序扫描，因此需要扫描 5 万行
      • 数据量与执行时间呈线性增涨
  • 一致性读

    • 栗子

      • select * from t where id=1;（扫描行数 1 ，执行时长 800 毫秒
      • select * from t where id=1 lock in share mode;（扫描行数 1，执行时长 0.2 毫秒

    • id=1 的数据状态

      

      • session B 更新完 100 万次，生成了 100 万个回滚日志 (undo log)
      • 一致性读需要从 1000001 开始依次执行 undo log，执行了 100 万次后，才将结果返回

幻读

• 特别说明

  • 幻读在 “当前读” 下才会出现（普通查询是快照读，看不到其他事物插入的数据
    • 当前读的规则，就是要能读到所有已经提交的