• mysql数据库怎么优化?mysql数据库优化之group by和distinct的优化
  • 发布于 2个月前
  • 120 热度
    0 评论
今天讲一下mysql数据库查询优化技术中的对group by和distinct排序的优化介绍。

Group by相比order by主要是多个排序之后的分组操作,在mysql中,group by的实现有三种,和order by类似,group by的前两种算法也是可以利用索引来完成排序,最后一种为完全无法使用索引来排序。

1 使用松散索引扫描实现grouop by
所谓松散索引实现group by,就是mysql完全利用索引扫描来实现grouop by,而且并不需要扫描所有满足条件的索引键即可完成操作得出结果。
mysql> create index ind_sche_tab_col on t_order01(table_schema,table_name,column
_name);
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select table_name,max(column_name) from t_order01 where table_sch
ema<'T' group by table_schema,table_name;
+----+-------------+-----------+-------+------------------+------------------+--
-------+------+------+---------------------------------------+
| id | select_type | table     | type  | possible_keys    | key              | k
ey_len | ref  | rows | Extra                                 |
+----+-------------+-----------+-------+------------------+------------------+--
-------+------+------+---------------------------------------+
|  1 | SIMPLE      | t_order01 | range | ind_sche_tab_col | ind_sche_tab_col | 3
88     | NULL |  195 | Using where; Using index for group-by |
+----+-------------+-----------+-------+------------------+------------------+--
-------+------+------+---------------------------------------+
1 row in set (0.00 sec)
这里我们看extra的:using index for group-by,这个表示这个group by使用松散索引来实现group by。
这个松散索引实现的方式大致如下:
1 首先通过table_scham<'T'过滤,此时可以利用索引完成
2 然后scan table_scham<'T' 对应的索引键
3 最后scan table_schema<’T’时,只扫描最后column_name的索引键

注意这里特别需要注意的是扫描满足where过滤条件的索引键时,只是扫描了每组 group_id,user_id的最后一组索引键,这个也是跟之前松散索引的定义相符合。

松散索引必须满足的条件:
1 group by条件字段必须在同一个索引的最前面的连续位置
2 在使用group by时,只能使用max和min两个聚合函数
3 如果引用到了该索引中group by条件之外的字段,必须以常量形式存在
需要注意的是这个where的过滤条件并不一定是必须的,松散索引的关键是否需要在where过滤条件后需要扫描所有的索引键,比如如下的query语句都是可以利用松散索引实现的。
mysql> explain select max(column_name) from t_order01 group by table_schema,tabl
e_name;
+----+-------------+-----------+-------+---------------+------------------+-----
----+------+------+--------------------------+
| id | select_type | table     | type  | possible_keys | key              | key_
len | ref  | rows | Extra                    |
+----+-------------+-----------+-------+---------------+------------------+-----
----+------+------+--------------------------+
|  1 | SIMPLE      | t_order01 | range | NULL          | ind_sche_tab_col | 388
    | NULL |  223 | Using index for group-by |
+----+-------------+-----------+-------+---------------+------------------+-----
----+------+------+--------------------------+
1 row in set (0.00 sec)
2 使用紧凑索引扫描实现group by
紧凑索引扫描实现group by和松散索引扫描的区别主要在于需要扫描索引时候,读取所有满足条件的索引键,然后再来完成group by操作得到相应的结果。
mysql> explain select max(column_name) from t_order01 where table_schema='ROOT'
group by table_name;
+----+-------------+-----------+------+------------------+------------------+---
------+-------+------+--------------------------+
| id | select_type | table     | type | possible_keys    | key              | ke
y_len | ref   | rows | Extra                    |
+----+-------------+-----------+------+------------------+------------------+---
------+-------+------+--------------------------+
|  1 | SIMPLE      | t_order01 | ref  | ind_sche_tab_col | ind_sche_tab_col | 19
4     | const |    1 | Using where; Using index |
+----+-------------+-----------+------+------------------+------------------+---
------+-------+------+--------------------------+
1 row in set (0.00 sec)
上述query语句中extra只有using index,此时优化器大体步骤如下:
1 通过table_schema='ROOT'过滤数据,此时可以走这个复合的索引来过滤
2 取出所有table_schema='ROOT'满足的数据
3 扫描步骤2取出的所有索引键来完成排序

而如果上述sql语句换一个过滤条件,也就是将query中的索引前导列的等值过滤修改为范围过滤
mysql> explain select max(column_name) from t_order01 where table_schema<'T' gro
up by table_name;
+----+-------------+-----------+-------+------------------+------------------+--
-------+------+------+----------------------------------------------------------
-+
| id | select_type | table     | type  | possible_keys    | key              | k
ey_len | ref  | rows | Extra
 |
