mysql调优三步曲(慢查询、explain profile)(二)
ef
这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
range
这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况
index
这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）
ALL
这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免
再者就是profile
我们可以先使用
mysql> SELECT @@profiling; +-------------+ | @@profiling | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec)来查看是否已经启用profile，如果profilng值为0，可以通过 mysql> SET profiling = 1; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@profiling; +-------------+ | @@profiling | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec)
来启用。启用profiling之后，我们执行一条查询语句，比如：
SELECT * FROM res_user ORDER BY modifiedtimeLIMIT 0,1000 mysql> show profiles; +----------+------------+-------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------------------------------------------+ | 1| 0.00012200 | SELECT @@profiling | | 2| 1.54582000 | SELECT res_id FROM res_user ORDER BY modifiedtime LIMIT 0,3 | +----------+------------+-------------------------------------------------------------+ 2 rows in set (0.00 sec) 注意：Query_ID表示刚执行的查询语句 mysql> show profile for query 2; +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000013 | | checking query cache for query | 0.000035 | | Opening tables | 0.000009 | | System lock | 0.000002 | | Table lock | 0.000015 | | init | 0.000011 | | optimizing | 0.000003 | | statistics | 0.000006 | | preparing | 0.000006 | | executing | 0.000001 | | Sorting result | 1.545565 | | Sending data | 0.000038 | | end | 0.000003 | | query end | 0.000003 | | freeing items | 0.000069 | | storing result in query cache | 0.000004 | | logging slow query | 0.000001 | | logging slow query | 0.000033 | | cleaning up | 0.000003 | +--------------------------------+----------+ 19 rows in set (0.00 sec)
结论：可以看出此条查询语句的执行过程及执行时间，总的时间约为1.545s。这时候我们再执行一次。
mysql> SELECT res_id FROM res_user ORDERBY modifiedtime LIMIT 0,3;
+---------+
| res_id |
+---------+
| 1000305 |
| 1000322 |
| 1000323 |
+---------+
3 rows in set (0.00 sec)
mysql> show profiles;
+----------+------------+-------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------+
| 1 | 0.00012200 | SELECT @@profiling |
| 2 | 1.54582000 | SELECT res_id FROM res_userORDER BY modifiedtime LIMIT 0,3 |
| 3 | 0.00006500 | SELECT res_id FROMres_user ORDER BY modifiedtime LIMIT 0,3 |
+----------+------------+-------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> show profile for query 3;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000013 |
| checking query cache for query | 0.000005|
| checking privileges on cached | 0.000003 |
| sending cac