mysql sql优化
-- sql执行频率 (Com_select 频率最高)
SHOW GLOBAL STATUS LIKE 'Com_______'
-- 慢查询日志
-- OFF 关闭 ON打开
-- etc/mysql.conf
-- slow_query_log=1
-- long_query_time=2
SHOW VARIABLES LIKE 'slow_query_log'
-- show profiles
-- YES 支持profiles
SELECT @@have_profiling;
-- 打开、关闭
SELECT @@profiling;
-- set
SET profiling=1;
SET profiling=0;
-- 查看所有执行的sql
-- query_id duration query
-- duration代表耗时
SHOW PROFILES;
-- 查看各个阶段的耗时
SHOW PROFILE FOR QUERY 2
SHOW PROFILE CPU FOR QUERY 2
-- 查看执行计划
-- explain
EXPLAIN SELECT * FROM `business_hospitalization` WHERE serial_number='012306150001'
-- type : null system const eq_ref ref index all 最差：all，走唯一索引：const，非唯一索引：ref
-- possible_key key
-- rows
-- extra using where using templary table
EXPLAIN
SELECT * FROM `business_hospitalization` a JOIN `business_hospitalization_advice` b
ON a.id=b.bus_hospitalization_id
WHERE serial_number='012306150001'