Day2 SQL调优

SQL调优

核心思路：减少磁盘I/O和避免无效计算

• 索引层优化
  • 合理设计联合索引，利用覆盖索引尽可能减少回表查询
  • 使用索引时尽量避免引起索引失效的情况
• 查询优化
  • 禁止使用SELECT *，只查必要字段
  • 避免LIKE %VALUE 左模糊查询
  • 避免查询字段隐式转换
  • 减少IN 查询使用
• 排序优化
  • 排序字段尽量走索引
  • 当where条件与order by条件冲突时，优先保证where条件

  filesort的排序类型 单路排序：把所有select的字段都放进sort_buffer进行排序，排完直接返回 双路排序：只放排序字段和id排序，排完后回表取其他字段

• 分页查询优化

  • 使用游标分页替代LIMIT（或者冷热数据分离，将历史数据挪到历史表）

• 关联SQL优化

  MySQL 表关联算法

  • 嵌套循环链接算法 Nested- Loop Join （NLJ）
    • 一次一行循环从驱动表读行，拿到关联字段根据关联字段在被驱动表里取出满足条件的行，然后取出两表的结果合集
  • 基于块的嵌套循环算法Block Nested- Loop Join （BNL）
    • 把驱动表的数据读取到join_buffer中，然后扫描被驱动表，把被驱动表的每一行取出来跟join_buffer中的数据做对比

  • 小表驱动大表
  • 关联字段加索引

• IN/EXISTS 优化

  • 查询时小表驱动大表，主表数据量小于驱动表时，EXISTS的性能优于IN

• count() 优化

  • count(*) count(1) count(主键)扫描所有行，效率无差别

    使用count(*)时，底层引擎会做优化，如果表上有二级索引，优化器通常走二级索引而不是主键索引，因为二级索引叶子节点只存主键值，数据量比聚簇索引小很多 count(1) 里面的1可以换成任何字符串和数字，只是一个占位符

  • count(field) 统计所有字段非null的行，如果字段是索引，效率与以上无差别，如果是非索引字段且有null值，效率会略低

  • 大表count优化

    • 取近似值 show table status like ‘field’;
    • 使用缓存维护计数，每次增删同步更新redis计数器，查询时读缓存，但是要注意缓存和数据库一致性问题
    • 单独维护计数

事物如何实现

靠四个核心组件：Redo Log、Undo Log、锁、MVCC，最终保证一致性

Redo Log保证持久性。事物提交时，修改先写到redo log再写磁盘数据页，就算写数据页宕机，重启后也能根据redo log恢复数据。

Undo Log保证原子性。每次修改数据前，先把原值存到undo log里，事物回滚时，按undo log的记录恢复回去，要么全做完，要么全撤销。

锁机制保证隔离性。InnoDB支持行锁，两个事物修改同一行，必须等待另一个释放锁。

MVCC（Multi-Version Concurrency Control 多版本并发控制）保证隔离性的读写并发。写操作产生新版本，读操作根据事物启动时间去版本链上找到属于自己的那个版本。

InnoDB每条记录里都有两个隐藏字段： trx_id 记录最后修改这条数据的事物ID，roll_pointer指向undo log。每次update不会覆盖原数据，而是把旧值存到undo log里，新值存到数据页。roll_pointer指向旧数据，形成一条完整的版本链