分类： Mysql/postgreSQL
2007-07-20 10:26:42
本章主要内容
l 查找慢速查询
l 查询基准
l 慢速查询日志
l 使用EXPLAIN查看查询的执行情况
l Mysql内置的查询优化
l 优化小技巧
查找慢速查询的方法有
1,观察, 比如一个具体的查询需要很长的时间
2,基准,测试应用程序,看哪部分比较慢
3,慢速查询日志
一般是优化那些经常使用的查询.
基准能较精确的计算出查询时间,最好是将一个查询执行多次,查看平均时间, 因为有cache.
可以使用外部程序或脚本执行多次查询,比如sql-bench., mysql的内置函数也可以实现此功能: 外部的实现今后在mysql test中学习
mysql> select benchmark(1000000, 6*9);
+-------------------------+
| benchmark(1000000, 6*9) |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.04 sec)
实际上的执行时间是: 1 row in set (0.04 sec)
mysql> select benchmark(1000000, 'show table status');
+-----------------------------------------+
| benchmark(1000000, 'show table status') |
+-----------------------------------------+
| 0 |
+-----------------------------------------+
1 row in set (0.01 sec
慢速查询的打开方式: --log-slow-queries=filename,如果你同是打开了log-long-format, 也会记录没有使用索引的查询. Mysqldumpslow 可以分析慢速查询,这是个perl脚本
mysql> explain select e.name, d.name from employee e, department d where e.departmentID = d.departmentID;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | d | ALL | PRIMARY | NULL | NULL | NULL | 4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)
mysql> desc employee;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| employeeID | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(80) | YES | | NULL | |
| job | varchar(30) | YES | | NULL | |
| departmentID | int(11) | NO | | | |
+--------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc department;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| departmentID | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> create index ename_did on employee(name, departmentID);
Query OK, 4 rows affected (0.13 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc employee;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| employeeID | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(80) | YES | MUL | NULL | |
| job | varchar(30) | YES | | NULL | |
| departmentID | int(11) | NO | | | |
+--------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> explain select e.name, d.name from employee e, department d where e.departmentID = d.departmentID;
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| 1 | SIMPLE | e | index | NULL | ename_did | 87 | NULL | 4 | Using index |
| 1 | SIMPLE | d | ALL | PRIMARY | NULL | NULL | NULL | 4 | Using where |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
2 rows in set (0.00 sec)
Mysql内置的查询优化暂略
优化小技巧有添加索引,使用ANALYZE TABLE和OPTIMIZE TABLE
