Повышение производительности mysql с помощью индексов и explain

EXPLAIN EXTENDED (ASCII TABLE)

+----+-------------+-------+--------+-------------------------------------------------------------+--------------------+---------+--------------------------+-------+----------+---------------------------------+
| id | select_type | table |  type  |                        possible_keys                        |        key         | key_len |           ref            | rows  | filtered |              Extra              |
+----+-------------+-------+--------+-------------------------------------------------------------+--------------------+---------+--------------------------+-------+----------+---------------------------------+
|  1 | SIMPLE      | t7    | ALL    | PRIMARY                                                     | NULL               | NULL    | NULL                     | 11627 | 100.00   | Using temporary; Using filesort |
|  1 | SIMPLE      | t4    | ref    | idx_table7RecordID,idx_table3RecordID                       | idx_table7RecordID | 5       | testDb.t7.recordID       |     1 | 100.00   | Using where                     |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY,table1RecordID_status,idx_status,idx_table1RecordID | PRIMARY            | 4       | testDb.t4.table3RecordID |     1 | 100.00   | Using where                     |
|  1 | SIMPLE      | t5    | ref    | compositeIDs                                                | compositeIDs       | 773     | const,testDb.t3.recordID |     5 | 100.00   |                                 |
|  1 | SIMPLE      | t6    | eq_ref | PRIMARY                                                     | PRIMARY            | 4       | testDb.t5.table6RecordID |     1 | 100.00   |                                 |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY                                                     | PRIMARY            | 4       | testDb.t3.table1RecordID |     1 | 100.00   |                                 |
|  1 | SIMPLE      | t2    | ref    | idx_table1RecordID                                          | idx_table1RecordID | 5       | testDb.t1.recordID       |    85 | 100.00   | Using index                     |
+----+-------------+-------+--------+-------------------------------------------------------------+--------------------+---------+--------------------------+-------+----------+---------------------------------+

Thanks again in advanced!


Query

SELECT
  `t1`.`name` AS `Object1.Name`,
  GROUP_CONCAT(DISTINCT
  IF(`t5`.`questionID`=68,
    IF(`t6`.`writeInRequired` = 1,
      CONCAT(
        `t6`.`value`,
        ':', `t5`.`writeInResponse`
      ),
      `t6`.`value`
    ),
    NULL
  ) SEPARATOR ', ') AS `Object3.Response_68`,
  GROUP_CONCAT(DISTINCT
  IF(`t5`.`questionID`=67,
    IF(`t6`.`writeInRequired` = 1,
      CONCAT(
        `t6`.`value`,
        ':', `t5`.`writeInResponse`
      ),
      `t6`.`value`
    ),
    NULL
  ) SEPARATOR ', ') AS `Object3.Response_67`,
  GROUP_CONCAT(DISTINCT
  IF(`t5`.`questionID`=66,
    IF(`t6`.`writeInRequired` = 1,
      CONCAT(
        `t6`.`value`,
        ':', `t5`.`writeInResponse`
      ),
      `t6`.`value`
    ),
    NULL
  ) SEPARATOR ', ') AS `Object3.Response_66`,
  `t7`.`firstName` AS `Object8.FirstName`,
  `t7`.`lastName` AS `Object8.LastName`,
  `t7`.`email` AS `Object8.Email`,
  `t1`.`recordID` AS `Object1.PackageID`,
  `t3`.`recordID` AS `Object5.RegistrationID`
FROM
  `Table1` t1
  LEFT JOIN `Table2` t2 ON `t1`.`recordID`=`t2`.`table1RecordID`
  LEFT JOIN `Table3` t3 ON `t3`.`table1RecordID`=`t1`.`recordID`
  LEFT JOIN `Table4` t4 ON `t4`.`table3RecordID`=`t3`.`recordID` AND `t4`.`type` = 1
  LEFT JOIN `Table5` t5 ON `t5`.`objectID`=`t3`.`recordID` AND `t5`.`objectType`='Type2'
  LEFT JOIN `Table6` t6 ON `t6`.`recordID`=`t5`.`table6RecordID`
  JOIN `Table7` t7 ON `t7`.`recordID`=`t4`.`table7RecordID`
WHERE
  `t3`.`status` IN ('3','4')
GROUP BY
  `Object5.RegistrationID` ASC,
  `Object1.PackageID` ASC

Explain

