首页 / MYSQL / MySQL索引优化分析和SQL优化
MySQL索引优化分析和SQL优化
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL索引优化分析和SQL优化,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5615字,纯文字阅读大概需要9分钟。
内容图文
![MySQL索引优化分析和SQL优化](/upload/InfoBanner/zyjiaocheng/464/87ec4754fbc0443b9bbc4a54e70d76c2.jpg)
2.2.2.3 type
type的取值在很大的程度上反应了SQL的执行性能, 按照性能由高到底,type的取值依次为:NULL,system,const,eq_reg,ref,range,index,ALL NULL 不用查表,速度最快![MySQL索引优化分析和SQL优化 - 文章图片](/upload/getfiles/0001/2021/4/24/20210424025058958.jpg)
![MySQL索引优化分析和SQL优化 - 文章图片](/upload/getfiles/0001/2021/4/24/20210424025059371.jpg)
ref非唯一索引查询
range 使用唯一索引返回扫描
index 扫描整个索引文件,例如覆盖索引的查询,效率只是比全表查询略快,因为索引文件一般比数据文件小,所以一次读入内存的索引数据更多,这样磁盘IO 就会更少
All表示全表扫描,是效率最低的一种查询
2.2.2.4 possible key
表示可能使用的索引,显示的顺序与表连接的顺序无关2.2.2.5 key
表示MySQL执行本条sql选的索引的名字,可以通过force idex 和 ignore index 来强制改变sql执行所需要的索引2.2.2.6 key_len
表示该条索引的占用的自己树,是根据索引字段的类型计算出来的, 例如 int(11) 索引长度是4 varchar(128)并且编码是U8 索引长度的计算方法为 : 128*3+22.2.2.7 ref
表示使用哪个列从表中选择行,取值有科恩个是const2.2.2.8 rows
表示执行该条SQL必须扫描的行数2.2.2.8 extra
包含了MySQL生成执行计划的详细信息:distinct 查找唯一值,一旦找到就不在继续查找了(暂时没有想好例子)
record 没有找到理想的索引
use file sort 使用外排来排序 效率比较低
use index 使用覆盖索引返回数据,没有扫描表
use tempoary 使用临时表来组合返回数据 效率较低
use where 使用where条件过滤返回的数据,在MySQL的存储引擎层没有过滤完数据,只能在MySQL服务层去过滤数据
2.3 profiling详解
2.3.1 开启profiling
因为profiling是比较消耗资源的,所以一般的MySQL默认都关闭了profiling功能,并且profiling只是针对当前session有效,目前不支持全局的profiling,可以通过如下的命令查看并开发profiling功能:SELECT @@profiling 返回的结果如果是0 表示当前的session的profiling功能是关闭的 set profiling=1 打开当前session的profiling功能
2.3.2 profiling的使用
2.3.2.1 查询当前session的profiling的概要信息
可以使用 show profiles命令获取当前session所执行的sql的概要信息![MySQL索引优化分析和SQL优化 - 文章图片](/upload/getfiles/0001/2021/4/24/20210424025100592.jpg)
2.3.2.2 profiling详解
profiling的语法如下:SHOW PROFILE [type [, type] ... ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]] type: ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS使用示例:
![MySQL索引优化分析和SQL优化 - 文章图片](/upload/getfiles/0001/2021/4/24/20210424025100887.jpg)
结果说明:
在使用profiling查看sql的详细执行计划的时候,主要关注的是前两列即:status和duration
status 表示sql的执行状态和 show full process list 查看到的状态一致
duration 表示每个状态执行的时间 可以看到sql的主要执行时间消耗在哪里
其次需要关注的是cup,io,swap的详细信息
cup表示 cpu的消耗时间
swap表示机器的swap情况
io表示io的消耗情况
3 无效索引
在很多时候MySQL的表建立了索引,并且在查询条件中也使用了索引进行筛选,但是并不一定会使用到索引,例如下面的几种情况3.1筛选条件包含了隐式转换
下面的例子中,name字段添加了唯一索引,但是name字段的类型是varchar类型的,而筛选添加时int类型,发生了隐式转换,所以走全表扫描。这里比较隐晦。在上周有一个项目分析酒店订单的时候,本来hive中的酒店订单包含了酒店项目的所有订单,订单id是varchar类型的,而我们需要统计QTA中参加某一个活动的订单,需要查询QTA的订单详情库,(QTA订单详情是hive中订单的子集)里面的订单ID是long类型的,最开始查询的时候就直接在一个表查询完后再另外一个表查询,结果看到一条简单的sql执行起来巨慢。最后分析原因就定位到了这个上面。![MySQL索引优化分析和SQL优化 - 文章图片](/upload/getfiles/0001/2021/4/24/20210424025101397.jpg)
3.2 不支持函数式索引
age字段上面添加了非唯一索引,但是使用了绝对值函数,所以age字段上面的索引就无法使用了。这个在处理日期的时候经常遇到这样的坑![MySQL索引优化分析和SQL优化 - 文章图片](/upload/getfiles/0001/2021/4/24/20210424025101614.jpg)
3.3 索引扫描的代价大于直接全表扫描
如果只有索引过滤的数据比较少,那么会直接走全表扫描,因为使用索引的时候会先扫描一遍索引,然后根据扫描到的索引回表找到所需要的数据,这样扫描的效率其实更低,所以直接走全表扫描![MySQL索引优化分析和SQL优化 - 文章图片](/upload/getfiles/0001/2021/4/24/20210424025101778.jpg)
3.4 使用“%”前缀匹配的时候
name字段添加了唯一索引 但是使用‘%’作为前缀匹配条件,所以不使用索引,直接走全表扫描![MySQL索引优化分析和SQL优化 - 文章图片](/upload/getfiles/0001/2021/4/24/20210424025102130.jpg)
3.5 复合索引非左前缀匹配
在使用复合索引的时候 如果不是使用的左前缀筛选条件 则不会使用索引,还是会全表扫描3.5 or筛选添加前后都有索引的时候才会走索引
在使用or作为筛选条件的时候,or的前后筛选条件都必须添加索引 这样才能使用索引 否则 整条sql都无法使用索引
MySQL索引优化分析和SQL优化
标签:
本文系统来源:http://blog.csdn.net/gcl272633743/article/details/51381765
内容总结
以上是互联网集市为您收集整理的MySQL索引优化分析和SQL优化全部内容,希望文章能够帮你解决MySQL索引优化分析和SQL优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。