MySQL之索引

要理解 mysql中索引是如何工作，最简单的方法就是看看一本书的"索引"部分（即目录）。然后通过索引找到指定的页码。

在mysql中，存储引擎用类似的方法使用索引，首先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。

在mysql中，索引是在存储引擎层而不是服务器层实现的。

 

索引的优点：　　
　　索引可以让服务器快速定位到表的指定位置，但这并不是索引的唯一作用，到目前为止可以看到，根据索引的数据结构不同，
　　索引页有一些附加作用。

最常见的b-tree索引，按照顺序存储数据，所以mysql可以用来做order by和group by操作。因为数据是有序的，所以b-tree也就会将相关的列都存储在一起。
最后因为索引中存储了实际的值列值，所以某些查询只使用索引就能够完成全部查询。总而言之，有三个优点：
　　1.索引大打减少了服务器需要扫描的数据量。
　　2.索引可以帮助服务器避免排序和临时表。
　　3.所以可以将随机I/O变成顺序I/O。

索引的类型:

B-Tree索引：

　　b-tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。结构如下图：

　　

　　b_tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，

　　取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据

　　这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些

　　指针实际上定义了子节点页中的上限和下限。

 

b-tree索引对如下类型的查询有效

1. 全值匹配
    全值匹配指的是和索引中所有列进行匹配，例如前面提到的索引可用于查找姓名为Cuba Allen，出生于 1960-01-01的人
2. 匹配最左前缀
    前面提到的索引可用于查找所有姓为allen的人，即只使用索引的第一列
3. 匹配列前缀
    也可以只匹配某一列值得开头部分。例如前面提到的索引可用于查找所有以J开头姓的人。即只使用索引的第一列
4. 匹配范围值
    查找姓在allen和barry之间的人。即只使用索引的第一列
5. 精确某一列，并范围匹配另一列
    前面提到的索引可用于查找姓为allen，并且名字是字母K开头的人；
    即第一列last_name 全匹配，
    第二列first_name 范围匹配。
6. 只访问索引的查询。
    b_tree 通常可以支持"只访问索引的查询"，即查询只需要访问索引，而无须访问数据行。
    
相反，b-tree索引的限制有：（这里说的是多列索引）
　　1.如果不是按照索引的最左列开始查找，则无法使用索引。
　　2.不能跳过索引中的列。
　　3.如果查询中有某个列的范围查询，则其右侧的列都无法使用索引优化查找。

哈希索引：
　　
　　哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。
　　对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码。哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。
　　哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
　　在mysql中只有Memory引擎显式支持哈希索引，这也是Memory引擎的默认索引类型。Memory引擎同时也支持B-tree索引。
　　值得一提的是，memory引擎是支持非唯一哈希索引的，这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同，索引会以链表的方式存放
　　多个记录指针到同一个哈希条目中。

哈希索引的限制：
　　哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过访问内存中的行速度很快，所以大部分情况下这一点对
　　性能的影响并不大。
　　
　　哈希索引数据并不会是按照索引值顺序存储的，所以无法用于排序。
　　
　　哈希所以也不支持部分索引列的匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如：
　　在数据列（a，b）建议哈希索引，如果查询只有数据列a，则无法使用该索引。

　　哈希索引只支持等值比较查询，包括 =， in（）， <=> (注意<> 和 <=>是不同的操作)，也不支持任何范围查询，例如where price > 100

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

　　如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低（哈希冲突很多）的列上建议哈希索引，那么当从表中删除一行时，
　　存储引擎需要遍历对应哈希值得链表中的每一行，找到并删除对应行的引用，冲突越多代价越大。

因为这些限制，哈希索引只使用与某些特定的场合。而一旦适合哈希索引，则它带来的性能提升将非常显著。


空间数据索引: 欲知后事如何

全文索引: 且听下回分解



高性能索引：
1.独立的列
　　　　指索引列不能是表达式的一部分，也不呢个是函数的参数。 
　　　　例：　select actor_id from actor where actor_id + 1 = 5;
　　　　这将导致无法使用actor_id的索引，所以我们应该养成简化where条件的习惯，始终将索引列单独放在比较符号的一侧。
2. 前缀索引和索引选择性
　　　　

　　索引选择性

　　# 索引的选择性 是指：不重复的索引值和数据表的记录总数(#T)的比值，范围从 1/#T 到 1 之间

　　# 索引的选择性越高则查询效率越高，因为选择性越高的索引可用让mysql查找时过滤更多的行

　　# 唯一索引的选择性为1，这是最好的索引选择性，性能也是最好的。

