mysql优化之profile

工作中要优化sql语句,故需要检测下执行之间,判断哪些地方时间较长

用到profile分析sql性能

首先,show profiles是5.0.37之后添加的，要想使用此功能，要确保版本在5.0.37之后。

1.查看数据库版本

select version();

2.查看是否打开了profiles功能,默认是关闭的

show profiles;

如果显示为空，说明profiles功能是关闭的。下面开启

set profiling=1;

执行下面的查询

SELECT
    l.id lock_id,
    l.lock_kind,
    l.lock_no,
    l.node_id,
    l.power,
    l.comu_status,
    li.region_code,
    li.address,
    li.house_id,
    li.house_code,
    li.room_code,
    li.install_time,
    li. STATUS,
    l.op_no,
    li.curr_owner_id,
    ul.remark_name,
    n.node_no,
    n.comu_status node_comu_status
FROM
    om_lock_install_info li
JOIN ha_lock l ON l.id = li.lock_id
LEFT JOIN lm_user_lock ul ON l.id = ul.lock_id
LEFT JOIN ha_node n ON l.node_id = n.id
WHERE
    (
        ul.auth_time_start IS NULL
        OR ul.auth_time_start <= now()
    )
AND (
    ul.auth_time_end IS NULL
    OR ul.auth_time_end > now()
)
AND ul.to_user_id = '151988dd6c3001'
AND l.lock_kind != '2'
AND (
    l.lock_kind = '0'
    OR l.lock_kind = '3'
)
AND l.power > 40
AND l.power <= 100
AND li.address LIKE '%测试%'
AND l.lock_no LIKE '%2%'
AND li.house_code LIKE '%B%'
AND li.room_code LIKE '%0%'
AND (
    l.power <= 40
    OR (
        (
            l.comu_status = '01'
            OR n.comu_status = '01'
            AND (
                l.lock_kind = '0'
                OR l.lock_kind = '3'
            )
        )
        OR (
            (
                l.comu_status = '01'
                OR l.comu_status IS NULL
            )
            AND l.lock_kind = '1'
        )
    )
)
AND l.node_id = '1550120f2e3001'
ORDER BY
    li.room_code DESC
LIMIT 1824,96

然后执行show profiles

show profiles;

可以看到执行时间为0.120....,query_id为108

3.根据query_id 查看某个查询的详细时间耗费

show profile for query 108;

查看cpu,io等信息

show profile block io,cpu for QUERY 108;

另外还可以看到memory,swaps,context switches,source 等信息

具体信息可以参考http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html