meisw's blog

先说下tmp_table_size吧：
它规定了内部内存临时表的最大值，每个线程都要分配。（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下，默认:
mysql> show variables like "tmpdir";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp/ |
+---------------+-------+
优化查询语句的时候，要避免使用临时表，如果实在避免不了的话，要保证这些临时表是存在内存中的。如果需要的话并且你有很多group by语句，并且你有很多内存，增大tmp_table_size(和max_heap_table_size)的值。这个变量不适用与用户创建的内存表(memory table).
你可以比较内部基于磁盘的临时表的总数和创建在内存中的临时表的总数（Created_tmp_disk_tables和Created_tmp_tables），一般的比例关系是:
Created_tmp_disk_tables/Created_tmp_tables<5%
max_heap_table_size
这个变量定义了用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。这个变量支持动态改变，即set @max_heap_table_size=#
,但是对于已经存在的内存表就没有什么用了，除非这个表被重新创建(create table)或者修改(alter table)或者truncate table。服务重启也会设置已经存在的内存表为全局max_heap_table_size的值。
这个变量和tmp_table_size一起限制了内部内存表的大小。

mysql的安装目录里有support-files，里面有几份默认的配置 分别是 huge large medium 等，还有高压力的InnoDB的参考： [root@aslibra support-files]# grep -v ^# my-huge.cnf key_buffer = 384M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M thread_concurrency = 8 [root@aslibra support-files]# grep -v ^# my-large.cnf key_buffer = 256M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M thread_concurrency = 8 [root@aslibra support-files]# grep -v ^# my-medium.cnf key_buffer = 16M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M [root@aslibra support-files]# cat my-innodb-heavy-4G.cnf|grep -v ^#|grep -v "^$" back_log = 50 max_connections = 100 max_connect_errors = 10 table_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 1M max_heap_table_size = 64M sort_buffer_size = 8M join_buffer_size = 8M thread_cache_size = 8 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default_table_type = MYISAM thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log-bin=mysql-bin log_slow_queries long_query_time = 2 log_long_format server-id = 1 key_buffer_size = 32M read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_max_extra_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover 一般情况下，自己根据机器的情况，复制一份到默认的配置文件的位置，替代默认配置 比如纯数据库的机器，有1-2G以上的内存，可以拷贝huge的那份，要不然默认配置效率不高，比如查询时容易用到磁盘做临时文件，这份可以提高内存的使用率。 对比一下几份配置的同一参数： key_buffer = 16M key_buffer = 256M key_buffer = 384M myisam_sort_buffer_size = 8M myisam_sort_buffer_size = 64M myisam_sort_buffer_size = 64M myisam_sort_buffer_size = 128M query_cache_size= 16M query_cache_size = 32M query_cache_size = 64M read_buffer_size = 256K read_buffer_size = 1M read_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 512K read_rnd_buffer_size = 4M read_rnd_buffer_size = 8M read_rnd_buffer_size = 16M sort_buffer_size = 512K sort_buffer_size = 1M sort_buffer_size = 2M sort_buffer_size = 8M table_cache = 64 table_cache = 256 table_cache = 512 table_cache = 2048 没有细节的技术可以解析，细节信息还是得查资料，这里只是列举对比一下，也许可以提醒大家使用内存和修改配置来优化mysql