　　前缀索引
　　　　是一种使索引更小，更快的有效方法。
　　　　mysql> alter table sakila.city_demo add key(city(7))
　　　　它也有缺点，mysql无法使用前缀索引做group by 和 order by，也无法使用前缀索引做覆盖索引。

3.多列索引
　　　　　"索引合并"：
　　　　　　　索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建的很糟糕。
　　　　　　　　1.当出现服务器对多个索引做相交操作时，（通常有多个AND条件）通常意味着需要一个包含所有相关列的多列索引，
　　　　　　　　而不是多个独立的单列索引。
　　　　　　　　2.当服务器需要对多个索引做联合操作时（通常是多个or条件）通常需要耗费大量的CPU和内存资源在算法的缓存，排序和合并操作上。
　　　　　　　　特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
　　　　　　　　3.更重要的是，优化器不会把这些计算把“查询成本”中，优化器只关系随机页面读取。

　　　　　　　　　

4.选择合适的索引列顺序。
　　　　　　我们遇到的最容易引起困惑的问题就是索引列的顺序。正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要。
　　　　　　在一个多列b-tree中，索引的顺序意味着首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列
　　　　　　顺序的 order by，group by和distinct等子句的需求。
　　　　　　
　　　　　　对于如何选择索引列的顺序有一个经验原则：　将选择性最高的列放在索引最前列。
　　　　　　这个建议在某些场景下可能有帮助，但通常不如避免随机IO和排序那么重要，考虑问题需要更全面。
　　　　　　
　　　　　　当不需要考虑排序和分组时，将选择性高的列放在前面通常是很好的。这时候索引的作用只是用于优化where条件的查找。
　　　　　　在这种情况下，这样设计的索引确实能够最快的过滤出需要的行，对于在where子句中只使用了索引部分前缀列的查询来说选择性也更高。】
　　　　　　然而，性能不只是依赖所有索引列的选择性（整体基数），也和查询条件的具体值有关，就是和值得分布有关。这和前面介绍的选择前缀的长度需要考虑的地方一样，
　　　　　　可能需要根据那些运行频率最高的查询来调整索引列的顺序。

　　　　　　尽管关于选择性和基数的经验法则值得去研究和分析，但一定要记住别忘了where子句的排序，分组和范围条件等其他因素。这些因素可能对查询的性能造成非常大的影响。

5.聚簇索引
　

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖与其实现方式，
但innoDB的聚簇索引实际上是在同一个结构中保存了B-Tree索引和数据行。

InnoDB通过主键聚簇数据，如果没有定义主键，会选择一个唯一的非空索引代替，如果没有这样的索引，会隐式定义个主键作为聚簇索引。

 

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页，
术语“聚簇”表示具有相近索引值的数据会放在相近的位置。
因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

下图展示聚簇索引中的记录是如何存放的。注意：叶子页包含了行的全部数据，但节点页值包含了索引列。

聚簇索引的优点：
1. 你可以讲有联系的数据放在一起。比如说，当实现一个邮箱，你可以通过user_id聚集，
因此在你检索某用户的邮箱信息时，值需要访问磁盘上很少的逻辑页。如果没有使用聚簇，
每个消息可能都需要请求他各自的磁盘IO。
2. 数据访问会很快。一个聚簇索引将索引和数据行一起放在一个b-tree，因此从聚簇索引中获取数据通常
比非聚簇索引快。
3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点：
1.聚簇索引最大限度地提高了IO密集型应用的性能，但如果数据全部都放在内存中，
则访问顺序就没那么重要了，聚簇索引也就没有优势了。
2. 插入速度严重依赖插入顺序。按照主键的顺序插入是加载数据到innoDB表速度最快的方式。
但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE 命令重新组织一下表。
3. 更新聚簇索引列的代价很高，因为会轻质innoDB将每个更新的行移动到新的位置。
4. 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能会面临‘页分裂’的问题。
当行的主键值要求必须将这一行插入到某个已满的页中，存储引擎会将该页分裂成两个页面来容纳该行，
这就是一次页分裂操作，页分裂会导致表占用更多的磁盘空间。
5. 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
6. 二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引叶子节点包含了引用行的主键列
7. 二级索引访问需要两次索引查找，而不是一次。

#聚簇索引的叶节点就是数据节点，而非聚簇索引的叶节点仍然是索引检点，并保留一个链接指向对应数据块。
#聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。
#相比之下，聚簇索引适合排序，非聚簇索引不适合用在排序的场合。因为聚簇索引本身已经是按照物理顺序放置的，排序很快。非聚簇索引则没有按序存放，需要额外消耗资源来排序。
#当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。

6.覆盖索引

