mysql - 如何使用索引表 MariaDB 改进慢查询?
问题描述
此查询平均需要 2/3 秒。连接中的每个字段以及索引在哪里。
如何改进此查询?
SELECT DISTINCT pro_id, pro_url, pro_data, pro_capa, pro_destaque, pro_destaque_data, pro_nome, pro_likes, pro_views, pro_comentarios_total, pro_autor FROM projeto JOIN utilizador_projeto ON pro_id=utp_proid JOIN utilizador ON utp_utiid=uti_id WHERE pro_activo=1 AND pro_privacidade=1 ORDER BY pro_destaque_data DESC LIMIT 24;
慢查询日志:
# Time: 190923 1:10:58
# User@Host: root[root] @ [10.133.247.241]
# Thread_id: 36 Schema: db QC_hit: No
# Query_time: 3.575462 Lock_time: 0.000114 Rows_sent: 24 Rows_examined: 104820
# Rows_affected: 0 Bytes_sent: 3974
MariaDB [db]> EXPLAIN SELECT DISTINCT pro_id, pro_url, pro_data, pro_capa, pro_destaque, pro_destaque_data, pro_nome, pro_likes, pro_views, pro_comentarios_total, pro_autor FROM projeto JOIN utilizador_projeto ON pro_id=utp_proid JOIN utilizador ON utp_utiid=uti_id WHERE pro_activo=1 AND pro_privacidade=1 ORDER BY pro_destaque_data DESC LIMIT 24;
+------+-------------+--------------------+--------+-----------------------------------------------------------------------------------------------+---------------------+---------+---------------------------------+-------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------+--------+-----------------------------------------------------------------------------------------------+---------------------+---------+---------------------------------+-------+------------------------------+
| 1 | SIMPLE | projeto | ref | PRIMARY,pro_destaques_index,pro_likes_index,pro_comments_index,pro_views_index,pro_date_index | pro_destaques_index | 2 | const,const | 17865 | Using where; Using temporary |
| 1 | SIMPLE | utilizador_projeto | ref | utp_utiid,utp_proid | utp_proid | 4 | db.projeto.pro_id | 1 | Distinct |
| 1 | SIMPLE | utilizador | eq_ref | PRIMARY | PRIMARY | 4 | db.utilizador_projeto.utp_utiid | 1 | Using index; Distinct |
+------+-------------+--------------------+--------+-----------------------------------------------------------------------------------------------+---------------------+---------+---------------------------------+-------+------------------------------+
解决方案
WHERE pro_active=1
AND pro_privacy=1
ORDER BY pro_highlight_date DESC
求这个“复合”索引:
INDEX(pro_active, pro_privacy, -- in either order
pro_highlight_date)
大概Rows_examined: 139482
会降到Rows_examined: 24
。
假设这 3 列在同一个表中。不要使用表名作为列名的前缀,而是执行以下操作:
...
FROM project AS pro
...
WHERE pro.active = 1 ...
这消除了任何歧义(并且需要大约相同数量的击键)。
连接中的每个字段以及索引在哪里
矫枉过正。而且单列索引通常不如多列(复合)索引有用。见:http: //mysql.rjweb.org/doc.php/index_cookbook_mysql
推荐阅读
- html - 无法在移动设备上看到完整的弹出窗口
- amazon-web-services - AWS Auto Scaling 数据库
- vert.x - `org.bson.codecs.configuration.CodecConfigurationException` 从 vertx 写入 MongoDB
- python - 如何使用美丽的汤保存页面中的所有图像?
- php - 计算数组中特定数字的值并返回出现次数
- java - switch语句上的“需要常量表达式”?
- java - 在将数据从 firebase 获取到 string[] 数组时需要帮助。尝试了所有可用的方法,但坚持只获得最后一个值
- c# - 在 C# 中从 CSV 读取不同的值到 Datagridview
- javascript - 当我尝试在模拟器上运行时,React Native 失败
- php - SOAP 调用后获取 XML 字段