+----+-------------+--------+--------+------------------------------+---------+---------+------------------------------+-------+----------------------------------------------+
| id | select_type | table  |  type  |        possible_keys         |   key   | key_len |             ref              | rows  |                    Extra                     |
+----+-------------+--------+--------+------------------------------+---------+---------+------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | Table3 | ALL    | fk_packageID,regStatus,pkgID | NULL    | NULL    | NULL                         | 11322 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | Table1 | eq_ref | PRIMARY,groupName            | PRIMARY | 4       | testDb.Table3.table1RecordID |     1 | Using where                                  |
+----+-------------+--------+--------+------------------------------+---------+---------+------------------------------+-------+----------------------------------------------+

If I remove the second part of the GROUP BY, «. ASC» but data isn’t correct when I do that. Why is it doing this and how can I fix it and still group down by Table1 in addition to the Table3 first.

Thanks in advanced!

Update 1/24/14

I had time to take the full query and pull the tables to a generic form to post without client data. I was able to add schema to sqlfiddle but without the data I’m using results can be different and I was even unable to put 100 rows pre-table (7 total) into sqlfiddle due to limitations of characters. So instead I’ve done a dump of the tables and I’m sharing it over dropbox.

Что это такое?

EXPLAIN ANALYZE – это инструмент профилирования для ваших запросов, который покажет вам, где MySQL тратит время на ваш запрос и почему. Он будет планировать запрос, обрабатывать его и выполнять при подсчете строк и измерении времени, проведенного в различных точках плана выполнения. Когда выполнение завершится, EXPLAIN ANALYZE напечатает план и измерения вместо результата запроса.

Эта новая функция построена поверх обычного инструмента проверки плана запросов EXPLAIN и может рассматриваться как расширение EXPLAIN FORMAT = TREE, которое было добавлено ранее в MySQL 8.0. В дополнение к плану запроса и сметным затратам, которые печатает обычный EXPLAIN, EXPLAIN ANALYZE также печатает фактические затраты отдельных итераторов в плане выполнения.

Data Model

Choosing a data model will most likely reveal the right form of database(s) as well. Unless your product is very basic, you will probably have several databases for several use cases — if you need to show near real-time numbers for access logs, you will most likely want a highly performant data warehouse whereas regular transactions might happen via a SQL database, and you might have a graph database that accumulates the relevant data points of both databases into a recommender engine as well.

The software architecture of the overall product is just as important as the database itself since bad design here will result in bottlenecks that go towards the database and slow everything down both from the software side as well as what the database can output. You will need to choose whether containers are right for your product, whether a monolith is the better way to handle things, whether you may want to have a core monolith with several microservices targeting other functionality spread out elsewhere and how you access, gather, process, and store data.

Что происходит с популярностью MySQL и PostgreSQL? Дискуссия на митапе

24 апреля мы провели онлайн-митап MySQL@Scale, посвященный проблемам масштабируемости MySQL. Участвовали спикеры из Avito, Badoo и ECOMMPAY: Андрей Аксенов (автор Sphinx, лид инфраструктуры поиска), Евгений Кузовлев (CIO ECOMMPAY), Владимир Федорков (MySQL эксперт/DBA в ECOMMPAY) и Николай Королев (MySQL эксперт/DBA в Badoo). Митап вышел длинным, поэтому мы решили публиковать его частями, и начать с конца — с очень интересной на наш взгляд дискуссии о популярности MySQL и PostgreSQL, причинах роста популярности PostgreSQL, ORM, impedance mismatch, фрактальных индексах, гневе, отрицании, торге и настройке автовакуума и прочих проблемах выбора СУБД разработчиками гостевых книг на NodeJS

Внимание! Имеется не очень цензурная лексика, ряд некорректных обобщений были заменены, а любые совпадения случайны и ни в коем случае не носят оскорбительного характера

◆ explain_single_table_modification()

explain_single_table_modification ( THD *  explain_thd,
const THD *  query_thd,
const Modification_plan *  plan,
SELECT_LEX *  select 
)

EXPLAIN handling for single-table UPDATE and DELETE queries.

Send to the client a QEP data set for single-table EXPLAIN UPDATE/DELETE queries. As far as single-table UPDATE/DELETE are implemented without the regular JOIN tree, we can’t reuse explain_unit() directly, thus we deal with this single table in a special way and then call explain_unit() for subqueries (if any).

