MySQL索引总结

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

存储原理

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

主存存取

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

磁盘存储

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

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

索引结构

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

物理地址样表列1样表列2
0x0A130
0x33245
0xA132
0xA846
0x5657
0x23625
0x12735
二叉树索引结构

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

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

B+树索引结构

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

B+树

下图是一张B+树样图,叶子节点均为右向箭头。
B+树样图
和B-树不同的是,B+树的非叶子节点,不存储data数据,只存储key值。
另外,为了优化范围查询,叶子节点增加邻向单向指针。

B+树索引设计特性

每个B+树节点设计为大小等于1页(4K),出度d通常较大(大于100),因此,高度h较小(不大于3)。一次B+树精确查询的磁盘IO次数最多为 logdn1=h1 ( l o g 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() c o u n t ( d i s t i n c t _ c o l u m n ) / c o u n t ( ∗ ) ,如果该值为1,辨识度最好,说明该字段每行均不相同,如果该值为0,则说明该字段只有一个值,不适宜建立索引。

主键优化

针对InnoDB数据库引擎,如果该数据库表日增量较大,推荐使用自增序列做为主键。原因是,参照B+树有序特点,自增序列主键在插入数据记录时,会自动在B+树最右侧节点,或最左侧节点所在内存页插入,可以最大化降低B+树变化开销。

本文主要参考了以下文章,在此表示感谢。
http://blog.codinglabs.org/articles/theory-of-mysql-index.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值