mysql 的一次查询优化过程

有这样一条查询语句：

select sum(index_count) as count, theday ,appVersion, channel, type
  from tableA 
 where project_id='qjp' and theday>='2019-05-17' and theday<='2019-05-23' 
 group by theday
复制代码

执行用时21s. 数据量55万左右，实在太慢了。

首先我需要确定这21s 都用在什么地方了,即性能瓶颈在哪里。需要用到Msql的 Query Profiler 诊断分析工具。使用方法如下：

1. 开启 profiling 参数

  root@localhost : (none) 10:53:11> set profiling=1;
  Query OK, 0 rows affected (0.00 sec)
复制代码

2. 执行你的查询 Query

mysql> select sum(index_count) as count, theday from tableA where project_id='qjp' and theday>='2019-05-17' and theday<='2019-05-23' group by theday
+-----------+-----------+------------+------------+------
| count     | theday     | appVersion | channel  | type | 
+-----------+-----------+------------+------------+------
| 180205137 | 2019-05-17 | 1.2.6      | 55550006 |      | 
| 168597045 | 2019-05-18 | 1.2.7      | 55550337 |      | 
| 153154098 | 2019-05-19 | 1.2.7      | 55550006 |      | 
+-----------+------------+------------+----------+------+
7 rows in set (21.03 sec)
复制代码

在开启 Query Profiler 功能之后，MySQL 就会自动记录所有执行的 Query 的 profile 信息了。

3. 获取系统中保存的所有 Query 的 profile 概要信息

mysql> show profiles;
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration    | Query    |
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------+
|        1 | 21.02640425 | select sum(index_count) as count, theday from tableA where project_id='qjp' and theday>='2019-05-17' and theday<='2019-05-23' group by theday|
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
复制代码

通过执行 “show profiles ” 命令获取当前系统中保存的多个 Query 的 profile 的概要信息。

4. 针对单个 Query 获取详细的 profile 信息。

mysql> show profile for query 1;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000117 |
| checking permissions |  0.000020 |
| Opening tables       |  0.000032 |
| init                 |  0.000039 |
| System lock          |  0.000021 |
| optimizing           |  0.000023 |
| statistics           |  0.000124 |
| preparing            |  0.000032 |
| Creating tmp table   |  0.000042 |
| Sorting result       |  0.000018 |
| executing            |  0.000016 |
| Sending data         | 21.021533 |
| Creating sort index  |  0.003272 |
| end                  |  0.000019 |
| query end            |  0.000022 |
| removing tmp table   |  0.000728 |
| query end            |  0.000019 |
| closing tables       |  0.000023 |
| freeing items        |  0.000064 |
| logging slow query   |  0.000020 |
| Opening tables       |  0.000025 |
| System lock          |  0.000146 |
| cleaning up          |  0.000051 |
+----------------------+-----------+
23 rows in set, 1 warning (0.00 sec)

复制代码

从上面的可看出时间主要用在 Sending data ,所谓的“Sending data” 并不是单纯的发送数据，而是包括“收集 + 发送 数据”。后来我又尝试将查询的列减少，但是没有什么效果。后来无意间检查下表结构发现其中 有两列projet_id和 theday的列类型是text，好像知道什么了（不知道是谁设计的，但是其实没有必要），改这两列为varchar类型，再执行一次查询，只用了0.5秒。

自此问题解决。总结一下，设计表的时候，列类型一定要考虑好，text类型尽量少用，设置为varchar的时候，长度够用就好，越短性能越好。

=============我是分割线======下面是追加的内容===================

查看表结构：

mysql> desc tmp_table;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| project_id  | text         | YES  | MUL | NULL    |       |
| index_count | varchar(100) | YES  |     | NULL    |       |
| theday      | text         | YES  | MUL | NULL    |       |
| app_version | varchar(32)  | NO   |     | NULL    |       |
| channel     | varchar(32)  | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
复制代码

修改列类型：

alter table 表名 MODIFY COLUMN 列名 VARCHAR(16)
复制代码

对了，在处理的过程中，我还尝试了加索引，但是发现加索引后，查询时间不快反而变的更慢。这里有一篇关于索引的文章 blog.csdn.net/u014470581/…