MySQL性能优化总结

要知道怎么优化MySQL首先要知道什么东西会影响MySQL的效率

1. 商业需求
2. 系统架构
3. 硬件环境
4. 数据库的设计
5. 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规范

• 不建议使用外键索引，但一定哟啊在表与表之间的关联键上建立索引
• 外键可用于保证数据的参照完整性，但建议在业务端实现
• 外键会影响父表和子表的写操作降低性能

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的系统架构和原理方面的，可以评论区或者私信我