Parameters
explain_thd thread handle for the connection doing explain
query_thd thread handle for the connection being explained
plan table modification plan
select Query’s select lex
Returns
false if success, true if error

Prepare the self-allocated result object

For queries with top-level JOIN the caller provides pre-allocated Query_result_send object. Then that JOIN object prepares the Query_result_send object calling result->prepare() in SELECT_LEX::prepare(), result->optimize() in JOIN::optimize() and result->start_execution() in JOIN::exec(). However without the presence of the top-level JOIN we have to prepare/initialize Query_result_send object manually.

◆ explain_query()

explain_query ( THD *  explain_thd,
const THD *  query_thd,
SELECT_LEX_UNIT *  unit 
)

EXPLAIN handling for SELECT, INSERT/REPLACE SELECT, and multi-table UPDATE/DELETE queries.

Send to the client a QEP data set for any DML statement that has a QEP represented completely by JOIN object(s).

This function uses a specific Query_result object for sending explain output to the client.

When explaining own query, the existing Query_result object (found in outermost SELECT_LEX_UNIT or SELECT_LEX) is used. However, if the Query_result is unsuitable for explanation (need_explain_interceptor() returns true), wrap the Query_result inside an Query_result_explain object.

When explaining other query, create a Query_result_send object and prepare it as if it was a regular SELECT query.

Note
see explain_single_table_modification() for single-table UPDATE/DELETE EXPLAIN handling.
Unlike handle_query(), explain_query() calls abort_result_set() itself in the case of failure (OOM etc.) since it may use an internally created Query_result object that has to be deleted before exiting the function.
Parameters
explain_thd thread handle for the connection doing explain
query_thd thread handle for the connection being explained
unit query tree to explain
Returns
false if success, true if error

For DML statements use QT_NO_DATA_EXPANSION to avoid over-simplification.

Предложения

Will you explain it in plain English?Ты объяснишь это человеческим языком?


Explain to me why Tom isn’t here.Объясните мне, почему нет Тома.

I’ll explain it to her.Я объясню ей это.

I have to explain this to her.Я должен ей это объяснить.

Can you explain why you were late?Вы можете объяснить причину своего опоздания?

Explain to me in detail how it happened.Объясни мне подробно, как это случилось.

Tom couldn’t explain why.Том не смог объяснить почему.

I’d explain it to you, but your brain would explode.Я бы объяснил тебе, но у тебя башка взорвётся.

I’ll explain it to him.Я объясню ему это.

I can’t explain the reason for his conduct.Я не могу объяснить причину его поведения.

One explains the other.Одно объясняет другое.

There any many answers to this questions, and many legends are created about the Devil’s stone by the people: human mind cannot calm down until it explains to itself the dark, the unknows, the vague.Много ответов есть на этот вопрос, много легенд сложено людьми про Чёртов камень: разум человеческий не может успокоиться, пока не разъяснит себе тёмное, неизвестное, неясное.

That explains everything.Это всё объясняет.

That explains why the door is open.Этим объясняется, почему дверь открыта.

Oh, that. That explains it.А, вот как. Это объясняет дело.

Oh, that explains everything!О, это всё объясняет!

Science explains many things that religion never could explain.Наука объясняет множество вещей, которые религия не могла объяснить никогда.

That explains it.Это все объясняет.

That explains a lot.Это многое объясняет.

It is a fine hypothesis; it explains many things.Это хорошая гипотеза, она объясняет много вещей.


Tom explained how he lost his money.Том объяснил, как он потерял свои деньги.

He explained it in detail.Он объяснил это в деталях.

Max explained to Julie why he could not go to her farewell party.Макс объяснил Джули, почему он не мог прийти на её прощальную вечеринку.

We explained the situation.Мы объяснили ситуацию.

I’ve explained the problem to Tom.Я объяснил проблему Тому.

He explained the process of building a boat.Он объяснил процесс построения лодки.

The lawyer explained the new law to us.Адвокат объяснил нам новый закон.

He explained his position to me.Он объяснил мне свою позицию.

My neighbours have already explained to me who Björk is.Мои соседи уже объяснили мне, кто такая Бьорк.

Tom explained it.Том объяснил это.

Analyse de la requête SQL

Nous allons voir dans notre exemple comment MySQL va exécuter cette requête. Pour cela, il faut utiliser l’instruction EXPLAIN:

EXPLAIN SELECT timezone_groupe_fr, COUNT(timezone_detail) AS total_timezone
FROM `timezones` 
GROUP BY timezone_groupe_fr
ORDER BY timezone_groupe_fr ASC

Le retour de cette requête SQL est le suivant :

Requête SQL avec EXPLAIN sans index

Dans cet exemple on constate les champs suivants :

  • id : identifiant de SELECT
  • select_type : type de cause SELECT (exemple : SIMPLE, PRIMARY, UNION, DEPENDENT UNION, SUBQUERY, DEPENDENT SUBSELECT ou DERIVED)
  • table : table à laquelle la ligne fait référence
  • type : le type de jointure utilisé (exemple : system, const, eq_ref, ref, ref_or_null, index_merge, unique_subquery, index_subquery, range, index ou ALL)
  • possible_keys : liste des index que MySQL pourrait utiliser pour accélérer l’exécution de la requête. Dans notre exemple, aucun index n’est disponible pour accélérer l’exécution de la requête SQL
  • key : cette colonne présente les index que MySQL a décidé d’utiliser pour l’exécution de la requête
  • key_len : indique la taille de la clé qui sera utilisée. S’il n’y a pas de clé, cette colonne renvois NULL
  • ref : indique quel colonne (ou constante) sont utilisés avec les lignes de la table
  • rows : estimation du nombre de ligne que MySQL va devoir analyser examiner pour exécuter la requête
  • Extra : information additionnelle sur la façon dont MySQL va résoudre la requête. Si cette colonne retourne des résultats, c’est qu’il y a potentiellement des index à utiliser pour optimiser les performances de la requête SQL. Le message “using temporary” permet de savoir que MySQL va devoir créer une table temporaire pour exécuter la requête. Le message “using filesort” indique quant à lui que MySQL va devoir faire un autre passage pour retourner les lignes dans le bon ordre

OPTIMIZE TABLE

The basic syntax for this command is as follows.

OPTIMIZE TABLE myTable1, myTable2;
OPTIMIZE NO_WRITE_TO_BINLOG TABLE myTable1, myTable2;

The option can be used if you don’t want the operation to be pushed to replication slaves.

The following is a quote from the documentation for this command.


This is an over simplistic view of the operation. In the case of a table with a primary key index, the optimize operation will compact the data and may improve performance. For tables with secondary indexes, the optimize operation may adversely affect performance by causing index fragmentation in the secondary indexes. You can read more about this here.

There is no hard and fast rule for when to issue an command, although some would say never! If you do decide it is necessary, it may be sensible to drop and secondary indexes, perform the command, then recreate the secondary indexes.

ANALYZE TABLE

The basic syntax for this command is as follows.

ANALYZE TABLE myTable1, myTable2;
ANALYZE NO_WRITE_TO_BINLOG TABLE myTable1, myTable2;

The option can be used if you don’t want the operation to be pushed to replication slaves.

The command gathers statistics, allowing MySQL to make better decisions when deciding on join operations. On heavily modified tables that are displaying performance issues, analyzing may give a performance benefit by making sure the statistics are representative of the data distribution in the tables and indexes.

If a table has not changed since it was last analyzed, the operation will be skipped.

◆ check_acl_for_explain()

static check_acl_for_explain ( const TABLE_LIST *  table_list )
static

Check that we are allowed to explain all views in list.

Because this function is called only when we have a complete plan, we know that:

  • views contained in merge-able views have been merged and brought up in the top list of tables, so we only need to scan this list
  • table_list is not changing while we are reading it. If we don’t have a complete plan, EXPLAIN output does not contain table names, so we don’t need to check views.
Parameters
table_list table to start with, usually lex->query_tables
Returns
true Caller can’t EXPLAIN query due to lack of rights on a view in the query false Caller can EXPLAIN query

Оптимизация использования индексов

Как я уже говорил, MySQL может не использовать индексы, даже когда они присутствуют! Для примера возьмем ту же таблицу test2, и извлечем из нее все значения, у которых id > 1 (это 9998 записей), а затем все значения, у которых id > 123456 (это 0 записей):

mysql> EXPLAIN SELECT * FROM test2 WHERE id > 1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test2 | ALL  | PRIMARY       | NULL | NULL    | NULL | 9999 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
mysql> EXPLAIN SELECT * FROM test2 WHERE id > 123456;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | test2 | range | PRIMARY       | PRIMARY | 4       | NULL |   13 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