+----+-------------+-----------+-------+------------------+------------------+--
-------+------+------+----------------------------------------------------------
-+
|  1 | SIMPLE      | t_order01 | index | ind_sche_tab_col | ind_sche_tab_col | 5
82     | NULL |  797 | Using where; Using index; Using temporary; Using filesort
 |
+----+-------------+-----------+-------+------------------+------------------+--
-------+------+------+----------------------------------------------------------
-+
1 row in set (0.00 sec)
这里extra出现了using temporary和using filesort的信息,此时mysql无法通过索引(松散索引和紧凑索引都不行)去实现group by,优化器会先通过索引查找数据,然后将检索的数据放入到临时表中,最后在临时表中完成排序和分组。

而同样的table_schema<'T'过滤条件,将group by分组从索引第二列换为索引前导列是可以利用索引完成group by的,此时using index表示用的是紧凑索引完成group by
mysql> explain select max(column_name) from t_order01 where table_schema<'T' gro
up by table_schema;
+----+-------------+-----------+-------+------------------+------------------+--
-------+------+------+--------------------------+
| id | select_type | table     | type  | possible_keys    | key              | k
ey_len | ref  | rows | Extra                    |
+----+-------------+-----------+-------+------------------+------------------+--
-------+------+------+--------------------------+
|  1 | SIMPLE      | t_order01 | index | ind_sche_tab_col | ind_sche_tab_col | 5
82     | NULL |  888 | Using where; Using index |
+----+-------------+-----------+-------+------------------+------------------+--
-------+------+------+--------------------------+
1 row in set (0.00 sec)

对于后一个query利用紧凑索引完成排序的个人比较好理解,先是优化器完成了索引全扫描,其中过滤了table_schema<'T'条件,然后由于group by的是索引前导列,此时可以直接利用之前索引过滤的数据来实现group by。

而对于前一个无法利用索引完成group by的例子理解起来也比较简单,由于首先索引前导列过滤是个范围值,然后group by并不是索引的前导列,此时无法就单单利用索引能够实现group by,只能先构建临时表完成排序和分组。

当group by条件字段并不连续或者不是索引前缀部分的时候,mysql优化器无法使用松散索引完成扫描,但是如果query 语句中存在一个常量值来引用缺失的索引键,则可以使用紧凑索引扫描完成group by操作。这个是因为有常量填充索引前导列情况下,可以行使完成的索引前缀,这些索引前缀可以用于索引查找。

3使用临时表实现group by
Mysql想利用索引实现group by,必须满足group by所有字段存放于同一个有序的索引中,记住这里必须是有序的索引(如hash索引并不是一个有序的索引),而且能否通过索引实现group by,还和聚合函数有关系,比如松散索引就只支持max和min的聚合函数。比如如果使用别的聚合函数,就无法使用松散索引。
mysql> explain select table_name,count(column_name) from t_order01 where table_s
chema<'ROOT' group by table_schema,table_name;
+----+-------------+-----------+-------+------------------+------------------+--
-------+------+------+--------------------------+
| id | select_type | table     | type  | possible_keys    | key              | k
ey_len | ref  | rows | Extra                    |
+----+-------------+-----------+-------+------------------+------------------+--
-------+------+------+--------------------------+
|  1 | SIMPLE      | t_order01 | index | ind_sche_tab_col | ind_sche_tab_col | 5
82     | NULL |  797 | Using where; Using index |
+----+-------------+-----------+-------+------------------+------------------+--
-------+------+------+--------------------------+
1 row in set (0.00 sec)

而如果无法利用索引完成group by,优化器此时需要构建临时表用于排序和分组来实现group by
mysql> explain select table_name,count(column_name) from t_order01 where table_s
chema<'ROOT' and table_schema>'RAM' group by table_name;
+----+-------------+-----------+-------+------------------+------------------+--
-------+------+------+----------------------------------------------------------
-+
| id | select_type | table     | type  | possible_keys    | key              | k
ey_len | ref  | rows | Extra
 |
+----+-------------+-----------+-------+------------------+------------------+--
-------+------+------+----------------------------------------------------------
-+
|  1 | SIMPLE      | t_order01 | range | ind_sche_tab_col | ind_sche_tab_col | 1
94     | NULL |    1 | Using where; Using index; Using temporary; Using filesort
 |
+----+-------------+-----------+-------+------------------+------------------+--
-------+------+------+----------------------------------------------------------
-+
1 row in set (0.00 sec)

这个很好分析首先谓词条件虽然是索引前导列,但是确是一个范围值,然后group by排序时并不是索引的前导列,此时也就无法单单就利用索引就实现了group by,就需要构建临时表并在其中完成排序和分组。

