1 INFORMATION_SCHEMA INNODB_SYS_INDEXES
The INFORMATION_SCHEMA INNODB_SYS_INDEXES Table
https://dev.mysql.com/doc/refman/5.7/en/information-schema-innodb-sys-indexes-table.html
The INNODB_SYS_INDEXES table has these columns:
INNODB_SYS_INDEXES 表有这些列：
INDEX_ID
An identifier for the index. Index identifiers are unique across all the databases in an instance.
索引的标识符。索引标识符在实例中的所有数据库中都是唯一的。
NAME
The name of the index. Most indexes created implicitly by InnoDB have consistent names but the index names are not necessarily unique. Examples: PRIMARY for a primary key index, GEN_CLUST_INDEX for the index representing a primary key when one is not specified, and ID_IND, FOR_IND, and REF_IND for foreign key constraints.
索引的名称。大多数由 InnoDB 隐式创建的索引具有一致的名称，但索引名称不一定是唯一的。示例：PRIMARY 用于主键索引，GEN_CLUST_INDEX 用于表示未指定主键的索引，ID_IND、FOR_IND 和 REF_IND 用于外键约束。
TABLE_ID
An identifier representing the table associated with the index; the same value as INNODB_SYS_TABLES.TABLE_ID.
表示与索引关联的表的标识符；与 INNODB_SYS_TABLES.TABLE_ID 相同的值。
TYPE
A numeric value derived from bit-level information that identifies the index type.
0 = nonunique secondary index;
1 = automatically generated clustered index (GEN_CLUST_INDEX);
2 = unique nonclustered index;
3 = clustered index;
32 = full-text index;
64 = spatial index;
128 = secondary index on a virtual generated column.
从标识索引类型的位级信息派生的数值。
0 = 非唯一二级索引；
1 = 自动生成的聚集索引 (GEN_CLUST_INDEX)；
2 = 唯一非聚集索引；
3 = 聚集索引；
32 = 全文索引；
64 = 空间索引；
128 = 虚拟生成列上的二级索引。
N_FIELDS
The number of columns in the index key. For GEN_CLUST_INDEX indexes, this value is 0 because the index is created using an artificial value rather than a real table column.
索引键中的列数。对于 GEN_CLUST_INDEX 索引，此值为 0，因为索引是使用人工值而不是真实表列创建的。
PAGE_NO
The root page number of the index B-tree. For full-text indexes, the PAGE_NO column is unused and set to -1 (FIL_NULL) because the full-text index is laid out in several B-trees (auxiliary tables).
索引 B 树的根页码。对于全文索引，PAGE_NO 列未使用并设置为 -1 (FIL_NULL)，因为全文索引布置在多个 B 树（辅助表）中。
SPACE
An identifier for the tablespace where the index resides. 0 means the InnoDB system tablespace. Any other number represents a table created with a separate .ibd file in file-per-table mode. This identifier stays the same after a TRUNCATE TABLE statement. Because all indexes for a table reside in the same tablespace as the table, this value is not necessarily unique.
索引所在的表空间的标识符。 0 表示 InnoDB 系统表空间。任何其他数字都表示在 file-per-table 模式下使用单独的 .ibd 文件创建的表。此标识符在 TRUNCATE TABLE 语句之后保持不变。因为一个表的所有索引都与该表位于同一个表空间中，所以这个值不一定是唯一的。
MERGE_THRESHOLD
The merge threshold value for index pages. If the amount of data in an index page falls below the MERGE_THRESHOLD value when a row is deleted or when a row is shortened by an update operation, InnoDB attempts to merge the index page with the neighboring index page. The default threshold value is 50%. For more information, see Section 14.8.12, “Configuring the Merge Threshold for Index Pages”.
索引页的合并阈值。如果在删除行或更新操作缩短行时索引页中的数据量低于 MERGE_THRESHOLD 值，InnoDB 会尝试将索引页与相邻索引页合并。默认阈值为 50%。有关更多信息，请参阅第 14.8.12 节，“为索引页配置合并阈值”。
2 索引的类型的例子
0 = 非唯一二级索引
经销商+订单号 是可以重复存在的，所以是非唯一二级索引
--创建表 CREATE TABLE `test` ( `id` int(11) unsigned NOT NULL COMMENT '主键ID', `order_no` int(11) DEFAULT '1', `batch_no` int(11) DEFAULT NULL, `c1` varchar(32) DEFAULT NULL, `c2` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unq_idx_batch_no` (`batch_no`), KEY `index_text` (`c1`,`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 0 = 非唯一二级索引； select t2.name, t1.* from INNODB_SYS_INDEXES t1 inner join INNODB_SYS_TABLES t2 on t1.table_id=t2.table_id where type =0 +-----------+----------+------------+----------+------+----------+---------+-------+-----------------+ | name | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD | +-----------+----------+------------+----------+------+----------+---------+-------+-----------------+ | test/test | 2342 | index_text | 1259 | 0 | 2 | 5 | 1314 | 50 | +-----------+----------+------------+----------+------+----------+---------+-------+-----------------+
索引展示
1 = 自动生成的聚集索引 (GEN_CLUST_INDEX)；
没有任何字段加索引。
CREATE TABLE `column_table` ( `id` int(11) NOT NULL, `c1` varchar(32) DEFAULT NULL, `c2` varchar(32) DEFAULT '' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; select t2.name, t1.* from information_schema.INNODB_SYS_INDEXES t1 inner join information_schema.INNODB_SYS_TABLES t2 on t1.table_id=t2.table_id where type =1 +-------------------+----------+-----------------+----------+------+----------+---------+-------+-----------------+ | name | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD | +-------------------+----------+-----------------+----------+------+----------+---------+-------+-----------------+ | test/column_table | 2357 | GEN_CLUST_INDEX | 1272 | 1 | 0 | 3 | 1327 | 50 | +-------------------+----------+-----------------+----------+------+----------+---------+-------+-----------------+ mysql> show index from column_table; Empty set (0.00 sec) mysql> show index from test; +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | test | 0 | PRIMARY | 1 | id | A | 1 | NULL | NULL | | BTREE | | | | test | 0 | unq_idx_batch_no | 1 | batch_no | A | 1 | NULL | NULL | YES | BTREE | | | | test | 1 | index_text | 1 | c1 | A | 1 | NULL | NULL | YES | BTREE | | | | test | 1 | index_text | 2 | c2 | A | 1 | NULL | NULL | YES | BTREE | | | +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 = 唯一非聚集索引；
batch_no 是个唯一索引。
CREATE TABLE `test` ( `id` int(11) unsigned NOT NULL COMMENT '主键ID', `order_no` int(11) DEFAULT '1', `batch_no` int(11) DEFAULT NULL, `c1` varchar(32) DEFAULT NULL, `c2` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unq_idx_batch_no` (`batch_no`), KEY `index_text` (`c1`,`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 2 = 唯一非聚集索引； -- UNIQUE KEY `uniq_idx_source_business_order_no` (`source`,`business_order_no`), select t2.name, t1.* from information_schema.INNODB_SYS_INDEXES t1 inner join information_schema.INNODB_SYS_TABLES t2 on t1.table_id=t2.table_id where type =2 and t2.name like 'test%' +-----------------+----------+------------------+----------+------+----------+---------+-------+-----------------+ | name | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD | +-----------------+----------+------------------+----------+------+----------+---------+-------+-----------------+ | test/table_test | 2349 | FTS_DOC_ID_INDEX | 1260 | 2 | 1 | 4 | 1315 | 50 | | test/test | 2341 | unq_idx_batch_no | 1259 | 2 | 1 | 4 | 1314 | 50 | +-----------------+----------+------------------+----------+------+----------+---------+-------+-----------------+ mysql> show index from test; +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | test | 0 | PRIMARY | 1 | id | A | 1 | NULL | NULL | | BTREE | | | | test | 0 | unq_idx_batch_no | 1 | batch_no | A | 1 | NULL | NULL | YES | BTREE | | | | test | 1 | index_text | 1 | c1 | A | 1 | NULL | NULL | YES | BTREE | | | | test | 1 | index_text | 2 | c2 | A | 1 | NULL | NULL | YES | BTREE | | | +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 = 聚集索引；
CREATE TABLE `test` ( `id` int(11) unsigned NOT NULL COMMENT '主键ID', `order_no` int(11) DEFAULT '1', `batch_no` int(11) DEFAULT NULL, `c1` varchar(32) DEFAULT NULL, `c2` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unq_idx_batch_no` (`batch_no`), KEY `index_text` (`c1`,`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 3 = 聚集索引； select t2.name, t1.* from information_schema.INNODB_SYS_INDEXES t1 inner join information_schema.INNODB_SYS_TABLES t2 on t1.table_id=t2.table_id where type =3 and t2.name like 'test/test%' +-----------+----------+---------+----------+------+----------+---------+-------+-----------------+ | name | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD | +-----------+----------+---------+----------+------+----------+---------+-------+-----------------+ | test/test | 2340 | PRIMARY | 1259 | 3 | 1 | 3 | 1314 | 50 | +-----------+----------+---------+----------+------+----------+---------+-------+-----------------+ mysql> show index from test; +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | test | 0 | PRIMARY | 1 | id | A | 1 | NULL | NULL | | BTREE | | | | test | 0 | unq_idx_batch_no | 1 | batch_no | A | 1 | NULL | NULL | YES | BTREE | | | | test | 1 | index_text | 1 | c1 | A | 1 | NULL | NULL | YES | BTREE | | | | test | 1 | index_text | 2 | c2 | A | 1 | NULL | NULL | YES | BTREE | | | +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
32=全文索引
全文检索用的比较少。如下的这个表的全文索引上也会有 【1 = 自动生成的聚集索引 (GEN_CLUST_INDEX)； 】
全文索引是在基于文本的列（CHAR、VARCHAR 或 TEXT 列）上创建的，以加快对这些列中包含的数据的查询和 DML 操作。
全文索引被定义为 CREATE TABLE 语句的一部分，或者使用 ALTER TABLE 或 CREATE INDEX 添加到现有表中。
全文搜索使用 MATCH() ... AGAINST 语法执行。 有关使用信息，请参阅第 12.10 节，“全文搜索功能”。
https://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html
CREATE TABLE `table_test` ( `id` int(11) NOT NULL, `c1` varchar(32) DEFAULT NULL, `c2` varchar(32) DEFAULT '', `c3` text, FULLTEXT KEY `idx_full_test` (`c1`,`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ALTER TABLE table_test ADD FULLTEXT (c1,c2); -- 32 全文索引 select t2.name, t1.* from information_schema.INNODB_SYS_INDEXES t1 inner join information_schema.INNODB_SYS_TABLES t2 on t1.table_id=t2.table_id where type =32 and t2.name like 'test/table_test%' +----------------------------------------------------+----------+-------------------------------------+----------+------+----------+---------+-------+-----------------+ | name | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD | +----------------------------------------------------+----------+-------------------------------------+----------+------+----------+---------+-------+-----------------+ | test/table_test | 2343 | GEN_CLUST_INDEX | 1260 | 1 | 0 | 3 | 1315 | 50 | | test/table_test | 2349 | FTS_DOC_ID_INDEX | 1260 | 2 | 1 | 4 | 1315 | 50 | | test/table_test | 2350 | idx_full_test | 1260 | 32 | 2 | -1 | 1315 | 50 | | test/FTS_00000000000004ec_BEING_DELETED | 2344 | FTS_COMMON_TABLE_IND | 1261 | 3 | 1 | 3 | 1316 | 50 | | test/FTS_00000000000004ec_BEING_DELETED_CACHE | 2345 | FTS_COMMON_TABLE_IND | 1262 | 3 | 1 | 3 | 1317 | 50 | | test/FTS_00000000000004ec_CONFIG | 2346 | FTS_COMMON_TABLE_IND | 1263 | 3 | 1 | 3 | 1318 | 50 | | test/FTS_00000000000004ec_DELETED | 2347 | FTS_COMMON_TABLE_IND | 1264 | 3 | 1 | 3 | 1319 | 50 | | test/FTS_00000000000004ec_DELETED_CACHE | 2348 | FTS_COMMON_TABLE_IND | 1265 | 3 | 1 | 3 | 1320 | 50 | | test/FTS_00000000000004ec_000000000000092e_INDEX_1 | 2351 | FTS_INDEX_TABLE_IND | 1266 | 3 | 2 | 3 | 1321 | 50 | | test/FTS_00000000000004ec_000000000000092e_INDEX_2 | 2352 | FTS_INDEX_TABLE_IND | 1267 | 3 | 2 | 3 | 1322 | 50 | | test/FTS_00000000000004ec_000000000000092e_INDEX_3 | 2353 | FTS_INDEX_TABLE_IND | 1268 | 3 | 2 | 3 | 1323 | 50 | | test/FTS_00000000000004ec_000000000000092e_INDEX_4 | 2354 | FTS_INDEX_TABLE_IND | 1269 | 3 | 2 | 3 | 1324 | 50 | | test/FTS_00000000000004ec_000000000000092e_INDEX_5 | 2355 | FTS_INDEX_TABLE_IND | 1270 | 3 | 2 | 3 | 1325 | 50 | | test/FTS_00000000000004ec_000000000000092e_INDEX_6 | 2356 | FTS_INDEX_TABLE_IND | 1271 | 3 | 2 | 3 | 1326 | 50 | +----------------------------------------------------+----------+-------------------------------------+----------+------+----------+---------+-------+-----------------+ mysql> show index from table_test; +------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | table_test | 1 | idx_full_test | 1 | c1 | NULL | 0 | NULL | NULL | YES | FULLTEXT | | | | table_test | 1 | idx_full_test | 2 | c2 | NULL | 0 | NULL | NULL | YES | FULLTEXT | | | +------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)
3 全文检索示例
MySQL 5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引，需要利用分词器把中文段落预处理拆分成单词，然后存入数据库。 MySQL 5.7.6开始，MySQL内置了ngram全文解析器，用来支持中文、日文、韩文分词。 本文使用的MySQL 版本是5.7.30，InnoDB数据库引擎。
- 自然语言模式(NATURAL LANGUAGE MODE)
自然语言模式是MySQL 默认的全文检索模式。自然语言模式不能使用操作符，不能指定关键词必须出现或者必须不能出现等复杂查询。
- 布隆模式(BOOLEAN MODE)
BOOLEAN模式可以使用操作符，可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询。
- 查询扩展(QUERY EXPANSION)
查询的结果不仅匹配出结果同时可以联想出其他你需要的结果。（类似关联查询，但是官网推荐仅支持短语查询 否则会出现很多脏数据）
-- mysql 于全文检索的demo
mysql> CREATE TABLE `articles` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(50) DEFAULT NULL COMMENT '主题',
`content` longtext NOT NULL COMMENT '内容',
PRIMARY KEY (`id`),
FULLTEXT KEY `title_content_index` (`content`,`title`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.20 sec)
mysql> INSERT INTO articles (`title`, `content`) VALUES
('如果','今生今世 永不再将你想起
除了
除了在有些个
因落泪而湿润的夜里 如果
如果你愿意'),
('爱情','有一天路标迁了希望你能从容
有一天桥墩断了希望你能渡越
有一天栋梁倒了希望你能坚强
有一天期待蔫了希望你能理解'),
('远和近','你 一会看我
一会看云
我觉得
你看我时很远
你看云时很近'),
('断章','你站在桥上看风景，
看风景人在楼上看你。
明月装饰了你的窗子，
你装饰了别人的梦。'),
('独语','我向你倾吐思念
你如石像
沉默不应
如果沉默是你的悲抑
你知道这悲抑
最伤我心');
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * from articles where match(content, title) against('风景' in NATURAL LANGUAGE MODE) LIMIT 10;
+----+--------+--------------------------------------------------------------------------------------------------------------------------+
| id | title | content |
+----+--------+--------------------------------------------------------------------------------------------------------------------------+
| 10 | 断章 | 你站在桥上看风景，
看风景人在楼上看你。
明月装饰了你的窗子，
你装饰了别人的梦。 |
+----+--------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
-- 自然语言模式（NATURAL LANGUAGE MODE）查询并得到评分 mysql> SELECT id, title, MATCH ( content, title ) against ( '风景' IN NATURAL LANGUAGE MODE ) AS score FROM articles; +----+-----------+--------------------+ | id | title | score | +----+-----------+--------------------+ | 7 | 如果 | 0 | | 8 | 爱情 | 0 | | 9 | 远和近 | 0 | | 10 | 断章 | 0.9771181344985962 | | 11 | 独语 | 0 | +----+-----------+--------------------+ 5 rows in set (0.02 sec) -- 布隆模式(BOOLEAN MODE) 可以组合查询 mysql> SELECT id, title FROM articles where MATCH ( content, title ) against ( '+风景 -爱情' IN BOOLEAN MODE ); +----+--------+ | id | title | +----+--------+ | 10 | 断章 | +----+--------+ 1 row in set (0.01 sec) -- 查询扩展(QUERY EXPANSION) 可以联想出其他结果 mysql> SELECT id, title FROM articles where MATCH ( content, title ) against ( '风景' WITH QUERY EXPANSION ); +----+--------+ | id | title | +----+--------+ | 10 | 断章 | | 11 | 独语 | +----+--------+ 2 rows in set (0.02 sec)
目前官网 MeCab Full-Text Parser 有支持日语的分词插件（可以更好的理解语义）
内置的 full-text parser 因为英文中单词的边界默认是空格，所以在处理英文文本时可以简单的使用空格作为分隔符。但是在处理中文时需要理解语义的基础上进行有效的分词，所以在处理中文、日文、韩文MySQL 提供了 ngram full-text （本文的配置就是 基于ngram的中文分词）
- 对比 like 查询效率有提升（具体提升的测试没有做）
- 全文搜索可以同时对多个字段做索引，like只能对单一字段搜索
对于中文的分词可能需要在理解语义的基础上才能有效的分词；比如上文中的 你好世界（hello world）对于英文按空格切分就可以，中文则需要理解语义的基础才能分成 你好/世界。
文章评论