【知识库】-数据库_MySQL性能分析之Query Optimizer
2023-09-14 08:57:04 时间
简书作者:Sio
文章出处: MySql优化之索引原理与 SQL 优化
Query Optimizer
MySQL Optimizer是一个专门负责优化SELECT 语句的优化器模块,它主要的功能就是通过计算分析系统中收 集的各种统计信息,为客户端请求的Query 给出他认为最优的执行计划,也就是他认为最优的数据检索方式。
MySQL常见瓶颈
- CPU饱和:CPU饱和的时候,一般发生在数据装入内存或从磁盘上读取数据的时候
- IO瓶颈: 磁盘IO瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件的性能瓶颈
执行计划Explai
Explain概述
使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MYSQL是如何处理SQL语句的.我们可以用执行 计划来分析查询语句或者表结构的性能瓶颈
Explain作用
- 查看表的读取顺序
- 查看数据库读取操作的操作类型
- 查看哪些索引有可能被用到
- 查看哪些索引真正被用到
- 查看表之间的引用
- 查看表中有多少行记录被优化器查询
4.3.3语法
- 语法
explain sql语句
各字段解释
- 准备工作
create table t1( id int primary key, name varchar(20), col1 varchar(20), col2 varchar(20), col3 varchar(20) ); create table t2( id int primary key, name varchar(20), col1 varchar(20), col2 varchar(20), col3 varchar(20) ); create table t3( id int primary key, name varchar(20), col1 varchar(20), col2 varchar(20), col3 varchar(20) ); insert into t1 values(1,'zs1','col1','col2','col3'); insert into t2 values(1,'zs2','col2','col2','col3'); insert into t3 values(1,'zs3','col3','col2','col3'); create index ind_t1_c1 on t1(col1); create index ind_t2_c1 on t2(col1); create index ind_t3_c1 on t3(col1); create index ind_t1_c12 on t1(col1,col2); create index ind_t2_c12 on t2(col1,col2); create index ind_t3_c12 on t3(col1,col2);
执行explain sql语句后
id
select 查询的序列号,包含一组数字,表示查询中执行Select子句或操作表的顺序
三种情况:
一、id值相同,执行顺序由上而下
explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id= t3.id and t1.name = 'zs';
二、id值不同,id值越大优先级越高,越先被执行
explain select t2.* from t2 where id = (select id from t1 where id = (select t3.id from t3 where t3.name='zs3'));
三、id值有相同的也有不同的,如果id相同,从上往下执行,id值越大,优先级越高,越先执行
select_type
查询类型,主要用于区别
- SIMPLE : 简单的select查询,查询中不包含子查询或者UNION
- PRIMARY: 查询中若包含复杂的子查询,最外层的查询则标记为PRIMARY
- SUBQUERY : 在SELECT或者WHERE列表中包含子查询
- DERIVED : 在from列表中包含子查询被标记为DRIVED衍生,MYSQL会递归执行这些子查询,把结果放到临时表 中
- UNION: 若第二个SELECT出现在union之后,则被标记为UNION, 若union包含在from子句的子查询中,外层 select被标记为:derived
- UNION RESULT: 从union表获取结果的select
table
显示这一行的数据是和哪张表相关
type
访问类型: all, index,range,ref,eq_ref, const,system,null
最好到最差依次是: system > const > eq_ref>ref >range > index > all , 最好能优化到range级别或则ref级别
最好到最差依次是: system > const > eq_ref>ref >range > index > all , 最好能优化到range级别或则ref级别
- system: 表中只有一行记录(系统表), 这是const类型的特例, 基本上不会出现
- const: 通过索引一次查询就找到了,const用于比较primary key或者unique索引,因为只匹配一行数据,所以很 快,如将主键置于where列表中,mysql就会将该查询转换为一个常量
explain select * from (select * from t1 where id=1) s1;
- eq_ref: 唯一性索引扫描, 对于每个索引键,表中只有一条记录与之匹配, 常见于主键或者唯一索引扫描
explain select * from t1,t2 where t1.id = t2.id;
- ref : 非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有符合条件的行,然而 它可能返回多个符合条件的行
explain select * from t1 where col1='zs1';
- range : 只检索给定范围的行, 使用一个索引来选择行.key列显示的是真正使用了哪个索引,一般就是在where条 件中使用between,>,<,in 等范围的条件,这种在索引范围内的扫描比全表扫描要好,因为它只在某个范围中扫描, 不需要扫描全部的索引
explain select * from t1 where id between 1 and 10;
- index : 扫描整个索引表, index 和all的区别为index类型只遍历索引树. 这通常比all快,因为索引文件通常比数据 文件小,虽然index和all都是读全表,但是index是从索引中读取,而all是从硬盘中读取数据
explain select id from t1;
- all : full table scan全表扫描 ,将遍历全表以找到匹配的行
explain select * from t1;
- 注意: 开发中,我们得保证查询至少达到range级别,最好能达到ref. 如果百万条数据出现all, 一般情况下就需要考虑使用索引优化了
possible_keys
SQL查询中可能用到的索引,但查询的过程中不一定真正使用
key
查询过程中真正使用的索引,如果为null,则表示没有使用索引查询中使用了覆盖索引,则该索引仅出现在key列表中
explain select t2.* from t1,t2,t3 where t1.col1 = ' ' and t1.id = t2.id and t1.id= t3.id;
explain select col1 from t1;
key_len
索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确度的情况下,长度越短越好, key_len显 示的值为索引字段的最大可能长度,并非实际使用长度, 即key_len是根据表定义计算而得
explain select * from t1 where col1='c1'
explain select * from t1 where col1='col1' and col2 = 'col2'; ‐‐ 注意: 为了演示这个结果,我们删除了c1上面的索引 alter table t1 drop index ind_t1_c1; ‐‐ 执行完成之后,再次创建索引 create index ind_t1_c1 on t1(col1);
ref
显示索引的哪一列被使用了,如果可能的话,是一个常数.哪些列或者常量被用于查找索引列上的值
rows
根据表统计信息及索引选用的情况,估算找出所需记录要读取的行数 (有多少行记录被优化器读取) ,越少越好extra
包含其它一些非常重要的额外信息
- Using filesort : 说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,Mysql中无 法利用索引完成的排序操作称为文件排序
explain select col1 from t1 where col1='col1' order by col3;
‐‐ 上面这条SQL语句出现了using filesort,但是我们去执行下面这条SQL语句的时候它,又不会出现using filesort explain select col1 from t1 where col1='col1' order by col2;
‐‐ 如何优化第一条SQL语句 ? create index ind_t1_c13 on t1(col1,col3); explain select col1 from t1 where col1='col1' order by col3;
- Using temporary : 使用了临时表保存中间结果,Mysql在对查询结果排序时使用了临时表,常见于order by 和分 组查询group by
explain select col1 from t1 where col1>'col1' group by col2;
explain select col1 from t1 where col1 >'col1' group by col1,col2;
- Using index :
- 查询操作中使用了覆盖索引(查询的列和索引列一致),避免访问了表的数据行,效率好
- 如果同时出现了using where, 表明索引被用来执行索引键值的查找
- 如果没有同时出现using where, 表明索引用来读取数据而非执行查找动作
- 覆盖索引: 查询的列和索引列一致, 换句话说查询的列要被所键的索引覆盖,就是select中数据列只需从索引中就 能读取,不必读取原来的数据行,MySql可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件
explain select col2 from t1 where col1='col1';
explain select col2 from t1;
- using where : 表明使用了where条件过滤
- using join buffer : 表明使用了连接缓存, join次数太多了可能会出现
- impossible where : where子句中的值总是false,不能用来获取任何数据
explain select * from t1 where col1='zs' and col1='ls';
- select tables optimized away :
- 在没有group by 子句的情况下, 基于索引优化min/max操作或者对于MyISAM存储引擎优化count(*)操作,不必 等到执行阶段再进行计算,查询执行计划生成阶段即完成优化
- distinct : 优化distinct操作,在找到第一个匹配的数据后即停止查找同样的值的动作
相关文章
- 解决MySql Error Code: 2006 – MySQL 服务器已离线 错误详解数据库
- 语句MySQL优化:精确掌握SQL语句(mysql优化sql)
- 数据PHP编程:从MySQL数据库中取出数据(php取mysql)
- MySQL操作:大于某个时间的数据查询(mysql时间大于)
- MySQL数据库中添加新字段的方法(mysql添加字段)
- MySQL语句入门:让您轻松玩转数据库(mysql语句入门)
- MySQL数据导出:漏洞大开(mysql导出数据不全)
- MySQL数据库实时监控解决方案(mysql数据库监控工具)
- MySQL数据库:使用可视化技术提高数据处理效率(mysql数据库可视化)
- 通过MySQL二级考试,一步步拥有更好的职业发展(mysql二级考试)
- MySQL如何修改数据库字符集?25字(mysql修改库的字符集)
- MySQL创建临时表的方法及注意事项(mysql创建临时表)
- 深入了解MySQL台服:高效稳定的数据库解决方案(mysql台服)
- 灵活使用MySQL分组条件,轻松处理大量数据(mysql分组条件)
- 数据库阿里云:打造高性能分布式MySQL数据库(阿里云分布式mysql)
- MySQL技能如何成为你简历中的闪亮亮点?(mysql简历)
- MySQL中GUID类型的应用及其有效性(mysql guid类型)
- 如何完整卸载MySQL数据库:详细教程(mysql 卸载教程)
- MySQL全面介绍:使用方法、性能调优和常见问题(mysql大全)
- MySQL同步部分表的操作方法(mysql 同步 部分表)
- 调试困难C语言无法连接MySQL数据库(c mysql 连不上)
- 深入浅出MySQL数据库备份与恢复(c mysql数据备份表)
- CMD下MySQL账号密码安全管理(cmd网络mysql密码)
- JS将文件传输至MySQL数据库(.js传文件至mysql)
- MySQL实现三个表的外连接(mysql 三表外连接)
- MySQL如何使用显示命令查看表结构(mysql下显示命令)
- MySQL更新操作中的条件问题(mysql不能更新条件)
- MySQL上下行连接的使用方法和优缺点(mysql 上下行连接)
- MySQL数据库不支持中文变量解决方法浅析(mysql不支持中文变量)