对于上述三种mysql处理group by的方式,可以有以下两种优化思路
1 尽可能让mysql利用索引完成group by操作,优先考虑松散索引,其次考虑紧凑索引,我们可以调整索引或者修改query来满足索引实现group by
2 当无法使用索引完成排序时,由于要使用临时表进行filesort,所以必须要有足够的sort_buffer_size来供mysql排序时候使用,而且尽量不要进行大结果集group by,以免超出系统设置的临时表大小,而将临时表数据copy到磁盘上面再进行操作,此时排序分组的性能将会成数量级的下降。

如果group by无用利用索引完成排序,此时可以在整个语句后面添加order by null禁用排序,此时优化器处理group by时只会分组,不会进行排序。
mysql> explain select column_name,count(*) from t_order01 group by column_name;
+----+-------------+-----------+-------+---------------+------------------+-----
----+------+------+----------------------------------------------+
| id | select_type | table     | type  | possible_keys | key              | key_
len | ref  | rows | Extra                                        |
+----+-------------+-----------+-------+---------------+------------------+-----
----+------+------+----------------------------------------------+
|  1 | SIMPLE      | t_order01 | index | NULL          | ind_sche_tab_col | 582
    | NULL |  908 | Using index; Using temporary; Using filesort |
+----+-------------+-----------+-------+---------------+------------------+-----
----+------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select column_name,count(*) from t_order01 group by column_name o
rder by null;
+----+-------------+-----------+-------+---------------+------------------+-----
----+------+------+------------------------------+
| id | select_type | table     | type  | possible_keys | key              | key_
len | ref  | rows | Extra                        |
+----+-------------+-----------+-------+---------------+------------------+-----
----+------+------+------------------------------+
|  1 | SIMPLE      | t_order01 | index | NULL          | ind_sche_tab_col | 582
    | NULL |  908 | Using index; Using temporary |
+----+-------------+-----------+-------+---------------+------------------+-----
----+------+------+------------------------------+
1 row in set (0.00 sec)

上面的extra我们发觉有了order by null的query没有出现using filesort信息,下面列举两个简单的query来展示就很清楚了。
mysql> select * from t;
+------+------+
| id   | name |
+------+------+
|  100 | op   |
|  900 | op   |
|    9 | op   |
|    9 | opw  |
+------+------+
4 rows in set (0.00 sec)

mysql> select id from t group by id;
+------+
| id   |
+------+
|    9 |
|  100 |
|  900 |
+------+
3 rows in set (0.00 sec)

mysql> select id from t group by id order by null;
+------+
| id   |
+------+
|  100 |
|  900 |
|    9 |
+------+
3 rows in set (0.00 sec)

除了order by和group by,还有一类query也跟排序相关,那就是distinct的query,distinct跟group by不同的是在分组后只取每组中的一条记录。

distinct的实现和group by基本都差不多,没有太大的区别,都可以用松散索引或者紧凑索引来实现,而当无法索引完成时,优化器只能利用临时表来完成。当无法利用索引排序时,distinct跟group by相比不会对数据排序,也就是输出的数据可能是无序的。

mysql> select * from t;
+------+------+
| id   | name |
+------+------+
|  100 | op   |
|  900 | op   |
|    9 | op   |
|    9 | opw  |
+------+------+
4 rows in set (0.00 sec)
    
mysql> select distinct id from t;
+------+
| id   |
+------+
|  100 |
|  900 |
|    9 |
+------+
3 rows in set (0.00 sec)

mysql> explain select distinct id from t;
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | Extra           |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    4
 | Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------+
1 row in set (0.00 sec)

这里由于id上没有索引,实现distinct用的临时表,但是我们没有看见extra有using filesort的信息,也就是没有排序,而且我们观察输出的结果id其实是无序的。

接下来在id列上建立索引
mysql> create index ind_id on t(id);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select distinct id from t;
+------+
| id   |
+------+
|    9 |
|  100 |
|  900 |
+------+
3 rows in set (0.00 sec)

mysql> explain select distinct id from t;
+----+-------------+-------+-------+---------------+--------+---------+------+--
----+-------------+
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | r
ows | Extra       |
+----+-------------+-------+-------+---------------+--------+---------+------+--
----+-------------+
|  1 | SIMPLE      | t     | index | NULL          | ind_id | 5       | NULL |
  4 | Using index |
+----+-------------+-------+-------+---------------+--------+---------+------+--
----+-------------+
1 row in set (0.00 sec)

此时实现distinct用了紧凑索引来实现,而且输出的数据是有序的。

对于distinct的优化和group by基本相同,就是尽量确保用索引实现分组,同样不要在大结果集上面进行distinct操作,以免超出系统设置的临时表大小而将临时表数据copy到磁盘上面进行操作,磁盘的IO和内存的IO性能差好多。
用户评论