mysql常用的性能分析
时间:2016-06-06 12:14 来源:linux.it.net.cn 作者:IT
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)
| 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) |