原始SQL：

select  distinct metadatalogid,opertime from xwcmmetadatalog where metadataid='4395597' order by opertime desc;

执行计划：

MariaDB [test]> explain select  distinct metadatalogid,opertime from xwcmmetadatalog where metadataid='4395597' order by opertime desc;
+------+-------------+-----------------+------+-------------------------------+-------------------------------+---------+-------+------+-----------------------------+| id   | select_type | table           | type | possible_keys                 | key                           | key_len | ref   | rows | Extra                       |
+------+-------------+-----------------+------+-------------------------------+-------------------------------+---------+-------+------+-----------------------------+
|    1 | SIMPLE      | xwcmmetadatalog | ref  | IX_xwcmmetadatalog_METADATAID | IX_xwcmmetadatalog_METADATAID | 5       | const |    1 | Using where; Using filesort |
+------+-------------+-----------------+------+-------------------------------+-------------------------------+---------+-------+------+-----------------------------+1 row in set (0.00 sec)

我们可以看到该执行计划其实问题也不是很大，唯一需要注意的就是用到了文件排序，当MySQL SERVER io比较吃紧的时候大量执行该SQL就会有问题了，所以我们优化思路就是消除这个文件排序即可。
查看索引

MariaDB [test]> show index from xwcmmetadatalog;
+-----------------+------------+----------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table           | Non_unique | Key_name                         | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| xwcmmetadatalog |          0 | PRIMARY                          |            1 | METADATALOGID | A         |           0 |     NULL | NULL   |      | BTREE      |         |               || xwcmmetadatalog |          1 | IX_xwcmmetadatalog_SRCMETADATAID |            1 | SRCMETADATAID | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               || xwcmmetadatalog |          1 | IX_xwcmmetadatalog_METADATAID    |            1 | METADATAID    | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------+------------+----------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+3 rows in set (0.00 sec)

执行计划用到的索引只包含METADATAID列，而我们的sql筛选条件不仅包含metadataid='4395597' 还用到了opertime来排序，所以我们针对这两个字段建立索引即可。

MariaDB [test]> create index idx_medataid_opertime on xwcmmetadatalog(metadataid,opertime);Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0

再次查看执行计划：

MariaDB [test]> explain select  distinct metadatalogid,opertime from xwcmmetadatalog where metadataid='4395597' order by opertime desc;
+------+-------------+-----------------+------+-----------------------------------------------------+-----------------------+---------+-------+------+--------------------------+| id   | select_type | table           | type | possible_keys                                       | key                   | key_len | ref   | rows | Extra                    |
+------+-------------+-----------------+------+-----------------------------------------------------+-----------------------+---------+-------+------+--------------------------+
|    1 | SIMPLE      | xwcmmetadatalog | ref  | IX_xwcmmetadatalog_METADATAID,idx_medataid_opertime | idx_medataid_opertime | 5       | const |    1 | Using where; Using index |
+------+-------------+-----------------+------+-----------------------------------------------------+-----------------------+---------+-------+------+--------------------------+1 row in set (0.00 sec)

完美

本文由职坐标整理并发布，希望对同学们有所帮助。了解更多详情请关注数据库MySQL频道！