|
alter table text engine=innodb;表引擎转移 //可能会花费大量时间，而且在转换期间i/o很高 set @input := 'hello'; select benchmark(1000000,md(@input)); 循环10万次,用于测试数据库的执行效率 mysql中日志： 普通日志 log = <file_name> 慢速日志 log-slow-queries = <file_name> long_query_time =2 //defalut 10 log-queries-not-using-indexed log-slow-admin-statements mysql_slow_log_filter 是慢日志分析工具 tail -f mysql-slow.log | perl mysql_slow_log_filter -T 0.5 -R 1000 //运行时间超过0.5秒,检查大于 1000行 show session status like 'select%' 不同类型的联接执行计划 show session status like 'handler%' 存储引擎的操作‘ show session status like 'created-%' 在查询执行期间创建临时表和文件 show session status like 'sort_% '几种排序信息 show profile 一般是关闭 >set profiling =1; >select * from test limit 100 mysql> show profiles\G *************************** 1. row *************************** Query_ID: 1 Duration: 0.00019000 Query: select * from ytext_cloud_card where Fid >100 order by Fid desc limit 100 *************************** 2. row *************************** Query_ID: 2 Duration: 0.00018900 Query: select * from ytext_cloud_card where id >100 order by id desc limit 100 *************************** 3. row *************************** Query_ID: 3 Duration: 0.00016200 Query: show create table ytext_cloud_card *************************** 4. row *************************** Query_ID: 4 Duration: 0.01603700 Query: select * from ytext_cloud_card where Fuid >100 order by Fuid desc limit 100 4 rows in set (0.00 sec) mysql> show profile; +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000017 | | checking query cache for query | 0.000055 | | Opening tables | 0.000011 | | System lock | 0.000003 | | Table lock | 0.000023 | | init | 0.000042 | | optimizing | 0.000007 | | statistics | 0.000058 | | preparing | 0.000021 | | executing | 0.000002 | | Sorting result | 0.000004 | | Sending data | 0.015761 | | end | 0.000004 | | query end | 0.000002 | | freeing items | 0.000021 | | storing result in query cache | 0.000003 | | logging slow query | 0.000001 | | cleaning up | 0.000002 | +--------------------------------+----------+ 18 rows in set (0.00 sec) show profile cpu for query 1; //执行查询时cpu使用率 show processlist mysql连接 varchar(5) 和varchar(100) 占有的空间是一样的，但是在分配内存是会分配一整块，所以尽量使用小值. 修改表中某字段的默认值,只修改.frm文件 alter table test alter column tt set default 5; (责任编辑：IT) |