ArthurXF从业10年的CTO,受上海非凡学院邀请,进行PHP,CSS,FREEBSD等网络技能授课培训,有意向参加的,请联系.
2007/08/24 11:47
对于稍微大型的项目来说，数据库无疑是其中最难设计的一块，而数据库的优化又尤其重要。笔者在开发中总结了一些人的信息。
为了速度，在确保查询已经最优的前提，就是以牺牲空间来获取速度，比如分表，建立索引
我碰到以下几种情况会导致查询速度慢
1、无索引 ，必要的索引可以很大地提高速度，但对字符型效果不大， 查询涉及关键的类别， 尽量用数字，然后建立索引
2、效率低的SQL语句，比如用了大量的 IN ，NOT IN （这个在MYSQL4.x好象没有 ：））
如果有几个条件， 可以选产生结果最小的做为首次查询， 因为MYSQL 4。X不支持子查询， 可以考虑建立临时表。
3、数据库服务器内存太小 ，这个你应该知道该如何办 ：）
4、表太大，可以根据主要分类，把表分解 ，或者常用的字段合成一个表，不常用的分成一个表
比如全国类型的企业信息，可以按省分，可以按行业分，
按省分后，每个省再分3个表， 地址表（名称、地址、邮政编码），企业信息表（行业，规模，。。。），通信表（电话、传真、邮件、主页）
Mysql中有查询高速缓存,使用高速缓存的语句格式:select sql_cache * from tableA where ...
EXPLAIN sql执行语句
mysql>
SELECT COUNT(*) FROM Headline WHERE ExpireTime >= 1112201600;
引用
+----------+
| COUNT(*) |
+----------+
| 3971 |
+----------+
1 row in set (1.04 sec)
mysql>
EXPLAIN SELECT COUNT(*) FROM Headline WHERE ExpireTime >= 1112201600 \G
引用
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Headline
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 302116
Extra: Using where
1 row in set (0.00 sec)
The NULL value in the key column of the EXPLAIN output tell us that MySQL won't be using an index for this query. In fact, the NULL value in the possible_keys column tells us that there were no indexes to pick from at all. If this type of query is likely to be common, we can simply add an index and rerun the query (or the EXPLAIN) to verify that MySQL uses it.
1、选取最适用的字段属性
MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。
另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。
2、使用连接（JOIN）来代替子查询(Sub-Queries)
MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：
DELETE FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）.. 替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：
SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
如果使用连接（JOIN）.. 来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：
SELECT * FROM customerinfo
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.
CustomerID
WHERE salesinfo.CustomerID IS NULL
连接（JOIN）.. 之所以更有效率一些，是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
3、使用联合(UNION)来代替手动创建的临时表
MySQL 从 4.0 的版本开始支持 UNION 查询，它可以把需要使用临时表的两条或更多的 SELECT 查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用 UNION 来创建查询的时候，我们只需要用 UNION作为关键字把多个 SELECT 语句连接起来就可以了，要注意的是所有 SELECT 语句中的字段数目要想同。下面的例子就演示了一个使用 UNION的查询。
SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author
UNION
SELECT Name, Supplier FROM product
4、事务
尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN 关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。
BEGIN;
INSERT INTO salesinfo SET CustomerID=14;
UPDATE inventory SET Quantity=11
WHERE item='book';
COMMIT;
事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。
5、锁定表
尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户
来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。
其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。
LOCK TABLE inventory WRITE
SELECT Quantity FROM inventory
WHEREItem='book';
...
UPDATE inventory SET Quantity=11
WHEREItem='book';
UNLOCK TABLES
这里，我们用一个 SELECT 语句取出初始数据，通过一些计算，用 UPDATE 语句将新值更新到表中。包含有 WRITE 关键字的 LOCK TABLE 语句可以保证在 UNLOCK TABLES 命令被执行之前，不会有其它的访问来对 inventory 进行插入、更新或者删除的操作。
6、使用外键
锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo 表中的CustomerID映射到salesinfo表中CustomerID，任何一条没有合法CustomerID的记录都不会被更新或插入到salesinfo中。
CREATE TABLE customerinfo
(
CustomerID INT NOT NULL ,
PRIMARY KEY ( CustomerID )
) TYPE = INNODB;
CREATE TABLE salesinfo
(
SalesID INT NOT NULL,
CustomerID INT NOT NULL,
PRIMARY KEY(CustomerID, SalesID),
FOREIGN KEY (CustomerID) REFERENCES customerinfo
(CustomerID) ON DELETECASCADE
) TYPE = INNODB;
注意例子中的参数“ON DELETE CASCADE”。该参数保证当 customerinfo 表中的一条客户记录被删除的时候，salesinfo 表中所有与该客户相关的记录也会被自动删除。如果要在 MySQL 中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表 InnoDB类型。该类型不是 MySQL 表的默认类型。定义的方法是在 CREATE TABLE 语句中加上 TYPE=INNODB。如例中所示。
7、使用索引
索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(), MIN()和ORDERBY这些命令的时候，性能提高更为明显。那该对哪些字段建立索引呢？一般说来，索引应建立在那些将用于JOIN, WHERE判断和ORDER BY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况，例如customerinfo中的“province”.. 字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTER TABLE或CREATE INDEX在以后创建索引。此外，MySQL
从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL 中是一个FULLTEXT类型索引，但仅能用于MyISAM 类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTER TABLE或CREATE INDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。
8、优化的查询语句
绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。下面是应该注意的几个方面。首先，最好是在相同类型的字段间进行比较的操作。在MySQL 3.23版之前，这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。其次，在建有索引的字段上尽量不要使用函数进行操作。
例如，在一个DATE类型的字段上使用YEAE()函数时，将会使索引不能发挥应有的作用。所以，下面的两个查询虽然返回的结果一样，但后者要比前者快得多。
SELECT * FROM order WHERE YEAR(OrderDate)<2001;
SELECT * FROM order WHERE OrderDate<"2001-01-01";
同样的情形也会发生在对数值型字段进行计算的时候：
SELECT * FROM inventory WHERE Amount/7<24;
SELECT * FROM inventory WHERE Amount<24*7;
上面的两个查询也是返回相同的结果，但后面的查询将比前面的一个快很多。第三，在搜索字符型字段时，我们有时会使用 LIKE 关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。例如下面的查询将会比较表中的每一条记录。
SELECT * FROM books
WHERE name like "MySQL%"
但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：..
SELECT * FROM books
WHERE name>="MySQL"and name<"MySQM"
最后，应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。
索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。
假设我们创建了一个名为people的表：
CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL );
然后，我们完全随机把1000个不同name值插入到people表。下图显示了people表所在数据文件的一小部分：
可以看到，在数据文件中name列没有任何明确的次序。如果我们创建了name列的索引，MySQL将在索引中排序name列：
对于索引中的每一项，MySQL在内部为它保存一个数据文件中实际记录所在位置的“指针”。因此，如果我们要查找name等于“Mike”记录的peopleid（SQL命令为“SELECT peopleid FROM people WHERE name='Mike';”），MySQL能够在name的索引中查找“Mike”值，然后直接转到数据文件中相应的行，准确地返回该行的peopleid（999）。在这个过程中，MySQL只需处理一个行就可以返回结果。如果没有“name”列的索引，MySQL要扫描数据文件中的所有记录，即1000个记录！显然，需要MySQL处理的记录数量越少，则它完成任务的速度就越快。
索引的类型
MySQL提供多种索引类型供选择：
普通索引
这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建：
创建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表);修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );
唯一性索引
这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种方式创建：
创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );
主键
主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。
全文索引
MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。本文下面的讨论不再涉及全文索引，要了解更多信息，请参见MySQL documentation。
单列索引与多列索引
索引可以是单列索引，也可以是多列索引。下面我们通过具体的例子来说明这两种索引的区别。假设有这样一个people表：
CREATE TABLE people ( peopleid SMALLINT NOT NULL AUTO_INCREMENT, firstname CHAR(50) NOT NULL, lastname CHAR(50) NOT NULL, age SMALLINT NOT NULL, townid SMALLINT NOT NULL, PRIMARY KEY (peopleid) );
下面是我们插入到这个people表的数据：
这个数据片段中有四个名字为“Mikes”的人（其中两个姓Sullivans，两个姓McConnells），有两个年龄为17岁的人，还有一个名字与众不同的Joe Smith。
这个表的主要用途是根据指定的用户姓、名以及年龄返回相应的peopleid。例如，我们可能需要查找姓名为Mike Sullivan、年龄17岁用户的peopleid（SQL命令为SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age=17;）。由于我们不想让MySQL每次执行查询就去扫描整个表，这里需要考虑运用索引。
首先，我们可以考虑在单个列上创建索引，比如firstname、lastname或者age列。如果我们创建firstname列的索引（ALTER TABLE people ADD INDEX firstname (firstname);），MySQL将通过这个索引迅速把搜索范围限制到那些firstname='Mike'的记录，然后再在这个“中间结果集”上进行其他条件的搜索：它首先排除那些lastname不等于“Sullivan”的记录，然后排除那些age不等于17的记录。当记录满足所有搜索条件之后，MySQL就返回最终的搜索结果。
由于建立了firstname列的索引，与执行表的完全扫描相比，MySQL的效率提高了很多，但我们要求MySQL扫描的记录数量仍旧远远超过了实际所需要的。虽然我们可以删除firstname列上的索引，再创建lastname或者age列的索引，但总地看来，不论在哪个列上创建索引搜索效率仍旧相似。
为了提高搜索效率，我们需要考虑运用多列索引。如果为firstname、lastname和age这三个列创建一个多列索引，MySQL只需一次检索就能够找出正确的结果！下面是创建这个多列索引的SQL命令：
ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age);
由于索引文件以B-树格式保存，MySQL能够立即转到合适的firstname，然后再转到合适的lastname，最后转到合适的age。在没有扫描数据文件任何一个记录的情况下，MySQL就正确地找出了搜索的目标记录！
那么，如果在firstname、lastname、age这三个列上分别创建单列索引，效果是否和创建一个firstname、lastname、age的多列索引一样呢？答案是否定的，两者完全不同。当我们执行查询的时候，MySQL只能使用一个索引。如果你有三个单列的索引，MySQL会试图选择一个限制最严格的索引。但是，即使是限制最严格的单列索引，它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。
为了速度，在确保查询已经最优的前提，就是以牺牲空间来获取速度，比如分表，建立索引
我碰到以下几种情况会导致查询速度慢
1、无索引 ，必要的索引可以很大地提高速度，但对字符型效果不大， 查询涉及关键的类别， 尽量用数字，然后建立索引
2、效率低的SQL语句，比如用了大量的 IN ，NOT IN （这个在MYSQL4.x好象没有 ：））
如果有几个条件， 可以选产生结果最小的做为首次查询， 因为MYSQL 4。X不支持子查询， 可以考虑建立临时表。
3、数据库服务器内存太小 ，这个你应该知道该如何办 ：）
4、表太大，可以根据主要分类，把表分解 ，或者常用的字段合成一个表，不常用的分成一个表
比如全国类型的企业信息，可以按省分，可以按行业分，
按省分后，每个省再分3个表， 地址表（名称、地址、邮政编码），企业信息表（行业，规模，。。。），通信表（电话、传真、邮件、主页）
Mysql中有查询高速缓存,使用高速缓存的语句格式:select sql_cache * from tableA where ...
EXPLAIN sql执行语句
mysql>
SELECT COUNT(*) FROM Headline WHERE ExpireTime >= 1112201600;
引用
+----------+
| COUNT(*) |
+----------+
| 3971 |
+----------+
1 row in set (1.04 sec)
mysql>
EXPLAIN SELECT COUNT(*) FROM Headline WHERE ExpireTime >= 1112201600 \G
引用
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Headline
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 302116
Extra: Using where
1 row in set (0.00 sec)
The NULL value in the key column of the EXPLAIN output tell us that MySQL won't be using an index for this query. In fact, the NULL value in the possible_keys column tells us that there were no indexes to pick from at all. If this type of query is likely to be common, we can simply add an index and rerun the query (or the EXPLAIN) to verify that MySQL uses it.
1、选取最适用的字段属性
MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。
另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。
2、使用连接（JOIN）来代替子查询(Sub-Queries)
MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：
DELETE FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）.. 替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：
SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
如果使用连接（JOIN）.. 来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：
SELECT * FROM customerinfo
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.
CustomerID
WHERE salesinfo.CustomerID IS NULL
连接（JOIN）.. 之所以更有效率一些，是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
3、使用联合(UNION)来代替手动创建的临时表
MySQL 从 4.0 的版本开始支持 UNION 查询，它可以把需要使用临时表的两条或更多的 SELECT 查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用 UNION 来创建查询的时候，我们只需要用 UNION作为关键字把多个 SELECT 语句连接起来就可以了，要注意的是所有 SELECT 语句中的字段数目要想同。下面的例子就演示了一个使用 UNION的查询。
SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author
UNION
SELECT Name, Supplier FROM product
4、事务
尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN 关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。
BEGIN;
INSERT INTO salesinfo SET CustomerID=14;
UPDATE inventory SET Quantity=11
WHERE item='book';
COMMIT;
事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。
5、锁定表
尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户
来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。
其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。
LOCK TABLE inventory WRITE
SELECT Quantity FROM inventory
WHEREItem='book';
...
UPDATE inventory SET Quantity=11
WHEREItem='book';
UNLOCK TABLES
这里，我们用一个 SELECT 语句取出初始数据，通过一些计算，用 UPDATE 语句将新值更新到表中。包含有 WRITE 关键字的 LOCK TABLE 语句可以保证在 UNLOCK TABLES 命令被执行之前，不会有其它的访问来对 inventory 进行插入、更新或者删除的操作。
6、使用外键
锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo 表中的CustomerID映射到salesinfo表中CustomerID，任何一条没有合法CustomerID的记录都不会被更新或插入到salesinfo中。
CREATE TABLE customerinfo
(
CustomerID INT NOT NULL ,
PRIMARY KEY ( CustomerID )
) TYPE = INNODB;
CREATE TABLE salesinfo
(
SalesID INT NOT NULL,
CustomerID INT NOT NULL,
PRIMARY KEY(CustomerID, SalesID),
FOREIGN KEY (CustomerID) REFERENCES customerinfo
(CustomerID) ON DELETECASCADE
) TYPE = INNODB;
注意例子中的参数“ON DELETE CASCADE”。该参数保证当 customerinfo 表中的一条客户记录被删除的时候，salesinfo 表中所有与该客户相关的记录也会被自动删除。如果要在 MySQL 中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表 InnoDB类型。该类型不是 MySQL 表的默认类型。定义的方法是在 CREATE TABLE 语句中加上 TYPE=INNODB。如例中所示。
7、使用索引
索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(), MIN()和ORDERBY这些命令的时候，性能提高更为明显。那该对哪些字段建立索引呢？一般说来，索引应建立在那些将用于JOIN, WHERE判断和ORDER BY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况，例如customerinfo中的“province”.. 字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTER TABLE或CREATE INDEX在以后创建索引。此外，MySQL
从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL 中是一个FULLTEXT类型索引，但仅能用于MyISAM 类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTER TABLE或CREATE INDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。
8、优化的查询语句
绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。下面是应该注意的几个方面。首先，最好是在相同类型的字段间进行比较的操作。在MySQL 3.23版之前，这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。其次，在建有索引的字段上尽量不要使用函数进行操作。
例如，在一个DATE类型的字段上使用YEAE()函数时，将会使索引不能发挥应有的作用。所以，下面的两个查询虽然返回的结果一样，但后者要比前者快得多。
SELECT * FROM order WHERE YEAR(OrderDate)<2001;
SELECT * FROM order WHERE OrderDate<"2001-01-01";
同样的情形也会发生在对数值型字段进行计算的时候：
SELECT * FROM inventory WHERE Amount/7<24;
SELECT * FROM inventory WHERE Amount<24*7;
上面的两个查询也是返回相同的结果，但后面的查询将比前面的一个快很多。第三，在搜索字符型字段时，我们有时会使用 LIKE 关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。例如下面的查询将会比较表中的每一条记录。
SELECT * FROM books
WHERE name like "MySQL%"
但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：..
SELECT * FROM books
WHERE name>="MySQL"and name<"MySQM"
最后，应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。
索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。
假设我们创建了一个名为people的表：
CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL );
然后，我们完全随机把1000个不同name值插入到people表。下图显示了people表所在数据文件的一小部分：
可以看到，在数据文件中name列没有任何明确的次序。如果我们创建了name列的索引，MySQL将在索引中排序name列：
对于索引中的每一项，MySQL在内部为它保存一个数据文件中实际记录所在位置的“指针”。因此，如果我们要查找name等于“Mike”记录的peopleid（SQL命令为“SELECT peopleid FROM people WHERE name='Mike';”），MySQL能够在name的索引中查找“Mike”值，然后直接转到数据文件中相应的行，准确地返回该行的peopleid（999）。在这个过程中，MySQL只需处理一个行就可以返回结果。如果没有“name”列的索引，MySQL要扫描数据文件中的所有记录，即1000个记录！显然，需要MySQL处理的记录数量越少，则它完成任务的速度就越快。
索引的类型
MySQL提供多种索引类型供选择：
普通索引
这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建：
创建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表);修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );
唯一性索引
这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种方式创建：
创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );
主键
主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。
全文索引
MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。本文下面的讨论不再涉及全文索引，要了解更多信息，请参见MySQL documentation。
单列索引与多列索引
索引可以是单列索引，也可以是多列索引。下面我们通过具体的例子来说明这两种索引的区别。假设有这样一个people表：
CREATE TABLE people ( peopleid SMALLINT NOT NULL AUTO_INCREMENT, firstname CHAR(50) NOT NULL, lastname CHAR(50) NOT NULL, age SMALLINT NOT NULL, townid SMALLINT NOT NULL, PRIMARY KEY (peopleid) );
下面是我们插入到这个people表的数据：
这个数据片段中有四个名字为“Mikes”的人（其中两个姓Sullivans，两个姓McConnells），有两个年龄为17岁的人，还有一个名字与众不同的Joe Smith。
这个表的主要用途是根据指定的用户姓、名以及年龄返回相应的peopleid。例如，我们可能需要查找姓名为Mike Sullivan、年龄17岁用户的peopleid（SQL命令为SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age=17;）。由于我们不想让MySQL每次执行查询就去扫描整个表，这里需要考虑运用索引。
首先，我们可以考虑在单个列上创建索引，比如firstname、lastname或者age列。如果我们创建firstname列的索引（ALTER TABLE people ADD INDEX firstname (firstname);），MySQL将通过这个索引迅速把搜索范围限制到那些firstname='Mike'的记录，然后再在这个“中间结果集”上进行其他条件的搜索：它首先排除那些lastname不等于“Sullivan”的记录，然后排除那些age不等于17的记录。当记录满足所有搜索条件之后，MySQL就返回最终的搜索结果。
由于建立了firstname列的索引，与执行表的完全扫描相比，MySQL的效率提高了很多，但我们要求MySQL扫描的记录数量仍旧远远超过了实际所需要的。虽然我们可以删除firstname列上的索引，再创建lastname或者age列的索引，但总地看来，不论在哪个列上创建索引搜索效率仍旧相似。
为了提高搜索效率，我们需要考虑运用多列索引。如果为firstname、lastname和age这三个列创建一个多列索引，MySQL只需一次检索就能够找出正确的结果！下面是创建这个多列索引的SQL命令：
ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age);
由于索引文件以B-树格式保存，MySQL能够立即转到合适的firstname，然后再转到合适的lastname，最后转到合适的age。在没有扫描数据文件任何一个记录的情况下，MySQL就正确地找出了搜索的目标记录！
那么，如果在firstname、lastname、age这三个列上分别创建单列索引，效果是否和创建一个firstname、lastname、age的多列索引一样呢？答案是否定的，两者完全不同。当我们执行查询的时候，MySQL只能使用一个索引。如果你有三个单列的索引，MySQL会试图选择一个限制最严格的索引。但是，即使是限制最严格的单列索引，它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。
同时在线访问量继续增大 对于1G内存的服务器明显感觉到吃力严重时甚至每天都会死机 或者时不时的服务器卡一下 这个问题曾经困扰了我半个多月MySQL使用是很具伸缩性的算法，因此你通常能用很少的内存运行或给MySQL更多的被存以得到更好的性能。
安装好mysql后，配制文件应该在/usr/local/mysql/share/mysql目录中，配制文件有几个，有my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf,不同的流量的网站和不同配制的服务器环境，当然需要有不同的配制文件了。
一般的情况下，my-medium.cnf这个配制文件就能满足我们的大多需要；一般我们会把配置文件拷贝到/etc/my.cnf 只需要修改这个配置文件就可以了，使用mysqladmin variables extended-status –u root –p 可以看到目前的参数，有３个配置参数是最重要的，即key_buffer_size,query_cache_size,table_cache。
key_buffer_size只对MyISAM表起作用，
key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。一般我们设为16M,实际上稍微大一点的站点 这个数字是远远不够的，通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）。 或者如果你装了phpmyadmin 可以通过服务器运行状态看到,笔者推荐用phpmyadmin管理mysql，以下的状态值都是本人通过phpmyadmin获得的实例分析:
这个服务器已经运行了20天
key_buffer_size – 128M
key_read_requests – 650759289
key_reads - 79112
比例接近1:8000 健康状况非常好
另外一个估计key_buffer_size的办法 把你网站数据库的每个表的索引所占空间大小加起来看看以此服务器为例:比较大的几个表索引加起来大概125M 这个数字会随着表变大而变大。
从4.0.1开始，MySQL提供了查询缓冲机制。使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。根据MySQL用户手册，使用查询缓冲最多可以达到238%的效率。
通过调节以下几个参数可以知道query_cache_size设置得是否合理
Qcache inserts
Qcache hits
Qcache lowmem prunes
Qcache free blocks
Qcache total blocks
Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况,同时Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。
Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多query_cache_type指定是否使用查询缓冲
我设置:
query_cache_size = 32M
query_cache_type= 1
得到如下状态值:
Qcache queries in cache 12737 表明目前缓存的条数
Qcache inserts 20649006
Qcache hits 79060095 看来重复查询率还挺高的
Qcache lowmem prunes 617913 有这么多次出现缓存过低的情况
Qcache not cached 189896
Qcache free memory 18573912 目前剩余缓存空间
Qcache free blocks 5328 这个数字似乎有点大 碎片不少
Qcache total blocks 30953
如果内存允许32M应该要往上加点
table_cache指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得）。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。
对于有1G内存的机器，推荐值是128－256。
笔者设置table_cache = 256
得到以下状态:
Open tables 256
Opened tables 9046
虽然open_tables已经等于table_cache，但是相对于服务器运行时间来说,已经运行了20天，opened_tables的值也非常低。因此，增加table_cache的值应该用处不大。如果运行了6个小时就出现上述值 那就要考虑增大table_cache。
如果你不需要记录2进制log 就把这个功能关掉，注意关掉以后就不能恢复出问题前的数据了，需要您手动备份，二进制日志包含所有更新数据的语句，其目的是在恢复数据库时用它来把数据尽可能恢复到最后的状态。另外，如果做同步复制( Replication )的话，也需要使用二进制日志传送修改情况。
log_bin指定日志文件，如果不提供文件名，MySQL将自己产生缺省文件名。MySQL会在文件名后面自动添加数字引，每次启动服务时，都会重新生成一个新的二进制文件。此外，使用log-bin-index可以指定索引文件；使用binlog-do-db可以指定记录的数据库；使用binlog-ignore-db可以指定不记录的数据库。注意的是：binlog-do-db和binlog-ignore-db一次只指定一个数据库，指定多个数据库需要多个语句。而且，MySQL会将所有的数据库名称改成小写，在指定数据库时必须全部使用小写名字，否则不会起作用。
关掉这个功能只需要在他前面加上#号
#log-bin
开启慢查询日志( slow query log )
慢查询日志对于跟踪有问题的查询非常有用。它记录所有查过long_query_time的查询，如果需要，还可以记录不使用索引的记录。下面是一个慢查询日志的例子：
开启慢查询日志，需要设置参数log_slow_queries、long_query_times、log-queries-not-using-indexes。
log_slow_queries指定日志文件，如果不提供文件名，MySQL将自己产生缺省文件名。long_query_times指定慢查询的阈值，缺省是10秒。log-queries-not-using-indexes是4.1.0以后引入的参数，它指示记录不使用索引的查询。笔者设置long_query_time=10
笔者设置:
sort_buffer_size = 1M
max_connections=120
wait_timeout =120
back_log=100
read_buffer_size = 1M
thread_cache=32
interactive_timeout=120
thread_concurrency = 4
参数说明:
back_log
要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 Unix listen(2)系统调用的手册页应该有更多的细节。检查你的OS文档找出这个变量的最大值。试图设定back_log高于你的操作系统的限制将是无效的。
max_connections
并发连接数目最大，120 超过这个值就会自动恢复，出了问题能自动解决
thread_cache
没找到具体说明，不过设置为32后 20天才创建了400多个线程 而以前一天就创建了上千个线程 所以还是有用的
thread_concurrency
#设置为你的cpu数目x2,例如，只有一个cpu,那么thread_concurrency=2
#有2个cpu,那么thread_concurrency=4
skip-innodb
#去掉innodb支持
安装好mysql后，配制文件应该在/usr/local/mysql/share/mysql目录中，配制文件有几个，有my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf,不同的流量的网站和不同配制的服务器环境，当然需要有不同的配制文件了。
一般的情况下，my-medium.cnf这个配制文件就能满足我们的大多需要；一般我们会把配置文件拷贝到/etc/my.cnf 只需要修改这个配置文件就可以了，使用mysqladmin variables extended-status –u root –p 可以看到目前的参数，有３个配置参数是最重要的，即key_buffer_size,query_cache_size,table_cache。
key_buffer_size只对MyISAM表起作用，
key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。一般我们设为16M,实际上稍微大一点的站点 这个数字是远远不够的，通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）。 或者如果你装了phpmyadmin 可以通过服务器运行状态看到,笔者推荐用phpmyadmin管理mysql，以下的状态值都是本人通过phpmyadmin获得的实例分析:
这个服务器已经运行了20天
key_buffer_size – 128M
key_read_requests – 650759289
key_reads - 79112
比例接近1:8000 健康状况非常好
另外一个估计key_buffer_size的办法 把你网站数据库的每个表的索引所占空间大小加起来看看以此服务器为例:比较大的几个表索引加起来大概125M 这个数字会随着表变大而变大。
从4.0.1开始，MySQL提供了查询缓冲机制。使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。根据MySQL用户手册，使用查询缓冲最多可以达到238%的效率。
通过调节以下几个参数可以知道query_cache_size设置得是否合理
Qcache inserts
Qcache hits
Qcache lowmem prunes
Qcache free blocks
Qcache total blocks
Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况,同时Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。
Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多query_cache_type指定是否使用查询缓冲
我设置:
query_cache_size = 32M
query_cache_type= 1
得到如下状态值:
Qcache queries in cache 12737 表明目前缓存的条数
Qcache inserts 20649006
Qcache hits 79060095 看来重复查询率还挺高的
Qcache lowmem prunes 617913 有这么多次出现缓存过低的情况
Qcache not cached 189896
Qcache free memory 18573912 目前剩余缓存空间
Qcache free blocks 5328 这个数字似乎有点大 碎片不少
Qcache total blocks 30953
如果内存允许32M应该要往上加点
table_cache指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得）。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。
对于有1G内存的机器，推荐值是128－256。
笔者设置table_cache = 256
得到以下状态:
Open tables 256
Opened tables 9046
虽然open_tables已经等于table_cache，但是相对于服务器运行时间来说,已经运行了20天，opened_tables的值也非常低。因此，增加table_cache的值应该用处不大。如果运行了6个小时就出现上述值 那就要考虑增大table_cache。
如果你不需要记录2进制log 就把这个功能关掉，注意关掉以后就不能恢复出问题前的数据了，需要您手动备份，二进制日志包含所有更新数据的语句，其目的是在恢复数据库时用它来把数据尽可能恢复到最后的状态。另外，如果做同步复制( Replication )的话，也需要使用二进制日志传送修改情况。
log_bin指定日志文件，如果不提供文件名，MySQL将自己产生缺省文件名。MySQL会在文件名后面自动添加数字引，每次启动服务时，都会重新生成一个新的二进制文件。此外，使用log-bin-index可以指定索引文件；使用binlog-do-db可以指定记录的数据库；使用binlog-ignore-db可以指定不记录的数据库。注意的是：binlog-do-db和binlog-ignore-db一次只指定一个数据库，指定多个数据库需要多个语句。而且，MySQL会将所有的数据库名称改成小写，在指定数据库时必须全部使用小写名字，否则不会起作用。
关掉这个功能只需要在他前面加上#号
#log-bin
开启慢查询日志( slow query log )
慢查询日志对于跟踪有问题的查询非常有用。它记录所有查过long_query_time的查询，如果需要，还可以记录不使用索引的记录。下面是一个慢查询日志的例子：
开启慢查询日志，需要设置参数log_slow_queries、long_query_times、log-queries-not-using-indexes。
log_slow_queries指定日志文件，如果不提供文件名，MySQL将自己产生缺省文件名。long_query_times指定慢查询的阈值，缺省是10秒。log-queries-not-using-indexes是4.1.0以后引入的参数，它指示记录不使用索引的查询。笔者设置long_query_time=10
笔者设置:
sort_buffer_size = 1M
max_connections=120
wait_timeout =120
back_log=100
read_buffer_size = 1M
thread_cache=32
interactive_timeout=120
thread_concurrency = 4
参数说明:
back_log
要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 Unix listen(2)系统调用的手册页应该有更多的细节。检查你的OS文档找出这个变量的最大值。试图设定back_log高于你的操作系统的限制将是无效的。
max_connections
并发连接数目最大，120 超过这个值就会自动恢复，出了问题能自动解决
thread_cache
没找到具体说明，不过设置为32后 20天才创建了400多个线程 而以前一天就创建了上千个线程 所以还是有用的
thread_concurrency
#设置为你的cpu数目x2,例如，只有一个cpu,那么thread_concurrency=2
#有2个cpu,那么thread_concurrency=4
skip-innodb
#去掉innodb支持
一. 启动参数优化
修改 my.cnf (或者my.ini)，加入/修改以下几行
#设定缓存的连接数,节省连接时的开销
back_log = 64
#禁用文件系统外部锁
external-locking = 0
#禁用BDB,如果你确实不需要的话,innodb也是如此
skip-bdb
#索引缓冲,如果是专用的数据库服务器,可以设置高达服务器内存的一半,如果不是专用的,还是设置得低一点
key_buffer = 512M
#缓存数据表数量,如果内存较大,可以设置稍微高一点,否则还是设置低一点
#设置这个参数可以参见系统状态中的 open_tables(表示当前打开的数据表总数) 和 opened_tables(表示所有打开的数据表总数)
table_cache = 128
#禁用dns解析,如果你的授权信息中采用dns授权方式了,就不能启用该选项
skip-name-resolve
#记录慢查询和没有使用索引的查询,便于帮助分析问题所在
long_query_time = 1
log-slow-queries = /usr/local/mysql/data/slow.log
log-queries-not-using-indexes
其他参数诸如 sort_buffer_size,net_buffer_length,read_buffer_size,read_rnd_buffer_size,myisam_sort_buffer_size,thread_cache_size,query_cache_size,max_binlog_cache_size 等请查询MySQL手册,然后做出合适的调整.
二. 其他小TIPS
针对Innodb表,尽量不执行 SELECT COUNT(*) 语句,因为Innodb表没有类似MyISAM那样的内部计数器来记录表记录总量,执行这个操作将会全表扫描,速度很慢.
尽量使用MyISAM表,除非必须使用其他类型,因为MyISAM类型的总体读写效率是相当高的,缺点是表级锁,而不是行/页级锁.
善用 EXPLAIN来帮助你分析查询优化情况
如果需要对一个较大的且并发读写较多的数据表做 GROUP BY 等统计操作,建议使用摘要表来存储统计信息,定期更新统计表,这可能获得很大的性能改善.
查询时如果有 ORDER BY分句的话,注意让它的字段顺序和索引字段顺序对应,这样能加快排序速度
如果有一个多字段索引,则查询时,必须按照索引顺序来使用,否则该索引不会用到.例如:
索引 `idx_`(col1, col2, col3),那么查询 SELECT …. FROM … WHERE col1=1 AND col2=2; 使用索引,而查询 … WHERE col2=2 AND col3=3; 或 … WHERE col1=1 AND col3=3; 则不使用索引.
2 个表连接时,连接字段的类型最好一致(包括字段长度),这样的话索引速度快多了.
大部分情况下,字符类型的字段索引值需要一部分,例如 CREATE INDEX char_idx ON tbl1 ( name(10) );
尽量使用最合适的数据类型,能使用 ENUM 就不使用 TINYINT ,能使用 SMALLINT 就不使用 MEDIUMINT.这样能节省存储空间,增加数据存储量,提高搜索速度.不要担心这样会对省级产生很大的影响,因为加入从 TINYINT 类型改变为 INT 的话,并不会改变原来的数据.
如果知道某个表总是频繁使用的话,可以把它放到 hot_cache 中,用以下方法:
SET GLOBAL hot_cache.key_buffer_size=128*1024;
CACHE INDEX `xxx` IN hot_cache;
把拖沓复杂,速度慢的的查询分解成多个简洁明了的查询,这样尽管查询次数多了,但是总体速度和效率却可能反而更高了,而且也减少了锁表的可能.
执行查询时,尽量不使用外部函数,因为这样的话就无法使用可能存在的索引,并且无论如何都会极大地降低效率.如: … WHERE `create_time` > UNIX_TIMESTAMP(NOW()); 这样的查询.可以在程序中把当前的时间取得,然后直接执行构造好了的SQL语句.
在索引字段上使用 LIKE 查询时,左边不要使用 ‘%’ 修饰符,这样就可以利用索引,否则无法使用索引.如 … `name` LIKE ‘yejr%’;.
如果有可能,多使用存储过程,这大概能获得 22% 的性能提高.
如果并发访问量相对最大连接数小多了的话,最好使用永久连接,这样能节省不少连接时的系统资源损耗.
定期的在MyISAM表上执行 OPTIMIZE TABLE,这能整理随便,提高索引效率.
如果你主要按 col1，col2，…顺序检索记录,请在对表大量更改后执行 ALTER TABLE … ORDER BY col1, col2, … 语句,这可以获得更好的性能.
对于频繁更改的MyISAM表,应尽量避免更新所有变长字段(VARCHAR、BLOB和TEXT).
对于记录总数超过500万的单表,就应该赶紧考虑分表了.分表策略有多种,比如按ID号段,或者按时间切分,等等.
创建数据表时尽量指定字段不能为NULL,并且有默认值.
使用 LOAD DATA,而不是使用大批量的 INSERT 语句来导入数据.
使数据表名和字段名尽可能的短,例如在 user 表中使用字段名 name,而不是 user_name.
用 DELAY_KEY_WRITE = 1 选项让MyISAM更快地更新索引,因为在表关闭之前它们不刷新到硬盘上.缺点是如果服务器如果突然被杀掉了,重启之后就必须运行 myisamchk 修复索引才行.
采用复制机制来分摊读数据的负载,把写数据只放在主服务器上,把读平均分摊到各个从服务器上,能大大提高系统负载.
修改 my.cnf (或者my.ini)，加入/修改以下几行
引用
#设定缓存的连接数,节省连接时的开销
back_log = 64
#禁用文件系统外部锁
external-locking = 0
#禁用BDB,如果你确实不需要的话,innodb也是如此
skip-bdb
#索引缓冲,如果是专用的数据库服务器,可以设置高达服务器内存的一半,如果不是专用的,还是设置得低一点
key_buffer = 512M
#缓存数据表数量,如果内存较大,可以设置稍微高一点,否则还是设置低一点
#设置这个参数可以参见系统状态中的 open_tables(表示当前打开的数据表总数) 和 opened_tables(表示所有打开的数据表总数)
table_cache = 128
#禁用dns解析,如果你的授权信息中采用dns授权方式了,就不能启用该选项
skip-name-resolve
#记录慢查询和没有使用索引的查询,便于帮助分析问题所在
long_query_time = 1
log-slow-queries = /usr/local/mysql/data/slow.log
log-queries-not-using-indexes
其他参数诸如 sort_buffer_size,net_buffer_length,read_buffer_size,read_rnd_buffer_size,myisam_sort_buffer_size,thread_cache_size,query_cache_size,max_binlog_cache_size 等请查询MySQL手册,然后做出合适的调整.
二. 其他小TIPS
引用
针对Innodb表,尽量不执行 SELECT COUNT(*) 语句,因为Innodb表没有类似MyISAM那样的内部计数器来记录表记录总量,执行这个操作将会全表扫描,速度很慢.
尽量使用MyISAM表,除非必须使用其他类型,因为MyISAM类型的总体读写效率是相当高的,缺点是表级锁,而不是行/页级锁.
善用 EXPLAIN来帮助你分析查询优化情况
如果需要对一个较大的且并发读写较多的数据表做 GROUP BY 等统计操作,建议使用摘要表来存储统计信息,定期更新统计表,这可能获得很大的性能改善.
查询时如果有 ORDER BY分句的话,注意让它的字段顺序和索引字段顺序对应,这样能加快排序速度
如果有一个多字段索引,则查询时,必须按照索引顺序来使用,否则该索引不会用到.例如:
索引 `idx_`(col1, col2, col3),那么查询 SELECT …. FROM … WHERE col1=1 AND col2=2; 使用索引,而查询 … WHERE col2=2 AND col3=3; 或 … WHERE col1=1 AND col3=3; 则不使用索引.
2 个表连接时,连接字段的类型最好一致(包括字段长度),这样的话索引速度快多了.
大部分情况下,字符类型的字段索引值需要一部分,例如 CREATE INDEX char_idx ON tbl1 ( name(10) );
尽量使用最合适的数据类型,能使用 ENUM 就不使用 TINYINT ,能使用 SMALLINT 就不使用 MEDIUMINT.这样能节省存储空间,增加数据存储量,提高搜索速度.不要担心这样会对省级产生很大的影响,因为加入从 TINYINT 类型改变为 INT 的话,并不会改变原来的数据.
如果知道某个表总是频繁使用的话,可以把它放到 hot_cache 中,用以下方法:
SET GLOBAL hot_cache.key_buffer_size=128*1024;
CACHE INDEX `xxx` IN hot_cache;
把拖沓复杂,速度慢的的查询分解成多个简洁明了的查询,这样尽管查询次数多了,但是总体速度和效率却可能反而更高了,而且也减少了锁表的可能.
执行查询时,尽量不使用外部函数,因为这样的话就无法使用可能存在的索引,并且无论如何都会极大地降低效率.如: … WHERE `create_time` > UNIX_TIMESTAMP(NOW()); 这样的查询.可以在程序中把当前的时间取得,然后直接执行构造好了的SQL语句.
在索引字段上使用 LIKE 查询时,左边不要使用 ‘%’ 修饰符,这样就可以利用索引,否则无法使用索引.如 … `name` LIKE ‘yejr%’;.
如果有可能,多使用存储过程,这大概能获得 22% 的性能提高.
如果并发访问量相对最大连接数小多了的话,最好使用永久连接,这样能节省不少连接时的系统资源损耗.
定期的在MyISAM表上执行 OPTIMIZE TABLE,这能整理随便,提高索引效率.
如果你主要按 col1，col2，…顺序检索记录,请在对表大量更改后执行 ALTER TABLE … ORDER BY col1, col2, … 语句,这可以获得更好的性能.
对于频繁更改的MyISAM表,应尽量避免更新所有变长字段(VARCHAR、BLOB和TEXT).
对于记录总数超过500万的单表,就应该赶紧考虑分表了.分表策略有多种,比如按ID号段,或者按时间切分,等等.
创建数据表时尽量指定字段不能为NULL,并且有默认值.
使用 LOAD DATA,而不是使用大批量的 INSERT 语句来导入数据.
使数据表名和字段名尽可能的短,例如在 user 表中使用字段名 name,而不是 user_name.
用 DELAY_KEY_WRITE = 1 选项让MyISAM更快地更新索引,因为在表关闭之前它们不刷新到硬盘上.缺点是如果服务器如果突然被杀掉了,重启之后就必须运行 myisamchk 修复索引才行.
采用复制机制来分摊读数据的负载,把写数据只放在主服务器上,把读平均分摊到各个从服务器上,能大大提高系统负载.
mysqld程序--目录和文件
mysqld程序--语言设置
mysqld程序--通信、网络、信息安全
mysqld程序--内存管理、优化、查询缓存区
mysqld程序--日志
mysqld程序--镜像(主控镜像服务器)
mysqld程序--镜像(从属镜像服务器)
mysqld--InnoDB--基本设置、表空间文件
mysqld程序--InnoDB--日志
mysqld程序--InnoDB--缓存区的设置和优化
mysqld程序--其它选项
注意：如果在配置文件里给出的某个选项是mysqld无法识别的，MySQL服务器将不启动。
引用
basedir = path 使用给定目录作为根目录(安装目录)。
character-sets-dir = path 给出存放着字符集的目录。
datadir = path 从给定目录读取数据库文件。
pid-file = filename 为mysqld程序指定一个存放进程ID的文件(仅适用于UNIX/Linux系统); Init-V脚本需要使用这个文件里的进程ID结束mysqld进程。
socket = filename 为MySQL客户程序与服务器之间的本地通信指定一个套接字文件(仅适用于UNIX/Linux系统; 默认设置一般是/var/lib/mysql/mysql.sock文件)。在Windows环境下，如果MySQL客户与服务器是通过命名管道进行通信的，--sock选项给出的将是该命名管道的名字(默认设置是MySQL)。
lower_case_table_name = 1/0 新目录和数据表的名字是否只允许使用小写字母; 这个选项在Windows环境下的默认设置是1(只允许使用小写字母)。
character-sets-dir = path 给出存放着字符集的目录。
datadir = path 从给定目录读取数据库文件。
pid-file = filename 为mysqld程序指定一个存放进程ID的文件(仅适用于UNIX/Linux系统); Init-V脚本需要使用这个文件里的进程ID结束mysqld进程。
socket = filename 为MySQL客户程序与服务器之间的本地通信指定一个套接字文件(仅适用于UNIX/Linux系统; 默认设置一般是/var/lib/mysql/mysql.sock文件)。在Windows环境下，如果MySQL客户与服务器是通过命名管道进行通信的，--sock选项给出的将是该命名管道的名字(默认设置是MySQL)。
lower_case_table_name = 1/0 新目录和数据表的名字是否只允许使用小写字母; 这个选项在Windows环境下的默认设置是1(只允许使用小写字母)。
mysqld程序--语言设置
引用
character-sets-server = name 新数据库或数据表的默认字符集。为了与MySQL的早期版本保持兼容，这个字符集也可以用--default-character-set选项给出; 但这个选项已经显得有点过时了。
collation-server = name 新数据库或数据表的默认排序方式。
lanuage = name 用指定的语言显示出错信息。
collation-server = name 新数据库或数据表的默认排序方式。
lanuage = name 用指定的语言显示出错信息。
mysqld程序--通信、网络、信息安全
引用
enable-named-pipes 允许Windows 2000/XP环境下的客户和服务器使用命名管道(named pipe)进行通信。这个命名管道的默认名字是MySQL，但可以用--socket选项来改变。
local-infile [=0] 允许/禁止使用LOAD DATA LOCAL语句来处理本地文件。
myisam-recover [=opt1, opt2, ...] 在启动时自动修复所有受损的MyISAM数据表。这个选项的可取值有4种:DEFAULT、BACKUP、QUICK和FORCE; 它们与myisamchk程序的同名选项作用相同。
old-passwords 使用MySQL 3.23和4.0版本中的老算法来加密mysql数据库里的密码(默认使用MySQL 4.1版本开始引入的新加密算法)。
port = n 为MySQL程序指定一个TCP/IP通信端口(通常是3306端口)。
safe-user-create 只有在mysql.user数据库表上拥有INSERT权限的用户才能使用GRANT命令; 这是一种双保险机制(此用户还必须具备GRANT权限才能执行GRANT命令)。
shared-memory 允许使用内存(shared memory)进行通信(仅适用于Windows)。
shared-memory-base-name = name 给共享内存块起一个名字(默认的名字是MySQL)。
skip-grant-tables 不使用mysql数据库里的信息来进行访问控制(警告:这将允许用户任何用户去修改任何数据库)。
skip-host-cache 不使用高速缓存区来存放主机名和IP地址的对应关系。
skip-name-resovle 不把IP地址解析为主机名; 与访问控制(mysql.user数据表)有关的检查全部通过IP地址行进。
skip-networking 只允许通过一个套接字文件(Unix/Linux系统)或通过命名管道(Windows系统)进行本地连接，不允许ICP/IP连接; 这提高了安全性，但阻断了来自网络的外部连接和所有的Java客户程序(Java客户即使在本地连接里也使用TCP/IP)。
user = name mysqld程序在启动后将在给定UNIX/Linux账户下执行; mysqld必须从root账户启动才能在启动后切换到另一个账户下执行; mysqld_safe脚本将默认使用--user=mysql选项来启动mysqld程序。
local-infile [=0] 允许/禁止使用LOAD DATA LOCAL语句来处理本地文件。
myisam-recover [=opt1, opt2, ...] 在启动时自动修复所有受损的MyISAM数据表。这个选项的可取值有4种:DEFAULT、BACKUP、QUICK和FORCE; 它们与myisamchk程序的同名选项作用相同。
old-passwords 使用MySQL 3.23和4.0版本中的老算法来加密mysql数据库里的密码(默认使用MySQL 4.1版本开始引入的新加密算法)。
port = n 为MySQL程序指定一个TCP/IP通信端口(通常是3306端口)。
safe-user-create 只有在mysql.user数据库表上拥有INSERT权限的用户才能使用GRANT命令; 这是一种双保险机制(此用户还必须具备GRANT权限才能执行GRANT命令)。
shared-memory 允许使用内存(shared memory)进行通信(仅适用于Windows)。
shared-memory-base-name = name 给共享内存块起一个名字(默认的名字是MySQL)。
skip-grant-tables 不使用mysql数据库里的信息来进行访问控制(警告:这将允许用户任何用户去修改任何数据库)。
skip-host-cache 不使用高速缓存区来存放主机名和IP地址的对应关系。
skip-name-resovle 不把IP地址解析为主机名; 与访问控制(mysql.user数据表)有关的检查全部通过IP地址行进。
skip-networking 只允许通过一个套接字文件(Unix/Linux系统)或通过命名管道(Windows系统)进行本地连接，不允许ICP/IP连接; 这提高了安全性，但阻断了来自网络的外部连接和所有的Java客户程序(Java客户即使在本地连接里也使用TCP/IP)。
user = name mysqld程序在启动后将在给定UNIX/Linux账户下执行; mysqld必须从root账户启动才能在启动后切换到另一个账户下执行; mysqld_safe脚本将默认使用--user=mysql选项来启动mysqld程序。
mysqld程序--内存管理、优化、查询缓存区
引用
bulk_insert_buffer_size = n 为一次插入多条新记录的INSERT命令分配的缓存区长度(默认设置是8M)。
key_buffer_size = n 用来存放索引区块的RMA值(默认设置是8M)。
join_buffer_size = n 在参加JOIN操作的数据列没有索引时为JOIN操作分配的缓存区长度(默认设置是128K)。
max_heap_table_size = n HEAP数据表的最大长度(默认设置是16M); 超过这个长度的HEAP数据表将被存入一个临时文件而不是驻留在内存里。
max_connections = n MySQL服务器同时处理的数据库连接的最大数量(默认设置是100)。
query_cache_limit = n 允许临时存放在查询缓存区里的查询结果的最大长度(默认设置是1M)。
query_cache_size = n 查询缓存区的最大长度(默认设置是0，不开辟查询缓存区)。
query_cache_type = 0/1/2 查询缓存区的工作模式:0, 禁用查询缓存区; 1，启用查询缓存区(默认设置); 2，"按需分配"模式，只响应SELECT SQL_CACHE命令。
read_buffer_size = n 为从数据表顺序读取数据的读操作保留的缓存区的长度(默认设置是128KB); 这个选项的设置值在必要时可以用SQL命令SET SESSION read_buffer_size = n命令加以改变。
read_rnd_buffer_size = n 类似于read_buffer_size选项，但针对的是按某种特定顺序(比如使用了ORDER BY子句的查询)输出的查询结果(默认设置是256K)。
sore_buffer = n 为排序操作分配的缓存区的长度(默认设置是2M); 如果这个缓存区太小，则必须创建一个临时文件来进行排序。
table_cache = n 同时打开的数据表的数量(默认设置是64)。
tmp_table_size = n 临时HEAP数据表的最大长度(默认设置是32M); 超过这个长度的临时数据表将被转换为MyISAM数据表并存入一个临时文件。
key_buffer_size = n 用来存放索引区块的RMA值(默认设置是8M)。
join_buffer_size = n 在参加JOIN操作的数据列没有索引时为JOIN操作分配的缓存区长度(默认设置是128K)。
max_heap_table_size = n HEAP数据表的最大长度(默认设置是16M); 超过这个长度的HEAP数据表将被存入一个临时文件而不是驻留在内存里。
max_connections = n MySQL服务器同时处理的数据库连接的最大数量(默认设置是100)。
query_cache_limit = n 允许临时存放在查询缓存区里的查询结果的最大长度(默认设置是1M)。
query_cache_size = n 查询缓存区的最大长度(默认设置是0，不开辟查询缓存区)。
query_cache_type = 0/1/2 查询缓存区的工作模式:0, 禁用查询缓存区; 1，启用查询缓存区(默认设置); 2，"按需分配"模式，只响应SELECT SQL_CACHE命令。
read_buffer_size = n 为从数据表顺序读取数据的读操作保留的缓存区的长度(默认设置是128KB); 这个选项的设置值在必要时可以用SQL命令SET SESSION read_buffer_size = n命令加以改变。
read_rnd_buffer_size = n 类似于read_buffer_size选项，但针对的是按某种特定顺序(比如使用了ORDER BY子句的查询)输出的查询结果(默认设置是256K)。
sore_buffer = n 为排序操作分配的缓存区的长度(默认设置是2M); 如果这个缓存区太小，则必须创建一个临时文件来进行排序。
table_cache = n 同时打开的数据表的数量(默认设置是64)。
tmp_table_size = n 临时HEAP数据表的最大长度(默认设置是32M); 超过这个长度的临时数据表将被转换为MyISAM数据表并存入一个临时文件。
mysqld程序--日志
引用
log [= file] 把所有的连接以及所有的SQL命令记入日志(通用查询日志); 如果没有给出file参数，MySQL将在数据库目录里创建一个hostname.log文件作为这种日志文件(hostname是服务器的主机名)。
log-slow-queries [= file] 把执行用时超过long_query_time变量值的查询命令记入日志(慢查询日志); 如果没有给出file参数，MySQL将在数据库目录里创建一个hostname-slow.log文件作为这种日志文件(hostname是服务器主机名)。
long_query_time = n 慢查询的执行用时上限(默认设置是10s)。
long_queries_not_using_indexs 把慢查询以及执行时没有使用索引的查询命令全都记入日志(其余同--log-slow-queries选项)。
log-bin [= filename] 把对数据进行修改的所有SQL命令(也就是INSERT、UPDATE和DELETE命令)以二进制格式记入日志(二进制变更日志，binary update log)。这种日志的文件名是filename.n或默认的hostname.n，其中n是一个6位数字的整数(日志文件按顺序编号)。
log-bin-index = filename 二进制日志功能的索引文件名。在默认情况下，这个索引文件与二进制日志文件的名字相同，但后缀名是.index而不是.nnnnnn。
max_binlog_size = n 二进制日志文件的最大长度(默认设置是1GB)。在前一个二进制日志文件里的信息量超过这个最大长度之前，MySQL服务器会自动提供一个新的二进制日志文件接续上。
binlog-do-db = dbname 只把给定数据库里的变化情况记入二进制日志文件，其他数据库里的变化情况不记载。如果需要记载多个数据库里的变化情况，就必须在配置文件使用多个本选项来设置，每个数据库一行。
binlog-ignore-db = dbname 不把给定数据库里的变化情况记入二进制日志文件。
sync_binlog = n 每经过n次日志写操作就把日志文件写入硬盘一次(对日志信息进行一次同步)。n=1是最安全的做法，但效率最低。默认设置是n=0，意思是由操作系统来负责二进制日志文件的同步工作。
log-update [= file] 记载出错情况的日志文件名(出错日志)。这种日志功能无法禁用。如果没有给出file参数，MySQL会使用hostname.err作为种日志文件的名字。
log-slow-queries [= file] 把执行用时超过long_query_time变量值的查询命令记入日志(慢查询日志); 如果没有给出file参数，MySQL将在数据库目录里创建一个hostname-slow.log文件作为这种日志文件(hostname是服务器主机名)。
long_query_time = n 慢查询的执行用时上限(默认设置是10s)。
long_queries_not_using_indexs 把慢查询以及执行时没有使用索引的查询命令全都记入日志(其余同--log-slow-queries选项)。
log-bin [= filename] 把对数据进行修改的所有SQL命令(也就是INSERT、UPDATE和DELETE命令)以二进制格式记入日志(二进制变更日志，binary update log)。这种日志的文件名是filename.n或默认的hostname.n，其中n是一个6位数字的整数(日志文件按顺序编号)。
log-bin-index = filename 二进制日志功能的索引文件名。在默认情况下，这个索引文件与二进制日志文件的名字相同，但后缀名是.index而不是.nnnnnn。
max_binlog_size = n 二进制日志文件的最大长度(默认设置是1GB)。在前一个二进制日志文件里的信息量超过这个最大长度之前，MySQL服务器会自动提供一个新的二进制日志文件接续上。
binlog-do-db = dbname 只把给定数据库里的变化情况记入二进制日志文件，其他数据库里的变化情况不记载。如果需要记载多个数据库里的变化情况，就必须在配置文件使用多个本选项来设置，每个数据库一行。
binlog-ignore-db = dbname 不把给定数据库里的变化情况记入二进制日志文件。
sync_binlog = n 每经过n次日志写操作就把日志文件写入硬盘一次(对日志信息进行一次同步)。n=1是最安全的做法，但效率最低。默认设置是n=0，意思是由操作系统来负责二进制日志文件的同步工作。
log-update [= file] 记载出错情况的日志文件名(出错日志)。这种日志功能无法禁用。如果没有给出file参数，MySQL会使用hostname.err作为种日志文件的名字。
mysqld程序--镜像(主控镜像服务器)
引用
server-id = n 给服务器分配一个独一无二的ID编号; n的取值范围是1~2的32次方启用二进制日志功能。
log-bin = name 启用二进制日志功能。这种日志的文件名是filename.n或默认的hostname.n，其中的n是一个6位数字的整数(日志文件顺序编号)。
binlog-do/ignore-db = dbname 只把给定数据库里的变化情况记入二进制日志文件/不把给定的数据库里的变化记入二进制日志文件。
log-bin = name 启用二进制日志功能。这种日志的文件名是filename.n或默认的hostname.n，其中的n是一个6位数字的整数(日志文件顺序编号)。
binlog-do/ignore-db = dbname 只把给定数据库里的变化情况记入二进制日志文件/不把给定的数据库里的变化记入二进制日志文件。
mysqld程序--镜像(从属镜像服务器)
引用
server-id = n 给服务器分配一个唯一的ID编号
log-slave-updates 启用从属服务器上的日志功能，使这台计算机可以用来构成一个镜像链(A->B->C)。
master-host = hostname 主控服务器的主机名或IP地址。如果从属服务器上存在mater.info文件(镜像关系定义文件)，它将忽略此选项。
master-user = replicusername 从属服务器用来连接主控服务器的用户名。如果从属服务器上存在mater.info文件，它将忽略此选项。
master-password = passwd 从属服务器用来连接主控服务器的密码。如果从属服务器上存在mater.info文件，它将忽略此选项。
master-port = n 从属服务器用来连接主控服务器的TCP/IP端口(默认设置是3306端口)。
master-connect-retry = n 如果与主控服务器的连接没有成功，则等待n秒(s)后再进行管理方式(默认设置是60s)。如果从属服务器存在mater.info文件，它将忽略此选项。
master-ssl-xxx = xxx 对主、从服务器之间的SSL通信进行配置。
read-only = 0/1 0: 允许从属服务器独立地执行SQL命令(默认设置); 1: 从属服务器只能执行来自主控服务器的SQL命令。
read-log-purge = 0/1 1: 把处理完的SQL命令立刻从中继日志文件里删除(默认设置); 0: 不把处理完的SQL命令立刻从中继日志文件里删除。
replicate-do-table = dbname.tablename 与--replicate-do-table选项的含义和用法相同，但数据库和数据库表名字里允许出现通配符"%" (例如: test%.%--对名字以"test"开头的所有数据库里的所以数据库表进行镜像处理)。
replicate-do-db = name 只对这个数据库进行镜像处理。
replicate-ignore-table = dbname.tablename 不对这个数据表进行镜像处理。
replicate-wild-ignore-table = dbn.tablen 不对这些数据表进行镜像处理。
replicate-ignore-db = dbname 不对这个数据库进行镜像处理。
replicate-rewrite-db = db1name > db2name 把主控数据库上的db1name数据库镜像处理为从属服务器上的db2name数据库。
report-host = hostname 从属服务器的主机名; 这项信息只与SHOW SLAVE HOSTS命令有关--主控服务器可以用这条命令生成一份从属服务器的名单。
slave-compressed-protocol = 1 主、从服务器使用压缩格式进行通信--如果它们都支持这么做的话。
slave-skip-errors = n1, n2, ...或all 即使发生出错代码为n1、n2等的错误，镜像处理工作也继续进行(即不管发生什么错误，镜像处理工作也继续进行)。如果配置得当，从属服务器不应该在执行SQL命令时发生错误(在主控服务器上执行出错的SQL命令不会被发送到从属服务器上做镜像处理); 如果不使用slave-skip-errors选项，从属服务器上的镜像工作就可能因为发生错误而中断，中断后需要有人工参与才能继续进行。
log-slave-updates 启用从属服务器上的日志功能，使这台计算机可以用来构成一个镜像链(A->B->C)。
master-host = hostname 主控服务器的主机名或IP地址。如果从属服务器上存在mater.info文件(镜像关系定义文件)，它将忽略此选项。
master-user = replicusername 从属服务器用来连接主控服务器的用户名。如果从属服务器上存在mater.info文件，它将忽略此选项。
master-password = passwd 从属服务器用来连接主控服务器的密码。如果从属服务器上存在mater.info文件，它将忽略此选项。
master-port = n 从属服务器用来连接主控服务器的TCP/IP端口(默认设置是3306端口)。
master-connect-retry = n 如果与主控服务器的连接没有成功，则等待n秒(s)后再进行管理方式(默认设置是60s)。如果从属服务器存在mater.info文件，它将忽略此选项。
master-ssl-xxx = xxx 对主、从服务器之间的SSL通信进行配置。
read-only = 0/1 0: 允许从属服务器独立地执行SQL命令(默认设置); 1: 从属服务器只能执行来自主控服务器的SQL命令。
read-log-purge = 0/1 1: 把处理完的SQL命令立刻从中继日志文件里删除(默认设置); 0: 不把处理完的SQL命令立刻从中继日志文件里删除。
replicate-do-table = dbname.tablename 与--replicate-do-table选项的含义和用法相同，但数据库和数据库表名字里允许出现通配符"%" (例如: test%.%--对名字以"test"开头的所有数据库里的所以数据库表进行镜像处理)。
replicate-do-db = name 只对这个数据库进行镜像处理。
replicate-ignore-table = dbname.tablename 不对这个数据表进行镜像处理。
replicate-wild-ignore-table = dbn.tablen 不对这些数据表进行镜像处理。
replicate-ignore-db = dbname 不对这个数据库进行镜像处理。
replicate-rewrite-db = db1name > db2name 把主控数据库上的db1name数据库镜像处理为从属服务器上的db2name数据库。
report-host = hostname 从属服务器的主机名; 这项信息只与SHOW SLAVE HOSTS命令有关--主控服务器可以用这条命令生成一份从属服务器的名单。
slave-compressed-protocol = 1 主、从服务器使用压缩格式进行通信--如果它们都支持这么做的话。
slave-skip-errors = n1, n2, ...或all 即使发生出错代码为n1、n2等的错误，镜像处理工作也继续进行(即不管发生什么错误，镜像处理工作也继续进行)。如果配置得当，从属服务器不应该在执行SQL命令时发生错误(在主控服务器上执行出错的SQL命令不会被发送到从属服务器上做镜像处理); 如果不使用slave-skip-errors选项，从属服务器上的镜像工作就可能因为发生错误而中断，中断后需要有人工参与才能继续进行。
mysqld--InnoDB--基本设置、表空间文件
引用
skip-innodb 不加载InnoDB数据表驱动程序--如果用不着InnoDB数据表，可以用这个选项节省一些内存。
innodb-file-per-table 为每一个新数据表创建一个表空间文件而不是把数据表都集中保存在中央表空间里(后者是默认设置)。该选项始见于MySQL 4.1。
innodb-open-file = n InnoDB数据表驱动程序最多可以同时打开的文件数(默认设置是300)。如果使用了
innodb-file-per-table选项并且需要同时打开很多数据表的话，这个数字很可能需要加大。
innodb_data_home_dir = p InnoDB主目录，所有与InnoDB数据表有关的目录或文件路径都相对于这个路径。在默认的情况下，这个主目录就是MySQL的数据目录。
innodb_data_file_path = ts 用来容纳InnoDB为数据表的表空间: 可能涉及一个以上的文件; 每一个表空间文件的最大长度都必须以字节(B)、兆字节(MB)或千兆字节(GB)为单位给出; 表空间文件的名字必须以分号隔开; 最后一个表空间文件还可以带一个autoextend属性和一个最大长度(max:n)。例如，ibdata1:1G; ibdata2:1G:autoextend:max:2G的意思是: 表空间文件ibdata1的最大长度是1GB，ibdata2的最大长度也是1G，但允许它扩充到2GB。除文件名外，还可以用硬盘分区的设置名来定义表空间，此时必须给表空间的最大初始长度值加上newraw关键字做后缀，给表空间的最大扩充长度值加上raw关键字做后缀(例如/dev/hdb1:20Gnewraw或/dev/hdb1:20Graw); MySQL 4.0及更高版本的默认设置是ibdata1:10M:autoextend。
innodb_autoextend_increment = n 带有autoextend属性的表空间文件每次加大多少兆字节(默认设置是8MB)。这个属性不涉及具体的数据表文件，那些文件的增大速度相对是比较小的。
innodb_lock_wait_timeout = n 如果某个事务在等待n秒(s)后还没有获得所需要的资源，就使用ROLLBACK命令放弃这个事务。这项设置对于发现和处理未能被InnoDB数据表驱动程序识别出来的死锁条件有着重要的意义。这个选项的默认设置是50s。
innodb_fast_shutdown 0/1 是否以最快的速度关闭InnoDB，默认设置是1，意思是不把缓存在INSERT缓存区的数据写入数据表，那些数据将在MySQL服务器下次启动时再写入(这么做没有什么风险，因为INSERT缓存区是表空间的一个组成部分，数据不会丢失)。把这个选项设置为0反面危险，因为在计算机关闭时，InnoDB驱动程序很可能没有足够的时间完成它的数据同步工作，操作系统也许会在它完成数据同步工作之前强行结束InnoDB，而这会导致数据不完整。
innodb-file-per-table 为每一个新数据表创建一个表空间文件而不是把数据表都集中保存在中央表空间里(后者是默认设置)。该选项始见于MySQL 4.1。
innodb-open-file = n InnoDB数据表驱动程序最多可以同时打开的文件数(默认设置是300)。如果使用了
innodb-file-per-table选项并且需要同时打开很多数据表的话，这个数字很可能需要加大。
innodb_data_home_dir = p InnoDB主目录，所有与InnoDB数据表有关的目录或文件路径都相对于这个路径。在默认的情况下，这个主目录就是MySQL的数据目录。
innodb_data_file_path = ts 用来容纳InnoDB为数据表的表空间: 可能涉及一个以上的文件; 每一个表空间文件的最大长度都必须以字节(B)、兆字节(MB)或千兆字节(GB)为单位给出; 表空间文件的名字必须以分号隔开; 最后一个表空间文件还可以带一个autoextend属性和一个最大长度(max:n)。例如，ibdata1:1G; ibdata2:1G:autoextend:max:2G的意思是: 表空间文件ibdata1的最大长度是1GB，ibdata2的最大长度也是1G，但允许它扩充到2GB。除文件名外，还可以用硬盘分区的设置名来定义表空间，此时必须给表空间的最大初始长度值加上newraw关键字做后缀，给表空间的最大扩充长度值加上raw关键字做后缀(例如/dev/hdb1:20Gnewraw或/dev/hdb1:20Graw); MySQL 4.0及更高版本的默认设置是ibdata1:10M:autoextend。
innodb_autoextend_increment = n 带有autoextend属性的表空间文件每次加大多少兆字节(默认设置是8MB)。这个属性不涉及具体的数据表文件，那些文件的增大速度相对是比较小的。
innodb_lock_wait_timeout = n 如果某个事务在等待n秒(s)后还没有获得所需要的资源，就使用ROLLBACK命令放弃这个事务。这项设置对于发现和处理未能被InnoDB数据表驱动程序识别出来的死锁条件有着重要的意义。这个选项的默认设置是50s。
innodb_fast_shutdown 0/1 是否以最快的速度关闭InnoDB，默认设置是1，意思是不把缓存在INSERT缓存区的数据写入数据表，那些数据将在MySQL服务器下次启动时再写入(这么做没有什么风险，因为INSERT缓存区是表空间的一个组成部分，数据不会丢失)。把这个选项设置为0反面危险，因为在计算机关闭时，InnoDB驱动程序很可能没有足够的时间完成它的数据同步工作，操作系统也许会在它完成数据同步工作之前强行结束InnoDB，而这会导致数据不完整。
mysqld程序--InnoDB--日志
引用
innodb_log_group_home_dir = p 用来存放InnoDB日志文件的目录路径(如ib_logfile0、ib_logfile1等)。在默认的情况下，InnoDB驱动程序将使用MySQL数据目录作为自己保存日志文件的位置。
innodb_log_files_in_group = n 使用多少个日志文件(默认设置是2)。InnoDB数据表驱动程序将以轮转方式依次填写这些文件; 当所有的日志文件都写满以后，之后的日志信息将写入第一个日志文件的最大长度(默认设置是5MB)。这个长度必须以MB(兆字节)或GB(千兆字节)为单位进行设置。
innodb_flush_log_at_trx_commit = 0/1/2 这个选项决定着什么时候把日志信息写入日志文件以及什么时候把这些文件物理地写(术语称为"同步")到硬盘上。设置值0的意思是每隔一秒写一次日志并进行同步，这可以减少硬盘写操作次数，但可能造成数据丢失; 设置值1(设置设置)的意思是在每执行完一条COMMIT命令就写一次日志并进行同步，这可以防止数据丢失，但硬盘写操作可能会很频繁; 设置值2是一般折衷的办法，即每执行完一条COMMIT命令写一次日志，每隔一秒进行一次同步。
innodb_flush_method = x InnoDB日志文件的同步办法(仅适用于UNIX/Linux系统)。这个选项的可取值有两种: fdatasync，用fsync()函数进行同步; O_DSYNC，用O_SYNC()函数进行同步。
innodb_log_archive = 1 启用InnoDB驱动程序的archive(档案)日志功能，把日志信息写入ib_arch_log_n文件。启用这种日志功能在InnoDB与MySQL一起使用时没有多大意义(启用MySQL服务器的二进制日志功能就足够用了)。
innodb_log_files_in_group = n 使用多少个日志文件(默认设置是2)。InnoDB数据表驱动程序将以轮转方式依次填写这些文件; 当所有的日志文件都写满以后，之后的日志信息将写入第一个日志文件的最大长度(默认设置是5MB)。这个长度必须以MB(兆字节)或GB(千兆字节)为单位进行设置。
innodb_flush_log_at_trx_commit = 0/1/2 这个选项决定着什么时候把日志信息写入日志文件以及什么时候把这些文件物理地写(术语称为"同步")到硬盘上。设置值0的意思是每隔一秒写一次日志并进行同步，这可以减少硬盘写操作次数，但可能造成数据丢失; 设置值1(设置设置)的意思是在每执行完一条COMMIT命令就写一次日志并进行同步，这可以防止数据丢失，但硬盘写操作可能会很频繁; 设置值2是一般折衷的办法，即每执行完一条COMMIT命令写一次日志，每隔一秒进行一次同步。
innodb_flush_method = x InnoDB日志文件的同步办法(仅适用于UNIX/Linux系统)。这个选项的可取值有两种: fdatasync，用fsync()函数进行同步; O_DSYNC，用O_SYNC()函数进行同步。
innodb_log_archive = 1 启用InnoDB驱动程序的archive(档案)日志功能，把日志信息写入ib_arch_log_n文件。启用这种日志功能在InnoDB与MySQL一起使用时没有多大意义(启用MySQL服务器的二进制日志功能就足够用了)。
mysqld程序--InnoDB--缓存区的设置和优化
引用
innodb_log_buffer_pool_size = n 为InnoDB数据表及其索引而保留的RAM内存量(默认设置是8MB)。这个参数对速度有着相当大的影响，如果计算机上只运行有MySQL/InnoDB数据库服务器，就应该把全部内存的80%用于这个用途。
innodb_log_buffer_size = n 事务日志文件写操作缓存区的最大长度(默认设置是1MB)。
innodb_additional_men_pool_size = n 为用于内部管理的各种数据结构分配的缓存区最大长度(默认设置是1MB)。
innodb_file_io_threads = n I/O操作(硬盘写操作)的最大线程个数(默认设置是4)。
innodb_thread_concurrency = n InnoDB驱动程序能够同时使用的最大线程个数(默认设置是8)。
innodb_log_buffer_size = n 事务日志文件写操作缓存区的最大长度(默认设置是1MB)。
innodb_additional_men_pool_size = n 为用于内部管理的各种数据结构分配的缓存区最大长度(默认设置是1MB)。
innodb_file_io_threads = n I/O操作(硬盘写操作)的最大线程个数(默认设置是4)。
innodb_thread_concurrency = n InnoDB驱动程序能够同时使用的最大线程个数(默认设置是8)。
mysqld程序--其它选项
引用
bind-address = ipaddr MySQL服务器的IP地址。如果MySQL服务器所在的计算机有多个IP地址，这个选项将非常重要。
default-storage-engine = type 新数据表的默认数据表类型(默认设置是MyISAM)。这项设置还可以通过--default-table-type选项来设置。
default-timezone = name 为MySQL服务器设置一个地理时区(如果它与本地计算机的地理时区不一样)。
ft_min_word_len = n 全文索引的最小单词长度工。这个选项的默认设置是4，意思是在创建全文索引时不考虑那些由3个或更少的字符构建单词。
Max-allowed-packet = n 客户与服务器之间交换的数据包的最大长度，这个数字至少应该大于客户程序将要处理的最大BLOB块的长度。这个选项的默认设置是1MB。
Sql-mode = model1, mode2, ... MySQL将运行在哪一种SQL模式下。这个选项的作用是让MySQL与其他的数据库系统保持最大程度的兼容。这个选项的可取值包括ansi、db2、oracle、no_zero_date、pipes_as_concat。
default-storage-engine = type 新数据表的默认数据表类型(默认设置是MyISAM)。这项设置还可以通过--default-table-type选项来设置。
default-timezone = name 为MySQL服务器设置一个地理时区(如果它与本地计算机的地理时区不一样)。
ft_min_word_len = n 全文索引的最小单词长度工。这个选项的默认设置是4，意思是在创建全文索引时不考虑那些由3个或更少的字符构建单词。
Max-allowed-packet = n 客户与服务器之间交换的数据包的最大长度，这个数字至少应该大于客户程序将要处理的最大BLOB块的长度。这个选项的默认设置是1MB。
Sql-mode = model1, mode2, ... MySQL将运行在哪一种SQL模式下。这个选项的作用是让MySQL与其他的数据库系统保持最大程度的兼容。这个选项的可取值包括ansi、db2、oracle、no_zero_date、pipes_as_concat。
注意：如果在配置文件里给出的某个选项是mysqld无法识别的，MySQL服务器将不启动。
以下MySQL提升性能优化的服务器系统参数是比较关键的：
(1)、back_log：
(2)、interactive_timeout：
(3)、key_buffer_size：
(4)、max_connections：
(5)、record_buffer：
(6)、sort_buffer：
(7)、table_cache：
(8)、thread_cache_size：
(10)、wait_timeout：
注：参数的调整可以通过修改 /etc/my.cnf 文件并重启 MySQL 实现。这是一个比较谨慎的工作，上面的结果也仅仅是我的一些看法，你可以根据你自己主机的硬件情况（特别是内存大小）进一步修改。
(1)、back_log：
引用
要求 MySQL 能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。
back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 试图设定back_log高于你的操作系统的限制将是无效的。
当你观察你的主机进程列表，发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大 back_log 的值了。默认数值是50，我把它改为500。
back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 试图设定back_log高于你的操作系统的限制将是无效的。
当你观察你的主机进程列表，发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大 back_log 的值了。默认数值是50，我把它改为500。
(2)、interactive_timeout：
引用
服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对 mysql_real_connect()使用 CLIENT_INTERACTIVE 选项的客户。 默认数值是28800，我把它改为7200。
(3)、key_buffer_size：
引用
索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。默认数值是8388600(8M)，我的MySQL主机有2GB内存，所以我把它改为402649088(400MB)。
(4)、max_connections：
引用
允许的同时客户的数量。增加该值增加 mysqld 要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到 Too many connections 错误。 默认数值是100，我把它改为1024 。
(5)、record_buffer：
引用
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是131072(128K)，我把它改为16773120 (16M)
(6)、sort_buffer：
引用
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。默认数值是2097144(2M)，我把它改为 16777208 (16M)。
(7)、table_cache：
引用
为所有线程打开表的数量。增加该值能增加mysql要求的文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符。默认数值是64，我把它改为512。
(8)、thread_cache_size：
引用
可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。我把它设置为 80。
(10)、wait_timeout：
引用
服务器在关闭它之前在一个连接上等待行动的秒数。 默认数值是28800，我把它改为7200。
注：参数的调整可以通过修改 /etc/my.cnf 文件并重启 MySQL 实现。这是一个比较谨慎的工作，上面的结果也仅仅是我的一些看法，你可以根据你自己主机的硬件情况（特别是内存大小）进一步修改。
