今天想的利用explain与progiling分析下语句然后进行优化。本文重点是如何通过explain与profiling分析SQL执行过程与性能。进而明白索引的重要性。
表的关系如下所示：
原始的查询SQL:(根据用户的ID查看用户的权限)
SELECT *
FROM permission
WHERE permissionid IN(SELECT
permissionid
FROM rolepermission
WHERE roleid IN(SELECT
roleid
FROM user_role
WHERE userid = 'b33b938faada40aeac2b5ca228336473'))
-----------------------------------不加索引的分析以及测试-----------------------------------------
1.分析
首先将三个表的索引全部去掉 (只剩下主键索引)
查看三个表的索引:
mysql> SHOW INDEX FROM permission\G
*************************** 1. row ***************************
Table: permission
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: permissionID
Collation: A
Cardinality: 50
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec) mysql> SHOW INDEX FROM rolepermission\G
*************************** 1. row ***************************
Table: rolepermission
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: rolePermissionId
Collation: A
Cardinality: 66
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec) mysql> SHOW INDEX FROM user_role\G
*************************** 1. row ***************************
Table: user_role
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: userRoleID
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec) mysql>
1. explain 分析上面SQL的执行计划:
EXPLAIN
SELECT *
FROM permission
WHERE permissionid IN(SELECT
permissionid
FROM rolepermission
WHERE roleid IN(SELECT
roleid
FROM user_role
WHERE userid = 'b33b938faada40aeac2b5ca228336473'))
结果:
分析:
ID越大越先执行，ID相同的按顺序从上向下执行。
查询rolepermission和userrole都是全表扫描(type为All)，且未用到任何索引，只有查询permission用到了主键索引。且看到后连个表的命中率也都比较低。
关于更全的参数解释参考:https://www.cnblogs.com/qlqwjy/p/7767479.html
2.通过profiling分析语句:
0.可以在执行之前清空查询缓存
reset query cache;
1、 开启 profiling 参数
root@localhost : (none) 10:53:11> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
通过执行 “set profiling”命令，可以开启关闭 Query Profiler 功能。
2.执行query:
SELECT *
FROM permission
WHERE permissionid IN(SELECT
permissionid
FROM rolepermission
WHERE roleid IN(SELECT
roleid
FROM user_role
WHERE userid = 'b33b938faada40aeac2b5ca228336473'))
3、获取系统中保存的所有 Query 的 profile 概要信息
mysql> show profiles\G
*************************** 1. row **************************
Query_ID: 1
Duration: 0.01237475
Query: SELECT *
FROM permission
WHERE permissionid IN(SELECT
permissionid
FROM rolepermission
WHERE roleid IN(SELECT
roleid
FROM user_role 1 row in set, 1 warning (0.00 sec)
分析 : 可以看到总共花了 12ms。
通过执行 “SHOW PROFILE” 命令获取当前系统中保存的多个 Query 的 profile 的概要信息。
4、针对单个 Query 获取详细的 profile 信息。
在获取到概要信息之后，我们就可以根据概要信息中的 Query_ID 来获取某个 Query 在执行过程中
详细的 profile 信息了，具体操作如下（1是上面的queryID）
mysql> show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000819 |
| checking permissions | 0.000536 |
| checking permissions | 0.000519 |
| checking permissions | 0.000525 |
| Opening tables | 0.000700 |
| init | 0.000958 |
| System lock | 0.000989 |
| optimizing | 0.000557 |
| statistics | 0.000587 |
| preparing | 0.000519 |
| executing | 0.000492 |
| Sending data | 0.001388 |
| end | 0.000512 |
| query end | 0.000492 |
| removing tmp table | 0.000492 |
| query end | 0.000479 |
| closing tables | 0.000517 |
| freeing items | 0.000720 |
| cleaning up | 0.000577 |
+----------------------+----------+
19 rows in set, 1 warning (0.00 sec)
如果想查看cpu,io等信息更具体的信息可以:
mysql> show profile cpu, block io for query 1;
+----------------------+----------+----------+------------+--------------+-----
---------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Bloc
_ops_out |
+----------------------+----------+----------+------------+--------------+-----
---------+
| starting | 0.000819 | 0.000000 | 0.000000 | NULL |
NULL |
| checking permissions | 0.000536 | 0.000000 | 0.000000 | NULL |
NULL |
| checking permissions | 0.000519 | 0.000000 | 0.000000 | NULL |
NULL |
| checking permissions | 0.000525 | 0.000000 | 0.000000 | NULL |
NULL |
| Opening tables | 0.000700 | 0.000000 | 0.000000 | NULL |
NULL |
| init | 0.000958 | 0.000000 | 0.000000 | NULL |
NULL |
| System lock | 0.000989 | 0.000000 | 0.000000 | NULL |
NULL |
| optimizing | 0.000557 | 0.000000 | 0.000000 | NULL |
NULL |
| statistics | 0.000587 | 0.000000 | 0.000000 | NULL |
NULL |
| preparing | 0.000519 | 0.000000 | 0.000000 | NULL |
NULL |
| executing | 0.000492 | 0.000000 | 0.000000 | NULL |
NULL |
| Sending data | 0.001388 | 0.000000 | 0.000000 | NULL |
NULL |
| end | 0.000512 | 0.000000 | 0.000000 | NULL |
NULL |
| query end | 0.000492 | 0.000000 | 0.000000 | NULL |
NULL |
| removing tmp table | 0.000492 | 0.000000 | 0.015625 | NULL |
NULL |
| query end | 0.000479 | 0.000000 | 0.000000 | NULL |
NULL |
| closing tables | 0.000517 | 0.000000 | 0.000000 | NULL |
NULL |
| freeing items | 0.000720 | 0.000000 | 0.000000 | NULL |
NULL |
| cleaning up | 0.000577 | 0.000000 | 0.000000 | NULL |
NULL |
+----------------------+----------+----------+------------+--------------+-----
---------+
2.测试修改SQL
将上述子查询SQL改为内连接查询:
SELECT *
FROM permission
INNER JOIN rolepermission
ON permission.permissionID = rolepermission.permissionId
INNER JOIN user_role
ON rolepermission.roleId = user_role.roleID
WHERE user_role.userID = 'b33b938faada40aeac2b5ca228336473'
1.explain分析:
分析:
EXPLAIN
SELECT *
FROM permission
INNER JOIN rolepermission
ON permission.permissionID = rolepermission.permissionId
INNER JOIN user_role
ON rolepermission.roleId = user_role.roleID
WHERE user_role.userID = 'b33b938faada40aeac2b5ca228336473'
结果:
分析:上面的子查询为四次查询，变为内连接查询为3此，且前两个未加索引，最后用到了主键索引。
2.通过profilies查看查询效率:
mysql> show profiles\G
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.02015850
Query: SELECT *
FROM permission
WHERE permissionid IN(SELECT
permissionid
FROM rolepermission
WHERE roleid IN(SELECT
roleid
FROM user_role *************************** 2. row ***************************
Query_ID: 2
Duration: 0.01072575
Query: EXPLAIN
SELECT *
FROM permission
INNER JOIN rolepermission
ON permission.permissionID = rolepermission.permissionId
INNER JOIN user_role
ON rolepermission.roleId = user_role.roleID
WHERE user_role.userID = 'b33b938faada40aeac2b5ca228336473'
2 rows in set, 1 warning (0.00 sec)
发现变为内连接查询所花费的时间变为原来的一班，原来20ms，现在10ms。
多查询几次，查看效率:
mysql> show profiles\G
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.01448950
Query: SELECT *
FROM permission
WHERE permissionid IN(SELECT
permissionid
FROM rolepermission
WHERE roleid IN(SELECT
roleid
FROM user_role *************************** 2. row ***************************
Query_ID: 2
Duration: 0.01417150
Query: SELECT *
FROM permission
WHERE permissionid IN(SELECT
permissionid
FROM rolepermission
WHERE roleid IN(SELECT
roleid
FROM user_role *************************** 3. row ***************************
Query_ID: 3
Duration: 0.01466100
Query: SELECT *
FROM permission
WHERE permissionid IN(SELECT
permissionid
FROM rolepermission
WHERE roleid IN(SELECT
roleid
FROM user_role *************************** 4. row ***************************
Query_ID: 4
Duration: 0.01308100
Query: SELECT *
FROM permission
inner join rolepermission
on permission.permissionID = rolepermission.permissionId
INNER join user_role
on rolepermission.roleId = user_role.roleID
where user_role.userID = 'a9e65788297e4a8cb68a369522ee5af7'
*************************** 5. row ***************************
Query_ID: 5
Duration: 0.01334675
Query: SELECT *
FROM permission
inner join rolepermission
on permission.permissionID = rolepermission.permissionId
INNER join user_role
on rolepermission.roleId = user_role.roleID
where user_role.userID = 'a9e65788297e4a8cb68a369522ee5af7'
*************************** 6. row ***************************
Query_ID: 6
Duration: 0.01289250
Query: SELECT *
FROM permission
inner join rolepermission
on permission.permissionID = rolepermission.permissionId
INNER join user_role
on rolepermission.roleId = user_role.roleID
where user_role.userID = 'a9e65788297e4a8cb68a369522ee5af7'
6 rows in set, 1 warning (0.00 sec)
前三条是子查询，后3条是内连接，发现内连接稍微快于子查询。
-----------------------------------加索引的分析以及测试-----------------------------------------
0.在上面的表上加上索引
mysql> alter table user_role add index roleIdIndex(roleId); #添加索引
Query OK, 0 rows affected (0.59 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table user_role add index userIdIndex(userId);
Query OK, 0 rows affected (0.53 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table rolepermission add index per_roleIdIndex(roleId);
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table rolepermission add index per_perssionIdIndex(permissionId);
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from user_role\G #查看索引
*************************** 1. row ***************************
Table: user_role
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: userRoleID
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: user_role
Non_unique: 1
Key_name: roleIdIndex
Seq_in_index: 1
Column_name: roleID
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: user_role
Non_unique: 1
Key_name: userIdIndex
Seq_in_index: 1
Column_name: userID
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.01 sec) mysql> show index from rolepermission\G #查看索引
*************************** 1. row ***************************
Table: rolepermission
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: rolePermissionId
Collation: A
Cardinality: 66
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: rolepermission
Non_unique: 1
Key_name: per_roleIdIndex
Seq_in_index: 1
Column_name: roleId
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: rolepermission
Non_unique: 1
Key_name: per_perssionIdIndex
Seq_in_index: 1
Column_name: permissionId
Collation: A
Cardinality: 52
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.01 sec)
1.针对子查询进行分析
1.explain分析:
EXPLAIN
SELECT *
FROM permission
WHERE permissionid IN(SELECT
permissionid
FROM rolepermission
WHERE roleid IN(SELECT
roleid
FROM user_role
WHERE userid = 'a9e65788297e4a8cb68a369522ee5af7'))
结果:
发现加上索引之后查询效果明显提升，三个表到用到索引，且命中率为100%。
2.profile分析:
mysql> show profiles\G*************************** 2. row ***************************
Query_ID: 2
Duration: 0.01505400
Query: SELECT *
FROM permission
WHERE permissionid IN(SELECT
permissionid
FROM rolepermission
WHERE roleid IN(SELECT
roleid
FROM user_role *************************** 3. row ***************************
Query_ID: 3
Duration: 0.01624350
Query: SELECT *
FROM permission
WHERE permissionid IN(SELECT
permissionid
FROM rolepermission
WHERE roleid IN(SELECT
roleid
FROM user_role 3 rows in set, 1 warning (0.00 sec) mysql>
数据量小没有多大影响。
2.针对显示内连接进行分析
1.explain分析
EXPLAIN
SELECT *
FROM permission
INNER JOIN rolepermission
ON permission.permissionID = rolepermission.permissionId
INNER JOIN user_role
ON rolepermission.roleId = user_role.roleID
WHERE user_role.userID = 'a9e65788297e4a8cb68a369522ee5af7'
结果:
2.show profile分析
*************************** 4. row ***************************
Query_ID: 4
Duration: 0.01447100
Query: SELECT *
FROM permission
INNER JOIN rolepermission
ON permission.permissionID = rolepermission.permissionId
INNER JOIN user_role
ON rolepermission.roleId = user_role.roleID
WHERE user_role.userID = 'a9e65788297e4a8cb68a369522ee5af7'
*************************** 5. row ***************************
Query_ID: 5
Duration: 0.01502600
Query: SELECT *
FROM permission
INNER JOIN rolepermission
ON permission.permissionID = rolepermission.permissionId
INNER JOIN user_role
ON rolepermission.roleId = user_role.roleID
WHERE user_role.userID = 'a9e65788297e4a8cb68a369522ee5af7'
*************************** 6. row ***************************
Query_ID: 6
Duration: 0.01471300
Query: SELECT *
FROM permission
INNER JOIN rolepermission
ON permission.permissionID = rolepermission.permissionId
INNER JOIN user_role
ON rolepermission.roleId = user_role.roleID
WHERE user_role.userID = 'a9e65788297e4a8cb68a369522ee5af7'
6 rows in set, 1 warning (0.00 sec)