加速 mySQL 查询并避免“复制到磁盘上的 tmp 表”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20152797/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-31 19:30:52 来源:igfitidea点击:
Speeding up mySQL query and avoiding 'copying to tmp table on disk'
提问by Brian Schroeter
I have a database server with over 60G of RAM and SSD drives in RAID10. I'm trying to get a query to run that'll return millions of records (3-6M, most likely). I'm using the following configuration for mySQL --
我有一个数据库服务器，在 RAID10 中有超过 60G 的 RAM 和 SSD 驱动器。我正在尝试运行一个查询，该查询将返回数百万条记录（最有可能是 3-6M）。我正在为 mySQL 使用以下配置——
[mysqld]
max_connections = 500
skip-external-locking
key_buffer = 32M
open_files_limit = 65535
table_cache = 9552
thread_cache = 50
#table-definition-cache = 4096
#table-open-cache = 10240
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 16M
query_cache_size = 512M
join_buffer_size = 1024M
max_heap_table_size = 20G
tmp_table_size = 20G
wait_timeout = 120
interactive_timeout = 120
#innodb-flush-method = O_DIRECT
#innodb-log-files-in-group = 2
#innodb-log-file-size = 512M
#innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 32G
innodb_autoextend_increment=512
innodb_thread_concurrency=18
innodb_locks_unsafe_for_binlog = 1
innodb_lock_wait_timeout=300
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
key_buffer_size = 10G
query_cache_limit = 256M
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 16M
sort_buffer_size = 10M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 16M
sort_buffer_size = 10M
read_buffer = 2M
write_buffer = 2M
The query I'm running is...:
我正在运行的查询是...：
SELECT DISTINCT
Import_AcesApplication.id,
Import_AcesApplication.sku,
Parts.partterminologyname,
BaseVehicle.YearID,
Make.MakeName,
Model.modelname,
SubModel.SubModelName,
CONCAT(EngineBase.Cylinders, ' CYL ', EngineBase.Liter, EngineBase.BlockType),
Positions.position
FROM
Import_AcesApplication
STRAIGHT_JOIN BaseVehicle
ON Import_AcesApplication.base_vehicle_id=BaseVehicle.BaseVehicleID
STRAIGHT_JOIN Parts
ON Import_AcesApplication.part_type_id=Parts.PartTerminologyID
STRAIGHT_JOIN Make
ON BaseVehicle.MakeID=Make.MakeID
STRAIGHT_JOIN Model
ON BaseVehicle.ModelID=Model.ModelID
STRAIGHT_JOIN Vehicle
ON Import_AcesApplication.base_vehicle_id=Vehicle.BaseVehicleID
STRAIGHT_JOIN SubModel
ON Vehicle.SubModelID=SubModel.SubModelID
STRAIGHT_JOIN VehicleConfig
ON Vehicle.VehicleID=VehicleConfig.VehicleID
STRAIGHT_JOIN EngineConfig
ON VehicleConfig.EngineConfigID=EngineConfig.EngineConfigID
STRAIGHT_JOIN EngineDesignation
ON EngineConfig.EngineDesignationID=EngineDesignation.EngineDesignationID
STRAIGHT_JOIN EngineVIN
ON EngineConfig.EngineVINID=EngineVIN.EngineVINID
STRAIGHT_JOIN EngineBase
ON EngineConfig.EngineBaseID=EngineBase.EngineBaseID
STRAIGHT_JOIN Positions
ON Positions.PositionID=Import_AcesApplication.position_id
EDIT: I've altered the query a bit...
编辑：我稍微改变了查询......
SELECT DISTINCT
Import_AcesApplication.id,
Import_AcesApplication.sku,
Parts.partterminologyname,
BaseVehicle.YearID,
Make.MakeName,
Model.modelname,
SubModel.SubModelName,
CONCAT(EngineBase.Cylinders, ' CYL ', EngineBase.Liter, EngineBase.BlockType),
Positions.position
FROM
Import_AcesApplication
STRAIGHT_JOIN BaseVehicle
ON Import_AcesApplication.base_vehicle_id=BaseVehicle.BaseVehicleID
STRAIGHT_JOIN Parts
ON Import_AcesApplication.part_type_id=Parts.PartTerminologyID
STRAIGHT_JOIN Make
ON BaseVehicle.MakeID=Make.MakeID
STRAIGHT_JOIN Model
ON BaseVehicle.ModelID=Model.ModelID
STRAIGHT_JOIN Vehicle
ON Import_AcesApplication.base_vehicle_id=Vehicle.BaseVehicleID
STRAIGHT_JOIN SubModel
ON Vehicle.SubModelID=SubModel.SubModelID
STRAIGHT_JOIN VehicleConfig
ON Vehicle.VehicleID=VehicleConfig.VehicleID
STRAIGHT_JOIN EngineConfig
ON VehicleConfig.EngineConfigID=EngineConfig.EngineConfigID
STRAIGHT_JOIN EngineBase
ON EngineConfig.EngineBaseID=EngineBase.EngineBaseID
STRAIGHT_JOIN Positions
ON Positions.PositionID=Import_AcesApplication.position_id
I'm using STRAIGHT_JOIN to force the order since SELECT EXPLAIN showed it to be incorrect. I've setup my indexes on the tables, but the query seems to be getting stuck on the state of 'copying to tmp table on the disk'. I've tried different tips online like increasing the tmp_table_size and whatnot but nothing has helped.
我正在使用 STRAIGHT_JOIN 来强制顺序，因为 SELECT EXPLAIN 显示它不正确。我已经在表上设置了索引，但查询似乎陷入了“复制到磁盘上的 tmp 表”的状态。我在网上尝试了不同的技巧，比如增加 tmp_table_size 等等，但没有任何帮助。
Can someone please help me figure this out so that the queries are faster?
有人可以帮我解决这个问题，以便查询更快吗？
EDIT: The EXPLAIN results can be seen here.
回答by Louis Ricci
Have you tried a tmpfs? (Using a RAM disk as your temp table storage)
你试过 tmpfs 吗？（使用 RAM 磁盘作为临时表存储）
Create the tmp directory
创建 tmp 目录
# mkdir -p /var/mysqltmp
Set permissions
设置权限
# chown mysql:mysql /var/mysqltmp
Determine mysql user id
确定mysql用户ID
# id mysql
Edit /etc/fstab And add the following line, replacing your specific mysql user id and group id instead of the 105 and 114 below:
编辑 /etc/fstab 并添加以下行，替换您特定的 mysql 用户 ID 和组 ID，而不是下面的 105 和 114：
tmpfs /var/mysqltmp tmpfs rw,gid=105,uid=114,size=10G,nr_inodes=10k,mode=0700 0 0
Mount the new tmpfs partition
挂载新的 tmpfs 分区
# mount -a
Change your MySQL configuration
更改您的 MySQL 配置
# vi /etc/mysql/conf.d/local.cnf
Change, or add the following line:
更改或添加以下行：
tmpdir = /var/mysqltmp
Restart MySQL
重启 MySQL
/etc/init.d/mysql restart
回答by Brian Schroeter
InnoDB: Error: Table “mysql”.”innodb_table_stats” not found.
: Error: Table "mysql"."innodb_table_stats" not found.
Follow the below steps:
请按照以下步骤操作：
mysql> `select table_name from information_schema.tables where table_schema='mysql' and engine='InnoDB';`
+----------------------+
| table_name |
+----------------------+
| innodb_index_stats |
| innodb_table_stats |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
+----------------------+
5 rows in set (0.00 sec)
Physical files:
物理文件：
innodb_index_stats.frm
innodb_index_stats.ibd
innodb_table_stats.frm
innodb_table_stats.ibd
slave_master_info.frm
slave_master_info.ibd
slave_relay_log_info.frm
slave_relay_log_info.ibd
slave_worker_info.frm
slave_worker_info.ibd
1.
1.
drop table innodb_index_stats;
drop table innodb_table_stats;
drop table slave_master_info;
drop table slave_relay_log_info;
drop table slave_worker_info;
2.
2.
delete all .frm & .ibd of the tables above.
rm -rf innodb_index_stats.frm
rm -rf innodb_index_stats.ibd
...
Create below tables :
CREATE TABLE `innodb_index_stats` ( `database_name` varchar(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(64) COLLATE utf8_bin NOT NULL, `index_name` varchar(64) COLLATE utf8_bin NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `stat_name` varchar(64) COLLATE utf8_bin NOT NULL, `stat_value` bigint(20) unsigned NOT NULL, `sample_size` bigint(20) unsigned DEFAULT NULL, `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; CREATE TABLE `innodb_table_stats` ( `database_name` varchar(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(64) COLLATE utf8_bin NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `n_rows` bigint(20) unsigned NOT NULL, `clustered_index_size` bigint(20) unsigned NOT NULL, `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL, PRIMARY KEY (`database_name`,`table_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; CREATE TABLE `slave_master_info` ( `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.', `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.', `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.', `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.', `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.', `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.', `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.', `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.', `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.', `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.', `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.', `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.', `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.', `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.', `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.', `Heartbeat` float NOT NULL, `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server', `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs', `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.', `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.', `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)', `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files', `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.', PRIMARY KEY (`Host`,`Port`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information'; CREATE TABLE `slave_relay_log_info` ( `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.', `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.', `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.', `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.', `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.', `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.', `Number_of_workers` int(10) unsigned NOT NULL, `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.', PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information'; CREATE TABLE `slave_worker_info` ( `Id` int(10) unsigned NOT NULL, `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Relay_log_pos` bigint(20) unsigned NOT NULL, `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Master_log_pos` bigint(20) unsigned NOT NULL, `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL, `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL, `Checkpoint_seqno` int(10) unsigned NOT NULL, `Checkpoint_group_size` int(10) unsigned NOT NULL, `Checkpoint_group_bitmap` blob NOT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';
创建下表：
CREATE TABLE `innodb_index_stats` ( `database_name` varchar(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(64) COLLATE utf8_bin NOT NULL, `index_name` varchar(64) COLLATE utf8_bin NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `stat_name` varchar(64) COLLATE utf8_bin NOT NULL, `stat_value` bigint(20) unsigned NOT NULL, `sample_size` bigint(20) unsigned DEFAULT NULL, `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; CREATE TABLE `innodb_table_stats` ( `database_name` varchar(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(64) COLLATE utf8_bin NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `n_rows` bigint(20) unsigned NOT NULL, `clustered_index_size` bigint(20) unsigned NOT NULL, `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL, PRIMARY KEY (`database_name`,`table_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; CREATE TABLE `slave_master_info` ( `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.', `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.', `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.', `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.', `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.', `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.', `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.', `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.', `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.', `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.', `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.', `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.', `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.', `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.', `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.', `Heartbeat` float NOT NULL, `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server', `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs', `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.', `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.', `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)', `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files', `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.', PRIMARY KEY (`Host`,`Port`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information'; CREATE TABLE `slave_relay_log_info` ( `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.', `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.', `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.', `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.', `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.', `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.', `Number_of_workers` int(10) unsigned NOT NULL, `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.', PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information'; CREATE TABLE `slave_worker_info` ( `Id` int(10) unsigned NOT NULL, `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Relay_log_pos` bigint(20) unsigned NOT NULL, `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Master_log_pos` bigint(20) unsigned NOT NULL, `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL, `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL, `Checkpoint_seqno` int(10) unsigned NOT NULL, `Checkpoint_group_size` int(10) unsigned NOT NULL, `Checkpoint_group_bitmap` blob NOT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';
Hope this solves your issue too.
希望这也能解决您的问题。
