MySQL从入门到高级 --- 15.优化 && 16.pymysql

第十五章 && 第十六章：

15.优化

优化方式：

• 从设计上

• 从查询上

• 从索引上

• 从存储上

15.1 查询SQL执行效率

语法：show [session | global |] status

• 查看服务器状态信息

15.2 定位低效率执行SQL

两种方式：

• 慢查询日志：定位执行效率较低的SQL语句

• show processlist：该指令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可实时查看SQL执行情况，同时对一些锁表操作进行优化

15.2.1 show processlist

1. id：用户登录mysqld时，系统分配的"connection_id"，可用函数connection_id()查询

2. user：显示当前用户。若不是root，该命令只显示用户权限范围的SQL语句

3. host：显示语句从哪个ip端口上发的，可用来跟踪出现问题语句的用户

4. db：显示进程目前连接的数据库

5. command：显示当前连接执行的命令，一般取值为休眠(sleep),查询(query)，连接(connect)等

6. time：显示状态持续的时间，单位是秒

7. state：显示使用当前连接的SQL语句状态。描述的是语句执行中的某一个状态

8. info：显示SQl语句，判断问题语句的重要依据

15.3 explain分析执行计划 - 基本使用

• id：序列号，查询中执行select子句或者是操作表的顺序

• select_type：SELECT的类型

• table：输出结果集的表

• type：表的连接类型

• possible_keys：查询时，可能使用的索引

• key：实际使用的索引

• key_len：索引字段的长度

• rows：扫描行的数量

• extra：执行情况的说明和描述

15.4 explain分析执行计划 - id

• 相同id表示加载表的顺序是从上到下

• 不同id值越大，优先级越高，越先被执行

• id有相同，也有不同，同时存在。id相同可认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先被执行

15.5 explain分析执行计划 - select_type

• 表示SELECT类型

15.6 explain分析执行计划 - type

• 显示访问类型

结果值从最好到最坏：system->const->eq_ref->ref->range->index->all

15.7 explain分析执行计划 -其他指标字段

• table：显示所访问数据库中表名称有时不是真实的表名字，可能是简称

• rows：扫描行的数量

• possible_keys：显示可能应用在该表的索引，一个或多个

• key：实际使用的索引，若为NULL，则没有使用索引

• key_len：索引中使用的字节数，该值为索引字段最大可能长度，不是实际使用长度，在不损失精度的情况下，长度越短越好

• extra：

15.8 show profile分析SQL

通过have_profiling参数，可看到当前MySQL是否支持profile

select @@have_profiling;
set profiling=1 -- 开启profiling开关

show profile for query query_id 可查看到该SQL执行过程中每个线程的状态和消耗时间

15.9 trace分析优化器执行计划

set optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

• 打开trace，设置格式为json，并设置trace最大能用的内存大小，避免解析过程中默认内存太小而不能完整展示

通过information_schema.optimizer_trace可知道MySQL如何执行SQL

-- 执行SQL
select * from emp;
select * from information_schema.optimizer_trace \G;

15.10 使用索引优化

15.10.1 数据

create table stu(
    id int,
    name varchar(20),
    age int,
    sex varchar(20),
    address varchar(20),
    primary key(id)
);

insert into stu values(1,'xiaoming',19,'男','北京');
insert into stu values(2,'xiaowang',25,'女','深圳');
insert into stu v