EXPLAIN SELECT * FROM 表名
rows (估算) =>这个是MYSQL在改优化器的执行方案中扫描的大约数据量与实际的数据有多少
在实际项目中,需要做一个某个统计操作的时候需要的数据不一定正确,这个时候就可以使用explain来获取
select_type:查询类型不是核心
SIMPLE:除了子查询或者union之外的查询显示
partitions:参数的使用,SQL优化本质=》减少=》减少数据扫描的范围
type:类型->当前SQL扫描的类型
all: 全表扫描效率是最低的
index:全索引扫描->就是根据索引去获取结果
const 读常量->主键和唯一字段(主键告诉数据库数据存储的位置)
rang: 索引的范围查询
eq_ref:最多只会有一条结果主要是也是唯一索引
possible key:代表这条SQL语句可能使用到的索引是哪些
key:来决定最终会使用的索引
key len: 索引的大小
extra:这里描述的细节信息
null: 如果key中索引标识那么这里是一个问题"回表"->使用了索引,但是效率不一定很高回表问题在索引去解释
using index覆盖索引->就是直接通过索引获取到了数据(效率是最高)
using where根据条件过,虽然可能使用了索引,但是还是会根据where条件对数据进行过滤
using temporary:意味着产生了一个临时表
using filesort:会对于数据在索引外进行了排序操作
回表:
customers 表 id是主键,a,b字段
SELECT * FROM customers WHERE id=1
这样不会产生回表,因为所有的数据在id的索引树中均能找到
SELECT * FROM customers WHERE a=1;
这样就会产生回表,因为where条件是a字段,那么会去a的索引树里查找数据,但a的索引里面只有a,id两个字段的值,没有b,那么这个查询为了取到b字段,就要取出主键id的值,然后去id的索引树去找b字段的数据。查了两个索引树,这就叫回表。
避免回表,可以建立a和b的联合索引,不用*号,只查询用上的字段
覆盖索引:
覆盖索引就是查这个索引能查到你所需要的所有数据,不需要去另外的数据结构去查。
建立索引
alter table 表名 add index 索引名称 (字段名);
alter table customers add index city_monthsalary(city);
查看表的索引
show indexes from 表名;
show indexes from customers;
删除索引
alter table 表名 drop index 索引名称
alter table salary drop index city_monthsalary
查询女性客户数量和平均月薪
未建索引前所需的时间
select count(*),avg(monthsalary) from customers1 where gender = 0;
[SQL]select count(*),avg(monthsalary) from customers1 where gender = 0;
受影响的行: 0
时间: 12.022s
建立联合索引后,再用explain来看看是否用到联合索引
alter table customers1 add index idex_gender_monthsalary(gender,monthsalary);
explain select count(*),avg(monthsalary) from customers1 where gender = 0;
用上了,再查询一次看看几秒,哇哦,差不多快了12s
建立索引要记住最左匹配原则,就是索引会先匹配最左边的字段,上面的语句gender是最左的,索引建立索引时,gender放在monthsalary前面
___________________________________________________________________
profiling分析
mysql的profiler是一个使用非常方便的查询诊断分析工具,通过该工具可以获取一条查询在 整个执行过程中多种资源的消耗情况,例如内存消耗、I/O消耗和CPU消耗等。
profile的语法规则如下:
show profile [type [, type] ...]
[for query n]
[limit row_count [offset offset]]
其中type参数的可选项含义如下:
all:显示所有的信息
block io:限时输入输出操作阻塞的数量
context switches:显示自动或非自动context switches的数量
cpu:显示系统和用户cpu使用的时间
ipc:显示信息发送和接受的数量
memory:内存的信息
page faults:显示主要的page faults数量
source:显示函数的名称,并且系那是函数所在文件的名字和行数
swaps:显示swap数量
开启profiling
set profiling =1;
show profiling;
在开启了query profiler功能之后,mysql就会自动记录所有执行的query的profile信息。然后执行sql
SELECT * FROM customers1;
通过执行 show profiles 命令获取当前系统中保存的多个query的profile的概要信息。
show PROFILES;
找到刚刚执行的sql,记录下query_id
针对单个query获取详细的profile的信息 在获取概要信息之后就可以根据概要信息中的query_id来获取某一个query在执行过程中的profile信息了
show PROFILE cpu,block io for query 387;