使用索引的 MySQL 慢查询，当我通过探查器运行它时并不慢

Question

在我的慢查询日志中，我看到像这样的慢查询

# 时间:121107 16:34:02
# User@Host: web_node[web_node] @ localhost [127.0.0.1]
# Thread_id: 34436186 Schema: test_db Last_errno: 0 Killed: 0
# Query_time: 1.413751 Lock_time: 0.000222 Rows_sent: 203 Rows_examined: 203 Rows_affected: 0 Rows_read: 203
# Bytes_sent: 7553 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# InnoDB_trx_id: 9B04384
设置时间戳=1352334842；
SELECT ID，电子邮件，TEST_DATA WHERE ID IN(13089576,3002681,3117763,1622233,2941590,12305279,1732672,2446772,3189510,13084725,4943929,5855071,6572137,2266261,3003496,2024860,3336832,13758671,6477694,1796684， 13001771,4690025,1071744,1017876,5175795,795988,1619821,2481819,2941090,4770802,13438250,3254708,2323402,526303,13219855,3313573,3190479,1733761,3300577,2941758,6474118,1733379,11523598,4205064,6521805， 2492903,1860388,3337093,5205317,1213970,5442738,12194039,1214203,12970536,3076611,3126152,3677156,5305021,2751587,4954875,875480,2105172,5309382,12981920,5204330,13729768,3254503,5030441,2680750,590661， 1338572,7272410,1860386,2567550,5434143,1918035,5329411,1683235,3254119,5175784,1855380,3336834,2102567,4749746,37269,3207031,6464336,2227907,2713471,3937600,2940442,2233821,5619141,5204711,5988803， 5050821,10109926,5226877,5050275,1874115,13677832,5338699,2423773,6432937,6443660,1990611,6090667,6527411,6568731,3254846,3414049,2011907,5180984,12178711,8558260,3130655,5864745,2059318,34 80233,2104948,2387703,1939395,5356002,2681209,1184622,1184456,10390165,510854,7983305,795991,2622393,4490187,9436477,5356051,2423464,5205318,1600499,13623229,3255205,12200483,6477706,3445661,5226284， 1176639,13760962,2101681,6022818,12909371,1732457,2377496,7260091,12191702,2492899,2630691,13047691,1684470,9382108,2233737,13117701,1796698,2535914,4941741,4565958,1100410,2321180,13080467,813342,4563877， 4689365,2104756,1102802,2714488,3188947,1599770,1558291,5592740,5233428,5204830,1574452,3188956,13693326,2102349,3704111,1748303,790889,9323280,4741494,2387900,5338213,3583795,2283942,3189482,3002296， 4490123,3585020,962926,3481423,1600920,1682364,4693123,6487778,2677582,2377195)；

当我使用 SQL_NO_CACHE 通过探查器运行慢查询时，它看起来说

203 行(0.03 秒)

显示查询 33 的配置文件；
+--------------+--------------+
|状态 |持续时间 |
+--------------+--------------+
|开始| 0.000187 |
|检查权限 | 0.000012 |
|开 table | 0.000034 |
|系统锁 | 0.000016 |
|初始化 | 0.000087 |
|优化 | 0.000024 |
|统计| 0.028694 |
|准备| 0.000074 |
|执行 | 0.000005 |
|发送数据 | 0.001596 |
|结束 | 0.000009 |
|查询结束 | 0.000008 |
|关闭表| 0.000014 |
|释放元素| 0.001600 |
|日志慢查询| 0.000007 |
|清理 | 0.000011 |
+--------------+--------------+

当我用解释运行查询时，它说

+----+--------------+------------------+-------+--- ---------------+---------+---------+------+------ +-------------------------+
|身份证 |选择类型 |表|类型 |可能的 key |关键| key 长度 |引用 |行 |额外 |
+----+--------------+------------------+-------+--- ---------------+---------+---------+------+------ +-------------------------+
| 1 |简单 |测试数据|范围| PRIMARY,id_email | id_email | 4 |空 | 203 |使用哪里；使用索引 |
+----+--------------+------------------+-------+--- ---------------+---------+---------+------+------ +-------------------------+

