优化案例

优化案例




欢迎进入SQL Command，请从底部输入框内输入要执行的SQL，按Ctrl+Enter运行.
=====================================================================================

mysql>EXPLAIN SELECT a.id AS account_id, a.sid AS account_sid, c.sid AS corp_sid, account_name, corp_name
, phone, email, is_main, a.is_use, account_status
, account_type, last_login_time, a.gmt_create
, (
SELECT GROUP_CONCAT(CONCAT(g.group_name, '(', gt.group_type_name, ')'))
FROM user_account_group ag
LEFT JOIN user_group g ON ag.group_sid = g.sid
LEFT JOIN user_group_type gt ON gt.sid = g.group_type_sid
WHERE account_sid = a.parent_sid
GROUP BY account_sid
) AS groupNames
FROM user_account a
LEFT JOIN user_corp c ON a.parent_sid = c.account_sid
ORDER BY a.id DESC
LIMIT 510760, 10
+--------------+-----------------------+-----------------+----------------------+----------------+----------------------------------------+-------------------------------+-------------------+-----------------------------+----------------+--------------------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+--------------+-----------------------+-----------------+----------------------+----------------+----------------------------------------+-------------------------------+-------------------+-----------------------------+----------------+--------------------+----------------------------------------------------+
| 1 | PRIMARY | a | | index | | PRIMARY | 4 | | 507607 | 100 | Backward index scan |
| 1 | PRIMARY | c | | eq_ref | uk_account_sid,idx_account_sid_perfect | uk_account_sid | 8 | zs_user_system.a.parent_sid | 1 | 100 | |
| 2 | DEPENDENT SUBQUERY | ag | | ref | uk_account_sid_group_type_sid | uk_account_sid_group_type_sid | 8 | zs_user_system.a.parent_sid | 1 | 100 | Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | g | | ref | uk_sid_group_type_sid | uk_sid_group_type_sid | 8 | zs_user_system.ag.group_sid | 1 | 100 | |
| 2 | DEPENDENT SUBQUERY | gt | | ALL | uk_sid | | | | 2 | 100 | Using where; Using join buffer (Block Nested Loop) |
+--------------+-----------------------+-----------------+----------------------+----------------+----------------------------------------+-------------------------------+-------------------+-----------------------------+----------------+--------------------+----------------------------------------------------+
返回行数：[5]，耗时：6 ms.
mysql>EXPLAIN SELECT a.id AS account_id, a.sid AS account_sid, c.sid AS corp_sid, account_name, corp_name
, phone, email, is_main, a.is_use, account_status
, account_type, last_login_time, a.gmt_create
, (
SELECT GROUP_CONCAT(CONCAT(g.group_name, '(', gt.group_type_name, ')'))
FROM user_account_group ag
LEFT JOIN user_group g ON ag.group_sid = g.sid
LEFT JOIN user_group_type gt ON gt.sid = g.group_type_sid
WHERE account_sid = a.parent_sid
GROUP BY account_sid
) AS groupNames
FROM user_account a
JOIN (
SELECT id
FROM user_account
ORDER BY id DESC
LIMIT 510760, 10
) tmp
ON tmp.id = a.id
LEFT JOIN user_corp c ON a.parent_sid = c.account_sid
ORDER BY a.id DESC
+--------------+-----------------------+-----------------+----------------------+----------------+----------------------------------------+-------------------------------+-------------------+-----------------------------+----------------+--------------------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+--------------+-----------------------+-----------------+----------------------+----------------+----------------------------------------+-------------------------------+-------------------+-----------------------------+----------------+--------------------+----------------------------------------------------+
| 1 | PRIMARY | <derived3> | | ALL | | | | | 507607 | 100 | Using temporary; Using filesort |
| 1 | PRIMARY | a | | eq_ref | PRIMARY | PRIMARY | 4 | tmp.id | 1 | 100 | |
| 1 | PRIMARY | c | | eq_ref | uk_account_sid,idx_account_sid_perfect | uk_account_sid | 8 | zs_user_system.a.parent_sid | 1 | 100 | |
| 3 | DERIVED | user_account | | index | | PRIMARY | 4 | | 507607 | 100 | Backward index scan; Using index |
| 2 | DEPENDENT SUBQUERY | ag | | ref | uk_account_sid_group_type_sid | uk_account_sid_group_type_sid | 8 | zs_user_system.a.parent_sid | 1 | 100 | Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | g | | ref | uk_sid_group_type_sid | uk_sid_group_type_sid | 8 | zs_user_system.ag.group_sid | 1 | 100 | |
| 2 | DEPENDENT SUBQUERY | gt | | ALL | uk_sid | | | | 2 | 100 | Using where; Using join buffer (Block Nested Loop) |
+--------------+-----------------------+-----------------+----------------------+----------------+----------------------------------------+-------------------------------+-------------------+-----------------------------+----------------+--------------------+----------------------------------------------------+
返回行数：[7]，耗时：6 ms.
mysql>SELECT a.id AS account_id, a.sid AS account_sid, c.sid AS corp_sid, account_name, corp_name
, phone, email, is_main, a.is_use, account_status
, account_type, last_login_time, a.gmt_create
, (
SELECT GROUP_CONCAT(CONCAT(g.group_name, '(', gt.group_type_name, ')'))
FROM user_account_group ag
LEFT JOIN user_group g ON ag.group_sid = g.sid
LEFT JOIN user_group_type gt ON gt.sid = g.group_type_sid
WHERE account_sid = a.parent_sid
GROUP BY account_sid
) AS groupNames
FROM user_account a
JOIN (
SELECT id
FROM user_account
ORDER BY id DESC
LIMIT 510760, 10
) tmp
ON tmp.id = a.id
LEFT JOIN user_corp c ON a.parent_sid = c.account_sid
ORDER BY a.id DESC




