索引可以包含一个或多个列的值。若索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效的使用索引的最左前缀列。

　　在MySQL中，索引是在存储引擎层而不是服务层实现的，所以并没有统一的索引标准。不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

　　B-Tree索引

　　　　若没有指定特定类型的索引，则一般都是指的是B-Tree索引，它使用B-Tree数据结构来存储数据。InnoDB使用的是B+Tree。MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。MyISAM索引通过数据的物理位置引用给索引的行，InnoDB根据主键引用被索引的行。

　　　　B-Tree意味着所有的值都是按顺序存储的，并且每个叶子叶到根的距离相同。

　　　　B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向字节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层字节点，这些指针实际上定义了字节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。叶子节点的指针指向是被索引的数据。树的深度和表的大小直接相关。

　　　　索引对多个值进行排序的依据是根据CREATE TABLE语句中定义索引时列的顺序。　　　

　　　　B-Tree索引适用于全健值，健值范围货键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。

　　　　全值匹配：全值匹配指的是和索引中的所有列进行匹配

　　　　匹配最左前缀：只使用索引的第一列

　　　　匹配列前缀：可以匹配某一列的值的开头部分　

　　　　匹配值范围：查找某一个范围内

　　　　精确匹配某一列并范围匹配另一列：若精确匹配第一列，第二列范围匹配

　　　　只访问索引的查询：查询只需要访问索引而无须访问数据行

　　　　B-Tree的限制

　　　　　　若不是按照索引的最左列开始查找，则无法使用索引

　　　　　　不能跳过索引的列

　　　　　　若查询中有某个列的范围查询，则其右边所有的列都无法使用索引优化查询。

　　B+Tree特点

　　　　所有关键字都出现在叶子节点的链表中

　　　　不可能在非叶子节点命中

　　　　非叶子节点相当于是叶子节点的索引，叶子节点相当于存储数据的数据层

　　　　适合文件索引系统

　　哈希索引(hash index)

　　　　哈希索引基于哈希表的实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎会对所有的索引列计算一个哈希码。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

　　　　在MySQL中，只有Memory引擎显式支持哈希索引，并是Memory引擎表的默认索引类型。Memory引擎是支持非唯一哈希索引，若多个列的哈希值相同，索引会以链表的方式存放多个记录指针同一个哈希条目中。每个槽存放的计算出的哈希码事顺序的，因此数据不是顺序的。

　　　　MySQL会根据查询数据计算哈希值，并使用该值寻找对应的记录指针，然后在槽对应的value中找到指向第几行数据的指针，最后一步比较该行的值是否事查询数据，以确保是要查找的行。

　　　　索引自身只存储的对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度十分快。但是也有一些限制：

　　　　　　哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过访问内存中的行的速度很快，所以大部分情况下这点对性能影响并不明显

　　　　　　哈希索引数据并不是按照索引值顺序存储的，所以无法用于排序

　　　　　　哈希索引不支持部分索引列匹配索引，因为哈希索引始终是使用索引列的全部内容来计算哈希值的

　　　　　　哈希索引只支持等值比较查询，包括=，IN(),<=>。不支持任何范围查询

　　　　　　访问哈希索引的数据非常快。但在有很多哈希冲突时例外。当出现哈希冲突时，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行

　　　　　　若哈希冲突很多，一些索引维护操作的代价也很高。

　　　　InnoDB引擎有个特殊的功能叫做“自适应哈希索引(adaptive hash index)”，当InnoDB注意到某些索引只被使用的非常频繁时，它会在内存中基于B-Tree之外上再创建一个哈希索引。这是一个完全自动的，内部的行为。

　　创建自定义哈希索引：在B-Tree基础上创建一个伪哈希索引。它使用哈希值而不是健本身进行索引查找。需要做的只是在查询的WHERE字句中手动指定使用哈希函数。这样的缺点是需要维护哈希值，可以手动维护，也可以用触发器实现。使用这种方式不要用SHA1()和MD5()作为哈希函数，因为这两个函数计算的哈希值是非常长的字符串，会浪费大量空间。若数据表非常大，CRC32()会出现大量的哈希冲突，此时可以自己实现一个64位的哈希函数。也可以使用MD5()函数返回值的一部分作为自定义哈希函数。

