mysql的优化方案有哪些

这篇文章主要介绍“MySQL的优化方案有哪些”，在日常操作中，相信很多人在mysql的优化方案有哪些问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”mysql的优化方案有哪些”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

创新互联公司是一家专业提供吐鲁番企业网站建设,专注与网站设计制作、做网站、H5响应式网站、小程序制作等业务。10年已为吐鲁番众多企业、政府机构等服务。创新互联专业网站建设公司优惠进行中。

1. 字段选择优化

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

2. 索引设计优化

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

一个常识：索引并不是越多越好，索引是会降低数据写入性能的。
索引字段长度尽量短，这样能够节省大量索引空间
取消外键，可交由程序来约束，性能更好。
复合索引的匹配最左列规则，索引的顺序和查询条件保持一致，尽量去除没必要的单列索引。
值分布较少的字段（不重复的较少）不适合建索引，比如像性别这种只有两三个值的情况字段建立索引意义不大。
需要排序的字段建议加上索引，因为索引是会排序的，能提高查询性能。
字符串字段使用前缀索引，不使用全字段索引，可大幅减小索引空间。

3.查询语句优化

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

千万级数据量

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

1.分区

分区是根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分，是一种水平划分。对应用来说是完全透明的，不影响应用的业务逻辑，即不用修改代码。因此能存更多的数据，查询，删除也支持按分区来操作，从而达到优化的目的。如果有考虑分区，可以提前做准备，避免下列一些限制：

一个表最多只能有1024个分区（mysql5.6之后支持8192个分区）。但你实际操作的时候，最好不要一次性打开超过 100 个分区，因为打开分区也是有时间损耗的。
如果分区字段中有主键或者唯一索引列，那么所有主键列和唯一索引列都必须包含进来，如果表中有主键或唯一索引，那么分区键必须是主键或唯一索引。对于这点，笔者补充说明，笔者本人是按 created_at 所在天进行分区的，所以建表如下

CREATE TABLE `sms_record_hash` (
  ...... 省略字段定义
  PRIMARY KEY (`id`,`created_at`)  
  ......
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY HASH (DAYOFYEAR(created_at))
PARTITIONS 366 */;