Java面试题系列【5】Mysql经典五十问

有道无术，术尚可求，有术无道，止于术。

资料整理来自网络

数据库

1. MySQL 中有哪些不同的表格？

MySQL 数据表七种类型 ，分别是：BDB、InnoBDB、HEAP、ISAM、MERGE、MYISAM、Gemeni。

这七种又分为两类，

• 一类是”事务安全型”(transaction-safe)，包括BDB和InnoDB；

• 其余都属于第二类，称为”非事务安全型”(non-transaction-safe)。

2. 简述在 MySQL 数据库中 MyISAM 和 InnoDB 的区别？

1、事务

InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交。

2、外键

InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败。

3、索引

InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

4、行数

InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）。

5、全文索引

Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了。

6、压缩

MyISAM表格可以被压缩后进行查询操作

7、锁

InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁。

InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

8、主键

InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有。

9、存储文件

Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI。

• Innodb：frm是表定义文件，ibd是数据文件

• Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

3. 数据库三大范式是什么？

第一范式：每个列都不可以再拆分。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

4. 说说MySQL的binlog日志？

Binlog是MySQL实例层面的日志，它跟引擎没有关系，即任何引擎都可以产生binlog日志。

根据官方介绍，Binlog只记录对表操作的语句，也就是只记录DML和DDL内容，同时也会记录该语句的耗时，不记录任何select show操作。

其中DML的记录三种格式。对于DDL的语句，只记录语句本身，不会记录语句涉及的具体数据行数。所以对某个表执行了Drop和Truncate操作后，如果需要恢复数据，则需要依据某一个备份文件才能恢复数据。

Binlog日志有三种格式，分别为Statement、MiXED、ROW，通过下面命令可以查看binlog格式：

root@localhost [(none)]>show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

事务执行过程中，binlog 首先会被写到 binlog cache 中。事务提交的时候，再将binlog cache 写到 binlog 文件中。一个事务的 binlog 是原子的，无论多大都需要保证完整性。

系统为每个客户端线程分配一个 binlog cache，其大小由 binlog_cache_size参数 控制。如果binlog cache 超过阀值，就会临时持久化到磁盘。当事务提交的时候，再将 binlog cache 中完整的事务持久化到磁盘中，并清空 binlog cache。

Binlog本身是一个二进制文件，因为二进制文件更省空间，写入速度更快。由于二进制是无法直接打开来查看，所以MySQl提供了命令mysqlbinlog进行查看。

Binlog用途

1、复制

目前业界mysql的主从复制主要是基于binlog的逻辑sql实现，他的原理如下图：

2 、备份

5. 说说Mysql 的架构？

MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层。

1、网络连接层

客户端连接器（Client Connectors）：提供与MySQL服务器建立的支持。目前几乎支持所有主流的服务器编程技术，例如常见的Java、C、Python、.NET等，它们通过各自的API技术与MySQL建立连接。

2、服务层（MySQL Server）

服务层是MySQL Server的核心，主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存六个部分。

• 连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接。
• 系统管理和控制工具（Management Services & Utilities）：例如备份恢复、安全管理、集群 管理等
• SQL接口（SQL Interface）：用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。
• 解析器（Parser）：负责将请求的SQL解析生成一个"解析树"。然后根据一些MySQL规则进一步检查解析树是否合法。
• 查询优化器（Optimizer）：当“解析树”通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互。
• 缓存（Cache&Buffer）：缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，权限缓 存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

3、存储引擎层（Pluggable Storage Engines）

存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异 。现在有很多种存储引擎，各有各的特点，最常见的是MyISAM和InnoDB。

4、 系统文件层（File System）
该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。

6. 说说Mysql 的运行原理

以Java程序为例，最为基本的操作过程是利用JDBC与MySQL建立连接，然后利用不同的SQL语句去完成我们所需要的操作，最终获取到执行结果。这时，Java服务就相当于是一个MySQL客户端，通过SQL语句与MySQL服务端进行交互。这一过程可以概括为下图：

1、MySQL客户端/服务端通信阶段

通信方式主要分为以下三种：

• 全双工：双向通信，发送同时也可以接收
• 半双工：双向通信，同时只能接收或者是发送，无法同时做操作
• 单工：只能单一方向传送

Mysql客户端与服务端的通信方式是“半双工”的通信方式。也就是说，在任何一个时刻，要么是有服务器向客户端发送数据，要么是客户端向服务端发送数据，这两个动作不能同时发生。所以我们无法也无需将一个消息切成小块进行传输。

客户端一旦开始发送消息，另一端要接收完整个消息才能响应。客户端一旦开始接收数据没法停下来发送指令。

2、查询缓存阶段

在解析一个查询SQL之前，如果查询缓存是开启的，那么MySQL会先去检查查询缓存中是否存在与之相同的查询。如果缓存中存在，即缓存命中，先查询用户权限，若权限允许则直接返回缓存中的结果集，无须解析本次的查询SQL语句的；

如果缓存中没有，即缓存未命中，则查询会进入下一阶段的处理，等到查询出结果后，会将本次的查询SQL与查询结果存放在查询缓存中，以便之后使用。

3、查询解析及优化处理阶段

语法解析器和预处理器

MySQL是通过SQL关键字将SQL语句进行解析的（lex词法分析,yacc语法分析），生成一棵对应的“解析树”。

MySQL预处理器根据MySQL的语法规则进一步检查验证解析树的合法性，例如表、字段是否存在，是否使用了错误的关键字，关键字的顺序是否正确合法，还会解析名字和别名是否会有歧义等等。

查询优化器

查询优化器：优化器的主要作用就是找到最优的执行计划。

查询优化器如何找到最优计划：explain + sql

经过语法解析器和预处理器形成的语法树被认为是合法的了，这时将有优化器将其转化成执行计划。一条查询可以有很多种执行方式，但最后返回的结果是相同的，优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本（CBO）的优化器，它将尝试预测一个查询使用某种执行计划的成本，并选择其中成本最小的一个。最初，成本的最小单位是随机读取一个4K数据页的成本，后来成本计算公式变得更加复杂，并且引入了一些“因子”来估算某些操作的代价，如当执行一次where条件比较的成本。可以通过查询当前会话的last_query_cost的值来得知MySQL计算的当前查询的成本。

4、查询执行引擎阶段

调用插件式的存储引擎的原子API的功能进行执行计划的执行，执行计划的好坏也是依赖于搜索引擎的。

5、返回客户端阶段

• 有需要做缓存的，执行缓存操作。
• 增量的返回结果：开始生成第一条结果时,mysql就开始往请求方逐步返回数据。

好处： mysql服务器无须保存过多的数据，浪费内存。用户体验好，马上就拿到了数据。

总结

MySQL使用的是“选取-投影-联接”策略进行查询。用一个例子就可以理解： select uid,name from user where gender = 1;

1. 这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤
2. 这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤
3. 将这两个查询条件联接起来生成最终查询结果

7. binlog redolog undolog区别?

日志是 mysql 数据库的重要组成部分，记录着数据库运行期间各种状态信息。mysql日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。

作为开发，我们重点需要关注的是二进制日志( binlog )和事务日志(包括redo log 和 undo log )，本文接下来会详细介绍这三种日志。

bin log

binlog 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog 是 mysql的逻辑日志，并且由 Server 层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。

redo log

redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。

比如MySQL实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。

MySQL中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到Buffer Pool中。

后续的查询都是先从Buffer Pool中找，没有命中再去硬盘加载，减少硬盘IO开销，提升性能。

更新表数据的时候，也是如此，发现Buffer Pool里存在要更新的数据，就直接在Buffer Pool里更新。

然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到redo log文件里。

undo log

undo log是InnoDB事务特性的重要组成部分。当对记录做增删改操作就会产生undo记录，undo记录会记录到单独的表空间中。

