62 mysql 中存储引擎MyISAM 中索引的使用

前言

固定数据表

mysql. tables_priv 的表结构创建如下

CREATE TABLE `tables_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Grantor` char(93) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
  KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges'

基于固定长度的MyISAM 的数据表的索引的使用

这里是按照 B+ 树来存储的索引

然后我们这里来看一下一个具体的索引的使用方式

假设执行 sql 如下 “select * from `tables_priv` where Host = '%';”

如下图, 在 sub_select 中的处理是在 qep_tab->read_first_record 的处理, 就是根据查询条件 “Host = '%';” 的条件的定位

然后具体到索引的存储方式, 这里 tables_priv 是以 BTREE 的方式进行存储的

然后下面的 (*info->read_record(info, info->lastpos, buf)) 是根据记录的位置, 读取具体的数据信息到 buf 中

然后这里是具体的根据索引进行查询的方式, keyinfo->bin_search 是具体的查询方式

然后下面 info->lastpos 以及相关代码是从索引信息中获取上下文需要的数据

info->lastpos 表示的是查询目标记录的偏移, 比如这里的 ” %@test_03@tz_test@tz_test” 记录对应的是第三条数据, 偏移为 947 * 2 = 1894

keyinfo 表示的是使用的索引的信息, 这里可以看到的是有四个字段, keyinfo->seg 开始为索引的每一个字段的信息

然后接着是获取到偏移之后, 具体的数据的读取, 这部分在上面的流程中对应于 (*info->read_record(info, info->lastpos, buf))

然后这里的具体的通过偏移读取记录数据的具体实现

然后这是读取了第一条数据, 接下来是查询索引获取下一条符合条件的数据

mi_search_next 查询下一个符合条件的记录之后, 下面 ha_key_cmp 比较目标索引, 待查询条件

我们这里的情况是匹配不上, 然后响应 HA_ERR_END_OF_FILE 给上层, 然后再上面 sub_select 跳出循环

下面的 (*info->read_record(info, info->lastpos, buf)) 为匹配成功之后, 读取下一条记录的处理

基于固定长度的MyISAM 的数据表的索引的查询方式

具体的查询方式, 这里的标准如下

如果索引有压缩的数据, 只能使用顺序查询 mi_seq_search

如果索引是有变长的字段, 查询方式为顺序查询 mi_seq_search 或者前缀查询 mi_prefix_search

如果是固定长度的数据, 并且没有压缩, 使用二分查找 mi_bin_search

我这里 tables_priv 的查询方式为顺序查询 mi_seq_search

我们这里主要是看一下顺序查询 mi_seq_search 和二分查找 mi_bin_search

顺序查询 mi_seq_search

这个是先很简单, 就是从一个索引开始, 向下顺序查询

这里 (*keyinfo->getkey(keyinfo, nod_flag, &page, t_buf)) 为查询当前索引的 key 相关信息, 然后并更新 page 的位置到下一个索引

然后接下来就是 ha_key_cmp 来进行索引和查询条件的比较, 如果比较成功, 则 break 出去

否则继续顺序向下面迭代

二分查询 mi_bin_search

这个就是一个很简单的二分查询, 这里不多说

基于固定长度的MyISAM 的数据表的索引的存储方式

在 MyISAM 中, 无论是主键索引, 还是其他普通索引, 均是采用非聚簇索引的方式进行存储的, 因为在 MyISAM 中该记录的偏移实际上是我们常规理解的索引, 因此索引值存放的是能够直接或者间接推导出该偏移的数据

比如我们这里 “%@test_03@tz_test@tz_test” 的索引记录如下

其前面这部分存储的是四个 key, 然后数据值存储的是 0x02, 是该记录的索引, 表示该记录是目标表的第三条数据

然后这里是读取索引记录, 获取索引的偏移

这里读取到的索引值为 0x02, 表示该记录是目标表的第三条记录

然后乘以 info->s->base.pack_reclength 为该记录的偏移, 然后之后基于该偏移来获取数据

基于动态长度的MyISAM 的数据表的索引的存储方式

mysql.user 创表语句如下, 可以看到索引是 “Host@User“

CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin DEFAULT '',
  `authentication_string` text COLLATE utf8_bin,
  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

索引记录拆解如下, “%@tz_test” 是索引的 key

然后具体存放的数值为 0x0184, 对应于偏移 388