MySQL 优化总结

查询优化

  1. 仅列出需要查询的字段,这对速度不会有明显影响,主要考虑节省内存。
  2. 索引列不能是表达式的一部分,也不能是函数的参数,这样会导致索引失效。
  3. 避免通配符开头的 LIKE 模糊查询。
  4. 分解关联查询。因为 JOIN 多个表时,可能导致更多的锁定和堵塞。
  5. 不要做无谓的排序操作,而应尽可能在索引中完成排序。如果 EXPLAIN 出来的 type: index,则说明 MySQL 使用了索引扫描来做排序。

索引优化

最常见的 B-Tree 索引,使用 B-Tree 数据结构来存储索引,存储引擎以不同的方式使用 B-Tree 索引,性能也各有不同,各有优劣。

B-Tree 索引,按照顺序存储数据,所以 MySQL 可以用来做 ORDER BY 和 GROUP BY 操作。因为数据是有序的,所以 B-Tree 也就会将相关的列值都存储在一起。最后,因为索引中存储了实际的列值,所以某些查询只使用索引就能够完成全部查询。

  1. 选择性低的列上没必要建索引。比如性别字段只有 0 和 1 两种结果集,在这个字段上建索引并不会有太多帮助。
  2. 索引可以包含一个或多个列的值。如果索引包含多个列,那么列的顺序也十分重要,选择合适的索引列顺序经验法则:将选择性最高的列放到索引最前列。
  3. 对于非常小的表,大部分情况下简单的全表扫描更高效。
  4. MySQL 可以使用同一个索引既满足排序,又用于查找行。因此,如果可能,设计索引时应该尽可能地同时满足这两种任务。
  5. 索引并非越多越好,因为维护索引需要成本,在修改表的内容时,索引必须进行更新,有时可能需要重构。每个表的索引应在 5 个以下。

库表结构优化

  1. 选择合适的存储引擎,MyISAM 注重性能,InnoDB 注重事务。
  2. 使用小而简单的数据类型。
  3. 尽量避免 NULL。
    • 如果查询中包含可为 NULL 的列,对 MySQL 来说更难优化,因为可为 NULL 的列使得索引、索引统计和值比较都更复杂。可为 NULL 的列会使用更多的存储空间,在 MySQL 里也需要特殊处理。当可为 NULL 的列被索引时,每个索引记录需要一个额外的字节。
    • 如果计划在列上建索引,就应该尽量避免设计成可为 NULL 的列。
  4. 范式和反范式。
  5. 缓存表和汇总表。

其他优化

当 MySQL 单表数据量达到千万级别以上时,无论如何对 MySQL 进行优化,查询如何简单,MySQL 的性能都会显著降低,这时候可以采取以下措施:

  1. 增加 MySQL 配置中的 buffer 和 Cache 的数值,增加服务器 CPU 数量和内存的大小。
  2. 迁移到其他数据库。如开源的 PostgreSQL 和商业的 Oracle。与 Oracle 和 PostgreSQL 相比,MySQL 属于线程模式,并且采用了插件引擎的架构。这种实现的确有自己的优势:轻巧快速、系统资源消耗小、支持更多并发连接,但进程模式能充分地应用 CPU 资源,在应付复杂业务查询上更有优势。在常规优化的前提下,Oracle 的单表性能瓶颈经验值在 2 亿数据量的级别,远远优于 MySQL。在关联查询和内置函数等功能上,Oracle 都是完胜 MySQL 数据库的。
  3. 对数据库进行分区、分表操作,减少单表体积。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

昵称 *