MySQL索引知识点及面试常问题

索引的功能

　　索引可以大幅增加数据库的查询的性能，在实际业务场景中，或多或少都会使用到。

 

但是索引是有如下 2 个代价的:

  1. 需要额外的磁盘空间来保存索引
  2. 对于插入、更新、删除等操作由于更新索引会增加额外的开销

因此索引比较适合用在读多写少的场景。

 

MySQL 的索引类型及实现

索引类型

共分为5类：

  • 唯一索引：索引列中的值必须是唯一的，但是允许出现空值。这种索引一般用来保证数据的唯一性，比如保存账户信息的表，每个账户的id必须保证唯一，如果重复插入相同的账户id时会MySQL返回异常。

  • 主键索引：特殊的唯一索引，不允许出现空值。

  • 普通索引：与唯一索引不同，允许索引列中存在相同的值。例如学生的成绩表，各个学科的分数是允许重复的，就可以使用普通索引。

  • 联合索引：由多个列共同组成的索引。一个表中含有多个单列的索引并不是联合索引，联合索引是对多个列字段按顺序共同组成一个索引。应用联合索引时需要注意最左原则，就是 Where 查询条件中的字段必须与索引字段从左到右进行匹配。比如，一个用户信息表，用姓名和年龄组成了联合索引，如果查询条件是姓名等于张三，那么满足最左原则；如果查询条件是年龄大于20，由于索引中最左的字段是姓名不是年龄，所以不能使用这个索引。

  • 全文索引：MyISAM 引擎中实现了这个索引，在 5.6版 本后 InnoDB 引擎也支持了全文索引，并且在 5.7.6 版本后支持了中文索引。全文索引只能在 CHAR,VARCHAR,TEXT 类型字段上使用，底层使用倒排索引实现。要注意对于大数据量的表，生成全文索引会非常消耗时间也非常消耗磁盘空间。

 

索引实现

索引实现共分4种形式：

  • B+ 树实现：B+ 树比较适合用作 '>' 或 '<' 这样的范围查询，是 MySQL 中最常使用的一种索引实现。

  • R-tree：是一种用于处理多维数据的数据结构，可以对地理数据进行空间索引。不过实际业务场景中使用的比较少。

  • Hash：是使用散列表来对数据进行索引，Hash 方式不像 Btree 那样需要多次查询才能定位到记录，因此Hash索引的效率高于 B-tree ，但是不支持范围查找和排序等功能.实际使用的也比较少。

  • FullText：就是我们前面提到的全文索引，是一种记录关键字与对应文档关系的倒排索引。

 

常见面试题

引入索引相关的常见的面试题，更客观的学习索引相关的内容

 

问：数据库中最常见的慢查询优化方式是什么？

  • 同学A：加索引。

问：为什么加索引能优化慢查询？

  • 同学A：...不知道
  • 同学B：因为索引其实就是一种优化查询的数据结构，比如 MySQL 中的索引是用 B+ 树实现的，而 B+ 树就是一种数据结构，可以优化查询速度，可以利用索引快速查找数据，所以能优化查询。

问：哪些数据结构可以提高查询速度？（听到这个问题就感觉此处有坑...）

  • 同学B：哈希表、完全平衡二叉树、B树、B+树等等。

问：那这些数据结构既然都能优化查询速度，那 MySQL 为何选择使用 B+ 树？

  • 同学B：...不知道（下面会解答）

问：有一个 titles 表，主键由 empno，title，fromdate 三个字段组成。那么以下几个语句会用到索引吗?

select * from employees.titles where emp_no=1
select * from employees.titles where title='1'
select * from employees.titles where emp_no='1'andtitle=1
select * from employees.titles where title='1'andemp_no=1

问：哈希表、完全平衡二叉树、B树、B+树都可以优化查询，为何 MySQL 独独喜欢 B+ 树？哈希表有什么特点？

  • 答：假如有这么一张表(表名：sanguo)：                                                                                                                                                                        

　　现在对name字段建立哈希索引，注意字段值所对应的数组下标是哈希算法随机算出来的，所以可能出现哈希冲突。那么对于这样一个索引结构，现在来执行下面的 SQL 语句：

