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 */;
分区表中无法使用外键约束。
NULL值会使分区过滤无效,这样会被放入默认的分区里,请千万不要让分区字段出现 NULL。
所有分区必须使用相同的存储引擎。
2.分表
分表分水平分表和垂直分表。
水平分表即拆分成数据结构相同的各个小表,如拆分成 table1, table2…,从而缓解数据库读写压力。
垂直分表即将一些字段分出去形成一个新表,各个表数据结构不相同,可以优化高并发下锁表的情况。
可想而知,分表的话,程序的逻辑是需要做修改的,所以,一般是在项目初期时,预见到大数据量的情况,才会考虑分表。后期阶段不建议分表,成本很大。
3.分库
分库一般是主从模式,一个数据库服务器主节点复制到一个或多个从节点多个数据库,主库负责写操作,从库负责读操作,从而达到主从分离,高可用,数据备份等优化目的。
当然,主从模式也会有一些缺陷,主从同步延迟,binlog 文件太大导致的问题等等,这里就不细讲了!
到此,关于“mysql的优化方案有哪些”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注创新互联网站,小编会继续努力为大家带来更多实用的文章!
本文标题:mysql的优化方案有哪些
文章来源:http://ybzwz.com/article/jhseop.html