数据库MySQL基础知识及常见面试题整理

谁践踏了优雅 2023-02-27 13:57 9阅读 0赞

# 数据库原理 #

> 此部分知识整理自CyC大神的笔记以及慕课网上的课程内容，补充了《高性能mysql第三版》第一章的内容

## 一、事务的四大特性 ##

**1. 原子性：**  
事务被视为不可分割的最小操作单元，一个事务的所有操作要么全部成功，要么全部失败。

**2. 一致性：**  
数据库在事务执行前后保持一致性状态，在一致性状态下，所以事务对一个数据的读取结果都是相同的。

**3. 隔离性：**  
事务所做的修改在未提交前，对其他事务不可见

**4. 持久性：**  
一旦事务提交，其所作的修改会永久保存到数据库中，即使系统发生崩溃，事务执行的结果也不能丢失，使用重做日志来保持持久性。

*  只有满足一致性，事务的执行结果才是正确的。
 *  在无并发的情况下，事务串行执行，隔离性一定满足。此时只要满足原子性，就能保证一致性。
 *  在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性
 *  事务满足持久化是为了应对数据库崩溃的情况

**AUTOCOMMIT**  
MySQL 默认采用自动提交模式。也就是说，如果不显式使用 START TRANSACTION 语句来开始一个事务，那么每个 查询都会被当做一个事务自动提交

## 二、并发一致性问题 ##

**并发环境下，事物的隔离性很难保证，因此会出现很多并发一致性的问题**

### 1. 丢失修改 ###

T1和T2两个事务都对一个数据进行修改，T1先修改，T2随后修改，T2的修改覆盖了T1的修改

### 2. 读脏数据 ###

T1修改了一个数据，T2读取了这个数据，随后T1撤销了这次修改，T2读取的数据是脏数据

### 3. 不可重复读 ###

T2读取了一个数据，T1对数据做了修改，如果T2再次读取这个数据，此时读取的结果和第一次读取的结果不同

### 4. 幻读 ###

T1读取某个范围的数据，T2在这个范围插入新的数据，T1再次读取这个范围的数据，此时读取的结果和第一次读取的不同

*  产生并发不一致的问题主要是破环了事务的隔离性，解决方法是通过并发控制来保证隔离性
 *  并发控制可以通过封锁来实现，但是封锁操作需要用户自己控制
 *  数据库管理系统提供了事务的隔离级别，让用户以一种更轻松的方式来处理并发一致性问题

--------------------

## 三、封锁 ##

### 1. 封锁粒度 ###

MySQL 中提供了两种封锁粒度：行级锁以及表级锁

应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，发生锁争用的可能就越小，系统的 并发程度就越高

但是加锁需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。因此封锁粒度 越小，系统开销就越大

在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡

### 2. 封锁类型 ###

**1.读写锁**

*  排他锁（Exclusive）简称X锁，又称写锁
 *  共享锁（Shared）简称S锁，又称读锁

有以下两个规定：

*  一个事务对数据对象 A 加了 X 锁，就可以对 A 进行读取和更新。加锁期间其它事务不能对 A 加任何锁
 *  一个事务对数据对象 A 加了 S 锁，可以对 A 进行读取操作，但是不能进行更新操作。加锁期间其它事务能对 A 加 S 锁，但是不能加 X 锁

**2.意向锁**  
使用意向锁（Intention Locks）可以更容易地支持多粒度封锁

在存在行级锁和表级锁的情况下，如果一个事务想要对表A加X锁，它需要对该表和表的每一行检查有无其他事物加了其他锁，这是非常耗时的

意向锁在原来的 X/S 锁之上引入了 IX/IS，IX/IS 都是表锁，用来表示一个事务**想要**在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定：

*  一个事务在获得某个数据行对象的S锁之前，必须先获得表的IS锁或者更强的锁
 *  一个事务在获得某个数据行对象的X锁之前，必须先获得表的IX锁