　　CREATE TABLE pseudohash(

　　　　id int unsigned NOT NULL auto_increament,

　　　　url varchar(255) NOT NULL,

　　　　url_crc int unsigned NOT NULL DEFAULT 0,

　　　　primary key(id)

　　);

　　-- create trigger　　

　　DELIMETER //

　　CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN

　　SET NEW.url_crc = crc32(New.url);

　　END;

　　//

　　CREATE TIGGER pseudohash_crc_update BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN

　　SET NEW.url_crc=crc32(NEW.url);

　　END;

　　//

　　DELIMETER ;

　　当使用哈希索引进行查询时，必须在WHERE子句中包含常量值。　　

　　空间数据索引(R-Tree)

　　　　MyISAM表支持空间索引，可以用作地理数据存储。R-Tree索引无需前缀查询。空间索引会从过所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据。

　　全文索引

　　　　全文索引是一种特殊类型的索引，它查找的是文本中的关键字，而不是比较索引中的值。全文索引适用于MATCH AGAINST操作，而不是WHERE条件操作。

　　索引的优点

　　　　最常见的B-Tree索引，按照顺序存储数据，所以MySQL可以用来做ORDER BY和GROUP BY操作。索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。因此，索引有三大优点：

　　　　　　索引大大减少了服务器需要扫描的数据量

　　　　　　索引可以帮助服务器避免排序和临时表

　　　　　　索引可以将随机I/O变成顺序I/O。

　　一个索引是否适合某个查询的三星系统：索引将相关记录放到一起就获得一星；若索引中的数据顺序和查找中的排序一致则获得两星；若索引中的列包含了查询中需要的全部列则获得三星。

　　高性能的索引策略

　　　　独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。索引应该是独立的列。始终将索引单独放在比较符号的一侧。

　　前缀索引

　　　　通常可以索引开始的部分字符，这样可以大大节约索引空间，但也会降低索引的选择性。索引的选择性是指不重复的索引值和数据表的比值。索引的选择性越高则查询效率越高。对于BLOB，TEXT或很长的VARCHAR类型的列，必须使用前缀索引。为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。

　　　　-- 选取前十个出现最多的字段

　　　　SELECT COUNT(index_column) AS cnt, index_column from table GROUP BY index_column ORDER BY cnt DESC LIMIT 10;

　　　　-- 选取列值的前缀直到这个前缀的选择性接近于完整列的选择性

　　　　SELECT COUNT(index_column) AS cnt, LEFT(index_column, number) AS pref FROM TABLE GROUP BY pref ORDER BY index_column DESC LIMIT 10;

　　　　另一个计算前缀长度的方法是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性

　　　　-- 计算完整列的选择性

　　　　SELECT COUNT(DISTINCT index_column)/COUNT(*) FROM table;

　　　　-- 在同一个查询中计算不同前缀长度的选择性

　　　　SELECT COUNT(DISTINCT LEFT(index_column, 3))/COUNT(*) AS sel3,

　　　　　　COUNT(DISTINCT LEFT(index_column, 4))/COUNT(*) AS sel4,

　　　　　　COUNT(DISTINCT LEFT(index_column, 5))/COUNT(*) AS sel5,

　　　　　　COUNT(DISTINCT LEFT(index_column, 6))/COUNT(*) AS sel6,

　　　　　　COUNT(DISTINCT LEFT(index_colum, 7))/COUNT(*) AS sel7

　　　　FROM table;

　　　　当数据分布不均匀时，需要考虑最坏情况下的选择性。

　　　　前缀索引的缺点是无法做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

　　多列索引

　　　　索引合并(index merge)：查询时能同时使用这两列单列索引进行扫描，并将结果进行合并。这种算法有三变种：OR条件的联合(union)，AND条件的相交(intersection)合组和前两种情况的联合及相交。　　　　

