Mysql性能调优

一，优化客户端服务端配置

当用户请求过多时，服务端会承受较大压力，为防止这种情况出现，服务端要尽可能的优化配置。

（1）增加服务端的最大连接数

（2）根据业务需求可以降低默认连接的超时时间，更快回收并复用；

（3）使用连接池复用连接；

二，架构优化

1，缓存

使用缓存，将时效性较低的数据存入redis缓存中，当收到来自客户端请求时，直接从缓存系统拿去数据发送给客户端，减少了数据库压力。

2，读写分离（集群，主从复制）

设置集群服务器，比如一号服务器为增删改服务器，只处理客户端的增删改请求，设置二三号服务器为搜索服务器，只处理搜索请求。每当接受客户端请求时，判断请求后分流，减少服务器压力，并通过主从复制保证服务器数据的一致性；

3，分库分表

水平分库分表降低了存储的瓶颈，垂直分库分表减轻了高并发的压力；

4，消息队列削峰

用户请求暴增时效果很好，将多余用户请求显存入消息队列，系统再按照一定频率从MQ中消费请求，削峰可以降低数据库压力；

三，SQL优化

1，定位执行效率慢的 sql 语句

1.命令:show status like 'Com_%',通过这条命令, 我们可以知道当前数据库是以查询为主还是更新为主. 如果是查询为主, 就重点查询; 如果增删改多就重点优化写入操作.

Com_select：执行select 操作的次数，一次查询只累加1。

Com_insert：执行INSERT 操作的次数，对于批量插入的INSERT 操作，只累加一次。

Com_update：执行UPDATE 操作的次数。

Com_delete：执行DELETE 操作的次数。

2.show profile 分析 SQL,可以查看所有 sql 语句的执行效率(所用时间). 前提是这个命令需要被打开, 严格的说也就是打开这个命令后执行的所有 sql 语句, 它都能记录下执行时间, 并展示出来. 可以通过这个命令分析哪些 sql 语句执行效率低. 耗时长, 就更有针对性的优化这条 sql.

3.开启服务器的慢查询日志

慢 查 询 日 志 记 录 了 所 有 执 行 时 间 超 过 参 数 long_query_time 的 sql 语 句 的 日 志 ,long_query_time 默认为 10 秒(可以通过配置文件设置), 日志保存在 /var/lib/mysql/目录下,有个 slow_query.log 文件,

slow_query_log = 1 #开启慢查询日志

slow_query_log_file = /var/log/mysql/mysql-slow.log #日志文件路径

long_query_time = 3 #超过3s即为慢查询

log_queries_not_using_indexes = 1 #记录未使用索引的查询

 2，explain找到慢sql的原因

通常我们在执行explain之后会发现，大部分sql语句没有命中索引，这时我们需要优化索引；

 3，优化索引

3.1 索引的设计需要遵循一些已有的原则, 这样便于提升索引的使用效率, 更高效的使用索引.

（1）对查询频次较高, 且数据量比较大的表, 建立索引.

（2）索引字段的选择, 最佳候选列应当从 where 子句的条件中提取, 如果 where 子句中的组合

比较多, 那么应当挑选最常用, 过滤效果最好的列的组合.

（3） 使用唯一索引, 区分度越高, 使用索引的效率越高.

（4）索引并非越多越好, 如果该表赠,删,改操作较多, 慎重选择建立索引, 过多索引会降低表维

护效率.

（5）使用短索引, 提高索引访问时的 I/O 效率, 因此也相应提升了 Mysql 查询效率.

（6）如果 where 后有多个条件经常被用到, 建议建立符合 索引, 复合索引需要遵循最左前缀法

则, N 个列组合而成的复合索引, 相当于创建了 N 个索引.

复合索引命名规则 index表名列名 1列名 2列明 3

比如:create index idx_seller_name_sta_addr on tb_seller(name, status, address)

3.2 避免索引失效

  （1）如果在查询的时候, 使用了复合索引, 要遵循最左前缀法则, 也就是查询从索引的最左列开

始, 并且不能跳过索引中的列.

  （2）尽量不要在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

  （3）应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。 4.不做列运算where age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数.计算表达式等, 都会是索引失效.

 （4）查询 like，如果是‘%aaa’ 也会造成索引失效.

 （5）应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有

索引，将导致引擎放弃使用索引而进行全表扫描

4，sql语句优化

1.根据业务场景建立复合索引只查询业务需要的字段，如果这些字段被索引覆盖，将极

大的提高查询效率.

2.多表连接的字段上需要建立索引，这样可以极大提高表连接的效率.

3.where 条件字段上需要建立索引, 但 Where 条件上不要使用运算函数，以免索引失效.

4.排序字段上, 因为排序效率低, 添加索引能提高查询效率.

5.优化 insert 语句: 批量列插入数据要比单个列插入数据效率高.

6.优化 order by 语句: 在使用 order by 语句时, 不要使用 select *, select 后面要查有

索引的列, 如果一条 sql 语句中对多个列进行排序, 在业务允许情况下, 尽量同时用升

序或同时用降序.

7.优化 group by 语句: 在我们对某一个字段进行分组的时候, Mysql 默认就进行了排序,

但是排序并不是我们业务所需的, 额外的排序会降低效率. 所以在用的时候可以禁止

排序, 使用 order by null 禁用

select age, count(*) from emp group by age order by null

8.尽量避免子查询, 可以将子查询优化为 join 多表连接查询.

四， 存储引擎与表结构

1 ，选择存储引擎

一般情况下，我们会选择MySQL默认的存储引擎存储引擎InnoDB，但是当对数据库性能要求精益求精的时候，存储引擎的选择也成为一个关键的影响因素。

建议根据不同的业务选择不同的存储引擎，例如：

查询操作、插入操作多的业务表，推荐使用MyISAM；

临时表使用Memory；

并发数量大、更新多的业务选择使用InnoDB；

不知道选啥直接默认。

2 ，优化字段

字段优化的最终原则是：使用可以正确存储数据的最小的数据类型。

五，其他优化

升级硬件cpu 、内存