好博客：MySQL
http://webnoties.blog.163.com/blog/#m=0&t=1&c=fks_084071081087081070085085086095085094082070086083087071085
http://www.cnblogs.com/mailingfeng/archive/2012/09/26/2704344.html
索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。
在数据库表中，对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表：
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL ); 我们随机向里面插入了10000条记录，其中有一条：5555, admin。
在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时，如果在username上已经建立了索引，MySQL无须任何扫描，即准确可找到该记录。相反，MySQL会扫描 所有记录，即要查询10000条记录。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。
MySQL索引类型包括：
（1）普通索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：
◆创建索引
CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。
◆修改表结构
ALTER mytable ADD INDEX [indexName] ON (username(length)) ◆创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) ); 删除索引的语法：
DROP INDEX [indexName] ON mytable;
（2）唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
◆创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length)) ◆修改表结构
ALTER mytable ADD UNIQUE [indexName] ON (username(length)) ◆创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );
（3）主键索引
它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ); 当然也可以用 ALTER 命令。记住：一个表只能有一个主键。
（4）组合索引
为了形象地对比单列索引和组合索引，为表添加多个字段：
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL ); 为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。
如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。
建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：
usernname,city,age usernname,city usernname 为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都 会用到该组合索引，下面的几个SQL就会用到这个组合索引：
SELECT * FROM mytable WHREE username="admin" AND city="郑州" SELECT * FROM mytable WHREE username="admin" 而下面几个则不会用到：
SELECT * FROM mytable WHREE age=20 AND city="郑州" SELECT * FROM mytable WHREE city="郑州"
（5）建立索引的时机
到这里我们已经学会了建立索引，那么我们需要在什么情况下建立索引呢？一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此， 因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：
SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' 此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。
刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引：
SELECT * FROM mytable WHERE username like'admin%' 而下句就不会使用：
SELECT * FROM mytable WHEREt Name like'%admin' 因此，在使用LIKE时应注意以上的区别。
（6）索引的不足之处
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：
◆虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
◆建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。
（7）使用索引的注意事项
使用索引时，有以下一些技巧和注意事项：
◆索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
◆使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
◆索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
◆like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
◆不要在列上进行运算
select * from users where YEAR(adddate)<2007; 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2007-01-01’;
◆不使用NOT IN和<>操作
以上，就对其中MySQL索引类型进行了介绍。
1.前言
在web开发中，
页面模板，业务逻辑(包括缓存、连接池)和数据库这三个部分，数据库在其中负责执行SQL查询并返回查询结果，是影响网站速度最重要的性能瓶颈。本文主要
针对MySql数据库，双十一的电商大战，引发了淘宝技术热议，而淘宝现在去IOE(I代表IBM的缩写，即去IBM的存储设备和小型机;O是代表
Oracle的缩写，也即去Oracle数据库，采用MySQL和Hadoop替代的解决方案，;E是代表EMC2,即去EMC2的设备性,用PC
Server替代EMC2)，大量采用MySql集群！让MySql再次成为耀眼的明星！而优化数据的重要一步就是索引的建立，对于mysql中出现的慢
查询，我们可以通过使用索引来提升查询速度。索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL将进行全表扫描，从第1条记录开始然后读
完整个表直到找出相关的行。
2.mysql索引类型及创建
常用的索引类型有
(1)主键索引
它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：
CREATE TABLE user(
id int unsigned not null auto_increment,
name varchar(50) not null,
email varchar(40) not null,
primary key (id)
);
(2)普通索引
这是最基本的索引，它没有任何限制。创建方式：
create index idx_name on user(
name(20)
);
mysql支持前缀索引，一般姓名不会超过20个字符，所以我们这里建立索引的时候限定了长度20，这样可以节省索引文件大小
(3)唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。创建方式：
CREATE UNIQUE INDEX idx_email ON user(
);
(4)全文索引
MySQL支持全文索引和搜索功能。MySQL中的全文索引类型为FULLTEXT的索引。 FULLTEXT 索引仅可用于 MyISAM表；
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
);
mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database');
查询结果：
+----+-------------------+------------------------------------------+
| id | title | body |
+----+-------------------+------------------------------------------+
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)
MATCH()
函数对于一个字符串执行资料库内的自然语言搜索。一个资料库就是1套1个或2个包含在FULLTEXT内的列。搜索字符串作为对AGAINST()的参数
而被给定。对于表中的每一行, MATCH() 返回一个相关值，即, 搜索字符串和 MATCH()表中指定列中该行文字之间的一个相似性度量。
(5)复合索引
CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name)
);
name索引是一个对last_name和first_name的索引。索引可以用于为last_name，或者为last_name和first_name在已知范围内指定值的查询。因此，name索引用于下面的查询：
SELECT * FROM test WHERE last_name='Widenius';
SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael';
但是不能用于SELECT * FROM test WHERE first_name='Michael';这是因为MySQL组合索引为“最左前缀”的结果,简单的理解就是只从最左面的开始组合。
3.在什么情况下使用索引
(1)为搜索字段建索引,如果在你的表中，某个字段你经常用来做搜索，那么，请为其建立索引吧。一般来说，在WHERE和JOIN中出现的列需要建立索引以提高查询速度。
例如从fps表（表中有name字段）中检索姓名为"李武"的人，
下面用explain来解释执行建立索引和未建立索引的区别：
a.未建立索引前
explain select name from fps where name="李武";
[SQL] select name from fps where name="李武";
影响的数据栏: 0
时间: 0.003ms
b.建立索引后
create index idx_name on fps(
name
);
explain select name from fps where name="李武";
[SQL] select name from fps where name="李武";
影响的数据栏: 0
时间: 0.001ms
(2)下面我们就来看看这个EXPLAIN分析结果的含义。
table：这是表的名字。
type：连接操作的类型。下面是MySQL文档关于ref连接类型的说明：
“对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是
UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接
类
型是不错的。” 在本例中，由于索引不是UNIQUE类型，ref是我们能够得到的最好连接类型。
如果EXPLAIN显示连接类型是“ALL”，而且你并不想从表里面选择出大多数记录，那么MySQL的操作效率将非常低，因为它要扫描整个表。你可以加
入更多的索引来解决这个问题。预知更多信息，请参见MySQL的手册说明。
possible_keys：
可能可以利用的索引的名字。这里的索引名字是创建索引时指定的索引昵称；如果索引没有昵称，则默认显示的是索引中第一个列的名字
（在本例中，它是“idx_name”）。
Key：
它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。
key_len：
索引中被使用部分的长度，以字节计。
ref：
它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行。在本例中，MySQL根据三个常量选择行。
rows：
MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1。 本例中未索引前遍历的记录数为1041，而建立索引后为1
Extra：
这里可能出现许多不同的选项，其中大多数将对查询产生负面影响。在本例中，MySQL只是提醒我们它将用using where，using index子句限制搜索结果集。
4.最常用的存储引擎：
(1)Myisam存储引擎：每
个Myisam在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别为.frm（存储表定义）、.MYD（存储数据）、.MYI（存储索引）。数据文
件和索引文件可以放置在不同目录，平均分布io，获得更快的速度。对存储大小没有限制，MySQL数据库的最大有效表尺寸通常是由操作系统对文件大小的限
制决定的,
(2)InnoDB存储引擎：具有提交、回滚、奔溃恢复能力的事务安全。与Myisam相比，InnoDB的写效率差一些并且会占用更多的磁盘空间以保留数据和索引。
(3)如何选择合适的引擎
下面是常用存储引擎适用的环境：
Myisam：它是在Web、数据仓储和其他应用环境下最常使用的存储引擎;
InnoDB：用于事务处理应用程序，具有更多特性，包括ACID事务特性。
EXPLAIN
先看一下arena_match_index的表结构，大家注意表的索引结构
CREATE TABLE `arena_match_index` (
`tid` int(10) unsigned NOT NULL DEFAULT '0',
`mid` int(10) unsigned NOT NULL DEFAULT '0',
`group` int(10) unsigned NOT NULL DEFAULT '0',
`round` tinyint(3) unsigned NOT NULL DEFAULT '0',
`day` date NOT NULL DEFAULT '0000-00-00',
`begintime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
UNIQUE KEY `tm` (`tid`,`mid`),
KEY `mid` (`mid`),
KEY `begintime` (`begintime`),
KEY `dg` (`day`,`group`),
KEY `td` (`tid`,`day`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
接着看下面的sql：
SELECT round FROM arena_match_index WHERE `day` = '2010-12-31' AND `group` = 18 AND `begintime` < '2010-12-31 12:14:28' order by begintime LIMIT 1;
这条sql的查询条件显示可能使用的索引有`begintime`和`dg`，但是由于使用了order by begintime排序mysql最后选择使用`begintime`索引，explain的结果为：
mysql>
explain SELECT round FROM arena_match_index WHERE `day` =
'2010-12-31' AND `group` = 18 AND `begintime` < '2010-12-31 12:14:28'
order by begintime LIMIT 1;
+----+-------------+-------------------+-------+---------------+-----------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+-------+---------------+-----------+---------+------+--------+-------------+
| 1 | SIMPLE | arena_match_index | range | begintime,dg | begintime | 8 | NULL | 226480 | Using where |
+----+-------------+-------------------+-------+---------------+-----------+---------+------+--------+-------------+
explain的结果显示使用`begintime`索引要扫描22w条记录，这样的查询性能是非常糟糕的，实际的执行情况也是初次执行（还未有缓存数据时）时需要30秒以上的时间。
实际上这个查询使用`dg`联合索引的性能更好，因为同一天同一个小组内也就几十场比赛，因此应该优先使用`dg`索引定位到匹配的数据集合再进行排序，那么如何告诉mysql使用指定索引呢？使用use index语句：
mysql> explain SELECT round FROM arena_match_index use index (dg) WHERE `day` = '2010-12-31' AND `group` = 18 AND `begintime` < '2010-12-31 12:14:28' order by begintime LIMIT 1;
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-----------------------------+
|
id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-----------------------------+
| 1 | SIMPLE | arena_match_index | ref | dg | dg | 7 | const,const | 757 | Using where; Using filesort |
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-----------------------------+
explain结果显示使用`dg`联合索引只需要扫描757条数据，性能直接提升了上百倍，实际的执行情况也是几乎立即就返回了查询结果。
在最初的查询语句中只要把order by begintime去掉，mysql就会使用`dg`索引了，再次印证了order by会影响mysql的索引选择策略！
mysql>
explain SELECT round FROM arena_match_index WHERE `day` =
'2010-12-31' AND `group` = 18 AND `begintime` < '2010-12-31
12:14:28' LIMIT 1;