返回行数：[10]，耗时：155 ms.
mysql>SELECT a.id AS account_id, a.sid AS account_sid, c.sid AS corp_sid, account_name, corp_name
, phone, email, is_main, a.is_use, account_status
, account_type, last_login_time, a.gmt_create
, (
SELECT GROUP_CONCAT(CONCAT(g.group_name, '(', gt.group_type_name, ')'))
FROM user_account_group ag
LEFT JOIN user_group g ON ag.group_sid = g.sid
LEFT JOIN user_group_type gt ON gt.sid = g.group_type_sid
WHERE account_sid = a.parent_sid
GROUP BY account_sid
) AS groupNames
FROM user_account a
LEFT JOIN user_corp c ON a.parent_sid = c.account_sid
ORDER BY a.id DESC
LIMIT 510760, 10




返回行数：[10]，耗时：1697 ms.
mysql>EXPLAIN SELECT dl.id, dl.sid, column_sid AS columnSid, cl.column_name AS columnName, title
, short_title AS shortTitle, user_editor AS userEditor, publish_date AS publishDate, property
, CASE property
WHEN 1 THEN '推荐'
WHEN 0 THEN ''
END AS propertyName, info_pv AS infoPv, dl.sort, cl.sort AS columnSort, is_delete AS isDelete
, dl.is_push AS isPush
, CASE dl.is_push
WHEN 1 THEN '开启'
WHEN 0 THEN '关闭'
END AS propertyName, dl.info_flag_name AS infoFlagName, dl.info_flag_alias AS infoFlagAlias, dl.delete_date AS deleteDate, dl.gmt_modified AS gmtModified
, dl.is_original AS isOriginal, dl.recommend_image_uri AS recommendImageUri, dl.article_source AS articleSource
FROM info_detail dl
INNER JOIN info_column cl ON dl.column_sid = cl.sid
WHERE title LIKE concat('%', '迈图', '%')
AND is_delete = 0
ORDER BY dl.publish_date DESC
LIMIT 10
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+------------------+-------------------+-----------------------+----------------+--------------------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+------------------+-------------------+-----------------------+----------------+--------------------+----------------------------------+
| 1 | SIMPLE | dl | | index | idx_column_sid | idx_publish_date | 5 | | 10 | 1.11 | Using where; Backward index scan |
| 1 | SIMPLE | cl | | eq_ref | uk_sid | uk_sid | 8 | zs_info.dl.column_sid | 1 | 100 | |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+------------------+-------------------+-----------------------+----------------+--------------------+----------------------------------+
返回行数：[2]，耗时：5 ms.
mysql>EXPLAIN SELECT dl.id, dl.sid, column_sid AS columnSid, cl.column_name AS columnName, title
, short_title AS shortTitle, user_editor AS userEditor, publish_date AS publishDate, property
, CASE property
WHEN 1 THEN '推荐'
WHEN 0 THEN ''
END AS propertyName, info_pv AS infoPv, dl.sort, cl.sort AS columnSort, is_delete AS isDelete
, dl.is_push AS isPush
, CASE dl.is_push
WHEN 1 THEN '开启'
WHEN 0 THEN '关闭'
END AS propertyName, dl.info_flag_name AS infoFlagName, dl.info_flag_alias AS infoFlagAlias, dl.delete_date AS deleteDate, dl.gmt_modified AS gmtModified
, dl.is_original AS isOriginal, dl.recommend_image_uri AS recommendImageUri, dl.article_source AS articleSource
FROM info_detail dl
INNER JOIN info_column cl ON dl.column_sid = cl.sid
WHERE title LIKE concat('迈图', '%')
AND is_delete = 0
ORDER BY dl.publish_date DESC
LIMIT 10
+--------------+-----------------------+-----------------+----------------------+----------------+--------------------------+---------------+-------------------+-----------------------+----------------+--------------------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+--------------+-----------------------+-----------------+----------------------+----------------+--------------------------+---------------+-------------------+-----------------------+----------------+--------------------+----------------------------------------------------+
| 1 | SIMPLE | dl | | range | idx_column_sid,idx_title | idx_title | 770 | | 4 | 10 | Using index condition; Using where; Using filesort |
| 1 | SIMPLE | cl | | eq_ref | uk_sid | uk_sid | 8 | zs_info.dl.column_sid | 1 | 100 | |
+--------------+-----------------------+-----------------+----------------------+----------------+--------------------------+---------------+-------------------+-----------------------+----------------+--------------------+----------------------------------------------------+
返回行数：[2]，耗时：6 ms.
mysql>SELECT dl.id, dl.sid, column_sid AS columnSid, cl.column_name AS columnName, title
, short_title AS shortTitle, user_editor AS userEditor, publish_date AS publishDate, property
, CASE property
WHEN 1 THEN '推荐'
WHEN 0 THEN ''
END AS propertyName, info_pv AS infoPv, dl.sort, cl.sort AS columnSort, is_delete AS isDelete
, dl.is_push AS isPush
, CASE dl.is_push
WHEN 1 THEN '开启'
WHEN 0 THEN '关闭'
END AS propertyName, dl.info_flag_name AS infoFlagName, dl.info_flag_alias AS infoFlagAlias, dl.delete_date AS deleteDate, dl.gmt_modified AS gmtModified
, dl.is_original AS isOriginal, dl.recommend_image_uri AS recommendImageUri, dl.article_source AS articleSource
FROM info_detail dl
INNER JOIN info_column cl ON dl.column_sid = cl.sid
WHERE title LIKE concat('迈图', '%')
AND is_delete = 0
ORDER BY dl.publish_date DESC
LIMIT 10