通过引入意向锁，事务T想要对表A加X锁，只需要先检测是否有其他事务对表A加了X/IX/S/IS锁，如果加了就表示有其他事务正在使用这个表或者表中某一行的锁，因此事务T加X锁失败

**注意：**

*  任意IS/IX锁之间是兼容的，因为它们只是表示想要对表加锁，而不是真正的加锁
 *  S锁只与S锁和IS锁兼容，也就是说事务T想要对数据行加S锁，其他事务可以已经获得对表或者表中行的S锁

### 3. 封锁协议 ###

#### 1. 三级封锁协议 ####

**一级封锁协议**  
事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁  
可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖

**二级封锁协议**  
在一级的基础上，要求读数据A时必须加S锁，**读取完马上释放S锁**  
可以解决读脏数据问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据

**三级封锁协议**  
在二级的基础上，要求读取数据 A 时必须加 S 锁，**直到事务结束了才能释放S锁**  
可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变

#### 2. 两段锁协议 ####

加锁和解锁分为两个阶段进行

可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同

事务遵循两段锁协议是保证可串行化调度的充分条件。例如以下操作满足两段锁协议，它是可串行化调度

lock-x(A)...lock-s(B)...lock-s(C)...unlock(A)...unlock(C)...unlock(B)

但不是必要条件，例如以下操作不满足两段锁协议，但是它还是可串行化调度

lock-x(A)...unlock(A)...lock-s(B)...unlock(B)...lock-s(C)...unlock(C)

**MySQL隐式与显式的锁定**  
MySQL 的 InnoDB 存储引擎采用两段锁协议，会根据隔离级别在需要的时候自动加锁，并且所有的锁都是在同一时 刻被释放，这被称为隐式锁定

InnoDB 也可以使用特定的语句进行显示锁定：

SELECT ... LOCK In SHARE MODE; 
    SELECT ... FOR UPDATE;

## 四、隔离级别 ##

#### 未提交读（READ UNCOMMITTED） ####

事务中的修改即使没有提交，对其他事务也是可见的

#### 提交读（READ COMMITTED） ####

一个事务只能读取已经提交的事务所做的修改。

#### 可重复读（REPEATABLE READ） ####

保证在同一个事务中多次读取同样的数据的结果是一样的

#### 可串行化（SERIALIZABLE） ####

强制事务串行执行，需要加锁实现，而其他隔离级别通常不需要

## 五、多版本并发控制 ##

**多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种 具体方式，用于实现提交读和可重复读这两种隔离级别。**  
而未提交读隔离级别总是读取新的数据行，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现

**版本号**

*  系统版本号：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
 *  事务版本号：事务开始时的系统版本号

**隐藏的列**  
MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号：

*  创建版本号：指示创建一个数据行的快照时的系统版本号；
 *  删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了

**Undo 日志**  
MVCC 使用到的快照存储在 Undo 日志中，该日志通过回滚指针把一个数据行（Record）的所有快照连接起来

#### 实现过程 ####

以下实现过程针对可重复读隔离级别

当开始创建一个事务时，该事务的版本号肯定大于当前所有数据行快照的创建版本号，理解这一点很关键。数据行快照的创建版本号是创建数据行快照时的系统版本号，系统版本号随着创建事务而递增，因此新创建一个事务时，这个事务的系统版本号比之前的系统版本号都大，也就是比所有数据行快照的创建版本号都大。

**1.SELECT**  
多个事务必须读取到同一个数据行的快照，并且这个快照是距离现在近的一个有效快照。但是也有例外，如果有一 个事务正在修改该数据行，那么它可以读取事务本身所做的修改，而不用和其它事务的读取结果一致

把没有对一个数据行做修改的事务称为 T，T 所要读取的数据行快照的创建版本号必须小于 T 的版本号，因为如果大 于或者等于 T 的版本号，那么表示该数据行快照是其它事务的新修改，因此不能去读取它。除此之外，T 所要读取 的数据行快照的删除版本号必须大于 T 的版本号，因为如果小于等于 T 的版本号，那么表示该数据行快照是已经被删 除的，不应该去读取它