undo log是mysql中比较重要的事务日志之一，顾名思义，undo log是一种用于撤销回退的日志，在事务没提交之前，MySQL会先记录更新前的数据到 undo log日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log来进行回退。

8. 数据库备份有哪些方式？

1、mysqldump命令

在MySQL中提供了命令行导出数据库数据以及文件的一种方便的工具mysqldump,我们可以通过命令行直接实现数据库内容的导出dump:

mysqldump -u root -p --databases 数据库1 数据库2 > xxx.sql

2、binlog 日志

开启binlog 日志，搭建备份数据库。

3、其他工具

Percona XtraBackup（简称PXB）是 Percona 公司开发的一个用于 MySQL 数据库物理热备的备份工具，支持 MySQl（Oracle）、Percona Server 和 MariaDB，并且全部开源。

9. 说说InnoDB体系架构？

InnoDB主要由后台线程、内 存池和磁盘文件三个部分组成。

1、后台线程

InnoDB有不同的后台线程，用于处理不同的任务，主要有以下4类线程，分别是master thread、i/o thread、purge thread和page cleaner thread。

• Master Thread：master thread是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新，合并插入缓冲，undo页的回收等。其内部由多个循环组成。
• IO Thread： InnoDB存储引擎中大量使用了异步IO（AIO）来处理IO请求，这样可以极大提高数据库的性能，而IO Thread的工作主要是负责这些IO请求的回调处理。
• Purge Thread： 事务被提交后，其所使用的undolog可能不再需要，因此需要PurgeThread来回收已经使用分配的undo页。
• Page Cleaner Thread： Page Cleaner Thread是在InnoDB1.2.x中引入的，其作用是为了将之前版本中的脏页的刷新操作都放入到单独的线程中来完成，其目的是为了减轻Master Thread的工作及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎的性能。

2、内存池

InnoDB的内存主要由缓冲池、重做日志缓冲和额外内存池三大部分组成。

• 缓冲池： InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。由于CPU和磁盘间的速度差异，通常使用缓冲池技术来提高数据库的整体性能。在InnoDB存储引擎中，缓冲池中页的大小默认为16KB。
• 重做日志缓冲： InnoDB首先将重做日志信息放入到这个缓冲区，然后按一定的频率将其刷新到重做日志文件，重做日志缓冲不需要设置很大，因为每秒都会将重做日志缓冲刷新到日志文件，因为只要保证每秒产生的事务量在这个缓冲大小之内即可，重做日志缓冲的大小由参数innodb_log_buffer_size配置，默认为8MB，重做日志缓冲的设计是为了满足ACID特性中持久性的要求。
• 额外的内存池： 在InnoDB引擎中，对一些数据结构本身的内存进行分配时，需要从额外的内存池进行申请，当该区域的内存不够时，会从缓冲池中进行申请。这些数据结构包括每个缓冲池中的帧缓冲（frame buffer）、对应缓冲控制对象（buffer control block），这些对象记录了一些诸如LRU、锁、等待等信息，而这个对象的内存需要从额外的内存池申请。

3、磁盘文件

MySQL的表数据存放在主目录下的Data\目录里面。通常情况下，InnoDB对每一个库的存储方式为File-Per-Table，就是说数据库里面每一张表的数据、索引和表的结构信息都会分开单独存放在一个 .ibd后缀的文件、一个 .frm后缀的文件，这样一张表就会有两种类型的文件，打开Data\目录就可以直观地看到。

10. MySQL集群方案有哪些？

MySQL的集群方案有多种，按照实现途径可以分为MySQL官方和第三方。

• 官方实现方式包括：主从方式、一主多从方式、cluster集群方式等。
• 第三方实现方式包括：MMM（双主多从）方式、MHA（多主多从）方式、Galera Cluster（多主结构）方式等。

1、 主从方式-MySQL Replication

通过重放binlog实现主库数据的异步复制。即当主库执行了一条 sql 命令，那么在从库同样的执行一遍，从而达到主从复制
的效果。

2、一主多从-MySQL Fabirc

在MySQL Replication基础上，增加了故障检测与转移，自动数据分片功能。MySQL Fabirc只有一个主节点，区别是当主节点挂了以后，会从从节点中选择一个来当主节点。

3、cluster集群-MySQL Cluster

Cluster多出了一层NDB存储引擎。每一集群中可能有多个机器，每一个节点就是NDB节点，它们之间会进行同步，相当于每一段管理一段数据，没有所谓的主从。

索引

11.索引是什么？

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。而且索引是一个文件，它是要占据物理空间的。

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。比如我们在查字典的时候，前面都有检索的拼音和偏旁、笔画等，然后找到对应字典页码，这样然后就打开字典的页数就可以知道我们要搜索的某一个key的全部值的信息了。

12.索引有哪些优缺点？

索引的优点

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
• 空间方面：索引需要占物理空间。

13. MySQL有哪几种索引类型？

1、从存储结构上来划分：

这里所描述的是索引存储时保存的形式：

• BTree索引（B-Tree或B+Tree索引）
• Hash索引
• full-index全文索引
• R-Tree索引。

2、从应用层次来分

• 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引

• 唯一索引：索引列的值必须唯一，但允许有空值

• 复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

• 聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。

• 非聚簇索引： 不是聚簇索引，就是非聚簇索引

3、根据中数据的物理顺序与键值的逻辑（索引）顺序关系

• 聚集索引
• 非聚集索引

14.说一说索引的底层实现？

1、Hash索引

Hash也称散列、哈希，对应的英文都是Hash。基本原理就是把任意长度的输入，通过Hash算法变成固定长度的输出。这个映射的规则就是对应的Hash算法，而原始数据映射后的二进制串就是哈希值。活动开发中经常使用的MD5和SHA都是历史悠久的Hash算法。

“散列表（Hash table，也叫哈希表）“，是根据键（Key）而直接访问在内存存储位置的数据结构。也就是说，它通过计算一个关于键值的函数，将所需查询的数据映射到表中一个位置来访问记录，这加快了查找速度。这个映射函数称做散列函数，存放记录的数组称做散列表。

Hash索引基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。

2、B-Tree索引（MySQL使用B+Tree）

而事实上是，B-tree就是指的B树。B即Balanced，平衡的意思。

B-Tree能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。

3、B+Tree索引

是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

B+tree性质：

• n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。

• 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

• 所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。

• B+ 树中，数据对象的插入和删除仅在叶节点上进行。

• B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。

15.为什么索引结构默认使用B+Tree，而不是B-Tree，Hash，二叉树，红黑树？

Inodb存储引擎 默认是 B+Tree索引。

使用Hash表存在的问题：

• Hash表必须要有hash算法，这个算法还要足够优秀来保证你的数据能够很好的散列，如果存在大量的hash冲突或者hash碰撞，会导致一部分查询效率非常低
• 即使算法足够优秀，如果进行范围查询，需要逐一对比每一个元素值，效率很低，并且在生产环境中大部分的查询是范围查询，如果还需要排序那就更低了
• hash比较浪费内存空间，而内存是非常宝贵的资源

使用这些二叉树存在的问题：

• 每一个分支有且尽有两个子节点，当需要向其中插入更多的数据的时候，就必须要增加树的高度，而增加树的高度会导致树变深，会导致IO的次数变多，查询性能会随着数据的增多越来越低
• 范围查询难以实现

使用B-Tree存在的问题：

• 因为索引是属于K-V的格式，每个节点中既要存索引信息，又要存其对应的数据，如果数据很大，那么当树的体量很大时，节点上的数据会超过磁盘块大小范围
• B树遍历整个树的过程和二叉树本质上是一样的，B树相对二叉树虽然提高了磁盘IO性能，但并没有解决范围查询问题

