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的版本信息： %20%20%20%20%20%20%20%20%20%20系统版本信息：%20%20%20%20%20%20%20%20%20%20

2%20索引的分析

%20%20%20%20%20%20%20%20%202.1数据准备

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202.1.1数据库建表SQL

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20表的说明：%20id是自增主键，name是唯一索引，age%20是非唯一索引，desc无索引%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20

CREATE%20TABLE%20`index_test`%20(%20%20`id`%20int(11)%20unsigned%20NOT%20NULL%20AUTO_INCREMENT%20COMMENT%20'自增ID',%20%20`name`%20varchar(128)%20COLLATE%20utf8_bin%20NOT%20NULL%20DEFAULT%20''%20COMMENT%20'名字',%20%20`age`%20int(11)%20NOT%20NULL%20COMMENT%20'年龄',%20%20`desc`%20varchar(128)%20CHARACTER%20SET%20utf8%20NOT%20NULL%20DEFAULT%20''%20COMMENT%20'描述',%20%20`status`%20tinyint(4)%20NOT%20NULL%20COMMENT%20'状态',%20%20PRIMARY%20KEY%20(`id`),%20%20UNIQUE%20KEY%20`uniq_name`%20(`name`),%20%20KEY%20`idx_age`%20(`age`))%20ENGINE=InnoDB%20DEFAULT%20CHARSET=utf8%20;

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202.1.2%20表中测试数据

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20

%20%20%20%20%20%20%20%202.2%20索引分析

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202.2.1%20使用explain查看sql的执行计划%20%20%20%20%20

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

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202.2.2%20explain详解%20%20%20

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202.2.2.1%20select_type%20%20%20%20%20

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20select_type表示查询语句的类型，取值主要有以下几种：

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20simple：表示是简单的单表查询

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20primary：表示子查询的外表

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20derived：派生表的查询

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20

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

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20union：表示union操作被连接的表%20%20

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20union%20result：表示连接操作之后的结果表%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20depend%20union%20表示子查询中union语句

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

%20

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202.2.2.2%20table

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

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202.2.2.3%20type

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20type的取值在很大的程度上反应了SQL的执行性能，%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20按照性能由高到底，type的取值依次为：NULL，system，const，eq_reg，ref，range，index，ALL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20NULL%20不用查表，速度最快%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20system当表中只有一条数据的时候%20type为system%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20const常数查询%20一般是根据唯一键或者主键等值查询%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20eq_reg%20表连接的时候%20在b表查询出来的结果在a表这中按照唯一索引值查询一行

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ref非唯一索引查询

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20range%20使用唯一索引返回扫描

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20

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

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20All表示全表扫描，是效率最低的一种查询

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202.2.2.4%20possible%20key

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20表示可能使用的索引，显示的顺序与表连接的顺序无关

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202.2.2.5%20key

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20表示MySQL执行本条sql选的索引的名字，可以通过force%20idex%20和%20ignore%20index%20来强制改变sql执行所需要的索引

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202.2.2.6%20key_len

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20表示该条索引的占用的自己树，是根据索引字段的类型计算出来的，%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20例如%20%20int(11)%20%20索引长度是4%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20varchar(128)并且编码是U8%20%20索引长度的计算方法为%20：%20128*3+2%20

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202.2.2.7%20ref

%20%20%20%20%20%20%20%20%20%20%20本文来源gaodaimacom搞#代%码@网-%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20表示使用哪个列从表中选择行，取值有科恩个是const

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202.2.2.8%20rows

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20表示执行该条SQL必须扫描的行数

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202.2.2.8%20extra

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20包含了MySQL生成执行计划的详细信息：

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20distinct%20查找唯一值，一旦找到就不在继续查找了（暂时没有想好例子）

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20record%20没有找到理想的索引

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20use%20file%20sort%20使用外排来排序%20%20效率比较低

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20use%20index%20使用覆盖索引返回数据，没有扫描表

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20use%20tempoary%20使用临时表来组合返回数据%20效率较低

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20use%20where%20使用where条件过滤返回的数据，在MySQL的存储引擎层没有过滤完数据，只能在MySQL服务层去过滤数据

%20%20%20%20%20%20%20%202.3%20profiling详解

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202.3.1%20开启profiling

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20因为profiling是比较消耗资源的，所以一般的MySQL默认都关闭了profiling功能，并且profiling只是针对当前session有效，目前不支持全局的profiling，可以通过如下的命令查看并开发profiling功能：%20

SELECT%20@@profiling%20%20返回的结果如果是0%20表示当前的session的profiling功能是关闭的set%20profiling=1%20打开当前session的profiling功能

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202.3.2%20profiling的使用

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202.3.2.1%20查询当前session的profiling的概要信息

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20可以使用%20show%20profiles命令获取当前session所执行的sql的概要信息%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202.3.2.2%20profiling详解

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20profiling的语法如下：%20

SHOW%20PROFILE%20[type%20[,%20type]%20...%20]%20%20%20%20[FOR%20QUERY%20n]%20%20%20%20[LIMIT%20row_count%20[OFFSET%20offset]]type:%20%20%20%20ALL%20%20|%20BLOCK%20IO%20%20|%20CONTEXT%20SWITCHES%20%20|%20CPU%20%20|%20IPC%20%20|%20MEMORY%20%20|%20PAGE%20FAULTS%20%20|%20SOURCE%20%20|%20SWAPS

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20使用示例：%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20

结果说明：%20%20%20

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20在使用profiling查看sql的详细执行计划的时候，主要关注的是前两列即：status和duration

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20status%20表示sql的执行状态和%20show%20full%20process%20list%20查看到的状态一致

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20duration%20表示每个状态执行的时间%20可以看到sql的主要执行时间消耗在哪里

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20其次需要关注的是cup，io,swap的详细信息

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20cup表示%20cpu的消耗时间

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20swap表示机器的swap情况

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20io表示io的消耗情况

%20%20%20%20%20%20%20%20

3%20无效索引

%20%20%20%20%20%20%20%20%20%20%20在很多时候MySQL的表建立了索引，并且在查询条件中也使用了索引进行筛选，但是并不一定会使用到索引，例如下面的几种情况

%20%20%20%20%20%20%20%20%203.1筛选条件包含了隐式转换

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20下面的例子中，name字段添加了唯一索引，但是name字段的类型是varchar类型的，而筛选添加时int类型，发生了隐式转换，所以走全表扫描。这里比较隐晦。在上周有一个项目分析酒店订单的时候，本来hive中的酒店订单包含了酒店项目的所有订单，订单id是varchar类型的，而我们需要统计QTA中参加某一个活动的订单，需要查询QTA的订单详情库，（QTA订单详情是hive中订单的子集）里面的订单ID是long类型的，最开始查询的时候就直接在一个表查询完后再另外一个表查询，结果看到一条简单的sql执行起来巨慢。最后分析原因就定位到了这个上面。%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20

%20%20%20%20%20%20%20%20%203.2%20不支持函数式索引

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

%20%20%20%20%20%20%20%20%203.3%20索引扫描的代价大于直接全表扫描

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

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

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

%20%20%20%20%20%20%20%20%203.5%20复合索引非左前缀匹配

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

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

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

%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20

喜欢 (0)赏

[搞代码]

分享 (0)