MySQL优化建议

仅个人总结，欢迎一起交流！

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。

• 避免SELECT *，从数据库里读出的数据越多，查询就会变得越慢。另外，如果数据库服务器和WEB服务器是两台独立的服务器，这还会增加网络传输的负载。

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

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

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

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

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

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

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

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

• 拆分大的 DELETE 或 INSERT 语句，避免较长时间的锁表，防止应用被crash。

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

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

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

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

最后，送大家一份免费MySQL学习资料，立即前往>>>。学无止境，愿大家都能够少走弯路。