1 TOP
这是一个大家经常问到的问题，例如在SQLSERVER中可以使用如下语句来取得记录集中的前十条记录：
SELECT TOP 10 * FROM [index] ORDER BY indexid DESC;
但是这条SQL语句在SQLite中是无法执行的，应该改为：
SELECT * FROM [index] ORDER BY indexid DESC limit 0,10;
其中limit 0,10表示从第0条记录开始，往后一共读取10条
2 创建视图（Create View）
SQLite在创建多表视图的时候有一个BUG，问题如下：
CREATE VIEW watch_single AS SELECT DISTINCTwatch_item.[watchid],watch_item.[itemid] FROM watch_item;
上面这条SQL语句执行后会显示成功，但是实际上除了
SELECT COUNT(*) FROM [watch_single ] WHERE watch_ single.watchid = 1;
能执行之外是无法执行其他任何语句的。其原因在于建立视图的时候指定了字段所在的表名，而SQLite并不能正确地识别它。所以上面的创建语句要改为：
CREATE VIEW watch_single AS SELECT DISTINCT [watchid],[itemid] FROM watch_item;
但是随之而来的问题是如果是多表的视图，且表间有重名字段的时候该怎么办？
3 COUNT（DISTINCT column）
SQLite在执行如下语句的时候会报错：
SELECT COUNT(DISTINCT watchid) FROM [watch_item] WHERE watch_item.watchid = 1;
其原因是SQLite的所有内置函数都不支持DISTINCT限定，所以如果要统计不重复的记录数的时候会出现一些麻烦。比较可行的做法是先建立一个不重复的记录表的视图，然后再对该视图进行计数。
4 外连接
虽然SQLite官方已经声称LEFT OUTER JOIN 已经实现，但还没有 RIGHT OUTER JOIN 和 FULL OUTER JOIN。但是实际测试表明似乎并不能够正常的工作。以下三条语句在执行的时候均会报错：
SELECT tags.[tagid] FROM [tags],[tag_rss] WHERE tags.[tagid] = tag_rss.[tagid](*);
SELECT tags.[tagid] FROM [tags],[tag_rss] WHERE LEFT OUTER JOIN tag_rss.[tagid] = tags.[tagid];
SELECT tags.[tagid] FROM [tags],[tag_rss] WHERE LEFT JOIN tag_rss.[tagid] = tags.[tagid];
此外经过测试用+号代替*号也是不可行的。
 
收集SQLite与Sql Server的语法差异

1.返回最后插入的标识值
返回最后插入的标识值sql server用@@IDENTITY
sqlite用标量函数LAST_INSERT_ROWID()
返回通过当前的 SQLConnection 插入到数据库的最后一行的行标识符（生成的主键）。此值与 SQLConnection.lastInsertRowID 属性返回的值相同。
2.top n
在sql server中返回前2行可以这样：
select top 2 * from aa
order by ids desc
sqlite中用LIMIT，语句如下：
select * from aa
order by ids desc
LIMIT 2
3.GETDATE ( )
在sql server中GETDATE ( )返回当前系统日期和时间
sqlite中没有
4.EXISTS语句
sql server中判断插入（不存在ids=5的就插入）
IF NOT EXISTS (select * from aa where ids=5)
BEGIN
insert into aa(nickname)
select 't'
END
在sqlite中可以这样
insert into aa(nickname)
select 't'
where not exists(select * from aa where ids=5)
5.嵌套事务
sqlite仅允许单个活动的事务
6.RIGHT 和 FULL OUTER JOIN
sqlite不支持 RIGHT OUTER JOIN 或 FULL OUTER JOIN
7.可更新的视图
sqlite视图是只读的。不能对视图执行 DELETE、INSERT 或 UPDATE 语句，sql server是可以对视图 DELETE、INSERT 或 UPDATE

1、Linux下mysql安装完后是默认：区分表名的大小写，不区分列名的大小写；

2、用root帐号登录后，在/etc/my.cnf 中的[mysqld]后添加添加lower_case_table_names=1，重启MYSQL服务，这时已设置成功：不区分表名的大小写；

lower_case_table_names参数详解：

lower_case_table_names = 0

其中 0：区分大小写，1：不区分大小写

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：

1、数据库名与表名是严格区分大小写的；

2、表的别名是严格区分大小写的；

3、列名与列的别名在所有的情况下均是忽略大小写的；

4、变量名也是严格区分大小写的；

MySQL在Windows下都不区分大小写。

3、如果想在查询时区分字段值的大小写，则：字段值需要设置BINARY属性，设置的方法有多种：

A、创建时设置：

