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

SQL语言有哪几部分组成

• 数据定义语言，简称DDL：DROP,CREATE,ALTER等语句。
• 数据操作语言，简称DML：也就是插入（insert），修改（update），删除（delete）等操作语句。
• 数据查询语言，简称DQL：就是常用的select语句了。
• 数据控制语言，简称DCL：比如grant，revoke，commit，rollback等语句

在工作中，这些语言都有用到，特别是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.cnf（my.ini）

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

架构设计方面

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

硬件和操作系统方面.

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