Mysql性能优化方法

1. mysql架构

在了解mysql性能优化前，我们需要先了解一下mysql的架构，以便更好理解优化方法。

MySQL逻辑架构整体分为三层 :

1> 客户端（连接层） : 并非MySQL所独有，它们都是服务于C/S程序或者是这些程序所需要的，诸如 : 连接处理、授权认证、安全等功能均在这一层处理
2> 核心服务层 : 是Mysql的核心部分，在MySQL据库系统处理底层数据之前的所有工作都是在这一层完成的，包括查询解析、分析、优化、缓存、内置函数(比如 : 时间、数学、加密等函数)，所有的跨存储引擎的功能也在这一层实现 : 存储过程、触发器、视图等
3> 存储引擎层 : 底层数据存取操作实现部分，由多种存储引擎共同组成。负责 MySQL 中的数据存储和提取，和 Linux 下的文件系统类似，每种存储引擎都有其优势和劣势，中间的服务层通过 API 与存储引擎通信，这些 API接口 屏蔽不同存储引擎间的差异

常用的包括MyISAM（插入查询快，不支持事务）、InnoDB、Memory（临时表）

2. 慢查询日志

2.1 慢查询日志概念

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。

默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

2.2 慢查询开启方式：

2.2.1 单次生效

1、临时开启慢查询日志
set global slow_query_log = on;

如果想关闭慢查询日志，只需要执行 set global slow_query_log = off; 即可

2、临时设置慢查询时间临界点
set long_query_time = 1;

所有执行时间超过1秒的sql都将被记录到慢查询文件中

3、设置慢查询存储的方式
set globle log_output = file;

设置慢查询日志是通过file体现,默认是none,还可以设置为table

2.2.2 永久生效

想要慢查询永久生效则需要修改mysql配置文件。在mysql安装目录下，找到my.ini配置文件，在mysqld下加上如下配置：
log-slow-queries = D:/Mysql/mysql-5.6.27-winx64/slowquery.log
long_query_time = 2

3.查看执行计划

3.1 查看执行计划概念

MySQL的EXPLAIN命令用于SQL语句的查询执行计划(QEP)。输出结果可以让我们了解MySQL 优化器是如何执行SQL 语句的。这条命令不会提供任何建议方案，能够提供重要信息进行调优执行方式。

比如： EXPLAIN SELECT * FROM s1;

3.2 查询结果的列含义

3.2.1 id

表示 SELECT 语句的编号, 如果有子查询，ID的序号会递增（比如id会出现2了），在一组循环中id越大优先级越高，越早被执行

3.2.2 select_type :

表示 SELECT 语句类型。

SIMPLE: 简单查询，其中不包括连接查询和子查询；
PRIMARY:主查询，或者是最外层的查询语句；
UNION: 连接查询的第二个或后面的查询语句

3.2.3 table

显示这一行的数据是关于哪张表的

3.2.4 type (重要) :

表示表的连接类型。从最好到最差分别为：

• system（性能最好） 表示表中只有一条记录，平时基本不会出现；
• const 表示表中有多条记录，但只从表中查询一条记录，比如查询id为1的那行数据，const直奔id为1的那行；
• eq_ref 表示多表连接时，后面的表示用 UNIQUE 或者 PRIMARY KEY；
• ref 表示多表查询时，使用非唯一索引，比如=查询或者前缀索引查询；
• ref or null：表示对普通二级索引进行等值匹配查询，但该索引列的值可以是NULL值
• range 表示查询语句中给出查询范围，比如（< > in like）
• index 索引树扫描 表示没有回表，在索引树上就获取了数据
• index merge 索引合并的方式执行查询
• fulltext 表示全文索引
• unique_subquery 表示子查询中使用 UNIQUE 或者 PRIMARY KEY，子查询返回唯一值
• index_subquery 表示子查询中使用普通索引，子查询可能返回重复值
• ALL（性能最差）全表扫描

注意事项:

1. 除了all之外，其他的type都可以使用到索引
2. 除了index merge之外，其他的type只可以用到一 个索引

3.2.5 possible_keys

表示查询中可能使用的索引

3.2.6 key

表示查询使用到的索引

3.2.7 key_len

表示索引字段的长度

3.2.8 ref

表示使用哪个列或常数与索引一起来查询记录

3.2.9 rows

表示查询的行数

3.3 Extra（重要）

表示查询过程的附加信息

3.3.1 显示信息

• Using index：表示使用了索引覆盖，不需要回表查询，通过索引就可以获取查询的结果数据
• Using where：使用了mysql server中的过滤信息
• Using temporary或Using filesort：表示MYSQL根本不能使用索引，检索会很慢

3.4 索引失效

3.4.1 最左前缀原则：针对组合索引和前缀索引

1. mysql会一直向右匹配直到遇到范围查询(>、 <、 between、 like)就停止匹配，比如a = 1 and b = 2 and c>3 and d=4如果建立(a,b,c, d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a, b, d的顺序可以任意调整。
2. =和in可以乱序，比如a = 1 and b= 2 and c = 3建立(a,b,c)索引可以任意顺序。mysq1的查询优化器会帮你优化成索引可以识别的形式

3.4.2 索引失效条件

索引并不是时时都会生效，发生以下情况都会导致索引失效

1. 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

2. Where条件中跳过了组合索引中某一列。

   ALTER TABLE ‘student’ ADD INDEX index_name(‘name’,’age’,’gender’) ；
   

以上sql语句建立了name、age、gender的联合索引。在where条件中，联合索引时一定要遵从最左前缀原则，一旦跳过某一列，其后的索引将会失效。当然，全值匹配的效率是最高的.

比如：如果查询时没加name，索引会完全失效，没加gender，索引只有部分生效（只有name生效）

3. 在索引上做计算、函数、类型转换操作会导致索引失效转向全表扫描
4. 索引范围条件右边的列会索引失效
   比如 select * from student where name=‘zhangsan’ and age>16 and gender=‘M’；
5. like查询时，百分号%写在最左边将导致索引失效
6. 使用不等（!=、<>），is null 或not null时，会导致索引失效
7. 字符串不加单引号索引失效
   比如： select * from student where name=zhangsan；

4. Show profiles

4.1 Show profiles概念

Show profile 是mysql 提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于sql 调优的测量。 默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

4.2 开启步骤

4.2.1 查看mysql版本是否支持

查看当前的mysql版本是否支持

命令：show variables like ‘profiling%’;（ 默认关闭，使用前需要开启）

4.2.2 开启

命令：set profiling=on;

4.2.3 诊断sql

Show profiles；
可以查看各语句执行时间

show profile for query 问题query_id;
可以查看具体的某一个query_id的执行过程

show profile cpu, block io, memory,swaps,context switches,source for query 6;
可以查看出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等

show profile all for query 6
可以查看第6条语句的所有的执行信息。

4.2.4关闭参数

set profiling=0