CREATE TABLE T(

A VARCHAR(10) BINARY

);

B、使用alter修改：

ALTER TABLE `tablename` MODIFY COLUMN `cloname` VARCHAR(45) BINARY;

C、mysql table editor中直接勾选BINARY项

mysql从4.1版开始支持子查询功能，在此版本前，可以用join写连表查询来进行替代，但不推荐这么写，相当的麻烦。以下是mysql子查询的几种常见写法：

mysql从4.1版开始支持子查询功能，在此版本前，可以用join写连表查询来进行替代，但不推荐这么写，相当的麻烦。mysql子查询的几种常见写法：

1. select * from xxx where col = [any|all](select * from xxxx); 

该句法可分为加关键词和不加关键词的写法，当不加关键词的时候，子查询语句返回的是一个离散值(注意是一个)，查询语句将以子查询语句的结果作为自 己 where子句的条件进行查询，该句法可以在子查询语句前加入any、all、some等关键字，此时子查询语句返回的是一组离散值。any则表示,查询 语句是以子查询返回的值作为一个范围，在此值范围内进行查询，其与in关键字相像；all关键不太好了解，表示起全部匹配啥的。

2. select * from xxx where col in (select * from xxxx); 

该 句法相当的明了，就是查询语句的where子句是以子查询语句的结果作为其范围的，与上一种语法的any相似。

3. select row(value1,value2.....) = [any](selectcol1,col2..); 

该语句的执行实质是：子查询语句的执行所得的结果与查询的结果集匹配，若能找到匹配的返回真，否则返回假，并且两边的结果集都是一组离散值。

4. select  .... where col = [not] exists (select......); 

该语句比较蹩脚，其执行是这样的：当子查询进行操作，有返回结果的时候，该语句才会执行，而且有多少个结果，语句就执行几次。

5. select .... from (select .....) as name where ...... 

该句法，在平时用的比较少，而且并不好理解。其实是这么一回事，通过子查询执行的结果来构造一张新的表（这个表是一张衍生数据表，是一张虚拟的表），其用来作为主句的查询的对象，该句法功能非常强大，在一些复杂的查询中会经常用到。

子查询虽然挺方便，但其有不少缺点，其不支持limit，而且经实验证明其执行效率相当不理想，在一般的情况下，还是不推荐是用子查询。

mysql 最新的版本都需要cmake编译安装，估计以后的版本也会采用这种方式，所以特地记录一下安装步骤及过程，以供参考。

注意：此安装是默认centos下已经安装了最新工具包，比如GNU make, GCC, Perl, libncurses5-dev，如果在编译安装过程中发现有缺失的工具包，先yum install 单独下载安装再继续即可。

 

一. Centos 用 wget 下载需要的软件，保存到目录 /usr/local/src/ 下

wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.11.tar.gz/from/http://mysql.he.net/

wget http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz

wget http://ftp.gnu.org/gnu/bison/bison-2.4.3.tar.gz

 

二. 安装 cmake

cd /usr/local/src

 

tar zxvf cmake-2.8.4.tar.gz
cd cmake-2.8.4
./bootstrap
gmake
gmake install
cd ../

tar zxvf bison-2.4.3.tar.gz
cd bison-2.4.3
./configure
make
make install

cd ../

 

三. 编译安装 MySQL 5.5.11

/usr/sbin/groupadd mysql
/usr/sbin/useradd -g mysql mysql

tar xvf mysql-5.5.11.tar.gz
cd mysql-5.5.11/

cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS:STRING=utf8,gbk \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_DATADIR=/var/mysql/data \
-DMYSQL_USER=mysql

make
make install

chmod +w /usr/local/mysql
chown -R mysql:mysql /usr/local/mysql

ln -s /usr/local/mysql/lib/libmysqlclient.so.16 /usr/lib/libmysqlclient.so.16

 

mkdir -p /var/mysql/
mkdir -p /var/mysql/data/
mkdir -p /var/mysql/log/
chown -R mysql:mysql /var/mysql/