通常大家都会根据查询的where条件来创建索引，不过这只是索引优化的一个方面。
设计优秀的索引应该考虑整个查询，而不单单是where条件部分。
索引确实是一种查找数据的高效方式，
但mysql也可以使用索引来直接获取列的数据，这样就不需要读取数据行。如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢。如果一个索引，
包含所有需要查询的字段的值，我们称之为“覆盖索引”

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引，空间索引，全文索引都不存储索引列的值。
所以mysql只能使用B-Tree索引做覆盖索引，另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎
都支持覆盖索引。

当发起一个被索引覆盖的查询时(也叫做索引覆盖查询)，在EXPLAIN 的Extra可以看到 "Using index"的信息.
例如，表sakila.inventory有一个多列索引(store_id, film_id).MySql如果只访问这两列，就可以使用这个索引做覆盖索引。

mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory

Mysql 不能在索引中执行LIKE 操作。 这是底层存储引擎api的限制。
mysql5.5 只允许在索引中做简单的比较操作（例如等于，大于，小于）
mysql能在索引中做最左前缀匹配的LIKE比较，因为可以转换为简单的比较操作。
但如果以通配符开头，存储引擎就无法匹配。只能提取数据行的值去做比较。


explain select *
from products
where actor = 'sean carrey' 
and title like '%apollo%'

优化-->

explain select *
from products
    join (
        select prod_id
        from products
        where actor = 'sean carrey' and title like '%apollo%'
    ) as t1 on (t1.prod_id = products.prod_id)
    
我们把这种方式叫做 延迟关联，
因为延迟了对列的访问。
在查询的第一阶段可以用覆盖索引。然后再匹配。    
    

7.使用索引扫描来做排序。

　　MySQL 有两种方式可以生成有序的结果， 通过排序操作， 或者按索引顺序排序，
　　如果 explain 出来的type类型 为index，则说明使用了索引扫描来排序。

　　扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不没扫描一条索引记录就都回
　　表查询一次对应的行。这基本上都是随机IO，因此按索引顺序读取数据的速度通常要比顺序的全表扫描慢，尤其是在io秘籍型工作负载时。
　　
　　mysql可以使用同一个索引既满足排序，又用于查找行，因此，如果可能，设计索引时就应该尽可能地同时满足这两种任务，这样是最好的。

　　只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，mysql才能使用索引来对结果做排序。
　　如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一张表时，才能使用索引做排序。order by 子句和查找型查询的限制是一样的：
　　需要满足索引的最左前缀的要求，否则mysql都需要执行排序操作，而无法利用索引排序。

8.压缩索引

9.冗余索引和重复索引

10.删除未使用的索引
　　　　　　
11.索引与锁
　　　　

索引优化：
　　

现在需要看看哪列拥有很多不同的取值，哪些列在where子句中出现得最频繁。在有更多不同值得列上创建索引的选择性会更高。
一般来说这样做都是对的，因为可以让mysql更有效的过滤不需要的行。

country 和 sex 列的选择性通常不高，但可能很多查询都会用到。所以考虑使用的频率，还是建议在创建不同的组合索引的时候将，(sex, country) 列作为前缀
但根据传统的经验不是说不应该在选择性低的列上创建索引吗？

的确是的，但我们这样做有两个理由，
第一个： 如前述几乎所有的查询都会用到sex列
第二个： 更重要的一点，索引中加上一列也没有坏处，即使查询中没有使用sex列也可以通过下列"诀窍"绕过

这个诀窍是：如果某个查询不限制sex，那么可以通过在查询条件中新增 and sex in ("m", "f") 来让mysql选择该索引。
这样写并不会过滤任何行，和没有这个条件时返回的结果相同，但是必须加上这个条件，mysql才能匹配索引的最左前缀，
这个诀窍在这类场景非常有效，但如果列有太多不同的值，就会让in()列表太长，这样做就不行了。

设计索引的一个基本原则： 考虑表上所有选项。当设计索引时，不要只为现有查询考虑需要哪些索引，还需要考虑对查询进行优化。如果发现某些查询需要创建新索引，
但是这个索引又会降低另一些查询的效率，那么应该想一下 是否能优化原来的查询。
应该在优化查询和索引之间找到最佳的平衡，而不是闭门造车去设计最完美的索引。


另一个基本原则是： 尽可能将需要做范围查询的列放到索引列的后面。以便优化器能使用更多的索引列。

# 基数 是数据列所包含的不同值的数量。例如，某个数据列包含值1、3、7、4、7、3，那么它的基数就是4。
可使用 show index from 命令查看索引的基数（Cardinality）.

# 强制使用某索引
SELECT * FROM table1 USE INDEX (col1_index,col2_index)
WHERE col1=1 AND col2=2 AND col3=3;