Оптимизация Mysql нагрузка большая

[bodyak0]

Всем привет, проблема следующего характера, имеется сайт хостеры бегета отсекают бд в связи с нагрузкой.
по логам говорят, что больше всего раз выполняется запрос
select * FROM url_alias WHERE `query` IN ('category_id=119', 'product_id=9300'), вероятно он выполняется каждый раз при обращении к странице. В базе данных gutdiz3e_569, создали индексы для столбцов url_alias, query сократив время выполнения запроса с 0,100 мс, до 0.0005 мс.
как оптимизировать лучше БД,

[i3bepb]

1 час назад, bodyak0 сказал:

select * FROM url_alias WHERE `query` IN ('category_id=119', 'product_id=9300')

Лог медленных запросов включали?

[bodyak0]

21 хвилину назад, i3bepb сказав:

Лог медленных запросов включали?

Reading from STDIN ...

# 34.5s user time, 3.8s system time, 26.48M rss, 96.03M vsz
# Current date: Sat Feb  8 03:04:03 2020
# Hostname: donald.beget.ru
# Files: STDIN
# Overall: 4.30k total, 37 unique, 0.30 QPS, 0.03x concurrency ___________
# Time range: 2020-02-07T23:04:12 to 2020-02-08T03:01:09
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           468s   100ms      1s   109ms   100ms    78ms   100ms
# Lock time              0       0       0       0       0       0       0
# Query size         1.17M      41   1.76k  284.72   1.69k  446.38   76.28
# id               221.15G  52.15M  53.24M  52.71M  51.29M       0  51.29M

# Profile
# Rank Query ID           Response time  Calls R/Call V/M   Item
# ==== ================== ============== ===== ====== ===== ==============
#    1 0x4E2FFD2953A6AA24 310.7497 66.4%  3056 0.1017  0.01 SELECT url_alias
#    2 0x4A7378B436916589  71.6143 15.3%   561 0.1277  0.15 SELECT product product_description product_to_store product_to_category
#    3 0x2B2A42A723A14D4A  32.7814  7.0%   323 0.1015  0.00 SELECT product_discount product_special product_reward stock_status weight_class_description length_class_description review product product_description product_to_store manufacturer
#    4 0xA1DF69020ECA9E69  11.8702  2.5%    74 0.1604  0.25 SELECT product product_description product_to_store product_to_category
#    5 0x38668E948B0AC48B   6.0247  1.3%    32 0.1883  0.36 SELECT product product_description product_to_store product_to_category
#    6 0xADEE77B4A5B00BD6   5.2319  1.1%    31 0.1688  0.28 SELECT review product product_description product_to_store product_to_category
#    7 0xB62584DCB790711B   4.7134  1.0%    26 0.1813  0.31 SELECT product product_description product_to_store product_to_category
#    8 0xD196072D4C2329B5   4.1177  0.9%    22 0.1872  0.35 SELECT review product product_description product_to_store product_to_category
# MISC 0xMISC              20.8305  4.5%   171 0.1218   0.0 <29 ITEMS>

# Query 1: 0.21 QPS, 0.02x concurrency, ID 0x4E2FFD2953A6AA24 at byte 0 __
# Scores: V/M = 0.01
# Time range: 2020-02-07T23:04:12 to 2020-02-08T03:01:09
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         71    3056
# Exec time     66    311s   100ms      1s   102ms   100ms    28ms   100ms
# Lock time      0       0       0       0       0       0       0       0
# Query size    18 224.10k      59      98   75.09   76.28    6.89   76.28
# id            71 157.37G  52.15M  53.24M  52.73M  51.29M       0  51.29M
# String:
# Databases    gutdiz3e_569
# Hosts        localhost
# Users        gutdiz3e_569
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  #
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'url_alias'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`url_alias`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM url_alias WHERE `query` IN ('category_id=119', 'product_id=9300')\G