cdsupport-files/

cpmy-large.cnf /var/mysql/my.cnf

cp mysql.server /etc/init.d/mysqld

四. 配置启动MySQL 5.5.11

1. 若有需要请先修改 mysql 的配置 my.cnf

vi /var/mysql/my.cnf

 

2. mysql 初始化安装

/usr/local/mysql/scripts/mysql_install_db \
--defaults-file=/var/mysql/my.cnf \
--basedir=/usr/local/mysql \
--datadir=/var/mysql/data \
--user=mysql

3. 将 mysql 加入开机启动

chmod +x /etc/init.d/mysqld
vi /etc/init.d/mysqld （编辑此文件，查找并修改以下变量内容：）
basedir=/usr/local/mysql
datadir=/var/mysql/data

chkconfig --add mysqld
chkconfig --level 345 mysqld on

4. 启动 mysql

service mysqld start

select table_schema,
sum(data_length+index_length)/1024/1024 as total_mb,
sum(data_length)/1024/1024 as data_mb,
sum(index_length)/1024/1024 as index_mb,
count(*) as tables
from information_schema.tables
group by table_schema
order by 2 desc;

Report Header

MySQL 5.0.3 uptime 0 0:34:26 Fri Sep 1 19:46:02 2006
MySQL Server 的版本、自上次启动后已经过多少时间、目前 Server 的日期与时间

key report
MySQL Server的Buffer分为Global Buffer和Thread Buffer

计算 Server 至少需使用的总内存数量的方式为：
min_memory_needed = global_buffer + (thread_buffers * max_connection)

MyISAM Storage Engine 将每个 table 分成三个档案储存在硬盘之中.

FRM： 储存这个数据表的结构
MYD： Row Data，也就是你存在 example 数据表里的数据
MYI： 此数据表的索引

Buffer used 380.00k of 512.00M %Used: 0.07
Current 59.32M %Usage: 11.59
Write ratio 0.93
Read ratio 0.00

1:Buffer used指出 MySQL “曾经” 耗用过的最大内存数量，因此目前 “正在使用” 的内存数量有可能少于（甚至大于）这个数字。MySQL 称此数值为 “High Water Mark”
如果你的 MySQL 已经使用了 80~90% 以上的 Key Buffer，你就应该要调高 key_buffer_size.

2:Current表示 Key_blocks_unused 这个系统变量来决定目前 MySQL “正在使用” 的 Share Key Buffer 大小

索引(Indexes, Keys)主要是在内存内(RAM-Based)进行操作的，索引之所以如此有用有部份原因就归功于它们主要是在 RAM 里面运作，因此拥有极高的存取效能，不像储存在硬盘中的数据存取速度非常慢。然而，不可否认的是 MySQL 终究还是必须从硬盘中将索引读入 RAM 或是将储存在 RAM 中的索引写回硬盘之中

3:Write ratio 标示着 MySQL 将索引写入硬盘与 MySQL 将索引写入 RAM 的比值(Write Ratio = MySQL 将索引写入硬盘的次数 / MySQL 将索引写入 RAM 的次数)。具有接近于1 的Write Ratio 并不是一件很罕见的事，就像 MySQL 官方手册中所说的，如果你的 MySQL 最主要的活动是 Update、Insert 等等，那么 Write Ratio 将会很接近于1

4:Read Ratio 比 Write Ratio 来得重要一些，它标示了 MySQL 从硬盘读取索引与从 RAM 读取索引的比值(Read Ratio = MySQL 从硬盘读取索引的次数 / MySQL 从 RAM 读取索引的次数)。Read Ratio 的值应该要是 0.00 或 0.01，若大于这个值则表示 Server 有问题需要进一步的调查，通常此问题的成因是 Share Key Buffer 设得太小造成 MySQL 需要不断地从硬盘中读取所需要的索引信息，而这个动作是十分没有效率的并且完全抵消了使用索引可以带来的好处。
Questions Report
Questions是第二重要的信息，因为它可以告诉你 MySQL 到底都在忙些什么事情。Questions 包含了 SQL queries 以及 MySQL protocol communications。大部份的人都只在意 Server 每秒可以处理多少查询(Queries Per Second, QPS)，但若以整个 Server 的观点来考虑，QPS 其实是非常不精确的数值，它无法有效的告诉您 Server 的整体运作状况

