之前整理的一篇关于mysql数据库优化的文章，希望还能用得上：）

1.1 建表 – 表的类型MySQL中表的类型表现为不同的存储引擎（Storage Engine），同一种存储引擎在不同的应用中表现出的性能差异也是不容忽略的重要因素。因此，在建表之初应当正确选择表的类型。鉴于我们的应用范围熟悉MyISAM和InnoDB两种类型的性能区别即可，MyISAM类型在select操作多的应用中优势明显；InnoDB在insert、update操作多的应用中优势明显。

1.2 建表 – 数据类型
选择数据类型来帮助提高查询运行速度
把数据列定义成不能为空（NOT NULL）。这会使处理速度更快，需要的存储更少。它有时候还简化了查询，因为在某些情况下不需要检查值的NULL属性。
考虑使用ENUM数据列。如果某个数据列的基数很低（包含的不同的值数量有限），那么可以考虑把它转换为ENUM列。ENUM值可以被更快地处理，因为它们在内部表现为数值。

1.3 建表 – 索引
索引是提高查询速度的最重要的工具。当然还有其它的一些技术可供使用，但是一般来说引起最大性能差异的都是索引的正确使用与否。
如果要考虑给已经索引过的表添加索引，那么就要考虑将要增加的索引是否是已存在于多列索引的最左前缀。如果是已存在的就不用新增加索引了。
最左前缀（Leftmost Prefixing）：多列索引的一个优点，比如在用户表上建立了一个username、nickname、birth列上的多列索引，我们称这个索引为fname_lname_ birth 。当搜索条件是以下各种列的组合时，MySQL将使用u_n_b索引：
username，nickname， birth
username，nickname
username
从另一方面理解，它相当于创建了(username，nickname， birth)、(username，nickname)以及(nickname)这些列组合上的索引。

普通索引、唯一性索引、主键、全文索引

普通索引
这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建：
创建索引，例如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”。我们常用的ID字段通常是这种类型的索引，且通常会增加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。

2.1 导入数据
批量载入比单行载入的效率高，因为在每条记录被载入后，键缓存（key cache）不用刷新（flush）；可以在这批记录的末尾刷新键缓存。键缓存刷新的频率减少得越多，数据载入的速度就越快。
没有索引的数据表的载入速度比有索引的要快一些。如果存在索引，不但要把记录添加到数据文件中，还必须修改索引来反映新增的记录，因此要合理添加索引。

3.1 查询
虽然索引对查询有着至关重要的作用，但查询语句同样对性能有很大影响。查询中条件的罗列及顺序会影响到MySQL查询优化器对索引的利用。查询原则：在多个条件情况下最先使用能够得到最小子集且已建立索引的字段；以逐步缩小查询范围，减少磁盘I/O操作。

大多情况下应该索引WHERE子句和join子句中出现的每一个列，但并不完全。还必须考虑到对列进行比较的操作符类型。MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符（%或者_）开头的情形。例如，“SELECT id FROM `table` WHERE `words` LIKE ‘ddes%’”这个查询将使用索引，但“SELECT id FROM `table` WHERE `words` LIKE ‘%ddes’;”这个查询不会使用索引。

EXPLAIN在实际应用中常用MySQL提供的explain命令对需要执行的SQL进行性能检查以达到优化的目的。
语法：
EXPLAIN tbl_name
Or:
EXPLAIN [EXTENDED] SELECT select_options

EXPLAIN SELECT * FROM `m2v_Mod_Log`
WHERE `urlMD5` = ‘016879b13274bcf710f5edb00066535d’
AND STATUS = ‘1′

EXPLAIN SELECT * FROM `m2v_Mod_Log`
WHERE `urlMD5` = ‘016879b13274bcf710f5edb00066535d’

EXPLAIN分析结果的含义。
table：这是表的名字。
type：连接操作的类型。
possible_keys：可能可以利用的索引的名字。
Key：显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。
key_len：索引中被使用部分的长度，以字节计。
ref：它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行。
rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。
Extra：这里可能出现许多不同的选项，其中大多数将对查询产生负面影响。
更多详细>>

作者：老王

前记：很多东西看似简单，那是因为你并未真正了解它。

Explain命令用于查看执行效果。虽然这个命令只能搭配select类型语句使用，如果你想查看update，delete类型语句中的索引效果，也不是太难的事情，只要保持条件不变，把类型转换成select就行了。

explain的语法如下：

explain [extended] select … from … where …

如果使用了extended，那么在执行完explain语句后，可以使用show warnings语句查询相应的优化信息。

==============================================================

mk-visual-explain工具扩展了explain，它提供了一种更直观的树形表现形式，使用方法很简单：