select * from sanguo where name='周瑜'

　　可以直接对 ‘周瑜’ 按哈希算法算出来一个数组下标，然后可以直接从数据中取出数据并拿到锁对应那一行数据的地址，进而查询那一行数据。那么如果现在执行下面的 SQL 语句：

select * from sanguo where name>'周瑜'

　　则无能为力，因为哈希表的特点就是可以快速的精确查询，但是不支持范围查询。

 

如果用完全平衡二叉树呢？

　　还是上面的表数据用完全平衡二叉树表示如下图（为了简单，数据对应的地址就不画在图中了。）：

　　图中的每一个节点实际上应该有四部分：

  • 左指针，指向左子树
  • 键值
  • 键值所对应的数据的存储地址
  • 右指针，指向右子树

　　另外需要提醒的是，二叉树是有顺序的，简单的说就是“左边的小于右边的”假如我们现在来查找‘周瑜’，需要找2次（第一次曹操，第二次周瑜），比哈希表要多一次。而且由于完全平衡二叉树是有序的，所以也是支持范围查找的。

 

如果用B树呢？

　　还是上面的表数据用B树表示如下图（为了简单，数据对应的地址就不画在图中了。）：

 

　　可以发现同样的元素，B树的表示要比完全平衡二叉树要“矮”，原因在于B树中的一个节点可以存储多个元素。

 

如果用B+树呢？

　　还是上面的表数据用B+树表示如下图（为了简单，数据对应的地址就不画在图中了。）

 我们可以发现同样的元素，B+树的表示要比B树要“胖”，原因在于B+树中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连。

 

那么B+树到底有什么优势呢？

　　这里我们用“反证法”，假如我们现在就用完全平衡二叉树作为索引的数据结构，我们来看一下有什么不妥的地方。实际上，索引也是很“大”的，因为索引也是存储元素的，我们的一个表的数据行数越多，那么对应的索引文件其实也是会很大的，实际上也是需要存储在磁盘中的，而不能全部都放在内存中，所以我们在考虑选用哪种数据结构时，我们可以换一个角度思考，哪个数据结构更适合从磁盘中读取数据，或者哪个数据结构能够提高磁盘的IO效率。回头看一下完全平衡二叉树，当我们需要查询“张飞”时，需要以下步骤：

  1. 从磁盘中取出“曹操”到内存，CPU从内存取出数据进行笔记，“张飞”<“曹操”，取左子树（产生了一次磁盘IO）
  2. 从磁盘中取出“周瑜”到内存，CPU从内存取出数据进行笔记，“张飞”>“周瑜”，取右子树（产生了一次磁盘IO）
  3. 从磁盘中取出“孙权”到内存，CPU从内存取出数据进行笔记，“张飞”>“孙权”，取右子树（产生了一次磁盘IO）
  4. 从磁盘中取出“黄忠”到内存，CPU从内存取出数据进行笔记，“张飞”=“张飞”，找到结果（产生了一次磁盘IO）

　　同理，回头看一下B树，我们发现只发送三次磁盘IO就可以找到“张飞”了，这就是B树的优点：一个节点可以存储多个元素，相对于完全平衡二叉树所以整棵树的高度就降低了，磁盘IO效率提高了。

　　而B+树是B树的升级版，只是把非叶子节点冗余一下，这么做的好处是为了提高范围查找的效率。

　　到这里可以总结出来，Mysql选用B+树这种数据结构作为索引，可以提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率，并且B+树里的元素也是有序的。

那么，一个B+树的节点中到底存多少个元素合适呢？

　　其实也可以换个角度来思考B+树中一个节点到底多大合适？

　　答案是：B+树中一个节点为一页或页的倍数最为合适。因为如果一个节点的大小小于1页，那么读取这个节点的时候其实也会读出1页，造成资源的浪费；如果一个节点的大小大于1页，比如1.2页，那么读取这个节点的时候会读出2页，也会造成资源的浪费；所以为了不造成浪费，所以最后把一个节点的大小控制在1页、2页、3页、4页等倍数页大小最为合适。

那么，Mysql中B+树的一个节点大小为多大呢？

