189 8069 5689
MySQL中使用show status语句查询MySQL的性能参数
创新互联建站坚持“要么做到，要么别承诺”的工作理念，服务领域包括：网站制作、成都网站制作、企业官网、英文网站、手机端网站、网站推广等服务，满足客户于互联网时代的丹阳网站设计、移动媒体设计的需求，帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴！
语法：
show status like [statement];
常用statement说明：
# 查询连接MySQL服务器的次数
# 查询MySQL服务器上线的时间
# 查询慢查询的次数
# 查询 查询操作的次数
查看MySQL服务器配置信息 ：
查看MySQL服务器运行的各种状态值 ：
慢查询：
连接数：
key_buffer_size 是对MyISAM表性能影响最大的一个参数
临时表：
查看open table ：
进程使用情况：
查询缓存(query cache) ：
文件打开数(open_files) ：
表锁情况 ：
表扫描情况 ：
查询吞吐量：
连接情况：
分析查询语句
mysql提供了explain语句和describe语句，进行分析查询语句
EXPLAIN的语法：
说明：
示例：
DESCRIBE语句
使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。
语法：
mysql中提高性能的最有效的方式：
1, 对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快了查询的速度。
使用索引查询几种特殊情况：
可以使用EXPLAIN语句查看SQL脚本性能：
EXPLAIN SELECT 语句；
例如学生表，其中电话字段建立了索引
学生表有6条记录
查询SQL语句的运行效率
explain select * from students where phone like '%34%'\G
下图rows为6，显示该查询扫描了6行记录，没有利用到索引
explain select * from students where phone like '45%'\G
下图rows为1，显示该查询扫描了1行记录，利用到了索引
两图比较like匹配查询
第一种写法 '%34%' 扫描了6行，索引未被利用；第二种写法 '45%' 扫描1行，索引发挥作用，效率更高。
限流算法目前程序开发过程常用的限流算法有两个：漏桶算法和令牌桶算法。
漏桶算法
漏桶算法的原理比较简单，请求进入到漏桶中，漏桶以一定的速率漏水。当请求过多时，水直接溢出。可以看出，漏桶算法可以强制限制数据的传输速度。如图所示，把请求比作是水滴，水先滴到桶里，通过漏洞并以限定的速度出水，当水来得过猛而出水不够快时就会导致水直接溢出，即拒绝服务。
图片来自网络
漏桶的出水速度是恒定的，那么意味着如果瞬时大流量的话，将有大部分请求被丢弃掉（也就是所谓的溢出）。
令牌桶算法
令牌桶算法的原理是系统以一定速率向桶中放入令牌，如果有请求时，请求会从桶中取出令牌，如果能取到令牌，则可以继续完成请求，否则等待或者拒绝服务。这种算法可以应对突发程度的请求，因此比漏桶算法好。
图片来自网络
漏桶算法和令牌桶算法的选择
两者的主要区别漏桶算法能够强行限制处理数据的速率，不论系统是否空闲。而令牌桶算法能够在限制数据的平均处理速率的同时还允许某种程度的突发流量。如何理解上面的含义呢？漏桶算法，比如系统吞吐量是 120/s，业务请求 130/s，使用漏斗限流 100/s，起到限流的作用，多余的请求将产生等待或者丢弃。对于令牌桶算法，每秒产生 100 个令牌，系统容量 200 个令牌。正常情况下，业务请求 100/s 时，请求能被正常被处理。当有突发流量过来比如 200 个请求时，因为系统容量有 200 个令牌可以同一时刻处理掉这 200 个请求。如果是漏桶算法，则只能处理 100 个请求，其他的请求等待或者被丢弃。
如何提高MySQL Limit查询的性能?
在MySQL数据库操作中，我们在做一些查询的时候总希望能避免数据库引擎做全表扫描，因为全表扫描时间长，而且其中大部分扫描对客户端而言是没有意义的。其实我们可以使用Limit关键字来避免全表扫描的情况，从而提高效率。
有个几千万条记录的表 on MySQL 5.0.x，现在要读出其中几十万万条左右的记录。常用方法，依次循环：
select * from mytable where index_col = xxx limit offset, limit;
经验：如果没有blob/text字段，单行记录比较小，可以把 limit 设大点，会加快速度。
问题：头几万条读取很快，但是速度呈线性下降，同时 mysql server cpu 99% ，速度不可接受。
调用 explain select * from mytable where index_col = xxx limit offset, limit;
显示 type = ALL
在 MySQL optimization 的文档写到"All"的解释
A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.
看样子对于 all, mysql 就使用比较笨的方法，那就改用 range 方式？ 因为 id 是递增的，也很好修改 sql 。
select * from mytable where id offset and id offset + limit and index_col = xxx
explain 显示 type = range，结果速度非常理想，返回结果快了几十倍。
Limit语法：
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
LIMIT子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT接受一个或两个数字参数。参数必须是一个整数常量。
如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)。
为了与 PostgreSQL 兼容，MySQL 也支持句法：LIMIT # OFFSET #。
mysql SELECT * FROM table LIMIT 5,10; //检索记录行6-15
//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为-1
mysql SELECT * FROM table LIMIT 95,-1; //检索记录行96-last
//如果只给定一个参数，它表示返回最大的记录行数目，换句话说，LIMIT n 等价于 LIMIT 0,n
mysql SELECT * FROM table LIMIT 5; //检索前5个记录行
MySQL的limit给分页带来了极大的方便，但数据量一大的时候，limit的性能就急剧下降。同样是取10条数据，下面两句就不是一个数量级别的。
select * from table limit 10000,10
select * from table limit 0,10
文中不是直接使用limit，而是首先获取到offset的id然后直接使用limit size来获取数据。根据他的数据，明显要好于直接使用limit。
这里我具体使用数据分两种情况进行测试。
1、offset比较小的时候：
select * from table limit 10,10
//多次运行，时间保持在0.0004-0.0005之间
Select * From table Where vid ＞=(Select vid From table Order By vid limit 10,1) limit 10
//多次运行，时间保持在0.0005-0.0006之间，主要是0.0006
结论：偏移offset较小的时候，直接使用limit较优。这个显然是子查询的原因。
2、offset大的时候：
select * from table limit 10000,10
//多次运行，时间保持在0.0187左右
Select * From table Where vid ＞=(Select vid From table Order By vid limit 10000,1) limit 10
//多次运行，时间保持在0.0061左右，只有前者的1/3。可以预计offset越大，后者越优。
给你看个范例：
测试环境：
DELL 2.4G memory 512M
RH9.0 MySQL 3.23.54
测试使用的是mysql缺省参数，用mysql提供的API用C编写测试程序
测试程序共启动40个线程进行数据库操作，查找、插入、修改、删除各10个，每个线程独立与Sql Server连接。
数据库结构，单表，表结构如下：
toheader 100byte 主键
contactheader 100byte
called 50byte
cseq 100byte
hashval int
timestamp int 次键
对主次键分别建了索引。分别在5万、10万、50万用户环境做测试，结果如下：
查找100次
插入100次
修改100次
删除100次
5万
100－300ms
100－300ms
100－300ms
100－300ms
10万
500ms-1s
500ms-1s
500ms-1s
500ms-1s
50万
3s-5s
3s-5s
3s-5s
3s-5s
从此数据看性能是很不错的，因为mysql能保证每个操作是原子的，所以不用考虑线程间的同步。
但有一个问题：即mysql的每个操作是原子的，所以做每个操作时，其它线程是阻塞的，在大数据量查询时，花的时间较长，会对其它线程有影响。我在保持其它线程工作不变的情况下，将每个查找线程改为做一次对所有记录的查询，在5万和10万记录时表现不错，分别为250ms和450ms，但在50万记录时，这个数值达到了22秒。而且在50万用户时，通过条件查找部分数据也很慢，如查询结果为10万记录时用11秒。
怀疑是sql server的参数影响，按数据库说明修改了几个缓冲区的参数，但没有效果。
由于首次同步发生的频率很低，象250ms和450ms这样的数据还是可以接受的，但22秒太离谱了。支持50万用户在线，需要考虑一个解决办法，现在有一个办法是建一个备份表，写备份表的请求放到一个队列里由一个单独线程处理，这样阻塞不会影响正常业务处理了。但这个线程的缓冲区要足够容纳30秒内发生的操作。
本来担心数据量大了mysql的缓冲区不够会出错或丢数据，但测试发现，一次查询最多读了50M数据，没有出现问题，经测算我们首次同步的数据不会超过10M。
在MySQL中启动了innoDB引擎后，可以实现真正的行级锁，select和update操作可以并发，这样在全表查询进行中间可以进行其它的select和update操作，但insert和delete不行。
性能也有很大提高，50万记录时100次操作300ms，全表查询11秒多
常用参数 [options] 详细说明：--auto-generate-sql,
-a 自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力。
--auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)。
--auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持。--number-char-cols=N,
-x N 自动生成的测试表中包含多少个字符类型的列，默认1--number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认1--number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)
--query=name,-q 使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试。--create-schema 代表自定义的测试库名称，测试的schema，MySQL中schema也就是database。--commint=N 多少条DML后提交一次。
--compress, -C 如果服务器和客户端支持都压缩，则压缩信息传递。--concurrency=N, -c N 表示并发量，也就是模拟多少个客户端同时执行select。可指定多个值，以逗号或者--delimiter参数指定的值做为分隔符。例如：
--concurrency=100,200,500。
--engine=engine_name, -e engine_name 代表要测试的引擎，可以有多个，用分隔符隔开。例如：--engines=myisam,innodb。--iterations=N, -i N 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次。
--only-print 只打印测试语句而不实际执行。--detach=N 执行N条语句后断开重连。--debug-info, -T 打印内存和CPU的相关信息。说明：测试的过程需要生成测试表，插入测试数据，这个mysqlslap可以自动生成，默认生成一个mysqlslap的schema，如果已经存在则先删除。可以用--only-print来打印实际的测试过程，整个测试完成后不会在数据库中留下痕迹。各种测试参数实例（-p后面跟的是mysql的root密码）：单线程测试。测试做了什么。
# mysqlslap -a -uroot -p123456多线程测试。使用–concurrency来模拟并发连接。# mysqlslap -a -c 100 -uroot -p123456迭代测试。用于需要多次执行测试得到平均值。# mysqlslap -a -i 10 -uroot -p123456# mysqlslap ---auto-generate-sql-add-autoincrement -a -uroot -p123456# mysqlslap -a --auto-generate-sql-load-type=read -uroot -p123456# mysqlslap -a --auto-generate-secondary-indexes=3 -uroot -p123456# mysqlslap -a --auto-generate-sql-write-number=1000 -uroot -p123456# mysqlslap --create-schema world -q "select count(*) from City" -uroot -p123456# mysqlslap -a -e innodb -uroot -p123456# mysqlslap -a --number-of-queries=10 -uroot -p123456测试同时不同的存储引擎的性能进行对比：# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -p123456执行一次测试，分别50和100个并发，执行1000次总查询：# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -p12345650和100个并发分别得到一次测试结果(Benchmark)，并发数越多，执行完所有查询的时间越长。为了准确起见，可以多迭代测试几次:# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -p123456
使用语法如下：
# mysqlslap [options]
常用参数 [options] 详细说明：
--auto-generate-sql, -a 自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力。
--auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)。
--auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持。
--number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列，默认1
--number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认1
--number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)
--query=name,-q 使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试。
--create-schema 代表自定义的测试库名称，测试的schema，MySQL中schema也就是database。
--commint=N 多少条DML后提交一次。
--compress, -C 如果服务器和客户端支持都压缩，则压缩信息传递。
--concurrency=N, -c N 表示并发量，也就是模拟多少个客户端同时执行select。可指定多个值，以逗号或者--delimiter参数指定的值做为分隔符。例如：--concurrency=100,200,500。
--engine=engine_name, -e engine_name 代表要测试的引擎，可以有多个，用分隔符隔开。例如：--engines=myisam,innodb。
--iterations=N, -i N 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次。
--only-print 只打印测试语句而不实际执行。
--detach=N 执行N条语句后断开重连。
--debug-info, -T 打印内存和CPU的相关信息。
说明：
测试的过程需要生成测试表，插入测试数据，这个mysqlslap可以自动生成，默认生成一个mysqlslap的schema，如果已经存在则先删除。可以用--only-print来打印实际的测试过程，整个测试完成后不会在数据库中留下痕迹。
各种测试参数实例（-p后面跟的是mysql的root密码）：
单线程测试。测试做了什么。
# mysqlslap -a -uroot -p123456
多线程测试。使用–concurrency来模拟并发连接。
# mysqlslap -a -c 100 -uroot -p123456
迭代测试。用于需要多次执行测试得到平均值。
# mysqlslap -a -i 10 -uroot -p123456
# mysqlslap ---auto-generate-sql-add-autoincrement -a -uroot -p123456
# mysqlslap -a --auto-generate-sql-load-type=read -uroot -p123456
# mysqlslap -a --auto-generate-secondary-indexes=3 -uroot -p123456
# mysqlslap -a --auto-generate-sql-write-number=1000 -uroot -p123456
# mysqlslap --create-schema world -q "select count(*) from City" -uroot -p123456
# mysqlslap -a -e innodb -uroot -p123456
# mysqlslap -a --number-of-queries=10 -uroot -p123456
测试同时不同的存储引擎的性能进行对比：
# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -p123456
执行一次测试，分别50和100个并发，执行1000次总查询：
# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -p123456
50和100个并发分别得到一次测试结果(Benchmark)，并发数越多，执行完所有查询的时间越长。为了准确起见，可以多迭代测试几次:
# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -p123456