小新博客

主题

siushin2025-08-16
1110
3.9m

高性能MySQL ​

从架构层上做优化，可以做分布式 ​

数据表设计层 ​

  • 选择合适的存储引擎（innoDB，MyISAM会使用回行操作）

  • 合适的列类型

占用的存储空间尽可能的小，如性别这些可以用tinnyint存储，能固定长度的尽量用固定长度char
因为有时候不可避免的需要用到文件排序filesort，
使用变长类型varchar的话会直接使用该列的最大长度，导致资源不必要的消耗
  • 尽量使用符合三范式的表设计结构
原子性：列是最小单元不可分割
唯一性：可以由主键字段来唯一的确定一条记录
依赖性：将一张表拆分为两张由主键字段关联的的表（比如学生表，班级表）
  • 根据业务对数据库进行分割
垂直分割：将不同的表拆分到不同的服务器上，如微服务
水平分割：如以学校id取模进行分表
MyISAM 存储引擎：建议使用固定长度，数据列代替可变长度的数据列
innoDB 存储引擎：建议使用 varchar 类型

索引设计层 ​

索引是一种空间换时间的策略，在换取时间的同时也有一定的开销，若索引失当，可能开销大于收益

  • 对于连接查询所需要的连接的字段，使用索引

  • 对于经常出现where条件后的字段与用于排序的字段，使用索引

  • 使用复合索引

在排序的时候，如果排序字段跟where条件后的字段分开建立索引，会导致mysql使用where条件后的索引
查询出结果，然后再在此结果上使用filesort进行排序；还有where条件后多个单列索引最终会使用到的
也只是一个，这个时候按照规则建立复合索引就能加大检索的效率

  • 对于一些列类型比较大的字段，可以使用索引的前缀原则，对其固定长度前缀建立索引（长度较长字段建立索引, 索引文件会比较大, 占用较多磁盘空间的同时, 检索索引的时候对io资源消耗也较大），或者使用一种伪造哈希索引，额外增加一个字段，作为该大字段的索引，在插入数据的时候，先对需要进行建立伪哈希索引的索引用哈希函数计算出哈希值，再一起存入数据库中。

  • 对于修改的比较频繁的字段，尽可能避免建立索引（避免索引树的页分裂）

  • 避免冗余索引，比如已经有了一个索引a，现在又建立了一个a,b 那么a索引就是冗余索引，因为a,b 与 a在已a为条件搜索时索引起到的功能是一样的，如果在a字段插入数据的时候，就得同时维护两个索引，造成了不必要的开销

SQL应用层的优化 ​

  • 查询了大量的数据，如limit的查询，举个例子，一张有着三百多万条数据的用户表，跟一张和它一样多的用户详情表，现有一个需求，需要查询出每页20条用户，有以下一句sql select a.username,a.userid,b.face,b.mood,b.date,b.... from user as a inner join user_info as b using userid limit 2500000,10; 再没有带where限制条件的情况下，该语句执行时间达到了30多秒，如果要带上一些排序，那将会产生一个巨大的临时表，将会占用太多的资源

  • 使用合理的索引覆盖，分页的情况下使用延迟关联

  • 不要连接太多的表（mysql连接查询优化机制是根据每个表的行数，索引来制定查询计划，如果表数量达到8个，那么将会有8 * 7 * 6 * 5 * 4 * 3 * 2 * 1种方式选择，那么mysql制定的查询计划可能就没那么准确）

  • 关注执行计划，尽可能的使用到索引，避免扫描过多的行数，避免产生临时表，filesort

最近更新