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

Sn1k3rS.322 · 30.05.2016 20:31:41

Здравствуйте. Имеется таблица, в которой может быть от 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, что существенно увеличивает количество запросов, и соответственно удобность сайта.
Надеюсь на вашу помощь.

vasya · 30.05.2016 20:47:16

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

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

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

vasya · 30.05.2016 21:08:17

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

Sn1k3rS.322 · 31.05.2016 10:55:19

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

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

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

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

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

vasya · 31.05.2016 12:29:45

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

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

Sn1k3rS.322 написал:
А вот где взять план тоже не знаю.

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

Sn1k3rS.322 · 01.06.2016 14:10:31

Спасибо, с 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) все эти варианты увеличивают время обработки примерно в двое

vasya · 01.06.2016 15:06:25

как вы измеряете время выполнения?
просто добавление индексов в таблицу увеличило вдвое выполнение 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)

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

#1 30.05.2016 20:31:41

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

#2 30.05.2016 20:47:16

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

#3 30.05.2016 21:08:17

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

#4 31.05.2016 10:55:19

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

vasya написал:

#5 31.05.2016 12:29:45

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

Sn1k3rS.322 написал:

Sn1k3rS.322 написал:

#6 01.06.2016 14:10:31

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

#7 01.06.2016 15:06:25

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

Board footer