Total 98.06k 47.46/s
DMS 81.23k 39.32/s %Total: 82.84
QC Hits 16.58k 8.02/s 16.91
COM_QUIT 200 0.10/s 0.20
Com_ 131 0.06/s 0.13
-Unknown 82 0.04/s 0.08
Slow 0 0.00/s 0.00 %DMS: 0.00
DMS 81.23k 39.32/s 82.84
SELECT 64.44k 31.19/s 65.72 79.33
INSERT 16.75k 8.11/s 17.08 20.61
UPDATE 41 0.02/s 0.04 0.05
REPLACE 0 0.00/s 0.00 0.00
DELETE 0 0.00/s 0.00 0.00
Com_ 131 0.06/s 0.13
change_db 119 0.06/s 0.12
show_fields 9 0.00/s 0.01
show_status 2 0.00/s 0.00

1:Total 表示
纯的记载 MySQL 总共响应过多少查询，第二个字段则记录响应的频率(QPS)

2:Distribution of Total Queries (DTQ):
所有的 Questions 可以大致区分为五个不同的类别：
1.Data Manipulation Statements (DMS)
2.query cache hits (QC Hits)
3.COM_QUIT
4.all other Com_ commands
5.Unknown

理想的情况下，你会希望 MySQL 把大部份的时间都花在 DMS 与 QC Hits 这两个类别，因为这两个类别才是真正在 “完成正事” 的类别

3:Data manipulation statements(DMS) 包含了：ELECT, INSERT, REPLACE, UPDATE, 与 DELETE(技术上来说，其实不只这几个类别但mysqlreport 只会用到这几类)。基本上，你可以将 DMS 想成是 MySQL 真正有在做些 “有用的事” 的情况，因此你会希望 DMS 是 MySQL 最忙着处理的事情。

4:QC Hits 是 MySQL 不需要实际执行 Query 而只要直接从 Query Cache 中即可找到所需数据的次数。拥有高比例的 QC Hits 是让人梦寐以求的事，因为从 Query Cache 直接存取所需要的数据是十分快速且有效率的。然而大部份的 MySQL Server 因为各种原因，而无法具有非常有效率的 Query Cache

5:COM_ 这个类别代表着所有 MySQL 所执行过的指令，通常与 MySQL protocol 相关。在正常的情况下，你会希望这个类别所占的比例越低越好，因为当这个数值很高的时候就表示 MySQL 正忙碌于无关紧要的事情上

Slow
表示它记录了 MySQL 总共执行了多少次 Slow Query。Slow Query 就是指执行所需时间超过某个时间区间的 Query。一般来说 Slow Query 占 Total Questions 的比例应该要低于 0.05，Slow Query 的次数(第一个字段)本身不是很重要，真正需要注意的是

Slow Query 占 Total Questions 的比例，若这比例偏高就代表 Server 有些问题需要解决
DMS 81.23k 39.32/s 82.84
SELECT 64.44k 31.19/s 65.72 79.33
INSERT 16.75k 8.11/s 17.08 20.61
UPDATE 41 0.02/s 0.04 0.05
REPLACE 0 0.00/s 0.00 0.00
DELETE 0 0.00/s 0.00 0.00

DMS 的子分类项目可以告诉我们，这台 MySQL Server 是属于哪一个类型的 MySQL Server，例如它是着重在 SELECT 操作或是 INSERT 操作，大部份的 MySQL Server 都是着重在 SELECT 操作。知道某台 Server 是属于哪一个类型的 MySQL Server 有助于我们思考报表中的其它信息，例如一台着重在 SELECT 操作的 MySQL Server 的 Write Ratio 应该会非常的接近 1，并有着较高的 Lock 时间。同时它也隐含了一个意义，就是也许你可以考虑使用 InnoDB Storage Engine，因为 MySQL 预设采用的 MyISAM Storage Engine 所提供的 Lock 层级只有

