数据库——MySQL之索引

数据库—MySQL之索引

brycezou@163.com

        在深入阅读《MySQL技术内幕》的基础上，结合实践整理得到本文，希望对大家有所帮助。

1、索引概述

• 索引太多、太少都会影响查询性能。
• 优化索引往往需要监控大量的SQL语句，从中找到问题，对症下药。
• InnoDB存储引擎支持的索引：
  
  • B+树索引，这是目前关系型数据库中最常用、最有效的索引
  • 自适应哈希索引，InnoDB会根据表的使用情况自动为表生成哈希索引，不能人工干预

2、B+树索引

• B+树是为磁盘或其它直接存取辅助设备而设计的一种平衡查找树。在B+树中，所有记录都存于（同一层的）叶子节点，并且是按键值大小顺序存放的，各叶节点通过双向指针进行连接。

• B+树中的B是balance，而不是binary，B+树不是二叉树。

• B+树是高度平衡的，且叶节点存放着所有的数据，数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index，也叫非聚集索引），它们的不同之处在于，叶节点是否存放了一整行的数据。

• 为了保持平衡，B+树对于新插入的键值，可能需要做大量的页拆分操作。而由于B+树主要用于磁盘，页的拆分意味着磁盘操作，因此应该尽可能减少页的拆分，使用旋转可以减少页拆分操作。

• B+树索引其本质就是B+树在数据库中的实现，它在数据库中的一大特点就是其高扇出性，因此在数据库中B+树的高度一般都在2~3层，也就是说，为了查找某一键值的记录，只需要2~3次IO。

• B+树索引本身并不能找到具体的一条记录，它只能找到该记录所在的页。然后，数据库将该页读入内存，通过对Page Directory（页目录）中的槽进行二叉查找，得到具体记录。

3、聚集索引

• 索引组织表：数据按照主键的顺序存放。堆表：数据按照插入的顺序存放。

• InnoDB存储引擎表是索引组织表，表中的数据按照主键顺序存放。

• 聚集索引：按照每张表的主键构造一棵B+树，叶节点作为数据页，存放整张表的行记录数据。由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能有一个聚集索引。查询优化器非常倾向于使用聚集索引，因为能在索引的叶节点上直接找到数据。此外，聚集索引对于主键的排序查找和范围查找速度非常快，查询优化器能够快速确定需要扫描的数据页范围。

• 数据页上存放的是完整的行记录，而索引页中，存放的仅是键值和指向数据页的偏移量。

• 聚集索引的存储并不是物理上连续的，相反是逻辑上连续的，这包含两层意思：1）数据页按照主键的顺序排列，页之间通过双向链表连接；2）每个数据页中的记录也是按照主键顺序排列，记录之间通过双向链表连接。如果存储上也按照特定顺序存放物理记录，则维护成本会非常高。

4、辅助索引

• 辅助索引（非聚集索引）的叶子节点不包含行的全部数据，它只包含索引键值和相应行数据的聚集索引键值，结构形如《辅助索引键值，聚集索引键值》。利用辅助索引查找数据时，先通过该辅助索引在叶节点中查找聚集索引的键值（主键值），然后通过聚集索引查询完整的行记录。

• 辅助索引不影响数据在聚集索引中的组织，因此每张表可以有多个辅助索引。

5、预读取

• 顺序读（Sequntial Read）是指顺序地读取磁盘上的块；随机读（Random Read）是指访问的块不是连续的，需要磁盘的磁头不断地移动。当前传统机械硬盘的瓶颈之一就是随机读的速度较低。

• 为了提升读取性能，InnoDB存储引擎引入了预读取（read ahead 或 prefetch）技术，以此来减少随机读操作。预读取是指，通过一次IO请求，将多个页预读取到缓冲池，因为我们猜测预读取的多个页马上会被访问。

• InnoDB的线性预读取策略：当一个区（区是64个连续页）中的 innodb_read_ahead_threshold（默认值为56）个页都被访问过且访问模式是顺序的，则预读取下一个区的所有页。

mysql> show variables like 'innodb_read_ahead_threshold';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_read_ahead_threshold | 56    |
+-----------------------------+-------+
1 row in set (0.00 sec)

6、创建索引

• 使用 alter table 创建索引。可以索引整个列的数据，也可以只索引列的开头部分。使用 show index 可以查看表中的索引，在结果中，各列的值依次表示：表名、是否非唯一索引、索引名称、索引中该列的位置、索引的列、列以什么方式存储在索引中（B+树索引总是A，即排序的）、索引中唯一值的数量估计值（越接近表的行数越好，如果太小就要考虑该索引是否有必要）、是否列的局部被索引、关键字如何被压缩（NULL表示没有被压缩）、索引的列是否允许为NULL、索引的类型（InnoDB存储引擎只支持B+树索引）、注释。

• 优化器会根据 Cardinality 来判断是否使用这个索引。但 Cardinality 并不是实时更新的，即并非每次索引的更新都会更新该值，因为代价很大。Cardinality 的值只是一个大概，使用 analyze table 可以更新索引的 Cardinality 数值。当 Cardinality 为NULL，或者 explain 两条基本一样的语句但结果差别很大时（一条用索引，另一条全表扫描），最好在非高峰时期，对核心表做一次 analyze table 操作，这样能使优化器和索引更好地工作。

• 联合索引：对表上的多个列做索引。联合索引本质上也是一棵B+树，只不过键值的数量大于1。键值都是排序的，通过叶节点可以逻辑上顺序地读出所有数据。联合索引还有一个好处，它可以自然地对第2个键值进行排序。

