前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL查询索引分析

MySQL查询索引分析

原创
作者头像
kevindang
修改2018-06-17 17:09:32
2.2K0
修改2018-06-17 17:09:32
举报
文章被收录于专栏:存储技术

场景:

前一段时间修改数据表时,给一个表添加一个datetime字段,当时遇到了一个问题:我是否需要给该datetime字段上加索引呢?如果不给该字段加索引,当where语句中使用该字段时,会不会扫全表呢?如果给其加了索引,那么势必会带来一些开销,假如这个索引用不到的话,给其加了索引岂不是画蛇添足了呢?

为了弄清楚上述问题的原因,以及当where条件很多时,Mysql如何选择索引进行查找,查阅了Mysql官方文档第8章optimization的相关内容。

首先,想要知道你的一条复杂的SQL语句到底是如何执行的,第一步我们可以执行

代码语言:txt
复制
show index from table_name

来查看你的表中都有哪些索引,例如:

代码语言:txt
复制
+------------+------------+-------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
代码语言:txt
复制
| Table      | Non_unique | Key_name          | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
代码语言:txt
复制
+------------+------------+-------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
代码语言:txt
复制
| acct_order |          0 | PRIMARY           |            1 | id             | A         |        2287 |     NULL | NULL   |      | BTREE      |         |
代码语言:txt
复制
| acct_order |          1 | game_id           |            1 | game_id        | A         |          51 |     NULL | NULL   |      | BTREE      |         |
代码语言:txt
复制
| acct_order |          1 | user_id           |            1 | user_id        | A         |         114 |     NULL | NULL   | YES  | BTREE      |         |
代码语言:txt
复制
| acct_order |          1 | game_work_id      |            1 | game_work_id   | A         |          78 |     NULL | NULL   |      | BTREE      |         |
代码语言:txt
复制
| acct_order |          1 | weixin_user_id    |            1 | weixin_user_id | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |
代码语言:txt
复制
+------------+------------+-------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+

字段很多,其中比较重要的几个:

  • Non_unique:表明是否是唯一键
  • Key_name:索引的名字
  • Column_name:索引包含的column名字
  • Cardinality:大概意思就是此列中存储唯一值的个数,该值是Mysql优化器是否选择使用该索引的重要判断依据,但是改指并不准确,不会自动跟新,我们可以通过analyze table等语句来更新表
  • Index_type:表明该索引的类型,一般为BTREE

索引那么多,Mysql使用索引的目的是什么呢?

  • 从where条件中快速定位到我们要找的行
  • 从条件中消除行,如果有多个index可供选择,mysql通常会使用那些能够找出最少行数的索引
  • 为了找出join表的行数据
  • 在某些索引查询中已经包含所需的数据时,不需要再读取完整的记录(Mysql一般会先从索引文件中读取要找的记录,然后根据索引再从数据表中读取真正的记录)
  • 其他

在了解了自己表结构以及索引结构之后,通常可以使用explain语句来查看Mysql的查询执行计划,例如下所示:

代码语言:txt
复制
mysql> explain select * from acct_order where user_id=39 and weixin_user_id=61 and add_time > "2014-10-01";G;
代码语言:txt
复制
*************************** 1. row ***************************
代码语言:txt
复制
           id: 1
代码语言:txt
复制
  select_type: SIMPLE
代码语言:txt
复制
        table: acct_order
代码语言:txt
复制
         type: index_merge
代码语言:txt
复制
possible_keys: user_id,weixin_user_id
代码语言:txt
复制
          key: weixin_user_id,user_id
代码语言:txt
复制
      key_len: 5,5
代码语言:txt
复制
          ref: NULL
代码语言:txt
复制
         rows: 4
代码语言:txt
复制
        Extra: Using intersect(weixin_user_id,user_id); Using where

explain的结果就是该次查询的执行计划,几个比较重要的字段为:

  • select_type:就是select类型,常用的有如下几种:
