http://hi.baidu.com/xiaohuo37/blog/item/07b2512c37deb8ec8a139907.html
1、检验 key_buffer_size 参数大小是否合适(适用 MyISAM 表)
key_buffer_size 指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads,可以知道 key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低,至少是1:100,1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)(检查状态值,在查询工具里输入 SHOW STATUS ,执行)。
key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表,但是内部的临时磁盘表是MyISAM表,也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。
对于1G内存的机器,如果不使用MyISAM表,推荐值是16M(8-64M)。
案例1:健康状况
key_buffer_size – 402649088 (384M)
key_read_requests – 597579931
key_reads - 56188
案例2:警报状态
key_buffer_size – 16777216 (16M)
key_read_requests – 597579931
key_reads - 53832731
案例1中比例低于1:10000,是健康的情况;案例2中比例达到1:11,警报已经拉响。
2、查询缓存 query_cache_size 设置
从 4.0.1 开始,MySQL 提供了查询缓冲机制。在启用查询缓冲的情况下,MySQL 将 SELECT 语句和查询结果存放在缓冲区中(内存),之后对于同样的 SELECT 查询语句(区分大小写),将直接从缓冲区中读取结果,避免了重复查询的无谓开销。和查询缓存相关的参数包括:Qcache_free_blocks、 Qcache_lowmem_prunes、Qcache_free_memory、Qcache_not_cached、 Qcache_total_blocks、Qcache_queries_in_cache、Qcache_hits、Qcache_inserts。其 中,如果 Qcache_lowmem_prunes 的值很大,说明经常出现缓冲不够的情况(最好保持在零),同时 Qcache_hits 的值非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小 Qcache_hits 的值不大,则表明你的查询重复率很低,这种情况下使用查询缓冲反而会影响效率,那么可以考虑不用查询缓冲。另外,如果 Qcache_free_blocks 的值非常大,则表明缓冲区中碎片很多。
3、table_cache
吴威 (16:59:27):
table_cache
The number of open tables for all threads ????
For more information about the table cache, see section 7.4.8 How MySQL Opens and Closes Tables
table_cache 用于指定表高速缓存的大小。每当 MySQL 访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查运行峰值时间的 Open_tables 和 Opened_tables 状态值,可以决定是否需要调整 table_cache 的值。如果你发现 open_tables 的值等于 table_cache,并且发现 opened_tables 状态值在不断增长,那么你就需要增加 table_cache 参数值了(上述状态值可以使用 SHOW STATUS LIKE ‘Open%tables’ 命令获得)。注意,不能盲目地把 table_cache 参数设置成很大的值,如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。
对于有1G内存的机器,推荐值是128-256。
案例1:该案例来自一个不是特别繁忙的服务器
table_cache – 512
open_tables – 103
opened_tables – 1273
uptime – 4021421 (measured in seconds)
该案例中table_cache似乎设置得太高了。在峰值时间,打开表的数目比table_cache要少得多。
案例2:该案例来自一台开发服务器。
table_cache – 64
open_tables – 64
opened-tables – 431
uptime – 1662790 (measured in seconds)
虽然open_tables已经等于table_cache,但是相对于服务器运行时间来说,opened_tables的值也非常低。因此,增加table_cache的值应该用处不大。
案例3:该案例来自一个upderperforming的服务器
table_cache – 64
open_tables – 64
opened_tables – 22423
uptime – 19538
该案例中table_cache设置得太低了。虽然运行时间不到6小时,open_tables达到了最大值,opened_tables的值也非常高。这样就需要增加table_cache的值。
4、Measuring Key Buffer Usage
When you add indexes to your data, it enables MySQL to find data faster. However, ideally you want to have these indexes stored in RAM for maximum speed, and the variable key_buffer_size defines how much RAM MySQL can allocate for index key caching. If MySQL cannot store its indexes in RAM, you will experience serious performance problems. Fortunately, most databases have relatively small key buffer requirements, but you should measure your usage to see what work needs to be done.
To do this, log in to MySQL and type SHOW STATUS LIKE ‘%key_read%’;. That returns all the status fields that describe the hit rate of your key buffer—you should get two rows back: Key_reads and Key_read_requests, which are the number of keys being read from disk and the number of keys being read from the key buffer. From these two numbers you can calculate the percentage of requests being filled from RAM and from disk, using this simple equation:
100 – ((Key_reads / Key_read_requests) x 100)
That is, you divide Key_reads by Key_read_requests, multiply the result by 100 and then subtract the result from 100. For example, if you have Key_reads of 1000 and Key_read_requests of 100000, you divide 1000 by 100000 to get 0.01; then you multiply that by 100 to get 1.0, and subtract that from 100 to get 99. That number is the percentage of key reads being served from RAM, which means 99% of your keys are served from RAM.
Most people should be looking to get more than 95% served from RAM, although the primary exception is if you update or delete rows very often—MySQL can’t cache what keeps changing. If your site is largely read only, this should be around 98%. Lower figures mean you might need to bump up the size of your key buffer.
If you are seeing problems, the next step is to check how much of your current key buffer is being used. Use the SHOW VARIABLES command and look up the value of the key_buffer_size variable. It is probably something like 8388600, which is eight million bytes, or 8MB. Now, use the SHOW STATUS command and look up the value of Key_blocks_used.
You can now determine how much of your key buffer is being used by multiplying Key_blocks_used by 1024, dividing by key_buffer_size, and multiplying by 100. For example, if Key_blocks_used is 8000, you multiply that by 1024 to get 8192000; then you divide that by your key_buffer_size (8388600) to get 0.97656, and finally multiplying that by 100 to get 97.656. Thus, almost 98% of your key buffer is being used.
Now, onto the important part: You have ascertained that you are reading lots of keys from disk, and you also now know that the reason for reading from disk is almost certainly because you do not have enough RAM allocated to the key buffer. A general rule of thumb is to allocate as much RAM to the key buffer as you can, up to a maximum of 25% of system RAM—128MB on a 512MB system is about the ideal for systems that read heavily from keys. Beyond that, you will actually see drastic performance decreases because the system has to use virtual memory for the key buffer.
http://hi.baidu.com/dkf198314/blog/item/ffaac180d117339af603a685.html
1, 查看MySQL 服务器配置信息
mysql> show variables;
mysql> show variables;
2, 查看MySQL服务器运行的各种状态值
mysql> show global status;
mysql> show global status;
3, 慢查询
mysql> show variables like '%slow%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| log_slow_queries | OFF |
| slow_launch_time | 2 |
+------------------+-------+
mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Slow_launch_threads | 0 |
| Slow_queries | 279 |
+---------------------+-------+
mysql> show variables like '%slow%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| log_slow_queries | OFF |
| slow_launch_time | 2 |
+------------------+-------+
mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Slow_launch_threads | 0 |
| Slow_queries | 279 |
+---------------------+-------+
配置中关闭了记录慢查询(最好是打开,方便优化 ),超过2秒即为慢查询,一共有279条慢查询
4, 连接数
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 500 |
+-----------------+-------+
mysql> show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 498 |
+----------------------+-------+
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 500 |
+-----------------+-------+
mysql> show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 498 |
+----------------------+-------+
设置的最大连接数是500,而响应的连接数是498
max_used_connections / max_connections * 100% = 99.6% (理想值 ≈ 85%)
5, key_buffer_size
key_buffer_size是对MyISAM表性能影响最大的一个参数, 不过数据库中多为Innodb
mysql> show variables like 'key_buffer_size';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| key_buffer_size | 67108864 |
+-----------------+----------+
mysql> show global status like 'key_read%';
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| Key_read_requests | 25629497 |
| Key_reads | 66071 |
+-------------------+----------+
mysql> show variables like 'key_buffer_size';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| key_buffer_size | 67108864 |
+-----------------+----------+
mysql> show global status like 'key_read%';
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| Key_read_requests | 25629497 |
| Key_reads | 66071 |
+-------------------+----------+
一共有25629497个索引读取请求,有66071个请求在内存中没有找到直接从硬盘读取索引,计算索引未命中缓存的概率:
key_cache_miss_rate = Key_reads / Key_read_requests * 100% =0.27%
需要适当加大key_buffer_size
mysql> show global status like 'key_blocks_u%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Key_blocks_unused | 10285 |
| Key_blocks_used | 47705 |
+-------------------+-------+
mysql> show global status like 'key_blocks_u%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Key_blocks_unused | 10285 |
| Key_blocks_used | 47705 |
+-------------------+-------+
Key_blocks_unused表示未使用的缓存簇(blocks)数,Key_blocks_used表示曾经用到的最大的blocks数
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 18% (理想值 ≈ 80%)
6, 临时表
mysql> show global status like 'created_tmp%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Created_tmp_disk_tables | 4184337 |
| Created_tmp_files | 4124 |
| Created_tmp_tables | 4215028 |
+-------------------------+---------+
mysql> show global status like 'created_tmp%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Created_tmp_disk_tables | 4184337 |
| Created_tmp_files | 4124 |
| Created_tmp_tables | 4215028 |
+-------------------------+---------+
每次创建临时表,Created_tmp_tables增加,如果是在磁盘上创建临时表,Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数:
Created_tmp_disk_tables / Created_tmp_tables * 100% = 99% (理想值<= 25%)
mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');
+---------------------+-----------+
| Variable_name | Value |
+---------------------+-----------+
| max_heap_table_size | 134217728 |
| tmp_table_size | 134217728 |
+---------------------+-----------+
mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');
+---------------------+-----------+
| Variable_name | Value |
+---------------------+-----------+
| max_heap_table_size | 134217728 |
| tmp_table_size | 134217728 |
+---------------------+-----------+
需要增加tmp_table_size
7,open table 的情况
mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 1024 |
| Opened_tables | 1465 |
+---------------+-------+
mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 1024 |
| Opened_tables | 1465 |
+---------------+-------+
Open_tables 表示打开表的数量,Opened_tables表示打开过的表数量,如果Opened_tables数量过大,说明配置中 table_cache(5.1.3之后这个值叫做table_open_cache)值可能太小,我们查询一下服务器table_cache值
mysql> show variables like 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache | 1024 |
+---------------+-------+
mysql> show variables like 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache | 1024 |
+---------------+-------+
Open_tables / Opened_tables * 100% =69% 理想值 (>= 85%)
Open_tables / table_cache * 100% = 100% 理想值 (<= 95%)
8, 进程使用情况
mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 31 |
| Threads_connected | 239 |
| Threads_created | 2914 |
| Threads_running | 4 |
+-------------------+-------+
mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 31 |
| Threads_connected | 239 |
| Threads_created | 2914 |
| Threads_running | 4 |
+-------------------+-------+
如果我们在MySQL服务器配置文件中设置了thread_cache_size,当客户端断开之后,服务器处理此客户的线程将会缓存起来以响应下一个客 户而不是销毁(前提是缓存数未达上限)。Threads_created表示创建过的线程数,如果发现Threads_created值过大的话,表明 MySQL服务器一直在创建线程,这也是比较耗资源,可以适当增加配置文件中thread_cache_size值,查询服务器 thread_cache_size配置:
mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 32 |
+-------------------+-------+
mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 32 |
+-------------------+-------+
9, 查询缓存(query cache)
mysql> show global status like 'qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 2226 |
| Qcache_free_memory | 10794944 |
| Qcache_hits | 5385458 |
| Qcache_inserts | 1806301 |
| Qcache_lowmem_prunes | 433101 |
| Qcache_not_cached | 4429464 |
| Qcache_queries_in_cache | 7168 |
| Qcache_total_blocks | 16820 |
+-------------------------+----------+
mysql> show global status like 'qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 2226 |
| Qcache_free_memory | 10794944 |
| Qcache_hits | 5385458 |
| Qcache_inserts | 1806301 |
| Qcache_lowmem_prunes | 433101 |
| Qcache_not_cached | 4429464 |
| Qcache_queries_in_cache | 7168 |
| Qcache_total_blocks | 16820 |
+-------------------------+----------+
Qcache_free_blocks:缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理,从而得到一个空闲块。
Qcache_free_memory:缓存中的空闲内存。
Qcache_hits:每次查询在缓存中命中时就增大
Qcache_inserts:每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。
Qcache_lowmem_prunes:缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看;如果这个数字在 不断增长,就表示可能碎片非常严重,或者内存很少。(上面的 free_blocks和free_memory可以告诉您属于哪种情况)
Qcache_not_cached:不适合进行缓存的查询的数量,通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
Qcache_queries_in_cache:当前缓存的查询(和响应)的数量。
Qcache_total_blocks:缓存中块的数量。
我们再查询一下服务器关于query_cache的配置:
mysql> show variables like 'query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit | 33554432 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
mysql> show variables like 'query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit | 33554432 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
各字段的解释:
query_cache_limit:超过此大小的查询将不缓存
query_cache_min_res_unit:缓存块的最小大小
query_cache_size:查询缓存大小
query_cache_type:缓存类型,决定缓存什么样的查询,示例中表示不缓存 select sql_no_cache 查询
query_cache_wlock_invalidate:当有其他客户端正在对MyISAM表进行写操作时,如果查询在query cache中,是否返回cache结果还是等写操作完成再读表获取结果。
query_cache_min_res_unit的配置是一柄”双刃剑”,默认是4KB,设置值大对大数据查询有好处,但如果你的查询都是小数据查询,就容易造成内存碎片和浪费。
查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
如果查询缓存碎片率超过20%,可以用FLUSH QUERY CACHE整理缓存碎片,或者试试减小query_cache_min_res_unit,如果你的查询都是小数据量的话。
查询缓存利用率 = (query_cache_size
怎么配置MySQL服务器,但考虑到服务器硬件配置的不同,具体应用的差别,那些文章的做法只能作为初步设置参考,我们需要根据自己的情况进行配置优化,好的做法是MySQL服务器稳定运行了一段时间后运行,根据服务器的”状态”进行优化。
mysql> show global status;
可以列出MySQL服务器运行各种状态值,另外,查询MySQL服务器配置信息语句:
mysql> show variables;
一、慢查询
mysql> show variables like '%slow%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
+------------------+-------+
mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Slow_launch_threads | 0 |
| Slow_queries | 4148 |
+---------------------+-------+
配置中打开了记录慢查询,执行时间超过2秒的即为慢查询,系统显示有4148个慢查询,你可以分析慢查询日志,找出有问题的SQL语句,慢查询时间不 宜设置过长,否则意义不大,最好在5秒以内,如果你需要微秒级别的慢查询,可以考虑给MySQL打补丁:http://www.percona.com /docs/wiki/release:start,记得找对应的版本。
打开慢查询日志可能会对系统性能有一点点影响,如果你的MySQL是主-从结构,可以考虑打开其中一台从服务器的慢查询日志,这样既可以监控慢查询,对系统性能影响又小。
二、连接数
经常会遇见”MySQL: ERROR 1040: Too many connections”的情况,一种是访问量确实很高,MySQL服务器抗不住,这个时候就要考虑增加从服务器分散读压力,另外一种情况是MySQL配 置文件中max_connections值过小:
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 256 |
+-----------------+-------+
这台MySQL服务器最大连接数是256,然后查询一下服务器响应的最大连接数:
mysql> show global status like 'Max_used_connections';
MySQL服务器过去的最大连接数是245,没有达到服务器连接数上限256,应该没有出现1040错误,比较理想的设置是:
Max_used_connections / max_connections * 100% ≈ 85%
最大连接数占上限连接数的85%左右,如果发现比例在10%以下,MySQL服务器连接数上限设置的过高了。
三、Key_buffer_size
key_buffer_size是对MyISAM表性能影响最大的一个参数,下面一台以MyISAM为主要存储引擎服务器的配置:
mysql> show variables like 'key_buffer_size';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| key_buffer_size | 536870912 |
+-----------------+------------+
分配了512MB内存给key_buffer_size,我们再看一下key_buffer_size的使用情况:
mysql> show global status like 'key_read%';
+------------------------+-------------+
| Variable_name | Value |
+------------------------+-------------+
| Key_read_requests | 27813678764 |
| Key_reads | 6798830 |
+------------------------+-------------+
一共有27813678764个索引读取请求,有6798830个请求在内存中没有找到直接从硬盘读取索引,计算索引未命中缓存的概率:
key_cache_miss_rate = Key_reads / Key_read_requests * 100%
比如上面的数据,key_cache_miss_rate为0.0244%,4000个索引读取请求才有一个直接读硬盘,已经很BT 了,key_cache_miss_rate在0.1%以下都很好(每1000个请求有一个直接读硬盘),如果key_cache_miss_rate在 0.01%以下的话,key_buffer_size分配的过多,可以适当减少。
MySQL服务器还提供了key_blocks_*参数:
mysql> show global status like 'key_blocks_u%';
+------------------------+-------------+
| Variable_name | Value |
+------------------------+-------------+
| Key_blocks_unused | 0 |
| Key_blocks_used | 413543 |
+------------------------+-------------+
Key_blocks_unused表示未使用的缓存簇(blocks)数,Key_blocks_used表示曾经用到的最大的blocks数,比 如这台服务器,所有的缓存都用到了,要么增加key_buffer_size,要么就是过渡索引了,把缓存占满了。比较理想的设置:
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%
四、临时表
mysql> show global status like 'created_tmp%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Created_tmp_disk_tables | 21197 |
| Created_tmp_files | 58 |
| Created_tmp_tables | 1771587 |
+-------------------------+---------+
每次创建临时表,Created_tmp_tables增加,如果是在磁盘上创建临时表,Created_tmp_disk_tables也增 加,Created_tmp_files表示MySQL服务创建的临时文件文件数,比较理想的配置是: Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%
Handler_read_key
根据键读一行的请求数。如果较高,说明查询和表的索引正确。
Handler_read_next
按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列,该值增加。
Handler_read_rnd_next
在数据文件中读下一行的请求数。如果你正进行大量的表扫描,该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。
举例说明,以下语句where 条件s_lasttime有索引。另外在执行查询语句之前关闭查询缓存。
mysql> set query_cache_type=0;
Query OK, 0 rows affected (0.00 sec)
没有执行查询语句之前的状态值:
mysql> show global status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 3 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 28 |
+-----------------------+-------+
6 rows in set (0.00 sec)
mysql> select i_uid from alluser where s_lasttime >= '2008-10-11 08:00:00' and s_lasttime <= '2008-10-11 08:01:10' ;
+----------+
| i_uid |
+----------+
| 34450409 |
| 35283490 |
| 33006053 |
| 29974553 |
| 32078303 |
| 31638119 |
| 35733904 |
+----------+
7 rows in set (0.06 sec)
执行查询语句之后的状态值:
mysql> show global status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 3 |
| Handler_read_key | 1 |
| Handler_read_next | 7 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 35 |
+-----------------------+-------+
6 rows in set (0.00 sec)
可以发现:Handler_read_key值加1, Handler_read_next 值加7,Handler_read_rnd_next值加7
Handler_read_key加1不太理解,查询是用到索引了,这个值才增加,如果没用到索引是不会增加的。
Handler_read_next是因为查询语句从索引中扫描到7行,故加7
Handler_read_rnd_next是执行show global status like 'Handler_read%';没有用到索引,有6行结果。