MySQL学习笔记（4）——优化

数据库
, ,

表设计

数据库设计三大范式

  • 确保每列都是不可分割的原子项。
  • 确保每列都和主键相关。
  • 确保每列和主键直接相关，而不是间接相关。

使用合适的数据类型

  • 对于字符串，固定长度使用char，非固定长度使用varchar。对于InnoDB而言，推荐使用varchar。
  • 对于小数，关于财务方面需要精确数据的，使用decimal，其他情况使用float或者double。
  • 对于日期类型，尽量使用timestamp，它占用空间小且自动进行时区转换。
  • 对于大数据，text只能存储字符型数据，blob可以存储二进制数据，例如图片。
  • 尽可能使用NOT NULL定义字段。

建立索引的原则

  • 对经常使用WHERE、ORDER BY、GROUP BY的字段建立索引，避免全表扫描。
  • 对区分度高的字段建立索引，而例如性别等区分度低的字段不适合建立索引。
  • 索引并不是建的越多越好，过多的索引会导致INSERT、UPDATE等写入操作的效率降低。
  • 如果多个字段常用AND连接，应当建立联合索引，而不是单独为每个字段建立索引。

MySQL查询过程

  1. 客户端连接数据库，连接器进行权限验证；
  2. 客户端发送查询语句，MySQL首先会查询缓存中是否执行过该语句，如果命中则返回缓存的结果，否则进入下一步；
  3. 对输入的SQL语句进行语法分析，识别字符串为对象，检查SQL语句是否符合语法；
  4. 优化器对SQL语句进行优化，比如存在多个索引时选择走哪一个索引，存在多表关联时选择表连接的顺序；
  5. 执行器先验证权限，再调用引擎的接口，将符合条件的行保存在结果集合中。

查询优化

  • 在WHERE子句中，应当避免使用函数、运算、不等号（<>或!=）、OR、IN、NOT IN、IS NULL、LIKE查询以%开头，这些会导致索引失效。
  • 注意使用同类型进行比较，字符串使用引号括起来。
  • 使用联合索引注意最左前缀原则。
  • 避免使用SELECT *，用具体的字段代替*。
  • 存储引擎使用聚簇索引时，尽量利用覆盖索引提升查询性能。
  • 使用LIMIT限制返回结果的数量。
  • 避免使用子查询和JOIN。
  • 使用BETWEEN代替IN。

EXPLAIN命令

explain SELECT ... FROM t WHERE ...用于显示MySQL为SQL语句准备的执行计划，查看是否走了索引、走了什么样的索引，可以用于优化索引和查询语句。主要关注以下几条信息：

  • type：表示MySQL走的索引类型，常见的类型如下（按性能由好到差排列）：
    • system：表仅有一行，是const的特例。
    • const：表最多有一个匹配行，将在查询开始时被读取。
    • eq_ref：最多返回一条符合条件的记录，使用主键索引或者唯一索引时出现。
    • ref：返回所有符合条件的记录，使用非主键索引、非唯一索引或者联合索引时出现。
    • range：范围查找。
    • index：遍历全索引树进行查找。
    • ALL：全表查找。
  • possible_keys：若查询涉及到的字段上存在索引，则该索引被列出，但不一定被使用。
  • key：实际使用的索引。
  • ref：显示哪些字段或常量被用于查找索引列上的值。
  • rows：估计需要读取的行数，这个值越小越好。
  • extra：主要关心是否出现Using index，如果出现则表示使用了覆盖索引。

分库分表

垂直拆分

  • 垂直分表是按照字段的活跃性，把一个表中的字段拆分到不同的表中。
  • 每个表的数据不同，每个表都有一列交集（一般为主键）用于关联数据，所有表的并集是全量数据。
  • 字段较多，热门字段和非热门字段放在一起，单行数据占用空间大，查询时IO开销大，产生IO瓶颈。垂直分表减少了随机IO，但查询时需要获取多个表的数据来获得全部数据。

水平拆分

  • 水平分表是将数据行按照一定策略（如Hash取模、时间戳等）拆分到多个表中。
  • 水平分库是将数据行按照一定策略拆分到多个库中。每个库中数据的结构一致，且没有交集。
  • 水平分库分表提升数据库整体的并发量和负载能力。
  • 按照时间戳或者ID范围切分：
    • 单表大小可控，便于水平扩展，避免跨分片查询。
    • 近期的热点数据被频繁读写，早期的数据很少被查询，使得热点数据成为性能瓶颈。
  • Hash取模切分：
    • 数据分片均匀，不会造成并发访问的瓶颈。
    • 后期集群扩容时，需要迁移旧的数据（使用一致性Hash算法可解决这类问题）；容易出现跨分片查询。

分布式ID

  • UUID：优点：易于实现，本地生成。缺点：UUID占用大量存储空间且无序，在B+树索引上引起数据频繁变动。
  • MySQL单独维护自增主键ID表：优点是易于实现。缺点：可用性低，容易成为性能瓶颈。
  • 设置自增步长的自增ID：优点是易于实现。缺点：后续扩容升级较为麻烦，也没有解决性能问题。
  • Redis自增ID：Redisincr原子自增，高并发。缺点：RDB持久化容易丢失数据，AOF持久化损失性能。
  • Snowflake：64位的Long型数字，其中第1位不使用，41位为毫秒级时间戳，5位为数据中心ID，5位为工作机器ID，12位为毫秒内的序列号。优点：自增，高性能，灵活。缺点：依赖强机器时钟。
  • Leaf Segment：一次性批量获取自增ID号段，每台机器只使用本地缓存的ID，耗尽之后再去获取。优点：易于扩展，容灾性高。缺点：ID不够随机，大量请求可能造成阻塞。

分布式事务

  • 两阶段提交：分为协调者和参与者节点。
    • 第一阶段：协调者向所有的参与者发送事务预处理请求，开始等待各参与者的响应；各个参与者节点在本地执行事务操作，但不提交，而是向协调者报告它执行事务是否成功；
    • 第二阶段：如果第一阶段所有参与者都报告本地事务执行成功，则告知所有参与者正式提交事务；如果有任何一个参与者报告事务失败或者超时，告知所有参与者回滚。

读写分离

主库负责写，从库负责读，这样即使主库需要锁表而不能读时，也能从从库读取保证业务正常进行；当主节点挂了，也能从从库中选取一个作为新的主节点。

主从复制

  1. Slave连接到Master，Master创建binlog dump线程。
  2. Master的binlog发生变化时，dump线程会通知所有的Slave，并将binlog内容推送给Slave。
  3. Slave执行start slave命令，创建IO线程用于从主节点接收binlog，将内容写到本地的relay log。
  4. Slave的SQL线程负责读取relay log中的内容，解析成具体的操作并执行，保证主从数据的一致性。

主从复制模式：

  • 同步：Master等待所有的Slave都回应后才提交。
  • 半同步：Master等待至少一个Slave回应就可以提交。
  • 异步：Master不会等待Slave回应就提交。