Table Lock(只能针对整个数据表锁定)，而 InnoDB 则提供 Row Lock 层级的锁定机制(可只针对特定的 ROW 进行锁定，减少等待时间)。若是着重在 SELECT 操作的 Server，它的 Read Ratio 应该会接近于零，并有着非常低的 Table Lock 时间。

SELECT and Sort Report

Scan 38 0.02/s %SELECT: 0.06
Range 14 0.01/s 0.02
Full join 3 0.00/s 0.00
Range check 0 0.00/s 0.00
Full rng join 0 0.00/s 0.00
Sort scan 14 0.01/s
Sort range 26 0.01/s
Sort mrg pass 0 0.00/s

大致上来说，你只要注意Scan 与 Full Join。Scan 指的是有多少 SELECT statements 造成 MySQL 需要进行 Full Table Scan。Full Join 的意思与 Scan 差不多，但它是适用在多个 Tables 相互 Join 在一起的情况。这二种情况的执行效能都非常的差，因此原则上你会希望这两个数值越低越好。

Query Cache Report

Memory usage 17.81M of 32.00M %Used: 55.66
Block Fragmnt 13.05%
Hits 16.58k 8.02/s
Inserts 48.50k 23.48/s
Prunes 33.46k 16.20/s
Insrt:Prune 1.45:1 7.28/s
Hit:Insert 0.34:1

Query Cache Report 只有在 MySQL 有支持 Query Cache，以及 Query Cache 功能有开启的情况下才会有这段信息出现。

(1)Memory usage 表示此项目指出 Query Cache 的使用状况

(2)Block Fragmnt 表示块碎片,通常它会界于 10%~20% 之间

(3)Hits, Inserts, Prunes

Table Locks Report

Waited 1.01k 0.49/s %Total: 1.24
Immediate 80.04k 38.74/s

这个部份包含了两项信息：第一项是 Waited，代表 MySQL 需要等待以取得 table lock 的次数。第二项是 Immediate，表示 MySQL 不需要等待即可立刻取得 table lock 的次数

Tables Report

Open 107 of 1024 %Cache: 10.45
Opened 118 0.06/s

Tables Report 同样包含了二项信息：第一是 Open，显示目前正开启的 table 数量、总共可开启的最大数量，以及 Table Cache 的使用状况。第二是 Opend，表示截至目前为止 MySQL 总共开启过的 Table 数量，以及除上 Uptime 后的比值。这里有两件事值得注意：首先是Table Cache 的使用状况，100% 的 Table Cache 使用率并不是一件坏事但你可以试着调大 Table Cache 以增进效能。第二是 MySQL 开启Table 的平均速率，若这个值很高则表示您的 table_cache 设得太小了，需要调大一些。一般来说，MySQL 开启 Table 的平均速率最好是小于 1/s。但大于这个数值也不一定就是坏事，有些调校良好且运作的十分有效率的 MySQL Server 其值为 7/s 并使用了 100% 的 Table Cache

Connections Report

Max used 77 of 600 %Max: 12.83
Total 202 0.10/s

