高性能MySQL学习五(B+树索引、哈希索引、全文索引)

1、为什么使用索引
索引用于快速查找具有特定列值的行。如果没有索引，MySQL必须从第一行开始，然后读取整个表以查找相关行。表越大，成本越高。但如果表中有相关​​列的索引，MySQL就可以快速确定要在数据文件中间寻找的位置，而无需查看所有数据。这比按顺序读取每一行要快得多。
大多数MySQL索引（PRIMARY KEY， UNIQUE，INDEX和 FULLTEXT）存储在B树。例外情况是空间数据类型的索引使用R树，并且该 MEMORY表也​​支持哈希索引。

2、B+树索引
B+树索引就是传统意义上的索引,这是目前关系型数据库系统中查找最为常用和最为有效的索引。B+树索引的构造类似于二叉树，根据键值(Key Value)快速找到数据。( B+树中的B不是代表二叉 (binary)，而是代表平衡(balance)， 因为B+树是从最早的平衡二叉树演化而来，但是B+树不是一个二叉树。)
另一个常常被DBA忽视的问题是:B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读人到内存，再在内存中进行查找，最后得到要查找的数据。
假设有以下数据表：

mysql> 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)
    -> )engine=innodb;
Query OK, 0 rows affected (0.05 sec)

该索引组织数据的存储方式如下图所示：

要注意的是，索引对多个值进行排序的依据是CREATE TABLE 语句中定义索引时列的顺序。看一下最后两个条目，两个人的姓和名都-样，则根据他们的出生日期来排列顺序。

我们插入部分数据来帮助我们实验

mysql> select * from people;
+-----------+------------+------------+--------+
| last_name | first_name | dob        | gender |
+-----------+------------+------------+--------+
| Akroyd    | Christian  | 1958-12-07 | f      |
| Akroyd    | Debbie     | 1990-03-18 | f      |
| Akroyd    | Kirsten    | 1978-11-02 | m      |
| Allen     | Cuba       | 1960-01-02 | m      |
| Allen     | Kim        | 1930-07-12 | m      |
| Allen     | Meryl      | 1980-12-12 | f      |
| Barrymore | Julia      | 2000-05-12 | f      |
+-----------+------------+------------+--------+
7 rows in set (0.01 sec)

这里需要在提到一个MySQL中的语句，EXPLAIN语句，该语句提供了有关SELECT语句执行计划的信息 ，使用也很简单，只要在SELECT语句前加上EXPLAIN即可，显示列信息如下所示。更多可以参见官网EXPLAIN输出格式

B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。前面所述的索引对如下类型的查询有效。

此外，B+Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问;数据行。

• 后面我们将单独讨论这种“覆盖索引”的优化。

//全值匹配
mysql> select * from people where last_name='Allen' and first_name='Cuba' and dob='1960-01-02';		
+-----------+------------+------------+--------+
| last_name | first_name | dob        | gender |
+-----------+------------+------------+--------+
| Allen     | Cuba       | 1960-01-02 | m      |
+-----------+------------+------------+--------+
1 row in set (0.00 sec)

mysql> explain select * from people where last_name='Allen' and first_name='Cuba' and dob='1960-01-02';
+----+-------------+--------+------+---------------+-----------+---------+-------------------+------+-------------+
| id | select_type | table  | type | possible_keys | key       | key_len | ref               | rows | Extra       |
+----+-------------+--------+------+---------------+-----------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | people | ref  | last_name     | last_name | 107     | const,const,const