MySQL性能优化总结
影响性能的相关因素
要知道怎么优化MySQL首先要知道什么东西会影响MySQL的效率
- 商业需求
- 系统架构
- 硬件环境
- 数据库的设计
- sql语句
在整个系统的性能优化中,如果按照百分比来划分上面几个层面的优化带来的性能收益,可以得出大概如下的数据:
需求和架构及业务实现优化:55%
sql语句的优化:30%
数据库自身的优化:15%
商业需求角度
从商业角度来说 ,如果一个需求本身就很离谱,那么这个性能是不可能高的,所以这点我们基本上是没有优化空间,但不是完全没有,比如可以直接否决掉这种离谱的功能
系统架构角度
第一点:最任意实现也是最基本的一点,就是尽量不要在数据库放一些奇怪的数据,比如二进制多媒体数据,流水队列数据,超大文本数据,可以使用类似阿里的对象存储来存大文件
第二点:是否合理利用了应用层的缓存机制?
- 我们可以把不需要经常改动但需要经常查询的数据放进缓存中,比如用户的基本信息等
第三点:不要过度依赖sql语句的查询,能在代码中实现的就不要放在sql中来做
第四点:一些不合理的系统架构
- 比如缓存的不合理利用导致缓存命中率底下,浪费缓存的同时又浪费数据库的效率
- 过度依赖面向对象思想也会给系统带来不必要的压力
- 对可扩展性的过度追求,使系统被拆分的过度离散,需要使用大量的join语句
- 过度依赖数据库,把大量更适合放入文件系统的文件放入数据库,会造成资源的浪费和整体性能下降
- 过度理想化系统的用户体验,使大量的非核心业务消耗过多的资源
硬件环境角度
这一点很简单,服务器的cpu和内存直接影响了数据库的操作效率,服务器太差怎么优化都没用,公司有钱的可以直接升级服务器,简单又直接
数据库的设计角度
数据库设计
1.在innodb能满足需求的情况下必须使用innodb,因为innodb支持事务,支持行级锁、更好的恢复性、高并发下性能更好,
2.字符集统一使用UTF8,兼容性更好,避免乱码,如果有存储emoji表情的需要,可以使用UFT8mb4字符集
3.使用comment从句添加表和列的备注,给所有字段和表都加上注释
4.单表数据量大小建议控制在500万以内,500万并不是MySQL的极限,但不建议过大,会造成表结构修改、备份恢复都有问题,可以使用分库分表等
5.谨慎使用分区表,分区表在物理上为多个文件,在逻辑上为一个表
6.尽量冷热分离,减少表的宽度,比如 用户的昵称和账号等信息基本不会改变,但是可能用户的积分等信息会经常改变,就可以把数据进行冷热分离
数据库字段
1.优先选择符合存储需要的最小的数据类型,字段越大单页节点存储的数量就越少,io操作就越多,索引性能就越差
2.避免使用TEXT,BLOB类型数据,因为mysql的内存临时表不支持大数据,如果一定要使用这种类型,建议分出到单独的表中,并且这两个类型只支持前缀索引,
3.避免使用ENUM类型,因为ENUM类型的orderBy操作效率低
4.尽量把所有列定义为NOT NULL,因为索引NULL需要额外空间来保存,并且比较和计算时要对NULL值做特殊处理
5.使用TIMESTAMP(4个字节)或DATETIME(8个字节)存储时间,不要用字符串类型
6.跟金融相关的数据必须用decimal类型
索引设计
1.单表索引建议不超过5个,增加查询效率的同时会减少插入和更新的效率
2.禁止给表中每一列都简历单独的索引
3.innodb的每个表必须有一个主键
4.常见索引列建议:
- 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
- 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
- 并不要将符合 1 和 2 中的字段的列都建立一个索引, 通常将 1、2 中的字段建立联合索引效果更好
- 多表 join 的关联列
5.如何选择索引列的顺序:
- 把区分度(列中不同值的数量/列的总行数)最高的放在左侧
- 字段长度小的放在左侧,因为长度越小一页能存储的数据量就越大,IO性能就越好
- 频繁使用的放在左侧
6.避免简历冗余索引和重复索引:
- 重复索引示例:primary key(id)、index(id)、unique index(id)
- 冗余索引示例:index(a,b,c)、index(a,b)、index(a)
7.对于频繁的查询优先考虑使用覆盖索引(包含了所有查询字段 (where,select,ordery by,group by 包含的字段) 的索引 ),覆盖索引的好处:
- 避免innodb进行索引的二次查询
- 可以把随机IO变成顺序IO加快查询效率
8.索引SET规范
- 不建议使用外键索引,但一定哟啊在表与表之间的关联键上建立索引
- 外键可用于保证数据的参照完整性,但建议在业务端实现
- 外键会影响父表和子表的写操作降低性能
sql语句角度
SQL开发规范
1.建议使用预编译语句进行数据库操作
相同语句可以一次解析,多次使用,提高处理效率
2.避免数据类型的隐式转换,因为会导致索引失效
例如
select name,phone from customer where id = '111';
id本不是字符串类型,但本身可以使字符串转换成int类等
3.充分利用表上以及存在的索引:
- 避免使用双%号的查询条件。如: a like ‘%123%’ ,(如果无前置%,只有后置%,是可以用到列上的 索引的)
- 一个 SQL 只能利用到复合索引中的一列进行范围查询。如:有 a,b,c 列的联合索引,在查询条件中有 a 列的范围查询,则在 b,c 列上的索引将不会被用到。
- 在定义联合索引时,如果 a 列要用到范围查找的话,就要把 a 列放到联合索引的右侧,使用 left join 或 not exists 来优化 not in 操作,因为 not in 也通常会使用索引失效。
4.数据库设计时应该要对以后扩展进行考虑
5.连接不同的数据库使用不同的账号,禁止跨库查询
6.禁止使用SELECT*
7.禁止使用不含字段列表的INSERT语句
8.避免使用子查询,可以把子查询优化为join
9.避免使用join关联太多的表
10.减少同数据库的交互次数
11.使用in代替or,如果是连续的值,使用between and代替in
12.禁止使用order by rand将进行随机排序
13.WHERE从句中禁止堆列进行函数转换和计算
14.在明显不会有重复值时使用UNION ALL而不是UNION
15.拆分复杂的大SQL为多个小SQL
数据库行为操作规范
1.超100万行的批量更改操作,要分批多次进行操作,因为可能会造成严重的主从延迟和大事务
2.对于大表使用 pt-online-schema-change 修改表结构 避免大表修改产生的主从延迟 避免在对表字段进行修改时进行锁表
3.禁止为程序使用的账号赋予 super 权限 当达到最大连接数限制时,还运行 1 个有 super 权限的用户连接 super 权限只能留给 DBA 处理问题的账号使用
4.对于程序连接数据库账号,遵循权限最小原则 程序使用数据库账号只能在一个 DB 下使用,不准跨库 程序使用的账号原则上不准有 drop 权限
如果想看msyl的系统架构和原理方面的,可以评论区或者私信我