mysql简要调优
##########################################################
benchmark()测试服务器速度
select benchmark(100000,10+10);
select benchmark(100000,extract(year from now()));
############################################################
key_buffer_size//用于索引的缓存大小，缓存越大，sql查询就越快
show status
key_reads/key_read_requests<0.01
key_writes/key_write_requests<1
table_cache//所有线程所打开的表的数目。默认值64
如果open_table的数值越大，table_cache的值就应该增加
#############################################################
optimize table user; //ok代表优化过
+-------------+----------+----------+----------+
| Table       | Op       | Msg_type | Msg_text |
+-------------+----------+----------+----------+
| mytest.user | optimize | status   | OK       |
+-------------+----------+----------+----------+
mysql> optimize table user;//再次运行
+-------------+----------+----------+-----------------------------+
| Table       | Op       | Msg_type | Msg_text                    |
+-------------+----------+----------+-----------------------------+
| mytest.user | optimize | status   | Table is already up to date |
+-------------+----------+----------+-----------------------------+
###############################################################
mysql> explain select * from user; //获取select相关信息
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    6 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
#######################################
flush [privileges,tables,hosts,logs]
###########################################
性能瓶颈定位
set profiling=1;
select * from user;
show profiles;
show profiles type for query n
type={all,block io,context switches,cpu,ipc,memory,page faults,source,swaps}