返回行数：[4]，耗时：5 ms.
mysql>SELECT dl.id, dl.sid, column_sid AS columnSid, cl.column_name AS columnName, title
, short_title AS shortTitle, user_editor AS userEditor, publish_date AS publishDate, property
, CASE property
WHEN 1 THEN '推荐'
WHEN 0 THEN ''
END AS propertyName, info_pv AS infoPv, dl.sort, cl.sort AS columnSort, is_delete AS isDelete
, dl.is_push AS isPush
, CASE dl.is_push
WHEN 1 THEN '开启'
WHEN 0 THEN '关闭'
END AS propertyName, dl.info_flag_name AS infoFlagName, dl.info_flag_alias AS infoFlagAlias, dl.delete_date AS deleteDate, dl.gmt_modified AS gmtModified
, dl.is_original AS isOriginal, dl.recommend_image_uri AS recommendImageUri, dl.article_source AS articleSource
FROM info_detail dl
JOIN (
SELECT id
FROM info_detail
WHERE title LIKE concat('%', '迈图', '%')
AND is_delete = 0
ORDER BY id DESC
LIMIT 10
) tmp
ON tmp.id = dl.id
INNER JOIN info_column cl ON dl.column_sid = cl.sid




返回行数：[4]，耗时：574 ms.
mysql>SELECT dl.id, dl.sid, column_sid AS columnSid, cl.column_name AS columnName, title
, short_title AS shortTitle, user_editor AS userEditor, publish_date AS publishDate, property
, CASE property
WHEN 1 THEN '推荐'
WHEN 0 THEN ''
END AS propertyName, info_pv AS infoPv, dl.sort, cl.sort AS columnSort, is_delete AS isDelete
, dl.is_push AS isPush
, CASE dl.is_push
WHEN 1 THEN '开启'
WHEN 0 THEN '关闭'
END AS propertyName, dl.info_flag_name AS infoFlagName, dl.info_flag_alias AS infoFlagAlias, dl.delete_date AS deleteDate, dl.gmt_modified AS gmtModified
, dl.is_original AS isOriginal, dl.recommend_image_uri AS recommendImageUri, dl.article_source AS articleSource
FROM info_detail dl
INNER JOIN info_column cl ON dl.column_sid = cl.sid
WHERE title LIKE concat('%', '迈图', '%')
AND is_delete = 0
ORDER BY dl.publish_date DESC
LIMIT 10



返回行数：[4]，耗时：1835 ms.