Connections Report 所代表的意义与 Tables Report 相似，请各位以此类推。比较需要注意的是：若你发现 Connections 的使用率接近100%，也许你会想调大 max_connections 的值以允许 MySQL 的 Client 建立更多联机。然而，这通常是一种错误。我们常常可以发现很多网络上的数据会教我们要调大 max_connections，但却从来没有给一个明确的理由。事实上，max_connections 的默认值(100)，就算是对于负载十分沉重但有良好调校过的 Server 都已十分足够。MySQL 对于单一联机的数据处理通常只需要零点几秒的时间即可完成，就算是最大只能使用 100 个联机也够让你用上很长一段时间。若是您的 Server 有着非常高的最大联机数(max connections)或是单一联机需要很长时间才可完成，那么问题八成不是 max_connections 的值不够大而是在别的地方，例如 slow queries、索引设计不良、甚至是过于缓慢的 DNS 解析。在您将 max_connections 的值调到 100 以上之前，您应该要先确定真的是因为 Server 过于忙碌而需要调高此数值，而不是其它地方出了问题。每秒平均联机数有可能会很高，事实上，若这个值很高而且 Server 的运作十分顺畅，那么这通常会是一个好现象，无需

担心。大部份 Server 的每秒平均联机数应该都会低于 5/s。

Created Temp Report

Disk table 10 0.00/s
Table 26 0.01/s
File 3 0.00/s

MySQL 可以建立暂时性的数据表，它可建立在硬盘中、档案里、或是 RAM 之中，而 Created Temp Report 则提供了相关的数据供您参考。这些数据大多是相对而言，没有一定的标准，但将暂时性的数据表建立在硬盘中是十分没有效率的，因此 Disk table 的值最好是三者中最小的一个。当暂时性的数据表被建立在硬盘中，表示此数据表没有办法被放进 RAM 里面（因为 tmp_table_size 的值设得不够大）。

Threads, Aborted, Bytes Reports

Running 55 of 77
Cache 0 %Hit: 0.5
Created 201 0.10/s
Slow 0 0.00/s

这几个部份大多没什么好解释的，只有一个项目值得特别说明：第 66 行的最后一个字段(%Hit)。每一个连接到 MySQL 的联机都是由不同的Thread 来处理，当 MySQL 启动时会预先建立一些 Threads 并保留在 Thread Cache 中，如此一来 MySQL 就不用一直忙着建立与删除Threads。但当每秒最大联机数大于 MySQL 的 Thread Cache 时，MySQL 就会进入 Thread Thrash 的状态：它不断地建立新的 Threads 以满足不断增加的联机的需求。当 Thread Thrash 发生时，%Hit 的数值就会降低。在本范例中 %Hit 的值为 0.05%，这是非常不好的，因为它表示几乎每一个新进来的联机都会造成 MySQL 建立新的 Thread。我们可以看到在此范例中造成此现象的原凶就在第 66 行的第一个字段，我们可以发现 Thread Cache 的值为 0，因此 thread_cache_size 的值需要调大

mysqlreport中的innodb部分详解

 

__ InnoDB Buffer Pool __________________________________________________
Usage 7.97M of 8.00M %Used: 99.61
Read hit 100.00%

Pages
Free 2 %Total: 0.39
Data 499 97.46 %Drty: 0.00
Misc 11 2.15
Latched 0 0.00

Reads 101.06M 8.5/s
From file 373 0.0/s 0.00
Ahead Rnd 19 0.0/s
Ahead Sql 13 0.0/s

Writes 860.88k 0.1/s
Flushes 254.62k 0.0/s
Wait Free 0 0/s

__ InnoDB Lock _________________________________________________________
Waits 424 0.0/s
Current 0
Time acquiring
Total 254266 ms
Average 599 ms
Max 39559 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 502 0.0/s
Writes 344.09k 0.0/s
fsync 158.03k 0.0/s
Pending
Reads 0
Writes 0
fsync 0

Pages
Created 699 0.0/s
Read 523 0.0/s
Written 254.62k 0.0/s

Rows
Deleted 4.59k 0.0/s
Inserted 74.16k 0.0/s
Read 94.67M 8.0/s
Updated 40.61k 0.0/s

第一区块, 展示了 mysql innodb 的缓存统计信息.
其中, innodb跟myisam的缓存机制有较大区别, innodb不仅缓存索引,还缓存一些表数据.而myisam只缓存索引.

Usage 7.97M of 8.00M %Used: 99.61
Read hit 100.00%

