README ¶
expl
MySQL explain checker. This tool runs multiple SQL explain from SQL log.
- Read Log or DB(mysql.general_log table) and explain multiple SQL
- Content filtering, Display explain result with 'SELECT_TYPE', 'TABLE', 'TYPE', 'EXTRA' specified
installation
Linux
wget https://github.com/muroon/expl/releases/download/v1.0.6/expl_1.0.6_Linux_x86_64.tar.gz
mkdir expl_1.0.6_Linux_x86_64
tar -zxvf expl_1.0.6_Linux_x86_64.tar.gz -C expl_1.0.6_Linux_x86_64
ln -s expl_1.0.6_Linux_x86_64/expl /user/local/expl
Mac
curl -vLJO -H 'Accept: application/octet-stream' https://github.com/muroon/expl/releases/download/v1.0.6/expl_1.0.6_Darwin_x86_64.tar.gz
mkdir expl_1.0.6_Darwin_x86_64
tar -zxvf expl_1.0.6_Darwin_x86_64.tar.gz -C expl_1.0.6_Darwin_x86_64
ln -s expl_1.0.6_Darwin_x86_64/expl /user/local/expl
Go
go get github.com/muroon/expl/cmd/expl
simple usage
expl explain simple "select * from memo" --database database1 --host localhost --user root --pass ""
DataBase: database1
SQL: select * from memo
+----+------------+-------+------------+------+--------------+-----+--------+-----+------+----------+-------+
| ID | SELECTTYPE | TABLE | PARTITIONS | TYPE | POSSIBLEKEYS | KEY | KEYLEN | REF | ROWS | FILTERED | EXTRA |
+----+------------+-------+------------+------+--------------+-----+--------+-----+------+----------+-------+
| 1 | SIMPLE | memo | | ALL | | | 0 | | 39 | 100.0000 | |
+----+------------+-------+------------+------+--------------+-----+--------+-----+------+----------+-------+
the case with logs of multiple databases in one file
1. Create config file ("conf" sub command)
Make config file in YAML format. This includes relationships between tables and databases.
- add setting in config file
# expl conf add --host host --database database --user user --pass password --conf config_file_path
expl conf add --host localhost --database database1 --user root --pass "" --conf config.yaml
expl conf add --host localhost --database database2 --user root --pass "" --conf config.yaml
2. Execute Explian ("explain" sub command)
Execute explain multipule SQL
- make mapping relationships between tables and databases in config YAML file
- execute explain SQL using using table-database mapping
This has advantages such as using the "Combine SQL" (see below)
#expl explain mode -c config_file_path --format format_type --log sql_log_file_path
expl explain log -c config.yaml --format simple --log simple.yaml
explain sub command
mode parameter
2nd Parameter
- simple : SQL direct input. The third parameter is sql.
- log : Getting SQL from log file. (official generate log or custom log file)
- log-db : Getting SQL from database. (mysql.general_log table)
# simple mode
expl explain simple "select * from memo" --database database1 --host localhost --user root --pass ""
# log mode
expl explain log --conf config.yaml --format official --log sql.log
# log-db mode
expl explain log-db --conf config.yaml --format official
conf option
This is Config file path. The config file includes host, database, user, password and table-database mapping.
log option
This is Log file path.
format option
format of one line in SQL log file.
- simple : Raw SQL
- official : Same log format of MySQL general_log. https://dev.mysql.com/doc/refman/5.6/en/query-log.html
- command : Edit by OS command
# simple format
expl explain simple "select * from memo" --database database1 --host localhost --user root --pass ""
# official format
expl explain log --conf config.yaml --format official --log /var/lib/mysql/general_sql.log
# command format
expl explain log --conf config.yaml --log custom_sql.log --format command --format-cmd "cut -c 21-"
format-cmd option
Using only "command" format. OS command for edit line of log to raw SQL.
expl explain log --conf config.yaml --log custom_sql.log --format command --format-cmd "cut -c 21-"
# same (using pipe simple mode)
cut -c 21- custom_sql.log | xargs -I$ expl explain simple "$" --conf config.yaml --format command --format-cmd "cut -c 21-"
Combine SQL option
Display the same type of SQL results in one view. The two SQL statements below are identical to the explain result. Thus, SQLs of the same type are displayed together in one
# sql1
select * from memo where id = 1;
# sql2
select * from memo where id = 100;
expl explain log --conf config.yaml --format official --log /var/lib/mysql/general_sql.log --combine-sql
filter option
Filtering the explain results
option | meaning |
---|---|
filter-select-type | Show only results included in the specified "Select Type" of explain |
filter-no-select-type | Show only results that are not included in the specified "Select Type" of explain |
filter-table | Show only results that contain the specified table |
filter-no-table | Show only results that do not contain the specified table |
filter-type | Show only results included in the specified "Type" of explain |
filter-no-type | Show only results that are not included in the specified "Type" of explain |
filter-possible-keys | Show only results included in the specified "Possible Keys" of explain |
filter-no-possible-keys | Show only results that are not included in the specified "Possible Keys" of explain |
filter-key | Show only results included in the specified "Key" of explain |
filter-no-key | Show only results that are not included in the specified "Key" of explain |
filter-extra | Show only results included in the specified "Extra" of explain |
filter-no-extra | Show only results that are not included in the specified "Extra" of explain |
# view only results where includes "ALL" in TYPE column.
expl explain log --conf config.yaml --format official --log /var/lib/mysql/general_sql.log --filter-type ALL
DataBase: memo_sample
SQL: select tag.* from tag, tag_memo where tag.id = tag_memo.tag_id
+----+------------+----------+------------+------+--------------+---------+--------+--------------------+------+----------+-------------+
| ID | SELECTTYPE | TABLE | PARTITIONS | TYPE | POSSIBLEKEYS | KEY | KEYLEN | REF | ROWS | FILTERED | EXTRA |
+----+------------+----------+------------+------+--------------+---------+--------+--------------------+------+----------+-------------+
| 1 | SIMPLE | tag | | ALL | PRIMARY | | 0 | | 22 | 100.0000 | |
| 1 | SIMPLE | tag_memo | | ref | PRIMARY | PRIMARY | 4 | memo_sample.tag.id | 1 | 100.0000 | Using index |
+----+------------+----------+------------+------+--------------+---------+--------+--------------------+------+----------+-------------+
ignore error option
This is to ignore the "Explain SQL Error" or "SQL Parse Error".
expl explain log --conf config.yaml --format official --log /var/lib/mysql/general_sql.log --ignore-error
With SQL parse or explain SQL errors, let's try use this option.
- If table "user" exists in "database1" and "database2", this tool will try to explain both "database1" and "database2". In this case, this option is useful to ignore the error and do the following processing.
- If the log contains only one unparsable string, using this option will not stop the execution of subsequent correct SQL statement lines.
option file option
Using file for option settings You can use YAML files instead of directly specifying options in the command
If there are duplicate definitions, priority is given in the following order
- Command
- env
- option file
expl explain log --option-file ./option.yaml --filter-extra "using where"
verbose output option
Display the value of the option just before execution
useful sample
Extracts results where Index is not used even if a specific column is used in the Where clause
expl explain log --log sql.log --filter-type ALL --filter-extra "using where" --filter-key ""