　　　　索引合并策略有时是一种优化的结果，但实际上更多时候说明了表的索引建得很糟糕：

　　　　　　当出现服务器对多个索引做相交操作时，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。

　　　　　　当服务器需要对多个索引做联合操作时，通常需要耗费大量CPU和内存资源在算法的缓存，排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。

　　　　　　优化器不会把这些计算到“查询成本”中，优化器只关心随机页面读取。这使得查询的成本会被低估。这样可能会将查询写成UNION的方式会更好。

　　　　SELECT film_id, actor_id FROM film_actor WHERE actor_id = 1 OR film_id = 1;

　　　　====>

　　　　SELECT film_id, actor_id FROM film_actor WHERE actor_id = 1 UNION ALL SELECT film_id, actor_id FROM film_actor WHERE film_id = 1 AND actor_id <>1

　　选择合适的索引列顺序(B-Tree)

　　　　在一个多列B-Tree索引中，索引列的顺序意味着索引首先是按照最左列进行排序。

　　　　选择索引的列的顺序有个经验法则：将选择性最高的列放在前面通常是好的，此时适用于优化WHERE条件的查找。然而性能不只依赖于所有索引列的选择性，也和查询条件的具体值有关，也就是和值的分布有关，可能根据运行频率最高的查询来调整索引列的顺序。

　　　　-- 针对具体查询

　　　　SELECT * FROM payment where staff_id = 2 AND customer_id = 584;

　　　　-- 查询条件分支对应的数据基数有多大，根据查询数据相对较少的列作为最左索引列

　　　　SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM payment;  

　　　　-- 针对整体查询，选择选择性高的列作为最左索引列

　　　　SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity, COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity, COUNT(*) FROM payment;

　　聚簇索引

　　　　聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDb的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子叶中。“聚簇”表示数据行和相邻的键值紧凑的存储在一起。一些数据库服务器允许选择哪个索引作为聚簇索引，但没有任何一个MySQL内建的存储引擎可以支持这一点。InnoDB通过主键聚集数据。若没有定义主键，InnoDB会选择一个唯一的非空索引代替。若没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

　　　　聚集的数据的优点：

　　　　　　可以把相关数据保存在一起

　　　　　　数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快

　　　　　　使用覆盖索引扫描的查询可以直接使用页节点中的主键值

　　　　聚集索引的缺点：

　　　　　　聚簇数据最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没有那么重要，聚簇索引就没有优势了

　　　　　　插入速度严重依赖于插入顺序，若按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，最好在加载完成之后使用OPTIMIZE TABLE命令重新组织下表

　　　　　　更新聚簇索引列的代价很高，因为强制InnoDB将每个被更新的行移动到新的位置

　　　　　　基于聚簇索引的表在插入新行，或主键被更新导致需要移动行的时候，可能面临页分裂问题。页分裂会导致表占用更多的磁盘空间

　　　　　　聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或由于页分裂导致数据存储不连续的时候

　　　　　　二级索引可能比想象大，因为二级索引的叶子节点包含了引用行的主键列

　　　　　　二级索引访问需要两次索引查找，而不是一次。二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。这意味着通过二级索引查找行，存储需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。

　　InnoDB和MyISAM数据分布对比

　　　　MyISAM的数据分布：MyISAM按照数据插入的顺序存储在磁盘上。

　　　　InnoDB的数据分布：聚簇索引就是表，所以不需要像MyISAM需要独立的行存储。聚簇索引的每个叶子节点都包含了主键值，事务ID，用于事务和MVCC的回滚指针以及所有的剩余列。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。InnoDB二级索引和叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无须更新二级索引中的这个“指针”。

　　在InnoDB表中按主键顺序插入行

　　　　若正在使用InnoDB表并且没有什么数据需要聚集，那么定义一个代理键(surrogate key)作为主键，这种主键的数据应该和应用无关。最简单的方式就是使用AUTO_INCREMENT自增列。最好避免随机的聚簇索引，特别是对于I／O密集型的应用。若主键值时顺序的，InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时(InnoDB默认的最大填充因子时页大小的15/16，留出部分空间用于以后修改)，下一条记录就会写入到新的页中，一旦数据按照这种方式加载，主键页就会近似于被顺序的记录填满。

