MySQL索引总结

最新推荐文章于 2024-08-11 16:40:55 发布

tinycs@126.com

最新推荐文章于 2024-08-11 16:40:55 发布

阅读量331

点赞数

数据库索引主要应用于提升海量数据库表的查询效率，是一种数据结构。

存储原理

通常情况下，索引本身也很大，不可能全部存储在内存，一部分要以索引文件形式存储磁盘。

主存存取

主存存取在内存中直接寻址，对任意地址的存取效率相同，仅与存取次数线性相关，与两次寻址距离不相关。

磁盘存储

磁盘存储为机械寻址，主要包括寻道时间和旋转时间，比直接主存寻址耗时高多个数量级。一般通过降低磁盘IO次数，来提升磁盘存储效率，主要包括局部性原理和磁盘预读取。

局部性原理是指，由于程序运行时所需数据的集中特性，当一个数据需要用到时，其附近的数据也可能会马上用到。
磁盘预读取是指，当程序要使用的数据不在主存时，会触发一个缺页中断（主存和磁盘通常以4K大小一页，划分为连续大小相等的页），从磁盘获取缺少的一页数据，并向后再多读取1页或多页数据到主存。

索引结构

下表是一张简单的数据样表，其中第一列为物理地址，可以看出，数据表存储物理地址并不连续，第二列为样表列1，第三列为样表列2。

物理地址	样表列1	样表列2
0x0A	1	30
0x33	2	45
0xA1	3	2
0xA8	4	6
0x56	5	7
0x23	6	25
0x12	7	35

二叉树索引结构

下图是基础样表列2建立的二叉树索引结构，可以在复杂度 $O(log_2 n)$ 查找到相应数据。

缺点
随着数据量增大，二叉树高度会越来越高，树枝变换也会越来越频繁。特别的，由于数据的不连续性，磁盘存取不能有效契合磁盘预读取功能，一次精确查找可能会触发多次磁盘IO，最高达到 $（log_2 n）-1$ 次（根节点常住内存）。
因此，二叉树索引结构，一方面，影响查询效率，另一方，影响增、删、改效率。

B+树索引结构

MySQL的MyISAM引擎和InnoDB引擎默认使用B+树作为索引结构。

B+树

下图是一张B+树样图，叶子节点均为右向箭头。

和B-树不同的是，B+树的非叶子节点，不存储data数据，只存储key值。
另外，为了优化范围查询，叶子节点增加邻向单向指针。

B+树索引设计特性

每个B+树节点设计为大小等于1页（4K），出度d通常较大（大于100），因此，高度h较小（不大于3）。一次B+树精确查询的磁盘IO次数最多为 $（log_d n）-1=h-1$ 次（根节点常住内存）。

MyISAM引擎B+树

MyISAM引擎B+树特点是叶子节点data域存储数据记录地址，通常称为“非聚集”索引。主索引和辅助索引的区别是，主索引key值唯一，辅助索引key值可重复。
索引查询时，如果定位至key值，则首先取出data域数据记录地址，然后根据地址获取相应数据记录。

InnoDB引擎B+树

InnoDB引擎B+树特点：

数据文件本身即是索引文件，叶子节点data域存储完整的数据记录，key域为主键，该索引即为主索引，是一种“聚集”索引；MyISAM索引文件只保存数据记录地址，数据文件和索引文件分离；InnoDB数据表必须要有和主键，如果没有，会自动选择具有唯一标识的列做为主键，再如果找不到这样的列，会自动生成一个6字节的长整形隐含字段作为主键。
辅助索引叶子节点data域存储主键值，首次查找时，定位到主键值，然后根据该主键值搜索主索引，定位到完成数据记录。由于辅助索引叶子节点data域均引用主键值的特点，在设计数据表时，选择主键值长度不宜过长。

索引优化

鉴于B+树特性，数据库表设计时，推荐使用自增序列做为主键，可以达到更好的插入效率。使用一个无规则乱序字段做为主键，在插入一条记录时，会使得B+树频繁的分裂调整，造成插入操作低效。另外，如上所述，InnoDB数据库表主键长度不宜过长，否则会造成辅助索引较大存储空间开销。
下表为样表car_gb_msg_log_t的索引结构，包括一个主索引和两个辅助索引，其中辅助索引car_id为组合索引（car_id, msg_type，length）。

MySQL [data]> show index from car_gb_msg_log_t;
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| car_gb_msg_log_t |          0 | PRIMARY  |            1 | id          | A         |     3704553 |     NULL | NULL   |      | BTREE      |         |               |
| car_gb_msg_log_t |          1 | recvtime |            1 | recv_time   | A         |      308712 |     NULL | NULL   | YES  | BTREE      |         |               |
| car_gb_msg_log_t |          1 | car_id   |            1 | car_id      | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
| car_gb_msg_log_t |          1 | car_id   |            2 | msg_type    | A         |        2352 |     NULL | NULL   | YES  | BTREE      |         |               |
| car_gb_msg_log_t |          1 | car_id   |            3 | length      | A         |       18709 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