# Query 2: 0.04 QPS, 0.01x concurrency, ID 0x4A7378B436916589 at byte 0 __
# Scores: V/M = 0.15
# Time range: 2020-02-07T23:14:05 to 2020-02-08T02:54:34
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         13     561
# Exec time     15     72s   100ms      1s   128ms   198ms   138ms   100ms
# Lock time      0       0       0       0       0       0       0       0
# Query size    17 213.58k     389     390  389.86  381.65       0  381.65
# id            13  28.88G  52.20M  53.21M  52.72M  51.29M       0  51.29M
# String:
# Databases    gutdiz3e_569
# Hosts        localhost
# Users        gutdiz3e_569
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  #
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_description'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_description`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_to_store'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_to_store`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_to_category'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_to_category`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(DISTINCT p.product_id) AS total FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND (p2c.category_id = '78')\G

# Query 3: 0.02 QPS, 0.00x concurrency, ID 0x2B2A42A723A14D4A at byte 0 __
# Scores: V/M = 0.00
# Time range: 2020-02-07T23:05:20 to 2020-02-08T02:54:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          7     323
# Exec time      7     33s   100ms   103ms   101ms   100ms       0   100ms
# Lock time      0       0       0       0       0       0       0       0
# Query size    47 569.81k   1.76k   1.76k   1.76k   1.69k       0   1.69k
# id             7  16.58G  52.16M  53.21M  52.56M  51.29M       0  51.29M
# String:
# Databases    gutdiz3e_569
# Hosts        localhost
# Users        gutdiz3e_569
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_discount'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_discount`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_special'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_special`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_reward'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_reward`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'stock_status'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`stock_status`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'weight_class_description'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`weight_class_description`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'length_class_description'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`length_class_description`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'review'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`review`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_description'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_description`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_to_store'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_to_store`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'manufacturer'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`manufacturer`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '1') AS reward, (SELECT ss.name FROM stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '1') AS stock_status, (SELECT wcd.unit FROM weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '1') AS weight_class, (SELECT lcd.unit FROM length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '1') AS length_class, (SELECT AVG(rating) AS total FROM review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '11274' AND pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0'\G

# Query 4: 0.01 QPS, 0.00x concurrency, ID 0xA1DF69020ECA9E69 at byte 0 __
# Scores: V/M = 0.25
# Time range: 2020-02-07T23:15:06 to 2020-02-08T02:54:33
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1      74
# Exec time      2     12s   100ms      1s   160ms   198ms   201ms   100ms
# Lock time      0       0       0       0       0       0       0       0
# Query size     3  47.67k     659     660  659.66  652.75       0  652.75
# id             1   3.81G  52.21M  53.21M  52.71M  51.29M       0  51.29M
# String:
# Databases    gutdiz3e_569
# Hosts        localhost
# Users        gutdiz3e_569
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  ###
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_description'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_description`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_to_store'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_to_store`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_to_category'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_to_category`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(DISTINCT p.product_id) AS total FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND (p2c.category_id = '136' OR p2c.category_id = '150' OR p2c.category_id = '151' OR p2c.category_id = '152' OR p2c.category_id = '153' OR p2c.category_id = '154' OR p2c.category_id = '155' OR p2c.category_id = '156' OR p2c.category_id = '157' OR p2c.category_id = '158' OR p2c.category_id = '159')\G

# Query 5: 0.00 QPS, 0.00x concurrency, ID 0x38668E948B0AC48B at byte 0 __
# Scores: V/M = 0.36
# Time range: 2020-02-07T23:14:07 to 2020-02-08T02:54:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0      32
# Exec time      1      6s   100ms      1s   188ms   992ms   260ms   100ms
# Lock time      0       0       0       0       0       0       0       0
# Query size     1  12.12k     387     388  387.88  381.65       0  381.65
# id             0   1.65G  52.20M  53.21M  52.70M  51.29M       0  51.29M
# String:
# Databases    gutdiz3e_569
# Hosts        localhost
# Users        gutdiz3e_569
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  ######
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_description'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_description`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_to_store'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_to_store`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_to_category'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_to_category`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(DISTINCT p.product_id) AS total FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND p2c.category_id = '128'\G

# Query 6: 0.00 QPS, 0.00x concurrency, ID 0xADEE77B4A5B00BD6 at byte 0 __
# Scores: V/M = 0.28
# Time range: 2020-02-07T23:37:34 to 2020-02-08T02:05:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0      31
# Exec time      1      5s   100ms      1s   169ms   198ms   219ms   100ms
# Lock time      0       0       0       0       0       0       0       0
# Query size     1  17.73k     585     586  585.55  563.87       0  563.87
# id             0   1.59G  52.32M  52.99M  52.55M  51.29M       0  51.29M
# String:
# Databases    gutdiz3e_569
# Hosts        localhost
# Users        gutdiz3e_569
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  ####
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'review'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`review`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_description'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_description`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_to_store'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_to_store`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_to_category'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_to_category`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT p.product_id, (SELECT AVG(rating) AS total FROM review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND p2c.category_id = '20' GROUP BY p.product_id ORDER BY p.date_added DESC, LCASE(pd.name) DESC LIMIT 0,1000000000\G

# Query 7: 0.00 QPS, 0.00x concurrency, ID 0xB62584DCB790711B at byte 0 __
# Scores: V/M = 0.31
# Time range: 2020-02-07T23:15:06 to 2020-02-08T02:54:33
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0      26
# Exec time      1      5s   100ms      1s   181ms   198ms   236ms   100ms
# Lock time      0       0       0       0       0       0       0       0
# Query size     1  21.48k     846     846     846     846       0     846
# id             0   1.34G  52.21M  53.21M  52.71M  51.29M       0  51.29M
# String:
# Databases    gutdiz3e_569
# Hosts        localhost
# Users        gutdiz3e_569
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  #####
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_description'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_description`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_to_store'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_to_store`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_to_category'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_to_category`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(DISTINCT p.product_id) AS total FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND (p2c.category_id = '18' OR p2c.category_id = '109' OR p2c.category_id = '105' OR p2c.category_id = '193' OR p2c.category_id = '34' OR p2c.category_id = '24' OR p2c.category_id = '115' OR p2c.category_id = '110' OR p2c.category_id = '108' OR p2c.category_id = '111' OR p2c.category_id = '106' OR p2c.category_id = '112' OR p2c.category_id = '113' OR p2c.category_id = '114' OR p2c.category_id = '107' OR p2c.category_id = '104' OR p2c.category_id = '116' OR p2c.category_id = '117')\G

# Query 8: 0.00 QPS, 0.00x concurrency, ID 0xD196072D4C2329B5 at byte 0 __
# Scores: V/M = 0.35
# Time range: 2020-02-07T23:14:07 to 2020-02-08T02:54:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0      22
# Exec time      0      4s   101ms      1s   187ms   198ms   256ms   100ms
# Lock time      0       0       0       0       0       0       0       0
# Query size     1  12.38k     575     578  576.36  563.87       0  563.87
# id             0   1.13G  52.20M  53.21M  52.62M  51.29M       0  51.29M
# String:
# Databases    gutdiz3e_569
# Hosts        localhost
# Users        gutdiz3e_569
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  ######
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'review'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`review`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_description'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_description`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_to_store'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_to_store`\G
#    SHOW TABLE STATUS FROM `gutdiz3e_569` LIKE 'product_to_category'\G
#    SHOW CREATE TABLE `gutdiz3e_569`.`product_to_category`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT p.product_id, (SELECT AVG(rating) AS total FROM review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND p2c.category_id = '34' GROUP BY p.product_id ORDER BY p.sort_order ASC, LCASE(pd.name) ASC LIMIT 192,96\G
 

[i3bepb]

В 09.02.2020 в 17:41, bodyak0 сказал:

как оптимизировать лучше БД

Надо брать и оптимизировать конкретные запросы, вот из лога же они видны, есть ситуации когда они занимают 1 секунду, возможно есть блокировки какие-то в БД.

 

В 09.02.2020 в 19:18, bodyak0 сказал:

Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0      22
# Exec time      0      4s   101ms      1s   187ms   198ms   256ms   100ms

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

 

В 09.02.2020 в 19:18, bodyak0 сказал:

# EXPLAIN /*!50100 PARTITIONS*/