mysql优化

<摘抄的mysql优化，深有体会，用以备忘>

1. 优化应用

应该集中精力解决问题。
在编写应用时，应该决定什么是最重要的：
速度
操作系统间的可移植性
SQL服务器间的可移植性
使用持续的连接。
缓存应用中的数据以减少SQL服务器的负载。
不要查询应用中不需要的列。
不要使用SELECT * FROM table_name...
测试应用的所有部分，但将大部分精力放在在可能最坏的合理的负载下的测试整体应用。通过以一种模块化的方式进行，你应该能用一个快速“哑模块”替代找到的瓶颈，然后很容易地标出下一个瓶颈。
如果在一个批处理中进行大量修改，使用LOCK TABLES。例如将多个UPDATES或DELETES集中在一起。

2. 如果你需要更快的速度

应该找出瓶颈(CPU、磁盘、内存、SQL服务器、操作系统、API或应用)并集中全力解决。
使用给予你更快速度/灵活性的扩展。
逐渐了解SQL服务器以便能为你的问题使用可能最快的SQL构造并避免瓶颈。
优化表布局和查询。
使用复制以获得更快的选择(select)速度。
如果你有一个慢速的网络连接数据库，使用压缩客户/服务器协议。
不要害怕时应用的第一个版本不能完美地移植，在你解决问题时，你总是可以在以后优化它。

3. 优化SQL

扬SQL之长，其它事情交由应用去做。使用SQL服务器来做：
找出基于WHERE子句的行。
JOIN表
GROUP BY
ORDER BY
DISTINCT
不要使用SQL来做：
检验数据(如日期)
成为一只计算器
技巧：
明智地使用键码。
键码适合搜索，但不适合索引列的插入/更新。
保持数据为数据库第三范式，但不要担心冗余信息或这如果你需要更快的速度，创建总结表。
在大表上不做GROUP BY，相反创建大表的总结表并查询它。
UPDATE table set count=count+1 where key_column=constant非常快。
对于大表，或许最好偶尔生成总结表而不是一直保持总结表。
充分利用INSERT的默认值。

4. 优化表

MySQL拥有一套丰富的类型。你应该对每一列尝试使用最有效的类型。
ANALYSE过程可以帮助你找到表的最优类型：SELECT * FROM table_name PROCEDURE ANALYSE()。
对于不保存NULL值的列使用NOT NULL，这对你想索引的列尤其重要。
将ISAM类型的表改为MyISAM。
如果可能，用固定的表格式创建表。
不要索引你不想用的东西。
利用MySQL能按一个索引的前缀进行查询的事实。如果你有索引INDEX(a,b)，你不需要在a上的索引。
不在长CHAR/VARCHAR列上创建索引，而只索引列的一个前缀以节省存储空间。CREATE TABLE table_name (hostname CHAR(255) not null, index(hostname(10)))
对每个表使用最有效的表格式。
在不同表中保存相同信息的列应该有同样的定义并具有相同的列名。

5. 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

6. 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

7. 学会使用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和范围类型提示一个潜在的问题。

8. 学会使用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来杀死溜掉的线程。

9. MySQL非常不错

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

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使用/忽略列出的索引。

11. 使用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 (...)