MySQL百万级/千万级数据存储解决方案

                               MySQL 百万级/千万级数据存储解决方案

百万级、千万级数据处理，个人认为核心关键在于数据存储方案设计，存储方案设计的是否合理，直接影响到数据CRUD操作。总体设计可以考虑一下三个方面进行设计考虑：

1. 数据存储结构设计
2. 索引设计
3. 数据主键设计
4. 查询方案设计

百万级数据处理方案：

数据存储结构设计

表字段设计

1. 表字段 not null，因为 null 值很难查询优化且占用额外的索引空间，推荐默认数字 0。
2. 数据状态类型的字段，比如 status, type 等等，尽量不要定义负数，如 -1。因为这样可以加上 UNSIGNED，数值容量就会扩大一倍。
3. 可以的话用 TINYINT、SMALLINT 等代替 INT，尽量不使用 BIGINT，因为占的空间更小。
4. 字符串类型的字段会比数字类型占的空间更大，所以尽量用整型代替字符串，很多场景是可以通过编码逻辑来实现用整型代替的。
5. 字符串类型长度不要随意设置，保证满足业务的前提下尽量小。
6. 用整型来存 IP。
7. 单表不要有太多字段，建议在20以内。
8. 为能预见的字段提前预留，因为数据量越大，修改数据结构越耗时。

索引设计

1. 索引，空间换时间的优化策略，基本上根据业务需求设计好索引，足以应付百万级的数据量，养成使用 explain 的习惯，关于 explain 也可以访问：explain 让你的 sql 写的更踏实了解更多。
2. 一个常识：索引并不是越多越好，索引是会降低数据写入性能的。
3. 索引字段长度尽量短，这样能够节省大量索引空间；
4. 取消外键，可交由程序来约束，性能更好。
5. 复合索引的匹配最左列规则，索引的顺序和查询条件保持一致，尽量去除没必要的单列索引。
6. 值分布较少的字段（不重复的较少）不适合建索引，比如像性别这种只有两三个值的情况字段建立索引意义不大。
7. 需要排序的字段建议加上索引，因为索引是会排序的，能提高查询性能。
8. 字符串字段使用前缀索引，不使用全字段索引，可大幅减小索引空间。

查询语句优化

1. 尽量使用短查询替代复杂的内联查询。
2. 查询不使用 select *，尽量查询带索引的字段，避免回表。
3. 尽量使用 limit 对查询数量进行限制。
4. 查询字段尽量落在索引上，尤其是复合索引，更需要注意最左前缀匹配。
5. 拆分大的 delete / insert 操作，一方面会锁表，影响其他业务操作，还有一方面是 MySQL 对 sql 长度也是有限制的。
6. 不建议使用 MySQL 的函数，计算等，可先由程序处理，从上面提的一些点会发现，能交由程序处理的尽量不要把压力转至数据库上。因为多数的服务器性能瓶颈都在数据库上。
7. 查询 count，性能：count(1) = count(*) > count(主键) > count(其他字段)。
8. 查询操作符能用 between 则不用 in，能用 in 则不用 or。
避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符，因为这些查询无法使用索引。
9. sql 尽量简单，少用 join，不建议两个 join 以上。

 

千万级数据处理方案：