高性能 MySql 语句小记

高性能 MySql 语句小记

--oracle 是从 1 开始
--mysql 初始偏移量是 0，即是从 0 开始不是从 1 开始
--6-15
limit 5,10
limit 10 offset 5

--事务启动
start transaction
--more sql
commit;

--自动提交，1 or ON 启用，0 or OFF 禁用
show variables like 'AUTOCOMMIT';

--修改隔离级别
set session transaction isolation level read commited;

--显示锁定
select ... lock in share mode;
select ... for update;

--显示表信息
show table status like 't_user';

--检查表错误和修复
CHECK TABLE mytable;
REPAIR TABLE mytable;

--转换表存储引擎
ALTER TABLE mytable ENGINE=InnoDB;

--转换表存储引擎
create table innodb_table like myisam_table;
alter table innodb_table engine=InnoDb;
insert into innodb_table select * from myisam_table;

--慢查询日志开始

show variables like 'long_query_time';
show variables like 'slow_query%';

--82-78页
SHOW FULL PROCESSLIST;

--剖析单条查询：show profile
set profiling=1;
select ... from mytable;
show profile;
show profile for query 1;--比较粗
select state,sum(duration) as total_r,
  round(
    100*sum(duration)/
      (select sum(duration)
      from information_schema.profiling
      where query_id=1
    ),2
  ) as pct_r,
  count(*) as calls,
  sum(duration)/count(*) as 'r/call'
from information_schema.profiling
where query_id=1
group by state
order by total_r desc;--比较细

--剖析单条查询：show status（大部分结果都只是一个计数器）
--（本身也会创建一个临时表，临时表计数最少多 2）
show status;
show global status;
--下面是一组
flush status;
select * from a;
show status where variable_name like 'Handler%' or variable_name like 'Created%';

--EXPLAIN（估计得到多结果，无法告知临时表是否是磁盘表）

--performance_schema（有限制）
select event_name,count_star,sum_timer_wait
from performance_schema.events_waits_summary_global_by_event_name
order by sum_timer_wait desc limit 5;

show global status where variable_name in('Queries','Threads_connected','Threads_running','Questions');

show innodb status;
--慢查询日志结束

--主键已存在则更新
ON DUPLICATE KEY UPDATE

--禁用（非唯一）索引
alter table mytable disable keys;

--检查表损坏
check table mytable;--部分引擎支持
--修复损坏的表
repair table mytable;--部分引擎支持
alter table innodb_tb1 ENGINE=INNODB;--所有引擎支持

--更新优化器统计信息
analyze table mytable;

--查看基数（cardinality）
show index from mytable;--会锁表，innodb 每次执行都会触发统计信息的更新
show indexes from mytable;

--基于成本的优化器：成本
show status like 'Last_query_cost';

--查看 MySQL 重构的查询语句
explain select id from t_user where id=-1;
show warnings;

--取消自动优化表排序（成本不一样）
select * from a straight_join b on a.id=b.a_id;

union
-----
(select name from a order by name desc limit 10)
union all 
(select name from b order by name desc limit 10)
order by name desc limit 10;

min max
-------
explain select min(id) from t_dict where name='sdfsf';
explain select id from t_dict use index(primary) where name='sdfsf' limit 1;

分页（代价可能非常高）
----
select sql_calc_found_rows * from people limit  0,1 ;
SELECT FOUND_ROWS();

--查询缓存
flush query cache;--碎片整理
reset query cache;--清空缓存