**2.INSERT**  
将当前系统版本号作为数据行快照的创建版本号

**3.DELETE**  
将当前系统版本号作为数据行快照的删除版本号

**4.UPDATE**  
将当前系统版本号作为更新前的数据行快照的删除版本号，并将当前系统版本号作为更新后的数据行快照的创建版本 号。可以理解为先执行 DELETE 后执行 INSERT

#### 快照读与当前读 ####

1.快照读  
使用 MVCC 读取的是快照中的数据，这样可以减少加锁所带来的开销  
`select * from table ...;`

2.当前读  
读取的是新的数据，需要加锁。以下第一个语句需要加 S 锁，其它都需要加 X 锁

select * from table where ? lock in share mode; 
    select * from table where ? for update; 
    insert; 
    update;
    delete

## 六、Next-Key Locks ##

Next-Key Locks 是 MySQL 的 InnoDB 存储引擎的一种锁实现

MVCC 不能解决幻影读问题，Next-Key Locks 就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ） 隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题

**Record Locks**  
锁定一个记录上的索引，而不是记录本身  
如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用

**Gap Locks**  
锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15  
SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;

**Next-Key Locks**  
它是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。例如一个索引包含 以下值：10, 11, 13, and 20，那么就需要锁定以下区间：

(-∞, 10] (10, 11] (11, 13] (13, 20] (20, +∞)

--------------------

*  **当前读** （加了锁的增删改查）

update, delete, insert, select ... lock in share mode , select ... for update(X锁)

![当前读][format_png]

*  **快照读**  
    不加锁的非阻塞读，select (这里的不加锁在隔离级别为非SERIALIZABLE时成立，在SERIALIZABLE级别下，因为是串行，所以快照读也会退化为当前读， 快照读的出现基于提升并发性能的考虑，实现基于多版本并发控制MVCC，可认为这是行级锁的变种，但在很多情况下避免了加锁操作，开销更低，既然基于多版本，那么快照读可能读到的并非最新版本)

### RC、RR级别下InnoDB的非阻塞读如何实现？ ###

*  **DB\_TRX\_ID** : 记录最近一次对数据行做修改的时间戳，**最后一次对本行进行修改的事务ID**
    
     *  删除操作也认为是修改，在除了这三列之外的第四列标记本行被删除
 *  **DB\_ROLL\_PTR** : 回滚指针，指写入回滚段 rollback segment的undo日志记录，如果一行记录被更新，则undo log record包含从建该行记录到被更新之前内容所必须的信息
 *  **DB\_ROW\_ID** : 行号（系统版本号），包含一个随着新行插入而单调递增的行ID
 *  **undo日志** :
    
     *  当我们对记录做变更操作时就会产生undo，undo记录中存储的是老版数据，当一个旧的事务需要读取数据时，为了能读取到老版本的数据，需要顺着undo链，找到满足其可见性的记录
     *  insert undo log
     *  update undo log: (delete&update), 回滚时需要，快照读也需要

![undo][]

*  **read view** :
    
     *  当我们执行快照读 select时，会针对我们查询的数据，创建出一个read view来决定当前事务能看到的是哪个版本的数据，有可能是当前最新版本的数据，也有可能是undo log里某个版本的数据
     *  read view遵循一个可见性算法，将要修改数据的DB\_TRX\_ID 取出来，与系统其他活跃事务ID做对比，如果大于等于这些事务ID, 就通过DB\_ROLL\_PTR指针指向的undo log的DB\_TRX\_ID, 直到小于这些活跃事务ID为止
        
         *  m\_low\_limit\_id 活动事务范围内最大ID
         *  m\_up\_limit\_id 活动事务范围内最小ID

--------------------

