mysql常见面试题--索引

mysql常见面试题
索引：
什么是索引
索引就是一个数据结构，他是有序的排列，非叶子节点不储存数据，叶子节点使用指针连接，这样范围查找的很快，通过索引查询数据不用全表扫描，他只是查询索引列。

创建索引的几大原则：
1.查询频率高的列要加索引–因为他经常查询。
2.对排序和分组，还有联合查询频繁的字段创建索引–因为是经常查询
3.尽量选择主键和唯一索引—
4.一个表的索引不能过多，否则会影响insert、update、delete语句的执行速度
5.如果多个列需要创建索引，则选用联合索引

索引的种类：
• 普通索引: 即针对数据库表创建索引
• 唯一索引: 与普通索引类似，不同的就是：MySQL数据库索引列的值必须唯一，但允许有空值
• 主键索引: 它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引
• 组合索引: 为了进一步榨取MySQL的效率，就要考虑建立组合索引。即将数据库表中的多个字段联合起来作为一个组合索引

索引的基本原理：
索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。
索引的原理很简单，就是把无序的数据变成有序的查询把创建了索引的列的内容进行排序对排序结果生成倒排表在倒排表内容上拼上数据地址链在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

什么情况下无法走索引：
1.以%开头的模糊查询
2.数据类型出现了隐式转换
3.or这个字段的值都需要有索引，有一个没有索引的 他也不走索引
4.联合索引没用最左前缀
5.mysql优化器分析全表扫描比索引速度快，索引也会失效（这种情况很小）

索引的类型：
1.FULLTEXT：即为全文索引，其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"针对这类文本的模糊查询效率较低的问题。
2.HASH 由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
3.BTREE：BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
4.RTREE：RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。 相对于BTREE，RTREE的优势在于范围查找。

索引的优点：
1.索引可以减少了服务器需要扫描的数据量，提高扫描速度
2.索引可以帮助服务器避免排序和临时表
3.扫描可以将随机I\O变为顺序I\O
4.可以通过创建唯一索引来保证数据表中的每一行数据的唯一性唯一索引可以保证每一行的数据唯一性

索引的缺点：
1.维护索引需要耗费数据库资源
2.索引需要占用磁盘空间（因为单独找一块磁盘存放索引）
3.对表数据增删改时候，因为要维护索引，速度会受到影响（因为插入一条数据，导致索引的数据结构有变化，索引会重新排序）索引不是越多越好
创建索引时，需要对表加锁，在锁表的同时，可能会影响到其他的数据操作

什么情况下《适合》创建索引：
1.频繁的作为where条件语句查询的字段
2.排序字段也要创建索引
3.统计的字段可以创建索引
4.经常用于连接查询的列

什么情况下《不适合》创建索引：
1.频繁更新的字段不适合创建
2.表数据比较少的情况下
3.参与计算的列不适合建索引

什么是聚簇索引和非聚簇索引：
聚簇索引：他是把数据和索引放在一起，并且是按照一定的顺序组织的，找到索引也就找到了他的数据，数据的物理存放数据与索引顺序是一致的， 只要索引时相邻的，那么对应的数据一定也是相邻的存放在磁盘上
非聚簇索引：非聚簇索引叶子节点不存储数据，储存的是数据行地址，也就是说根据索引查找到数据行的位置再去磁盘查找数据。
优势：1.查找通过聚簇索引可以直接获取到数据，相比非聚簇索引需要第二次查询
2.聚簇索引对范围查询的效率高，因为数据是按照大小排列的
3.聚簇索引适合用在排序的场合，非聚簇索引不适合
劣势：1.维护索引的代价大，特别是插入新行或主键被更新导致要分页的时候，
3. 如果主键比较大，那辅助索引会变得更大，因为辅助索引的叶子节点存储的使主键值，过长的主键值会导致非叶子节点占用更多的物理空间

聚集索引与非聚集索引的区别：
一个表中只能拥有一个聚集索引，而非聚集索引一个表可以存在多个。
聚集索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；非聚集索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
聚集索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序；

什么是数据结构：
索引的数据结构有很多种，Mysql使用的索引是B+树结构：因为B+Tree的所有根节点都不带有任何数据信息，只有索引信息，所有数据信息全部存储在叶子节点里，B+Tree会以一个双向链表的形式将所有叶子节点的信息全部串联起来，这样，想遍历所有数据信息只需要顺序遍历叶子节点就可以了B+Tree的查询效率是非常稳定的，因为所有信息都存储在了叶子节点里面，从根节点到所有叶子节点的路径是相同的

索引为什么快：
索引的底层数据结构是用的B+树，所有的数据存放在叶子节点，枝节点和根节点都是存放的索引，而且枝节点和根节点常驻与内存，叶子节点的数据之间有类似于链表一样串联起来，非常适合范围查询，而且IO次数很少，大概2-3次就能查到数据，还有索引他是排好序的，所以索引查询的速度非常快

为什么不用hash结构呢：
hash结构是无序的，不能够提供范围性查找，比如我要查 >= 某个数的或者小于某个数的结果就不能实现，但是进行单个查找hash表是非常快的

