MySQL查询索引分析

场景:

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

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

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

show index from table_name

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

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

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

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

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

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

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

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

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

  • select_type:就是select类型,常用的有如下几种:
  - 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

例如:

SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20;
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一个维度,显然给其加上索引能够大大加速查找。

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

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

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

dbms_xplan之display_cursor函数的使用

        DBMS_XPLAN包中display_cursor函数不同于display函数,display_curso...

743
来自专栏java一日一条

一次非常有意思的SQL优化经历

发现没有用到索引,type全是ALL,那么首先想到的就是建立一个索引,建立索引的字段当然是在where条件的字段。

321
来自专栏杨建荣的学习笔记

MySQL关于数据字典的一个疑问

今天看着MySQL的数据字典,突然想到一个问题:为什么MySQL数据字典 information_schema中的表名是大写,而performance_sche...

3448
来自专栏互联网开发者交流社区

数据操纵:SELECT, INSERT, UPDATE, DELETE

892
来自专栏weixuqin 的专栏

MySQL 学习笔记(三):完整性和触发器设计

1034
来自专栏C/C++基础

MySQL数据库的设计和命令行模式下建立详细过程

MySQL数据库管理系统(DBMS)中,包含的MySQL中定义数据字段的类型对你数据库的优化是非常重要的。MySQL支持多种类型,大致可以分为三类:数值、日期/...

680
来自专栏晨星先生的自留地

mysql注入高级篇1--内置系统表注入

1723
来自专栏GuZhenYin

2014/11/06 Oracle触发器初步 2014-11-06 09:03 49人阅读 评论(0)

触发器我就不多解释了,保证数据的完整性的神器,嗯..也是减少程序员工作托管给数据库操作的好帮手.就不讲一些大道理了.通俗点,我们对数据库的操作,无非就是增 删 ...

1756
来自专栏zingpLiu

python【第十二篇】Mysql基础

数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,每个数据库都有一个或多个不同的API用于创建,访问,管理,搜索和复制所保存的数据。我们也可...

692
来自专栏文渊之博

mysql表分区简述

数据库分区是一种物理数据库设计技术。虽然分区技术可以实现很多效果,但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间,同时对于应...

623

扫码关注云+社区