10年开发码农带你理解MySQL——架构与概念＆索引与优化

 

前言

最早接触的MySQL是在三年前，那时候MySQL还是4.x版本，很多功能都不支持，比如，存储过程，视图，触发器，更别说分布式事务等复杂特性了。但从5.0(2005年10月)开始，MySQL渐渐步入企业级数据库的行列了；复制、集群、分区、分布式事务，这些企业级的特性，使得现在的MySQL，完全可以应用于企业级应用环境(很多互联网公司都用其作为数据库服务器，尽管节约成本是一个因素，但是没有强大功能作后盾，则是不可想象的)。虽然，MySQL还有很多不足，比如，复制、分区的支持都十分有限、查询优化仍需要改进，但是MySQL已经是一个足够好的DBMS了，更何况它是opensource的。这段时间没有事，出于好奇，略微的研究了一下MySQL，积累了一些资料，欲总结出来。这些资料打算分为两部分，上部主要讨论MySQL的优化，如果有时间，以后在下部分析一下MySQL的源码。如果你是MySQL高手，希望你不吝赐教；如果你是新手，希望对你有用。

MySQL架构与概念

1、MySQL的逻辑架构

 

最上面不是MySQL特有的，所有基于网络的C/S的网络应用程序都应该包括连接处理、认证、安全管理等。
中间层是MySQL的核心，包括查询解析、分析、优化和缓存等。同时它还提供跨存储引擎的功能，包括存储过程、触发器和视图等。
最下面是存储引擎，它负责存取数据。服务器通过storage engine API可以和各种存储引擎进行交互。

1.1、查询优化和执行(Optimization and Execution)

MySQL将用户的查询语句进行解析，并创建一个内部的数据结构——分析树，然后进行各种优化，例如重写查询、选择读取表的顺序，以及使用哪个索引等。查询优化器不关心一个表所使用的存储引擎，但是存储引擎会影响服务器如何优化查询。优化器通过存储引擎获取一些参数、某个操作的执行代价、以及统计信息等。在解析查询之前，服务器会先访问查询缓存(query cache)——它存储SELECT语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。

1.2、并发控制
MySQL提供两个级别的并发控制：服务器级(the server level)和存储引擎级(the storage engine level)。加锁是实现并发控制的基本方法，MySQL中锁的粒度：
(1) 表级锁：MySQL独立于存储引擎提供表锁，例如，对于ALTER TABLE语句，服务器提供表锁(table-level lock)。
(2) 行级锁：InnoDB和Falcon存储引擎提供行级锁，此外，BDB支持页级锁。InnoDB的并发控制机制，下节详细讨论。
另外，值得一提的是，MySQL的一些存储引擎（如InnoDB、BDB）除了使用封锁机制外，还同时结合MVCC机制，即多版本两阶段封锁协议(Multiversion two-phrase locking protocal)，来实现事务的并发控制，从而使得只读事务不用等待锁，提高了事务的并发性。
注：并发控制是DBMS的核心技术之一(实际上，对于OS也一样)，它对系统性能有着至关重要的影响，以后再详细讨论。

1.3、事务处理
MySQL中，InnoDB和BDB都支持事务处理。这里主要讨论InnoDB的事务处理(关于BDB的事务处理，也十分复杂，以前曾较为详细看过其源码，以后有机会再讨论)。
1.3.1、事务的ACID特性
事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性(Jim Gray在《事务处理：概念与技术》中对事务进行了详尽的讨论)。
(1)原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
(2)一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
(3)隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
(4)持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
1.3.2、事务处理带来的相关问题
由于事务的并发执行，带来以下一些著名的问题：
(1)更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。
(2)脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做"脏读"。
(3)不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。
(4)幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。
1.3.3、事务的隔离性
SQL2标准定义了四个隔离级别。定义语句如下：
SET TRANSACTION ISOLATION LEVEL
[READ UNCOMMITTED |
READ COMMITTED |
REPEATABLE READ |
SERIALIZABLE ]
这与Jim Gray所提出的隔离级别有点差异。其中READ UNCOMMITTED即Jim的10（浏览）；READ COMMITTED即20，游标稳定性；REPEATABLE READ为2.99990隔离(没有幻像保护)；SERIALIZABLE隔离级别为30，完全隔离。SQL2标准默认为完全隔离(30)。各个级别存在问题如下：

 