### InnoDB可重复读隔离级别下如何避免幻读？ ###

*  表象：快照读（非阻塞读）–伪MVCC
 *  内在：next-key锁（行锁+gap锁）

**Gap锁**

gap – 索引树中插入新纪录的空隙; gap锁 – 锁定一个范围，但不包括记录本身

为了防止同一事务的两次当前读出现幻读的情况，gap锁在RC及更低级别下是没有的，只在RR以及SERIALIZABLE级别下才有

**对主键索引和唯一索引会用Gap锁么？**

*  如果where条件全部命中，则不会用Gap锁，只会加记录锁
    
     *  全部命中是指 `SELECT * FROM customers WHERE customer_id IN (1,3,5)` 中记录行1，3，5都存在
 *  如果where条件部分命中或者全不命中，则加Gap锁

![例子][format_png 1]

*  这里我们在A事务删除不存在的记录7，在B事务插入记录8

delete from tb where id = 7;
    
    insert into tb values('i',8);//此时插入语句被阻塞

**Gap锁会用在非唯一索引或者不走索引的当前读中**

*  这里事务A读非唯一索引9时，为了防止事务B此时添加一条id=9的记录时，事务A产生读到三条id=9的记录的幻读，Gap会给（6，9\] (9,11\]这个范围上锁  
    ![非唯一索引][format_png 2]
 *  当前读不走索引时，对所有的Gap都上锁, 相当于锁表  
    ![不走索引][format_png 3]

## 七、关系数据库设计理论 ##

### 函数依赖 ###

记 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A

如果 \{A1，A2，… ，An\} 是关系的一个或多个属性的集合，该集合函数决定了关系的其它所有属性并且是小的，那 么该集合就称为键码

对于 A->B，如果能找到 A 的真子集 A’，使得 A’-> B，那么 A->B 就是部分函数依赖，否则就是完全函数依赖

对于 A->B，B->C，则 A->C 是一个传递函数依赖

### 异常 ###

以下的学生课程关系的函数依赖为 \{Sno, Cname\} -> \{Sname, Sdept, Mname, Grade\}，键码为 \{Sno, Cname\}。也 就是说，确定学生和课程之后，就能确定其它信息

不符合范式的关系，会产生很多异常，主要有以下四种异常：

*  冗余数据：例如 学生-2 出现了两次
 *  修改异常：修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改
 *  删除异常：删除一个信息，那么也会丢失其它信息。例如删除了 课程-1 需要删除第一行和第三行，那么 学 生-1 的信息就会丢失。
 *  插入异常：例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入

### 范式 ###

范式理论是为了解决以上提到四种异常  
高级别范式的依赖于低级别的范式，1NF 是低级别的范式  
**1.第一范式（1NF）**  
属性不可分

**2.第二范式（2NF）**  
每个非主属性完全函数依赖于键码  
可以通过分解来满足

**3.第三范式（3NF）**  
非主属性不传递函数依赖于键码  
上面的 关系-1 中存在以下传递函数依赖

**关于三范式的详细介绍可以参见这个知乎回答**  
[数据库第一二三范式到底在说什么？][Link 1]

> 把一个概念讲得简单易懂的老师是真正的良师!  
> 向宁波大学的刘慰老师献上我最崇高的敬意！

## 八、ER图 ##

Entity-Relationship，有三个组成部分：实体、属性、联系  
用来进行关系型数据库系统的概念设计

### 实体的三种联系 ###

包含一对一，一对多，多对多三种

*  如果 A 到 B 是一对多关系，那么画个带箭头的线段指向 B
 *  如果是一对一，画两个带箭头的线段
 *  如果是多对多，画两个不带箭头的线段

### 表示出现多次的关系 ###

一个实体在联系出现几次，就要用几条线连接

下图表示一个课程的先修关系，先修关系出现两个 Course 实体，第一个是先修课程，后一个是后修课程，因此需要用两条线来表示这种关系