B+Tree的好处：

• B+Tree和B-Tree的差距在于B+Tree的叶子节点也存储分支节点上的数据，这样分支节点只存储索引信息，把数据都放在叶子节点上，这样保证了相近的数据都能存在同一块数据块里
• 同时也使得B+树的查询次数更稳定，每次查询次数都是相同的，都需要查询到叶子节点
• 叶子节点的指针指向下一个数据对应的叶子节点，因此B+树具备了天然排序功能，在排序和范围查找的时候更方便

16.讲一讲聚簇索引与非聚簇索引？

聚簇索引

聚簇索引又叫聚集索引，聚簇索引不是一种索引类型，而是一种数据存储方式。它的数据行只存放在索引（B+树）的叶子中，内部节点不存放数据。

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

非聚簇索引

非聚簇索引就是指B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。

非聚簇索引比聚簇索引多了一次读取数据的IO操作，所以查找性能上会差。

聚簇索引与非聚簇索引的区别：

• 非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键（行号）

• 对于InnoDB来说，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。第一次索引一般是顺序IO，回表的操作属于随机IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于使用全表扫描 。

• 通常情况下， 主键索引（聚簇索引）查询只会查一次，而非主键索引（非聚簇索引）需要回表查询多次。当然，如果是覆盖索引的话，查一次即可

• 注意：MyISAM无论主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引。

17.什么是回表？非聚簇索引一定会回表查询吗？

InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：

（1）如果表定义了主键，则PK就是聚集索引；
（2）如果表没有定义主键，则第一个非空唯一索引（not NULL unique）列是聚集索引；
（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

对于InnoDB来说，如果查询条件不是聚集索引所在列（比如不是主键），查找数据时，会先查询当前列的索引树查询到主键，再根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

非聚簇索引不一定会回表查询吗，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。一个索引包含（覆盖）所有需要查询字段的值，被称之为"覆盖索引"。

举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select score from student where score > 90的查询时，在索引的叶子节点上，已经包含了score 信息，不会再次进行回表查询。

18.联合索引是什么？为什么需要注意联合索引中的顺序？

基于多个字段创建的索引我们称为联合索引，比如我们创建索引create index idx on table(A,B,C) 我们称在字段A,B,C上创建了一个联合索引。

在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

具体原因为:

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

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

19.讲一讲MySQL的最左前缀原则?

最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

如果索引了多个列，要遵循最左前缀法则，查询从索引的最左前列开始，并且不能跳过索引中的列，比如组合索引（age，money，name）你select * from user where money=3 AND name='程序员fly’跳过了age这一些，索引失效。

20.讲一讲前缀索引？

因为可能我们索引的字段非常长，这既占内存空间，也不利于维护。所以我们就想，如果只把很长字段的前面的公共部分作为一个索引，就会产生超级加倍的效果。但是，我们需要注意，order by不支持前缀索引 。

默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

21.了解索引下推吗？

索引下推(Index Condition Pushdown，简称ICP)，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。

索引条件下推默认是开启的，可以使用系统参数optimizer_switch来控制器是否开启。

不使用索引条件下推优化时的查询过程

• 获取下一行，首先读取索引信息，然后根据索引将整行数据读取出来。
• 然后通过where条件判断当前数据是否符合条件，符合返回数据。

使用索引条件下推优化时的查询过程

• 获取下一行的索引信息。
• 检查索引中存储的列信息是否符合索引条件，如果符合将整行数据读取出来，如果不符合跳过读取下一行。
• 用剩余的判断条件，判断此行数据是否符合要求，符合要求返回数据。

22.怎么查看MySQL语句有没有用到索引？

通过explain，如以下例子：

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';

id：在⼀个⼤的查询语句中每个SELECT关键字都对应⼀个唯⼀的id ，如explain select * from s1 where id = (select id from s1 where name = ‘egon1’);第一个select的id是1，第二个select的id是2。有时候会出现两个select，但是id却都是1，这是因为优化器把子查询变成了连接查询 。

select_type：select关键字对应的那个查询的类型，如SIMPLE,PRIMARY,SUBQUERY,DEPENDENT,SNION 。

table：每个查询对应的表名 。

type：type 字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是 全表扫描 还是 索引扫描 等。如const(主键索引或者唯一二级索引进行等值匹配的情况下),ref(普通的⼆级索引列与常量进⾏等值匹配),index(扫描全表索引的覆盖索引) 。

通常来说, 不同的 type 类型的性能关系如下: ALL < index < range ~ index_merge < ref < eq_ref < const < system ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的. 而 index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.

possible_key：查询中可能用到的索引(可以把用不到的删掉，降低优化器的优化时间) 。

key：此字段是 MySQL 在当前查询时所真正使用到的索引。

filtered：查询器预测满足下一次查询条件的百分比 。

rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数. 这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好。

extra：表示额外信息，如Using where,Start temporary,End temporary,Using temporary等。

23.为什么官方建议使用自增长主键作为索引？

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

24.如何创建索引？

创建索引有三种方式。

1、 在执行CREATE TABLE时创建索引

CREATE TABLE user_index2 (
    id INT auto_increment PRIMARY KEY,
    first_name VARCHAR (16),
    last_name VARCHAR (16),
    id_card VARCHAR (18),
    information text,
    KEY name (first_name, last_name),
    FULLTEXT KEY (information),
    UNIQUE KEY (id_card)
);

2、 使用ALTER TABLE命令去增加索引

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。

索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

ALTER TABLE table_name ADD INDEX index_name (column_list);

3、 使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name (column_list);

25. 建索引的原则有哪些？

1、维度高的列创建索引。

• 数据列中不重复值出现的个数，这个数量越高，维度就越高。
• 如数据表中存在8行数据a,b ,c,d,a,b,c,d这个表的维度为4。
• 要为维度高的列创建索引，如性别和年龄，那年龄的维度就高于性别。
• 性别这样的列不适合创建索引，因为维度过低。

2、对 where,on,group by,order by 中出现的列使用索引。

3、对较小的数据列使用索引，这样会使索引文件更小，同时内存中也可以装载更多的索引键。

4、为较长的字符串使用前缀索引。

5、不要过多创建索引

除了增加额外的磁盘空间外，对于DML操作的速度影响很大，因为其每增删改一次就得从新建立索引。

6、使用组合索引

7、尽量的扩展索引

尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

8、注意最左前缀匹配原则

最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

9、索引列不能参与计算

索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

26.使用索引查询一定能提高查询的性能吗？

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

索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的I* NSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。

使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:

• 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%。
• 基于非唯一性索引的检索。

27.什么情况下不走索引（索引失效）？

1、使用!= 或者 <> 导致索引失效
2、类型不一致导致的索引失效
3、函数导致的索引失效
如：

SELECT * FROM `user` WHERE DATE(create_time) = '2020-09-03';

如果使用函数在索引列，这是不走索引的。

4、运算符导致的索引失效

SELECT * FROM `user` WHERE age - 1 = 20;

如果你对列进行了（+，-，*，/，!）, 那么都将不会走索引。

5、OR引起的索引失效

SELECT * FROM `user` WHERE `name` = '张三' OR height = '175';

OR导致索引是在特定情况下的，并不是所有的OR都是使索引失效，如果OR连接的是同一个字段，那么索引不会失效，反之索引失效。

6、模糊搜索导致的索引失效

SELECT * FROM `user` WHERE `name` LIKE '%冰';

当%放在匹配字段前是不走索引的，放在后面才会走索引。

7、NOT IN、NOT EXISTS导致索引失效

事务

28. 什么是数据库事务？

事务（Transaction）一般是指要做的或所做的事情。数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。

事务由事务开始与事务结束之间执行的全部数据库操作组成。在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序流程。

