

Hi,大家好!我是白日梦!
本文是MySQL专题的第 38 篇,下一篇以视频的方式跟大家分享执行计划。
欢迎关注!持续更新中~
目录
一、导读 二、聚簇索引 三、二级索引 四、联合索引 4.1、什么是联合索引 4.2、左前缀原则 4.3、联合索引的分组&排序 五、覆盖索引 六、倒排索引 七、推荐阅读
一、导读
在MySQL中,不仅为主键创建的聚簇索引选用的数据结构是B+Tree,像辅助索引,二级索引、覆盖索引、联合索引等等其实都是B+Tree。
二、聚簇索引
MySQL默认为 int 类型的主键创建一个聚簇索引。这棵B+Tree是如何设计、如何长高可以点击参考上一篇文章。
这颗B+Tree之所以叫做聚簇索引是因为它的叶子节点中存储的是完整的数据行,也就是说你拿着id从这棵树的根索引上检索,一直到叶子节点并且定位到特定的数据页后,你是可以去除完整的数据行来的!(所有列都有)
三、二级索引
二级索引也被大家称为辅助索引,其实每个索引都是对应一棵独立的B+Tree,而且他们都有这个特性:后面的数据页中的索引值均比它前面的数据页中的索引值大,并且都会通过页分裂的机制保证这个特性一致成立。
不同的是不同索引的叶子节点中存储的数据是不一样的!对于二级索引来说它的叶子节点中存储的不再是完整的数据行,而是id值。
比如表里面有 id、age、name、addr四列,且name列是二级索引。然后你的SQL是这样的
select * from table
where name = 'tom';复制
那就会先扫描name列这颗B+Tree,找到name=‘tom’所在的叶子节点,叶子节点中存储的只有name = ‘tom’的这行数据在表中的id值。于是再拿着这个id值去聚簇索引中重新查询,这个动作我们称为:“回表”
你可以像下面这样创建二级索引
CREATE INDEX [index name] ON [table name]([column name]);
复制
或者
ALTER TABLE [table name] ADD INDEX [index name]([column name]);
复制
四、联合索引
4.1、什么是联合索引
联合索引也叫复合索引,说白了就是多个字段一起组合成一个索引。
像下面这样使用 id + title 组合在一起构成一个联合索引
CREATE TABLE `text` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=3691 DEFAULT CHARSET=utf8
# 或者通过这种方式添加联合索引
alter table text add INDEX `t3_index_title_content` (`title`,`content`);复制
如果我们像上图那样创建了索引,我们只要保证 id+title 两者结合起来全局唯一就ok 建立联合索引同样是需要进行排序的,排序的规则就是按照联合索引所有列组成的字符串的之间的先后顺序进行排序,,如a比b优先。
4.2、左前缀原则
使用联合索引进行查询时一定要遵循左前缀原则。
什么是左前缀原则呢?
就是说想让索引生效的话,一定要添加上第一个索引,只使用第二个索引进行查询的话会导致索引失效。
比如上面创建的联合索引,假如我们的查询条件是 where id = '1' 或者 where id = '1' and title = '唐诗宋词' 索引都会不失效。
但是如果我们不使用第一个索引id,像这样 where title = '唐诗' ,结果就是导致索引失效。
问:如果我不遵循做前缀原则,一定不能使用聚簇索引吗?
回答:不是的!可以看下面的例子:
# t3表中有3个索引,如下:
# id:聚簇索引
# x1:唯一的二级索引
# x1_x3_x2:联合索引
explain select * from t3 where x2 = 'fdc1a9f7d94ece2b68b7d3e3be1b0f3b';复制
可以看到,x2列没有单独的索引。但是sql的执行计划选择去联合索引树中扫全表,也不会去聚簇索引中全表扫描。