　　这个问题的答案是“1页”，这里说的“页”是Mysql自定义的单位（其实和操作系统类似），Mysql的Innodb引擎中一页的默认大小是16k（如果操作系统中一页大小是4k，那么Mysql中1页=操作系统中4页），可以使用命令SHOW GLOBALSTATUS like 'Innodbpagesize'; 查看。并且还可以告诉你的是，一个节点为1页就够了。

为什么一个节点为1页（16k）就够了？

　　解决这个问题，我们先来看一下Mysql中利用B+树的具体实现。

　　Mysql中MyISAM和innodb使用B+树

　　通常我们认为B+树的非叶子节点不存储数据，只有叶子节点才存储数据；而B树的非叶子和叶子节点都会存储数据，会导致非叶子节点存储的索引值会更少，树的高度相对会比B+树高，平均的I/O效率会比较低，所以使用B+树作为索引的数据结构，再加上B+树的叶子节点之间会有指针相连，也方便进行范围查找。上图的data区域两个存储引擎会有不同。

 

B+ 树

 MyISAM 中的 B+ 树

　　MYISAM 中叶子节点的数据区域存储的是数据记录的地址，MyISAM 存储引擎在使用索引查询数据时，会先根据索引查找到数据地址，再根据地址查询到具体的数据。并且主键索引和辅助索引没有太多区别。

主键索引

 

 

辅助索引

　　

 

 InnoDB 中的 B+ 树

　　 InnoDB 中主键索引的叶子节点的数据区域存储的是数据记录，辅助索引存储的是主键值

主键索引

辅助索引

 

　　InnoDB 中的主键索引和实际数据时绑定在一起的，也就是说 InnoDB 的一个表一定要有主键索引，如果一个表没有手动建立主键索引，InnoDB 会查看有没有唯一索引，如果有则选用唯一索引作为主键索引，如果连唯一索引也没有，则会默认建立一个隐藏的主键索引（用户不可见）。另外，InnoDB 的主键索引要比 MyISAM 的主键索引查询效率要高（少一次磁盘 IO），并且比辅助索引也要高很多。所以，我们在使用 InnoDB 作为存储引擎时，我们最好：

  1. 手动建立主键索引
  2. 尽量利用主键索引查询

 

回到我们的问题：为什么一个节点为1页（16k）就够了？

　　对着上面 MySQL 中 InnoDB  中对 B+ 树的实际应用（主要看主键索引），可以发现 B+ 树中的一个节点存储的内容是：

  • § 非叶子节点：主键+指针
  • § 叶子节点：数据

 

　　假设我们一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页里就可以存储16K/14=1170个(主键+指针)，那么一颗高度为2的B+树能存储的数据为：117016=18720条，一颗高度为3的B+树能存储的数据为：11701170*16=21902400（千万级条）。所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。所以也就回答了我们的问题，1页=16k这么设置是比较合适的，是适用大多数的企业的，当然这个值是可以修改的，所以也能根据业务的时间情况进行调整。

 

最左前缀原则

　　我们模拟数据建立一个联合索引 select *,concat(right(emp_no,1),"-",right(title,1),"-",right(from_date,2)) from employees.titles limit 10;

 

　　我们判断一个查询条件能不能用到索引，我们要分析这个查询条件能不能利用某个索引缩小查询范围

　　对于 select from employees.titles where emp_no=1是能用到索引的，因为它能利用上面的索引所有查询范围，首先和第一个节点“4-r-01”比较，1<4，所以可以直接确定结果在左子树，同理，依次按顺序进行比较，逐步可以缩小查询范围。对于select from employees.titles where title='1'是不能用到索引的，因为它不能用到上面的所以，和第一节点进行比较时，没有empno这个字段的值，不能确定到底该去左子树还是右子树继续进行查询。对于 select * from employees.titles where title='1' and emp_no=1是能用到索引，按照我们的上面的分析，先用title='1'这个条件和第一个节点进行比较，是没有结果的，但是mysql会对这个sql进行优化，优化之后会将empno=1这个条件放到第一位，从而可以利用索引。

 

 

 

 

 

             

posted @ 2019-05-27 14:01  ''竹先森゜  阅读(2103)  评论(0)  编辑  收藏  举报