Home > Database > Mysql Tutorial > MySQL慢查询查找和调优测试_MySQL

MySQL慢查询查找和调优测试_MySQL

WBOY
Release: 2016-06-01 13:28:12
Original
1117 people have browsed it

bitsCN.com

MySQL慢查询查找和调优测试,接下来详细介绍,需要了解的朋友可以参考下。
本文参考自:http://www.jbxue.com/db/4376.html

 编辑 my.cnf或者my.ini文件,去除下面这几行代码的注释: log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 2 
log-queries-not-using-indexes 
这将使得慢查询和没有使用索引的查询被记录下来。 
这样做之后,对mysql-slow.log文件执行tail -f命令,将能看到其中记录的慢查询和未使用索引的查询。 
随便提取一个慢查询,执行explain:
explain low_query  
你将看到下面的结果: 
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+ 
| 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 | 
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+ 
注意上面的rows和key列。rows显示该查询影响了多少行记录,我们不想让这个值太大。key显示用了哪个索引,为NULL时表示查询未用任何索引。 
如果想让查询更快,你或许需要为某些列增加索引: 
CREATE INDEX myapp_mytable_myfield_idx on myapp_mytable(myfield); 
除了配置mysql配置文件来实现记录mysql慢查询外,还有下面的方法可以记录慢查询: 
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)) ORDER BY `sel %`, s.TABLE_SCHEMA, s.TABLE_NAME /* DESC for best non-unique indexes */ 
LIMIT 10; bitsCN.com
Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template