MySQL slow query and optimization test_mysql

Source: Internet
Author: User
MySQL slow query and Optimization Test bitsCN. comMySQL slow query and Optimization Test. Next we will introduce in detail. For more information, see.
This article reference from: http://www.jbxue.com/db/4376.html edit my. cnf or my. ini file, remove the following line of code comment: log_slow_queries =/var/log/mysql/mysql-slow.log long_query_time = 2
Log-queries-not-using-indexes
This will record slow queries and queries without indexes.
After doing so, execute the tail-f command on the mysql-slow.log file and you will be able to see slow queries that record them and queries that do not use indexes.
Extract A Slow query and execute explain:
Explain low_query
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 | 166 | Using where |
+ ---- + ------------- + --------------------- + ------ + ------------- + ------ + --------- + ------ + ------------- +
Note the preceding rows and key columns. Rows shows the number of rows affected by this query. We do not want to make this value too large. Key indicates which index is used. If it is NULL, no index is used for the query.
To make the query faster, you may need to add an index for some columns:
Create index myapp_mytable_myfield_idx on myapp_mytable (myfield );
In addition to configuring the mysql configuration file to record mysql slow queries, the following methods can be used to record slow queries:
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 ', -- // www.jbxue.com
ROUND (s. CARDINALITY/IFNULL (t. TABLE_ROWS, 0.01) * 100), 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 */
AND t. TABLE_ROWS> 10/* 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; bitsCN.com

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.