SQLinfo.ru - Все о MySQL Фестиваль «Российские интернет-технологии» 2017

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 06.06.2016 04:07:27

OlehKolomiyets
Участник
Зарегистрирован: 06.06.2016
Сообщений: 6

Оптимизация медленного select запросы

Доброй ночи, прошу помощи в следующем вопросе.
Имеется битрикс на довольно не слабом железе, при импорте данных без проверки на дубликаты - скорость отличная, процессор не загружен.
При выборе проверки на дубликаты - скорость снижается в 10-20 раз, процессор загружается на 100%.
лог медленных запросов спамит подобным:
# Time: 160606  3:53:52
# User@Host: root[root] @ localhost []
# Query_time: 1.213858  Lock_time: 0.000083 Rows_sent: 3  Rows_examined: 650150
SET timestamp=1465174432;
SELECT
    `crm_integrity_duplicate_communication_match_code`.`ENTITY_TYPE_ID` AS `ENTITY_TYPE_ID`,
    `crm_integrity_duplicate_communication_match_code`.`ENTITY_ID` AS `ENTITY_ID`
FROM `b_crm_dp_comm_mcd` `crm_integrity_duplicate_communication_match_code`

WHERE UPPER(`crm_integrity_duplicate_communication_match_code`.`TYPE`) like upper('PHONE')
AND `crm_integrity_duplicate_communication_match_code`.`VALUE` = '543095752'
ORDER BY `crm_integrity_duplicate_communication_match_code`.`ENTITY_TYPE_ID` ASC, `crm_integrity_duplicate_communication_match_code`.`ENTITY_ID` ASC
LIMIT 0, 50;


My.ini:

#
# Basic mysql configuration. Use bvat for advanced settings.
# Parameters set by bvat are stored in /etc/mysql/conf.d/bvat.cnf.
# If you want to change any parameter, you'll have to redefine it in /etc/mysql/conf.d/z_bx_custom.cnf
#

[client]
port = 3306
socket = /var/lib/mysqld/mysqld.sock
default-character-set = utf8

[mysqld_safe]
nice = 0
socket = /var/lib/mysqld/mysqld.sock

[mysqld]

# Basic mysql server configuration
user = mysql
port = 3306
basedir = /usr
datadir = /var/lib/mysql
socket = /var/lib/mysqld/mysqld.sock
skip-external-locking
# default-storage-engine = innodb
innodb=OFF
default-storage-engine = MyISAM
pid-file = /var/run/mysqld/mysqld.pid
transaction-isolation = READ-COMMITTED
max_allowed_packet = 16M
myisam-recover = BACKUP
expire_logs_days = 10
max_binlog_size = 100M

# Cache parameters
query_cache_size = 512M
key_buffer_size = 2048M
query_cache_type=0
table_open_cache = 16384
thread_cache_size = 512
key_buffer = 16M
thread_stack = 8M
join_buffer_size = 16M
sort_buffer_size = 16M

# Parameters for temporary tables
tmpdir = /tmp
max_heap_table_size    = 1024M
tmp_table_size = 1024M

# InnoDB parameters
innodb_file_per_table
innodb_buffer_pool_size    = 4024M
innodb_additional_mem_pool_size=50M
innodb_lock_wait_timeout=50
innodb_file_io_threads=8
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=0
innodb_log_file_size = 64M
innodb_flush_method = O_DIRECT
table_cache=9000
key_buffer_size=2048M
sort_buffer=8M
read_buffer_size=16M
tmp_table_size=100M
join_buffer_size = 30M


# Database charset parameters
character-set-server = utf8
collation-server = utf8_unicode_ci
init-connect = "SET NAMES utf8 COLLATE utf8_unicode_ci"
#skip-character-set-client-handshake
skip-name-resolve

[mysqldump]
quick
quote-names
max_allowed_packet = 16M
default-character-set = utf8

[mysql]

[isamchk]
key_buffer = 2048M

# Include additional settings
!includedir /etc/mysql/conf.d/

long_query_time = 1
log-slow-queries = /var/log/mysqld-slow-query.log
log-queries-not-using-indexes


Куда копать, подскажите.

Неактивен

 

#2 06.06.2016 10:04:19

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5130

Re: Оптимизация медленного select запросы

покажите план этого запроса и структуру таблицы в виде show create table `имя таблицы`

Неактивен

 

#3 06.06.2016 10:37:47

OlehKolomiyets
Участник
Зарегистрирован: 06.06.2016
Сообщений: 6

Re: Оптимизация медленного select запросы

Подскажите, как показать план?
Структуру b_crm_dp_comm_mcd ?

