interviewDoc/Java/database/MySQL.md · SHIELD/JavaInterview

**所有问题以及答案，我都整理成了高清PDF，并且带目录：[Java面试整理高清PDF下载](https://gitee.com/tiger-a/java-interview/blob/master/interviewDoc/Java/index.md)**

- [MySQL中的varchar和char有什么区别.](#mysql中的varchar和char有什么区别)
- [varchar(10)和int(10)代表什么含义?](#varchar10和int10代表什么含义)
- [MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义](#mysql中varchar与char的区别以及varchar50中的50代表的涵义)
- [请说明varchar和text的区别](#请说明varchar和text的区别)
- [FLOAT和DOUBLE的区别是什么？](#float和double的区别是什么)
- [如何在MySQL种获取当前日期？](#如何在mysql种获取当前日期)
- [MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义](#mysql中varchar与char的区别以及varchar50中的50代表的涵义)
- [innodb的事务与日志的实现方式](#innodb的事务与日志的实现方式)
- [MySQL的binlog有有几种录入格式?分别有什么区别?](#mysql的binlog有有几种录入格式分别有什么区别)
- [MySQL数据库cpu飙升怎么处理？](#mysql数据库cpu飙升怎么处理)
- [超大分页怎么处理?](#超大分页怎么处理)
- [关心过业务系统里面的sql耗时吗?统计过慢查询吗?对慢查询都怎么优化过?](#关心过业务系统里面的sql耗时吗统计过慢查询吗对慢查询都怎么优化过)
- [横向分表和纵向分表,可以分别举一个适合他们的例子吗?](#横向分表和纵向分表可以分别举一个适合他们的例子吗)
- [什么是存储过程？有哪些优缺点？](#什么是存储过程有哪些优缺点)
- [说一说三个范式](#说一说三个范式)
- [MySQL的复制原理以及流程](#mysql的复制原理以及流程)
- [六种关联查询](#六种关联查询)
- [内连接分为三类](#内连接分为三类)
- [外连接（LEFT JOIN/RIGHT JOIN）](#外连接left-joinright-join)
- [联合查询（UNION与UNION ALL）](#联合查询union与union-all)
- [全连接（FULL JOIN）](#全连接full-join)
- [UNION与UNION ALL的区别？](#union与union-all的区别)
- [SQL语句优化的一些方法？](#sql语句优化的一些方法)
- [MySQL由哪些部分组成, 分别用来做什么](#mysql由哪些部分组成-分别用来做什么)
- [如果一个表有一列定义为TIMESTAMP，将发生什么？](#如果一个表有一列定义为timestamp将发生什么)
- [MySQL 里记录货币用什么字段类型好](#mysql-里记录货币用什么字段类型好)
- [你们数据库是否支持emoji表情，如果不支持，如何操作？](#你们数据库是否支持emoji表情如果不支持如何操作)
- [MySQL 数据库作发布系统的存储，一天五万条以上的增量， 预计运维三年,怎么优化？](#mysql-数据库作发布系统的存储一天五万条以上的增量-预计运维三年怎么优化)
- [一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。](#一个6亿的表a一个3亿的表b通过外间tid关联你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录)
- [优化数据库的方法](#优化数据库的方法)
- [简单描述 MySQL 中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响（从读写两方面）](#简单描述-mysql-中索引主键唯一索引联合索引的区别对数据库的性能有什么影响从读写两方面)
- [SQL 注入漏洞产生的原因？如何防止？](#sql-注入漏洞产生的原因如何防止)
- [存储时期](#存储时期)
- [解释 MySQL 外连接、内连接与自连接的区别](#解释-mysql-外连接内连接与自连接的区别)
- [存储引擎常用命令](#存储引擎常用命令)
- [MySQL支持哪些存储引擎?](#mysql支持哪些存储引擎)
- [请说明InnoDB和MyISAM的区别](#请说明innodb和myisam的区别)
- [innodb引擎的特性](#innodb引擎的特性)
- [什么是触发器，MySQL中都有哪些触发器？](#什么是触发器mysql中都有哪些触发器)
- [InnoDB和MyISAM有什么区别?](#innodb和myisam有什么区别)
- [myisamchk 是用来做什么的？](#myisamchk-是用来做什么的)
- [为什么要尽量设定一个主键?](#为什么要尽量设定一个主键)
- [主键使用自增ID还是UUID?](#主键使用自增id还是uuid)
- [字段为什么要求定义为not null?](#字段为什么要求定义为not-null)
- [如果要存储用户的密码散列,应该使用什么字段进行存储?](#如果要存储用户的密码散列应该使用什么字段进行存储)
- [索引是个什么样的数据结构呢?](#索引是个什么样的数据结构呢)
- [唯一索引比普通索引快吗, 为什么](#唯一索引比普通索引快吗-为什么)
- [索引的优缺点](#索引的优缺点)
- [做过哪些MySQL索引相关优化](#做过哪些mysql索引相关优化)
- [怎么看到为表格定义的所有索引？](#怎么看到为表格定义的所有索引)
- [索引的基础](#索引的基础)
- [创建索引的语法](#创建索引的语法)
- [索引对性能的影响：](#索引对性能的影响)
- [索引的类型](#索引的类型)
- [索引的区别](#索引的区别)
- [MySQL索引的创建原则](#mysql索引的创建原则)
- [索引的使用场景](#索引的使用场景)
- [索引分类](#索引分类)
- [MySQL索引的注意事项](#mysql索引的注意事项)
- [MySQL索引的原理](#mysql索引的原理)
    - [解题方法](#解题方法)
- [创建MySQL联合索引应该注意什么？](#创建mysql联合索引应该注意什么)
- [列值为NULL时，查询是否会用到索引？](#列值为null时查询是否会用到索引)
- [以下语句是否会应用索引：SELECT FROM users WHERE YEAR(adddate) < 2007;](#以下语句是否会应用索引select-from-users-where-yearadddate--2007)
- [MyISAM索引实现？](#myisam索引实现)
- [MyISAM索引与InnoDB索引的区别？](#myisam索引与innodb索引的区别)
- [使用索引查询一定能提高查询的性能吗？为什么](#使用索引查询一定能提高查询的性能吗为什么)
- [什么情况下设置了索引但无法使用](#什么情况下设置了索引但无法使用)
- [超键、候选键、主键、外键分别是什么？](#超键候选键主键外键分别是什么)
- [简单说一说drop、delete与truncate的区别](#简单说一说dropdelete与truncate的区别)
- [drop、delete与truncate分别在什么场景之下使用？](#dropdelete与truncate分别在什么场景之下使用)
- [B-Tree 和 B+Tree](#b-tree-和-btree)
- [为什么 B+Tree 比 B-Tree 更适合实际应用中操作系统的文件索引和数据库索引？**](#为什么-btree-比-b-tree-更适合实际应用中操作系统的文件索引和数据库索引)
- [什么是视图？以及视图的使用场景有哪些？](#什么是视图以及视图的使用场景有哪些)
- [Hash索引和B+树所有有什么区别或者说优劣呢?](#hash索引和b树所有有什么区别或者说优劣呢)
- [为什么用 B+ 树做索引而不用哈希表做索引?](#为什么用-b-树做索引而不用哈希表做索引)
- [索引是什么？有什么作用以及优缺点？](#索引是什么有什么作用以及优缺点)
- [上面提到了B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据,什么是聚簇索引?](#上面提到了b树在满足聚簇索引和覆盖索引的时候不需要回表查询数据什么是聚簇索引)
- [非聚簇索引一定会回表查询吗?](#非聚簇索引一定会回表查询吗)
- [在建立索引的时候,都有哪些需要考虑的因素呢?](#在建立索引的时候都有哪些需要考虑的因素呢)
- [联合索引是什么?为什么需要注意联合索引中的顺序?](#联合索引是什么为什么需要注意联合索引中的顺序)
- [创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?](#创建的索引有没有被使用到或者说怎么才可以知道这条语句运行很慢的原因)
- [那么在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢?](#那么在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢)
- [什么是事务?](#什么是事务)
- [数据库的乐观锁和悲观锁是什么？](#数据库的乐观锁和悲观锁是什么)
- [ACID是什么?可以详细说一下吗?](#acid是什么可以详细说一下吗)
- [同时有多个事务在进行会怎么样呢?](#同时有多个事务在进行会怎么样呢)
- [Myql 中的事务回滚机制概述](#myql-中的事务回滚机制概述)
- [并发事务带来哪些问题?](#并发事务带来哪些问题)
- [怎么解决这些问题呢?MySQL的事务隔离级别了解吗?](#怎么解决这些问题呢mysql的事务隔离级别了解吗)
- [Innodb使用的是哪种隔离级别呢?](#innodb使用的是哪种隔离级别呢)
- [MySQL 中有哪几种锁？](#mysql-中有哪几种锁)
- [对MySQL的锁了解吗?](#对mysql的锁了解吗)
- [锁机制与InnoDB锁算法](#锁机制与innodb锁算法)
- [MySQL都有哪些锁呢?像上面那样子进行锁定岂不是有点阻碍并发效率了?](#mysql都有哪些锁呢像上面那样子进行锁定岂不是有点阻碍并发效率了)
- [锁的优化策略](#锁的优化策略)
- [Explain 性能分析](#explain-性能分析)
- [如何优化SQL](#如何优化sql)

### MySQL中的varchar和char有什么区别.

char是一个定长字段,假如申请了`char(10)`的空间,那么无论实际存储多少内容.该字段都占用10个字符,而varchar是变长的,也就是说申请的只是最大长度,占用的空间为实际字符长度+1,最后一个字符存储使用了多长的空间.

在检索效率上来讲,char > varchar,因此在使用中,如果确定某个字段的值的长度,可以使用char,否则应该尽量使用varchar.例如存储用户MD5加密后的密码,则应该使用char.

### varchar(10)和int(10)代表什么含义?

varchar的10代表了申请的空间长度,也是可以存储的数据的最大长度,而int的10只是代表了展示的长度,不足10位以0填充.也就是说,int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示时按照长度展示.

### MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义

**(1)、varchar与char的区别**
char是一种固定长度的类型，varchar则是一种可变长度的类型

**(2)、varchar(50)中50的涵义**
最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)

**(3)、int（20）中20的涵义**
是指显示字符的长度
但要加参数的，最大为255，比如它是记录行数的id,插入10笔资料，它就显示00000000001 ~~~00000000010，当字符的位数超过11,它也只显示11位，如果你没有加那个让它未满11位就前面加0的参数，它不会在前面加0
20表示最大显示宽度为20，但仍占4字节存储，存储范围不变；

**(4)、mysql为什么这么设计**
对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和int(20)存储和计算均一样；

### 请说明varchar和text的区别

- varchar可指定字符数，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。
- text类型不能有默认值。
- varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引几乎不起作用。
- 查询text需要创建临时表。

### FLOAT和DOUBLE的区别是什么？

- FLOAT类型数据可以存储至多8位十进制数，并在内存中占4字节。
- DOUBLE类型数据可以存储至多18位十进制数，并在内存中占8字节。

### 如何在MySQL种获取当前日期？

```
SELECT CURRENT_DATE();
```

### MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义

**1、** varchar与char的区别char是一种固定长度的类型，varchar则是一种可变长度的类型

**2、** varchar(50)中50的涵义最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)

**3、** int（20）中20的涵义是指显示字符的长度但要加参数的，最大为255，比如它是记录行数的id,插入10笔资料，它就显示00000000001 ~~~00000000010，当字符的位数超过11,它也只显示11位，如果你没有加那个让它未满11位就前面加0的参数，它不会在前面加020表示最大显示宽度为20，但仍占4字节存储，存储范围不变；

**4、** mysql为什么这么设计对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和int(20)存储和计算均一样；

### innodb的事务与日志的实现方式

**1、** 有多少种日志；错误日志：记录出错信息，也记录一些警告信息或者正确的信息。查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。二进制日志：记录对数据库执行更改的所有操作。中继日志：事务日志：

**2、** 事物的4种隔离级别隔离级别读未提交(RU)读已提交(RC)可重复读(RR)串行

**3、** 事务是如何通过日志来实现的，说得越深入越好。事务日志是通过redo和innodb的存储引擎日志缓冲（Innodb log buffer）来实现的，当开始一个事务的时候，会记录该事务的lsn(log sequence number)号; 当事务执行时，会往InnoDB存储引擎的日志的日志缓存里面插入事务日志；当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过innodb_flush_log_at_trx_commit来控制），也就是写数据前，需要先写日志。这种方式称为“预写日志方式”

### MySQL的binlog有有几种录入格式?分别有什么区别?

有三种格式,statement,row和mixed.

- statement模式下,记录单元为语句.即每一个sql造成的影响会记录.由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法被记录复制.
- row级别下,记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大.
- mixed. 一种折中的方案,普通操作使用statement记录,当无法使用statement的时候使用row.

此外,新版的MySQL中对row级别也做了一些优化,当表结构发生变化的时候,会记录语句而不是逐行记录.

### MySQL数据库cpu飙升到500%的话他怎么处理？

**1、** 列出所有进程  show processlist,观察所有进程 ,多秒没有状态变化的(干掉)

**2、** 查看超时日志或者错误日志 (做了几年开发,一般会是查询以及大批量的插入会导致cpu与i/o上涨,当然不排除网络状态突然断了,,导致一个请求服务器只接受到一半，比如where子句或分页子句没有发送,,当然的一次被坑经历)

### 超大分页怎么处理?

超大的分页一般从两个方向上来解决.

- 数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于`select * from table where age > 20 limit 1000000,10`这种查询其实也是有可以优化的余地的. 这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢. 当时我们可以修改为`select * from table where id in (select id from table where age > 20 limit 1000000,10)`.这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果ID连续的好,我们还可以`select * from table where id > 1000000 limit 10`,效率也是不错的,优化的可能性有许多种,但是核心思想都一样,就是减少load的数据.
- 从需求的角度减少这种请求….主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击.

解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可.

在阿里巴巴《Java开发手册》中,对超大分页的解决办法是类似于上面提到的第一种.

![img](https://imgs.itxueyuan.com/990913-20190806091439548-806498135.png)

### 关心过业务系统里面的sql耗时吗?统计过慢查询吗?对慢查询都怎么优化过?

在业务系统中,除了使用主键进行的查询,其他的我都会在测试库上测试其耗时,慢查询的统计主要由运维在做,会定期将业务中的慢查询反馈给我们.

慢查询的优化首先要搞明白慢的原因是什么? 是查询条件没有命中索引?是load了不需要的数据列?还是数据量太大?

所以优化也是针对这三个方向来的,

- 首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写.
- 分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引.
- 如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表.

### 横向分表和纵向分表,可以分别举一个适合他们的例子吗?

**横向分表是按行分表.**

假设我们有一张用户表,主键是自增ID且同时是用户的ID.数据量较大,有1亿多条,那么此时放在一张表里的查询效果就不太理想.我们可以根据主键ID进行分表,无论是按尾号分,或者按ID的区间分都是可以的. 假设按照尾号0-99分为100个表,那么每张表中的数据就仅有100w.这时的查询效率无疑是可以满足要求的.

**纵向分表是按列分表.**

假设我们现在有一张文章表.包含字段`id-摘要-内容`.而系统中的展示形式是刷新出一个列表,列表中仅包含标题和摘要,当用户点击某篇文章进入详情时才需要正文内容.此时,如果数据量大,将内容这个很大且不经常使用的列放在一起会拖慢原表的查询速度.我们可以将上面的表分为两张.`id-摘要`,`id-内容`.当用户点击详情,那主键再来取一次内容即可.而增加的存储量只是很小的主键字段.代价很小.

当然,分表其实和业务的关联度很高,在分表之前一定要做好调研以及benchmark.不要按照自己的猜想盲目操作.

### 什么是存储过程？有哪些优缺点？

存储过程是一些预编译的SQL语句。

**1、** 更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

**2、** 存储过程是一个预编译的代码块，执行效率比较高,一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率,可以一定程度上确保数据安全

但是,在互联网项目中,其实是不太推荐存储过程的,比较出名的就是阿里的《Java开发手册》中禁止使用存储过程,我个人的理解是,在互联网项目中,迭代太快,项目的生命周期也比较短,人员流动相比于传统的项目也更加频繁,在这样的情况下,存储过程的管理确实是没有那么方便,同时,复用性也没有写在服务层那么好.

### 说一说三个范式

**第一范式（1NF）：** 数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
**第二范式（2NF）：** 数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。
**第三范式（3NF）： ** 在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如 果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段 x → 非关键字段y

### MySQL的复制原理以及流程

> 基本原理流程，3个线程以及之间的关联；

**1、主：** binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；

**2、从：** io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中；

**3、从：** sql执行线程——执行relay log中的语句；

### 六种关联查询

- 交叉连接（CROSS JOIN）
- 内连接（INNER JOIN）
- 外连接（LEFT JOIN/RIGHT JOIN）
- 联合查询（UNION与UNION ALL）
- 全连接（FULL JOIN）
- 交叉连接（CROSS JOIN）

```
SELECT * FROM A,B(,C)或者SELECT * FROM A CROSS JOIN B (CROSS JOIN C)#没有任何关联条件，结果是笛卡尔积，结果集会很大，没有意义，很少使用内连接（INNER JOIN）SELECT * FROM A,B WHERE A.id=B.id或者SELECT * FROM A INNER JOIN B ON A.id=B.id多表中同时符合某种条件的数据记录的集合，INNER JOIN可以缩写为JOIN
```

### 内连接分为三类

- 等值连接：ON A.id=B.id
- 不等值连接：ON A.id > B.id
- 自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

### 外连接（LEFT JOIN/RIGHT JOIN）

- 左外连接：LEFT OUTER JOIN, 以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN
- 右外连接：RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN

### 联合查询（UNION与UNION ALL）

```
SELECT * FROM A UNION SELECT * FROM B UNION ...
```

- 就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并
- 如果使用UNION ALL，不会合并重复的记录行
- 效率 UNION 高于 UNION ALL

### 全连接（FULL JOIN）

- MySQL不支持全连接
- 可以使用LEFT JOIN 和UNION和RIGHT JOIN联合使用

```
SELECT * FROM A LEFT JOIN B ON A.id=B.id UNIONSELECT * FROM A RIGHT JOIN B ON A.id=B.id
```

### UNION与UNION ALL的区别？

- 如果使用UNION ALL，不会合并重复的记录行
- 效率 UNION 高于 UNION ALL

### SQL语句优化的一些方法？

- **1、** 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
- **2、** 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

```
select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=
```

- **3、** 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
- **4、** 应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

```
select id from t where num=10 or num=20可以这样查询：select id from t where num=10 union all select id from t where num=20
```

- **5、** in 和 not in 也要慎用，否则会导致全表扫描，如：

```
select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3
```

- **6、** 下面的查询也将导致全表扫描：select id from t where name like ‘%李%’若要提高效率，可以考虑全文检索。
- **7、** 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

```
select id from t where num=@num可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num
```

- **8、** 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

```
select id from t where num/2=100应改为:select id from t where num=100*2
```

- **9、** 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

```
select id from t where substring(name,1,3)=’abc’ ，name以abc开头的id应改为:select id from t where name like ‘abc%’
```

- **10、** 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

### MySQL由哪些部分组成, 分别用来做什么

**1、Server**

- 连接器: 管理连接, 权限验证.
- 分析器: 词法分析, 语法分析.
- 优化器: 执行计划生成, 索引的选择.
- 执行器: 操作存储引擎, 返回执行结果.

**2、存储引擎** : 存储数据, 提供读写接口.

### 如果一个表有一列定义为TIMESTAMP，将发生什么？

每当行被更改时， 时间戳字段将获取当前时间戳。列设置为 AUTO INCREMENT 时， 如果在表中达到最大值， 会发生什么情况？它会停止递增， 任何进一步的插入都将产生错误， 因为密钥已被使用。

怎样才能找出最后一次插入时分配了哪个自动增量？LAST_INSERT_ID 将返回由 Auto_increment 分配的最后一个值， 并且不需要指定表名称。

### MySQL 里记录货币用什么字段类型好

NUMERIC 和 DECIMAL 类型被 MySQL 实现为同样的类型， 这在 SQL92 标准允许。他们被用于保存值， 该值的准确精度是极其重要的值， 例如与金钱有关的数据。当声明一个类是这些类型之一时， 精度和规模的能被(并且通常是)指定。

例如：

```
salary DECIMAL(9,2)
```

在这个例子中， 9(precision)代表将被用于存储值的总的小数位数，而 2(scale)代表将被用于存储小数点后的位数。因此， 在这种情况下， 能被存储在 salary 列中的值的范围是从-9999999.99 到9999999.99。

### 你们数据库是否支持emoji表情，如果不支持，如何操作？

如果是utf8字符集的话，需要升级至utf8_mb4方可支持

### MySQL 数据库作发布系统的存储，一天五万条以上的增量， 预计运维三年,怎么优化？

**1、** 设计良好的数据库结构， 允许部分数据冗余， 尽量避免 join 查询， 提高效率。

**2、** 选择合适的表字段数据类型和存储引擎， 适当的添加索引。

**3、** MySQL 库主从读写分离。

**4、** 找规律分表， 减少单表中的数据量提高查询速度。5、添加缓存机制， 比如 memcached， apc 等。

**5、** 不经常改动的页面， 生成静态页面。

**6、** 书写高效率的 SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.

### 一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。

**1、如果A表TID是自增长,并且是连续的,B表的ID为索引**

```sql
select * from a,b where a.tid = b.id and a.tid>500000 limit 200;
```

**2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。**

```sql
select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;
```

### 优化数据库的方法

**1、** 选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置 NOTNULL， 例如’ 省份’、’ 性别’ 最好适用 ENUM

**2、** 使用连接(JOIN)来代替子查询

**3、** 适用联合(UNION)来代替手动创建的临时表

**4、** 事务处理

**5、** 锁定表、优化事务处理

**6、** 适用外键， 优化锁定表

**7、** 建立索引

**8、** 优化查询语句

### 简单描述 MySQL 中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响（从读写两方面）

索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分)， 它们包含着对数据表里所有记录的引用指针。

普通索引(由关键字 KEY 或 INDEX 定义的索引)的唯一任务是加快对数据的访问速度。

普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值， 在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一索引。也就是说， 唯一索引可以保证数据记录的唯一性。

主键， 是一种特殊的唯一索引， 在一张表中只能定义一个主键索引， 主键用于唯一标识一条记录， 使用关键字 PRIMARY KEY 来创建。

索引可以覆盖多个数据列，如像 INDEX(columnA, columnB)索引，这就是联合索引。

索引可以极大的提高数据的查询速度， 但是会降低插入、删除、更新表的速度， 因为在执行这些写操作时， 还要操作索引文件。

### SQL 注入漏洞产生的原因？如何防止？

**SQL 注入产生的原因：** 程序开发过程中不注意规范书写 sql 语句和对特殊字符进行过滤，导致客户端可以通过全局变量 POST 和 GET 提交一些 sql 语句正常执行。防止 SQL 注入的方式：

开启配置文件中的 magic_quotes_gpc 和 magic_quotes_runtime 设置

执行 sql 语句时使用 addslashes 进行 sql 语句转换Sql 语句书写尽量不要省略双引号和单引号。

过滤掉 sql 语句中的一些关键词： update、insert、delete、select、 * 。

提高数据库表和字段的命名技巧， 对一些重要的字段根据程序的特点命名， 取不易被猜到的。

### 存储时期

**Datatime：**

以 YYYY-MM-DD HH:MM:SS 格式存储时期时间， 精确到秒， 占用 8 个字节得存储空间， datatime 类型与时区无关

**Timestamp：**

以时间戳格式存储，占用 4 个字节，范围小 1970-1-1 到 2038-1-19， 显示依赖于所指定得时区， 默认在第一个列行的数据修改时可以自动得修改timestamp 列得值

**Date（ 生日）：**

占用得字节数比使用字符串.datatime.int 储存要少， 使用 date 只需要 3 个字节， 存储日期月份， 还可以利用日期时间函数进行日期间得计算Time:存储时间部分得数据

**注意:** 不要使用字符串类型来存储日期时间数据（ 通常比字符串占用得储存空间小， 在进行查找过滤可以利用日期得函数）使用 int 存储日期时间不如使用 timestamp 类型

### 解释 MySQL 外连接、内连接与自连接的区别

**先说什么是交叉连接:**

交叉连接又叫笛卡尔积，它是指不使用任何条件，直接将一个表的所有记录和另一个表中的所有记录一一匹配。

**内连接** 则是只有条件的交叉连接，根据某个条件筛选出符合条件的记录，不符合条件的记录不会出现在结果集中， 即内连接只连接匹配的行。

**外连接** 其结果集中不仅包含符合连接条件的行，而且还会包括左表、右表或两个表中的所有数据行，

这三种情况依次称之为左外连接， 右外连接， 和全外连接。左外连接， 也称左连接， 左表为主表， 左表中的所有记录都会出现在结果集中， 对于那些在右表中并没有匹配的记录， 仍然要显示， 右边对应的那些字段值以NULL 来填充。右外连接，也称右连接，右表为主表，右表中的所有记录都会出现在结果集中。左连接和右连接可以互换， MySQL 目前还不支持全外连接。

### 存储引擎常用命令

**查看MySQL提供的所有存储引擎**

```sql
mysql> show engines;
```

![](https://gitee.com/gsjqwyl/images_repo/raw/master/2021-3-11/20210325102701.png)

从上图我们可以查看出 MySQL 当前默认的存储引擎是InnoDB,并且在5.7版本所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 支持事务。

**查看MySQL当前默认的存储引擎**

我们也可以通过下面的命令查看默认的存储引擎。

```sql
mysql> show variables like '%storage_engine%';
```

**查看表的存储引擎**

```sql
show table status like "table_name" ;
```

![](https://gitee.com/gsjqwyl/images_repo/raw/master/2021-3-11/20210325102753.png)

### MySQL支持哪些存储引擎?

MySQL支持多种存储引擎,比如InnoDB,MyISAM,Memory,Archive等等.在大多数的情况下,直接选择使用InnoDB引擎都是最合适的,InnoDB也是MySQL的默认存储引擎.

### 请说明InnoDB和MyISAM的区别

- InnoDB支持事务，MyISAM不支持；
- InnoDB数据存储在共享表空间，MyISAM数据存储在文件中；
- InnoDB支持行级锁，MyISAM只支持表锁；
- InnoDB支持崩溃后的恢复，MyISAM不支持；
- InnoDB支持外键，MyISAM不支持；
- InnoDB不支持全文索引，MyISAM支持全文索引；

### innodb引擎的特性

- 插入缓冲（insert buffer)
- 二次写(double write)
- 自适应哈希索引(ahi)
- 预读(read ahead)

### 什么是触发器，MySQL中都有哪些触发器？

触发器是指一段代码，当触发某个事件时，自动执行这些代码。在MySQL数据库中有如下六种触发器：

- 1、Before Insert
- 2、After Insert
- 3、Before Update
- 4、After Update
- 5、Before Delete
- 6、After Delete

### InnoDB和MyISAM有什么区别?

- InnoDB支持事物，而MyISAM不支持事物
- InnoDB支持行级锁，而MyISAM支持表级锁
- InnoDB支持MVCC, 而MyISAM不支持
- InnoDB支持外键，而MyISAM不支持
- InnoDB不支持全文索引，而MyISAM支持。

|                                                              | MyISAM                                            | Innodb                                   |
| ------------------------------------------------------------ | ------------------------------------------------- | ---------------------------------------- |
| 文件格式                                                     | 数据和索引是分别存储的，数据`.MYD`，索引`.MYI`    | 数据和索引是集中存储的，`.ibd`           |
| 文件能否移动                                                 | 能，一张表就对应`.frm`、`MYD`、`MYI`3个文件       | 否，因为关联的还有`data`下的其它文件     |
| 记录存储顺序                                                 | 按记录插入顺序保存                                | 按主键大小有序插入                       |
| 空间碎片（删除记录并`flush table 表名`之后，表文件大小不变） | 产生。定时整理：使用命令`optimize table 表名`实现 | 不产生                                   |
| 事务                                                         | 不支持                                            | 支持                                     |
| 外键                                                         | 不支持                                            | 支持                                     |
| 锁支持（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的） | 表级锁定                                          | 行级锁定、表级锁定，锁定力度小并发能力高 |

### myisamchk 是用来做什么的？

它用来压缩 MyISAM 表， 这减少了磁盘或内存使用。

MyISAM Static 和 MyISAM Dynamic 有什么区别？

在 MyISAM Static 上的所有字段有固定宽度。动态 MyISAM 表将具有像 TEXT， BLOB 等字段， 以适应不同长度的数据类型。

MyISAM Static 在受损情况下更容易恢复。

### 为什么要尽量设定一个主键?

主键是数据库确保数据行在整张表唯一性的保障,即使业务上本张表没有主键,也建议添加一个自增长的ID列作为主键.设定了主键之后,在后续的删改查的时候可能更加快速以及确保操作数据范围安全.

### 主键使用自增ID还是UUID?

推荐使用自增ID,不要使用UUID.

因为在InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也就是说,主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序),如果主键索引是自增ID,那么只需要不断向后排列即可,如果是UUID,由于到来的ID与原来的大小不确定,会造成非常多的数据插入,数据移动,然后导致产生很多的内存碎片,进而造成插入性能的下降.

总之,在数据量大一些的情况下,用自增主键性能会好一些.

*图片来源于《高性能MySQL》: 其中默认后缀为使用自增ID,_uuid为使用UUID为主键的测试,测试了插入100w行和300w行的性能.*

![img](https://imgs.itxueyuan.com/990913-20190806091416714-780950030.png)

关于主键是聚簇索引,如果没有主键,InnoDB会选择一个唯一键来作为聚簇索引,如果没有唯一键,会生成一个隐式的主键.

> If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.
>
> If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index.

### 字段为什么要求定义为not null?

MySQL官网这样介绍:

> NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.

null值会占用更多的字节,且会在程序中造成很多与预期不符的情况.

### 如果要存储用户的密码散列,应该使用什么字段进行存储?

密码散列,盐,用户身份证号等固定长度的字符串应该使用char而不是varchar来存储,这样可以节省空间且提高检索效率.

### 索引是个什么样的数据结构呢?

索引的数据结构和具体存储引擎的实现有关, 在MySQL中使用较多的索引有Hash索引,B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引.

### 唯一索引比普通索引快吗, 为什么

唯一索引不一定比普通索引快, 还可能慢.

**1、查询时,** 在未使用`limit 1`的情况下, 在匹配到一条数据后, 唯一索引即返回, 普通索引会继续匹配下一条数据, 发现不匹配后返回. 如此看来唯一索引少了一次匹配, 但实际上这个消耗微乎其微.

**2、更新时** , 这个情况就比较复杂了. 普通索引将记录放到`change buffer`中语句就执行完毕了. 而对唯一索引而言, 它必须要校验唯一性, 因此, 必须将数据页读入内存确定没有冲突, 然后才能继续操作. 对于**写多读少**的情况, 普通索引利用`change buffer`有效减少了对磁盘的访问次数, 因此普通索引性能要高于唯一索引.

### 索引的优缺点

**优点**

- 提高数据检索的效率，降低数据库的 IO 成本。
- 通过索引列对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗。

**缺点**

- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为 更新所带来的键值变化后的索引信息。
- 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间 的。

### 做过哪些MySQL索引相关优化

- 尽量使用主键查询: 聚簇索引上存储了全部数据, 相比普通索引查询, 减少了回表的消耗.

- MySQL5.6之后引入了索引下推优化, 通过适当的使用联合索引, 减少回表判断的消耗.

- 若频繁查询某一列数据, 可以考虑利用覆盖索引避免回表.

- 联合索引将高频字段放在最左边.

### 怎么看到为表格定义的所有索引？

索引是通过以下方式为表格定义的：

SHOW INDEX FROM ;

### 索引的基础

- 索引类似于书籍的目录，要想找到一本数的某个特定主题，需要先查找书的目录，定位对应的页码
- 存储引擎使用类似的方式进行数据查询，先去索引当中找到对应的值，然后根据匹配的索引找到对应的数据行。

### 创建索引的语法

- 首先创建一个表：create table t1 (id int primary key,username varchar(20),password varchar(20));
- 创建单个索引的语法：CREATE INDEX 索引名 on 表名（字段名）
- 索引名一般是：表名_字段名
- 给id创建索引：CREATE INDEX t1_id on t1(id);
- 创建联合索引的语法：CREATE INDEX 索引名 on 表名（字段名1，字段名2）
- 给username和password创建联合索引：CREATE index t1_username_password ON t1(username,password)
- 其中index还可以替换成unique，primary key，分别代表唯一索引和主键索引
- 删除索引：DROP INDEX t1_username_password ON t1

### 索引对性能的影响：

- 大大减少服务器需要扫描的数据量。
- 帮助服务器避免排序和临时表。
- 将随机I/O变顺序I/O。
- 大大提高查询速度。
- 降低写的速度（不良影响）。
- 磁盘占用（不良影响）。

### 索引的类型

索引很多种类型，是在MySQL的存储引擎实现的。

- 普通索引：最基本的索引，没有任何约束限制。
- 唯一索引：和普通索引类似，但是具有唯一性约束。
- 主键索引：特殊的唯一索引，不允许有空值。

### 索引的区别

-一个表只能有一个主键索引，但是可以有多个唯一索引。

- 主键索引一定是唯一索引，唯一索引不是主键索引。
- 主键可以与外键构成参照完整性约束，防止数据不一致。
- 联合索引：将多个列组合在一起创建索引，可以覆盖多个列。（也叫复合索引，组合索引）
- 外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性、和实现级联操作（基本不用）。
- 全文索引：MySQL自带的全文索引只能用于MyISAM，并且只能对英文进行全文检索 （基本不用）

### MySQL索引的创建原则

- 最适合创建索引的列是出现在WHERE或ON子句中的列，或连接子句中的列而不是出现在SELECT关键字后的列。
- 索引列的基数越大，数据区分度越高，索引的效果越好。
- 对于字符串进行索引，应该制定一个前缀长度，可以节省大量的索引空间。
- 根据情况创建联合索引，联合索引可以提高查询效率。
- 避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率。
- 主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率。

### 索引的使用场景

- 对于非常小的表，大部分情况下全表扫描效率更高。
- 中到大型表，索引非常有效。
- 特大型的表，建立和使用索引的代价会随之增大，可以使用分区技术来解决。

### 索引分类

**单值索引：** 即一个索引只包含单个列，一个表可以有多个单列索引

- 建表时，加上 `key(列名)` 指定
- 单独创建，`create index 索引名 on 表名(列名)`
- 单独创建，`alter table 表名 add index 索引名(列名)`

**唯一索引：** 索引列的值必须唯一，但允许有 null 且 null 可以出现多次

- 建表时，加上 `unique(列名)` 指定
- 单独创建，`create unique index idx_表名_列名 on 表名(列名)`
- 单独创建，`alter table 表名 add unique 索引名(列名)`

**主键索引：** 设定为主键后数据库会自动建立索引，innodb 为聚簇索引，值必须唯一且不能为 null

- 建表时，加上 `primary key(列名)` 指定

**复合索引：** 即一个索引包含多个列

- 建表时，加上 `key(列名列表)` 指定
- 单独创建，`create index 索引名 on 表名(列名列表)`
- 单独创建，`alter table 表名 add index 索引名(列名列表)`

### MySQL索引的注意事项

**1、联合索引遵循前缀原则**

```
KEY(a,b,c)WHERE a = 1 AND b = 2 AND c = 3WHERE a = 1 AND b = 2WHERE a = 1#以上SQL语句可以用到索引WHERE b = 2 AND c = 3WHERE a = 1 AND c = 3#以上SQL语句用不到索引
```

**2、LIKE查询，%不能在前**

```
WHERE name LIKE "%wang%"#以上语句用不到索引，可以用外部的ElasticSearch、Lucene等全文搜索引擎替代。
```

**3、** 列值为空（NULL）时是可以使用索引的，但MySQL难以优化引用了可空列的查询,它会使索引、索引统计和值更加复杂。可空列需要更多的储存空间，还需要在MySQL内部进行特殊处理。

**4、** 如果MySQL估计使用索引比全表扫描更慢，会放弃使用索引，例如：
表中只有100条数据左右。对于SQL语句WHERE id > 1 AND id < 100，MySQL会优先考虑全表扫描。

**5、** 如果关键词or前面的条件中的列有索引，后面的没有，所有列的索引都不会被用到。

**6、** 列类型是字符串，查询时一定要给值加引号，否则索引失效，例如：
列name varchar(16)，存储了字符串"100"
WHERE name = 100;
以上SQL语句能搜到，但无法用到索引。

### MySQL索引的原理

- MySQL索引是用一种叫做聚簇索引的数据结构实现的，下面我们就来看一下什么是聚簇索引。
- 聚簇索引是一种数据存储方式，它实际上是在同一个结构中保存了B+树索引和数据行，InnoDB表是按照聚簇索引组织的（类似于Oracle的索引组织表）。

```
注：B+ 树是一种树数据结构，是一个n叉排序树，每个节点通常有多个孩子，一棵B+树包含根节点、内部节点和叶子节点。根节点可能是一个叶子节点，也可能是一个包含两个或两个以上孩子节点的节点。B+ 树通常用于数据库和操作系统的文件系统中。NTFS, ReiserFS, NSS, XFS, JFS, ReFS 和BFS等文件系统都在使用B+树作为元数据索引。B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入。
```

InnoDB通过主键聚簇数据，如果没有定义主键，会选择一个唯一的非空索引代替，如果没有这样的索引，会隐式定义个主键作为聚簇索引。
下图形象说明了聚簇索引表(InnoDB)和普通的堆组织表(MyISAM)的区别：

最常问的MySQL面试题三——每个开发人员都应该知道
对于普通的堆组织表来说（右图），表数据和索引是分别存储的，主键索引和二级索引存储上没有任何区别。
而对于聚簇索引表来说（左图），表数据是和主键一起存储的，主键索引的叶结点存储行数据，二级索引的叶结点存储行的主键值。
聚簇索引表最大限度地提高了I/O密集型应用的性能，但它也有以下几个限制：

- 1）插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
- 2）更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
- 3）二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

二级索引的叶节点存储的是主键值，而不是行指针，这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。

#### 解题方法

在一些MySQL索引基础考题中，我们可以轻松的通过索引基础和类型来解决此类问题，对于一些索引创建注意事项方面的考点，我们可以通过索引创建原则和注意事项来解决。

### 创建MySQL联合索引应该注意什么？

需遵循前缀原则

### 列值为NULL时，查询是否会用到索引？

在MySQL里NULL值的列也是走索引的。当然，如果计划对列进行索引，就要尽量避免把它设置为可空，MySQL难以优化引用了可空列的查询,它会使索引、索引统计和值更加复杂。

### 以下语句是否会应用索引：SELECT FROM users WHERE YEAR(adddate) < 2007;

不会，因为只要列涉及到运算，MySQL就不会使用索引。

### MyISAM索引实现？

MyISAM存储引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。MyISAM的索引方式也叫做非聚簇索引的，之所以这么称呼是为了与InnoDB的聚簇索引区分。

### MyISAM索引与InnoDB索引的区别？

- InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
- InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
- MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
- InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

### 使用索引查询一定能提高查询的性能吗？为什么

通常,通过索引查询数据比全表扫描要快.但是我们也必须注意到它的代价.

> 1、索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:
> 2、基于一个范围的检索,一般查询返回结果集小于表中记录数的30%
> 3、基于非唯一性索引的检索

### 什么情况下设置了索引但无法使用

**1、** 以“%” 开头的 LIKE 语句， 模糊匹配

**2、** OR 语句前后没有同时使用索引

**3、** 数据类型出现隐式转化（ 如 varchar 不加单引号的话可能会自动转换为 int 型）

### 超键、候选键、主键、外键分别是什么？

**1、超键：** 在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
**2、候选键：** 是最小超键，即没有冗余元素的超键。
**3、主键：** 数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
**4、外键：** 在一个表中存在的另一个表的主键称此表的外键。

### 简单说一说drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别

> 1、delete和truncate只删除表的数据不删除表的结构
> 2、速度,一般来说: drop> truncate >delete
> 3、delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;
> 4、如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

### drop、delete与truncate分别在什么场景之下使用？

**1、** 不再需要一张表的时候，用drop
**2、** 想删除部分数据行时候，用delete，并且带上where子句
**3、** 保留表而删除所有数据的时候用truncate

### B-Tree 和 B+Tree

**区别**

1. B-Tree 的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+Tree 的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。
2. 在 B-Tree 中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而 B+Tree 中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看 B-Tree 的性能好像要比 B+Tree 好，而在实际应用中却是 B+Tree 的性能要好些。因为 B+Tree 的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比 B-Tree 多，树高比 B-Tree 小，这样带来的好处是减少磁盘访问次数。尽管 B+Tree 找到一个记录所需的比较次数要比 B-Tree 多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中 B+Tree 的性能可能还会好些，而且 B+Tree 的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用 B+Tree 的缘故。

### 为什么 B+Tree 比 B-Tree 更适合实际应用中操作系统的文件索引和数据库索引？**

1. B+Tree 的磁盘读写代价更低

> B+Tree 的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B-Tree 更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了。

1. B+Tree 的查询效率更加稳定

> 由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

### 什么是视图？以及视图的使用场景有哪些？

**1、** 视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。
**2、** 只暴露部分字段给访问者，所以就建一个虚表，就是视图。
**3、** 查询的数据来源于不同的表，而查询者希望以统一的方式查询，这样也可以建立一个视图，把多个表查询结果联合起来，查询者只需要直接从视图中获取数据，不必考虑数据来源于不同表所带来的差异

### Hash索引和B+树所有有什么区别或者说优劣呢?

首先要知道Hash索引和B+树索引的底层实现原理:

hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据.B+树底层实现是多路平衡查找树.对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据.

那么可以看出他们有以下的不同:

- hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询.

因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询.而B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围.

- hash索引不支持使用索引进行排序,原理同上.
- hash索引不支持模糊查询以及多列索引的最左前缀匹配.原理也是因为hash函数的不可预测.**AAAA**和**AAAAB**的索引没有相关性.
- hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询.
- hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差.而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低.

因此,在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度.而不需要使用hash索引.

### 为什么用 B+ 树做索引而不用哈希表做索引?

**1、** 哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个表。而B+树则可以通过最左前缀原则快速找到对应的数据。

**2、** 如果我们要进行范围查找，例如查找ID为100 ~ 400的人，哈希表同样不支持，只能遍历全表。

**3、** 索引字段通过哈希映射成哈希码，如果很多字段都刚好映射到相同值的哈希码的话，那么形成的索引结构将会是一条很长的**链表**，这样的话，查找的时间就会大大增加。

### 索引是什么？有什么作用以及优缺点？

**1、** 索引是对数据库表中一或多个列的值进行排序的结构，是帮助MySQL高效获取数据的数据结构
**2、** 索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

**MySQL数据库几个基本的索引类型：普通索引、唯一索引、主键索引、全文索引**

**1、** 索引加快数据库的检索速度
**2、** 索引降低了插入、删除、修改等维护任务的速度
**3、** 唯一索引可以确保每一行数据的唯一性
**4、** 通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能
**5、** 索引需要占物理和数据空间

### 上面提到了B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据,什么是聚簇索引?

在B+树的索引中,叶子节点可能存储了当前的key值,也可能存储了当前的key值以及整行的数据,这就是聚簇索引和非聚簇索引. 在InnoDB中,只有主键索引是聚簇索引,如果没有主键,则挑选一个唯一键建立聚簇索引.如果没有唯一键,则隐式的生成一个键来建立聚簇索引.

当查询使用聚簇索引时,在对应的叶子节点,可以获取到整行数据,因此不用再次进行回表查询.

### 非聚簇索引一定会回表查询吗?

不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回表查询.

举个简单的例子,假设我们在员工表的年龄上建立了索引,那么当进行`select age from employee where age < 20`的查询时,在索引的叶子节点上,已经包含了age信息,不会再次进行回表查询.

### 在建立索引的时候,都有哪些需要考虑的因素呢?

建立索引的时候一般要考虑到字段的使用频率,经常作为条件进行查询的字段比较适合.如果需要建立联合索引的话,还需要考虑联合索引中的顺序.此外也要考虑其他方面,比如防止过多的所有对表造成太大的压力.这些都和实际的表结构以及查询方式有关.

### 联合索引是什么?为什么需要注意联合索引中的顺序?

MySQL可以使用多个字段同时建立一个索引,叫做联合索引.在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引.

具体原因为:

MySQL使用索引时需要索引有序,假设现在建立了"name,age,school"的联合索引,那么索引的排序为: 先按照name排序,如果name相同,则按照age排序,如果age的值也相等,则按照school进行排序.

当进行查询时,此时索引仅仅按照name严格有序,因此必须首先使用name字段进行等值查询,之后对于匹配到的列而言,其按照age字段严格有序,此时可以使用age字段用做索引查找,,,以此类推.因此在建立联合索引的时候应该注意索引列的顺序,一般情况下,将查询需求频繁或者字段选择性高的列放在前面.此外可以根据特例的查询或者表结构进行单独的调整.

### 创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?

MySQL提供了explain命令来查看语句的执行计划,MySQL在执行某个语句之前,会将该语句过一遍查询优化器,之后会拿到对语句的分析,也就是执行计划,其中包含了许多信息. 可以通过其中和索引有关的信息来分析是否命中了索引,例如possilbe_key,key,key_len等字段,分别说明了此语句可能会使用的索引,实际使用的索引以及使用的索引长度.

### 那么在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢?

- 使用不等于查询,
- 列参与了数学运算或者函数
- 在字符串like时左边是通配符.类似于'%aaa'.
- 当mysql分析全表扫描比使用索引快的时候不使用索引.
- 当使用联合索引,前面一个条件为范围查询,后面的即使符合最左前缀原则,也无法使用索引.

以上情况,MySQL无法使用索引.

### 什么是事务?

**事务是逻辑上的一组操作，要么都执行，要么都不执行。**

理解什么是事务最经典的就是转账的栗子,相信大家也都了解,这里就不再说一边了.

事务是一系列的操作,他们要符合ACID特性.最常见的理解就是:事务中的操作要么全部成功,要么全部失败.但是只是这样还不够的.

### 数据库的乐观锁和悲观锁是什么？

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

**悲观锁：** 假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
**乐观锁：** 假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

### ACID是什么?可以详细说一下吗?

**A=Atomicity**

**原子性：** 就是上面说的,要么全部成功,要么全部失败.不可能只执行一部分操作.

**C=Consistency**

**一致性：** 系统(数据库)总是从一个一致性的状态转移到另一个一致性的状态,不会存在中间状态.

**I=Isolation**

**隔离性:**  通常来说:一个事务在完全提交之前,对其他事务是不可见的.注意前面的通常来说加了红色,意味着有例外情况.

**D=Durability**

**持久性：**  一旦事务提交,那么就永远是这样子了,哪怕系统崩溃也不会影响到这个事务的结果.

### 同时有多个事务在进行会怎么样呢?

**事务（ transaction）** 是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功， 则认为事务成功， 即使只有一个操作失败， 事务也不成功。如果所有操作完成， 事务则提交， 其修改将作用于所有其他数据库进程。如果一个操作失败， 则事务将回滚， 该事务所有操作的影响都将取消。

**事务特性：**

**1、原子性。** 即不可分割性， 事务要么全部被执行， 要么就全部不被执行。

**2、一致性或可串性。** 事务的执行使得数据库从一种正确状态转换成另一种正确状  态

**3、隔离性。** 在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何  其他事务，

**4、持久性。** 事务正确提交后， 其结果将永久保存在数据库中， 即使在事务提交后有了其他故障， 事务的处理结果也会得到保存。或者这样理解：事务就是被绑定在一起作为一个逻辑工作单元的 SQL 语句分组， 如果任何一个语句操作失败那么整个操作就被失败， 以后操作就会回滚到操作前状态， 或者是上有个节点。为了确保要么执行， 要么不执行， 就可以使用事务。要将有组语句作为事务考虑， 就需要通过 ACID 测试， 即原子性， 一致性， 隔离性和持久性。

### Myql 中的事务回滚机制概述

事务是用户定义的一个数据库操作序列， 这些操作要么全做要么全不做， 是一个不可分割的工作单位。

事务回滚是指将该事务已经完成的对数据库的更新操作撤销。要同时修改数据库中两个不同表时， 如果它们不是一个事务的话， 当第一个表修改完， 可能第二个表修改过程中出现了异常而没能修改， 此时就只有第二个表依旧是未修改之前的状态， 而第一个表已经被修改完毕。而当你把它们设定为一个事务的时候， 当第一个表修改完， 第二表修改出现异常而没能修改， 第一个表和第二个表都要回到未修改的状态， 这就是所谓的事务回滚

### 并发事务带来哪些问题?

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

- **脏读（Dirty read）:** 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
- **丢失修改（Lost to modify）:**  指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
- **不可重复读（Unrepeatableread）:** 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
- **幻读（Phantom read）:**  幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

**不可重复读和幻读区别：**

不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

### 怎么解决这些问题呢?MySQL的事务隔离级别了解吗?

MySQL的四种隔离级别如下:

- 未提交读(READ UNCOMMITTED)

这个隔离级别下,其他事务可以看到本事务没有提交的部分修改.因此会造成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚).

这个级别的性能没有足够大的优势,但是又有很多的问题,因此很少使用.

- 已提交读(READ COMMITTED)

其他事务只能读取到本事务已经提交的部分.这个隔离级别有 不可重复读的问题,在同一个事务内的两次读取,拿到的结果竟然不一样,因为另外一个事务对数据进行了修改.

- REPEATABLE READ(可重复读)

可重复读隔离级别解决了上面不可重复读的问题(看名字也知道),但是仍然有一个新问题,就是 幻读,当你读取id> 10 的数据行时,对涉及到的所有行加上了读锁,此时例外一个事务新插入了一条id=11的数据,因为是新插入的,所以不会触发上面的锁的排斥,那么进行本事务进行下一次的查询时会发现有一条id=11的数据,而上次的查询操作并没有获取到,再进行插入就会有主键冲突的问题.

- SERIALIZABLE(可串行化)

这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用.

### Innodb使用的是哪种隔离级别呢?

InnoDB默认使用的是可重复读隔离级别.

### MySQL 中有哪几种锁？

**1、表级锁：** 开销小， 加锁快； 不会出现死锁； 锁定粒度大， 发生锁冲突的概率最高， 并发度最低。

**2、行级锁：**  开销大， 加锁慢； 会出现死锁； 锁定粒度最小， 发生锁冲突的概率最低， 并发度也最高。

**3、页面锁：**  开销和加锁时间界于表锁和行锁之间； 会出现死锁； 锁定粒度界于表锁和行锁之间， 并发度一般。

### 对MySQL的锁了解吗?

当数据库有并发事务的时候,可能会产生数据的不一致,这时候需要一些机制来保证访问的次序,锁机制就是这样的一个机制.

就像酒店的房间,如果大家随意进出,就会出现多人抢夺同一个房间的情况,而在房间上装上锁,申请到钥匙的人才可以入住并且将房间锁起来,其他人只有等他使用完毕才可以再次使用.

### 锁机制与InnoDB锁算法

**MyISAM和InnoDB存储引擎使用的锁：**

- MyISAM采用表级锁(table-level locking)。
- InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

**表级锁和行级锁对比：**

- **表级锁：** MySQL中锁定 **粒度最大** 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。

- **行级锁：** MySQL中锁定 **粒度最小** 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

**InnoDB存储引擎的锁的算法有三种：**

- Record lock：单个行记录上的锁
- Gap lock：间隙锁，锁定一个范围，不包括记录本身
- Next-key lock：record+gap 锁定一个范围，包含记录本身

### MySQL都有哪些锁呢?像上面那样子进行锁定岂不是有点阻碍并发效率了?

从锁的类别上来讲,有共享锁和排他锁.

**共享锁:**  又叫做读锁. 当用户要进行数据的读取时,对数据加上共享锁.共享锁可以同时加上多个.

**排他锁:**  又叫做写锁. 当用户要进行数据的写入时,对数据加上排他锁.排他锁只可以加一个,他和其他的排他锁,共享锁都相斥.

用上面的例子来说就是用户的行为有两种,一种是来看房,多个用户一起看房是可以接受的. 一种是真正的入住一晚,在这期间,无论是想入住的还是想看房的都不可以.

锁的粒度取决于具体的存储引擎,InnoDB实现了行级锁,页级锁,表级锁.

他们的加锁开销从大大小,并发能力也是从大到小.

### 锁的优化策略

**1、** 读写分离

**2、** 分段加锁

**3、** 减少锁持有的时间

多个线程尽量以相同的顺序去获取资源

不能将锁的粒度过于细化， 不然可能会出现线程的加锁和释放次数过多， 反而效率不如一次加一把大锁。

### Explain 性能分析

**是什么**

查看执行计划：使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的。分析查询语句或是表结构的性能瓶颈。

**能干嘛**

- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询

**怎么玩**

Explain + SQL 语句。
Explain 执行后返回的信息：
 ![img](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/5a1a6ab4fe3842da846e89f69de62ff2~tplv-k3u1fbpfcp-watermark.image)

**各字段解释**

1. id：select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序。

- id 相同，执行顺序由上至下
   - id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
   - id 有相同也有不同：id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行

> id 号每个号码，表示一趟独立的查询。一个 sql 的查询趟数越少越好。

2. select_type：代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询，取值范围如下：

- simple：简单的 select 查询，查询中不包含子查询或者 UNION
   - primary：查询中若包含任何复杂的子部分，最外层查询则被标记为 primary
   - derived：在 FROM 列表中包含的子查询被标记为 DERIVED (衍生)，MySQL 会递归执行这些子查询, 把结果放在临时表里。
   - subquery：在 SELECT 或 WHERE 列表中包含了子查询
   - depedent subquery：在 SELECT 或 WHERE 列表中包含了子查询，子查询基于外层
   - uncacheable subquery：无法使用缓存的子查询
   - union：若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION；若 UNION 包含在 FROM 子句的子查询中，外层 SELECT 将被标记为：DERIVED
   - union result：从 UNION 表获取结果的 SELECT

3. table：这个数据是基于哪张表的。

4. type：是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

> 只需要记住：system > const > eq_ref > ref > range > index > ALL 就行了，其他的不常见。

- system：表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也可以忽略不计。
   - const：表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量。
   - eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
   - ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
   - range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引一般就是在 where 语句中出现了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
   - index：出现 index 是 sql 使用了索引但是没用索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。
   - all：将遍历全表以找到匹配的行。

> 其他 type 如下：

- index_merge：在查询过程中需要多个索引组合使用，通常出现在有 or 关键字的 sql 中。
   - ref_or_null：对于某个字段既需要过滤条件，也需要 null 值的情况下。查询优化器会选择用 ref_or_null 连接查询。
   - index_subquery：利用索引来关联子查询，不再全表扫描。
   - unique_subquery：该联接类型类似于 index_subquery。子查询中的唯一索引。

5. possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

6. key：实际使用的索引。如果为 NULL，则没有使用索引。

7. key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len 显示的值为索引字段的最大可能长度，并非实际使用长度。如何计算 key_len？

- 先看索引上字段的类型 + 长度，比如：int=4; varchar(20)=20; char(20)=20
   - 如果是 varchar 或者 char 这种字符串字段，视字符集要乘不同的值，比如 utf-8 要乘 3，GBK 要乘 2
   - varchar 这种动态字符串要加 2 个字节
   - 允许为空的字段要加 1 个字节

8. ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

9. rows：显示 MySQL 认为它执行查询时必须检查的行数。越少越好！

10. Extra：其他的额外重要的信息。

- Using filesort：说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”。**排序字段若通过索引去访问将大大提高排序速度**。
    - Using temporary：使用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
    - Using index：表示相应的 select 操作中使用了覆盖索引 (Covering Index)，避免访问了表的数据行，效率不错！如果同时出现 using where，表明索引被用来执行索引键值的查找；如果没有同时出现 using where，表明索引只是用来读取数据而非利用索引执行查找。
    - Using where：表明使用了 where 过滤。
    - Using join buffer：使用了连接缓存。
    - impossible where：where 子句的值总是 false，不能用来获取任何数据。
    - select tables optimized away：在没有 group by 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
    - distinct：优化 distinct 操作，在找到第一匹配的元祖后即停止找同样值的动作。

### 如何优化SQL

**1、SQL语句中IN包含的值不应过多**

MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：`select id from table_name where num in(1,2,3)` 对于连续的数值，能用 between 就不要用 in 了；再或者使用连接来替换。

**2、SELECT语句务必指明字段名称**

SELECT *增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。

**3、当只需要一条数据的时候，使用limit 1**

这是为了使EXPLAIN中type列达到const类型

**4、如果排序字段没有用到索引，就尽量少排序**

**5、如果限制条件中其他字段没有索引，尽量少用or**

or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果

**6、尽量用union all代替union**

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。**当然，union all的前提条件是两个结果集没有重复数据。**

**7、不使用ORDER BY RAND()**

```sql
select id from `table_name` order by rand() limit 1000;
```

上面的sql语句，可优化为

```sql
select id from `table_name` t1 join (select rand() * (select max(id) from `table_name`) as nid) t2 ont1.id > t2.nid limit 1000;
```

**8、区分in和exists， not in和not exists**

```sql
select * from 表A where id in (select id from 表B)
```

上面sql语句相当于

```sql
select * from 表A where exists(select * from 表B where 表B.id=表A.id)
```

区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以**IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。**
 关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。**如何高效的写出一个替代not exists的sql语句？**

原sql语句

```sql
select colname … from A表 where a.id not in (select b.id from B表)
```

高效的sql语句

```csharp
select colname … from A表 Left join B表 on where a.id = b.id where b.id is null
```

取出的结果集如下图表示，A表不在B表中的数据

![](https://gitee.com/gsjqwyl/images_repo/raw/master/2021-3-11/20210325104417.png)

**9、使用合理的分页方式以提高分页的效率**

```sql
select id,name from table_name limit 866613, 20
```

使用上述sql语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。

优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。sql可以采用如下的写法：

```csharp
select id,name from table_name where id> 866612 limit 20
```

**10、分段查询**

在一些用户选择页面中，可能一些用户选择的时间范围过大，造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。

如下图这个sql语句，扫描的行数成百万级以上的时候就可以使用分段查询

![](https://gitee.com/gsjqwyl/images_repo/raw/master/2021-3-11/20210325104641.png)

**11、避免在 where 子句中对字段进行 null 值判断**

对于null的判断会导致引擎放弃使用索引而进行全表扫描。

**12、不建议使用%前缀模糊查询**

例如LIKE “%name”或者LIKE “%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

**那如何查询%name%？**

如下图所示，虽然给secret字段添加了索引，但在explain结果果并没有使用

![](https://gitee.com/gsjqwyl/images_repo/raw/master/2021-3-11/20210325104807.png)

那么如何解决这个问题呢，答案：**使用全文索引**

在我们查询中经常会用到select id,fnum,fdst from table_name where user_name like '%zhangsan%'; 。这样的语句，普通索引是无法满足查询需求的。庆幸的是在MySQL中，有全文索引来帮助我们。

创建全文索引的sql语法是：

```go
ALTER TABLE `table_name` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);
```

使用全文索引的sql语句是：

```csharp
select id,fnum,fdst from table_name where match(user_name) against('zhangsan' in boolean mode);
```

**注意：在需要创建全文索引之前，请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别**

**13、避免在where子句中对字段进行表达式操作**

比如

```csharp
select user_id,user_project from table_name where age*2=36;
```

中对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成

```csharp
select user_id,user_project from table_name where age=36/2;
```

**14、避免隐式类型转换**

where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型

![](https://gitee.com/gsjqwyl/images_repo/raw/master/2021-3-11/20210325104923.png)

**15、对于联合索引来说，要遵守最左前缀法则**

举列来说索引含有字段id,name,school，可以直接用id字段，也可以id,name这样的顺序，但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面

**16、必要时可以使用force index来强制查询走某个索引**

有的时候MySQL优化器采取它认为合适的索引来检索sql语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用force index来强制优化器使用我们制定的索引。

**17、注意范围查询语句**

对于联合索引来说，如果存在范围查询，比如between,>,<等条件时，会造成后面的索引字段失效。

**18、关于JOIN优化**

![](https://gitee.com/gsjqwyl/images_repo/raw/master/2021-3-11/20210325105131.png)

- LEFT JOIN A表为驱动表
- INNER JOIN MySQL会自动找出那个数据少的表作用驱动表
- RIGHT JOIN B表为驱动表

**注意：MySQL中没有full join，可以用以下方式来解决**

```csharp
select * from A left join B on B.name = A.name 
where B.name is null
 union all
select * from B;
```

**尽量使用inner join，避免left join**

参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。

**合理利用索引**

被驱动表的索引字段作为on的限制字段。

**利用小表去驱动大表**

![](https://gitee.com/gsjqwyl/images_repo/raw/master/2021-3-11/20210325105158.png)

从原理图能够直观的看出如果能够减少驱动表的话，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数。

**巧用STRAIGHT_JOIN**

inner join是由mysql选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，右边则是被驱动表。**在使用STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join。其他链接不推荐使用STRAIGHT_JOIN，否则可能造成查询结果不准确。**

![](https://gitee.com/gsjqwyl/images_repo/raw/master/2021-3-11/20210325105213.png)

这个方式有时可能减少3倍的时间。

SHIELD/JavaInterview

简介

发行版

贡献者

近期动态

SHIELD/JavaInterview .gitee-modal { width: 500px !important; }

简介

发行版

贡献者

近期动态

搜索帮助

SHIELD/JavaInterview