【数据库】必须知道的MySQL优化

SQL语言有哪几部分组成

• 数据定义语言简称DDLDROP,CREATE,ALTER等语句。
• 数据操作语言简称DML也就是插入insert修改update删除delete等操作语句。
• 数据查询语言简称DQL就是常用的select语句了。
• 数据控制语言简称DCL比如grantrevokecommitrollback等语句

在工作中这些语言都有用到特别是DROP,CREATE,ALTER,INSERT,UPDATE,DELETE,SELECT等语句在实际的业务开发中复杂的业务场景下需要创建表查询表。都离不开要写SQL语句来完成开发工作任务了随着时间的增长数据库的数据会越来越多有可能会出现单表千万以上的数据量这个时候你就会发现系统数据返回就变慢了然后就去找原因就需要进行优化之路了。

为什么要进行MySQL优化

为什么要进行MySQL优化我觉得应该有以下几个原因吧

1. 系统出现了瓶颈就需要找出来优化掉查询慢的SQL语句提高MySQL数据库整体性能。
2. 表结构设计不合理冗余字段太多了。就需要进行合理的结构设计和参数调整来提高响应速度。
3. 资源利用上要尽量控制在系统前期可以用少的资源获得好的效果后期可以根据系统具体情况进行资源上的补充。

优化方法有哪些

优化的方法有哪些不同的开发工程师可能使用的办法也不一样吧。但是目标是一致的都是要提高系统的性能。

所以我觉的可以从一下几个方面入手去操作吧

SQL层面优化

使用SHOW STATUS L语句查询一些MySQL数据的性能参数。
SHOW STATUS LIKE ‘value’value就是要查询的参数值常用参数如下表

参数	说明
Connections	是指连接mysql服务器的次数
slow_queries	是指慢查询的次数
uptime	MySQL服务器上线时间
Com_insert	插入操作次数
Com_delete	删除操作次数
Com_update	修改操作次数
Com_select	查询操作次数

可以根据相应的参数判断需要优化的sql语句接下来就可以用执行计划EXPLAIN 来判断了

explain select * from t_order  where  user_id =1

结果如下图

可以看到有几列有参数都一一解释一下
1.通过select_type可以判断查询语句的类型这里simple表示简单查询不包括连接查询和子查询。
2.type表示表的连接类型这里的ALL表示对当前的表进行全表扫描。
3.rows列值是1表示只有一条记录
4.Extra该列表示sql在处理查询的是的详细信息这里表示使用了where过滤

这里的查询没有用到索引所以需要加索引优化那还需要进行以下优化

• SQL查询基于索引来进行数据扫描
• 使用索引扫描联合索引要遵循最左前缀原则也就是列从左往右命中越多越好
• 查询有效的列信息返回就好了避免使用‘*’查询返回全部列参数
• where语句中like %号尽量放在右边
• 在查询时小结果集驱动大结果集原则

MySQL配置方面

MySQL默认的配置文件是my.cnfmy.ini

如上图有很多默认的配置参数可以根据业务需要进行相应的更改。可以看到最大连接数是151个。binlog日志默认是不开启的。需要注意的是配置作用域分为全局和会话级别是否支持热加载。这些参数的更改都是要根据实际情况进行的。

架构设计方面

MySQL是关系型数据库在高并发场景下会承受巨大的压力就需要从架构设计方面进行优化了。主要包括
1.搭建MySQL主从集群单个MySQL服务容易出现单点故障一旦服务宕机应用全部无法响应主从集群或者主主集群可以保证服务的可用性。
2.读写分离设计在读多写少的场景中通过读写分离方案可以避免读写导致的性能影响。
3.针对热点数据可以引入非关系型数据库如RedisMongoDB等可以很好的缓解MySQL访问压力也能提高数据检索性能。
4.引入分库分表机制通过分库可以降低单个服务节点的IO压力通过分表的方式可以降低单表数据量从而提升sql查询效率。

硬件和操作系统方面.

硬件和操作系统方面的话大厂一般都是有专门的DBA或者运维工程师去负责优化的但是小厂创业公司这些都是有开发工程是去操作的所以多掌握一些技能是必不可少的。
从硬件层面上影响MySQL性能的因素有CPU可用内存大小。磁盘读写速度网络带宽等等
从操作系统上操作系统网络的配置文件句柄数都会影响到MySQL性能。