精确匹配

使用关键字：=、in、between and。其中in和between and之所以归类到此，是因为其不会影响到最左前缀匹配规则。

MySQL [data]> explain select * from car_gb_msg_log_t where car_id = 72617063;
+----+-------------+------------------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table            | type | possible_keys | key    | key_len | ref   | rows | Extra       |
+----+-------------+------------------+------+---------------+--------+---------+-------+------+-------------+
|  1 | SIMPLE      | car_gb_msg_log_t | ref  | car_id        | car_id | 5       | const | 4522 | Using where |
+----+-------------+------------------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.13 sec)

MySQL [data]> explain select * from car_gb_msg_log_t where car_id in( 72617063,32817006);
+----+-------------+------------------+-------+---------------+--------+---------+------+-------+-------------+
| id | select_type | table            | type  | possible_keys | key    | key_len | ref  | rows  | Extra       |
+----+-------------+------------------+-------+---------------+--------+---------+------+-------+-------------+
|  1 | SIMPLE      | car_gb_msg_log_t | range | car_id        | car_id | 5       | NULL | 23366 | Using where |
+----+-------------+------------------+-------+---------------+--------+---------+------+-------+-------------+
1 row in set (0.18 sec)

MySQL [data]> explain select * from car_gb_msg_log_t where car_id between 32817006 and 32817056 and msg_type = 2;
+----+-------------+------------------+-------+---------------+--------+---------+------+--------+-------------+
| id | select_type | table            | type  | possible_keys | key    | key_len | ref  | rows   | Extra       |
+----+-------------+------------------+-------+---------------+--------+---------+------+--------+-------------+
|  1 | SIMPLE      | car_gb_msg_log_t | range | car_id        | car_id | 10      | NULL | 226030 | Using where |
+----+-------------+------------------+-------+---------------+--------+---------+------+--------+-------------+
1 row in set (0.00 sec)

最左前缀匹配

精确查询条件必须遵循最左前缀匹配原则，且严格按序使用，才能有效使用索引。
如上文提到的辅助索引car_id组合索引（car_id, msg_type,length），如果查询条件直接使用msg_type字段，将不能使用索引。如果仅使用car_id和length字段，则只会用到car_id字段的索引。

MySQL [data]> explain select * from car_gb_msg_log_t where  msg_type = 2;
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | car_gb_msg_log_t | ALL  | NULL          | NULL | NULL    | NULL | 3721316 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

MySQL [data]> explain select * from car_gb_msg_log_t where  car_id = 32817006 and length = 52;
+----+-------------+------------------+------+---------------+--------+---------+-------+-------+-------------+
| id | select_type | table            | type | possible_keys | key    | key_len | ref   | rows  | Extra       |
+----+-------------+------------------+------+---------------+--------+---------+-------+-------+-------------+
|  1 | SIMPLE      | car_gb_msg_log_t | ref  | car_id        | car_id | 5       | const | 17428 | Using where |
+----+-------------+------------------+------+---------------+--------+---------+-------+-------+-------------+
1 row in set (0.00 sec)

范围查询

当索引字段使用范围关键字>、<、>=、<=、<>、%时，该字段及组合索引中该字段后的索引全部无效，将执行全表扫描。

MySQL [data]> explain select recv_time from car_gb_msg_log_t where car_id > 32817006 and msg_type = 2 and length =56;
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | car_gb_msg_log_t | ALL  | car_id        | NULL | NULL    | NULL | 3704553 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

MySQL [data]> explain  select recv_time from car_gb_msg_log_t where car_id like '3704553%' and msg_type = 2 and length =56;
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | car_gb_msg_log_t | ALL  | car_id        | NULL | NULL    | NULL | 3704553 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

对索引字段进行计算

如果对索引字段使用函数，或进行计算，则该字段索引无效。

MySQL [data]> explain  select recv_time from car_gb_msg_log_t where trim(car_id) = '3704505330' and msg_type = 2 and length =56;
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | car_gb_msg_log_t | ALL  | NULL          | NULL | NULL    | NULL | 3704553 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

MySQL [data]> explain  select recv_time from car_gb_msg_log_t where car_id-1 = '3704505330' and msg_type = 2 and length =56;
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | car_gb_msg_log_t | ALL  | NULL          | NULL | NULL    | NULL | 3704553 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

前缀索引

如果要对一些长度较长的字段建立辅助索引，并且又为了尽可能减少索引存储空间，可以对该字段辨识度较高前缀建立索引。
其中辨识度计算公式为： $count(distinct\_column) / count(*)$ ，如果该值为1，辨识度最好，说明该字段每行均不相同，如果该值为0，则说明该字段只有一个值，不适宜建立索引。