mysql InnoDB索引详解

1.索引类型

1. B+树索引(常用)
2. 哈希索引
3. 全文索引

1.B+树索引

1. B+树索引的B指的是balance平衡，因为B+树是由平衡树演化而来
2. B+树并不能找到给定键值的具体行。B+树索引能找到的是只是相应叶子结点上即数据页，找到数据页之后再把数据页放到内存中，再在内存中查找数据，最后找到数据行。

B+树索引分类

1. 聚集索引: 按照每一张表的主键构造的B+树，同时叶子结点中存放的是整张表的行记录数据，也将聚集索引的叶子结点称为数据页。每一个数据页通过双向链表来链接。一张表只有这一个聚集索引。
2. 辅助索引: 叶子结点并不包含行记录的所有数据。每个叶子结点包含键值(索引列的值)和书签，此书签是相应行数据的聚集索引键值。辅助索引的存在不影响数据在聚集索引中的组织，因此每张表可以有多个辅助索引。 查找过程: 当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。
3. 覆盖索引: 从辅助索引中就可以得到查询的记录，而不需要查询主键索引中的记录。
   好处1: 辅助索引不包含整行记录的所有信息，故其大小远小于主键索引，因此可以减少大量IO操作
   好处2：针对一些统计问题而言 eg: select count(*) from tableName
4. 联合索引: 对表中多个列进行索引

B+树索引的创建

！！！ 建议 在建表的时候就创建索引！！
建表时创建索引: CREATE TABLE tb_stu_info (id INT NOT NULL,
name CHAR(45) DEFAULT NULL,
dept_id INT DEFAULT NULL,
age INT DEFAULT NULL,
height INT DEFAULT NULL,
INDEX(height) );
show index from 表名称; 显示表的所有索引
创建B+树索引: create index 索引名称 on 表名称(列名); 多列用,隔开
eg： create index nameindex on example(name);
删除表索引: drop index index_name on example;
！！！ 注意: 由于表只有一个聚集索引，故创建的一般都是非聚集索引

B+树索引失效场景

1. like ‘%XX’ 如果没有指定列名，则会失效。解决: 1.对应的列建索引，走辅助索引，select 后 写出列名。参考大神博客 2.使用全文索引(注意不支持中文检索)
2. 联合索引需要遵循最佳左前缀原则(b+树是按照从左到右的顺序来建立搜索树的 参考大神博客)
3. 数据类型中出现隐式转换时不会用到索引。
4. 用or分隔的条件，如果or前条件中的列有索引，而后面的列没有索引，那么涉及的索引都不会被用到(前面的索引也不会用到)
5. 如果Mysql估计使用索引比全表扫描更慢，则不使用索引。
   参考 大神博客

索引优化 MRR和ICP

MRR Multi-Range Read 优化: 优化点: 减少磁盘随机访问，并且将随机访问转化为较为顺序的数据访问。
好处:

1. MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
2. 减少缓冲池中页被替换的次数。
3. 批量处理键值对的查询操作。

对于InnoDB和MyIsAM存储引擎的范围查找和join查询操作，MRR的工作方式:

1. 将查询得到的辅助索引键值存放到一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
2. 将缓存中的键值根据RowID进行排序。
3. 根据RowID的排序顺序来访问实际的数据文件。

ICP(5.6之后默认开启) 索引下推: mysql数据库会在取出索引的同时，判断是否可以进行where条件的过滤，也就是将where的部分过滤操作放到了存储引擎层。

2.哈希索引

这是数据库自身创建并使用的，DBA本身不能进行干预。自适应哈希索引经哈希函数映射到一个哈希表中，因此对于字典类型的查找非常快速。eg: select * from table where index_col = ‘XXX’

3.全文索引

场景: select * from 表名 where body like ‘%XXX%’; B+树索引一般是失效的

全文检索是将存储于数据库中的整本书或者整篇文章中的任意内容信息查找出来的技术。
全文检索是采用倒排索引来实现的。InnoDB采用 full inverted index(表现形式为{单词,(单词所在文档ID,在具体文档的位置)}) 的方式。
FTS Index Cache(全文检索缓存)，用来提高全文检索性能，是红黑树结构，默认大小为32M

创建方式

create fulltext index 索引名称 on 表名称(列名称)
eg: create fulltext index index_name on example(name);

InnoDB全文索引限制

1. 每张表只能有一个全文检索的索引
2. 由多列组合而成的全文检索的索引列必须使用相同的字符集和排序规则
3. 不支持没有单词界定符的语言，如中文，日语，韩文等

mysql全文检索的使用

注意: mysql 全文检索不能检索中文 详情查看大神博客

1. Natural Language
   match(字段名称) again(‘XXXX’ in Natural Language mode)
   in Natural Language mode 可省略 mysql默认采用Natural Language mode
   eg: explain select * from example where match(name) against (‘zhangsan’)

thanks!!!