### 联系的多样性 ###

虽然老师可以开设多门课，并且可以教授多名学生，但是对于特定的学生和课程，只有一个老师教授，这就构成了一 个三元联系

### 表示子类 ###

用一个三角形和两条线来连接类和子类，与子类有关的属性和联系都连到子类上，而与父类和子类都有关的连到父类 上

--------------------

# MySQL面试题 #

> 此部分笔记补充慕课上的相关内容 2020-5-4

## 1.如何设计一个关系型数据库？ ##

![format_png 4][]

*  存储部分：类似文件系统，将数据持久化到存储设备当中
 *  程序实例模块：对存储进行逻辑管理
    
     *  存储管理：数据逻辑关系转换成存储关系
     *  缓存机制：优化执行效率
     *  SQL解析：SQL语句解析
     *  日志管理：记录操作
     *  权限划分：多用户管理
     *  容灾机制：灾难恢复
     *  索引管理：优化数据查询效率
     *  锁管理：使数据库支持并发操作

## 2.MySQL常见索引结构 ##

### 1. 索引模块 ###

#### 1.1 优化索引 B Tree ####

*  **平衡多路查找树**
 *  **定义** （一个m阶B树具有如下特征）
    
    1.  **根节点至少两个子节点**
    2.  **k个子树的非叶子节点包含k-1个元素（关键字）非叶子节点和叶子节点都用来保存数据**，其中ceil(m/2) <= k <= m
    3.  **每个叶子节点都包含k-1个元素**，其中ceil(m/2) <= k <= m
    4.  **所有叶子节点都位于同一层**
    5.  **每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域的划分**

## ![B树][B] ##

#### 1.2 优化索引 B+ Tree ####

> 在B+树中的节点通常被表现为一组有序的元素和子指针，如果此B+树的阶数是m，则除了根之外的每个节点都包含最少ceil（m/2）个元素最多m个元素，对于任意节点有最多m个指针。对于所有内部结点，子指针的元素总是与元素的数目相同，所有叶子都在相同的高度上，叶结点本身按元素大小 从小到大连接  
> *–源自维基百科*

1.  **k个子树的非叶子节点包含有k个关键字（元素），非叶子节点不保存数据，只用来索引，所有数据都保存在叶子节点**
2.  **所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接**
3.  **所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素**

**B+树更适合用来做存储索引**

*  **B+树的非叶子节点不存数据，所以同样大小的磁盘也可以容纳更多的节点元素，因此在数据量相同的情况下，B+树比B树更加“矮胖”，因此查询时磁盘IO次数也更少**
 *  **B树的查询性能并不稳定（最好情况是只查根节点，最差是查叶子节点）; B+树的查询效率更加稳定（所有查询都是从根节点走到叶子节点，路径长度相同）**
 *  更利于数据库扫描（所有叶子节点链接起来，支持范围查询！比如查>=10 则从第二个叶子节点顺序遍历下去即可）

![B+树][B 1]

#### 1.3 优化索引 Hash ####

![format_png 5][]

**缺点**：

*  仅仅满足“=”，“IN”, 不能使用范围查询
 *  无法被用来避免数据的排序操作
 *  不能利用部分索引键查询
 *  不能避免表扫描
 *  遇到大量Hash值相等的情况后性能并不一定比B树索引高

## 3.MyISAM和InnoDB的区别 ##

**两者对比：**

1.  **是否支持行级锁**：MyISAM只有表级锁，而InnoDB支持行级锁和表级锁，默认为行级锁
2.  **是否支持事务和崩溃后的安全恢复**：MyISAM不提供事务支持；但InnoDB提供事务支持，具有事务（commit）回滚（rollback）和崩溃修复能力（crash recovery capabilities）的事务安全型表
3.  **是否支持外键**：MyISAM不支持，而InnoDB支持
4.  **是否支持MVCC**: 仅InnoDB支持
5.  **MyISAM的B+树索引，索引和数据分开放，非聚簇索引；InnoDB 索引和数据文件在一起，聚簇索引**

