SQLinfo.ru - Все о MySQL Highload++ Junior 2017

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

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

Вы не зашли.

#1 30.05.2016 20:31:41

Sn1k3rS.322
Участник
Зарегистрирован: 30.05.2016
Сообщений: 3

Оптимизация таблицы и запросов

Здравствуйте. Имеется таблица, в которой может быть от 0 до 10,000 записей, нужно оптимизировать её.

SHOW CREATE TABLE

CREATE TABLE `prefix_product` (
  `product_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL DEFAULT '',
  `title` varchar(200) NOT NULL,
  `description` text NOT NULL,
  `category_id` mediumint(8) unsigned NOT NULL,
  `image` text NOT NULL,
  `price` decimal(12,4) unsigned NOT NULL,
  `discount` int(11) unsigned NOT NULL DEFAULT '0',
  `alt` varchar(255) NOT NULL,
  `meta_keywords` text NOT NULL,
  `meta_description` varchar(200) NOT NULL,
  `shipping` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `length` decimal(15,8) unsigned NOT NULL DEFAULT '0.00000000',
  `height` decimal(15,8) unsigned NOT NULL DEFAULT '0.00000000',
  `width` decimal(15,8) unsigned NOT NULL DEFAULT '0.00000000',
  `weight` decimal(15,8) unsigned NOT NULL DEFAULT '0.00000000',
  `minimum` int(11) unsigned NOT NULL DEFAULT '1',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `date_available` int(11) unsigned NOT NULL DEFAULT '0',
  `date_added` int(11) unsigned NOT NULL DEFAULT '0',
  `date_modified` int(11) unsigned NOT NULL DEFAULT '0',
  `more_data` longtext NOT NULL,
  `allow_comments` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `allow_preorder` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `quantity` int(5) unsigned NOT NULL DEFAULT '0',
  `viewed` int(11) unsigned NOT NULL DEFAULT '0',
  `manufacturer_id` int(11) unsigned NOT NULL DEFAULT '0',
  `tax_ids` varchar(100) NOT NULL,
  `novelty` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `recommended` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`product_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1490 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED


Прочитал уже много по оптимизации. В частности по индексам. Ничего не получается сделать, производительность только падает от моих попыток.
Запросы, которые сейчас выполняются.

SELECT *
    FROM `prefix_product`  p
    LEFT JOIN `prefix_manufacturer` m ON (p.manufacturer_id = m.manufacturer_id)
    WHERE p.status = '1' AND p.date_available <= 1464627305 ORDER BY (p.price - (p.price * p.discount / 100)) DESC LIMIT 0, 10
/*** Время выполнения ~ 0.020495891571045 ***/

SELECT p.product_id
    FROM `prefix_product` p
    WHERE p.status = '1' AND p.date_available <= 1464627305
/*** Время выполнения ~ 0.017859935760498 ***/

SELECT *
    FROM `prefix_product` p
    LEFT JOIN `prefix_manufacturer` m ON (p.manufacturer_id = m.manufacturer_id)
    WHERE p.product_id = 43 AND p.status = '1' AND p.date_available <= 1464629253 AND p.alt = 'in-ipsa-cumque' LIMIT 0, 1
/*** Время выполнения ~ 0.0006260871887207 ***/

UPDATE `prefix_product` SET viewed = (viewed + 1) WHERE product_id = 43
/*** Время выполнения ~ 0.00037884712219238 ***/
 


Оптимизация требуется потому, что еще помимо этих выполняется около 2-10 запросов на страницу и запрос происходит через AJAX, что существенно увеличивает количество запросов, и соответственно удобность сайта.
Надеюсь на вашу помощь.

Неактивен

 

#2 30.05.2016 20:47:16

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

Re: Оптимизация таблицы и запросов

Для начала нужно определить, что именно стоит оптимизировать. Посмотрите  Поиск узких мест в производительности MySQL: ботанический определитель

По приведенному:
-) зачем вы числовым полям даете строковые значения
p.status = '1'
аналогично в определении таблицы
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0',
-) в текущем варианте нет никаких индексов
-) по первым двум запросам напрашивается (status, date_available)
-) ну и план показывайте