　　　　使用随机主键的缺点：

　　　　　　写入目标页可能已经刷新到磁盘上并从缓存中移除，或者时还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机I/O。

　　　　　　因为写入时乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。

　　　　　　由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

　　　　主键在并发时可能导致间隙锁竞争，也可能导致AUTO_INCREMENT锁机制。

　　覆盖索引

　　　　若一个索引包含所有需要查询的字段的值，我们称之为覆盖索引。

　　　　覆盖索引优点：　　

　　　　　　索引条目通常远小于数据行大小，所以如果只需要读取索引，那么MySQL就会极大地减少数据访问量

　　　　　　索引时按照列值顺序存储的，所以对于I/O密集型的范围查询也会比随机从磁盘读取每一行数据的I/O要小得多。

　　　　　　一些存储引擎入MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用，这可能导致严重的性能问题。

　　　　　　由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，因此若二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

　　　　覆盖索引必须要存储索引列的值，而哈希索引，空间索引和全文索引等不存储索引列的值。

　　　　有两种情况下不能使用覆盖索引：查询从表中选择了所有的列；MySQL不能在索引中执行LIKE操作。这是底层存储引擎API限制。

　　　　SELECT * FROM products WHERE actor=‘SEAN CARREY‘ AND title like ‘%APOLLO‘;

　　　　===>

　　　　-- 使用延迟关联(deferred join)，因为延迟了对列的访问，在查询的第一阶段MySQL可以使用覆盖索引

　　　　SELECT * FROM products JOIN (SELECT prod_id FROM products WHERE actor=‘SEAN CARRERY‘ AND tiltle LIKE ‘%APOLLO%‘) AS t1 ON(t1.prod_id = products.prod_id);

　　使用索引扫描来做排序

　　　　MySQL有两种方式可以生成有序的结果：通过排序操作或按索引顺序扫描。若EXPLAIN出来的type列的值为index，则说明MySQL使用了索引来做排序。如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条记录就都回表查询一次对应的行。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I／O密集型的工作负载时。

　　　　当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的顺序方向都一样时，MySQL才能够使用索引来对结果做排序。若查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找类型的限制是一样的：需要满足索引的最左前缀的要求。

　　　　当前导列为常量的时，ORDER BY子句不可以满足索引的最左前缀的要求。若WHERE子句或JOIN子句中对最左前列指定了常量，就可以弥补索引的不足。

　　　　以下几种方式不能使用索引做排序的查询：

　　　　　　查询使用了两种不同的排序方式，但是索引列都是正序排序的

　　　　　　查询的ORDER BY子句中引用了一个不再索引中的列

　　　　　　查询的WHERE和ORDER BY中的列无法组合成索引的最左前缀

　　　　　　查询在索引列的第一列上范围条件，所以MySQL无法使用索引的其余列

　　　　　　在某个索引上使用多个等于条件。对于排序来说这也是一种范围查询

　　　　使用索引做排序的一个最重要的用法是当查询同时有ORDER BY和LIMIT子句时。

　　压缩(前缀压缩)索引

　　　　MyISAM使用前缀压缩来减少索引的大小。MyISAM压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。压缩快使用更少的空间，代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值，所以MyISAM查找时无法在索引块使用二分查找而只能从头开始扫描。

　　　　可以在CREATE TABLE语句中指定PACK_KEYS参数来控制索引压缩的方式

　　冗余和重复索引

　　　　重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。

　　　　冗余索引通常发生在为表添加新索引的时候，此时应该尽量扩展已有的索引而不是创建新的索引。

　　索引和锁

　　　　索引可以让查询锁定更少的行。InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行才有效。InnoDB在二级索引上使用共享锁(读锁)，但访问主键索引需要排他锁(写锁)。这消除了使用覆盖索引的可能性，并并且使得SELECT FOR UPDATE比LOCK IN SHARE MODE或非锁定查询要慢很多　　　　