这里只需要大概看懂这个执行计划就ok,下一讲详细讲!
4.3、联合索引的分组&排序
还是使用这个例子:
CREATE TABLE `text` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=3691 DEFAULT CHARSET=utf8复制
demo1: 当我们像下面这样写sql时,就会先按照id进行排序。当id相同时再按照title进行排序。
select * form text order by id, title;
复制
demo2: 当我们像下面这样写sql时,就会先将id相同的划分为一组,再将title相同的划分为一组。
select id,title form text group by id, title;
复制
demo3: ASC和DESC混用, 其实大家都知道底层使用B+树,本身就是有序的。要是不加限制的话,默认就是ASC。反而是混着使用就使得索引失效。
select * form text order by id ASC, title DESC;
复制
另外补充一点:如果你的group by xxx列,这一列没有索引时,mysql会 Using temporary 也就是中间表来实现你的分组操作,效率是很低的!而如果有索引的话,直接走索引就可以实现 group by。
五、覆盖索引
覆盖索引其实和二级索引没啥区别,只不过是查询方式不同而让它省去了回表的操作而已。
还是这个例子:
比如表结构是这样的
CREATE TABLE `text` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`title`,`content`)
) ENGINE=InnoDB AUTO_INCREMENT=3691 DEFAULT CHARSET=utf8复制
然后你的SQL是这样的
select content from table
where title = 'all in';复制
你会发现,其实select中期望得到的内容已经全部存在于辅助索引中了,所以不需要再使用id进行回表操作也能得到正确的返回值。
这其实在一定程度上也说明了别总是动不动就select *
,能走覆盖索引尽量使用覆盖索引。哪怕是不得不进行一次回表操作也尽量使用limit、where条件限制一下!
六、倒排索引
InnoDB中是存在倒排索引和全文检索的概念的!
MySQL的inverted index同B+Tree索引一样。另外会使用一张辅助表来存储单词和document之间的映射关系。
比如它的倒排索引表长下面这样:
Number | Text | Documents |
---|---|---|
1 | old | 1,4 |
2 | hot | 2,5 |
解读上表:old这个单词在document1和doc4中出现过。单词hot在doc2、doc5中出现过
full inverted index关联数据长下面这样
Number | Text | Documents |
---|---|---|
1 | code | (1,4), (2,5) |
2 | review | (3,5),(5,8) |
解读上表:单词code在doc1的第4个单词的位置上出现了。同理单词review也类似。
但是一般我们一说到全文检索或者是倒排索引往往都会直观的想到:Elasticsearch 这款NoSQL
因为InnoDB存储引擎的全文检索是存在限制的:
每张表只能有一个全文检索的索引 由多列组合而成的全文检索的索引列必须使用相同的字符集 不支持没有单词界定符的语言,如:中文、日语、韩语
七、欢迎关注白日梦!
八、推荐阅读
1、MySQL的修仙之路,图文谈谈如何学MySQL、如何进阶!
2、数据库面经,常见的面试题
6、能谈谈year、date、datetime、time、timestamp的区别吗?
10、了解Flush-List吗?顺便说一下脏页的落盘机制!
11、用 11 张图讲清楚,当你CRUD时BufferPool中发生了什么!以及BufferPool的优化!
16、简述undo log、truncate、以及undo log如何帮你回滚事务?
19、LSN、Checkpoint?谈谈MYSQL的崩溃恢复是怎么回事!
20、MySQL的 bin log有啥用?在哪里?谁写的?怎么配置?
21、bin log有哪些格式?有啥区别?优缺点?线上用哪种格式?
24、自导自演的数据库面试现场--谈谈MySQL的10种文件
25、大型面试现场:一条update sql的执行都经历了什么?
26、大型翻车现场:如何实现记录存在的话就更新,如果记录不存在的话就插入。
27、如何实现记录存在的话就更新,如果记录不存在的话就插入--续
28、面试现场:说说char和varchar的区别你了解多少?
29、沙尘暴也阻挡不了学习的脚步-- 面试官:你竟然不知道MySQL的组提交?
长按二维码、关注白日梦
赠送精美脑图(操作系统、网络、Java基础...)
长按二维码
关注白日梦吧!