• 欢迎访问微视觉网站,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站,欢迎加入微视觉微视觉-影视后期交流
  • 本站全面支持自动充值,目的是更好的服务大家!
  • 本站全面开启SSL服务,请放心使用!
  • 如果您觉得本站对你非常有用,那么赶紧使用Ctrl+D 收藏吧

细聊MySQL的Innodb存储引擎(完结篇)

数据库 Jason.w.wei 2年前 (2016-09-03) 956次浏览 已收录 0个评论

上篇主要和大家探讨了 Innodb 引擎中出现幻读的处理方法与死锁的探测及避免死锁的一些注意事项。此篇,我们来研究下 Innodb 的索引。
Innodb 里涉及到的索引主要有四种,分别为聚簇索引(Clustered Index)、次级索引(Secondary Index)、全文索引(FULLTEXT Index)、哈希索引(Hash Index)。
聚簇索引与次级索引
每一个 Innodb 表都有一个唯一的聚簇索引。一般来说,每个表的主键就是聚簇索引。如果你的表中没有定义主键,那么MySQL会将第一个非空唯一索引作为聚簇索引。如果表中既没有主键,也没有合适的唯一索引,Innodb 会自己生成一个隐藏的聚簇索引。
通过聚簇索引的查询速度是很快的,因为查询到的索引会直接指向数据行。如果一个表的数据量非常大,聚簇索引会频繁的被读写而造成 I/O 负载教高。特别是数据文件与索引文件不在一个文件的情况下。
次级索引,所有非聚簇索引的索引就被称为次级索引。次级索引可以有很多个,每一个次级索引记录内都包含主键列。用户在使用次级索引查询时,MySQL根据次级索引对应的主键进行查询。如果主键所占的字节过大,那么次级索引也就需要更大的空间。所以,主键还是越短越好。
全文索引
Innodb 在 5.5 之后支持全文索引。全文索引能帮助用户快速查询设置了全文索引的列。全文索引可以使用 CREATE TABLE 或 ALTER TABLE 或 CREATE INDEX 等语法设置。
全文索引有一个被称为“反转索引”的设计。反转索引存储数据列中出现的每一个单词。它会将数据列中的文档划分为不同的单词,将单词、单词所在的位置信息、偏移量等信息都存储在全文索引表里。
下面介绍下存储全文索引的“全文索引表”,首先进行以下操作:

mysql> use test;

mysql> create table opening_lines 
( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 
  opening_line TEXT(500), 
  author VARCHAR(200), 
  title VARCHAR(200), 
  FULLTEXT idx (opening_line) )
ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql> select table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE 'test/%';
+----------+----------------------------------------------------+-------+
| table_id | name                                               | space |
+----------+----------------------------------------------------+-------+
|       54 | test/FTS_0000000000000030_0000000000000039_INDEX_1 |    40 |
|       55 | test/FTS_0000000000000030_0000000000000039_INDEX_2 |    41 |
|       56 | test/FTS_0000000000000030_0000000000000039_INDEX_3 |    42 |
|       57 | test/FTS_0000000000000030_0000000000000039_INDEX_4 |    43 |
|       58 | test/FTS_0000000000000030_0000000000000039_INDEX_5 |    44 |
|       59 | test/FTS_0000000000000030_0000000000000039_INDEX_6 |    45 |
|       51 | test/FTS_0000000000000030_BEING_DELETED            |    37 |
|       52 | test/FTS_0000000000000030_BEING_DELETED_CACHE      |    38 |
|       53 | test/FTS_0000000000000030_CONFIG                   |    39 |
|       49 | test/FTS_0000000000000030_DELETED                  |    35 |
|       50 | test/FTS_0000000000000030_DELETED_CACHE            |    36 |
|       45 | test/b#P#p0                                        |    31 |
|       46 | test/b#P#p1                                        |    32 |
|       47 | test/b#P#p2                                        |    33 |
|       21 | test/imptest                                       |     7 |
|       48 | test/opening_lines                                 |    34 |
|       20 | test/product                                       |     6 |
|       42 | test/t                                             |    28 |
+----------+----------------------------------------------------+-------+
18 rows in set (0.01 sec)

首先在数据库内创建一个有全文索引的表”opening_lines”,然后查看 innodb 的系统表信息。
类似 test/FTS_XXXXXXX_XXXXXXXX_INDEX_XX 的就是索引表。索引表以 FTS_ 为前缀,INDEX_XX 为后缀。表的命名规则如下:我们可以看到,opeining_lines 表对应的 table_id 是 48,48 转换为 16 进制为 30,而索引表的名称中第一个下划线后面也是跟的 00000..030,它们是相对应的。另外一个具有对应关系的是索引表名称中第二个下划线后面的值,此例中该值为 39,转换为 10 进制是 57,这个值为索引 ID。根据索引 ID 可查询到 table_id。

mysql> select index_id,name,table_id,space from INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE index_id=57;
+----------+------+----------+-------+
| index_id | name | table_id | space |
+----------+------+----------+-------+
|       57 | idx  |       48 |    34 |
+----------+------+----------+-------+
1 row in set (0.01 sec)

可以看到,查询出来的值所对应的 table_id 就是 opening_lines 所对应的 table_id。
全文索引将文档内的单词过滤出来存放到不同的索引表里,此操作可能在高并发的情况下产生大量的 I/O 操作,从而影响系统性能。为此,MySQL设计了全文索引缓存。该缓存存储最近插入的数据索引,当缓存存满时,再将索引数据批量写入到磁盘中。由于存在缓存,在事务中,MySQL对全文索引有特殊的处理方式,全文索引必须在事务提交后才会生效。可以参考以下例子:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO opening_lines(opening_line,author,title) VALUES
    -> ('Call me Ishmael.','Herman Melville','Moby-Dick'),
    -> ('A screaming comes across the sky.','Thomas Pynchon','Gravity\'s Rainbow'),
    -> ('I am an invisible man.','Ralph Ellison','Invisible Man'),
    -> ('Where now? Who now? When now?','Samuel Beckett','The Unnamable'),
    -> ('It was love at first sight.','Joseph Heller','Catch-22'),
    -> ('All this happened, more or less.','Kurt Vonnegut','Slaughterhouse-Five'),
    -> ('Mrs. Dalloway said she would buy the flowers herself.','Virginia Woolf','Mrs. Dalloway'),
    -> ('It was a pleasure to burn.','Ray Bradbury','Fahrenheit 451');
Query OK, 8 rows affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.02 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec

在提交前,查询不到关键词’Ishmael’,在提交后就查询到了。
哈希索引
启动哈希索引的参数为 innodb_adaptive_hash_index。哈希索引将 B-tree 索引树上的关键字进行哈希处理存放到哈希表上。哈希索引主要用于精确查找,如=,IN 等。像 LIKE 或通配符的查找不适合使用哈希索引。哈希索引由于是对索引关键字进行哈希操作,而变换之后的哈希值的大小关系无法与原始值相对应。所以哈希索引无法被用来避免数据的排序操作。


微视觉 , 版权所有丨如未注明 , 均为网络收集丨本网站采用BY-NC-SA协议进行授权 , 转载请注明细聊 MySQL 的 Innodb 存储引擎(完结篇)
喜欢 (7)
[wuwei967@126.com]
分享 (0)

您必须 登录 才能发表评论!