我们在日常开发中可以对数据库进行性能分析,进而优化我们的SQL语句。我们可以通过以下几种方法进行性能的分析。
一、SQL语句执行频率
1.查看当前数据库的状态
语法:
SHOW GLOBAL STATUS;
或者可以通过下面的命令模糊查询当前数据库的插入、查询、修改、删除的访问频率。
语法:
show global status like 'Com_______';
我们可以通过查询当前数据库的插入、查询、修改、删除的访问频率，针对数据库进行优化。如果是查询的次数占大多数，那么我们就应该加上索引等方式优化查询的效率。如果是插入、修改比较多就可以使用其他方式优化效率。
二、慢查询日志
慢查询日志记录了执行时间超过指定参数long_query_time(默认10秒)的所有SQL语句的日志。MySQL的慢查询日志默认是没有开启的，我们需要手动开启，设置slow-query-log=1。
语法:
SHOW VARIABLES LIKE 'slow_query_log'
如果值是ON说明我们开启了慢查询日志。 如果没有开启，我们需要到MySQL的配置文件etc下的cnf配置文件配置如下信息: 我们分别设置slow-query-log和 long_query_time。
设置好之后，我们重启MySQL服务。
我们执行一个查询时间超过1秒的SQL语句。
最后我们在MySQL的 Data目录下查询慢查询日志记录。
打开这个文件发现了我们刚才慢查询日志的相关日志记录。
后面我们就可以通过这个文件，定位到一些操作比较耗时的SQL语句，然后对这些SQL语句进行优化。
三、profile详情查看耗时
show profiles可以帮助我们查看任意时间耗时的SQL语句执行情况。在使用之前我们需要通过have_profiling参数查看当前MySQL是否支持profile操作。
语法:
SELECT @@have_profiling;
YES代表已经开启了profile，No代表没有开启profile。如果没有开启profile，我们可以设置set profiling=1 开启profile。
查看每一条SQL的耗时基本情况
语法:
SHOW PROFILES;
可以查询最近每一条查询语句的耗时情况，针对耗时情况进行对应的SQL优化。
四、explain执行计划
我们可以通过EXPLAIN或者DESC命令获取MySQL任何执行查询语句的信息，包括执行过程中表如何连接和连接的顺序等。
语法:
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件...;SQL语句:
explain select result from t_protocol_log WHERE id > 1000;
Explain执行计划返回的各个字段含义如下图所示:
字段 | 含义 |
id | select查询的序列号，表示查询中执行select子句或者是操作表的顺序。 |
select_type | 表示SELECT的类型，常见的取值有SIMPLE(简单表查询),PRIMARY(主查询),UNION(联合查询),SUBQUERY(子查询) |
type | 表示连接类型，性能由好到坏的连接类型为NULL、system、const、eq_ref、ref、range、index、all. |
possible_key | 在这张表上可能会使用到的索引，一个或多个 |
key | 实际使用的索引 |
key_len | 表示索引中使用的字节数，该值为索引字段最大可能长度。长度越短越好 |
rows | MySQL认为必须要执行查询的行数 |
filtered | 表示返回结果的行数占需读取行数的百分百,值越大越好 |
对于type 字段值补充说明：
- NULL：一般不太可能优化到NULL,除非在查询的时候不访问任何表，比如Select 'A'
- system ：一般出现在访问系统表时
- const ：一般出现在使用主键或者唯一索引访问时
- ref ：一般出现在使用非唯一性索引访问时
- range ：一般出现在使用了非唯一索引, 但是范围匹配, 比如age > 18