В первом случае, индекс по полю id не используется. Во втором, MySQL заранее знает, что таких значений не более 13, и потому использует индекс (см. поле key). Но мы можем замедлить запрос, вызвав поиндексное сканирование таблицы при помощи инструкции FORCE INDEX(PRIMARY). Если в таблице имеется несколько индексов, можно указать любой из них. Для использования основного индекса, применяется служебное слово PRIMARY.

mysql> SELECT * FROM test2 WHERE id > 1;
9998 rows in set (0.08 sec)
mysql> SELECT * FROM test2 FORCE INDEX(PRIMARY) WHERE id > 1;
9998 rows in set (0.23 sec)

А вот планы выполнения обоих запросов:

mysql> EXPLAIN SELECT * FROM test2 FORCE INDEX(PRIMARY) WHERE id > 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | test2 | range | PRIMARY       | PRIMARY | 4       | NULL | 9998 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM test2 WHERE id > 1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test2 | ALL  | PRIMARY       | NULL | NULL    | NULL | 9999 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Также необходимо иметь ввиду, что база данных не будет использовать более одного индекса, и при помощи FORCE INDEX можно заставить ее использовать именно тот индекс, который нужен (если он определен неправильно). Для определения нужного индекса, используется максимально «уникальный» индекс. Узнать, какой из индексов максимально привлекателен, можно при помощи запроса SHOW KEYS FROM test2. Уникальность индекса характеризует столбец Cardinality.

Сжатие таблиц MyISAM в MySQL

Для сжатия таблиц формата Myisam, нужно использовать специальный запрос с консоли сервера, а не в консоли mysql. Чтобы сжать нужную таблицу выполните:

Где /var/lib/mysql/test/modx_session — путь до вашей таблицы. К сожалению, у меня не было раздутой БД и пришлось выполнять сжатие на небольших таблицах, но результат все равно виден (файл сжался с 25 до 18 Мб):

25M modx_session.MYD
Compressing /var/lib/mysql/test/modx_session.MYD: (4933 records)
- Calculating statistics
- Compressing file
29.84%
Remember to run myisamchk -rq on compressed tables
18M modx_session.MYD

В запросе, мы указали ключ -b, при его добавлении, перед сжатием создается бэкап таблицы и помечается как OLD:

-rw-r----- 1 mysql mysql 25550000 Dec 17 15:20 modx_session.OLD
25M modx_session.OLD

Exemple

Pour expliquer concrètement le fonctionnement de l’instruction EXPLAIN nous allons prendre une table des fuseaux horaires en PHP. Cette table peut être créé à partir de la requête SQL suivante :

CREATE TABLE IF NOT EXISTS `timezones` (
 `timezone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `timezone_groupe_fr` varchar(50) DEFAULT NULL,
 `timezone_groupe_en` varchar(50) DEFAULT NULL,
 `timezone_detail` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`timezone_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf-8 AUTO_INCREMENT=698;

La requête ci-dessous permet de mieux comprendre la structure et les index de cette table.

Imaginons que l’ont souhaite compter le nombre de fuseaux horaires par groupe, pour cela il est possible d’utiliser la requête SQL suivante :

SELECT timezone_groupe_fr, COUNT(timezone_detail) AS total_timezone
FROM `timezones` 
GROUP BY timezone_groupe_fr
ORDER BY timezone_groupe_fr ASC

Заключение

В этом посте представлены несколько полезных методов профилирования запросов, в которых используются несколько встроенных инструментов сервера MariaDB: журнал медленных запросов и схема производительности. Журнал медленных запросов записывает запросы, которые считаются медленными и потенциально проблематичными, то есть запросы, которые занимают больше времени, чем ожидаемое значение глобальной системной переменной long_query_time. Журнал медленных запросов можно просмотреть с помощью любого текстового редактора. В качестве альтернативы инструмент mysqldumpslow MariaDB может упростить процесс, обобщая информацию. Полученные строки более читабельны, а также сгруппированы по запросу. Схема производительности – это механизм хранения, который содержит базу данных с именем performance_schema, которая, в свою очередь, состоит из нескольких таблиц, которые могут быть запрошены с помощью регулярных операторов SQL для широкого спектра информации о производительности.


С этим читают