MySQL笔试面试部分知识点2

索引

1.什么是索引？

　　“索引（在MySQL中也叫“键key”）是存储引擎快速找到记录的一种数据结构。”

         ——《高性能MySQL》

 

2.索引优化

• 独立的列：索引不能是表达式的一部分，也不能是函数参数
• 覆盖索引（索引回表）：从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。
  使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作
• 索引顺序（选择性强）：将选择性最强的列放在前面
• 前缀索引（部分）：对于blog、text、varchar使用前缀索引，只索引开始部分字符

 

3.为何数据库索引使用B+树（平衡，多路，有序，磁盘块）

提升查找效率

B+树的父节点也必须存在于子节点中，是其中最大或者最小元素，B+树的节点只存储索引key值，具体信息的地址存在于叶子节点的地址中。这就使以页为单位的索引中可以存放更多的节点，减少更多的I/O支出。

注：B+Tree比B-Tree的优势：

IO次数少（B+树的非叶子节点不存放数据，同样大小的磁盘页可以容纳更多的节点元素），查询性能稳定（B+树的查询必须查找到叶子节点），范围查询简便（B-树需要中序遍历，B+树的所有叶子节点形成有序链表）

 

4.索引分类

 

聚集索引：主索引的叶子节点 data 域记录着完整的数据记录

辅助索引：辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到聚集索引中进行查找

Hash索引：

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

• 特性：
  1、Hash索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。
  2、Hash索引无法用于排序
  3、不支持部分索引列匹配查找
  4、只支持等值比较查询

 

5.索引缺点

 

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

 

 

6.索引失效情况

 

• 前导模糊查询不能使用索引
• 在where 子句中使用 != 或 <> 操作符
• 在where 子句中使用 or 来连接条件
• 对于多列索引，不是使用的第一部分，则不会使用索引（最左前缀原则）
• 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不会使用索引
• 如果需要进行 join 的字段两表的字段类型要相同
• 在字段上进行计算不能命中索引