Неактивен

 

#4 06.06.2016 10:51:27

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5130

Re: Оптимизация медленного select запросы

покажите результаты выполнения следующих команд

explain SELECT
    `crm_integrity_duplicate_communication_match_code`.`ENTITY_TYPE_ID` AS `ENTITY_TYPE_ID`,
    `crm_integrity_duplicate_communication_match_code`.`ENTITY_ID` AS `ENTITY_ID`
FROM `b_crm_dp_comm_mcd` `crm_integrity_duplicate_communication_match_code`

WHERE UPPER(`crm_integrity_duplicate_communication_match_code`.`TYPE`) like upper('PHONE')
AND `crm_integrity_duplicate_communication_match_code`.`VALUE` = '543095752'
ORDER BY `crm_integrity_duplicate_communication_match_code`.`ENTITY_TYPE_ID` ASC, `crm_integrity_duplicate_communication_match_code`.`ENTITY_ID` ASC
LIMIT 0, 50;

и
show create table b_crm_dp_comm_mcd;

Неактивен

 

#5 06.06.2016 13:24:22

OlehKolomiyets
Участник
Зарегистрирован: 06.06.2016
Сообщений: 6

Re: Оптимизация медленного select запросы

mysql> show create table b_crm_dp_comm_mcd;
+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| b_crm_dp_comm_mcd | CREATE TABLE `b_crm_dp_comm_mcd` (
  `ID` int(1) unsigned NOT NULL AUTO_INCREMENT,
  `ENTITY_TYPE_ID` tinyint(1) unsigned NOT NULL,
  `ENTITY_ID` int(1) unsigned NOT NULL,
  `TYPE` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `VALUE` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `IX_B_CRM_DP_COMM_MCD_1` (`TYPE`,`VALUE`(255),`ENTITY_TYPE_ID`,`ENTITY_ID`,`ID`),
  KEY `IX_B_CRM_DP_COMM_MCD_2` (`ENTITY_TYPE_ID`,`ENTITY_ID`,`TYPE`,`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=926963 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Неактивен

 

#6 06.06.2016 13:25:21

OlehKolomiyets
Участник
Зарегистрирован: 06.06.2016
Сообщений: 6

Re: Оптимизация медленного select запросы

и:
http://cs631721.vk.me/v631721758/2fdc0/pdqplgC8zhI.jpg

Неактивен

 

#7 06.06.2016 13:39:54

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5130

Re: Оптимизация медленного select запросы

у вас есть возможность править запрос?
замените
WHERE UPPER(`crm_integrity_duplicate_communication_match_code`.`TYPE`) like upper('PHONE')
на
WHERE `crm_integrity_duplicate_communication_match_code`.`TYPE` = 'PHONE'

Неактивен

 

#8 06.06.2016 13:41:53

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5130

Re: Оптимизация медленного select запросы

отчего у вас при `VALUE` varchar(256)
индекс сделан на первые 255 символов?
  KEY `IX_B_CRM_DP_COMM_MCD_1` (`TYPE`,`VALUE`(255),`ENTITY_TYPE_ID`,`ENTITY_ID`,`ID`),

Неактивен

 

#9 06.06.2016 13:58:37

OlehKolomiyets
Участник
Зарегистрирован: 06.06.2016
Сообщений: 6

Re: Оптимизация медленного select запросы

vasya написал:

у вас есть возможность править запрос?
замените
WHERE UPPER(`crm_integrity_duplicate_communication_match_code`.`TYPE`) like upper('PHONE')
на
WHERE `crm_integrity_duplicate_communication_match_code`.`TYPE` = 'PHONE'

После 4 сделаю и отпишу.

Неактивен

 

#10 06.06.2016 13:59:26

OlehKolomiyets
Участник
Зарегистрирован: 06.06.2016
Сообщений: 6

Re: Оптимизация медленного select запросы

vasya написал:

отчего у вас при `VALUE` varchar(256)
индекс сделан на первые 255 символов?
  KEY `IX_B_CRM_DP_COMM_MCD_1` (`TYPE`,`VALUE`(255),`ENTITY_TYPE_ID`,`ENTITY_ID`,`ID`),

Без понятия, само значение в этих полях 9-10 символов (номер телефона).

Неактивен

 

#11 06.06.2016 21:10:46

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5130

Re: Оптимизация медленного select запросы

OlehKolomiyets написал:

Без понятия, само значение в этих полях 9-10 символов (номер телефона).

Тогда делайте varchar(10)
А если номера не начинаются с нулей и содержат только цифры, то числовой.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson