分析MySQL中优化distinct的技巧

有这样的一个需求:select count(distinct nick) from user_access_xx_xx;

这条sql用于统计用户访问的uv,由于单表的数据量在10G以上,即使在user_access_xx_xx上加上nick的索引,

通过查看执行计划,也为全索引扫描,sql在执行的时候,会对整个服务器带来抖动;

root@db 09:00:12>select count(distinct nick) from user_access;

+———————-+

| count(distinct nick) |

+———————-+

|        806934 |

+———————-+

1 row in set (52.78 sec)

执行一次sql需要花费52.78s,已经非常的慢了

现在需要换一种思路来解决该问题:

我们知道索引的值是按照索引字段升序的,比如我们对(nick,other_column)两个字段做了索引,那么在索引中的则是按照nick,other_column的升序排列:

我们现在的sql:select count(distinct nick) from user_access;则是直接从nick1开始一条条扫描下来,直到扫描到最后一个nick_n,

那么中间过程会扫描很多重复的nick,如果我们能够跳过中间重复的nick,则性能会优化非常多(在oracle中,这种扫描技术为loose index scan,但在5.1的版本中,mysql中还不能直接支持这种优化技术):

所以需要通过改写sql来达到伪loose index scan:

root@db 09:41:30>select count(*) from ( select distinct(nick) from user_access)t ;

| count(*) |

+———-+

|  806934 |

1 row in set (5.81 sec)

Sql中先选出不同的nick,最后在外面套一层,就可以得到nick的distinct值总和;

最重要的是在子查询中:select distinct(nick) 实现了上图中的伪loose index scan,优化器在这个时候的执行计划为Using index for group-by ,

需要注意的是mysql把distinct优化为group by,它首先利用索引来分组,然后扫描索引,对需要的nick只扫描一次;

两个sql的执行计划分别为:

优化写法:

root@db 09:41:10>explain select distinct(nick) from user_access-> ;

+—-+————-+——————————+——-+—————+————-| id | select_type | table            | type | possible_keys | key               | key_len | ref | rows  | Extra          |

+—-+————-+——————————+——-+—————+————-

| 1 | SIMPLE   | user_access | range | NULL     | ind_user_access_nick | 67   | NULL | 2124695 | Using index for group-by |

+—-+————-+——————————+——-+—————+————-

原始写法:

root@db 09:42:55>explain select count(distinct nick) from user_access;

+—-+————-+——————————+——-+—————+————-

| id | select_type | table            | type | possible_keys | key            | key_len | ref | rows   | Extra    |

+—-+————-+——————————+——-+—————+————-

| 1 | SIMPLE   | user_access | index | NULL     | ind_user_access | 177   | NULL | 19546123 | Using index |
时间: 2015-05-06

MySQL中索引优化distinct语句及distinct的多字段操作

MySQL通常使用GROUPBY(本质上是排序动作)完成DISTINCT操作,如果DISTINCT操作和ORDERBY操作组合使用,通常会用到临时表.这样会影响性能. 在一些情况下,MySQL可以使用索引优化DISTINCT操作,但需要活学活用.本文涉及一个不能利用索引完成DISTINCT操作的实例. 实例1 使用索引优化DISTINCT操作 create table m11 (a int, b int, c int, d int, primary key(a)) engine=INNODB;

MySQL关键字Distinct的详细介绍

MySQL关键字Distinct用法介绍 DDL Prepare SQL: create table test(id bigint not null primary key auto_increment, name varchar(10) not null, phone varchar(10) not null, email varchar(30) not null)engine=innodb; Prepare Data: insert into test(name, phone, email)

MySQL中distinct和count(*)的使用方法比较

首先对于MySQL的DISTINCT的关键字的一些用法: 1.在count 不重复的记录的时候能用到,比如SELECT COUNT( DISTINCT id ) FROM tablename:就是计算talbebname表中id不同的记录有多少条. 2,在需要返回记录不同的id的具体值的时候可以用,比如SELECT DISTINCT id FROM tablename:返回talbebname表中不同的id的具体的值. 3.上面的情况2对于需要返回mysql表中2列以上的结果时会有歧义,比如SE

MySQL中distinct语句的基本原理及其与group by的比较

DISTINCT 实际上和 GROUP BY 操作的实现非常相似,只不过是在 GROUP BY 之后的每组中只取出一条记录而已.所以,DISTINCT 的实现和 GROUP BY 的实现也基本差不多,没有太大的区别.同样可以通过松散索引扫描或者是紧凑索引扫描来实现,当然,在无法仅仅使用索引即能完成 DISTINCT 的时候,MySQL 只能通过临时表来完成.但是,和 GROUP BY 有一点差别的是,DISTINCT 并不需要进行排序.也就是说,在仅仅只是 DISTINCT 操作的 Query

MySQL中distinct与group by语句的一些比较及用法讲解

在数据表中记录了用户验证时使用的书目,现在想取出所有书目,用DISTINCT和group by都取到了我想要的结果,但我发现返回结果排列不同,distinct会按数据存放顺序一条条显示,而group by会做个排序(一般是ASC).           DISTINCT 实际上和 GROUP BY 操作的实现非常相似,只不过是在 GROUP BY 之后的每组中只取出一条记录而已.所以,DISTINCT 的实现和 GROUP BY 的实现也基本差不多,没有太大的区别,同样可以通过松散索引扫描或者是

使用distinct在mysql中查询多条不重复记录值的解决办法

在使用mysql时,有时需要查询出某个字段不重复的记录,虽然mysql提供有distinct这个关键字来过滤掉多余的重复记录只保留一条,但往往只用它来返回不重复记录的条数,而不是用它来返回不重记录的所有值.其原因是distinct只能返回它的目标字段,而无法返回其它字段,这个问题让我困扰了很久,用distinct不能解决的话,我只有用二重循环查询来解决,而这样对于一个数据量非常大的站来说,无疑是会直接影响到效率的.所以我花了很多时间来研究这个问题,网上也查不到解决方案,期间把容容拉来帮忙,结果是

