Mysql性能优化

目录

查看数据库中各操作类型的统计信息

定位低效率执行SQL:慢查询日志

定位低效率执行SQL: show processlist

低效率执行SQL定位原因：EXPLAIN 执行计划

profile查看sql执行情况

---

查看数据库中各操作类型的统计信息

查看哪些类型的操作多

#查询当前session会话期间的操作类型的统计信息
show session status like 'Com_______';

#查询当全局（从mysql服务启动到当前）的操作类型的统计信息
show GLOBAL status like 'Com_______';

#查看Innodb存储引擎的操作类型的统计信息
show status like 'Innodb_rows_%';

定位低效率执行SQL:慢查询日志

# 查看慢日志配置信息
show VARIABLES like '%slow_query_log%';

# 开启慢查询日志
set global slow_query_log=1;

# 查看慢查询日志记录SQL的阈值时间：默认是10秒，执行时间大于阈值时间才会被记录到慢查询日志中
show VARIABLES like '%long_query_time%';

# sql强行休眠15秒钟，查看慢日志是否记录
select sleep(15);

# 修改慢查询日志记录sql的阈值时间
set global long_query_time=1;

接下来就可以去慢查询日志中 ********-slow.log 中查看已经执行的慢日志了

定位低效率执行SQL: show processlist

show processlist显示用户正在运行的线程，需要注意的是，除了root用户能看到所有正在运行的线程外，
其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程。除非单独个这个用户赋予了PROCESS权限。

show processlist;

低效率执行SQL定位原因：EXPLAIN 执行计划

EXPLAIN 
    SELECT
        A.SNO,
        A.SNAME,
        A.SSEX,
        A.SBIRTHDAY,
        A.SCLASS,
        B.CNO,
        C.CNAME,
        C.TNO,
        D.TNAME,
        D.Tsex,
        D.Tbirthday,
        D.Tprof,
        D.Depart,
        B.DEGREE,
        ROW_NUMBER() OVER(PARTITION BY SNO ORDER BY DEGREE DESC) AS RANG
    FROM
        STUDENT A
    INNER JOIN SCORE B ON A.SNO = B.SNO
    INNER JOIN course C ON C.CNO = B.CNO
    INNER JOIN teacher D ON C.TNO = D.TNO

---

---

Explain 各个字段及其含义
id : 表示 SQL 执行的顺序的标识， SQL 从大到小的执行
select_type：表示查询中每个 select 子句的类型
table：显示这一行的数据是关于哪张表的，有时不是真实的表名字
type：表示 MySql 在表中找到所需行的方式，又称访问类型。常用的类型有：ALL, index, range, ref,eq_ref, const, system, NULL（从左到右，性能从差到好）
possible_keys：指出 MySql 能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
Key：key 列显示 MySql 实际决定使用的键（索引），如果没有选择索引，键是 NULL
key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的）
ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
rows：表示 MySql 根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，理论上行数越少，查询性能越好
Extra：该列包含 MySql 解决查询的详细信息
在 Explain 执行计划中，我们重点关注 Type 和 Extra 两个字段，它们是核心

Explain 中的 Type
Explain 中的 Type 在 MySql 的官网解释为：连接类型（the join type）。它描述了找到所需数据使用的扫描方式。最为常见的扫描方式有

system：说明数据已经加载到内存里，不需要进行磁盘 IO，这类扫描是速度最快的
const：命中主键索引或者唯一索引，被连接的部分是一个常量值
eq_ref：主键索引或者非空唯一索引等值扫描
ref：非主键索引或非唯一索引等值扫描
range：范围扫描，它是索引上的范围查询，它会在索引上扫描特定范围内的值
index：索引树扫描，需要扫描索引树上的全部数据
ALL：全表扫描
上面各类扫描方式由快到慢：system > const > eq_ref > ref > range > index > ALL

Explain 中的 Extra
Explain 中的 Extra 的值有

Using index：说明 SQL 所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录
Using where：说明 SQL 使用了 where 条件过滤数据
Using index condition：说明确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录
Using filesort：说明得到所需结果集，需要对所有记录进行文件排序。典型的，在一个没有建立索引的列上进行了 order by，就会触发 filesort，常见的优化方案是，在 order by 的列上添加索引，避免每次查询都全量排序。详情可以参考：MySql 中 order by 的原理
Using temporary：说明需要建立临时表来暂存中间结果。这类 SQL 语句性能较低，往往也需要进行优化。典型的 group by 和 order by 同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集

profile查看sql执行情况

# 查看当前数据库是否支持profile;
select @@have_profiling;

# 设置开启profile;
set profiling = 1;

# 查看所有的profile执行情况
show PROFILES;

# 查询某个查询id的sql执行情况；
show PROFILE for QUERY 204;

# 查询某个查询id的sql执行时CPU情况；
show PROFILE cpu for QUERY 218;

# 查询某个查询id的sql执行时CPU情况；
show PROFILE cpu for QUERY 218;

TRACE 优化器执行任务

# 打开trace,设置格式为 json
set optimizer_trace = 'enabled=on',end_markers_in_json = on; 
#设置 trace 可用内存的大小
set optimizer_trace_max_mem_size = 1000000;
# 执行sql
SELECT
    A.SNO,
    A.SNAME,
    A.SSEX,
    A.SBIRTHDAY,
    A.SCLASS,
    B.CNO,
    C.CNAME,
    C.TNO,
    D.TNAME,
    D.Tsex,
    D.Tbirthday,
    D.Tprof,
    D.Depart,
    B.DEGREE,
    ROW_NUMBER() OVER(PARTITION BY SNO ORDER BY DEGREE DESC) AS RANG
FROM
    STUDENT A
INNER JOIN SCORE B ON A.SNO = B.SNO
INNER JOIN course C ON C.CNO = B.CNO
INNER JOIN teacher D ON C.TNO = D.TNO

# 查看 trace 优化器执行任务:navicat无法执行该语句，需要到CMR客户端执行
select * from information_schema.OPTIMIZER_TRACE\G;