高性能MYSQL---索引读书笔记

B+Tree 每一个叶子节点都包含指向下一个叶子节点的指针。

B-Tree 

MYSIAM, 使用前缀压缩技术使得数据更小，INNODB则按照原数据格式进行存储。

每一个叶子节点到根节点的距离相同

哈希索引，每一列生成唯一哈希索引。在哈希表中保存每个数据行的指针

MYSQL memory，支持哈希索引

哈希索引，只包含哈希值，不存储字段，所以不能使用索引中的值来避免读取行。

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

哈希索引也不支持部分索引列匹配查询。  例如  在 A,B列建立索引，那查询只有数据列A 则无法使用索引。

哈希索引如果发生冲突（不同的索引列值有相同的哈希值），存储引擎必须遍历链表中的所有的行指针吗，每行精心比较知道找到所有符合条件的行。

   数据仓库景点的星形  schema需要关联很多查找表。哈希索引就非常适合查找表的需求。。

INNODB 有一个特殊的功能，叫做自适应哈希索引。当INNODB注意到某系索引值被使用的非常频繁，会在索引上再创建一个哈希索引。

自定义哈希索引   SELECT ID FROM URL WHERE url ="http://baidu.com"   ===>>>   SELECT ID FROM URL WHERE cur_crc = CRC32("http://baidu.com") AND url = "http://baidu.com"

只要根据哈希值做快速的比较，就可以很快找到索引条目。而不是   对完整URL进行 字符串做索引

如果只是统计数，不精确的，则可以不带入列值，直接使用CRC32()的哈希值 查询即可。还可使用FNV64() 函数座位哈希函数。

空间数据索引，

可以存储地理数据信息，和B-Tree索引不同，这类索引会从所有维度来索引数据。查询时可以有效的使用任意维度的组合来查询。    必须使用mysql的GIS相关的函数，如MBRCONTAINS()来维护。　　　mysql 对GIS的支持并不完善。  PostgreSql 的PostGIS支持好

全文索引：

他查找的是文本中的关键词。和普通的where 不一样

索引优点

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

帮助服务器避免排序和临时表

可以将随机I/O变为顺序I/O

索引的选择性

前缀索引：TEXT与BLOB或VCHAR   字段类型。可以使用前缀索引

 SELECT COUNT(*), LEFT(city,3) AS pre 

FROM city GROUP BY pre ORDER BY cnt DESC LIMIT 5；

通过此来判断使用多长的索引。

前缀索引是一种使索引更小，更快的方法，但是缺点：无法使用前缀索引进行  ORDER BY 和 GROUP BY 。也无法使用前缀索引做覆盖扫描。

多列索引：

actor_id 和film_id 都有索引

SELECT filmd_id,actor_id FROM film_actor 

WHERE actor_id = 1 OR film_id =1;

MYSQL   5.0 会优化使用  UNION 来交叉查询。

     EXPLAIN   extra：Using union(PRIMARY，idx_fk_film_id); Using where

当服务器对多个索引做想加操作时  通常多个AND 条件，意味着需要一个包所有相关列的多列索引。而不是多个单独的单列索引。

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

如何使用多列索引？

多列索引的顺序：将选择性最高的列放在最前面。

  SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selctivity,

COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,

COUNT(*)

FROM payment;

staff_id_selectivity:0.0001;

customer_id_selectivity:0.0003;

count(*):100000;

customer_id_serlectivity 选择性更高，将其作为索引的第一列。

SQL的where条件提取

 

在有了以上的t1表之后，接下来就可以在此表上进行SQL查询了，获取自己想要的数据。例如，考虑以下的一条SQL：

select * from t1 where b >= 2 and b < 8 and c > 1 and d != 4 and e != ‘a’;

所有SQL的where条件，均可归纳为3大类：Index Key (First Key & Last Key)，Index Filter，Table Filter。

 

接下来，让我们来详细分析者3大类分别是如何定义，以及如何提取的。

 

l         Index Key

 

