How to tune SQL in a system running MySQL Part 2: SQL analysis

This is Haraoka from Beyond

This is a continuation from the previous content

How to tune SQL in a MySQL system: Find bottlenecks

This time, we will show you how to analyze the SQL that was found to be a bottleneck

If you discover a slow SQL query, you should eliminate its cause.
There are various ways to fix SQL queries, but
the basic principle is to ensure that indexes are used appropriately.

・What is an index?

An index, as the name suggests,
is a heading used to quickly access data.
The most commonly used basic index is a B-tree index.

[See below for details]
wiki Index (database)
Introduction to B-tree indexes

between using indexes properly and not using them
can be not just several times, but hundreds or even thousands of times greater, making them extremely effective.

・How to use EXPLAIN

To analyze SQL, use EXPLAIN.
It's easy to use: simply add "EXPLAIN" before the SQL statement you want to analyze.

When you analyze using explain, the analysis results will be output as shown below

For primary key search

mysql > explain select col_b from tbl_a where col_b=100000; +----+-------------+---------------+-------+---------------+---------+---------+-------+------+------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+-------+---------------+---------+---------+-------+------+------+ | 1 | SIMPLE | tbl_a | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | (0.01 sec)

For index searches

mysql > explain select col_b from tbl_a where col_c=2000000; +----+-------------+---------------+-------+---------------+-------------+---------+-------+------+------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+-------+---------------+-------------+---------+-------+------+-------+ | 1 | SIMPLE | tbl_a | const | col_c | col_c | 4 | const | 1 | NULL |

For a full scan

mysql > explain select col_b from tbl_a where col_d='aaa'; +----+-------------+---------------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+------+-----+------+---------+------+-------+-------------+ | 1 | SIMPLE | tbl_a | ALL | NULL | NULL | NULL | NULL | 500000 | Using where |

If you have "using filesort"

mysql > explain select col_b from tbl_a where col_d='aaa' order by col_e; +----+-------------+---------------+------+---------------+------+---------+------+---------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+------+-----+------+---------+------+-------+--------------------------+ | 1 | SIMPLE | tbl_a | ALL | NULL | NULL | NULL | NULL | 500000 | Using where;

Table 8.1 EXPLAIN Output Columns

・Points to note

a `select_type
Be careful if there is

The `type
ALL` option performs a full scan of the table, which is slow. You should use an index.
However, this won't have much impact if the number of data records is small (around a few hundred).
The `INDEX` option performs a full scan of the index, which is slow.

Key
indicates an index used; NULL indicates it is not used.

rows
; generally, a large value of this should be a cause for concern.

"Extra
Using filesort" or "Using temporary".

[Reference]
A Summary of Key Points for Index Tuning for MySQL Beginners (2014)

・Check table structure and indexes

Check table structure with desc

mysql > desc tbl_a; +----------------------+----------------------+------+------+-------+-------+ | Field | Type | Null | Key | Default | Extra | NO | MUL | NULL | | | col_d | varchar(20) | NO | | 0 | | | | col_e | int(5) | NO | | NULL |

Check the index with show index

mysql> show index from account_public; | +---------------+------------+-----------------+--------------+-----------------+----------+-------------+----------+---------+------+------------+---------+---------------+ | tbl_a | 0 | PRIMARY | 1 | col_b | A | 500000 | NULL | NULL | | BTREE | | | | tbl_a | 0 | col_c | 1 | col_c | A | 80000 | NULL | NULL | | BTREE | | | |

There is a row called "Cardinality," which represents the number of values ​​in the column.
The larger this number, the more efficient the index is at filtering (high cardinality), and
the smaller the number, the less efficient the index is at filtering (low cardinality).
Let's create the most efficient index possible.

・Create an index

If you find ALL full scan, index full scan, or Using filesort, create an index on the target table

CREATE INDEX col_d ON tbl_a(col_d);

reference
Creating, deleting, displaying, and adding

Once the index is created, run `explain` again to check its effect.
Also, run the SQL query as is and compare the execution speed before and after creating the index.
Be patient and repeat this process until you find a method that is effective.

·summary

• Use EXPLAIN to analyze SQL
• Switch to indexed searches whenever possible
• Use index if type=ALL,type=INDEX
• Be careful with using filesort and using temporary
• If the rows are large, make them as small as possible
• Create and use high-cardinality indexes

In addition

• Subqueries are slow, so avoid them if possible
• Avoid unnecessary joins

[Other related articles]
Yahoo! Japan's internal MySQL tuning seminar revealed