mysql常用的性能分析
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;