用于确定SQL查询在索引中的连续范围(起始范围+结束范围)的查询条件，被称之为Index Key。由于一个范围，至少包含一个起始与一个终止，因此Index Key也被拆分为Index First Key和Index Last Key，分别用于定位索引查找的起始，以及索引查询的终止条件。

 

Index First Key

 

用于确定索引查询的起始范围。提取规则：从索引的第一个键值开始，检查其在where条件中是否存在，若存在并且条件是=、>=，则将对应的条件加入Index First Key之中，继续读取索引的下一个键值，使用同样的提取规则；若存在并且条件是>，则将对应的条件加入Index First Key中，同时终止Index First Key的提取；若不存在，同样终止Index First Key的提取。

针对上面的SQL，应用这个提取规则，提取出来的Index First Key为(b >= 2, c > 1)。由于c的条件为 >，提取结束，不包括d。

 

Index Last Key

 

Index Last Key的功能与Index First Key正好相反，用于确定索引查询的终止范围。提取规则：从索引的第一个键值开始，检查其在where条件中是否存在，若存在并且条件是=、<=，则将对应条件加入到Index Last Key中，继续提取索引的下一个键值，使用同样的提取规则；若存在并且条件是 < ，则将条件加入到Index Last Key中，同时终止提取；若不存在，同样终止Index Last Key的提取。

针对上面的SQL，应用这个提取规则，提取出来的Index Last Key为(b < 8)，由于是 < 符号，因此提取b之后结束。

 

2         Index Filter

 

在完成Index Key的提取之后，我们根据where条件固定了索引的查询范围，但是此范围中的项，并不都是满足查询条件的项。在上面的SQL用例中，(3,1,1)，(6,4,4)均属于范围中，但是又均不满足SQL的查询条件。

Index Filter的提取规则：同样从索引列的第一列开始，检查其在where条件中是否存在：若存在并且where条件仅为 =，则跳过第一列继续检查索引下一列，下一索引列采取与索引第一列同样的提取规则；若where条件为 >=、>、<、<= 其中的几种，则跳过索引第一列，将其余where条件中索引相关列全部加入到Index Filter之中；若索引第一列的where条件包含 =、>=、>、<、<= 之外的条件，则将此条件以及其余where条件中索引相关列全部加入到Index Filter之中；若第一列不包含查询条件，则将所有索引相关条件均加入到Index Filter之中。

针对上面的用例SQL，索引第一列只包含 >=、< 两个条件，因此第一列可跳过，将余下的c、d两列加入到Index Filter中。因此获得的Index Filter为 c > 1 and d != 4 。

 

3         Table Filter

 

Table Filter是最简单，最易懂，也是提取最为方便的。提取规则：所有不属于索引列的查询条件，均归为Table Filter之中。

同样，针对上面的用例SQL，Table Filter就为 e != ‘a’。

 

3.1 Index Key/Index Filter/Table Filter小结 

 

SQL语句中的where条件，使用以上的提取规则，最终都会被提取到Index Key (First Key & Last Key)，Index Filter与Table Filter之中。

 

Index First Key，只是用来定位索引的起始范围，因此只在索引第一次Search Path(沿着索引B+树的根节点一直遍历，到索引正确的叶节点位置)时使用，一次判断即可；

 

Index Last Key，用来定位索引的终止范围，因此对于起始范围之后读到的每一条索引记录，均需要判断是否已经超过了Index Last Key的范围，若超过，则当前查询结束；

 

Index Filter，用于过滤索引查询范围中不满足查询条件的记录，因此对于索引范围中的每一条记录，均需要与Index Filter进行对比，若不满足Index Filter则直接丢弃，继续读取索引下一条记录；

 

Table Filter，则是最后一道where条件的防线，用于过滤通过前面索引的层层考验的记录，此时的记录已经满足了Index First Key与Index Last Key构成的范围，并且满足Index Filter的条件，回表读取了完整的记录，判断完整记录是否满足Table Filter中的查询条件，同样的，若不满足，跳过当前记录，继续读取索引的下一条记录，若满足，则返回记录，此记录满足了where的所有条件，可以返回给前端用户。