Best Practices of Optimizing MySQL Perfomance

This is a draft.

SQL优化

  • 使用MySQL自身缓存

  • select

    • 避免使用select *

    • select 1

  • limit

    • 写入用limit分批

    • 查询limit分页

  • 尽量不用MySQL自带函数

  • 预先计算表达式

  • 避免%前缀模糊查询

  • 预编译

  • join

    • 小表驱动大表

    • inner join代替left join

    • join避免子查询

  • in

    • between代替in

    • in代替or, or的效率是n级别,in的效率是log(n)级别

    • in与exists

  • union all代替union

表结构优化

  • 自增主键
    并不意味着需要用MySQL自带的自增主键,也可以是snowflakes, 货美团的leaf, 因为自增主键有很多好处,存储效率更高,检索效率更高,更短,且有顺序性

  • ip转存int

  • 垂直划分字段

  • 使用not null

  • 避免使用Text, Blog类型

  • 避免触发器

  • 尽量不适用外键

  • 冗余,空间换时间

  • 索引

    • force index

    • 常用字段左前缀法则

    • order by

    • on

    • group by

    • 非字符串

    • where

  • 使用enum类型

  • 固定长度表更快

业务逻辑优化

  • 拆分复杂SQL语句

  • 冷热数据分存

  • 业务端实现复杂逻辑,数据库只做存储

  • 缓存

    • memcached

    • redis

存储引擎

  • MyISAM

    • MyISAM

    • 表锁

    • 读多写少

  • InnoDB

    • 事务

    • 行锁表锁

分表分库

  • 垂直

  • 水平

    • 范围

    • 取模

  • 中间件路由

    • mycat

      -# 分片

  • lookup映射

读写分离

在线迁移

  • 双机迁移

灾备

  • binlog

  • 双写部署

  • gh-ost

结合Lucene

  • 文本检索

慢查询

  • slow_query_log

  • explain