MySQL之某个表中有近千万数据且CRUD访问缓慢的优化处理

一、前言

• MySQL 数据库某张表近千万的数据，CRUD比较慢，如何优化？
• 说实话，这个数据量级， MySQL 单库单表支撑起来完全没有问题的，所以首先还是考虑数据库本身的优化。

• 从上图可以看到，数据库优化通常可以通过以上几点来实现：
  • 硬件升级：也就是花更多的钱，升级我们数据库硬件配置，包括 CPU、内存、磁盘、网络等等，但是这个方案成本高，而且不一定能起到非常好的效果。
  • 数据库配置：修改数据库的配置，有可能让我们的 CRUD 操作变得更快，不过我也不建议大家把经历放在这一点上面；首先，数据库的配置通常由专业的 DBA 来负责；第二，大部分时候，默认的数据库配置在大多数情况下已经是最优配置了。

二、优化

① 数据结构的优化，也就是表结构的优化

• 数据类型的选择：选用合适的数据结构。什么叫做"合适的数据结构"，比如性别字段，M表示男F表示女，那么一个 char(1) 就足够了，如果存储人的年龄，那么就没有必要使用 INT 这么大范围的字段了；
• 适当的拆分：千万不要试图把所有的字段放在一张表中，因为这会非常影响性能，通常一张表的字段最好不要超过 30 个；
• 适当的冗余：如果一些常用的字段，可能会用在不同的维度，那么我们可以把这些字段设计在多张表中，因为这样可能会减少表关联；
• 字段尽量设置成 not Null，尽量带有默认值。

② SQL 语句的优化

• 选取最适用的字段属性：表中字段的宽度设得尽可能小：char 的上限为 255 字节（固定占用空间），varchar 的上限 65535 字节（实际占用空间），text 的上限为 65535。尽量把字段设置为 NOT NULL，执行查询的时候，数据库不用去比较 NULL 值。
• 尽量使用索引，尽量避免全表扫描，提高查询速度；
  • 查询语句当中包含有 MAX (), MIN () 和 ORDERBY 这些命令的时候，性能提高更为明显。
  • 索引应建立在那些将用于 JOIN, WHERE 判断和 ORDER BY 排序的字段上。
  • 尽量不要对数据库中某个含有大量重复的值的字段建立索引。
  • 对于一个 ENUM 类型的字段来说，出现大量重复值是很有可能的情况，例如 customerinfo 中的 “province”… 字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。
• 当然你不能无限制地建立索引；维护索引也会影响性能，会降低 DML 操作的速度；
• 注意 SQL 语句的书写，有一些错误的写法可能会导致索引失效；
  • SELECT FROM order WHERE YEAR(OrderDate)<2001;
  • SELECT FROM order WHERE OrderDate<“2001-01-01”;
  • SELECT FROM inventory WHERE Amount/7<24;
  • SELECT FROM inventory WHERE Amount<24*7;
• 尽量避免在 where 子句中对字段进行 Null 值判断（当然我们在表设计中，直接建议不要有 Null）；
• 条件值多的情况下，尽量不要使用 in 和 not in ；
• select 的时候，使用具体的字段代替 * 号；
• 避免返回大量数据，增加分页。

③ 减少数据库的访问

• 我们可以通过增加本地缓存或分布式缓存的方式，将热点数据存储到缓存中，以减少数据库的访问；
• 终极大招，如果是一个不合理的需求，我们可以拒绝做这个需求，这样也算是"减少了数据库访问"。

• 说完了 MySQL 本身的优化，如果数据量进一步增大的话，我们还有什么优化的方案呢？

④ 读写分离

• 主库用于写，从库用于读，将读写分散在不同的数据库上，利用多台机器的资源，来提高数据库的可用性和性能。

⑤ 分库分表

• 如果数据持续增多，超过了单台 MySQL 的支撑上限，那么只能用【分库分表】这一招了；我们可以采用一定的路由规则，将数据保存到不同的数据库中。
• 当然，如果不是“迫不得已”，我是不太建议分库分表的，因为这样极大地增加了系统的复杂程度，并且会带来更多的问题需要开发人员解决。

文章来源: blog.csdn.net，作者：Serendipity·y，版权归原作者所有，如需转载，请联系作者。

原文链接：blog.csdn.net/Forever_wj/article/details/115991595