MySql очень медленно считает запросы с объединениями
У меня есть представление для mysql:
CREATE VIEW
loggingquarantine_quarantine ( id, mail_id, partition_tag, content, rs, subject, sender, TIME,
spam_level, size, sid, email ) AS
SELECT
concat(CAST(`mr`.`mail_id` AS CHAR(255) charset utf8),CAST(`mr`.`partition_tag` AS CHAR(255)
charset utf8)) AS `id`,
`mr`.`mail_id` AS `mail_id`,
`mr`.`partition_tag` AS `partition_tag`,
`mr`.`content` AS `content`,
`mr`.`rs` AS `rs`,
`m`.`subject` AS `subject`,
`m`.`from_addr` AS `sender`,
`m`.`time_num` AS `TIME`,
`m`.`spam_level` AS `spam_level`,
`m`.`size` AS `size`,
`m`.`sid` AS `sid`,
`maddr`.`email` AS `email`
FROM
(((`msgrcpt` `mr` JOIN `msgs` `m`
ON
(
`m`.`partition_tag` = `mr`.`partition_tag`
AND
`m`.`mail_id` = `mr`.`mail_id`
)
)
JOIN `maddr` maddr
ON
(
`mr`.`rid` = `maddr`.`id`
)
))
Когда я пытаюсь подсчитать для этого представления, требуется около 13 минут для 2,5 миллионов записей. Это невероятно медленно. Все поля имеют индексы , Если я буду считать на каждом столе, это займет не более 20 секунд. Вот что показывает объяснение mysql:
mysql> explain SELECT COUNT(*) FROM `loggingquarantine_quarantine`;
+----+-------------+-------+--------+-----------------------------------------------------------------------+-------------------------+---------+-----------------------------------------------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------------------------------------------+-------------------------+---------+-----------------------------------------------------------+---------+-------------+
| 1 | SIMPLE | maddr | index | PRIMARY | maddr_partition_tag_idx | 5 | NULL | 1016497 | Using index |
| 1 | SIMPLE | mr | ref | PRIMARY,msgrcpt_idx_rid,msgrcpt_mail_id_idx,msgrcpt_partition_tag_idx | msgrcpt_idx_rid | 8 | mroute_logquar.maddr.id | 2 | Using index |
| 1 | SIMPLE | m | eq_ref | PRIMARY,msgs_mail_id_idx,msgs_partition_tag_idx | PRIMARY | 22 | mroute_logquar.mr.partition_tag,mroute_logquar.mr.mail_id | 1 | Using index |
+----+-------------+-------+--------+-----------------------------------------------------------------------+-------------------------+---------+-----------------------------------------------------------+---------+-------------+
Как мне оптимизировать запрос/представление, чтобы подсчет не занимал 13 минут. Что не так с текущим запросом?
ОБНОВЛЕНИЕ. Если я сделаю выбор непосредственно при выборе без просмотра, он все равно получит тот же 14-минутный запрос.
mysql> select count(1) FROM (((`msgrcpt` `mr` JOIN `msgs` `m` ON ( `m`.`partition_tag` = `mr`.`partition_tag` AND `m`.`mail_id` = `mr`.`mail_id` ) ) JOIN `maddr` maddr ON ( `mr`.`rid` = `maddr`.`id` ) ));
+----------+
| count(1) |
+----------+
| 2582227 |
+----------+
1 row in set (14 min 28.96 sec)
А вот результат для подсчета, когда я делаю это в трех отдельных запросах:
mysql> select count(1) from msgrcpt;
+----------+
| count(1) |
+----------+
| 2587307 |
+----------+
1 row in set (46.02 sec)
mysql> select count(1) from msgs;
+----------+
| count(1) |
+----------+
| 2421710 |
+----------+
1 row in set (7.77 sec)
mysql> select count(1) from maddr;
+----------+
| count(1) |
+----------+
| 994880 |
+----------+
1 row in set (0.23 sec)
ОБНОВЛЕНИЕ 2.
Все таблицы InnoDB.
mysql> SHOW status like 'key_%'; +------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 26792 |
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
+------------------------+-------+
Таблицы msgs и msgrcpt имеют составной первичный ключ (mail_id, partation_tag для msgs и (partition_tag, mail_id, rseqnum) для msgrpt).
ОБНОВИТЬ
Объясните для отдельных таблиц:
mysql> explain select count(1) from msgs;
+----+-------------+-------+-------+---------------+-------------------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------------+---------+------+---------+-------------+
| 1 | SIMPLE | msgs | index | NULL | msgs_idx_time_num | 4 | NULL | 2357360 | Using index |
+----+-------------+-------+-------+---------------+-------------------+---------+------+---------+-------------+
1 row in set (0.00 sec)
mysql> explain select count(1) from msgrcpt;
+----+-------------+---------+-------+---------------+----------------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+----------------+---------+------+---------+-------------+
| 1 | SIMPLE | msgrcpt | index | NULL | msgrcpt_rs_idx | 3 | NULL | 2620758 | Using index |
+----+-------------+---------+-------+---------------+----------------+---------+------+---------+-------------+
1 row in set (0.00 sec)
mysql> explain select count(1) from maddr;
+----+-------------+-------+-------+---------------+-------------------------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------------------+---------+------+--------+-------------+
| 1 | SIMPLE | maddr | index | NULL | maddr_partition_tag_idx | 5 | NULL | 967058 | Using index |
+----+-------------+-------+-------+---------------+-------------------------+---------+------+--------+-------------+
1 row in set (0.00 sec)
UPDATE. Создать таблицу для всех таблиц:
mysql> show create table msgrcpt;
| Table | Create Table
| msgrcpt | CREATE TABLE `msgrcpt` (
`partition_tag` int(11) NOT NULL DEFAULT '0',
`mail_id` varbinary(16) NOT NULL,
`rseqnum` int(11) NOT NULL DEFAULT '0',
`rid` bigint(20) unsigned NOT NULL,
`is_local` char(1) NOT NULL DEFAULT '',
`content` char(1) NOT NULL DEFAULT '',
`ds` char(1) NOT NULL,
`rs` char(1) NOT NULL,
`bl` char(1) DEFAULT '',
`wl` char(1) DEFAULT '',
`bspam_level` float DEFAULT NULL,
`smtp_resp` varchar(255) DEFAULT '',
PRIMARY KEY (`partition_tag`,`mail_id`,`rseqnum`),
KEY `msgrcpt_idx_rid` (`rid`),
KEY `msgrcpt_mail_id_idx` (`mail_id`),
KEY `msgrcpt_rs_idx` (`rs`),
KEY `msgrcpt_ds_idx` (`ds`),
KEY `msgrcpt_partition_tag_idx` (`partition_tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
| msgs | CREATE TABLE `msgs` (
`partition_tag` int(11) NOT NULL DEFAULT '0',
`mail_id` varbinary(16) NOT NULL,
`secret_id` varbinary(16) DEFAULT '',
`am_id` varchar(20) NOT NULL,
`time_num` int(10) unsigned NOT NULL,
`time_iso` char(16) NOT NULL,
`sid` bigint(20) unsigned NOT NULL,
`policy` varchar(255) DEFAULT '',
`client_addr` varchar(255) DEFAULT '',
`size` int(10) unsigned NOT NULL,
`originating` char(1) NOT NULL DEFAULT '',
`content` char(1) DEFAULT NULL,
`quar_type` char(1) DEFAULT NULL,
`quar_loc` varbinary(255) DEFAULT '',
`dsn_sent` char(1) DEFAULT NULL,
`spam_level` float DEFAULT NULL,
`message_id` varchar(255) DEFAULT '',
`from_addr` varchar(255) DEFAULT '',
`subject` varchar(255) DEFAULT '',
`host` varchar(255) NOT NULL,
PRIMARY KEY (`partition_tag`,`mail_id`),
KEY `msgs_idx_sid` (`sid`),
KEY `msgs_idx_mess_id` (`message_id`),
KEY `msgs_idx_time_num` (`time_num`),
KEY `msgs_mail_id_idx` (`mail_id`),
KEY `msgs_partition_tag_idx` (`partition_tag`),
KEY `msgs_content_idx` (`content`),
FULLTEXT KEY `ft_from_addr` (`from_addr`),
FULLTEXT KEY `ft_subject` (`subject`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
| maddr | CREATE TABLE `maddr` (
`partition_tag` int(11) DEFAULT '0',
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`email` varbinary(255) NOT NULL,
`domain` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `part_email` (`partition_tag`,`email`),
KEY `maddr_email_idx` (`email`),
KEY `maddr_partition_tag_idx` (`partition_tag`)
) ENGINE=InnoDB AUTO_INCREMENT=3373444 DEFAULT CHARSET=utf8 |
Этот запрос с профилем:
mysql> SET PROFILING=1; SELECT
Query OK, 0 rows affected (0.00 sec)
-> count(1)
-> FROM
-> (((`msgrcpt` `mr` JOIN `msgs` `m`
-> ON
-> (
-> `m`.`partition_tag` = `mr`.`partition_tag`
-> AND
-> `m`.`mail_id` = `mr`.`mail_id`
-> )
-> )
-> JOIN `maddr` maddr
-> ON
-> (
-> `mr`.`rid` = `maddr`.`id`
-> )
-> )); SHOW PROFILE ALL;
+----------+
| count(1) |
+----------+
| 4279394 |
+----------+
1 row in set (23 min 56.61 sec)
+----------------------+------------+-----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+------------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+----------------------+------------+-----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+------------------+-------------+
| starting | 0.000161 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |
| checking permissions | 0.000030 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | check_access | sql_parse.cc | 5043 |
| checking permissions | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_parse.cc | 5043 |
| checking permissions | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_parse.cc | 5043 |
| Opening tables | 0.000039 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5014 |
| System lock | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 304 |
| init | 0.000040 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_select | sql_select.cc | 1041 |
| optimizing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 138 |
| statistics | 0.000063 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 358 |
| preparing | 0.000032 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 470 |
| executing | 0.000021 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 137 |
| Sending data | 999.999999 | 97.014251 | 10.376423 | 681167 | 25822 | 5157072 | 1951032 | 0 | 0 | 4 | 277 | 0 | execute | sql_executor.cc | 758 |
| end | 0.000106 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_select | sql_select.cc | 1071 |
| query end | 0.000017 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4761 |
| closing tables | 0.000021 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4809 |
| freeing items | 0.000030 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5997 |
| logging slow query | 0.000059 | 0.000000 | 0.000000 | 0 | 0 | 0 | 8 | 0 | 0 | 0 | 0 | 0 | log_slow_statement | sql_parse.cc | 1720 |
| cleaning up | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1654 |
+----------------------+------------+-----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+------------------+-------------+
18 rows in set (0.02 sec)
Индексы в таблицах:
mysql> show index from msgs;
+-------+------------+------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| msgs | 0 | PRIMARY | 1 | partition_tag | A | 16 | NULL | NULL | | BTREE | | |
| msgs | 0 | PRIMARY | 2 | mail_id | A | 4174440 | NULL | NULL | | BTREE | | |
| msgs | 1 | msgs_idx_sid | 1 | sid | A | 2087220 | NULL | NULL | | BTREE | | |
| msgs | 1 | msgs_idx_mess_id | 1 | message_id | A | 4174440 | NULL | NULL | YES | BTREE | | |
| msgs | 1 | msgs_idx_time_num | 1 | time_num | A | 1391480 | NULL | NULL | | BTREE | | |
| msgs | 1 | msgs_mail_id_idx | 1 | mail_id | A | 4174440 | NULL | NULL | | BTREE | | |
| msgs | 1 | msgs_partition_tag_idx | 1 | partition_tag | A | 16 | NULL | NULL | | BTREE | | |
| msgs | 1 | msgs_content_idx | 1 | content | A | 16 | NULL | NULL | YES | BTREE | | |
| msgs | 1 | ft_from_addr | 1 | from_addr | NULL | 4174440 | NULL | NULL | YES | FULLTEXT | | |
| msgs | 1 | ft_subject | 1 | subject | NULL | 4174440 | NULL | NULL | YES | FULLTEXT | | |
+-------+------------+------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
10 rows in set (0.97 sec)
MSGRCPT
mysql> show index from msgrcpt;
+---------+------------+---------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+---------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| msgrcpt | 0 | PRIMARY | 1 | partition_tag | A | 29 | NULL | NULL | | BTREE | | |
| msgrcpt | 0 | PRIMARY | 2 | mail_id | A | 5218535 | NULL | NULL | | BTREE | | |
| msgrcpt | 0 | PRIMARY | 3 | rseqnum | A | 5218535 | NULL | NULL | | BTREE | | |
| msgrcpt | 1 | msgrcpt_idx_rid | 1 | rid | A | 347902 | NULL | NULL | | BTREE | | |
| msgrcpt | 1 | msgrcpt_mail_id_idx | 1 | mail_id | A | 5218535 | NULL | NULL | | BTREE | | |
| msgrcpt | 1 | msgrcpt_rs_idx | 1 | rs | A | 29 | NULL | NULL | | BTREE | | |
| msgrcpt | 1 | msgrcpt_ds_idx | 1 | ds | A | 29 | NULL | NULL | | BTREE | | |
| msgrcpt | 1 | msgrcpt_partition_tag_idx | 1 | partition_tag | A | 29 | NULL | NULL | | BTREE | | |
+---------+------------+---------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.70 sec)
MADDR:
mysql> show index from maddr;
+-------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| maddr | 0 | PRIMARY | 1 | id | A | 1653970 | NULL | NULL | | BTREE | | |
| maddr | 0 | part_email | 1 | partition_tag | A | 19 | NULL | NULL | YES | BTREE | | |
| maddr | 0 | part_email | 2 | email | A | 1653970 | NULL | NULL | | BTREE | | |
| maddr | 1 | maddr_email_idx | 1 | email | A | 1653970 | NULL | NULL | | BTREE | | |
| maddr | 1 | maddr_partition_tag_idx | 1 | partition_tag | A | 19 | NULL | NULL | YES | BTREE | | |
+-------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.41 sec)
Размер буфера Inno db
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 2147483648 |
+-------------------------+------------+
1 row in set (0.02 sec)
6
задан Aldarund 4 July 2012 в 08:21
поделиться
0 ответов
Другие вопросы по тегам: