数据库优化中的explain
explain作用:
是在执行某条SQL(复杂的)与之前,采用explain先测试一下这个SQL语句的性能如何,可以从是否使用索引,主从表格等方面来评估你的SQL语句质量。explain执行计划的结果:
- 如果是复杂查询,在select_type中只有一个primary,但是会有多个 derived(派生类);
- 其中,derived导出类是针对原表的;
- 在inner join操作中,from后面的是驱动表,order By驱动表的字段,才会按照索引排序,否则会在extra中显示using temporary(使用临时表)
- derived对应的id是在数据树结构中id序号,并不能代表实际的执行顺序;
- 虽然,有时候你用了索引,也是按照驱动表的索引字段进行排序的,但是有可能数据库强制给你优化成没有按照索引字段进行排序等情况,这个时候可以通过explain extended命令进行查询,查看被强制优化之后的代码。
- 还存在一些情况,就是单个查询的性能非常好,一旦好几个进程一起执行查询计划之后,就非常慢。这是因为索引是存放在磁盘中的,当查询来的时候,先是从内存中读取索引,如果有就直接取出来;否则需要从磁盘中调取索引信息,这时候会存在分页的可能性。那么,索引的数据很多的时候,就会非常的慢了。
explain执行计划例子
- 执行语句
USE labor;
EXPLAIN
SELECT DISTINCT W.F_IDENTIFICATION, W.F_ID,W.F_NAME,W.F_PHOTO,W.F_PLACE_NAME AS F_NATIVE_PLACE,W.F_GENDER AS F_SEX,W.F_QR_CODE
FROM
(SELECT F_IDENTIFICATION, F_ID, F_NAME, F_PHOTO, F_PLACE_NAME, F_GENDER, F_QR_CODE
FROM T_WORKER
WHERE T_WORKER.F_TENANT_ID = 1
AND (F_MODIFY_TIME >= '2015-06-01' AND F_MODIFY_TIME <= '2017-07-03')) W
INNER JOIN (SELECT F_WORKER_ID
FROM T_PROJECT_WORKER
WHERE T_PROJECT_WORKER.F_TENANT_ID = 1 AND F_PROJECT_ID = 1011) PW
ON W.F_ID = PW.F_WORKER_ID
WHERE W.F_ID > 10
ORDER BY W.F_ID
LIMIT 0, 400
结果分析:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | PRIMARY | derived3 | ALL | NULL | NULL | NULL | NULL | 994 | Using where; Using temporary; Using filesort |
2 | PRIMARY | derived2 | ref | auto_key0 | auto_key0 | 8 | PW.F_WORKER_ID | 19 | NULL |
3 | DERIVED | T_PROJECT_WORKER | ref | idxProjectWorker_Vendor, idxProjectWorker_Deleted, idxProjectWorker_ApproachDate, idxProjectWorker_ExitDate, idxProjectWorker_TypeName, idxProjectWorker_TenantTeam, idxProjectWorker_TenantGroup, idxProjectWorker_TenantWorker, idxProjectWorker_Project, idxProjectWorker_CardMade, idxProjectWorker_CardMadeExit, idxProjectWorker_TypeCode, idxProjectWorker_CardID, idxProjectWorker_Tenant, idxProjectWorker_ProjectTeam | idxProjectWorker_TypeName | 16 | const,const | 994 | NULL |
2 | DERIVED | T_WORKER | ref | uidxWorker_ID,idxWorker_Tenant | idxWorker_Tenant | 8 | const | 19470 | Using where |
- 执行语句优化
EXPLAIN
SELECT F_IDENTIFICATION, W.F_ID, F_NAME, F_PHOTO, F_PLACE_NAME AS F_NATIVE_PLACE, F_GENDER AS F_SEX, F_QR_CODE
FROM T_PROJECT_WORKER PW
INNER JOIN T_WORKER W ON W.F_ID = PW.F_WORKER_ID
WHERE PW.F_TENANT_ID = 1
AND PW.F_PROJECT_ID = 1011
AND W.F_MODIFY_TIME >= '2015-06-01' AND W.F_MODIFY_TIME <= '2017-07-03'
AND W.F_ID > 10
ORDER BY PW.F_WORKER_ID
LIMIT 0, 400
- explain执行结果
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | PW | ref | idxProjectWorker_Vendor, idxProjectWorker_Deleted, idxProjectWorker_ApproachDate, idxProjectWorker_ExitDate, idxProjectWorker_TypeName, idxProjectWorker_TenantTeam, idxProjectWorker_TenantGroup, idxProjectWorker_TenantWorker, idxProjectWorker_Project, idxProjectWorker_Worker, idxProjectWorker_CardMade, idxProjectWorker_CardMadeExit, idxProjectWorker_TypeCode, idxProjectWorker_CardID, idxProjectWorker_Tenant, idxProjectWorker_ProjectTeam | idxProjectWorker_TypeName | 16 | const,const | 994 | Using where; Using filesort |
2 | SIMPLE | W | eq_ref | PRIMARY | PRIMARY | 8 | labor.PW.F_WORKER_ID | 1 | Using where |
可以明显看到优化之后的语句查询的行数大大减少,而且都是simple类型的查询。
**. 推荐一款好用的数据库连接工具SQLyog
它相对Navicate来说,更加的方便,具有执行耗时,多种视图展现形式。