項目中一般使用的都是單表查詢，但是在一些業(yè)務(wù)場景下，偶爾會選擇聯(lián)表查詢，一直對聯(lián)表查詢時如何使用索引一直感到很好奇。正好近期項目中遇到一個問題，聯(lián)表查詢時，沒有建立索引，耗時居然達到了可恥的10分鐘，所以趁機了解了一下。
表數(shù)據(jù)
一共3張表knowledge, knowledge_question, knowledge_answer，數(shù)據(jù)在6000~10000之間。
knowledge： 6126
knowledge_question：9647
knowledge_answer：8267
執(zhí)行的語句：
SELECT DISTINCT(k.base_id) FROM knowledge AS k
LEFT JOIN knowledge_question AS q ON k.id=q.knowledge_id
LEFT JOIN knowledge_answer AS a ON k.id=a.knowledge_id
WHERE k.update_time>'2019-01-01 12:00:00' AND q.update_time>'2019-01-01 12:00:00' AND a.update_time>'2019-01-01 12:00:00'
沒有索引（只有主鍵）
mysql > SELECT DISTINCT(k.base_id) FROM knowledge AS k LEFT JOIN knowledge_question AS q ON k.id=q.knowledge_id LEFT JOIN knowledge_answer AS a ON k.id=a.knowledge_id WHERE k.update_time>'2019-01-01 12:00:00' AND q.update_time>'2019-01-01 12:00:00' AND a.update_time>'2019-01-01 12:00:00';
+---------+
| base_id |
+---------+
| 159 |
...
| 413 |
| 414 |
+---------+
145 rows in set, 3 warnings (9 min 26.57 sec)
執(zhí)行時間約10分鐘，查看執(zhí)行計劃如下：
mysql > explain SELECT DISTINCT(k.base_id) FROM knowledge AS k LEFT JOIN knowledge_question AS q ON k.id=q.knowledge_id LEFT JOIN knowledge_answer AS a ON k.id=a.knowledge_id WHERE k.update_time>'2019-01-01 12:00:00' AND q.update_time>'2019-01-01 12:00:00' AND a.update_time>'2019-01-01 12:00:00';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
| 1 | SIMPLE | k | NULL | ALL | NULL | NULL | NULL | NULL | 6238 | 100.00 | Using temporary |
| 1 | SIMPLE | q | NULL | ALL | NULL | NULL | NULL | NULL | 9540 | 100.00 | Using where; Distinct; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 8410 | 100.00 | Using where; Distinct; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
3 rows in set, 4 warnings (0.00 sec)
全部都是全表掃描，根據(jù)MySQL聯(lián)表查詢的算法Nested-Loop Join，MySQL查詢的結(jié)果集是3張表的笛卡爾積，所以效率特別低。
JOIN字段建立索引
explain SELECT DISTINCT(k.base_id) FROM knowledge AS k LEFT JOIN knowledge_question AS q ON k.id=q.knowledge_id LEFT JOIN knowledge_answer AS a ON k.id=a.knowledge_id WHERE k.update_time>'2019-01-01 12:00:00' AND q.update_time>'2019-01-01 12:00:00' AND a.update_time>'2019-01-01 12:00:00';
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------+------+----------+------------------------------+
| 1 | SIMPLE | k | NULL | ALL | PRIMARY | NULL | NULL | NULL | 6444 | 33.33 | Using where; Using temporary |
| 1 | SIMPLE | a | NULL | ref | idx_kid | idx_kid | 4 | knowledge_base.k.id | 1 | 33.33 | Using where; Distinct |
| 1 | SIMPLE | q | NULL | ref | idx_kid | idx_kid | 4 | knowledge_base.k.id | 1 | 33.33 | Using where; Distinct |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------+------+----------+------------------------------+
執(zhí)行結(jié)果
+---------+
| base_id |
+---------+
| 159 |
...
| 413 |
| 414 |
+---------+
145 rows in set (0.02 sec)
耗時變成20毫秒
Where條件建立索引
給Where條件建立索引，并不一定會使用。
比如：在表knowledge的字段update上建立索引idx_time：
MySQL [knowledge_base]> alter table knowledge add index idx_time(update_time);
MySQL [knowledge_base]> explain SELECT DISTINCT(k.base_id) FROM knowledge AS k LEFT JOIN knowledge_question AS q ON k.id=q.knowledge_id LEFT JOIN knowledge_answer AS a ON k.id=a.knowledge_id WHERE k.update_time>'2019-01-03 12:00:00' AND q.update_time>'2019-01-01 12:00:00' AND a.update_time>'2019-01-01 12:00:00';
+----+-------------+-------+------------+------+------------------+---------+---------+-------------------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+---------+---------+-------------------+------+----------+------------------------------+
| 1 | SIMPLE | k | NULL | ALL | PRIMARY,idx_time | NULL | NULL | NULL | 6444 | 19.01 | Using where; Using temporary |
| 1 | SIMPLE | a | NULL | ref | idx_kid | idx_kid | 4 | knowledge_base.k.id | 1 | 33.33 | Using where; Distinct |
| 1 | SIMPLE | q | NULL | ref | idx_kid | idx_kid | 4 | knowledge_base.k.id | 1 | 33.33 | Using where; Distinct |
+----+-------------+-------+------------+------+------------------+---------+---------+-------------------+------+----------+------------------------------+
結(jié)果執(zhí)行上來看，并沒有使用索引idx_time。
如果where條件從k.update_time>'2019-01-03 12:00:00'修改為k.update_time='2019-01-03 12:00:00'（從>變成=）
MySQL [knowledge_base]> explain SELECT DISTINCT(k.base_id) FROM knowledge AS k LEFT JOIN knowledge_question AS q ON k.id=q.knowledge_id LEFT JOIN knowledge_answer AS a ON k.id=a.knowledge_id WHERE k.update_time='2019-01-03 12:00:00' AND q.update_time>'2019-01-01 12:00:00' AND a.update_time>'2019-01-01 12:00:00';
+----+-------------+-------+------------+------+------------------+----------+---------+-------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+----------+---------+-------------------+------+----------+-----------------------+
| 1 | SIMPLE | k | NULL | ref | PRIMARY,idx_time | idx_time | 4 | const | 1 | 100.00 | Using temporary |
| 1 | SIMPLE | a | NULL | ref | idx_kid | idx_kid | 4 | knowledge_base.k.id | 1 | 33.33 | Using where; Distinct |
| 1 | SIMPLE | q | NULL | ref | idx_kid | idx_kid | 4 | knowledge_base.k.id | 1 | 33.33 | Using where; Distinct |
+----+-------------+-------+------------+------+------------------+----------+---------+-------------------+------+----------+-----------------------+
則會使用索引idx_time
繼續(xù)試驗發(fā)現(xiàn)，如果在
knowledge_question和knowledge_answer表上的字段update_time上建立索引，有時候會較大幅度的改變執(zhí)行計劃。 所以說，檢查SQL語句是否用到索引，一定要用explain查看執(zhí)行計劃，MySQL優(yōu)化器做了太多的工作了。
其他知識點
在建立索引的時候，會遇到Table Metadata Lock的問題，可以先show processlist，找到占用表鎖的連接，然后kill。
MySQL [(none)]> show processlist;
+---------+-----------+----------------------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+-----------+----------------------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 3468722 | Aics_user | 10.219.153.217:46574 | knowledge_base | Query | 94 | Sending data | SELECT DISTINCT(k.base_id) FROM knowledge AS k LEFT JOIN knowledge_question AS q ON k.id=q |
MySQL [(none)]> kill 3468722
結(jié)論
- 關(guān)聯(lián)字段一定要添加索引
- where條件的索引建立，一定要查看explain，mysql的工作方式經(jīng)常跟我們想的不一樣
- 增加慢查詢?nèi)罩荆╠ba呢？）
