MySQL优化方案了解吗？

在去面试的时候经常会遇到技术面试官问到这样的问题：聊一下你对MySQL性能优化的方案。那么这篇文章就来聊一下MySQL优化的个人见解

那为什么要进行MySQL优化呢？

我觉得应该有以下几个原因吧

1. 系统出现了瓶颈，就需要找出来，优化掉查询慢的SQL语句，提高MySQL数据库整体性能。

2. 表结构设计不合理，冗余字段太多了。就需要进行合理的结构设计和参数调整，来提高响应速度。

3. 资源利用上要尽量控制，在系统前期可以用少的资源获得好的效果，后期可以根据系统具体情况进行资源上的补充。

SQL语言部分组成

数据定义语言，简称DDL：DROP,CREATE,ALTER等语句。

数据操作语言，简称DML：也就是插入（insert），修改（update），删除（delete）等操作语句。

数据查询语言，简称DQL：就是常用的select语句了。

数据控制语言，简称DCL：比如grant，revoke，commit，rollback等语句

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

优化方法

1

SQL层面优化

使用SHOW STATUS L语句查询一些MySQL数据的性能参数。

SHOW STATUS LIKE 'value'，value就是要查询的参数值，常用参数如下表：

可以根据相应的参数判断需要优化的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 %号尽量放在右边

- 在查询时小结果集驱动大结果集原则

2

MySQL配置方面

MySQL默认的配置文件是my.cnf（my.ini）。

my.ini文件部分内容：

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

3

架构设计方面

MySQL 关系型数据库在高并发场景下，会承受巨大的压力，就需要从架构设计方面进行优化了。主要包括：

1.搭建MySQL主从集群，单个MySQL服务容易出现单点故障，一旦服务宕机，应用全部无法响应，主从集群或者主主集群可以保证服务的可用性。

2.读写分离设计，在读多写少的场景中，通过读写分离方案可以避免读写导致的性能影响。

3.针对热点数据，可以引入非关系型数据库，如Redis，MongoDB等，可以很好缓解MySQL访问压力，也能提高数据检索性能。

4.引入分库分表机制，通过分库可以降低单个服务节点的IO压力，通过分表的方式可以降低单表数据量，从而提升sql查询效率。

4

硬件和操作系统方面

硬件和操作系统方面的话大厂一般都是有专门的DBA或者运维工程师去负责优化的，但是小厂，创业公司，这些都是有开发工程师去操作的，所以，多掌握一些技能是必不可少的。

从硬件层面上，影响MySQL性能的因素有：CPU，可用内存大小。磁盘读写速度，网络带宽等等。

从操作系统上，操作系统网络的配置，文件句柄数都会影响到MySQL性能。

在面试的时候这样回答，是不是更好一些呢？