mk-visual-explain <file_containing_explain_output>
mk-visual-explain -c <file_containing_query>
mysql -e “explain select * from mysql.user” | mk-visual-explain

也可以在MySQL命令行里通过设置pager的方式来执行：

mysql> pager mk-visual-explain
mysql> explain [extended] select … from … where …

==============================================================

进入正题，为了让例子更具体化，我们先来建一个表，插入一点测试数据：

[code lang="sql" line="1" file="create_table.sql" colla="+"]
CREATE TABLE IF NOT EXISTS `article` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`author_id` int(10) unsigned NOT NULL,
`category_id` int(10) unsigned NOT NULL,
`views` int(10) unsigned NOT NULL,
`comments` int(10) unsigned NOT NULL,
`title` varbinary(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`)
);

INSERT INTO `article`
(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
(1, 1, 1, 1, ‘1′, ‘1′),
(2, 2, 2, 2, ‘2′, ‘2′);

[/code]

缺省只建了一个主键，没有建其他的索引。测试时，如果你时间充裕，应该尽可能插入多一点的测试数据，怎么说也应该保证几千条。如果数据量过少，可能会影响MySQL在索引选择上的判断。如此一来，一旦产品上线，数据量增加。索引往往不会按照你的预想工作。

下面让我们设置一个任务：查询category_id为1且comments大于1的情况下，views最多的article_id。

问题很简单，SQL也很简单：

[code lang="sql" line="1" colla="+"]
SELECT author_id
FROM `article`
WHERE category_id = 1 AND comments > 1
ORDER BY views DESC
LIMIT 1
[/code]

下面让我们用explain命令查看索引效果：

[code lang="sql" line="1" colla="+"]
EXPLAIN SELECT author_id
FROM `article`
WHERE category_id = 1
AND comments > 1
ORDER BY views DESC
LIMIT 1
[/code]

这时explain部分结果如下：

type: ALL
key: NULL
Extra: Using where; Using filesort

显示数据库进行了全表扫描，没有用到索引，并且在过程中文件排序。这样的结果肯定是糟糕的，下面让我们通过建立索引优化一下它：

[code lang="sql" line="0" colla="+"]
ALTER TABLE `article` ADD INDEX x ( `category_id` , `comments`, `views` ) ;
[/code]

这时explain部分结果如下：

type: range
key: x
Extra: Using where; Using filesort

虽然不再是全表扫描了，但是仍然存在文件排序。一般来说，文件排序都是由于ORDER BY语句一起的，而我们已经把views字段放到了联合索引里面，为什么没有效果呢？这是因为按照B-Tree的工作原理，先排序category_id，如果遇到相同的category_id则再排序comments，如果遇到相同的comments则再排序views。当comments字段在联合索引里处于中间位置时，因为comments > 1条件是一个范围值，所以导致views部分索引无效。从这个意义上来说，此时的category_id, comments, views复合索引的效果不会比category_id, comments复合索引的效果好。

文件排序是否会引起性能问题要视数据分布情况而定。这里有一个案例可供参考：Using index for ORDER BY vs restricting number of rows.

多数情况下应该避免出现它。此时可以这样设置索引：

[code lang="sql" line="0" colla="+"]
ALTER TABLE `article` ADD INDEX y ( `category_id` , `views` ) ;
[/code]

这时explain部分结果如下：

type: range
key: x
Extra: Using where; Using filesort

很奇怪，系统无视我们刚建立的y索引，还使用x索引。导致仍然存在文件排序。

如果你也出现了类似的情况，可以使用强制索引：

[code lang="sql" line="1" colla="+"]
EXPLAIN SELECT author_id
FROM `article`
FORCE INDEX ( y )
WHERE category_id =1
AND comments >1
ORDER BY views DESC
LIMIT 1
[/code]

这时explain部分结果如下：

type: ref
key: y
Extra: Using where

也可以删除x索引，那样系统会自动使用y索引（有时候MySQL比较傻，所以你得会使用FORCE INDEX）。

后记：Explain的type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

Explain的Extra信息也相当重要，如果此信息显示Using filesort或者Using temporary的话，噩梦即将开始，不过也不尽然，比如说在一个WHERE … ORDER BY … 类型的查询里，很多时候我们无法创建一个兼顾WHERE和ORDER BY的索引，此时如果按照WHERE来确定索引，那么在ORDER BY时，就必然会引起Using filesort，文件排序是好是坏需要仔细判断，说白了就是看是先过滤再排序划算，还是先排序再过滤划算，正确答案取决与数据分布的情况，具体的情况可以参考Using index for ORDER BY vs restricting number of rows。

Explain具体含义参见此链接：http://dev.mysql.com/doc/refman/5.1/en/using-explain.html