MySQL面试题大全1

1.mysql的索引机制，什么是索引

索引的概念：

索引是对数据库表中一列或者多列的值进行排序的一种结构。

MYSQL索引存在的意义就是提高检索速度。

索引的优点：

• 大大的减少服务器扫描的数据量，加快检索速度。
• 避免排序和创建临时表
• 将随机IO变成顺序IO
• 索引对支持行级锁的InnoDB非常重要，查询锁更少元组，提高了并发性
• InnoDB在二级索引使用共享锁（读锁），但是访问主键索引需要排他锁（写锁）
• 通过创建唯一性索引，可以保证数据库表每一行数据唯一
• 可以加速表与表的连接
• 使用分组和排序子句进行检索时，可以减少查询中分组和排序的时间。
• 使用索引进而使用优化隐藏器，提高系统性能。

索引的缺点：

• 创建索引和维护索引需要耗费时间，随着数据量的增加而增加
• 需要占物理空间。如果建立聚簇索引，那么需要占用的空间会更大
• 对表数据进行增删改的时候也需要维护索引
• 包含太多重复数据的时候就失去了实际效果

何时创建索引？

• 经常搜索，作为主键，作为外键 范围搜索 排序 where

不该创建索引的情况

查询中很少使用的、修改多的、大量重复的

索引结构

B-Tree 树高一层就说明要多做一次IO操作

B树的特点：

• 关键字集合分布在整棵树中。
• 任何一个关键字只出现在一个节点中
• 搜索有可能在非叶子节点结束
• 关键字全集做一个二分查找
• 自动层次控制

B+Tree 的特点

• 所有关键字都出现在叶子结点的链表中（稠密索引），且恰好都是有序的
• 不可能在非叶子结点命中
• 非叶子结点相当于叶子结点索引（稀疏索引），叶子结点相当于存储关键字数据的数据层
• 每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。
• 更适合文件索引系统。

Hash ： 不支持范围查询

哈希索引就是一种哈希算法。把键值对换成新的哈希值。不需要像B+树那样子从根节点逐级查找。只需要一次哈希就可以找到位置，速度非常快。

补充：索引存储在文件系统中

索引是占用物理空间，在不同的存储引擎，索引存在的文件也不同。存储引擎是基于表的，以下分别是基于MyISAM和InnoDB存储引擎建立的两张表。

存储引擎为MyISAM：

• .frm：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
• .MYD：MyISAM DATA，用于存储MyISAM表的数据
• .MYI：MyISAM INDEX，用于存储MyISAM表的索引相关信息

存储引擎InnoDB

• .frm存储表相关的元数据信息都存放在frm中 。包括表的结构和定义信息等
• .ibd InnoDB的表数据和索引都保存在这里面

索引分类

逻辑分类：

按照功能划分：

• 主键索引：一张表只能有一个主键索引，不允许重复，不允许为NULL
• ALTER TABLE TableName ADD PRIMARY KEY(column_list)
• 唯一索引: 数据列不允许重复，允许为NULL值，一张表可以有多个唯一索引，索引列必须唯一，但允许有空值。如果是组合索引，列值组合必须唯一。
• CREATE UNIQUE INDEX indexName ON ‘TableName’(‘字段名’(length)); 或者
• ALTER TABLE TableName ADD UNIQUE (column_list)
• 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许NULL插入
• CREATE INDEX IndexName ON ‘TabelName’(‘字段名’(length)) 或者
• ALTER TABLE TableName ADD INDEX IndexName(‘字段名’(length));
• 全文索引 : 它查找的文本中的关键词，主要用于全文检索。

按列数划分

• 单例索引： 一个索引只包含一个列，一个表可以有多个单例索引
• 组合索引：一个索引包含两个或者两个以上的列。查询的时候遵循mysql组合索引的“最左前缀”原则，即使用where时条件要按照建立索引的时候字段的排列方式放置索引才会生效。

物理分类

分为聚簇索引和非聚簇索引（有时也称为辅助索引或者二级索引）

聚簇索引和非聚簇索引

聚簇是为了提高某个属性（或者属性组）的查询速度，把这个活儿这些属性（称为聚簇码）上具有相同值得元组集中存放在连续的物理块中。

聚簇索引（cluster index）不是单独的索引类型，而是一种数据存储方式。这种存储方式是依靠B+树来实现的，根据表的主键构造一颗B+树且B+树叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储和索引放到了一块，找到了索引也就找到了数据

非聚簇索引：数据和索引是分开的，B+树的叶子节点不是数据表行记录

虽然InnoDB和MyISAM存储引擎都是默认使用B+树结构存储索引，但是只有InnoDB的主键才是聚簇索引，InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。

聚簇索引优缺点：
优点：

• 数据访问更快，因为聚簇索引将索引和数据存储在一个B+树中，因此从聚簇索引中获取数据更快。
• 对于主键的排序查找和范围查找速度非常快

缺点：

• 依赖插入顺序。按照主键的顺序插入式最快的方式。
• 更新主键的代价很高。
• 二级索引访问需要两次索引查找。第一次找到主键，第二次找得到行数据。

InnoDB和MyISAM索引

InnoDB索引实现