hive mysql慢查询_MySQL索引优化分析,SQL优化,慢查询分析

  • Post author:
  • Post category:mysql


1 配置环境的说明 MySQL的版本信息: 系统版本信息: 2 索引的分析 2.1数据准备 2.1.1数据库建表SQL 表的说明: id是自增主键,name是唯一索引,age 是非唯一索引,desc无索引 CREATE TABLE `index_test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT C

1 配置环境的说明

MySQL的版本信息:

3527653eb069e9cd23f2f783e80c5426.png

系统版本信息:

e9a2a3a2b7e573cb8b244d4e2d1b715d.png

2 索引的分析

2.1数据准备

2.1.1数据库建表SQL

表的说明: id是自增主键,name是唯一索引,age 是非唯一索引,desc无索引 CREATE TABLE `index_test` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增ID’,

`name` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT ” COMMENT ‘名字’,

`age` int(11) NOT NULL COMMENT ‘年龄’,

`desc` varchar(128) CHARACTER SET utf8 NOT NULL DEFAULT ” COMMENT ‘描述’,

`status` tinyint(4) NOT NULL COMMENT ‘状态’,

PRIMARY KEY (`id`),

UNIQUE KEY `uniq_name` (`name`),

KEY `idx_age` (`age`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

2.1.2 表中测试数据

bbdb720aae619f55259516cb149c3eba.png

2.2 索引分析

2.2.1 使用explain查看sql的执行计划

在MySQL中可以在sql前面加上explain语句,来显示该条SQL的执行计划,输出内容如下:

c8dbd9629ebe5cc8b61b1fa8d298ed75.png

2.2.2 explain详解

2.2.2.1 select_type

select_type表示查询语句的类型,取值主要有以下几种:

simple:表示是简单的单表查询

primary:表示子查询的外表

derived:派生表的查询

ebd1148c4b250cdb7c6e1460323dbe6b.png

subquery: 子查询的内部第一个SQL

8c168e250e6c7e4502db584efd4d110b.png

union:表示union操作被连接的表

union result:表示连接操作之后的结果表

ab24034bd518cbc5e69211a8b9a4dddf.png

depend union 表示子查询中union语句

depend subquery 表示子查询中生成的结果

5e670fb8ab8d02ae88e07f75d92a9342.png

2.2.2.2 table

当前SQL查询涉及到的表的表名,注意 这里有时候是中间结果表的表名,MySQL会按照自己的规则生成

2.2.2.3 type

type的取值在很大的程度上反应了SQL的执行性能,

按照性能由高到底,type的取值依次为:NULL,system,const,eq_reg,ref,range,index,ALL

NULL 不用查表,速度最快

563465d448ad812f2ad8949008709593.png

system当表中只有一条数据的时候 type为system

const常数查询 一般是根据唯一键或者主键等值查询

5a099d4ded61e492689bac76856b01b6.png

eq_reg 表连接的时候 在b表查询出来的结果在a表这中按照唯一索引值查询一行

033ed010d9342025f4a54092a6c851d1.png

ref非唯一索引查询

78502244dd7b4f050b70516dc960d581.png

range 使用唯一索引返回扫描

c48750b5874a08106aa17bc3e2c6dcb7.png

index 扫描整个索引文件,例如覆盖索引的查询,效率只是比全表查询略快,因为索引文件一般比数据文件小,所以一次读入内存的索引数据更多,这样磁盘IO 就会更少

d2ff4b3283318a1d2e91d053c86b9553.png

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+2

2.2.2.7 ref

表示使用哪个列从表中选择行,取值有科恩个是const

2.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的概要信息

0a80eb6c995f7bbeddb4162a97b1517c.png

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 使用示例:

90ee1434dbe1c2985005f8ce2f80758e.png

结果说明:

在使用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执行起来巨慢。最后分析原因就定位到了这个上面。

ae66dd519ad0fbaa8541af37e41921b5.png

3.2 不支持函数式索引

age字段上面添加了非唯一索引,但是使用了绝对值函数,所以age字段上面的索引就无法使用了。这个在处理日期的时候经常遇到这样的坑

f6a2b249e5710ec5544e8ac382fc9499.png

3.3 索引扫描的代价大于直接全表扫描

如果只有索引过滤的数据比较少,那么会直接走全表扫描,因为使用索引的时候会先扫描一遍索引,然后根据扫描到的索引回表找到所需要的数据,这样扫描的效率其实更低,所以直接走全表扫描

594232d4ed3e8c72ff5ced218775d3b2.png

3.4 使用“%”前缀匹配的时候

name字段添加了唯一索引 但是使用‘%’作为前缀匹配条件,所以不使用索引,直接走全表扫描

5f763c845fdb3c837264abee14513422.png

3.5 复合索引非左前缀匹配

在使用复合索引的时候 如果不是使用的左前缀筛选条件 则不会使用索引,还是会全表扫描

3.5 or筛选添加前后都有索引的时候才会走索引

在使用or作为筛选条件的时候,or的前后筛选条件都必须添加索引 这样才能使用索引 否则 整条sql都无法使用索引

961B62E5-CF31-4D66-8DC8-DB0C0137D35D.png?version=2&modificationDate=1462933049000

f68f2add0b68e4f9810432fce46917b7.png

本文原创发布php中文网,转载请注明出处,感谢您的尊重!



版权声明:本文为weixin_35225264原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。