优前先首先是分析表属性与sql语句,根据情况进行优化:
显示进程
show full processlist \G
显示语句影响
explain select*from student\G
explain partitions select*from student where yr in (2011,2012)\G
explain extended select t1.name -> from test1 t1 inner join test2 t2 using(uid)\G
show warnings\G
表的结构
show create table student\G
表的大小
方法一:show table status like 'student'\G
方法二:
set @schema = ifnull(@schema,database());
set @table = 'table_name';
select @schema as table_schema ,curdate()as today;
select table_name,engin,
row_format as format,
table_rows,
avg_row_length as avg_row,
round((data_length+index_length)/1024/1024,2) as total_mb,
round(data_length/1024/1024,2) as data_mb,
round(index_length/1024/1024,2) as index_mb
from information_schema.tables
where table_schema = @schema and table_name= @table;
表的索引
show indexes from student\G
字段的属性
show full columns from `table_name`;查看当前内部状态信息
show global status like 'created_tmp_%tables';
show session status like 'handler_read%';
查看系统变量当前值
show variables 服务器配置变量
show session variables like 'tmp_table_size';
show global variables like '';
select version() 服务器版本信息
select database() 当前数据库
select user() 当前用户
show status 服务器状态
set @table='table_name';
source tablesize.sql
软件工具:
maatkit mk-duplicate-key-checker 找出重复索引的开源工具,也可以对模式表进行桌面人工验证
show index_statistics ###google 发行的查看使用的index和没有使用的index 命令,mysql官方版本的没有这个命令,其它发行版有。
oak-online-alter-table 或 facebook发布的 osc工具 ###主表复制技术