解析Mysql执行语句并优化

最新推荐文章于 2021-11-25 10:48:21 发布

kwu_ganymede

最新推荐文章于 2021-11-25 10:48:21 发布

阅读量364

点赞数 1

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/kwu_ganymede/article/details/53640702

版权

数据库专栏收录该内容

15 篇文章 0 订阅

订阅专栏

解析Mysql执行语句并优化

1、分析执行语句

desc SELECT 
COUNT(1) as loginInvestNum 
FROM YQ_LOGIN_INVESTMENT INNER JOIN YQ_Investment ON YQ_LOGIN_INVESTMENT.AUTHORID = YQ_Investment.AUTHORID 
WHERE 
YQ_LOGIN_INVESTMENT.DATE BETWEEN 
'2016-12-13 00:00:00' 
AND 
'2016-12-13 23:59:59';

没优化前，全表扫描470万。

+----+-------------+---------------------+------+---------------+------+---------+------+---------+--------------------------------+
| id | select_type | table               | type | possible_keys | key  | key_len | ref  | rows    | Extra                          |
+----+-------------+---------------------+------+---------------+------+---------+------+---------+--------------------------------+
|  1 | SIMPLE      | YQ_Investment       | ALL  | NULL          | NULL | NULL    | NULL |  211384 |                                |
|  1 | SIMPLE      | YQ_LOGIN_INVESTMENT | ALL  | NULL          | NULL | NULL    | NULL | 4704015 | Using where; Using join buffer |
+----+-------------+---------------------+------+---------------+------+---------+------+---------+--------------------------------+

470万join21万没有索引，15分钟也出不来

2、按需创建索引

create index idx_date_authorid on YQ_LOGIN_INVESTMENT(date,AUTHORID);

创建复合索引

create index idx_AUTHORID on YQ_Investment(AUTHORID);

创建普通索引

查看索引

show index from YQ_LOGIN_INVESTMENT;

查看字段

show columns from  YQ_LOGIN_INVESTMENT;

如下：

+---------------------+------------+-------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table               | Non_unique | Key_name          | Seq_in_index | Column_name            | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------------+------------+-------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+
| YQ_LOGIN_INVESTMENT |          0 | PRIMARY           |            1 | PK_YQ_LOGIN_INVESTMENT | A         |     4704015 |     NULL | NULL   |      | BTREE      |         |
| YQ_LOGIN_INVESTMENT |          1 | idx_date_authorid |            1 | DATE                   | A         |          18 |     NULL | NULL   |      | BTREE      |         |
| YQ_LOGIN_INVESTMENT |          1 | idx_date_authorid |            2 | AUTHORID               | A         |     4704015 |     NULL | NULL   |      | BTREE      |         |
+---------------------+------------+-------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+

分析执行语句优化后，权扫描172610行。

+----+-------------+---------------------+-------+-------------------+-------------------+---------+----------------------------------+--------+--------------------------+
| id | select_type | table               | type  | possible_keys     | key               | key_len | ref                              | rows   | Extra                    |
+----+-------------+---------------------+-------+-------------------+-------------------+---------+----------------------------------+--------+--------------------------+
|  1 | SIMPLE      | YQ_LOGIN_INVESTMENT | range | idx_date_authorid | idx_date_authorid | 4       | NULL                             | 172610 | Using where; Using index |
|  1 | SIMPLE      | YQ_Investment       | ref   | idx_AUTHORID      | idx_AUTHORID      | 122     | nlp.YQ_LOGIN_INVESTMENT.AUTHORID |      1 | Using where; Using index |
+----+-------------+---------------------+-------+-------------------+-------------------+---------+----------------------------------+--------+--------------------------+

3、总结：

关系库的查询，创建合适的索引非常重要！