MySQL数据库千万级数据查询和存储的示例分析

这篇文章主要介绍MySQL数据库千万级数据查询和存储的示例分析，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

百万级数据处理方案

数据存储结构设计

表字段设计

• 表字段 not null，因为 null 值很难查询优化且占用额外的索引空间，推荐默认数字 0。

• 数据状态类型的字段，比如 status, type 等等，尽量不要定义负数，如 -1。因为这样可以加上 UNSIGNED，数值容量就会扩大一倍。

• 可以的话用 TINYINT、SMALLINT 等代替 INT，尽量不使用 BIGINT，因为占的空间更小。

• 字符串类型的字段会比数字类型占的空间更大，所以尽量用整型代替字符串，很多场景是可以通过编码逻辑来实现用整型代替的。

• 字符串类型长度不要随意设置，保证满足业务的前提下尽量小。

• 用整型来存 IP。

• 单表不要有太多字段，建议在20以内。

• 为能预见的字段提前预留，因为数据量越大，修改数据结构越耗时。

索引设计

• 索引，空间换时间的优化策略，基本上根据业务需求设计好索引，足以应付百万级的数据量，养成使用 explain 的习惯，关于 explain 也可以访问：explain 让你的 sql 写的更踏实了解更多。

• 一个常识：索引并不是越多越好，索引是会降低数据写入性能的。

• 索引字段长度尽量短，这样能够节省大量索引空间；

• 取消外键，可交由程序来约束，性能更好。

• 复合索引的匹配最左列规则，索引的顺序和查询条件保持一致，尽量去除没必要的单列索引。

• 值分布较少的字段（不重复的较少）不适合建索引，比如像性别这种只有两三个值的情况字段建立索引意义不大。

• 需要排序的字段建议加上索引，因为索引是会排序的，能提高查询性能。

• 字符串字段使用前缀索引，不使用全字段索引，可大幅减小索引空间。

查询语句优化

• 尽量使用短查询替代复杂的内联查询。

• 查询不使用 select *，尽量查询带索引的字段，避免回表。

• 尽量使用 limit 对查询数量进行限制。

• 查询字段尽量落在索引上，尤其是复合索引，更需要注意最左前缀匹配。

• 拆分大的 delete/insert 操作，一方面会锁表，影响其他业务操作，还有一方面是 MySQL 对 sql 长度也是有限制的。

• 不建议使用 MySQL 的函数，计算等，可先由程序处理，从上面提的一些点会发现，能交由程序处理的尽量不要把压力转至数据库上。因为多数的服务器性能瓶颈都在数据库上。

• 查询 count，性能：count(1)=count(*)> 在计数(主键);计数(其他字段)。

  <李>

  查询操作符能之间用则不用,能在则用不用。

  <李>

  避免使用!=或<>,是NULL或空,,不是在等这样的操作符,因为这些查询无法使用索引。

  <李>

  sql尽量简单,少用加入,不建议两个加入以上。

千万级数据处理方案

数据存储结构设计

到了这个阶段的数据量,数据本身已经有很大的价值了,数据除了满足常规业务需求外,还会有一些数据分析的需求。而这个时候数据可变动性不高,基本上不会考虑修改原有结构,一般会考虑从分区,分表,分库三方面做优化:

分区: