MySQL InnoDB索引原理分析和算法解密

select * from t where f='100'；

mysql> show engine innodb status\g;
*************************** 1. row ***************************
  type: innodb
  name: 
status: 
=====================================
2019-05-13 23:32:21 7f4875947700 innodb monitor output
=====================================
per second averages calculated from the last 32 seconds
...
-------------------------------------
insert buffer and adaptive hash index
-------------------------------------
ibuf: size 1, free list len 1226, seg size 1228, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
hash table size 276671, node heap has 1288 buffer(s)
0.16 hash searches/s, 16.97 non-hash searches/s

# 查看表的定义，可以看到id为主键，name为普通列
mysql> show create table dimensionsconf;
| table          | create table     
| dimensionsconf | create table `dimensionsconf` (
  `id` int(11) not null auto_increment,
  `name` varchar(20) default null,
  `remark` varchar(1024) not null,
  primary key (`id`),
  fulltext key `fullindex_remark` (`remark`)
) engine=innodb auto_increment=178 default charset=utf8 |
1 row in set (0.00 sec)

# 先测试一个非主键的name属性排序并查找，可以看到没有使用到任何索引，且需要filesort（文件排序），这里的rows为输出行数的预估值
mysql> explain select * from dimensionsconf order by name limit 10\g;
*************************** 1. row ***************************
           id: 1
  select_type: simple
        table: dimensionsconf
         type: all
possible_keys: null
          key: null
      key_len: null
          ref: null
         rows: 57
        extra: using filesort
1 row in set (0.00 sec)

# 再测试主键id的排序并查找，此时使用主键索引，在执行计划中没有了filesort操作，这就是聚集索引带来的优化
mysql> explain select * from dimensionsconf order by id limit 10\g;
*************************** 1. row ***************************
           id: 1
  select_type: simple
        table: dimensionsconf
         type: index
possible_keys: null
          key: primary
      key_len: 4
          ref: null
         rows: 10
        extra: null
1 row in set (0.00 sec)

# 再查找根据主键id的范围查找，此时直接根据叶子节点的上层节点就可以快速得到范围，然后读取数据
mysql> explain select * from dimensionsconf where id>10 and id<10000\g;
*************************** 1. row ***************************
           id: 1
  select_type: simple
        table: dimensionsconf
         type: range
possible_keys: primary
          key: primary
      key_len: 4
          ref: null
         rows: 56
        extra: using where
1 row in set (0.00 sec)

select * from blog where content like 'xxx%';

select * from blog where content like '%xxx%';

mysql> select * from information_schema.innodb_ft_default_stopword;
+-------+
| value |
+-------+
| a     |
| about |
| an    |
| are   |
| as    |
| at    |
| be    |
| by    |
| com   |
| de    |
| en    |
| for   |
| from  |
| how   |
| i     |
| in    |
| is    |
| it    |
| la    |
| of    |
| on    |
| or    |
| that  |
| the   |
| this  |
| to    |
| was   |
| what  |
| when  |
| where |
| who   |
| will  |
| with  |
| und   |
| the   |
| www   |
+-------+
36 rows in set (0.00 sec)

mysql> create fulltext index fullindex_remark on dimensionsconf(remark);
query ok, 0 rows affected, 1 warning (0.39 sec)
records: 0  duplicates: 0  warnings: 1

mysql> show warnings;
+---------+------+--------------------------------------------------+
| level   | code | message                                          |
+---------+------+--------------------------------------------------+
| warning |  124 | innodb rebuilding table to add column fts_doc_id |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> select remark from dimensionsconf where remark like '%baby%';
+-------------------+
| remark            |
+-------------------+
| a baby like panda |
| a baby like panda |
+-------------------+
2 rows in set (0.00 sec)

mysql> select remark from dimensionsconf where match(remark) against('baby' in natural language mode);
+-------------------+
| remark            |
+-------------------+
| a baby like panda |
| a baby like panda |
+-------------------+
2 rows in set (0.00 sec)

# 查看下执行计划，使用了全文索引排序
mysql> explain select * from dimensionsconf where match(remark) against('baby');
+----+-------------+----------------+----------+------------------+------------------+---------+------+------+-------------+
| id | select_type | table          | type     | possible_keys    | key              | key_len | ref  | rows | extra       |
+----+-------------+----------------+----------+------------------+------------------+---------+------+------+-------------+
|  1 | simple      | dimensionsconf | fulltext | fullindex_remark | fullindex_remark | 0       | null |    1 | using where |
+----+-------------+----------------+----------+------------------+------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> select id,remark,match(remark) against('baby') as relevance from dimensionsconf;
+-----+-----------------------+--------------------+
| id  | remark                | relevance          |
+-----+-----------------------+--------------------+
| 106 | c                     |                  0 |
| 111 | 运营商             |                  0 |
| 115 | a baby like panda     | 2.1165735721588135 |
| 116 | a baby like panda     | 2.1165735721588135 |
+-----+-----------------------+--------------------+
4 rows in set (0.01 sec)

# 代表必须有a baby短语，不能有man，可以有lik开头的单词，可以有panda，
select remark from dimensionsconf where match(remark) against('+"a baby" -man lik* panda' in boolean mode);

select * from articles where match(title,body) against('database' with query expansion);