各个具体数据库并不一定完全实现了上述4个隔离级别，例如，Oracle只提供READ COMMITTED和Serializable两个标准隔离级别，另外还提供自己定义的Read only隔离级别；SQL Server除支持上述ISO/ANSI SQL92定义的4个隔离级别外，还支持一个叫做“快照”的隔离级别，但严格来说它是一个用MVCC实现的Serializable隔离级别。MySQL 支持全部4个隔离级别，其默认级别为Repeatable read，但在具体实现时，有一些特点，比如在一些隔离级别下是采用MVCC一致性读。国产数据库DM也支持所有级别，其默认级别为READ COMMITTED。

1.3.4、InnoDB的锁模型InnoDB的行级锁有两种类型：

(1)共享锁(shared lock，S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

(2)排它锁(exclusive lock，X)：允许获得排它锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。此外，InnoDB支持多粒度加锁(multiple granularity locking)，从而允许对记录和表同时加锁。为此，InnoDB引入意向锁(intention locks)，意向锁是针对表的：(1)意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。(2)意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。例如，SELECT ... LOCK IN SHARE MODE加IS锁，SELECT ... FOR UPDATE加IX锁，意向锁的规则如下：(1)事务在对表T中的记录获取S锁前，先要获取表T的IS锁或者更强的锁；(2)事务在获取表T中记录的X锁前，先要获取表T的IX锁。InnoDB的锁相容性矩阵：

 

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。意向锁只会阻塞其它事务对表的请求，例如，LOCK TABLES …WRITE，意向锁的主要目的是表明该事务将要或者正在对表中的记录加锁。使用封锁机制来进行并发控制，一个比较重要的问题就是死锁。
来看一个死锁的例子：

 

1.3.5、一致性非阻塞读
一致性都是MySQL的重要特点之一，InnoDB通过MVCC机制表示数据库某一时刻的查询快照，查询可以看该时刻之前提交的事务所做的改变，但是不能看到该时刻之后或者未提交事务所做的改变。但是，查询可以看到同一事务中之前语句所做的改变，例如：

 

如果事务的隔离级别为REPEATABLE READ（默认），同一个事务中的所有一致性读都是读的事务的第一次读操作创建的快照。你可以提交当前事务，然后在新的查询中即可看到最新的快照，如上所示。
如果事物的隔离级别为READ COMMITTED，一致性都只是对事务内部的读操作和它自己的快照而言的，结果如下：

 

注意，session 2发生了不可重复都。当InnoDB在READ COMMITTED 和REPEATABLE READ隔离级别下处理SELECT语句时，一致性都是默认的模式。一致性读不会对表加任何锁，所以，其它连接可以同时改变表。假设事务处于REPEATABLE READ级别，当你正在进行一致性读时，InnoDB根据查询看到的数据给你一个时间点。如果其它的事务在该时间点之后删除一行，且提交事务，你不会看到行已经被删除，插入和更新操作一样。但是，InnoDB与其它DBMS的不同是，在REPEATABLE READ隔离级别下并不会造成幻像。一致性都不与DROP TABLE 或者 ALTER TABLE一起工作。在nodb_locks_unsafe_for_binlog变量被设置或者事务的隔离级别不是SERIALIZABLE的情况下，InnoDB对于没有指定FOR UPDATE 或 LOCK IN SHARE MODE的INSERT INTO ... SELECT, UPDATE ... (SELECT), 和CREATE TABLE ... SELECT语句使用一致性读，在这种情况下，查询语句不会对表中的元组加锁。否则，InnoDB将使用锁。

1.3.6、SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE的加锁读(locking read)在一些场合，一致性读并不是很方便，此时，可以用加锁读。InnoDB支持两种加速读：

(1) SELECT ... LOCK IN SHARE MODE：对读取的元组加S锁。

(2) SELECT ... FOR UPDATE：在扫描索引记录的过程中，会阻塞其它连接的SELECT ...LOCK IN SHARE MODE和一定事务隔离级别下的读操作。InnoDB使用两阶段封锁协议，事务直到提交或回滚时才会释放所有的锁，这都是系统自动执行的。此外，MySQL支持LOCK TABLES和UNLOCK TABLES，但这些都是在服务器层实现的，而不是在存储引擎。它们有用处，但是不能取代存储引擎完成事务处理，如果你需要事务功能，请使用事务型存储引擎。来考虑locking read的应用，假设你要在表child插入一个新的元组，并保证child中的记录在表parent有一条父记录。如果你用一致性读来读parent表，确实可以将要插入的child row的parent row，但是可以安全的插入吗？不，因为在你读parent表时，其它连接可能已经删除该记录。（一致性读是针对事务内而言的，对于数据库的状态，它应该叫做“不一致性读”）此时，就可以使用SELECT LOCK IN SHARE MODE，它会对读取的元组加S锁，从而防止其它连接删除或更新元组。另外，如果你想在查询的同时，进行更新操作，可以使用SELECT ... FOR UPDATE，它读取最新的数据，然后对读到的元组加X锁。此时，使用SELECT ... LOCK IN SHARE MODE不是一个好主意，因为此时如果有两个事务进行这样的操作，就会造成死锁。注：SELECT ... FOR UPDATE仅在自动提交关闭(即手动提交)时才会对元组加锁，而在自动提交时，符合条件的元组不会被加锁。

1.3.7、记录锁(record lok)、间隙锁(gap lock)和后码锁(next-key lock)InnoDB有以下几种行级锁：

(1)记录锁：对索引记录(index records)加锁，InnoDB行级锁是通过给索引的索引项加锁来实现的，而不是对记录实例本身加锁。如果表没有定义索引，InnoDB创建一个隐藏的聚簇索引，然后用它来实现记录加锁（关于索引与加锁之间的关系的详细介绍请看下一章）。

(2)间隙锁：对索引记录之间的区间，或者第一个索引记录之前的区间和最后一个索引之后的区间加锁。

(3)后码锁：对索引记录加记录锁，且对索引记录之前的区间加锁。默认情况下，InnoDB的事务工作在REPEATABLE READ的隔离级别，而且系统变量innodb_locks_unsafe_for_binlog为关闭状态。此时，InnoDB使用next-key锁进行查找和索引扫描，从而达到防止“幻像”的目的。Next-key锁是记录锁和间隙的结合体。当InnoDB查找或扫描表的索引时，对它遇到的索引记录加S锁或者X锁，所以，行级锁(row-level lock)实际上就是索引记录锁(index-record lock)；此外，它还对索引记录之前的区间加锁。也就是说，next-key锁是索引记录锁，外加索引记录之前的区间的间隙锁。如果一个连接对索引中的记录R持有S或X锁，其它的连接不能按照索引的顺序在R之前的区间插入一个索引记录。假设索引包含以下值：10, 11,13和20，则索引的next-key锁会覆盖以下区间(“(”表示不包含，“[”表示包含)：(negative infinity, 10](10, 11](11, 13](13, 20](20, positive infinity)对于最后一个区间，next-key锁将锁住索引最大值以上的区间，上界虚记录(“supremum” pseudo-record)的值比索引中的任何值都大，其实，上界不是一个真实的索引记录，所以，next-lock将对索引的最大值之后的区间加锁。间隙锁对查询唯一索引中的唯一值是没有必要的，例如，id列有唯一索引，则下面的查询仅对id=100的元组加索引记录锁(index-record lock)，而不管其它连接是否在之前的区间插入元组。SELECT * FROM child WHERE id = 100;如果id没有索引，或者非唯一索引，则语句会锁住之前的空间。

 

上例中，产生了幻像问题。如果将唯一查询变成范围查询，结果如下(接上例的索引)：

 

可以看到，session 2 的next-key使得在i=4之前的区间和之后的插入都被阻塞。另外，如果删除索引i_index，则结果如下：

 

另外，针对插入(INSERT)操作，只要多个事务不会在同一索引区间的同一个位置插入记录，它们就不用互相等待，这种情况可以称为插入意向间隙锁(insertion intention gap lock)。例如，索引记录的值为4和7，两个独立的事务分别插入5和6，尽管它们都持有4—7之间的间隙锁，但是它们不会相互阻塞。这可以提高食物的并发性。

 

间隙锁是可以显示关闭的，如果你将事务的隔离级别设为READ COMMITTED，或者打开innodb_locks_unsafe_for_binlog系统变量，间隙锁就会关闭。在这种情况下，查找或扫描索引仅会进行外键约束检查和重复键值检查。此外，READ COMMITTED隔离级别和关闭nodb_locks_unsafe_for_binlog还有另外一个负作用：MySQL会释放掉不匹配Where条件的记录锁。例如，对于UPDATE语句，InnoDB只能进行“半一致性(semi_consistent)读”，所以，它会返回最新提交事务所做改变，从而产生不可重复度和幻像问题。

1.3.8、使用next-key lock防止幻像问题例1-4展示了一个幻像问题。使用next-key锁的select语句可以解决幻像问题，但例1-4的之所以会产生总是在于唯一索引，使得select语句没有使用gap lock，而只使用了index-record lock。

1.4、存储引擎插件式存储引擎是MySQL最重要特性之一，也是最不同于其它DBMS的地方。MySQL支持很多存储引擎，以适用于不同的应用需求，常用的包括MyISAM、InnoDB、BDB、MEMORY、MERGE、NDB Cluster等。其中，BDB和NDB Cluster提供事务支持。MySQL默认的存储引擎为MyISAM，当然，创建表的时候可以指定其它的存储引擎，你可以在同一个数据库中对不同的表使用不同的存储引擎(这是非常强大而独特的特性)。可以通过SHOW TABLE STATUS命令查询表所使用的存储引擎，例如，查看mysql数据库的user表：

 

Name：表的名称；
Engine：表使用的存储引擎；
Row_format：记录的格式。MyISAM支持三种不同的存储格式：静态(固定长度)表(默认格式)、动态表及压缩表。静态表的字段都是固定长度的，例如CHAR和INTEGER；动态表的字段可以是变长的，例如，VARCHAR或者BLOB。
Rows：表中记录的数量。
Avg_row_length：记录的平均长度(字节数)；
Data_length：表中数据的全部字节数；
Max_data_length：表中数据最大的字节数；
Index_length：索引消耗的磁盘空间；
Data_free：对于MyISAM表，表示已经分配但还没有使用的空间；该空间包含以前删除的记录留下的空间，可以被INSERT操作重用。
Auto_increment：下一个自增的值。
Check_time：上次使用CHECK TABLE或myisamchk检查表的时间。

1.4.1、MyISAM
1.4.1.1、存储
MySQL的默认存储引擎，性能与功能的折中，包括全文索引(full-text index)、数据压缩，支持空间(GIS)数据，但是，不支持事务和行级锁。一般来说，MyISAM更适用于大量查询操作。如果你有大量的插入、删除操作，你应该选择InnoDB。
每个表包含3个文件：
(1).frm：表定义文件，对于其它存储引擎也一样。
(2).MYD文件：数据文件。
(3).MYI文件：索引文件。
可以在创建表时通过DATA DIRECTORY和INDEX DIRECTORY为数据文件和索引文件指定路径，它们可以位于不同目录。另外，MyISAM的存储格式是跨平台的，你可以将数据文件和索引文件从Intel平台拷贝到PPC或者SPARC平台。
5.0中，MyISAM的变长记录表默认处理256TB数据，使用6字节的指针来指向数据记录；而之前的版本使用默认的4字节指针，所以只能处理4GB数据。所有的版本都可以将指针增加到8字节指针，如果你想改变MyISAM表的指针的大小，可以通过设置MAX_ROWS和AVG_ROW_LENGTH来实现：
CREATE TABLE mytable (
a INTEGER NOT NULL PRIMARY KEY,
b CHAR(18) NOT NULL
) MAX_ROWS = 1000000000 AVG_ROW_LENGTH = 32;
上面的例子中，MySQL将至少可以存储32GB的数据。可以查看一下表的信息：

 

可以看到，Create_options列出了创建时的选项，而且该表的最大的数据量为91GB。你可以用ALTER TABLE来改变指针的大小，但是那会导致表和索引的重建，这会花费很长的时间。
1.4.1.2、MyISAM的特性
(1)锁与并发性：MyISAM只有表级锁，不支持行级锁。所以不适合于大量的写操作，但是它支持并发插入(concurrent inserts)，这是一个非常重要且有用的特性。
(2)自动修复：MySQL支持自动检查和修复MyISAM表。
(3)手动修复：你可以使用CHECK TABLE检查表的状态，并用REPAIR TABLE修复表。
(4)索引：你可以为BLOB和TEXT的前500个字符创建索引。而且，MyISAM还支持全文索引，但仅限于CHAR、VARCHAR、和TEXT列。
(5)延迟键写(Delayed key writes)：如果创建MyISAM表时指定DELAY_KEY_WRITE，MySQL在查询结束时，不会将改变的索引数据写入磁盘，而将修改保存在key buffer中。只有要改变缓存或者关闭表时，才会把索引数据刷入磁盘。
1.4.2、InnoDB
InnoDB是一个高性能的事务存储引擎，此外，BDB也支持事务处理(关于BDB，以前曾较为详细的阅读过其源码，以后有时间再讨论)，它有以下一些特点：

1.4.2.1、表空间
InnoDB存储表和索引有两种方式：
(1)共享表空间存储：这种方式下，表的定义位于.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path指定的表空间中。
(2)多表空间存储：表的定义仍位于.frm文件，但是，每个InnoDB表和它的索引在它自己的文件(.idb)中，每个表有它自己的表空间。
对那些想把特定表格移到分离物理磁盘的用户，或者那些希望快速恢复单个表的备份而无须打断其余InnoDB表的使用的用户，使用多表空间会是有益的。你可以往my.cnf的[mysqld]节添加下面行来允许多表空间：
[mysqld]
innodb_file_per_table
重启服务器之后，InnoDB存储每个新创建的表到表格所属于的数据库目录下它自己的文件tbl_name.ibd里。这类似于MyISAM存储引擎所做的，但MyISAM 把表分成数据文件tbl_name.MYD和索引文件tbl_name.MYI。对于InnoDB，数据和所以被一起存到.ibd文件。tbl_name.frm文件照旧依然被创建。
如果你从my.cnf文件删除innodb_file_per_table行，并重启服务器，InnoDB在共享的表空间文件里再次创建表。
innodb_file_per_table只影响表的创建。如果你用这个选项启动服务器，新表被用.ibd文件来创建，但是你仍旧能访问在共享表空间里的表。如果你删掉这个选项，新表在共享表空间内创建，但你仍旧可以访问任何用多表空间创建的表。
InnoDB总是需要共享表空间，.ibd文件对InnoDB不足以去运行，共享表空间包含熟悉的ibdata文件，InnoDB把内部数据词典和undo日志放在这个文件中。
1.4.2.2、外键约束
MySQL中，支持外键的存储引擎只有InnoDB，在创建外键时，要求被参照表必须有对应的索引，参照表在创建外键时也会自动创建对应的索引。
1.4.2.3、MVCC与后码锁(next-key locking)
InnoDB将MVCC机制与next-key lock结合起来，实现事务的各个隔离级别，这是非常用意思的。在nodb_locks_unsafe_for_binlog变量被设置或者事务的隔离级别不是SERIALIZABLE的情况下，InnoDB对于没有指定FOR UPDATE 或 LOCK IN SHARE MODE的INSERT INTO ... SELECT, UPDATE ... (SELECT), 和CREATE TABLE ... SELECT语句使用一致性读(参照前面)，在这种情况下，查询语句不会对表中的元组加锁。否则，InnoDB将使用锁。

MySQL索引与优化

索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点。考虑如下情况，假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引，则只需要进行log100(10^6)=3次页面读取，最坏情况下耗时30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引。进入正题：

1、选择索引的数据类型

MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则：

(1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
(2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。
(3)尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。
1.1、选择标识符
选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型，而且应该考虑MySQL是怎样进行运算和比较的。一旦选定数据类型，应该保证所有相关的表都使用相同的数据类型。
(1) 整型：通常是作为标识符的最好选择，因为可以更快的处理，而且可以设置为AUTO_INCREMENT。

(2) 字符串：尽量避免使用字符串作为标识符，它们消耗更好的空间，处理起来也较慢。而且，通常来说，字符串都是随机的，所以它们在索引中的位置也是随机的，这会导致页面分裂、随机访问磁盘，聚簇索引分裂（对于使用聚簇索引的存储引擎）。

2、索引入门对于任何DBMS，索引都是进行优化的最主要的因素。

对于少量的数据，没有合适的索引影响不是很大，但是，当随着数据量的增加，性能会急剧下降。如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。例如：假设存在组合索引it1c1c2(c1,c2)，查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是，查询语句select * from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必须出现c1等于某值。

2.1、索引的类型索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。

2.1.1、B-Tree索引假设有如下一个表：

 

其索引包含表中每一行的last_name、first_name和dob列。其结构大致如下：

 

索引存储的值按索引列中的顺序排列。可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询，当然，如果想使用索引，你必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。
(1)匹配全值(Match the full value)：对索引中的所有列都指定具体的值。例如，上图中索引可以帮助你查找出生于1960-01-01的Cuba Allen。
(2)匹配最左前缀(Match a leftmost prefix)：你可以利用索引查找last name为Allen的人，仅仅使用索引中的第1列。
(3)匹配列前缀(Match a column prefix)：例如，你可以利用索引查找last name以J开始的人，这仅仅使用索引中的第1列。
(4)匹配值的范围查询(Match a range of values)：可以利用索引查找last name在Allen和Barrymore之间的人，仅仅使用索引中第1列。
(5)匹配部分精确而其它部分进行范围匹配(Match one part exactly and match a range on another part)：可以利用索引查找last name为Allen，而first name以字母K开始的人。
(6)仅对索引进行查询(Index-only queries)：如果查询的列都位于索引中，则不需要读取元组的值。
由于B-树中的节点都是顺序存储的，所以可以利用索引进行查找(找某些值)，也可以对查询结果进行ORDER BY。当然，使用B-tree索引有以下一些限制：

1. 查询必须从索引的最左边的列开始。关于这点已经提了很多遍了。例如你不能利用索引查找在某一天出生的人。
2. 不能跳过某一索引列。例如，你不能利用索引查找last name为Smith且出生于某一天的人。
3. 存储引擎不能使用索引中范围条件右边的列。例如，如果你的查询语句为WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'，则该查询只会使用索引中的前两列，因为LIKE是范围查询。

2.1.2、Hash索引
MySQL中，只有Memory存储引擎显示支持hash索引，是Memory表的默认索引类型，尽管Memory表也可以使用B-Tree索引。Memory存储引擎支持非唯一hash索引，这在数据库领域是罕见的，如果多个值有相同的hash code，索引把它们的行指针用链表保存到同一个hash表项中。
假设创建如下一个表：

CREATE TABLE testhash (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
KEY USING HASH(fname)
) ENGINE=MEMORY;

包含的数据如下：

 

假设索引使用hash函数f( )，如下：

 

此时，索引的结构大概如下：

 

Slots是有序的，但是记录不是有序的。当你执行mysql> SELECT lname FROM testhash WHERE fname='Peter';MySQL会计算’Peter’的hash值，然后通过它来查询索引的行指针。因为f('Peter') = 8784，MySQL会在索引中查找8784，得到指向记录3的指针。因为索引自己仅仅存储很短的值，所以，索引非常紧凑。Hash值不取决于列的数据类型，一个TINYINT列的索引与一个长字符串列的索引一样大。 Hash索引有以下一些限制：

(1)由于索引仅包含hash code和记录指针，所以，MySQL不能通过使用索引避免读取记录。但是访问内存中的记录是非常迅速的，不会对性造成太大的影响。

(2)不能使用hash索引排序。

(3)Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的。

(4)Hash索引只支持等值比较，例如使用=，IN( )和<=>。对于WHERE price>100并不能加速查询。

2.1.3、空间(R-Tree)索引MyISAM支持空间索引，主要用于地理空间数据类型，例如GEOMETRY。

2.1.4、全文(Full-text)索引全文索引是MyISAM的一个特殊索引类型，主要用于全文检索。

3、高性能的索引策略

3.1、聚簇索引(Clustered Indexes)聚簇索引保证关键字的值相近的元组存储的物理位置也相同（所以字符串类型不宜建立聚簇索引，特别是随机字符串，会使得系统进行大量的移动操作），且一个表只能有一个聚簇索引。因为由存储引擎实现索引，所以，并不是所有的引擎都支持聚簇索引。目前，只有solidDB和InnoDB支持。聚簇索引的结构大致如下：

 

注：叶子页面包含完整的元组，而内节点页面仅包含索引的列(索引的列为整型)。一些DBMS允许用户指定聚簇索引，但是MySQL的存储引擎到目前为止都不支持。InnoDB对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。一般来说，DBMS都会以聚簇索引的形式来存储实际的数据，它是其它二级索引的基础。

3.1.1、InnoDB和MyISAM的数据布局的比较为了更加理解聚簇索引和非聚簇索引，或者primary索引和second索引(MyISAM不支持聚簇索引)，来比较一下InnoDB和MyISAM的数据布局，对于如下表：

 

假设主键的值位于1---10,000之间，且按随机顺序插入，然后用OPTIMIZE TABLE进行优化。col2随机赋予1---100之间的值，所以会存在许多重复的值。
(1) MyISAM的数据布局
其布局十分简单，MyISAM按照插入的顺序在磁盘上存储数据，如下：

 

注：左边为行号(row number)，从0开始。因为元组的大小固定，所以MyISAM可以很容易的从表的开始位置找到某一字节的位置。
据所建立的primary key的索引结构大致如下：

 

注：MyISAM不支持聚簇索引，索引中每一个叶子节点仅仅包含行号(row number)，且叶子节点按照col1的顺序存储。
来看看col2的索引结构：

 

实际上，在MyISAM中，primary key和其它索引没有什么区别。Primary key仅仅只是一个叫做PRIMARY的唯一，非空的索引而已。
(2) InnoDB的数据布局
InnoDB按聚簇索引的形式存储数据，所以它的数据布局有着很大的不同。它存储表的结构大致如下：

 

注：聚簇索引中的每个叶子节点包含primary key的值，事务ID和回滚指针(rollback pointer)——用于事务和MVCC，和余下的列(如col2)。
相对于MyISAM，二级索引与聚簇索引有很大的不同。InnoDB的二级索引的叶子包含primary key的值，而不是行指针(row pointers)，这减小了移动数据或者数据页面分裂时维护二级索引的开销，因为InnoDB不需要更新索引的行指针。其结构大致如下：

 

聚簇索引和非聚簇索引表的对比：

 

3.1.2、按primary key的顺序插入行(InnoDB)

如果你用InnoDB，而且不需要特殊的聚簇索引，一个好的做法就是使用代理主键(surrogate key)——独立于你的应用中的数据。最简单的做法就是使用一个AUTO_INCREMENT的列，这会保证记录按照顺序插入，而且能提高使用primary key进行连接的查询的性能。应该尽量避免随机的聚簇主键，例如，字符串主键就是一个不好的选择，它使得插入操作变得随机。

3.2、覆盖索引(Covering Indexes)如果索引包含满足查询的所有数据，就称为覆盖索引。覆盖索引是一种非常强大的工具，能大大提高查询性能。只需要读取索引而不用读取数据有以下一些优点：

(1)索引项通常比记录要小，所以MySQL访问更少的数据；

(2)索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O；

(3)大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。

(4)覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。覆盖索引不能是任何索引，只有B-TREE索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同，并不是所有存储引擎都支持覆盖索引(Memory和Falcon就不支持)。对于索引覆盖查询(index-covered query)，使用EXPLAIN时，可以在Extra一列中看到“Using index”。例如，在sakila的inventory表中，有一个组合索引(store_id,film_id)，对于只需要访问这两列的查询，MySQL就可以使用索引，如下：

 

在大多数引擎中，只有当查询语句所访问的列是索引的一部分时，索引才会覆盖。但是，InnoDB不限于此，InnoDB的二级索引在叶子节点中存储了primary key的值。因此，sakila.actor表使用InnoDB，而且对于是last_name上有索引，所以，索引能覆盖那些访问actor_id的查询，如：

 

3.3、利用索引进行排序
MySQL中，有两种方式生成有序结果集：一是使用filesort，二是按索引顺序扫描。利用索引进行排序操作是非常快的，而且可以利用同一索引同时进行查找和排序操作。当索引的顺序与ORDER BY中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时，可以使用索引来排序。如果查询是连接多个表，仅当ORDER BY中的所有列都是第一个表的列时才会使用索引。其它情况都会使用filesort。

 

 

当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序）。对于filesort，MySQL有两种排序算法。

(1)两边扫描算法(Two passes)实现方式是先将需要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存（通过参数sort_buffer_size设定）中进行排序，完成排序之后再次通过行指针信息取出所需的Columns。注：该算法是4.1之前采用的算法，它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。另一方面，内存开销较小。

(2) 一次扫描算法(single pass)该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出。注：从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出“Using filesort”；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出“Using temporary;Using filesort”。

3.4、索引与加锁索引对于InnoDB非常重要，因为它可以让查询锁更少的元组。这点十分重要，因为MySQL 5.0中，InnoDB直到事务提交时才会解锁。有两个方面的原因：首先，即使InnoDB智能锁的开销非常高效，内存开销也较小，但不管怎么样，还是存在开销。其次，对不需要的元组的加锁，会增加锁的开销，降低并发性。InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。但是，只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做（即达不到过滤的目的），MySQL服务器只能对InnoDB返回的数据进行WHERE操作，此时，已经无法避免对那些元组加锁了：InnoDB已经锁住那些元组，服务器无法解锁了。来看个例子：

 

该查询仅仅返回2---3的数据，实际已经对1---3的数据加上排它锁了。InnoDB锁住元组1是因为MySQL的查询计划仅使用索引进行范围查询（而没有进行过滤操作，WHERE中第二个条件已经无法使用索引了）：

 

表明存储引擎从索引的起始处开始，获取所有的行，直到actor_id<4为假，服务器无法告诉InnoDB去掉元组1。
为了证明row 1已经被锁住，我们另外建一个连接，执行如下操作：

 

该查询会被挂起，直到第一个连接的事务提交释放锁时，才会执行（这种行为对于基于语句的复制(statement-based replication)是必要的）。
如上所示，当使用索引时，InnoDB会锁住它不需要的元组。更糟糕的是，如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要。