MySQL 数据过多时的优化--图文并茂版

图

文

单表优化

字段设计

• 单表不要有太多字段；

• VARCHAR 的长度尽量只分配真正需要的空间；

• 尽量使用TIMESTAMP而非DATETIME；

• 避免使用NULL，通过设置字段默认值来解决；

• 尽量符合三范式设计。

  • 第一范式（1NF）：表的列具有原子性，不可拆分，只要是关系型数据库那么就自动符合第一范式（1NF）的要求；

  • 第二范式（2NF）：满足第二范式必须先满足第一范式，确保表中每一列都和主键有关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中；

    • 表必须有一个主键；

    • 没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的部分。

  • 第三范式（3NF）：满足第三范式的必须先满足第二范式，目标是确定其他非主键列都是与主键列直接相关而不是间接相关，非主键列必须直接依赖于主键，不能存在传递依赖。

索引优化

• 要针对性的建立索引，索引会加速查询，但是对新增、删除、修改会造成一定的消极影响；

• 值域很少的字段不适合建索引，比如用户表的性别，；

• 尽量不用唯一索引，不建外键，使用程序保证。

• 正确的使用索引：

  尽量使用索引，但是需要保证避免错误地使用导致的索引失效问题 ：

  • like 以%开头,索引无效;当like前缀没有%,后缀有%时,索引有效；

  • or语句前后没有同时使用索引，当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效；

  • 组合索引，不是使用第一列索引，索引失效；

  • 数据类型出现隐式转化，如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描；

  • 在索引列上使用 IS NULL 或 IS NOT NULL操作，索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可；

  • 在索引字段上使用not，<>，!=，不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描， 优化方法： key<>0 改为 key>0 or key<0；

  • 对索引字段进行计算操作、字段上使用函数；

  • 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

善用NoSQL

有一些场景，可以抛弃 MySQL 等关系型数据库，拥抱 NoSQL，比如：统计类、日志类、弱结构化的数据；事务要求低的场景。

增加缓存

主要思想就是减少对数据库的访问：MySQL本身就支持缓存，只不过在MySQL 8.0版本中被取消了；第三方的缓存，如 Redis 的缓存。

表分区

MySQL 在 5.1 之后才有的，可以看做是水平拆分，分区表需要在建表的时候加上分区参数。

表拆分

• 垂直拆分

  垂直拆分就是将一个字段较多的表，拆分成多个字段较少的表，一般来说，单个表的字段数不宜超过二、三十个；

• 水平拆分

  • 分库分表：分表，解决了单表数据过大的问题，但是毕竟还在同一台数据库服务器上，所以 IO、CPU、网络方面的压力，并不会得到彻底的缓解，这个可以通过分库来解决。

  • 水平拆分优点很明显，可以利用多台数据库服务器的资源，提高了系统的负载能力；缺点是逻辑会变得复杂，跨节点的数据关联性能差，维护难度大（特别是扩容的时候）。

本作品采用《CC 协议》，转载必须注明作者和本文链接