Edit the MY.CNF or My.ini file to remove the comments from the following lines of code:
Copy Code code as follows:
Log_slow_queries =/var/log/mysql/mysql-slow.log
Long_query_time = 2
Log-queries-not-using-indexes
This will allow slow queries and queries that do not use indexes to be recorded.
After doing so, execute the tail-f command on the Mysql-slow.log file, and you will see the slow and unused indexes recorded in the query.
Random extraction of a slow query, the implementation of explain:
Copy Code code as follows:
you will see the following results：
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+
| 1 | Simple | some_table | All | NULL | NULL | NULL | NULL | 166 | Using where |
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+
Note the rows and key columns above. Rows shows how many rows the query affects, and we don't want this value to be too large. The key shows which index is used, or null to indicate that the query is not using any indexes.
If you want to make queries faster, you might want to add indexes for some columns:
CREATE INDEX myapp_mytable_myfield_idx on myapp_mytable (MyField);
In addition to configuring a MySQL profile to implement slow queries, there are a few ways to record slow queries:
Copy Code code as follows:
SELECT T.table_schema as ' db ',
T.table_name as ' TABLE ',
S.index_name as ' INDEX NAME ',
S.column_name as ' FIELD NAME ',
S.seq_in_index ' SEQ in INDEX ',
S2.max_columns as ' # cols ',
S.cardinality as ' card ',
T.table_rows as ' est ROWS ',
ROUND ((S.cardinality/ifnull (t.table_rows, 0.01)), 2) as ' sel% '
From INFORMATION_SCHEMA. STATISTICS s
INNER JOIN information_schema. TABLES T on s.table_schema = T.table_schema and s.table_name = T.table_name
INNER JOIN (
SELECT TABLE_SCHEMA, TABLE_NAME, index_name, MAX (seq_in_index) as Max_columns
From INFORMATION_SCHEMA. STATISTICS
WHERE table_schema!= ' MySQL ' GROUP by TABLE_SCHEMA, TABLE_NAME, index_name-as s2 on s.table_schema = s2. Table_schema and s.table_name = s2. table_name and s.index_name = s2. Index_name
WHERE t.table_schema!= ' mysql '/Filter out the MySQL system DB * *
T.table_rows >/* Only tables with some ROWS/*
And s.cardinality is not NULL/* Need at least one Non-null value in the field * *
and (S.cardinality/ifnull (t.table_rows, 0.01)) < 1.00/* Unique indexes are perfect anyway * *
ORDER BY ' sel% ', S.table_schema, s.table_name/* DESC for best non-unique indexes *
LIMIT 10;
