Index-High performance mysql

主要目的是知道什么case下应该用什么，或者怎么设计。
所以 1.知道各种设计的优缺点
2. 积累cases in real world
hash:
1. memory table(memory storage engine)
2. the lookup tables in star schema

1. B+ tree indexes:

数据结构

• insertion

  start from leaf node

  1. if node not full, insert
  2. if node is full. do separation using median. median passed to upper level

• deletion

  1. deletion from a leaf node
  2. deletion from an internal node
  3. rebalancing after deletion: rotation

索引实现

1. 使用索引的过程 flow：
根据索引,经过多层node page（logical page）查到数据行所在的leaf page, 然后数据库把leaf page读入memory，在memory中查找，最后得到结果

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

2. 使用b+tree索引的实例：
Suppose you have the following table:

CREATE TABLE People (
    last_name varchar(50) not null,
    first_name varchar(50) not null,
    dob date not null,
    gender enum('m', 'f')not null,
    key(last_name, first_name, dob)
);

创建了一个组合索引

3. B+tree indexes的应用场景
索引存储的值按索引列中的顺序排列。可以利用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。

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

---

2. Hash indexes:

mysql中， 只有memory storage engine explicitly 支持hash indexes，是memory表的默认索引类型。

1. 使用hash indexes 实例
假设创建如下一个表：

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

包含的数据如下：

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

f('Arjen') = 2323
f('Baron') = 7437
f('Peter') = 8784
f('Vadim') = 2458

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

当你执行

mysql> SELECT lname FROM testhash WHERE fname='Peter';

MySQL会计算’Peter’的hash值，然后通过它来查询索引的行指针。因为f(‘Peter’) = 8784，MySQL会在索引中查找8784，得到指向记录3的指针。

2. 好处
(1) 速度快
(2) 因为索引自己仅仅存储很短的值，所以，索引非常紧凑。
(3) Hash值不取决于列的数据类型，一个TINYINT列的索引与一个长字符串列的索引一样大。

3. 限制：
(1)由于索引仅包含hash code和记录指针，所以，MySQL不能通过使用索引避免读取记录。但是如果是memory table，访问内存中的记录是非常迅速的，不会对性造成太大的影响。
(2)不能使用hash索引排序。
(3)Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的。
(4)Hash索引只支持等值比较，例如使用=，IN( )和<=>。对于WHERE price>100并不能加速查询。

---

3. Spatial(R-Tree) indexes

MyISAM支持空间索引，主要用于地理空间数据类型，例如 GIS.

4. Full-text indexes 第七章有详解

全文索引是MyISAM的一个特殊索引类型，主要用于全文检索。