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, I will explain how to analyze the SQL that I found as a bottleneck.

If you find heavy SQL, remove the cause.
There are many ways to modify the SQL, but
the basic idea is to use indexes appropriately.

・What is an index?

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

[See below for details]
Wiki index (database)
Introduction to B-tree indexing

Using or not using indexes properly
can make a difference not just by several times, but by hundreds, even thousands of times, so it is extremely effective.

・How to use EXPLAIN

Use EXPLAIN to analyze SQL.
It is easy to use, just add "EXPLAIN" before the SQL you want to analyze.

If you use explain to analyze it, the following analysis result will be output.

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 | +----+-------------+---------------+---- ---+---------------+---------+---------+-------+-- ----+-------+ 1 row in set (0.01 sec)

For index search

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 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; Using filesort | +----+-------------+---------------+------+ ---------------+------+---------+------+---------+- ----------------------------+

Table 8.1 EXPLAIN output columns

・Points to pay attention to

Be careful if there is select_type

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 (about a few hundred), there will not be much of an impact.
INDEX A full scan of the index is being performed, heavy.

key
used index, if NULL it is not used

in rows
. Basically, be careful if this value is large.

Extra
Using filesort and Using temporary

[Reference]
Summary of index tuning points for MySQL beginners 2014

・Check table structure and indexes

Check the table structure with desc

mysql > desc tbl_a; +-----------------------+-------------------- -+------+------+----------------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+----------------- ----+------+-----+----------------------+-------+ | col_b | int(15) unsigned | NO | PRI | NULL | | | col_c | int(9) | 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; +---------------+------------+------------- ----+--------------+-----------------+------------+ -------------+----------+---------+------+--------- ---+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------------+------------+------------- ----+--------------+-----------------+------------+ -------------+----------+---------+------+--------- ---+---------+---------------+ | tbl_a | 0 | PRIMARY | 1 | col_b | A | 500000 | NULL | NULL | | BTREE | | | | tbl_a | 0 | col_c | 1 | col_c | A | 80000 | NULL | -------+-----------------+--------------+--------- --------+------------+-------------+------------+---- ----+------+------------+---------+--------------- +

There is a row called "Cardinality", which is the type of column value.
The larger this number, the higher the index filtering efficiency (higher cardinality), and
the smaller this number, the lower the index filtering efficiency (lower cardinality).
Create an index that is as efficient as possible.

・Add an index

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

CREATE INDEX col_d ON tbl_a(col_d);

Creation/deletion/information display/addition of reference index

Once the index is created, try explaining again and check the effect.
We will also run the SQL as is and compare the execution speed before and after adding the index.
Repeat patiently until you find a method that works.

·summary

• Use EXPLAIN to analyze SQL
• Switch to index-based searches as much as possible
• If type=ALL,type=INDEX, use index
• Be careful when using filesort and using temporary
• If rows is large, make it as small as possible
• Create and use high indexes with large cardinality

Incidentally

• Subqueries are slow so avoid them if possible.
• Don't make unnecessary connections

[Other reference articles]
MySQL tuning seminar held within Yahoo!