EXPLAIN列详情
详细解读:https://www.cnblogs.com/yycc/p/7338894.html
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
使用方法,在select语句前加上explain就可以了:
如:explain select surname,first_name form a,b where a.id=b.id
Type列详解
Extra详解
MySQL高级命令
强制索引MySQL FORCE INDEX
SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …
忽略索引 IGNORE INDEX
SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …
关闭查询缓冲 SQL_NO_CACHE
SELECT SQL_NO_CACHE field1, field2 FROM TABLE1;
MySQL force Index 强制索引:强制查询缓冲 SQL_CACHE
SELECT SQL_CALHE * FROM TABLE1;
优先操作 HIGH_PRIORITY
SELECT HIGH_PRIORITY * FROM TABLE1;
滞后操作 LOW_PRIORITY
update LOW_PRIORITY table1 set field1= where field1= …
延时插入 INSERT DELAYED
INSERT DELAYED INTO table1 set field1= …
强制连接顺序 STRAIGHT_JOIN
SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …
MySQL force Index 强制索引:强制使用临时表 SQL_BUFFER_RESULT
SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …
分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT
SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1;