Usage 表示, 总的缓存中, 当前已占用 7.97M, 使用率达 99.61%. 这种情况, 是存在瓶颈的, 需要适当增加缓存总量.
Read hit 表示缓存命中率 100%, 这个数值是比较理想的, 一般情况下, 都应该大于 99.98%.

Pages
Free 2 %Total: 0.39
Data 499 97.46 %Drty: 0.00
Misc 11 2.15
Latched 0 0.00

innodb的存储是按页分的, 每页的容量默认是 16K. (详见 http://www.mysqlperformanceblog.com/2006/06/04/innodb-page-size/)

这里的,Free指的是缓存中的总页数, 剩余的页, 占总的 0.39%.
Data是指缓存中, 存储索引数据的页的数量.其中, 最后一项 %Dtry 表示脏数据的百分比.所谓的脏数据是指, 对缓存数据更新后, 没有同步到硬盘的数据.
misc 和 latched 就是之前说的其他信息的一些缓存.

Reads 101.06M 8.5/s
From file 373 0.0/s 0.00
Ahead Rnd 19 0.0/s
Ahead Sql 13 0.0/s

Reads代表从缓存里, 总共读取了多少M的数据.
From file, 表示从硬盘文件中读取到缓存里的页数量.
Ahead Rnd, 表示随机预读的次数.
Ahead Sql, 表示全表扫描时, sql预读的次数.

Writes 860.88k 0.1/s
Flushes 254.62k 0.0/s
Wait Free 0 0/s

Writes , 表示写入缓存的总大小.
Flushes , 表示缓存数据更新到硬盘的大小.
Waint Free, 表示, 等待可写入数据的页的次数.
这里为什么会等待, 是因为, 数据写入到缓存页时, 必须保证, 这个页被创建好, 或者这个页之前的数据已经被同步到硬盘上.

Waits 424 0.0/s
Current 0
Time acquiring
Total 254266 ms
Average 599 ms
Max 39559 ms

Waits , 表示执行线程等待锁的释放的次数.
Current, 表示当前所有的执行线程, 正在等待锁的数量.
Time acquiring 里的 Total , 指的是, 等待锁所消耗的总时间.
Average, 是指, 平均消耗的时间.
Max, 是指, 单次等待锁, 所消耗最多的时间.

Data
Reads 502 0.0/s
Writes 344.09k 0.0/s
fsync 158.03k 0.0/s
Pending
Reads 0
Writes 0
fsync 0

Data里的Reads, 表示数据读取的次数.
Writes, 表示数据写入的数据量大小.
fsync, 表示缓存同步到硬盘的数据量大小.

Pending里的Reads, Writes, fsync , 表示当前进行读写, 同步的次数.

Pages
Created 699 0.0/s
Read 523 0.0/s
Written 254.62k 0.0/s

Pages 里的created, 表示, 总共创建过 699 个页.
Read, 表示被读取的页的数量.
Written, 表示写入到页里的,总大小.

Rows
Deleted 4.59k 0.0/s
Inserted 74.16k 0.0/s
Read 94.67M 8.0/s
Updated 40.61k 0.0/s

Rows里的四个操作,分别代表删除, 插入, 查询, 更新的数据量大小.

性能关注点分析
首先是Usage, 如果占比达 90 – 95%以上, 可能需要增加预设缓存大小.
调整的参数是 innodb_buffer_pool_size

其次是 Read Hit, 即缓存命中率, 如果该值远远小于100%, 就要调查缓存的有效性了.
比如, 缓存写次数是否大于查询次数. (Buffer里的reads和writes)

最后,需要关注的是, 数据的读写比例. 这里有个要注意的地方是, mysql如果启动到现在不到24小时或一个较长的运行周期, 这个读写比例值可能是不准的.

一般的应用,我感觉读写比例在 8/2 差不多. 如果读远远大于写, 那么你可以测下 MyISAM 引擎的性能, 看看是否适合你.

http://www.uini.net/2010/05/mysqlreport-report-describes.html