mysql sql优化

Question

刚刚接触mysql，希望把全表扫描给干掉，求指点，SQL执行计划如下：
explain 
SELECT * FROM sum_and_rate_22
         RIGHT JOIN (
         SELECT
         MAX(created_at) AS created_at,
         source_entity_name
         FROM sum_and_rate_22
         GROUP BY source_entity_name) AS temp
         ON sum_and_rate_22.created_at = temp.created_at
         AND sum_and_rate_22.source_entity_name = temp.source_entity_name;

Answer 1

自己抢个沙发，这个是表结构：
mysql> desc sum_and_rate_22;
+---------------------+--------------+------+-----+-------------------+----------------+
| Field               | Type         | Null | Key | Default           | Extra          |
+---------------------+--------------+------+-----+-------------------+----------------+
| id                  | int(11)      | NO   | PRI | NULL              | auto_increment |
| source_entity_name  | varchar(100) | NO   | MUL | NULL              |                |
| source_count_rate   | double       | NO   |     | NULL              |                |
| source_bytes_rate   | double       | NO   |     | NULL              |                |
| source_count_sum    | bigint(20)   | NO   |     | NULL              |                |
| source_bytes_sum    | bigint(20)   | NO   |     | NULL              |                |
| source_collect_time | datetime     | NO   |     | NULL              |                |
| sink_count_rate     | double       | NO   |     | NULL              |                |
| sink_bytes_rate     | double       | NO   |     | NULL              |                |
| sink_count_sum      | bigint(20)   | NO   |     | NULL              |                |
| sink_bytes_sum      | bigint(20)   | NO   |     | NULL              |                |
| sink_collect_time   | datetime     | NO   |     | NULL              |                |
| errq_count_sum      | bigint(20)   | NO   |     | NULL              |                |
| errq_bytes_sum      | bigint(20)   | NO   |     | NULL              |                |
| errq_collect_time   | datetime     | NO   |     | CURRENT_TIMESTAMP |                |
| created_at          | datetime     | NO   | MUL | CURRENT_TIMESTAMP |                |
| created_by          | int(11)      | NO   |     | NULL              |                |
| updated_at          | datetime     | NO   |     | CURRENT_TIMESTAMP |                |
| updated_by          | int(11)      | NO   |     | NULL              |                |
+---------------------+--------------+------+-----+-------------------+----------------+

Answer 2

在source_entity_name,created_at上建立复合索引 

Answer 3

mysql> alter table sum_and_rate_22 add index indx_union_created_source(created_at,source_entity_name);
Query OK, 0 rows affected, 1 warning (0.45 sec)
Records: 0  Duplicates: 0  Warnings: 1mysql> explain SELECT * FROM sum_and_rate_22
    ->          RIGHT JOIN (
    ->          SELECT
    ->          MAX(created_at) AS created_at,
    ->          source_entity_name
    ->          FROM sum_and_rate_22
    ->          GROUP BY source_entity_name) AS temp
    ->          ON sum_and_rate_22.created_at = temp.created_at
    ->          AND sum_and_rate_22.source_entity_name = temp.source_entity_name;这个是目前数据库里面有1w+ 的数据以后的结果，目前看来还是走的全表扫描，求指教。

Answer 4

mysql> alter table sum_and_rate_22 add index indx_union_created_source(created_at,source_entity_name);
Query OK, 0 rows affected, 1 warning (0.
Records: 0  Duplicates: 0  Warnings: 1mysql> explain SELECT * FROM sum_and_rate_22
    ->          RIGHT JOIN (
    ->          SELECT
    ->          MAX(created_at) AS created_at,
    ->          source_entity_name
    ->          FROM sum_and_rate_22
    ->          GROUP BY source_entity_name) AS temp
    ->          ON sum_and_rate_22.created_at = temp.created_at
    ->          AND sum_and_rate_22.source_entity_name = temp.source_entity_name;这个是目前数据库里面有1w+ 的数据以后的结果，目前看来还是走的全表扫描，求指教。
sum_and_rate_22 表没有过滤条件(WHERE）又是RIGHT JOIN 肯定是要走全表扫描的

Answer 5

SELECT * FROM sum_and_rate_22 全表扫描