1. 您现在的位置:首页
  2. 新闻
  3. 技术支持
  4. MySQL优化建议

MySQL优化建议

2020-12-25 阅读数:22

MySQL优化的本质:

      1. 结合MySQL数据库的主要特性(数据存储与数据查询),使得数据存储占用空间更小,更新、查询速度更快,并发程度更高,使得服务器资源利用率更高。

      2. 尤其是对于大数据量、高并发的业务场景,避免因不合理的设计拖垮数据库,造成数据库宕机、数据丢失、业务无法正常进行等问题,保证服务的可用性。

以下为本人总结的部分优化建议:

  • 选择InnoDB引擎。InnoDB引擎支持事务、行级锁、并发性能更好,CPU及内存缓存页优化使得资源利用率更高。

  • 使用UTF8字符集,UTF-8MB4。无需转码,不存在乱码的风险,更节省空间。

  • 减少存储过程、视图、触发器等使用,尽量将计算逻辑设计运用至业务代码层面,避免因耗时计算影响到数据库的并发,严重时拖垮数据库。

  • 使用自增主键。Innodb是基于B+树的索引组织表,使用自增主键,数据行写入可以提高插入性能,可以避免页分裂,减少表碎片提升空间和内存的使用。

  • 禁止使用外键,外键导致表和表之间的耦合。更新和删除操作涉及关联的表,极大地影响sql的性能,甚至可能造成死锁。

  • 禁止使用TEXT、BLOB类型。更多的磁盘和内存空间将被浪费,且大字段查询将会清理掉热点数据的缓存,导致缓存命中率急剧下降并影响数据库性能。如有必要,创建另一个表记录,将热点数据与大字段数据分离。

  • 适当的建立索引,避免全表扫描。

  • 不要过多的建立索引,建议控制在5个以内。索引的本质是对数据顺序的记录,占用存储空间,不宜过多。可使用EXPLAIN来查看了解索引命中情况。

  • 索引的建立原则:为常用于where或orderby语句后的字段建立索引,但对于数据值变化少(区分度不大)的情况不适用,如“性别”字段,一般值为“男/女/未知”等,建立这种索引并不能加快查询速度,却浪费了磁盘空间。

  • 更新十分频繁的字段上不宜建立索引。更新操作会变更B+树,重建索引,这个过程十分耗性能。

  • 联合索引的建立要满足“最左匹配”原则,将命中率更高的字段建立在最前面。

  • 列值设置默认值,禁止null值,以防破坏索引,影响查询效率。

  • 不在where语句后使用函数或者表达式计算,会造成索引失效。

  • 不使用NOT IN和<>操作,%开头的模糊查询,避免全表扫描;

  • 避免使用属性隐式转换。如phone='123456789’为字符串不写成phone=123456789。

  • 尽量建立覆盖索引,避免回表查询。

  • 唯一/普通索引的选择。唯一索引需要唯一性检查,不能利用 change buffer特性。需要频繁从磁盘读取数据,涉及随机 IO 的访问,效率变低。所以能使用普通索引的场景尽量不使用唯一索引。

  • 更新条件列要使用索引,避免无法通过索引键加锁而升级为表级锁。

  • varchar字段过长时考虑使用前缀索引。

  • 禁止对大型表使用JOIN查询或子查询。会生成临时表,消耗内存和CPU,极大地影响数据库性能。关联查询可在业务代码层拆分为多次单表查询。

  • 避免存储大文件。大文件可存至专门的文件系统,如OSS。

  • 根据业务需要合理地确定字段类型与长度,避免空间浪费。

  • 大事务尽量拆成多个小事务,减少锁记录和时间。

  • 最长事务代码块尽量放在程序的最后执行。

  • 尽量减少基于范围的数据检索过滤条件更新,避免间隙锁带来的影响而锁定了不该锁定的记录。

  • 注意间隙锁,批量更新&删除操作尽量避免并发,以免造成意外的死锁。

  • 数据量大的业务系统,考虑分库分表,按业务系统的划分垂直分库,单表数据量过大考虑分表(水平/垂直分表)