B+树索引 hash索引 B树索引的区别：
B树：叶子节点和非叶子节点都存储数据，数据结构为有序数组+平衡多叉树。
B+树：只有在叶子节点存储数据，数据结构为有序数组链表+平衡多叉树。B+树索引的关键字索引效率比较平均，不像B树那样波动幅度大。在有大量重复键值的情况下，哈希索引效率也是极低的，因为存在哈希碰撞问题。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。
哈希索引：Hash索引仅仅能满足=和<=>等值查询，不能使用范围查询。哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快，但是Hash索引在任何时候都不能避免表扫描。

索引的算法：
索引算法有 BTree算法和Hash算法
BTree算法
BTree是最常用的mysql数据库索引算法，也是mysql默认的算法。因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量，
Hash算法
Hash Hash索引只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引

哈希索引不适用的场景：
不支持范围查询
不支持索引完成排序
不支持联合索引的最左前缀匹配规则

为什么用自增列作为主键：
where：根据id查询记录，因为id字段仅建立了主键索引，因此sql执行可选的索引只有主键索引，如果有多个，最终会选一个较优的作为检索的依据。
如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页，可以提高查询性能数据库自动编号，速度快，而且是增量增长，按顺序存放，对于检索非常有利数字型，占用空间小，易排序，在程序中传递也方便

为什么选择B+tree而不用B-tree：
首先B+树的数据都是存放在叶子节点，而非叶子节点存放的是索引和指针，这样查询起来只需要走三次IO就会找到数据，如果数据增多，他的树干高度也会比B树增加的缓慢，因为，B树是数据和索引放在一个数据块里，它会随着数据的增长，树形结构也会变高，这样查询会增加IO次数以及cpu等资源增高
为什么就偏要采用B+树呢：
二叉搜索树在极端情况下会演变成链表，红黑树以及B树虽然都有自平衡的功能，但是在数据量比较大的时候，其高度还是不够理想的，因此采用B树的变种B+树来作为底层的数据结构

key和index的区别：
1.key 是数据库的物理结构，它包含两层意义，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key, unique key, foreign key 等。
2.index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等；

Hash索引和B+树区别是什么：
B+树可以进行范围查询，Hash索引不能。
B+树支持联合索引的最左侧原则，Hash索引不支持。
B+树支持order by排序，Hash索引不支持。
Hash索引在等值查询上比B+树效率更高。
B+树使用like 进行模糊查询的时候，like后面（比如%开头）的话可以起到优化的作用，Hash索引根本无法进行模糊查询

为什么InnoDB不使用Hash表这种数据结构呢？毕竟Hash表的查找效率是非常高的：
如果采取Hash表作为底层数据结构的话，我们每次查询数据的时候只要根据Hash算法以及过滤条件的值就能一下子定位到所需的数据，但是对于Hash表来说，
他是不支持我们的范围查找的，一旦涉及到范围查找，就不得不进行一个全表的扫描，这对于我们在实际开发中的场景来说，这个问题是致命的。

B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据？
在B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引。 在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。
当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询

覆盖索引、回表等这些，了解过吗
覆盖索引： 查询列要被所建的索引覆盖，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
回表：二级索引无法直接查询所有列的数据，所以通过二级索引查询到聚簇索引后，再查询到想要的数据，这种通过二级索引查询出来的过程，就叫做回表。

索引的工作机制
数据结构的性能特点，决定了数据的检索性能，创建索引后会生成一个数据结构，他会储存着索引和磁盘地址，然后通过磁盘地址就会找到相对应的文件
如果没创建索引，就会执行全表扫描，如果创建了就会根据id快速的定位磁盘地址

怎么较少回表查询的次数？
，一般避免回表查询有两种，第一种就是尽量使用主键进行查找，另一种就是避免使用 * 查询，直接列出所需列名。
如果所需数据仅包含非主键索引列以及主键列，那么MySQL将直接返回数据，不进行回表。

索引的常见模型：
哈希表：一种以KV存储数据的结构，只适合等值查询，不适合范围查询。
有序数组：只适用于静态存储引擎，涉及到插入的时候比较麻烦。可以参考Java中的ArrayList。
搜索树：按照数据结构中的二叉树来存储数据，不过此时是N叉树(B+树)。广泛应用在存储引擎层中。
创建索引的时候锁表吗
这个说不好，因为不同的版本，还有我们用的RDS和开源的mysql都是有区别的，我在开源的mysql里面测试是不加锁的，可以查询可以插入，RDS我没有测试过，RDS需要在实际环境中测试
普通索引跟唯一索引查询性能
InnoDB的数据是按数据页为单位来读写的，默认每页16KB，因此这两种索引查询数据性能差别微乎其微。

唯一索引和普通索引哪个快change buffer：
普通索引用在更新过程的加速，更新的字段如果在缓存中，如果是普通索引则直接更新即可。如果是唯一索引需要将所有数据读入内存来确保不违背唯一性，所以尽量用普通索引

数据库自增主键可能遇到什么问题：
使用自增主键对数据库做分库分表，可能出现诸如主键重复等的问题。解决方案的话，简单点的话可以考虑使用UUID哈 自增主键会产生表锁，从而引发问题 自增主键可能用完问题。