How to tune SQL to a system operating with MySQL Part 2 ~SQL analysis~

This is Haraoka from Beyond.

This is a continuation from the previous content

How to tune SQL to a system running on MySQL - Discover the bottleneck -

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

Once you find a heavy SQL, you need to eliminate the cause.
There are various ways to fix the SQL, but
the basic idea is to use indexes appropriately.

・What is an index?

An index is, as the word suggests,
a table that allows quick access to data.
The most commonly used basic index structure is the B-tree index.

[For more details, see below]
wiki index (database)
Introduction to B-tree indexes

as using indexes properly
can often result in a difference of not just a few times but hundreds or even thousands of times.

・How to use EXPLAIN

EXPLAIN is used to analyze SQL.
It is easy to use; just add "EXPLAIN" before the SQL 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

select_type
Be careful if there is a DEPENDENT SUBQUERY (correlated subquery)

type
ALL A full scan of the table is being performed, so it is heavy. Use an index.
However, if the number of data items is small (several hundred), it will not have much of an impact.
INDEX A full scan of the index is being performed, so it is heavy.

key:
Used index, NULL means not used

Estimated number of rows

Be careful if you see Extra

[Reference]
Summary of Index Tuning Tips 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 line called "Cardinality," which indicates the type of column value.
The larger this number, the more efficient the index at narrowing down (high cardinality), and
the smaller this number, the less efficient the index at narrowing down (low cardinality).
Try to create an index that is as efficient as 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);

Create, delete, display, and add reference

Once the index is created, run explain again to check the effect.
Also, run the SQL as is and compare the execution speed before and after adding the index.
Repeat this process patiently until you find a method that works.

·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 reference articles]
MySQL tuning seminar held within Yahoo! Japan revealed