事务保证一系列数据库操作要么全部成功，要么全部失败，如果某一步出现了异常，数据就会回滚，把之前的操作撤销。

比如，我们去银行转账，操作可以分为下面两个环节：

• 从第一个账户划出款项。
• 将款项存入第二个账户。

在这个过程中，两个环节是关联的。第一个账户划出款项必须保证正确的存入第二个账户，如果第二个环节没有完成，整个的过程都应该取消，否则就会发生丢失款项的问题。整个交易过程，可以看作是一个事务，成功则全部成功，失败则需要全部撤消，这样可以避免当操作的中间环节出现问题时，产生数据不一致的问题。

• 显式事务又称自定义事务，是指用显式的方式定义其开始和结束的事务，当使用start transaction和 commit语句时则表示发生显式事务。

• 隐式事务是指每一条数据操作语句都自动地成为一个事务，事务的开始是隐式的，事务的结束有明确的标记。即当用户进行数据操作时，系统自动开启一个事务，事务的结束则需手动调用 commit或 rollback语句来结束当前事务，在当前事务结束后又自动开启一个新事务。

• 自动事务是指能够自动开启事务并且能够自动结束事务。在事务执行过程中，如果没有出现异常，事务则自动提交;当执行过程产生错误时，则事务自动回滚。

29. 数据库事务的四个特性（ACID）？

事务需要满足四大特性（简称ACID），才能保证数据正确性。

原子性(Atomicity)
事务必须是原子工作单元，对于其数据修改，要么全都执行，要么全都不执行。如果只执行一半，则之前对数据库所做的操作将不会执行。

一致性(Consistency)

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

隔离性(Isolation)

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

持久性(Durability)

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，发生数据库因为故障而没有执行事务的重大错误。

30. 事务并发异常有哪些？

数据库是要被共享访问的，那么在并发操作数据库过程中很可能出现以下几种异常情况。

1.第一类更新丢失（回滚丢失）

比如下图中，T1、T2同时执行，由于没有事务隔离，虽然T1更新成功，但是T2进行了回滚，余额却没有变化，这种因为回滚导致另外一个事务操作丢失的情况叫做回滚丢失。

2.第二类更新丢失（覆盖丢失）

比如下图中，T1、T2同时执行，由于没有事务隔离，T1、T2都提交了事务，但是由于T2后执行，将T1事务的操作覆盖了，导致更新丢失问题。

3.脏读

脏读是因为一个事务读取了另一个事务修改了但是未提交的数据。

比如下图中，T1修改余额为1100，此时T2开始事务查询到的结果为1100，由于读取到了未提交的事务，当T1回滚时，T2还在脏数据上操作，会导致结果错误。

4.不可重复读

不可重复读是指一个事务对同一行数据执行了两次或更多次查询，但是却得到了不同的结果。

比如下图中，在T2中，多次查询的不一致，如果这个时候修改，就会造成数据错误。

5.幻读

幻读和不可重复读有点像，两者都表现为两次读取的结果不一致，只是针对的不是数据的值而是数据的数量。不可重复读重点在于update和delete，而幻读的重点在于insert。

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会 发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。

31. 事务隔离级别有哪些？

数据并发访问所产生的问题，在有些场景下可能是允许的，但是有些场景下可能就是致命的，数据库通常会通过锁机制来解决数据并发访问问题，

直接使用锁是非常麻烦的，为此数据库为用户提供了自动锁机制，只要用户指定会话的事务隔离级别，数据库就会通过分析SQL语句然后为事务访问的资源加上合适的锁，此外，数据库还会维护这些锁通过各种手段提高系统的性能，这些对用户来说都是透明的。ANSI/ISO SQL 92标准定义了4个等级的事务隔离级别，如下表所示：

事务隔离级别和数据访问的并发性是对立的，事务隔离级别越高并发性就越差。所以要根据具体的应用来确定合适的事务隔离级别，这个地方没有万能的原则。

可以通过以下SQL 查询和修改会话级别：

-- 查询数据库版本
SELECT VERSION();
-- 查询当前会话事务界别
SELECT @@session.tx_isolation;
-- 设置当前会话事务隔离级别，仅仅该窗口会话有效
set session tx_isolation='read-uncommitted';

1. 读未提交 Read uncommitted

读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据，这个级别，几乎没有任何隔离性，一般不会采用这个隔离级别。

2. 读已提交 Read committed

读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。另外一个事务不能读取该事务未提交的数据。

在Navicat 中，打开两个会话窗口，修改隔离级别为读已提交：

-- 设置当前会话事务隔离级别，仅仅该窗口会话有效
set session tx_isolation='Read-committed';

开启事务，将当前账户余额扣掉100，不提交事务：

START TRANSACTION;
UPDATE account_tbl 
SET money = money - 100 
WHERE
	id = "11111111";

在另外一个窗口，查询当余额：

SELECT
	* 
FROM
	account_tbl 
WHERE
	id = "11111111";

可以看到读取到的余额仍为1000，说明该隔离级别下，事务读取不到另外一个事务未提交的数据。

分析：这就是读已提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但是存在不可重复读问题，一旦事务提交后，另外的事务再次查询，查询结果会不一致。

3. 可重复度 Repeatable read

可重复读，就是在开始读取数据（事务开启）时，多次读取到的结果是一致的，这是Mysql 默认的事务隔离级别。

比如我们先开启一个事务，读取数据，此时查询结果为1000：

另外一个事务，修改当前数据，并提交事务：

查询事务中，再次查询，发现余额没有变，这就是可重复读，Repeatable Read的确可以解决“不可重复读”的问题。

4. 序列化 Serializable

Serializable是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。

虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。

32. Mysql中的各种锁？

在之前，我们了解了数据库事务和各种事务隔离级别，在并发的情况下，数据库是通过锁的机制实现隔离级别。

数据库中存在各种各样的锁，我们在说某种锁的时候，一定要先说明，按照什么分类，分为哪些锁。

锁的目的和并发编程中锁的一样，是为了解决并发情况下，对同一个资源访问限制，旨在强制实施互斥排他、并发控制策略。

33. 什么是乐观锁？Mysql 实现乐观锁的方式有哪些？

乐观锁不是数据库自带的，需要我们自己去实现。

乐观锁是相对悲观锁而言的，乐观锁机制下会假设数据一般情况下不会造成冲突，所以数据已经在进行提交更新的时候，乐观锁才会正式对数据的冲突与否进行检测，如果发现冲突，就会返回给用户错误的信息，让用户自己决定如何去做。

乐观锁采取了更加宽松的加锁机制。因为它是相对悲观锁而言的，这也是为了避免可能的数据库幻读、业务处理时间过长等等原因而引起数据处理错误的一种机制，但乐观锁一般不会去刻意使用数据库本身的锁机制，它会依赖数据本身来保证数据的正确性。

乐观锁它相信事务之间的数据竞争的概率是比较小的，因此它会尽可能的直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。

乐观锁一般有哪些实现方式?

1.版本号机制

一般是说在数据表中加上一个数据库版本号version字段，在表述数据被修改的次数当数据被修改时，它的version 值会加1。

如：当然线程A需要更新数据值时，在读取数据的同时也会读取 version 值，在提交更新时，若刚才读取到的 version 值为当前数据库中的 version 值相等时才更新，否则重试更新操作，直到更新成功。

2.CAS 算法

CAS(compare and swap) 比较并交换，有三个操作数，内存地址V ，预期值B，要替换得到的目标子A;

CAS指令执行时，比较内存地址V与预期值B是否相等，若相等则将A赋给B，(不相等则会循环比较直到相等)整个比较赋值操作是一个原子操作;

CAS缺点：

• 循环时间开销大：当内存地址V与预期值B不相等时会一直循环比较直到相等，