解析mysql中:单表distinct、多表group by查询去除重复记录

单表的唯一查询用:distinct多表的唯一查询用:group bydistinct 查询多表时,left join 还有效,全连接无效,在使用mysql时,有时需要查询出某个字段不重复的记录,虽然mysql提供有distinct这个关键字来过滤掉多余的重复记录只保留一条,但往往只用它来返回不重复记录的条数,而不是用它来返回不重复记录的所有值.其原因是distinct只能返回它的目标字段,而无法返回其它字段,用distinct不能解决的话,我只有用二重循环查询来解决,而这样对于一个数据量非常大的

MySQL中Distinct和Group By语句的基本使用教程

MySQL Distinct 去掉查询结果重复记录 DISTINCT 使用 DISTINCT 关键字可以去掉查询中某个字段的重复记录. 语法: SELECT DISTINCT(column) FROM tb_name 例子: 假定 user 表有如下记录: uid username 1 小李 2 小张 3 小李 4 小王 5 小李 6 小张 SQL 语句: SELECT DISTINCT(username) FROM user 返回查询结果如下: username 小李 小张 小王 提示 使用

MySQL中distinct与group by之间的性能进行比较

最近在网上看到了一些测试,感觉不是很准确,今天亲自测试了一番.得出了结论,测试过程在个人计算机上,可能不够全面,仅供参考. 测试过程: 准备一张测试表 CREATE TABLE `test_test` ( `id` int(11) NOT NULL auto_increment, `num` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

MySQL中distinct语句去查询重复记录及相关的性能讨论

在 MySQL 查询中,可能会包含重复值.这并不成问题,不过,有时您也许希望仅仅列出不同(distinct)的值. 关键词 DISTINCT 用于返回唯一不同的值,就是去重啦.用法也很简单: SELECT DISTINCT * FROM tableName DISTINCT 这个关键字来过滤掉多余的重复记录只保留一条. 另外,如果要对某个字段去重,可以试下: SELECT *, COUNT(DISTINCT nowamagic) FROM table GROUP BY nowamagic 这个用

MySQL中join语句的基本使用教程及其字段对性能的影响

join语句的基本使用 SQL(MySQL) JOIN 用于根据两个或多个表中的字段之间的关系,从这些表中得到数据. JOIN 通常与 ON 关键字搭配使用,基本语法如下: ... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona table1 通常称为左表,table2 称为右表.ON 关键字用于设定匹配条件,用于限定在结果集合中想要哪些行.如果需要指定其他条件,后面可以加上 WHERE 条件 或者 LIMIT 以限制记录返回数目等.

sqlserver中distinct的用法(不重复的记录)

下面先来看看例子: table表 字段1     字段2   id        name   1           a   2           b   3           c   4           c   5           b 库结构大概这样,这只是一个简单的例子,实际情况会复杂得多. 比如我想用一条语句查询得到name不重复的所有数据,那就必须 使用distinct去掉多余的重复记录. select distinct name from table得到的结果是: ---

细数MySQL中SQL语句的分类

1:数据定义语言(DDL)   用于创建.修改.和删除数据库内的数据结构,如:1:创建和删除数据库(CREATE DATABASE || DROP  DATABASE):2:创建.修改.重命名.删除表(CREATE  TABLE || ALTER TABLE|| RENAME TABLE||DROP  TABLE):3:创建和删除索引(CREATEINDEX  || DROP INDEX)   2:数据查询语言(DQL)   从数据库中的一个或多个表中查询数据(SELECT)   3:数据操作语

MySql中如何使用 explain 查询 SQL 的执行计划

explain命令是查看查询优化器如何决定执行查询的主要方法. 这个功能有局限性,并不总会说出真相,但它的输出是可以获取的最好信息,值得花时间去了解,因为可以学习到查询是如何执行的. 1.什么是MySQL执行计划 要对执行计划有个比较好的理解,需要先对MySQL的基础结构及查询基本原理有简单的了解. MySQL本身的功能架构分为三个部分,分别是 应用层.逻辑层.物理层,不只是MySQL ,其他大多数数据库产品都是按这种架构来进行划分的. 应用层,主要负责与客户端进行交互,建立链接,记住链接状态,

PHP获取MySQL执行sql语句的查询时间方法

如下所示: //计时开始 runtime(); //执行查询 mysql_query($sql); //计时结束. echo runtime(1); //计时函数 function runtime($mode=0) { static $t; if(!$mode) { $t = microtime(); return; } $t1 = microtime(); list($m0,$s0) = explode(" ",$t); list($m1,$s1) = explode("

MySQL中or语句用法示例

1.mysql中or语法的使用,在mysql语法中or使用注意点. 项目遇到坑,遍历发放奖励数据查询错误!!! $sql = 'SELECT * FROM `vvt_spread_doubleegg_exchange_award` AS p WHERE p.`act_type` = 4 or p.`act_type` = 5 AND p.`user_id` = ' .$user_id ; sql中的or语法一般用于多个条件的查询,上面的语法查询的相当于:两个sql查询出来的数据集合. $sql

MySQL中select语句使用order按行排序

本文介绍MySQL数据库中执行select查询语句,并对查询的结果使用order by 子句进行排序. 再来回顾一下SQL语句中的select语句的语法: Select 语句的基本语法: Select <列的集合> from <表名> where <条件> order by <排序字段和方式> 如果要对查询结果按某个字段排序,则要使用order by 子句,如下: select * from <表名> order by <字段名称>