面向面试知识--MySQL数据库与索引

面向面试知识–MySQL数据库与索引

优化难点与面试点

什么是MySQL索引？

索引的MySQL官方定义：索引是帮助MySQL快速获取数据的数据结构。

动力节点原文：
MysQL官方对于索引的定义:索引是帮助MySQL高效获取数据的数据结构。
MysQL在存储数据之外，数据库系统中还维护着满足特定查找算法的数据结构，这些数据结构以某种引用(指向)表中的数据,这样我们就可以通过数据结构上实现的高级查找算法来快速找到我们想要的数据。而这种数据结构就是索引。
简单理解为：“排好序的，帮助我们快速查找数据的数据结构”

索引的分类

逻辑分类：

按照功能划分

1. 主键索引：一张表只能有一个主键索引，不允许重复、不允许为null；
2. 唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
3. 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；（阿里开发者手册：高并发场景的表，索引数量控制在五个以内）
4. 全文索引：它查找的是文本中的关键词，主要用于全文检索。（篇幅较长，下文有独立主题说明）

按照列数划分

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

物理分类：

聚簇索引

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

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

• 数据访问速度快，因为聚簇索引将索引和数据都放在同一棵B+树，因此聚簇索引中获取数据比飞聚簇索引更快；
• 聚簇索引对于主键的排序查找和范围查找速度非常快。
  缺点
  （删除和插入慢）
• 插入速度严重依赖于插入顺序；插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键（主键列不要选没有意义的自增列，选经常查询的条件列才好，不然无法体现其主键索引性能）；
• 更新主键代价很高（不推荐更改主键）；更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
• 二级索引需要两次索引查找（回表）；二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

补充：MySQL中，key、primary key、unique key、与index的区别。
详见文章一文搞懂MySQL索引（清晰明了）

非聚簇索引

数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录。
虽然InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引，但是只有InnoDB的主键索引才是聚簇索引，InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。

InnoDB和MyISAM索引实现，索引的数据结构

InnoDB和MyISAM的区别

因为MyISAM不是聚簇索引，所以可能某条记录在多个B+树索引中出现，很难实现行级锁，因此不支持事务。

事务相关

事务的特性

ACID：

1. A，原子性；事务内的操作要么全都执行，要么全都不执行；
2. C，一致性；数据库在事务执行前后，数据要保持一致。比如转账操作，转账后，转出方和转入方的金额之和应该想对。
3. I，隔离性；一个事务不应该被其他事务所影响。
4. D，持久性；事务操作的数据要持久化到数据库，即使数据库出故障也不应该受影响。

事务的隔离等级

隔离等级由低到高分别为：

1. 读未提交；。
2. 读已提交；不可解决幻读和不可重复度，可解决脏读问题；
3. 可重复度；单个事务内部对特定数据的前后读取数据一致。不可解决幻读问题。
4. 串行化；最高等级，事务串行化执行，可以避免所有问题：脏读、幻读、不可重复度等。但是效率较低。

InnoDB索引实现

InnoDB使用B+树存储数据，除了主键索引为聚簇索引，其他索引均为非聚簇索引。
一个表中只能存在一个聚簇索引（主键索引），但是可以存在多个非聚簇索引。
InnoDB表和索引的数据是在一起的，表数据和索引的文件都放在.ibd文件中。

聚簇索引（主键索引）

B+树叶子节点包含数据表中行记录就是聚簇索引（索引和数据是存放在一块的）

可以看到叶子节点包含了完整的数据记录，这就是聚簇索引。因为InnoDB的数据文件（.idb）按主键聚集，所以InnoDB必须有主键（MyISAM可以没有），如果没有显示指定主键，则选取首个为唯一且非空的列作为主键索引，如果还没具备，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
;
主键索引结构分析：
数据保存以key-value的形式保存。
B+树：所有的数据都存放在叶子结点，非叶子结点只保存键值方便查找；
B树：所有的结点都保存数据；
对于MySQL中的InnoDB数据库引擎的主索引，索引即数据，数据即索引。方便范围查找以及顺序查找；同时存在普通索引，帮助查找某些被查找频率较高的字段。如果需要较多索引，推荐使用联合索引，以提高索引的利用效率。

索引的优缺点

参考文章：一文搞懂MySQL索引（清晰明了）

索引失效

建立索引确实可以提升查询速度，但前提是要正确使用索引，否则不奏效。

索引失效的可能原因：

1. where中有or；

• 如果条件中有or，其中有部分条件带索引也不会使用索引，例如user_id无索引
  
  注意：如果想用or，又想用索引，只能将or条件中的每一列都加上索引。

2. 组合索引没有用左列字段；（顺序匹配问题）

• 如果不使用前列，后续列也没法使用；

3. like以%开头；
   

4. 需要类型转换；
   如果查询条件中有字符串，一定要用单引号括起来，否则索引不命中；
   

5. where中索引列有运算；
   

6. where中索引列使用了函数；
   

7. 如果MySQL觉得全表扫描更快时（数据少）；

没必要用索引的场景

1. 唯一性差；
   某个字段的取值（数据范围）只有几种时，比如性别。意味着索引的二叉树级别少，多时平级。这样的二叉树查找无异于全表扫描；
2. 频繁更新的字段（更新索引的消耗代价）；
   比如longinCount登录次数统计，频繁变化，增大数据库工作了，降低效率；
3. where中不用的字段；
4. 索引使用<>号时效果一般。
   where子句中对索引列使用不等于<>，使用索引效果一般；

MySQL体系结构：

自上而下分为四层：
① 网络接入层
② 服务层
③ 存储引擎层
④ 文件系统层

网络接入层

提供了应用程序进入MySQL服务的接口。客户端与服务器建立连接，客户端发送SQL到服务端。

服务层

管理工具和服务

系统管理和控制工具，例如备份恢复、MySQL复制、集群等。

连接池

主要负责连接管理、授权认证、安全等。
主要负责连接管理、授权认证、安全等等。每个客户端连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过SSL证书进行认证。登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限。

SQL接口

查询解析器

查询优化器

缓存（8.0以前支持查询缓存，8.0之后就不支持了）

存储引擎层

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

当存储引擎是MyISAM时：

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

文件系统层

*.frm：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等

• *.ibd：InnoDB DATA，表数据和索引的文件。该表的索引（B+树）的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据

参考文章：10分钟了解MySQL体系构架、存储引擎和索引结构