## 4.大表优化 ##

*  当Mysql单表数据量过大时，CRUD性能明显下降，一些常见优化措施如下

#### 限定数据的范围 ####

*  务必禁止b不带任何限制数据范围条件的查询语句。比如，我们当用户在查询订单历史的时候，限制在一个月内

#### 读写分离 ####

*  主库负责写，从库负责读

#### 读写分离为主从库后的MySql的主从复制？ ####

将主数据库中的DDL和DML操作通过二进制日志传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

**原理**

*  master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；
 *  slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件；
 *  同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

#### 垂直分区 ####

**根据数据库里数据表的相关性进行拆分**，例如用户表里既有用户登录信息又有基本信息，可以将用户表拆分成两个单独的表，其实质是**数据表列的拆分**

*  **垂直拆分的优点：** 可以使得列数据变小，在查询时减少读取的阻塞数，减少I/O次数，此外垂直分区可见简化表结构，易于维护
 *  **缺点：** 主键会出现冗余，需要管理冗余列，并会引起Join操作，会让事务变的复杂

#### 水平分区 ####

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。其实质是**数据表行的拆分**

分表对提升数据库并发能力没有意义，所以**水平拆分最好分库**

水平拆分能够支持非常大的数据量存储，应用端改造也少，但分片事务难以解决。跨节点Join性能较差，逻辑复杂

## 5.分库分表后，id主键怎么处理？ ##

**生成全局ID**

1.  **UUID**：不适合作为主键，太长且无序不可读，查询效率低
2.  **数据库自增ID**: 两台数据库分别设置不同步长，生成不重复ID来实现高可用。这种方式生成的ID有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈
3.  **利用redis生成id**：性能比较好，灵活方便，不依赖于数据库，但是引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本
4.  Twitter的snowflake算法
5.  美团的Leaf分布式ID生成系统

## 6.一条sql执行的很慢，原因可能是什么？ ##

#### 1.大多数正常，偶尔很慢 ####

*  数据库在刷新脏页（flush）

> 数据库先在内存更新，然后写入redo log，然后在空闲的时候同步到磁盘，所谓刷新脏页就是把内存的更新同步到磁盘的过程

**刷新脏页的4种场景**
    - redo log写满了，同时数据库操作频繁，不得不停下所有事务先把更新同步到磁盘上去（主要原因）
    - 内存不够用了
    - mysql认为系统空闲的时候
    - mysql正常关闭的时候

*  拿不到锁

#### 2.总是很慢 ####

*  没用到索引
    
     *  字段没有索引
     *  字段有索引，但没用上 `select * from t where c - 1 = 1000;` c上有索引，但因为运算，所以不会走索引
     *  函数操作导致没有用上索引
 *  数据库自身误优化，导致选错了索引

> 数据库有时会认为走全表比走索引更快，因为数据量小或者索引基数统计失误

## 7.建立索引有哪些需要考虑的？ ##

建立索引一般需要考虑到字段的使用频率，经常作为条件进行查询的字段比较适合作为索引。如果需要建立联合索引的话，还需要考虑联合索引中的顺序。

除此之外还需要考虑，防止过多的索引对表造成太大压力，这些都和实际的表结构以及查询方式有关。

#### 哪些列需要建索引？ ####

1.  在经常需要搜索的列上，可以加快搜索的速度；
2.  在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
3.  在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4.  在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5.  在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6.  在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

#### 哪些列不应该建索引？ ####

1.  对于那些在查询中很少使用或者参考的列不应该创建索引。

> 这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

1.  对于那些只有很少数据值的列也不应该增加索引。

> 这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

1.  对于那些定义为text, image和bit数据类型的列不应该增加索引。

> 这是因为，这些列的数据量要么相当大，要么取值很少。