• 只能保证一个共享变量的原子操作，

• 如果一个变量V初次读取的时候是A值，并且在准备赋值的时候检查到它仍然是A值，那么我们就能说明它的值没有被其他线程修改过吗?很明显不是，因为在这段时间内它的值可能被改为其他值，然后又被改回A，那CAS操作就会认为它从来没被改过，这个问题j就被称为 CAS 操作的“ABA” 问题。

34. 什么是悲观锁？怎么实现？

悲观锁(Pessimistic Lock) 一个对生活态度很悲观的锁，内心极度缺乏安全感，它总是以为别人会来偷它的数据，所以它就来预防其它线程来防止数据冲突，每次修改数据之前都会把数据锁住，然后再进行读写操作，直到它释放。

利用【select … for update】实现加锁，操作完成后使用commit来释放锁。

注意事项：

• FOR UPDATE 仅适用于InnoDB存储引擎
• 必须在事务区块(BEGIN/COMMIT)中才能生效。
• 会根据where条件来锁多条记录, 根据where条件的字段是不是索引来决定锁定表还是锁定行，同时根据是聚簇索引还是二级索引和查询条件（范围查询还是精确查询）来决定锁定特定的行还是一个范围内的行

SQL 语句

35. 什么是SQL？

SQL (Structured Query Language:结构化查询语言) 是用于管理关系数据库管理系统（RDBMS）。 SQL 的范围包括数据插入、查询、更新和删除，数据库模式创建和修改，以及数据访问控制。

• SQL 指结构化查询语言，全称是 Structured Query Language。
• SQL 让您可以访问和处理数据库，包括数据插入、查询、更新和删除。
• SQL 在1986年成为 ANSI（American National Standards Institute 美国国家标准化组织）的一项标准，在 1987 年成为国际标准化组织（ISO）标准。

36. 什么是视图？

MySQL 视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的。

数据库中只存放了视图的定义，并没有存放视图中的数据，这些数据都存放在定义视图查询所引用的真实表中。使用视图查询数据时，数据库会从真实表中取出对应的数据。因此，视图中的数据是依赖于真实表中的数据的。一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。

视图并不同于数据表，它们的区别在于以下几点：

• 视图不是数据库中真实的表，而是一张虚拟表，其结构和数据是建立在对数据中真实表的查询基础上的。
• 存储在数据库中的查询操作 SQL 语句定义了视图的内容，列数据和行数据来自于视图查询所引用的实际表，引用视图时动态生成这些数据。
• 视图没有实际的物理记录，不是以数据集的形式存储在数据库中的，它所对应的数据实际上是存储在视图所引用的真实表中的。
• 视图是数据的窗口，而表是内容。表是实际数据的存放单位，而视图只是以不同的显示方式展示数据，其数据来源还是实际表。
• 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些 SQL 语句的集合。
• 从安全的角度来看，视图的数据安全性更高，使用视图的用户不接触数据表，不知道表结构。
  视图的建立和删除只影响视图本身，不影响对应的基本表。

37. 数据库方言（dialect）是什么？

抛开数据库，生活中的方言是什么？方言就是某个地方的特色语言，是一种区别于其它地方的语言，只有你们这一小块地方能听懂，出了这个地方又是另一种方言。

数据库方言也是如此，MySQL 是一种方言，Oracle 也是一种方言，MSSQL 也是一种方言，他们之间在遵循 SQL 规范的前提下，都有各自的扩展特性。

拿分页来说，MySQL 的分页是用关键字 limit， 而 Oracle 用的是 ROWNUM。

38. SQL命令分为哪些类型？

SQL命令分为以下类型：

• DDL（数据定义语言） - 用于定义数据库的结构。

• DCL（数据控制语言） - 用于为用户提供权限。

• DML（数据操作语言） - 用于管理数据。

• DQL（数据查询语言） - 所有命令都在SQL中，用于检索DQL中的数据。

• TCL（事务控制语言） - 用于管理DML所做的更改。

39. 什么是SQL中的Joins(连接)？

SQL join 用于把来自两个或多个表的行结合起来。

通常有以下几种连接方式：

• JOIN or INNER JOIN（内连接） ： 这两个是相同的，要求两边表同时有对应的数据，返回行，任何一边缺失数据就不显示。

• LEFT JOIN（左外连接）：即使右边的表中没有匹配，也从左表返回所有的行。

• RIGHT JOIN（右外连接）：即使左边的表中没有匹配，也从右表返回所有的行。

• FULL JOIN（全外连接）：只要其中一个表中存在匹配就返回行。

40. SQL内置 函数有哪些？

SQL Aggregate 函数

SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。

常用的 Aggregate 函数：

• AVG() - 返回平均值
• COUNT() - 返回行数
• FIRST() - 返回第一个记录的值
• LAST() - 返回最后一个记录的值
• MAX() - 返回最大值
• MIN() - 返回最小值
• SUM() - 返回总和

SQL Scalar 函数

SQL Scalar 函数基于输入值，返回一个单一的值。

常用的 Scalar 函数：

• UCASE() - 将某个字段转换为大写
• LCASE() - 将某个字段转换为小写
• MID() - 从某个文本字段提取字符，MySql 中使用
• SubString(字段，1，end) - 从某个文本字段提取字符
• LEN() - 返回某个文本字段的长度
• ROUND() - 对某个数值字段进行指定小数位数的四舍五入
• NOW() - 返回当前的系统日期和时间
• FORMAT() - 格式化某个字段的显示方式

41. 什么是存储过程(特定功能的SQL语句集)？

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

优点：

• 重复使用：存储过程可以重复使用，从而可以减少数据库开发人员的工作量。

• 减少网络流量：存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。

• 安全性：参数化的存储过程可以防止SQL注入式攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。

存储过程的缺点：

• 更改比较繁琐：如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等，这时候估计比较繁琐。

• 可移植性差：由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。如果应用程序的可移植性在您的环境中非常重要，则需要将业务逻辑封装在不特定于 RDBMS 的中间层中。

42. having和where的区别?

HAVING 关键字和 WHERE 关键字都可以用来过滤数据，且 HAVING 支持 WHERE 关键字中所有的操作符和语法。

但是 WHERE 和 HAVING 关键字也存在以下几点差异：

1. 一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
2. WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
3. WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。
4. WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
5. WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。

43.简单说一说drop、delete与truncate的区别？

• delete是DML语句，可以选择删除部分数据，也可以选择删除全部数据；删除的数据可以回滚；不会释放空间
• drop是DDL语句，删除表结构和所有数据，同时删除表结构所依赖的约束、触发器和索引；删除的数据无法回滚；会释放空间
• truncate是DDL语句，删除表的所有数据，不能删除表的部分数据，也不能删除表的结构；删除的数据无法回滚；会释放空间

执行速度：一般来说：drop>truncate>delete

一般使用场景：如果一张表确定不再使用，我们使用drop来操作；如果只是删表中的全部数据，一般使用truncate；如果删除的是表中的部分数据，一般使用delete

优化

44. MySQL慢查询日志如何开启以及分析？

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录MySQL中查询时间超过（大于）设置阈值（long_query_time，默认值是10S）的语句，记录到慢查询日志中。

默认情况下，MySQL没有开启慢查询日志。需要手动打开，如果不是调优需要的话，不建议开启，因为开启会带来一定的性能影响，慢查询日志支持将日志记录写入文件。

临时开启：

-- 查看慢查询日志是否开启
show variables like '%slow_query_log%';
-- 开启慢查询日志，只对当前数据库生效，并且重启数据库后失效
set global slow_query_log = 1;
-- 查看慢查询日志的阈值，默认10s 
show variables like '%long_query_time%';
-- 设置阈值 
set long_query_time = 3;
show global status like '%Slow_queries%';

