您当前的位置: 首页 » MySQL/MSSQL编程 » Monty说MySQL的优化（五）
Monty说MySQL的优化（五）
论坛链接
文是Monty在O'Reilly Open Source Convention 2000大会上的演讲之五，涉及MySQL表高速缓存工作原理、MySQL扩展/优化-提供更快的速度、MySQL何时使用索引、学会使用 EXPLAIN、使用SHOW PROCESSLIST五个方面，是篇详尽的MySQL优化文档
二十一、MySQL表高速缓存工作原理
每个MyISAM表的打开实例(instance)使用一个索引文件和一个数据文件。如果表被两个线程使用或在同一条查询中使用两次，MyIASM将共享索引文件而是打开数据文件的另一个实例。
如果所有在高速缓存中的表都在使用，缓存将临时增加到比表缓存尺寸大些。如果是这样，下一个被释放的表将被关闭。
你可以通过检查mysqld的Opened_tables变量以检查表缓存是否太小。如果该值太高，你应该增大表高速缓存。
二十二、MySQL扩展/优化-提供更快的速度
使用优化的表类型（HEAP、MyIASM或BDB表）。
对数据使用优化的列。
如果可能使用定长行。
使用不同的锁定类型（SELECT HIGH_PRIORITY，INSERT LOW_PRIORITY）
Auto_increment
REPLACE (REPLACE INTO table_name VALUES (...))
INSERT DELAYED
LOAD DATA INFILE / LOAD_FILE()
使用多行INSERT一次插入多行。
SELECT INTO OUTFILE
LEFT JOIN, STRAIGHT JOIN
LEFT JOIN ，结合IS NULL
ORDER BY可在某些情况下使用键码。
如果只查询在一个索引中的列，将只使用索引树解决查询。
联结一般比子查询快（对大多数SQL服务器亦如此）。
LIMIT
SELECT * from table1 WHERE a > 10 LIMIT 10,20
DELETE * from table1 WHERE a > 10 LIMIT 10
foo IN (常数列表) 高度优化。
GET_LOCK()/RELEASE_LOCK()
LOCK TABLES
INSERT和SELECT可同时运行。
UDF函数可装载进一个正在运行的服务器。
压缩只读表。
CREATE TEMPORARY TABLE
CREATE TABLE .. SELECT
带RAID选项的MyIASM表将文件分割成很多文件以突破某些文件系统的2G限制。
Delay_keys
复制功能
二十二、MySQL何时使用索引
对一个键码使用>, >=, =, 1 and key_part1 < 90
如果使用HEAP表且不用=搜索所有键码部分。
在HEAP表上使用ORDER BY。
如果不是用键码第一部分
SELECT * FROM table_name WHERE key_part2=1
如果使用以一个通配符开始的LIKE
SELECT * FROM table_name WHERE key_part1 LIKE '%jani%'
搜索一个索引而在另一个索引上做ORDER BY
SELECT * from table_name WHERE key_part1 = # ORDER BY key2
二十四、学会使用EXPLAIN
对于每一条你认为太慢的查询使用EXPLAIN!
mysql> explain select t3.DateOfAction, t1.TransactionID
-> from t1 join t2 join t3
-> where t2.ID = t1.TransactionID and t3.ID = t2.GroupID
-> order by t3.DateOfAction, t1.TransactionID;
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
| t1 | ALL | NULL | NULL | NULL | NULL | 11 | Using temporary; Using filesort |
| t2 | ref | ID | ID | 4 | t1.TransactionID | 13 | |
| t3 | eq_ref | PRIMARY | PRIMARY | 4 | t2.GroupID | 1 | |
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
ALL和范围类型提示一个潜在的问题。
二十五、学会使用SHOW PROCESSLIST
使用SHOW processlist来发现正在做什么：
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+
| 6 | monty | localhost | bp | Query | 15 | Sending data | select * from station,station as s1 |
| 8 | monty | localhost | | Query | 0 | | show processlist |
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+
在mysql或mysqladmin中用KILL来杀死溜掉的线程。
TAG: Monty mysql MySQL Mysql
二十一、MySQL表高速缓存工作原理
每个MyISAM表的打开实例(instance)使用一个索引文件和一个数据文件。如果表被两个线程使用或在同一条查询中使用两次，MyIASM将共享索引文件而是打开数据文件的另一个实例。
如果所有在高速缓存中的表都在使用，缓存将临时增加到比表缓存尺寸大些。如果是这样，下一个被释放的表将被关闭。
你可以通过检查mysqld的Opened_tables变量以检查表缓存是否太小。如果该值太高，你应该增大表高速缓存。
二十二、MySQL扩展/优化-提供更快的速度
使用优化的表类型（HEAP、MyIASM或BDB表）。
对数据使用优化的列。
如果可能使用定长行。
使用不同的锁定类型（SELECT HIGH_PRIORITY，INSERT LOW_PRIORITY）
Auto_increment
REPLACE (REPLACE INTO table_name VALUES (...))
INSERT DELAYED
LOAD DATA INFILE / LOAD_FILE()
使用多行INSERT一次插入多行。
SELECT INTO OUTFILE
LEFT JOIN, STRAIGHT JOIN
LEFT JOIN ，结合IS NULL
ORDER BY可在某些情况下使用键码。
如果只查询在一个索引中的列，将只使用索引树解决查询。
联结一般比子查询快（对大多数SQL服务器亦如此）。
LIMIT
SELECT * from table1 WHERE a > 10 LIMIT 10,20
DELETE * from table1 WHERE a > 10 LIMIT 10
foo IN (常数列表) 高度优化。
GET_LOCK()/RELEASE_LOCK()
LOCK TABLES
INSERT和SELECT可同时运行。
UDF函数可装载进一个正在运行的服务器。
压缩只读表。
CREATE TEMPORARY TABLE
CREATE TABLE .. SELECT
带RAID选项的MyIASM表将文件分割成很多文件以突破某些文件系统的2G限制。
Delay_keys
复制功能
二十二、MySQL何时使用索引
对一个键码使用>, >=, =, 1 and key_part1 < 90
如果使用HEAP表且不用=搜索所有键码部分。
在HEAP表上使用ORDER BY。
如果不是用键码第一部分
SELECT * FROM table_name WHERE key_part2=1
如果使用以一个通配符开始的LIKE
SELECT * FROM table_name WHERE key_part1 LIKE '%jani%'
搜索一个索引而在另一个索引上做ORDER BY
SELECT * from table_name WHERE key_part1 = # ORDER BY key2
二十四、学会使用EXPLAIN
对于每一条你认为太慢的查询使用EXPLAIN!
mysql> explain select t3.DateOfAction, t1.TransactionID
-> from t1 join t2 join t3
-> where t2.ID = t1.TransactionID and t3.ID = t2.GroupID
-> order by t3.DateOfAction, t1.TransactionID;
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
| t1 | ALL | NULL | NULL | NULL | NULL | 11 | Using temporary; Using filesort |
| t2 | ref | ID | ID | 4 | t1.TransactionID | 13 | |
| t3 | eq_ref | PRIMARY | PRIMARY | 4 | t2.GroupID | 1 | |
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
ALL和范围类型提示一个潜在的问题。
二十五、学会使用SHOW PROCESSLIST
使用SHOW processlist来发现正在做什么：
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+
| 6 | monty | localhost | bp | Query | 15 | Sending data | select * from station,station as s1 |
| 8 | monty | localhost | | Query | 0 | | show processlist |
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+
在mysql或mysqladmin中用KILL来杀死溜掉的线程。
TAG: Monty mysql MySQL Mysql