## 8.哪些情况下索引会失效？ ##

1.  如果索引列出现了隐式类型转换，则 MySQL 不会使用索引。常见的情况是在 SQL 的 WHERE 条件中字段类型为字符串，其值为数值，如果没有加引号那么 MySQL 不会使用索引。
2.  使用like时通配符在前
3.  在查询条件中使用OR；（要想是索引生效，需要将or中的每个列都加上索引）
4.  对索引列进行函数运算
5.  联合索引ABC问题 （Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是index (a,b,c)，可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c或c进行查找 。）
6.  如果 MySQL 判断全表扫描比使用索引查询更快，则不会使用索引。

## 9.索引如何分析和优化？ ##

#### 1 如何定位并优化慢查询sql ####

show variables like '%quer%'
    
    'long_query_time', '10.000000' //超过10s认为是慢查询
    'slow_query_log', 'ON'        //慢查询日志开启
    'slow_query_log_file', 'DESKTOP-9CACBSD-slow.log' //慢查询日志保存地址
    
    show status like '%slow_queries%'

#### 2 使用explain等工具分析sql ####

explain模拟优化器执行SQL语句，在5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用explain查看执行计划，从而知道mysql是如何处理sql语句，分析查询语句或者表结构的性能瓶颈。

![format_png 6][]

#### id ####

查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序  
**两种情况**

*  id相同，执行顺序从上往下
 *  id不同，id值越大，优先级越高，越先执行

#### select\_type ####

查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询

1.  simple ——简单的select查询，查询中不包含子查询或者UNION
2.  primary ——查询中若包含任何复杂的子部分，最外层查询被标记
3.  subquery——在select或where列表中包含了子查询
4.  derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中
5.  union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived
6.  union result:UNION 的结果

#### table ####

*  输出的行所引用的表

#### type ####

1.  **system**：表中仅有一行（=系统表）这是const联结类型的一个特例。
2.  **const**：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量
3.  **eq\_ref**:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描
4.  **ref**:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体
5.  **range**:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描
6.  **index**:index 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。
7.  **all**：遍历全表以找到匹配的行

#### possible\_keys ####

*  指出MySQL能使用哪个索引在该表中找到行

#### key ####

*  显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。查询中如果使用覆盖索引，则该索引和查询的select字段重叠。

#### key\_len ####

*  表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度。

#### ref ####

*  显示索引的哪一列被使用了，如果有可能是一个常数，哪些列或常量被用于查询索引列上的值

#### rows ####

*  根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数

#### Extra ####

包含不适合在其他列中显示，但是十分重要的额外信息

1.  **Using filesort**：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序”
2.  **Using temporary**:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。
3.  **Using index**:表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。
4.  **Using where** :表明使用where过滤
5.  using join buffer:使用了连接缓存
6.  impossible where:where子句的值总是false，不能用来获取任何元组
7.  select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（\*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
8.  distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

explain select name from person_info_large order by name desc;//很慢 查询耗时6s

**关键字段：**

*  type ：出现**index 或者 all**说明走的是全表扫描，需要优化
 *  extra：extra出现以下两项意味着mysql根本不能使用索引，效率会受到重大影响，应尽可能对此优化

<table> 
 <thead> 
  <tr> 
   <th>extra 项</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Using filesort</td> 
   <td>表示mysql会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序，mysql中无法利用索引完成的排序操作称为文件排序</td> 
  </tr> 
  <tr> 
   <td>Using temporary</td> 
   <td>表示mysql在对查询结果进行排序时使用临时表，常见于排序 order by和分组查询group by</td> 
  </tr> 
 </tbody> 
</table>

#### 3 修改sql或者尽量让sql走索引 ####

![format_png 7][]

explain select account from person_info_large order by account desc; //4s
    //因为account和name也是一一对应的，所以借助有索引的account我们依然可以找到name相关信息
    alter table person_info_large add index idx_name(name);// 3s
    //或者直接加索引