代码语言:txt
复制
  - SIMPLE(简单select查询,不包含union或子查询等)    
  - PRIMARY(主键查询)     
  - SUBQUERY     
  - UNION     
  - UNION RESULT     
  • type字段:显示了连接使用了哪种类别,有无使用索引.这列很重要,往往能够表明你的SQL语句执行的速度,好坏等 其中从好到坏的取值依次有:system、const(最多只有一行满足条件)、eq_ref、ref、fulltext、ref_or_null、index_merge(该联接类型表示使用了索引合并优化方法)、unique_subquery、index_subquery、range(索引的范围查询)、index(扫描整个索引树)、ALL(扫描全表)
  • possible_keys字段:该列指出Mysql可能会选择使用的索引
  • key字段:Mysql在执行该条查询语句时,真正选择使用的索引
  • rows字段:显示MySQL认为它执行查询时必须检查的行数,不是最后得出的结果的真实行数
  • Extra字段:显示Mysql解析查询时的详细信息,例如使用了哪一种索引合并优化算法、查询是否使用了临时表、是否使用了filesort、等等,通过该字段你可以判断出Mysql执行查询计划是否跟你的预期一致,来决定是否要对SQL语句进行优化,从而获取更优的执行计划

在执行查询语句时,Mysql对select语句进行了很多优化,例如:

  • where 语句优化:去掉无用的where条件等等,详见官方文档
  • range 优化
  • index merge算法优化
  • 判空优化
  • order by语句优化
  • group by语句优化
  • 等等

这里不得不提的是range optimization和index merge Optimization,我在写SQL语句时,所用到的select语句大多数情况下,Mysql进行的都是这两种优化:

index merge Optimization:

该种优化是基于三种算法来进行的:The Index Merge Intersection Access Algorithm、The Index Merge Union Access Algorithm以及The Index Merge Sort-Union Access Algorithm

首先我们说说 The Index Merge Intersection Access Algorithm,上述SQL语句的执行就是使用了该种算法,该算法执行的前提条件为:

  • where 条件里,所有的condition都用AND相连
  • 每一个condition必须符合以下规则:
    • 主键范围查询
    • 对于多列索引,where条件中需要有多列索引的每一部分: key_part1=const1 AND key_part2=const2 ... AND key_partN=constN

例如:

代码语言:txt
复制
SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20;
代码语言:txt
复制
SELECT * FROM tbl_name WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;

该算法操作流程如下:

  1. 同时扫描多个索引,然后取索引扫描结果的交集
  2. 如果需要的columns都在刚刚的index 结果中,则不需要再去数据表中取数据(例如:SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;)
  3. 然后根据索引的交集去数据表中取出所需要的真实数据记录

那么针对我们上述SQL语句的案例,执行过程就是:

  1. 先从user_id索引树中找user_id=39的记录,同时从weixin_user_id索引树中找weixin_user_id=61的记录
  2. 取两次索引结果的并集(这里,每个索引中都会包含主键的)
  3. 拿着最终索引结果去数据表中取真实记录

同理:The Index Merge Union Access Algorithm和Intersection相似,区别在于把AND条件换成了OR。

range optimization

对于range optimization,TNT存储引擎大神总结过一篇blog

所有SQL的where条件均可归纳为3大类:

  • Index Key (First Key & Last Key): - 用于确定SQL查询在索引中的连续范围(起始+终止)
  • Index Filter: - 在完成Index Key的提取之后,根据where条件固定了索引的查询范围,但是此范围中的项,并不都是满足查询条件的项,需要过滤index,具体提取规则查看该blog
  • Table Filter: - 所有不属于索引列的查询条件,均归为Table Filter之中(Mysql会先根据索引从数据表中读取出真实记录,然后根据table filter中的条件来过滤结果)

那么其实我们再回头来看之前那条SQL语句的执行时,已经很清晰的知道其进行查询时的一个大概过程:

  1. 首先从两个索引树中找数据
  2. 然后去两个结果的交集,从数据文件中读取真实记录
  3. 最后在根据table fileter条件(add_time > "2014-10-01")来过滤结果,返回给客户端。

结论

因为我们的查询基本上都不是只有add_time一个条件,一般都会带有其他索引,所以这里我们不需要给add_time添加一个索引,当select语句中有该condition时,其作为table filter也不会对查询速度又什么影响。

不过具体问题具体分析,例如在某些场景下,例如:论坛中会存在查找某一个时间段的所有问题等场景,此时由于查询条件仅仅是add_time一个维度,显然给其加上索引能够大大加速查找。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 场景:
    • index merge Optimization:
      • range optimization
        • 结论
        相关产品与服务
        云数据库 SQL Server
        腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
        领券
        问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档