《MySQL管理员指南》之 MySQL的优化

 
《MySQL管理员指南》之 MySQL的优化
 

本文由正泰linux 阿泰qq：253222170

******************************************************************************

二十一、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何时使用索引

对一个键码使用>, >=, =, <, <=, IF NULL和BETWEEN
????SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5;
????SELECT * FROM table_name WHERE key_part1 IS NULL;

当使用不以通配符开始的LIKE
????SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'

在进行联结时从另一个表中提取行时
????SELECT * from t1,t2 where t1.col=t2.key_part

找出指定索引的MAX()或MIN()值
????SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10

一个键码的前缀使用ORDER BY或GROUP BY
????SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3

在所有用在查询中的列是键码的一部分时间
????SELECT key_part3 FROM table_name WHERE key_part1=1

--------------------------------------------------------------------------------

二十三、MySQL何时不使用索引

如果MySQL能估计出它将可能比扫描整张表还要快时，则不使用索引。例如如果key_part1均匀分布在1和100之间，下列查询中使用索引就不是很好：
????SELECT * FROM table_name where key_part1 > 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来杀死溜掉的线程。
--------------------------------------------------------------------------------

二十六、如何知晓MySQL解决一条查询

运行项列命令并试图弄明白其输出：
SHOW VARIABLES;
SHOW COLUMNS FROM ...\G
EXPLAIN SELECT ...\G
FLUSH STATUS;
SELECT ...;
SHOW STATUS;

--------------------------------------------------------------------------------

二十七、MySQL非常不错

日志
在进行很多连接时，连接非常快。
同时使用SELECT和INSERT的场合。
在不把更新与耗时太长的选择结合时。
在大多数选择/更新使用唯一键码时。
在使用没有长时间冲突锁定的多个表时。
在用大表时(MySQL使用一个非常紧凑的表格式)。

--------------------------------------------------------------------------------

二十八、MySQL应避免的事情

用删掉的行更新或插入表，结合要耗时长的SELECT。
在能放在WHERE子句中的列上用HAVING。
不使用键码或键码不够唯一而进行JOIN。
在不同列类型的列上JOIN。
在不使用=匹配整个键码时使用HEAP表。
在MySQL监控程序中忘记在UPDATE或DELETE中使用一条WHERE子句。如果想这样做，使用mysql客户程序的--i-am-a-dummy选项。

--------------------------------------------------------------------------------

二十九、MySQL各种锁定

内部表锁定
LOCK TABLES（所有表类型适用）
GET LOCK()/RELEASE LOCK()
页面锁定（对BDB表）
ALTER TABLE也在BDB表上进行表锁定
LOCK TABLES允许一个表有多个读者和一个写者。
一般WHERE锁定具有比READ锁定高的优先级以避免让写入方干等。对于不重要的写入方，可以使用LOW_PRIORITY关键字让锁定处理器优选读取方。
??UPDATE LOW_PRIORITY SET value=10 WHERE id=10;

????????

--------------------------------------------------------------------------------

三十、给MySQL更多信息以更好地解决问题的技巧

注意你总能去掉(加注释)MySQL功能以使查询可移植：

SELECT /*! SQL_BUFFER_RESULTS */ ...
SELECT SQL_BUFFER_RESULTS ...
将强制MySQL生成一个临时结果集。只要所有临时结果集生成后，所有表上的锁定均被释放。这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助。??
SELECT SQL_SMALL_RESULT ... GROUP BY ...
告诉优化器结果集将只包含很少的行。
SELECT SQL_BIG_RESULT ... GROUP BY ...
告诉优化器结果集将包含很多行。
SELECT STRAIGHT_JOIN ...
强制优化器以出现在FROM子句中的次序联结表。
SELECT ... FROM table_name [USE INDEX (index_list) | IGNORE INDEX (index_list)] table_name2
强制MySQL使用/忽略列出的索引。

--------------------------------------------------------------------------------

三十一、事务的例子

MyIASM表如何进行事务处理:
mysql> LOCK TABLES trans READ, customer WRITE;
mysql> select sum(value) from trans where customer_id=some_id;
mysql> update customer set total_value=sum_from_previous_statement
?????????? where customer_id=some_id;
mysql> UNLOCK TABLES;

BDB表如何进行事务:
mysql> BEGIN WORK;
mysql> select sum(value) from trans where customer_id=some_id;
mysql> update customer set total_value=sum_from_previous_statement
?????????? where customer_id=some_id;
mysql> COMMIT;

注意你可以通过下列语句回避事务:
UPDATE customer SET value=value+new_value WHERE customer_id=some_id;

--------------------------------------------------------------------------------

三十二、使用REPLACE的例子

REPLACE的功能极像INSERT，除了如果一条老记录在一个唯一索引上具有与新纪录相同的值，那么老记录在新纪录插入前则被删除。不使用

??SELECT 1 FROM t1 WHERE key=#
??IF found-row
????LOCK TABLES t1
????DELETE FROM t1 WHERE key1=#
????INSERT INTO t1 VALUES (...)
????UNLOCK TABLES t1;
??ENDIF

而用
??REPLACE INTO t1 VALUES (...)????

--------------------------------------------------------------------------------

三十三、一般技巧

使用短主键。联结表时使用数字而非字符串。
当使用多部分键码时，第一部分应该时最常用的部分。
有疑问时，首先使用更多重复的列以获得更好地键码压缩。
如果在同一台机器上运行MySQL客户和服务器，那么在连接MySQL时则使用套接字而不是TCP/IP（这可以提高性能7.5%）。可在连接MySQL服务器时不指定主机名或主机名为localhost来做到。
如果可能，使用--skip-locking(在某些OS上为默认)，这将关闭外部锁定并将提高性能。
使用应用层哈希值而非长键码：
??SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2)) AND
??col_1='constant' AND col_2='constant'

在文件中保存需要以文件形式访问的BLOB，在数据库中只保存文件名。
删除所有行比删除一大部分行要快。
如果SQL不够快，研究一下访问数据的较底层接口。

--------------------------------------------------------------------------------

三十四、使用MySQL 3.23的好处

MyISAM：可移植的大表格式
HEAP：内存中的表
Berkeley DB：支持事务的表。
众多提高的限制
动态字符集
更多的STATUS变量
CHECK和REPAIR表
更快的GROUP BY和DISTINCT
LEFT JOIN ... IF NULL的优化
CREATE TABLE ... SELECT
CREATE TEMPORARY table_name (...)
临时HEAP表到MyISAM表的自动转换
复制
mysqlhotcopy脚本

--------------------------------------------------------------------------------

三十五、正在积极开发的重要功能

改进事务处理
失败安全的复制
正文搜索
多个表的删除(之后完成多个表的更新)
更好的键码缓存
原子RENAME (RENAME TABLE foo as foo_old, foo_new as foo)
查询高速缓存
MERGE TABLES
一个更好的GUI客户程序