#### 4 联合索引最左匹配原则 ####

*  下图中（title，area）是联合索引，查询时候走的是title-area联合索引
 *  如果只查title 也是走联合索引
 *  如果查area，则不走联合索引，走全表查询

![format_png 8][]

*  上图中的向右停止匹配后就不再用索引，意思是之后字段在查询过程是无序排列的，拿上面那个例子来说，a,b,c字段查出来后是有序排列的，d不是

![format_png 9][]

最左前缀匹配：https://www.cnblogs.com/ljl150/p/12934071.html

成因：

MySQL创建复合索引时，首先对最左边的索引字段进行排序，在第一个基础上再对第二个排序，第一个字段绝对有序，第二个字段无序，因此通常直接用第二个字段条件判断是用不到索引的

[format_png]: /images/20230209/fc1eae3e8d5747d38b884ec2100cbae2.png
[undo]: https://imgconvert.csdnimg.cn/aHR0cDovL3d3dy56dW95aWZhbi50b3AvdW5kbyVFNiU5NyVBNSVFNSVCRiU5Ny5wbmc?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cDovL3d3dy56dW95aWZhbi50b3AvR0FQJUU5JTk0JTgxJUU0JUJFJThCJUU4JUExJUE4LnBuZw?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cDovL3d3dy56dW95aWZhbi50b3AvJUU5JTlEJTlFJUU1JTk0JUFGJUU0JUI4JTgwJUU3JUI0JUEyJUU1JUJDJTk1LnBuZw?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cDovL3d3dy56dW95aWZhbi50b3AvJUU0JUI4JThEJUU4JUI1JUIwJUU3JUI0JUEyJUU1JUJDJTk1LnBuZw?x-oss-process=image/format,png
[Link 1]: https://zhuanlan.zhihu.com/p/20028672
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cDovL3d3dy56dW95aWZhbi50b3AvJUU1JUE2JTgyJUU0JUJEJTk1JUU4JUFFJUJFJUU4JUFFJUExJUU0JUI4JTgwJUU0JUI4JUFBJUU1JTg1JUIzJUU3JUIzJUJCJUU1JTlFJThCJUU2JTk1JUIwJUU2JThEJUFFJUU1JUJBJTkzLnBuZw?x-oss-process=image/format,png
[B]: https://imgconvert.csdnimg.cn/aHR0cDovL3d3dy56dW95aWZhbi50b3AvQiVFNiVBMCU5MS5wbmc?x-oss-process=image/format,png
[B 1]: https://imgconvert.csdnimg.cn/aHR0cDovL3d3dy56dW95aWZhbi50b3AvQiUyQiVFNiVBMCU5MS5wbmc?x-oss-process=image/format,png
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cDovL3d3dy56dW95aWZhbi50b3AvaGFzaC5wbmc?x-oss-process=image/format,png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cDovL3d3dy56dW95aWZhbi50b3AvZXhwbGFpbi5wbmc?x-oss-process=image/format,png
[format_png 7]: https://imgconvert.csdnimg.cn/aHR0cDovL3d3dy56dW95aWZhbi50b3AvcGVyc29uX2luZm8ucG5n?x-oss-process=image/format,png
[format_png 8]: https://imgconvert.csdnimg.cn/aHR0cDovL3d3dy56dW95aWZhbi50b3AvJUU4JTgxJTk0JUU1JTkwJTg4JUU3JUI0JUEyJUU1JUJDJTk1JUU0JUJFJThCJUU1JUFEJTkwLnBuZw?x-oss-process=image/format,png
[format_png 9]: https://imgconvert.csdnimg.cn/aHR0cDovL3d3dy56dW95aWZhbi50b3AvJUU2JTlDJTgwJUU1JUI3JUE2JUU1JThDJUI5JUU5JTg1JThEJUU1JUFFJTlFJUU0JUJFJThCLnBuZw?x-oss-process=image/format,png