赐我白日梦

赐我白日梦

关注
发信
关注(28)粉丝(399)

白日梦的MySQL专题（第38篇文章）8分钟回顾MySQL的索引

公众号首发-推荐阅读原文-格式更好看

点击阅读原文

点击阅读原文

点击阅读原文

一、导读

在MySQL中，不仅为主键创建的聚簇索引选用的数据结构是B+Tree，像辅助索引，二级索引、覆盖索引、联合索引等等其实都是B+Tree。

二、聚簇索引

MySQL默认为 int 类型的主键创建一个聚簇索引。这棵B+Tree是如何设计、如何长高可以参考上一篇文章。

https://mp.weixin.qq.com/s/TwcNEzEWg0PpN0Ra_kiTag

这颗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的执行计划选择去联合索引树中扫全表，也不会去聚簇索引中全表扫描。

白日梦的MySQL专题（第38篇文章）8分钟回顾MySQL的索引-LMLPHP

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之间的映射关系。

比如它的倒排索引表长下面这样：

解读上表：old这个单词在document1和doc4中出现过。 单词hot在doc2、doc5中出现过

full inverted index关联数据长下面这样

解读上表：单词code在doc1的第4个单词的位置上出现了。同理单词review也类似。

但是一般我们一说到全文检索或者是倒排索引往往都会直观的想到：Elasticsearch 这款NoSQL

因为InnoDB存储引擎的全文检索是存在限制的：

  • 每张表只能有一个全文检索的索引
  • 由多列组合而成的全文检索的索引列必须使用相同的字符集
  • 不支持没有单词界定符的语言，如：中文、日语、韩语
  1. MySQL的修仙之路，图文谈谈如何学MySQL、如何进阶！（已发布）
  2. 面前突击！33道数据库高频面试题，你值得拥有！（已发布）
  3. 大家常说的基数是什么？（已发布）
  4. 讲讲什么是慢查！如何监控？如何排查？（已发布）
  5. 对NotNull字段插入Null值有啥现象？（已发布）
  6. 能谈谈 date、datetime、time、timestamp、year的区别吗？（已发布）
  7. 了解数据库的查询缓存和BufferPool吗？谈谈看！（已发布）
  8. 你知道数据库缓冲池中的LRU-List吗？（已发布）
  9. 谈谈数据库缓冲池中的Free-List？（已发布）
  10. 谈谈数据库缓冲池中的Flush-List？（已发布）
  11. 了解脏页刷回磁盘的时机吗？（已发布）
  12. 用十一张图讲清楚，当你CRUD时BufferPool中发生了什么！以及BufferPool的优化！（已发布）
  13. 听说过表空间没？什么是表空间？什么是数据表？(已发布)
  14. 谈谈MySQL的：数据区、数据段、数据页、数据页究竟长什么样？了解数据页分裂吗？谈谈看！(已发布)
  15. 谈谈MySQL的行记录是什么？长啥样？(已发布)
  16. 了解MySQL的行溢出机制吗？(已发布)
  17. 说说fsync这个系统调用吧! (已发布)
  18. 简述undo log、truncate、以及undo log如何帮你回滚事物! (已发布)
  19. 我劝！这位年轻人不讲MVCC，耗子尾汁！ (已发布)
  20. MySQL的崩溃恢复到底是怎么回事？ (已发布)
  21. MySQL的binlog有啥用？谁写的？在哪里？怎么配置 (已发布)
  22. MySQL的bin log的写入机制 (已发布)
  23. 删库后！除了跑路还能干什么？(已发布)
  24. 自导自演的面试现场，趣学数据库的10种文件(已发布)
  25. 大型面试现场：一条update sql执行都经历什么？(已发布)
  26. 大型翻车现场：如何实现记录存在的话就更新，如果记录不存在的话就插入。(已发布)
  27. 视频+图文串讲：MySQL 行锁、间隙锁、Next-Key-Lock、以及实现记录存在的话就更新，如果记录不存在的话就插入如何保证并发安全(已发布)
  28. 自导自演的面试现场：说说char 和 varchar的区别你了解多少？。(已发布)
  29. 自导自演的面试现场之--你竟然不了解MySQL的组提交？。(已发布)
  30. 全网最清楚的：MySQL的insert buffer和change buffer 串讲(已发布)
  31. Double Write并不难理解
  32. 简述MySQL的三大范式
  33. 盘点各种登陆数据库的方式
  34. 面试现场：join联表的注意点、有哪些联表查询方式、真题...
    35.心里有点树
    36.心里有点B树
    37.聚簇索引到底是怎么回事？是如何长高的？
    38.8分钟回顾MySQL的索引
06-20 15:43
Powered by LMLPHP ©2024 赐我白日梦 0.045873