mysql> create table t(a int not null primary key,b varchar(20));
mysql> insert into t values(1,repeat('a',20));
mysql> insert into t values(2,repeat('b',20));
mysql> insert into t values(3,repeat('c',20));
mysql> insert into t values(4,repeat('a',20));
mysql> alter table t add c int not null;
mysql> update t set c=0-a*10;
mysql> alter table t add key idx_b(b(10));
mysql> alter table t add key idx_c(c);
mysql> alter table t add key idx_ac(a,c);
mysql> desc t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | int(11)     | NO   | PRI | NULL    |       |
| b     | varchar(20) | YES  | MUL | NULL    |       |
| c     | int(11)     | NO   | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> analyze table t;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.01 sec)

mysql>  show index from t;

7、优化索引

• 高选择性（取值范围广、几乎没有重复，例如姓名、学号）的字段很适合用 B+ 树索引，而低选择性（取值范围很小、大量重复，例如性别、省份）的字段则不建议使用 B+ 树索引。

• 当访问高选择性字段，并且只从表中取出很少一部分数据行时，很有必要为该字段添加 B+ 树索引；但如果访问字段仍是高选择性的，而取出的数据行在表中占一大部分比例，这时 MySQL 数据库就不会使用 B+ 树索引。MySQL 数据库的优化器通过 explain 的 rows 字段预估查询可能得到的行数，如果大于某一个阈值（姜承尧预估为总记录数的20%左右，我通过对10000行自定义记录进行查询，得到该比例约为17%），则会进行全表扫描而不是使用 B+ 树索引。

• Explain 命令可以得到 MySQL 的执行计划，rows 为查询结果的预估返回行数。

• 下面的例子，结果集只相差1，但执行计划却完全不同，此处的阈值貌似接近40%

mysql> select a,c from t;
+----+-----+
| a  | c   |
+----+-----+
| 10 | -91 |
|  9 | -90 |
|  8 | -80 |
|  7 | -70 |
|  6 | -60 |
|  5 | -50 |
|  4 | -40 |
|  3 | -30 |
|  2 | -20 |
|  1 | -10 |
+----+-----+
10 rows in set (0.00 sec)

mysql> explain select * from t where c >= -30;
-+-------+---------------+-------+---------+------+----------+------------
 | type  | possible_keys | key   | key_len | rows | filtered | Extra      
-+-------+---------------+-------+---------+------+----------+------------
 | range | idx_c         | idx_c | 4       |    3 |   100.00 | Using index
-+-------+---------------+-------+---------+------+----------+------------
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t where c >= -40;
-+------+---------------+------+---------+------+----------+-------------+
 | type | possible_keys | key  | key_len | rows | filtered | Extra       |
-+------+---------------+------+---------+------+----------+-------------+
 | ALL  | idx_c         | NULL | NULL    |   10 |    40.00 | Using where |
-+------+---------------+------+---------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• 当然，也可以强制使用索引

mysql> explain select * from t force index(idx_c) where c >= -40;
-+-------+---------------+-------+---------+------+----------+------------
 | type  | possible_keys | key   | key_len | rows | filtered | Extra      
-+-------+---------------+-------+---------+------+----------+------------
 | range | idx_c         | idx_c | 4       |    4 |   100.00 | Using index
-+-------+---------------+-------+---------+------+----------+------------
1 row in set, 1 warning (0.00 sec)

• 注意：优化器的选择并不总是正确的，有时需要相信自己的判断。

8、辅助索引的优化使用

• 辅助索引的叶节点中包含有主键，但是并不包含完整的行信息。因此，InnoDB存储引擎总是会先从辅助索引的叶节点判断是否能得到所需的数据。

• 因为辅助索引中包含了主键 a 的值，因此访问 b 列上的辅助索引就能得到 a 的值，进而得到表中的所有数据。并且通常情况下，一个辅助索引页中能存放的数据比主键页上存放的数据多，因此优化器选择了辅助索引。为了按主键值进行排序，可以强制使用主键进行查询。

mysql> create table t(a int not null,b varchar(20),primary key(a),key(b));
mysql> insert into t select 1,'kangaroo';
mysql> insert into t select 2,'dolphin';
mysql> insert into t select 3,'dragon';
mysql> insert into t select 4,'antelope';
mysql> select * from t; （和想象的结果有点差距）
+---+----------+
| a | b        |
+---+----------+
| 4 | antelope |
| 2 | dolphin  |
| 3 | dragon   |
| 1 | kangaroo |
+---+----------+
4 rows in set (0.00 sec)

mysql> explain select * from t;
-+-------+---------------+------+---------+------+----------+-------------+
 | type  | possible_keys | key  | key_len | rows | filtered | Extra       |
-+-------+---------------+------+---------+------+----------+-------------+
 | index | NULL          | b    | 23      |    4 |   100.00 | Using index |
-+-------+---------------+------+---------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from t force index(primary);
+---+----------+
| a | b        |
+---+----------+
| 1 | kangaroo |
| 2 | dolphin  |
| 3 | dragon   |
| 4 | antelope |
+---+----------+
4 rows in set (0.00 sec)

9、哈希索引

• 哈希函数必须能够很好地进行散列，最好的情况是能避免冲突的发生，即使不能避免，也应该使冲突发生的几率尽可能小。数据库中一般采用除法散列 h(k)=m mod k，m 的值为略大于2倍的缓冲池页数量的质数。例如，参数 innodb_buffer_pool_size=10M，则其共有640个16K的页。那么，对于缓冲池页内存的哈希表来说，需要分配640*2=1280个槽，但1280不是质数，比1280略大的质数为1399，所以在启动时会分配1399个槽的哈希表，用来哈希查询所在缓冲池中的页。

• 哈希索引只能用来搜索等值查询，它不支持范围查询。虽然自适应哈希索引是数据库自己创建并使用的，我们不能对其进行干预，但我们可以通过参数 innodb_adaptive_hash_index 来控制是否启用自适应哈希索引。