MySQL面试连环问(一)

迈不过友情╰ 2022-09-15 13:58 158阅读 0赞

![9667456e9b07e9fd1321795b4f30b08e.png][]

若有收获，请记得分享转发和点**在看**哦

![3617300186aae4ad2b21a10f781db9f4.png][]

![61f50b26b1d640ec0ecd5b7734934152.png][]

能说下myisam 和 innodb的区别吗?

![5df37a96081fbe401a4e412bac909145.gif][]

myisam

myisam引擎是5.1版本之前的默认引擎，⽀持全⽂检索、压缩、空间函数等，但是不⽀持事务和⾏级锁，所以⼀般⽤于有⼤量查询少量插⼊的场景来使⽤，⽽且myisam不⽀持外键，并且索引和数据是分开存储的。

innodb

innodb是基于聚簇索引建⽴的，和myisam相反它⽀持事务、外键，并且通过MVCC来⽀持⾼并发，索引和数据存储在⼀起。

![6c875c1419d34850c2f4a881380a041a.png][]

说下MySQL有哪些索引吧?聚簇和⾮聚簇索引是什么?

![302c7913531a8d7531be016dff90e6bb.gif][]

索引按照数据结构来说主要包含B+树和Hash索引。

B+树索引

B+树是左⼩右⼤的顺序存储结构，节点只包含id索引列，⽽叶⼦节点包含索引列和数据，这种数据和索引在⼀起存储的索引⽅式叫做聚簇索引，⼀张表只能有⼀个聚簇索引。假设没有定义主键，InnoDB会选择⼀个唯⼀的⾮空索引代替，如果没有的话则会隐式定义⼀个主键作为聚簇索引。

![d54b0cf81309f1632c355c173b45c7cf.png][]

Hash索引

![85a69153e40030be19de7ba86c0c69ad.png][]

优点

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。

![ab104f6bbc5cf8f4104159125b0bf198.png][]

缺点

Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

Hash 索引无法被用来避免数据的排序操作。

Hash 索引不能利用部分索引键查询。

Hash 索引在任何时候都不能避免表扫描。

Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

聚簇索引

将数据存储与索引放到了一块，找到索引也就找到了数据。

注意:innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找(回表)。由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引。

非聚簇索引

将数据存储于索引分开的结构，索引结构的叶子节点指向了数据的对应行，myisam通过key\_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。

注意:非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

## **何时使用聚簇索引与非聚簇索引？** ##

![649c6ad2dfb09c757c775c2ce4dd9174.png][]

## **一个误区：把主键自动设为聚簇索引** ##

“

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻键值的页面可能相距甚远。如果你已经设置了主键为聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。此时其他索引只能被定义为非聚簇索引。这个是最大的误区。有的主键还是无意义的自动增量字段，那样的话Clustered index对效率的帮助，完全被浪费了。刚才说到了，聚簇索引性能最好而且具有唯一性，所以非常珍贵，必须慎重设置。一般要根据这个表最常用的SQL查询方式来进行选择，某个字段作为聚簇索引，或组合聚簇索引，这个要看实际情况。记住我们的最终目的就是在相同结果集情况下，尽可能减少逻辑IO。

”

[9667456e9b07e9fd1321795b4f30b08e.png]: /images/20220828/a0a2de6577ef4dc7a48af5edaee55977.png
[3617300186aae4ad2b21a10f781db9f4.png]: /images/20220828/2fd7de4392d240609e6eefa21f4f580a.png
[61f50b26b1d640ec0ecd5b7734934152.png]: /images/20220828/98d40c677c074bf2883600b140a77a65.png
[5df37a96081fbe401a4e412bac909145.gif]: /images/20220828/0ae74614ed054f2ca8d6b6088bff9730.png
[6c875c1419d34850c2f4a881380a041a.png]: /images/20220828/12f4a5d23553499ca94531661b5655fc.png
[302c7913531a8d7531be016dff90e6bb.gif]: /images/20220828/79e99927899a4b72a67b4fd5672da93b.png
[d54b0cf81309f1632c355c173b45c7cf.png]: /images/20220828/be026bbb1b2a4a4eb4cd3e7e01f793be.png
[85a69153e40030be19de7ba86c0c69ad.png]: /images/20220828/10798b366b454e23a0dcbb3a6525d0ec.png
[ab104f6bbc5cf8f4104159125b0bf198.png]: /images/20220828/302172bda81a413c967f3f8634e5f9a0.png
[649c6ad2dfb09c757c775c2ce4dd9174.png]: /images/20220828/bd55899817024bb69fa7d697bf235b6d.png