永久开启：

修改配置文件my.cnf

[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3
log_output=FILE

日志分析工具mysqldumpslow

mysqldumpslow能将相同的慢SQL归类，并统计出相同的SQL执行的次数，每次执行耗时多久、总耗时，每次返回的行数、总行数，以及客户端连接信息等。

# 得到返回记录集最多的10 个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

# 得到访问次数最多的10 个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

# 得到按照时间排序的前10 条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

# 另外建议在使用这些命令时结合| 和more 使用，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

45. MySQL一张表最多能存多少数据？

MySQL本身并没有对单表最大记录数进行限制，这个数值取决于你的操作系统对单个文件的限制本身。业界流传是500万行。超过500万行就要考虑分表分库了。阿里巴巴《Java开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。

事实上，MySql数据库一张表中能存储的最大数据量和实际记录的条数无关，而与 MySQL 的配置以及机器的硬件有关。因为，MySQL 为了提高性能，会将表的索引装载到内存中。InnoDB buffer size足够的情况下，其能完成全加载进内存，查询不会有问题。

但是，当单表数据库到达某个量级的上限时，导致内存无法存储其索引，使得之后的 SQL 查询会产生磁盘 IO，从而导致性能下降。当然，这个还有具体的表结构的设计有关，最终导致的问题都是内存限制。

46. 亿级数据量存储在Mysql 中如何设计？

MySQL单机的存储能力、连接数是有限的，它自身就很容易会成为系统的瓶颈。当单表数据量在百万以里时，我们还可以通过添加从库、优化索引提升性能。一旦数据量朝着千万以上趋势增长，再怎么优化数据库，很多操作性能仍下降严重。

事实上MySQL单表可以存储10亿级数据，只是这时候性能比较差，业界公认MySQL单表容量在1KW量级是最佳状态，因为这时它的BTREE索引树高在3~5之间。

目前比较普遍的方案有3个：

• 分区；
• 分库分表；
• NoSQL/NewSQL；

说明：只分库，或者只分表，或者分库分表融合方案都统一认为是分库分表方案，因为分库，或者分表只是一种特殊的分库分表而已。NoSQL比较具有代表性的是MongoDB，es。NewSQL比较具有代表性的是TiDB。

1、分库分表中间件选择

业界已经有了很多比较成熟的分库分表中间件：

• sharding-jdbc（当当）
• TSharding（蘑菇街）
• Atlas（奇虎360）
• Cobar（阿里巴巴）
• MyCAT（基于Cobar）
• Oceanus（58同城）
• Vitess（谷歌）

全部可以归结为两大类型：

• CLIENT模式；
• PROXY模式；

CLIENT模式代表有阿里的TDDL，开源社区的sharding-jdbc（sharding-jdbc的3.x版本即sharding-sphere已经支持了proxy模式）。

Client方式是指分库分表的逻辑都在应用本地进行控制，应用本地会直连多个数据库进行操作，然后本地进行数据的聚合汇总等操作逻辑。

PROXY模式代表有阿里的cobar，民间组织的MyCAT。

Proxy方式是指挥有一个独立的应用，这个应用实现了Mysql的协议，可以对外提供服务。业务方的应用不需要直接连接数据库，而是连接这个Proxy的应用，把这个Proxy就当做一个数据库使用。Proxy会将Sql分发到具体的数据库进行执行，并返回结果。

但是，无论是CLIENT模式，还是PROXY模式。几个核心的步骤是一样的：SQL解析，重写，路由，执行，结果归并。

2、表设计

• 主键选择：前面我们已经对比分析过业务主键和自增主键的优缺点，结论是业务主键更符合业务的查询需求，而互联网业务大多都符合读多写少的特性，所以所有线上业务都使用业务主键；
• 索引个数：由于过多的索引会造成索引文件过大，所以要求索引数不多于5个；
• 列类型选择：通常越小、越简单越好，例如：BOOL字段统一使用TINYINT，枚举字段统一使用TINYINT，交易金额统一使用LONG。因为BOOL和枚举类型使用TINYINT可以很方便的扩展，针对金额数据，虽然InnoDB提供了支持精确计算的DECIMAL类型，但DECIMAL是存储类型不是数据类型，不支持CPU原声计算，效率会低一些，所以我们简单处理将小数转换为整数用LONG存储。
• 分表策略：首先要明确数据库出现性能问题一般在数据量到达一定程度后！所以要求我们提前做好预估，不要等需要拆分时再拆，一般把表的数据量控制在千万级别；常用分表策略有两种：按key取模，读写均匀；按时间分，冷热数据明确；

总之，对于海量数据，且有一定的并发量的分库分表，绝不是引入某一个分库分表中间件就能解决问题，而是一项系统的工程。需要分析整个表相关的业务，让合适的中间件做它最擅长的事情。例如有sharding column的查询走分库分表，一些模糊查询，或者多个不固定条件筛选则走es，海量存储则交给HBase。

做了这么多事情后，后面还会有很多的工作要做，比如数据同步的一致性问题，还有运行一段时间后，某些表的数据量慢慢达到单表瓶颈，这时候还需要做冷数据迁移。总之，分库分表是一项非常复杂的系统工程。任何海量数据的处理，都不是简单的事情，做好战斗的准备吧！

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

简而言之，SQL 注入就是在用户输入的字符串中加入 SQL 语句，如果在设计不良的程序中忽略了检查，那么这些注入进去的 SQL 语句就会被数据库服务器误认为是正常的 SQL 语句而运行，攻击者就可以执行计划外的命令或访问未被授权的数据。

SQL 注入的原理主要有以下 4 点：

1）恶意拼接查询

我们知道，SQL 语句可以查询、插入、更新和删除数据，且使用分号来分隔不同的命令。例如：

SELECT * FROM users WHERE user_id = $user_id

其中，user_id 是传入的参数，如果传入的参数值为“1234; DELETE FROM users”，那么最终的查询语句会变为：

SELECT * FROM users WHERE user_id = 1234; DELETE FROM users

如果以上语句执行，则会删除 users 表中的所有数据。

2）利用注释执行非法命令。

SQL 语句中可以插入注释。例如：

SELECT COUNT(*) AS 'num' FROM game_score WHERE game_id=24411 AND version=$version

如果 version 包含了恶意的字符串’-1’ OR 3 AND SLEEP(500)–，那么最终查询语句会变为：

SELECT COUNT(*) AS 'num' FROM game_score WHERE game_id=24411 AND version='-1' OR 3 AND SLEEP(500)--

以上恶意查询只是想耗尽系统资源，SLEEP(500) 将导致 SQL 语句一直运行。如果其中添加了修改、删除数据的恶意指令，那么将会造成更大的破坏。

3）传入非法参数

SQL 语句中传入的字符串参数是用单引号引起来的，如果字符串本身包含单引号而没有被处理，那么可能会篡改原本 SQL 语句的作用。 例如：

SELECT * FROM user_name WHERE user_name = $user_name

如果 user_name 传入参数值为 G’chen，那么最终的查询语句会变为：

SELECT * FROM user_name WHERE user_name ='G'chen'

一般情况下，以上语句会执行出错，这样的语句风险比较小。虽然没有语法错误，但可能会恶意产生 SQL 语句，并且以一种你不期望的方式运行。

4）添加额外条件

在 SQL 语句中添加一些额外条件，以此来改变执行行为。条件一般为真值表达式。例如：

UPDATE users SET userpass='$userpass' WHERE user_id=$user_id;

如果 user_id 被传入恶意的字符串“1234 OR TRUE”，那么最终的 SQL 语句会变为：

UPDATE users SET userpass= '123456' WHERE user_id=1234 OR TRUE;

这将更改所有用户的密码。

避免SQL注入

对于 SQL 注入，我们可以采取适当的预防措施来保护数据安全。下面是避免 SQL 注入的一些方法。

1. 过滤输入内容，校验字符串

过滤输入内容就是在数据提交到数据库之前，就把用户输入中的不合法字符剔除掉。可以使用编程语言提供的处理函数或自己的处理函数来进行过滤，还可以使用正则表达式匹配安全的字符串。

如果值属于特定的类型或有具体的格式，那么在拼接 SQL 语句之前就要进行校验，验证其有效性。比如对于某个传入的值，如果可以确定是整型，则要判断它是否为整型，在浏览器端（客户端）和服务器端都需要进行验证。

2. 参数化查询

参数化查询目前被视作是预防 SQL 注入攻击最有效的方法。参数化查询是指在设计与数据库连接并访问数据时，在需要填入数值或数据的地方，使用参数（Parameter）来给值。

MySQL 的参数格式是以“?”字符加上参数名称而成，如下所示：

UPDATE myTable SET c1 = ?c1, c2 = ?c2, c3 = ?c3 WHERE c4 = ?c4

在使用参数化查询的情况下，数据库服务器不会将参数的内容视为 SQL 语句的一部分来进行处理，而是在数据库完成 SQL 语句的编译之后，才套用参数运行。因此就算参数中含有破坏性的指令，也不会被数据库所运行。

3. 安全测试、安全审计

除了开发规范，还需要合适的工具来确保代码的安全。我们应该在开发过程中应对代码进行审查，在测试环节使用工具进行扫描，上线后定期扫描安全漏洞。通过多个环节的检查，一般是可以避免 SQL 注入的。

48. mysql调优？

MySQL常见的优化手段分为下面几个方面：

1. 硬件层优化
2. 设计优化
3. sql层面的优化（包含索引优化）
4. 系统配置

1、 硬件层优化

MySQL 对硬件的要求主要体现在三个方面：磁盘、网络和内存

磁盘

磁盘应该尽量使用有高性能读写能力的磁盘，比如固态硬盘，这样就可以减少 I/O 运行的时间，从而提高了 MySQL 整体的运行效率

磁盘也可以尽量使用多个小磁盘而不是一个大磁盘，因为磁盘的转速是固定的，有多个小磁盘就相当于拥有多个并行运行的磁盘一样

网络

保证网络带宽的通畅（低延迟）以及够大的网络带宽是 MySQL 正常运行的基本条件，如果条件允许的话也可以设置多个网卡，以提高网络高峰期 MySQL 服务器的运行效率

内存

MySQL 服务器的内存越大，那么存储和缓存的信息也就越多，而内存的性能是非常高的，从而提高了整个 MySQL 的运行效率

2、设计优化

尽量避免使用NULL

NULL在MySQL中不好处理，存储需要额外空间，运算也需要特殊的运算符，含有NULL的列很难进行查询优化

应当指定列为not null，用0、空串或其他特殊的值代替空值，比如定义为int not null default 0

最小数据长度

越小的数据类型长度通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快

使用最简单数据类型

简单的数据类型操作代价更低，比如：能使用 int 类型就不要使用 varchar 类型，因为 int 类型比 varchar 类型的查询效率更高

尽量少定义 text 类型

text 类型的查询效率很低，如果必须要使用 text 定义字段，可以把此字段分离成子表，需要查询此字段时使用联合查询，这样可以提高主表的查询效率

适当分表、分库策略

分表是指当一张表中的字段更多时，可以尝试将一张大表拆分为多张子表，把使用比较高频的主信息放入主表中，其他的放入子表，这样我们大部分查询只需要查询字段更少的主表就可以完成了，从而有效的提高了查询的效率

分库是指将一个数据库分为多个数据库。比如我们把一个数据库拆分为了多个数据库，一个主数据库用于写入和修改数据，其他的用于同步主数据并提供给客户端查询，这样就把一个库的读和写的压力，分摊给了多个库，从而提高了数据库整体的运行效率

常见类型选择
整数类型宽度设置

MySQL可以为整数类型指定宽度，例如int(11)，实际上并没有意义，它并不会限制值的范围，对于存储和计算来说，int(1)和int(20)是相同的

VARCHAR和CHAR类型

char类型是定长的，而varchar存储可变字符串，比定长更省空间，但是varchar需要额外1或2个字节记录字符串长度，更新时也容易产生碎片

需要结合使用场景来选择：如果字符串列最大长度比平均长度大很多，或者列的更新很少，选择varchar较合适；如果要存很短的字符串，或者字符串值长度都相同，比如MD5值，或者列数据经常变更，选择使用char类型

DATETIME和TIMESTAMP类型

datetime的范围更大，能表示从1001到9999年，timestamp只能表示从1970年到2038年。datetime与时区无关，timestamp显示值依赖于时区。在大多数场景下，这两种类型都能良好地工作，但是建议使用timestamp，因为datetime占用8个字节，timestamp只占用了4个字节，timestamp空间效率更高

BLOB和TEXT类型

blob和text都是为存储很大数据而设计的字符串数据类型，分别采用二进制和字符方式存储

在实际使用中，要慎用这两种类型，它们的查询效率很低，如果字段必须要使用这两种类型，可以把此字段分离成子表，需要查询此字段时使用联合查询，这样可以提高主表的查询效率

范式化

当数据较好范式化时，修改的数据更少，而且范式化的表通常要小，可以有更多的数据缓存在内存中，所以执行操作会更快

缺点则是查询时需要更多的关联

第一范式：字段不可分割，数据库默认支持

第二范式：消除对主键的部分依赖，可以在表中加上一个与业务逻辑无关的字段作为主键，比如用自增id

第三范式：消除对主键的传递依赖，可以将表拆分，减少数据冗余

3、 sql层面的优化（包含索引优化）

此优化方案指的是通过优化 SQL 语句以及索引来提高 MySQL 数据库的运行效率，具体内容如下：

分页优化
例如：

select * from table where type = 2 and level = 9 order by id asc limit 190289,10;

优化方案：

• 延迟关联：先通过where条件提取出主键，在将该表与原数据表关联，通过主键id提取数据行，而不是通过原来的二级索引提取数据行
  例如：

select a.* from table a, (select id from table where type = 2 and level = 9 order by id asc limit 190289,10 ) b where a.id = b.id

• 书签方式：书签方式说白了就是找到limit第一个参数对应的主键值，再根据这个主键值再去过滤并limit
  例如：

select * from table where id > (select * from table where type = 2 and level = 9 order by id asc limit 190289, 1) limit 10;

索引优化
正确使用索引

假如我们没有添加索引，那么在查询时就会触发全表扫描，因此查询的数据就会很多，并且查询效率会很低，为了提高查询的性能，我们就需要给最常使用的查询字段上，添加相应的索引，这样才能提高查询的性能

建立覆盖索引

InnoDB使用辅助索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引。

例如对于如下查询：

select name from test where city=‘上海’
我们将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取

alter table test add index idx_city_name (city, name);

在 MySQL 5.0 之前的版本尽量避免使用or查询

在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，在 MySQL 5.0 之后的版本中引入了索引合并

索引合并简单来说就是把多条件查询，比如or或and查询对多个索引分别进行条件扫描，然后将它们各自的结果进行合并，因此就不会导致索引失效的问题了

如果从Explain执行计划的type列的值是index_merge可以看出MySQL使用索引合并的方式来执行对表的查询

避免在 where 查询条件中使用 != 或者 <> 操作符

SQL中，不等于操作符会导致查询引擎放弃索引索引，引起全表扫描，即使比较的字段上有索引

解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描

例如，把column<>’aaa’，改成column>’aaa’ or column<’aaa’，就可以使用索引了

适当使用前缀索引

MySQL 是支持前缀索引的，也就是说我们可以定义字符串的一部分来作为索引

我们知道索引越长占用的磁盘空间就越大，那么在相同数据页中能放下的索引值也就越少，这就意味着搜索索引需要的查询时间也就越长，进而查询的效率就会降低，所以我们可以适当的选择使用前缀索引，以减少空间的占用和提高查询效率

比如，邮箱的后缀都是固定的“@xxx.com”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引

alter table test add index index2(email(6));
使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本

需要注意的是，前缀索引也存在缺点，MySQL无法利用前缀索引做order by和group by 操作，也无法作为覆盖索引

查询具体的字段而非全部字段

要尽量避免使用select *，而是查询需要的字段，这样可以提升速度，以及减少网络传输的带宽压力

优化子查询

尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大

小表驱动大表

我们要尽量使用小表驱动大表的方式进行查询，也就是如果 B 表的数据小于 A 表的数据，那执行的顺序就是先查 B 表再查 A 表，具体查询语句如下：

select name from A where id in (select id from B);

不要在列上进行运算操作

不要在列字段上进行算术运算或其他表达式运算，否则可能会导致查询引擎无法正确使用索引，从而影响了查询的效率

select * from test where id + 1 = 50;
select * from test where month(updateTime) = 7;

一个很容易踩的坑：隐式类型转换：

select * from test where skuId=123456

skuId这个字段上有索引，但是explain的结果却显示这条语句会全表扫描

原因在于skuId的字符类型是varchar(32)，比较值却是整型，故需要做类型转换

适当增加冗余字段

增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略

正确使用联合索引

使用了 B+ 树的 MySQL 数据库引擎，比如 InnoDB 引擎，在每次查询复合字段时是从左往右匹配数据的，因此在创建联合索引的时候需要注意索引创建的顺序

例如，我们创建了一个联合索引是idx(name,age,sex)，那么当我们使用，姓名+年龄+性别、姓名+年龄、姓名等这种最左前缀查询条件时，就会触发联合索引进行查询；然而如果非最左匹配的查询条件，例如，性别+姓名这种查询条件就不会触发联合索引

Join优化

MySQL的join语句连接表使用的是nested-loop join算法，这个过程类似于嵌套循环，简单来说，就是遍历驱动表（外层表），每读出一行数据，取出连接字段到被驱动表（内层表）里查找满足条件的行，组成结果行

要提升join语句的性能，就要尽可能减少嵌套循环的循环次数

一个显著优化方式是对被驱动表的join字段建立索引，利用索引能快速匹配到对应的行，避免与内层表每一行记录做比较，极大地减少总循环次数。另一个优化点，就是连接时用小结果集驱动大结果集，在索引优化的基础上能进一步减少嵌套循环的次数

如果难以判断哪个是大表，哪个是小表，可以用inner join连接，MySQL会自动选择小表去驱动大表

避免使用JOIN关联太多的表

对于 MySQL 来说，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置

在 MySQL 中，对于同一个 SQL 多关联（join）一个表，就会多分配一个关联缓存，如果在一个 SQL 中关联的表越多，所占用的内存也就越大

如果程序中大量的使用了多表关联的操作，同时join_buffer_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性

排序优化
利用索引扫描做排序

MySQL有两种方式生成有序结果：其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的

但是如果索引不能覆盖查询所需列，就不得不每扫描一条记录回表查询一次，这个读操作是随机IO，通常会比顺序全表扫描还慢

因此，在设计索引时，尽可能使用同一个索引既满足排序又用于查找行

例如：

--建立索引（date,staff_id,customer_id）
select staff_id, customer_id from test where date = '2010-01-01' order by staff_id,customer_id;

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序

UNION优化

MySQL处理union的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在union查询中都会失效，因为它无法利用索引

最好手工将where、limit等子句下推到union的各个子查询中，以便优化器可以充分利用这些条件进行优化

此外，除非确实需要服务器去重，一定要使用union all，如果不加all关键字，MySQL会给临时表加上distinct选项，这会导致对整个临时表做唯一性检查，代价很高

慢查询日志

出现慢查询通常的排查手段是先使用慢查询日志功能，查询出比较慢的 SQL 语句，然后再通过 Explain 来查询 SQL 语句的执行计划，最后分析并定位出问题的根源，再进行处理

慢查询日志指的是在 MySQL 中可以通过配置来开启慢查询日志的记录功能，超过long_query_time值的 SQL 将会被记录在日志中

我们可以通过设置“slow_query_log=1”来开启慢查询

需要注意的是，在开启慢日志功能之后，会对 MySQL 的性能造成一定的影响，因此在生产环境中要慎用此功能

4、系统配置

好的硬件也需要合理的配置(如连接数配置、占用内存等)，即使你的硬件服务器性能再好，但是数据库配置不合适没有充分利用完硬件性能还是没有完全发挥出硬件的优势。

其他

49. 什么是MVCC多版本并发控制？能否解决了幻读问题呢？

多版本控制: 指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。

在内部实现中，InnoDB通过undo log保存每条数据的多个版本，并且能够找回数据历史版本提供给用户读，每个事务读到的数据版本可能是不一样的。在同一个事务中，用户只能看到该事务创建快照之前已经提交的修改和该事务本身做的修改。

MVCC只在已提交读（Read Committed）和可重复读（Repeatable Read）两个隔离级别下工作，其他两个隔离级别和MVCC是不兼容的。因为未提交读，总数读取最新的数据行，而不是读取符合当前事务版本的数据行。而串行化（Serializable）则会对读的所有数据多加锁。

MVCC的实现原理主要是依赖每一行记录中两个隐藏字段，undo log，ReadView。

MVCC能解决不可重复读问题,但是不能解决幻读问题，不论是快照读和当前读都不能解决。RR级别解决幻读靠的是锁机制,而不是MVCC机制。

50. 说说你对Mysql死锁的理解？

死锁：是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用，它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。

死锁产生的4个必要条件

1、互斥条件

指进程对所分配的资源进行排他性使用，即一段时间内某资源只有一个进程占用，其他的进程请求资源只能等待，直至被占有资源的进程得到释放。

2、请求和保留条件

指进程至少保持占用一个资源，但又提出新的资源请求，而该资源正被其他进程占用，此时请求进程阻塞，但对以获得的其他资源保持不放。

3、不剥夺条件

指进程已获得的资源，在未使用完之前，不能剥夺，只能使用完时由自己释放。

4、环路等待条件

值发生死锁时，必然存在一个进程占用资源的环形链，即进程集合（P0，P1，P2, … Pn），P0等待P1资源释放，P1等待P2资源释放，P3等待 … Pn等待P0资源释放。

如何预防死锁

阻止死锁的途径就是避免满足死锁条件的情况发生，为此我们在开发的过程中需要遵循如下原则：

1.尽量避免并发的执行涉及到修改数据的语句。

2.要求每一个事务一次就将所有要使用到的数据全部加锁，否则就不允许执行。

3.预先规定一个加锁顺序，所有的事务都必须按照这个顺序对数据执行封锁。如不同的过程在事务内部对对象的更新执行顺序应尽量保证一致。

4.每个事务的执行时间不可太长，对程序段的事务可考虑将其分割为几个事务。在事务中不要求输入，应该在事务之前得到输入，然后快速执行事务。

5.使用尽可能低的隔离级别。

6.数据存储空间离散法。该方法是指采用各种手段，将逻辑上在一个表中的数据分散的若干离散的空间上去，以便改善对表的访问性能。主要通过将大表按行或者列分解为若干小表，或者按照不同的用户群两种方法实现。

7.编写应用程序，让进程持有锁的时间尽可能短，这样其它进程就不必花太长的时间等待锁被释放。

8.保持事务简短并在一个批处理中