　　避免多个范围条件：当有两个氛围条件并且这两个列都为索引列时，MySQL只能使用其中一列的索引

　　维护索引和表

　　　　维护表主要有三个目的：找到并修复损坏的表；维护准确的索引统计信息；减少碎片

　　　　可以使用CHECK TABLE来检查是否发生了表损坏。用REPAIR TABLE命令来修复索引损坏的表。若存储引擎不支持该命令，可以通过一个不做任何操作no-op的ALTER TABLE操作来重建表。当表中数据损坏时，可以通过设置innodb_force_recovery参数进入InnoDB的强制恢复模式来修复数据。

　　更新索引统计信息

　　　　MySQL的查询优化器通过两个API类来了解存储引擎的索引值的分布信息。第一个是records_in_range()，通过向存储引擎传入两个边界值获取在这个范围大概有多少记录。第二个是info()，该接口返回各种类型的数据，包括索引的基数。

　　　　如果存储引擎向优化器提供的扫描行数信息是不准确的数据，或执行计划本身太复杂以致无法准确地获取各个阶段匹配的行数，那么优化器会使用索引统计信息来估算扫描行数。MySQL优化器使用的是基于基本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行。若表没有统计信息或信息不准确，优化器可能会做出错误的决定。可以通过ANALYZE TABLE来重新生成统计信息解决这个问题。

　　　　每种存储引擎实现索引统计信息的方式不同，所以需要ANALYZE TABLE的频率也因此不同，每次运行的成本也不同：

　　　　　　Memory引擎根本不存储索引统计信息

　　　　　　MyISAM嫁给你索引统计信息存储在磁盘中，ANALYZE TABLE需要进行一次全索引扫描来计算索引基数

　　　　　　InnoDB通过随机的索引访问进行评估并将其存储在内存中国年

　　　　可以使用SHOW INDEX FROM命令来查看索引的基数

　　　　　　SHOW INDEX FROM tablename; -- 显式的结果中索引列的基数(Cardinality显示了存储引擎估算索引列有多少个不同的取值)

　　　　在5.0以上版本中，可以通过INFORMATION_SCHEMA，STATISTICS表查询这些信息。

　　　　InnoDB引擎通过抽样的方式来计算统计信息，首先随机地读取少量的索引页面，然后以此为样本计算索引的统计信息。可以通过innodb_stats_sample_pages来设置样本页的数量。InnoDB会在表首次打开或执行ANALYZE TABLE抑或表的大小发生非常大的变化时会计算索引的统计信息。

　　减少索引和数据碎片

　　　　B-Tree需要随机磁盘上访问才能定位到叶子页，若叶子页在物理分布上是顺序且紧密的，那么查询性能会更好，否则对于范围查询，索引覆盖等操作来说，速度可能会减低很多倍。

　　　　表的存储有三种类型的数据碎片：

　　　　　　行碎片(Row fragmentation)：数据行被存储为多个地方的多个片段中。即使查询只从索引中访问一行记录，行碎片也会导致性能下降

　　　　　　行间碎片(Intra-row fragmentation)：行间碎片是指逻辑上顺序的页，或行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响

　　　　　　剩余空间碎片(Free space fragmentation)：剩余空间碎片是指数据页中有大量的空余空间。

　　　　对于MyISAM表，这三类碎片化都可能发生。但在InnoDB中不会出现短小的碎片，InnoDB会移动短小的行并重写到一个片段中。

　　　　可以通过OPTIMIZE TABLE或导出再导入的方式来重新整理数据。对于MyISAM，可以通过排序算法重建索引的方式来消除碎片。InnoDB新增了“在线”添加和删除索引的功能，可以通过先删除，然后再重新建索引的方式来消除索引的碎片化。对于不支持OPTIMIZE TABLE的存储引擎，可以通过ALTER TABLE操作来重建表，将表的存储引擎改为当前的引擎即可。

创建高性能的索引