Но это всё пальцем в небо, сначала нужно определить, что затык в базе, потом собрать статистику и посмотреть какие запросы больше всего нагружают и уже их оптимизировать.

Неактивен

 

#3 30.05.2016 21:08:17

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

Re: Оптимизация таблицы и запросов

Кстати, viewed это кол-во просмотров товара?
Если да, и используете кэш запросов, то из-за этого эффекта не будет, подробней тут

Неактивен

 

#4 31.05.2016 10:55:19

Sn1k3rS.322
Участник
Зарегистрирован: 30.05.2016
Сообщений: 3

Re: Оптимизация таблицы и запросов

vasya написал:

Для начала нужно определить, что именно стоит оптимизировать. Посмотрите  Поиск узких мест в производительности MySQL: ботанический определитель

По приведенному:
-) зачем вы числовым полям даете строковые значения
p.status = '1'
аналогично в определении таблицы
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0',
-) в текущем варианте нет никаких индексов
-) по первым двум запросам напрашивается (status, date_available)
-) ну и план показывайте

Но это всё пальцем в небо, сначала нужно определить, что затык в базе, потом собрать статистику и посмотреть какие запросы больше всего нагружают и уже их оптимизировать.

Спасибо, со "status" исправил.
Индексов нету, потому, что я не пойму, как их правильно составить для таких запросов.
А вот где взять план тоже не знаю.

И да, viewed это кол-во просмотров товара, но кэш запросов не используется. Буду пробовать его включить, если стоит.

Неактивен

 

#5 31.05.2016 12:29:45

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

Re: Оптимизация таблицы и запросов

Sn1k3rS.322 написал:

Индексов нету, потому, что я не пойму, как их правильно составить для таких запросов.

посмотрите FAQ №5
экспериментируйте, создавайте разные индексы, смотрите через план запроса, какие используются
для первых двух, как я уже говорил подойдет (status, date_available) или возможно просто на date_available


Sn1k3rS.322 написал:

А вот где взять план тоже не знаю.

добавьте explain перед запросом
explain select ...
на русском немного есть тут

Неактивен

 

#6 01.06.2016 14:10:31

Sn1k3rS.322
Участник
Зарегистрирован: 30.05.2016
Сообщений: 3

Re: Оптимизация таблицы и запросов

Спасибо, с FAQ ознакомлюсь. Не знал, что EXPLAIN это план.

stdClass Object
(
    [num_rows] => 2
    [row] => Array
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => p
            [type] => ALL
            [possible_keys] =>
            [key] =>
            [key_len] =>
            [ref] =>
            [rows] => 1477
            [Extra] => Using where; Using filesort
        )

    [rows] => Array
        (
            [0] => Array
                (
                    [id] => 1
                    [select_type] => SIMPLE
                    [table] => p
                    [type] => ALL
                    [possible_keys] =>
                    [key] =>
                    [key_len] =>
                    [ref] =>
                    [rows] => 1477
                    [Extra] => Using where; Using filesort
                )

            [1] => Array
                (
                    [id] => 1
                    [select_type] => SIMPLE
                    [table] => m
                    [type] => eq_ref
                    [possible_keys] => PRIMARY,id
                    [key] => PRIMARY
                    [key_len] => 4
                    [ref] => test2.p.manufacturer_id
                    [rows] => 1
                    [Extra] => Using where
                )

        )

)


На счет индексов. (status, date_available), (status), (date_available) все эти варианты увеличивают время обработки примерно в двое

Неактивен

 

#7 01.06.2016 15:06:25

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

Re: Оптимизация таблицы и запросов

как вы измеряете время выполнения?
просто добавление индексов в таблицу увеличило вдвое выполнение select?
сделайте профилирование, чтобы определить на что тратится время


не понятно к каким запросам относятся указанные планы
показывайте в виде:

MariaDB [test]> explain select * from city;
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|    1 | SIMPLE      | city  | ALL  | NULL          | NULL | NULL    | NULL | 4079 |       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.17 sec)

или

MariaDB [test]> explain select * from city\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4079
        Extra:
1 row in set (0.00 sec)
 


и структуру таблиц:

MariaDB [test]> show create table city\G
*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Неактивен

 

Board footer

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