mysql-性能优化

1：我们说到性能调优，大部分时候，我们想要实现的目标，是查询更快！一个查询的流程，由很多环节组成，每个环节都会消耗时间，

因此，我们要减少查询的时间，需要从没一个环节入手
先确认下自己的环境

SELECT VERSION();
SHOW VARIABLES LIKE ‘%engine%’；

2：链接-配置优化
服务端：
2.1从服务端来说，可以增大链接数
show VARIABLES like ‘max_connections’;-- 修改最大连接数
2.2 及时释放不活动的链接，默认是8小时，我们可以把这个值调小
show GLOBAL VARIABLES like ‘wart_timeout’;-- 及时释放不活动的链接

客户端：
2.3 可以使用链接池，维护一定数量的链接池，其他的客户端排队等待，获取链接就可以了；建议是机器核数*2 +1

3:缓存–架构优化
在系统里面，一些很慢的查询，要么是数据量大，要么是关联的表多，要么是计算逻辑非常复杂，这样的查询，每次会占用很多时间；所以为了减轻数据库的压力，和提升查询效率。我们可以把数据放到内存缓存起来，比如用redis

适用：缓存适用于实时性不是特别高的业务，例如报表数据，一次查询要2分钟，但是一天只更新一次

4.集群，主从复制-减轻单台数据库的压力
mysql的主从复制是怎么实现的呢？是利用binglog；mysql的所有更新语句都会记录到这个binlog文件中；有了这个binlog,从服务器不断解析这个文件，并在从服务器上执行一遍，保持主从服务器的数据一致

5：分库分表
5.1垂直分库-减少并发压力，做法是把一个数据库，安装业务拆分成不同的数据库

水平分库：把单张表的数据，按照一定规则分布到多个数据库

6：解析器
词法语法分析，主要保证语句的正确性，语句不出错就没问题

7：优化器-sql语句分析和优化
优化器的作用就是对我们的sql语句进行优化，并生成执行计划
问题：我们在做项目的时候，有时候会受到dba的邮件，里面列出几个耗时比较长的查询语句，让我们去优化，这些语句是从哪里来的呢？这需要用到慢查询日志
7.1 打开慢查询日志开关
show VARIABLES like ‘slow_query’;-- 慢查询日志，默认是关闭的
7.2 怎么分析慢查询日志 mysqldumpslow,在mysql的bin目录下

8：explain执行计划

select type 查询类型
8.1 simple简单查询

8.2子查询

type 链接类型

const 主键索引或者唯一索引

ref 查询用到了非唯一索引，或者关联操作只用到了最左前缀

range 索引范围扫描
如果where后面是between and 或者< 或者> >= <= in 这些

index 查询全部索引中的数据，比不走索引要快

all 代表没有用到索引或者全表扫描

总结：一般all或者index都需要优化

9 sql和索引优化
当我们的sql语句比较复杂，有多个关联和子查询的时候，就要分析sql语句有没有改写的方法。举个简单的例子

10：存储引擎
10.1引擎的选择 查询插入多的用myisam,临时表用memeroy,事务用innodb;
10.2 分表或者分区
交易历史表：每个月一个分区
10.3 字段定义：使用可以正确存储数据的最小类型
整数类型有6种

字符类型
变长情况用varchar
固定情况用char

大文件存储，存储到专门的服务器，不要放到数据库里面

总结：优化体系
如果面试的时候，你再遇到类似的问题，你怎么回答？

优化案例：
服务端状态分析：
如果出现连接变慢，查询被阻塞，无法获取链接的情况
一
1：重启
2：show PROCESSLIST;查询线程转态，链接数量，链接时间和转态

3：查询锁的转态
show open tables;
4：kill掉有问题的线程

二：
1分析查询基本情况，表的结构，字段，每张表的数据量，查询的业务含义
2 找出慢的原因
查看执行计划，分析sql执行情况，了解查询类型，索引类型
3：如果总体的时间很长，不确定是哪一个因素，通过条件的删减，顺序的调整，不断的尝试
三：找到原因之后，对症下药
1：创建索引
2：改写sql，这里需要平时不断积累
2.1 小表驱动大表
2.2用join代替子查询
2.3大偏移量，先过滤再排序
2.4 or 改成union
2.5 如果表允许重复的话，用union all代替union
如果sql本身解决不了，就要上升到表结构和架构了
3表结构 冗余 拆分 not null
4 架构优化
读写分离，分库分表