创建表看起来像

创建表`test_data`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(254) 默认为空，
`domain` varchar(254) 默认为空，
`age` smallint(6) 默认为空，
`gender` tinyint(1) 默认 NULL，
`location_id` int(11) 无符号默认 NULL，
`created` 时间戳 NOT NULL DEFAULT '0000-00-00 00:00:00',
`unistall_date` 时间戳 NOT NULL DEFAULT '0000-00-00 00:00:00',
`subscription_date` 时间戳 NOT NULL DEFAULT '0000-00-00 00:00:00',
`active` tinyint(1) DEFAULT '1',
主键(`id`)，
唯一键`email`(`email`)，
KEY`域`(`域`)，
KEY`id_email`(`id`,`email`),
KEY `email_id` (`email`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13848530 默认字符集=utf8

还有另一个查询会定期运行，从电子邮件地址列表中选择 id 和电子邮件，因此电子邮件、id 键、电子邮件地址需要是唯一的，因此为什么这是唯一键。该表只有 ~14M 行

我想也许索引在内存和交换方面变得很大，但盒子有 8 演出内存。

SELECT table_schema "Data Base Name", SUM(data_length + index_length)/1024/1024 "Data Base Size in MB", SUM(index_length)/1024/1024 "Index Size in MB"FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+----------------------+----- -------------+
|数据库名称 |数据库大小 (MB) |索引大小 (MB) |
+--------------------+----------------------+----- -------------+
|指标 | 3192.50000000 | 1594.42187500 |
|数据| 8096.48437500 | 5639.51562500 |
|原始数据 | 6000.35937500 | 745.07812500 |
| information_schema | 0.00878906 | 0.00878906 |
| mysql | 0.04319191 | 0.04101563 |
|性能模式| 0.00000000 | 0.00000000 |
+--------------------+----------------------+----- -------------+

在 my.cnf 文件中设置 innodb_file_per_table=1 似乎已经解决了这个问题。

这改善了执行时间，我的理解是每个表只有一个文件意味着磁盘针不需要移动这么大的距离。

问题

如果可以使用索引评估查询，为什么设置 innodb_file_per_table=1 会提高性能

为什么在不使用缓存的情况下为探查器运行查询时查询不会变慢？

我的主键应该是 (id, email) 吗？

更新

最初没有/etc/my.cnf 文件，然后我用以下内容创建了一个

[mysqld]
服务器 ID=1
最大连接数=1500
key_buffer_size=50M
query_cache_limit=16M
query_cache_size=256M
线程缓存=16
table_open_cache=4096
sort_buffer_size=512K
join_buffer_size=8M
read_buffer_size=8M
skip_name_resolve=1
thread_cache_size=256
innodb_buffer_pool_size=6G
innodb_buffer_pool_instances=1
innodb_thread_concurrency=96
innodb_additional_mem_pool_size=32M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=0
innodb_log_file_size=256M
innodb_flush_method=O_DIRECT
innodb_file_per_table=1
net_read_timeout=15
net_write_timeout=30
日志-bin=mysql-bin
同步二进制日志=0
数据目录=/var/lib/mysql

Answer 1

您的 innodb_log_buffer 数据过多。

什么是值(value)观:

innodb_buffer_pool_size 
innodb_log_file_size 

所有的 InnoDB 都必须在内存中运行。当您拆分文件时，它的运行效率更高，因为它以较少的磁盘读取和写入换入和换出内存，因为一个较大的文件需要更长的时间来扫描数据。

它没有交换，因为您的 innodb_buffer_pool_size 限制了 MySQL 加载到内存中的内存量。

解决问题的唯一方法是获得更多内存并为所有 innodb 表和索引分配足够的 innodb_buffer_pool_size。