Java 攻城狮面试题 02_MySQL 关系型数据库

基础概念

关系型数据库与非关系型数据库

• 关系型数据库， Oracle、MySQL、SQLServer、Access

• 非关系型数据库， MongoDB、Redis、Solr、ElasticSearch、Hive、HBase

• 早期发展的数据库建立在数据的紧密关系基础之上（如：父子关系、师生关系），我们称其为关系型数据库，也称为传统数据库；

• 现今数据库建立在数据的松散关系基础之上（如：中国人和美国人、中国人和印度人、视频、音频），我们称其为非关系型数据库nosql（not only sql）

关系型数据库以关系模型（可以简单理解为二维表格模型）及其之间的关系组成的一个数据集合。其以行和列的形式存储数据，以便于用户理解。

Mysql 的技术特点是什么？

Mysql 数据库软件是一个客户端或服务器系统，其中包括：支持各种客户端程序和库的多线程 SQL 服务器、不同的后端、广泛的应用程序编程接口和管理工具。

Mysql 服务器默认端口是什么？

Mysql 服务器的默认端口是 3306

与 Oracle 相比，Mysql 有什么优势？

• Mysql 是开源软件，随时可用，无需付费。
• Mysql 是便携式的。
• 带有命令提示符的 GUI。
• 使用 Mysql 查询浏览器支持管理。

Mysql 驱动程序是什么？

以下是 Mysql 中可用的驱动程序：

• PHP 驱动程序
• JDBC 驱动程序
• ODBC 驱动程序
• CWRAPPER
• PYTHON 驱动程序
• PERL 驱动程序
• RUBY 驱动程序
• CAP11PHP 驱动程序
• Ado.net5.mx

MySQL 有关权限的表都有哪几个？

MySQL 服务器通过权限表来控制用户对数据库的访问， 权限表存放在 MySQL 数据库里，由 MySQL_install_db 脚本初始化。这些权限表分别 user，db，table_priv， columns_priv 和 host 。

MySql 引擎

MySQL由哪些部分组成，分别用来做什么？

1. Server

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

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

MySql的存储引擎有哪些?

MyISAM、InnoDB、BDB、MEMORY、 MERGE、 EXAMPLE、NDB Cluster、ARCHIVE、CSV.BLACKHOLE、FEDERATED。

Tips:InnoDB和BDB提供事务安全表，其他存储引擎都是非事务安全表。

说一下 MySQL 常用的引擎？

1、lnnoDB InnoDB 存储引擎

• InnoDB 引擎：是一个由 Oracle 公司开发的 Innobase Oy 事务安全存储引擎。mysql 5.1 后默认的数据库引擎，提供了对数据库 acid 事务的支持，具有提交、回滚和崩溃恢复能力的事务安全，并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。MySQL 运行的时候，InnoDB 会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎是不支持全文搜索，同时启动也比较的慢，它是不会保存表的行数的，所以当进行 select count(*) from table 指令的时候，需要进行扫描全表。由于锁的粒度小，写操作是不会锁定全表的，所以在并发度较高的场景下使用会提升效率的。对比Myisam的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

• MyIASM 引擎：Myisam是Mysql的默认存储引擎（mysql 5.1 之前），不提供事务的支持，也不支持行级锁和外键。因此当执行插入和更新语句时，即执行写操作的时候需要锁定这个表，所以会导致效率会降低。不过和 InnoDB 不同的是，MyIASM 引擎是保存了表的行数，于是当进行 select count(*) from table 语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将MyIASM 作为数据库引擎的首选。

MyISAM和InnoDB的区别有哪些?

• InnoDB支持事务，MyISAM不支持。
• InnoDB支持行级锁, MyISAM支持表级锁。
• InnoDB支持多版本并发控制(MVVC)，MyISAM不支持。
• InnoDB支持外键，MyISAM不支持。
• MyISAM支持全文索引， InnoDB部分版本不支持(但可以使用Sphinx插件)。

MyISAM 表格将在哪里存储，并且还提供其存储格式？

每个 MyISAM 表格以三种格式存储在磁盘上，文件名都和表名相同，扩展名分别是：

• .frm (存储表定义)、
• .MYD (MYData,存储数据)、
• .MYI (MYIndex，存储索引)。

数据文件和索引文件可以放置在不同的目录，平均分布io，获得更快的速度。

myisamchk 是用来做什么的？

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

MyISAM Static 和 MyISAM Dynamic 有什么区别？

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

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

一张表，里面有 ID 自增主键，当 insert 了 17 条记录之后，删除了第 15,16,17 条记录，再把 Mysql 重启，再 insert 一条记录，这条记录的 ID 是 18还是15 ？

1)如果表的类型是MyISAM，那么是18。
因为MyISAM表会把自增主键的最大ID记录到数据文件里，重启MySQL自增主键的最大ID也不会丢失。

(2)如果表的类型是InnoDB，那么是15。
InnoDB表只是把自增主键的最大ID记录到内存中，所以重启数据库或者是对表进行OPTIMIZE操作，都会导致最大ID丢失。

MySql 表

Mysql 中有哪些不同的表格？

共有 5 种类型的表格：

• MyISAM
• Heap
• Merge
• INNODB
• ISAM

MyISAM 是 Mysql 的默认存储引擎。

Heap 表是什么？

HEAP 表存在于内存中，用于临时高速存储。

• BLOB 或 TEXT 字段是不允许的
• 只能使用比较运算符=，<，>，=>，= <
• HEAP 表不支持 AUTO_INCREMENT
• 索引不可为 NULL

MySQL 有关权限的表都有哪几个？

MySQL 服务器通过权限表来控制用户对数据库的访问， 权限表存放在 MySQL 数据库里，由MySQL_install_db 脚本初始化。这些权限表分别

• user
• db
• table_priv
• columns_priv
• host

基础操作

数据库的三范式是什么?

• 第一范式: 属性不可再分。

• 第二范式: 在一范式的基础上， 要求数据库表中的每个实例或行必须可以被唯一地区分。通常需要为表加上一个列，以存储各个实例的唯一标识，这个惟一属性列被称为主关键字或主键。

• 第三范式: 在二范式的基础上， 要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
  所以第三范式具有如下特征：
  （1）每一列只有一个值。
  （2）每一行都能区分。
  （3）每一个表都不包含其他表已经包含的非主关键字信息。

Select 语法规范

只能按顺序写

SELECT 查询列表
FROM 表名或视图列表
【WHERE 条件表达式】
【GROUP BY 字段名 【HAVING 条件表达式】】
【ORDER BY 字段 【ASC|DESC】】
【LIMIT m,n】;

SQL 语言包括哪几部分？每部分都有哪些操作关键字？

SQL 语言包括数据定义(DDL)、数据操纵(DML)、数据控制(DCL)和数据查询（ DQL） 四个部分。

• 数据定义(DDL)： Create Table,Alter Table,Drop Table, Craete/Drop Index 等
• 数据操纵(DML)： select,insert,update,delete,
• 数据控制(DCL)： grant,revoke
• 数据查询(DQL)： select

什么样的对象可以使用 CREATE 语句创建？

以下对象是使用 CREATE 语句创建的：

• DATABASE（数据库）
• EVENT(事件)
• FUNCTION（函数）
• INDEX（索引）
• PROCEDURE（程序）
• TABLE（表）
• TRIGGER（触发器）
• USER（用户）
• VIEW（视图）

Mysql 表中允许有多少个 TRIGGERS？

在 Mysql 表中允许有六个触发器，如下：

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELET

完整性约束包括哪些？

数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)。

分为以下四类：
1、实体完整性： 规定表的每一行在表中是惟一的实体。

2、域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括 取值范围、精度等规定。

3、参照完整性： 是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据
库中扩散。

4、用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需 要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件， 它反映某一具体应用必须满足的语义要求。

与表有关的约束：包括列约束(NOT NULL（ 非空约束）)和表约束(PRIMARY KEY、foreign key、check、UNIQUE)

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

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

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

• 外连接 其结果集中不仅包含符合连接条件的行，而且还会包括左表、右表或两个表中的所有数据行， 这三种情况依次称之为左外连接， 右外连接， 和全外连接。
  左外连接， 也称左连接，左表为主表， 左表中的所有记录都会出现在结果集中， 对于那些在右表中并没有匹配的记录， 仍然要显示， 右边对应的那些字段值以NULL 来填充。
  右外连接，也称右连接，右表为主表，右表中的所有记录都会出现在结果集中。左连接和右连接可以互换。

MySQL 目前还不支持全外连接，但是可以用 left join union right join 代替。

基础数据

列对比运算符是什么？

在 SELECT 语句的列比较中使用=，<>，<=，<，> =，>，<<，>>，<=>，AND，OR 或 LIKE 运算符

列的字符串类型可以是什么？

字符串类型是：

• SET
• BLOB （二进制对象）
• ENUM （枚举类）
• CHAR
• TEXT
• VARCHA

在 Mysql 中 ENUM 的用法是什么？

ENUM 是一个字符串对象，用于指定一组预定义的值，并可在创建表时使用。

Create table size(name ENUM('Smail,'Medium','Large')

BLOB 和 TEXT 有什么区别？

BLOB 是一个二进制对象，可以容纳可变数量的数据。有四种类型的 BLOB -  TINYBLOB

• BLOB
• MEDIUMBLOB
• LONGBLOB

它们只能在所能容纳价值的最大长度上有所不同。

TEXT 是一个不区分大小写的 BLOB。四种 TEXT 类型

• TINYTEXT
• TEXT
• MEDIUMTEXT
• LONGTEXT

它们对应于四种 BLOB 类型，并具有相同的最大长度和存储要求。
BLOB 和 TEXT 类型之间的唯一区别在于对 BLOB 值进行排序和比较时区分大小写，对TEXT 值不区分大小写。

什么是非标准字符串类型？

• TINYTEXT
• TEXT
• MEDIUMTEXT
• LONGTEXT

char 和 varchar 的区别是什么？

char(n) ：固定长度类型，比如订阅 char(10)，当你输入"abc"三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。

chat 优点：效率高；缺点：占用空间；适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。

• varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。

所以，从空间上考虑 varcahr 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。

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

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

例如：
salary DECIMAL(9,2)

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

什么是通用 SQL 函数？

• CONCAT(A, B) - 连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段。
• FORMAT(X, D)- 格式化数字 X 到 D 有效数字。
• CURRDATE(), CURRTIME()- 返回当前日期或时间。
• NOW（） - 将当前日期和时间作为一个值返回。
• MONTH（），DAY（），YEAR（），WEEK（），WEEKDAY（） - 从日期值中提取给定数据。
• HOUR（），MINUTE（），SECOND（） - 从时间值中提取给定数据。
• DATEDIFF（A，B） - 确定两个日期之间的差异，通常用于计算年龄
• SUBTIMES（A，B） - 确定两次之间的差异。
• FROMDAYS（INT） - 将整数天数转换为日期值。

如何输入字符为十六进制数字？

如果想输入字符为十六进制数字，可以输入带有单引号的十六进制数字和前缀（X），或者只用（Ox）前缀输入十六进制数字。

如果表达式上下文是字符串，则十六进制数字串将自动转换为字符串。

float 和 double 的区别是什么？

• float 最多可以存储 8 位的十进制数，并在内存中占 4 字节。
• double 最可可以存储 16 位的十进制数，并在内存中占 8 字节。

区分 char_length 和 length？

char_length 是字符数，而 length是字节数。Latin 字符的这两个数据是相同的，但是对于 Unicode 和其他编码，它们是不同的。

now（）和 current_date（）有什么区别？

NOW（）命令用于显示当前年份、月份、日期、小时、分钟和秒。
CURRENT_DATE（）仅显示当前年份，月份和日期

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

每当行被更改时， 时间戳字段将获取当前时间戳。

存储时期的类型？

• 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 类型

LIKE 声明中的％和_是什么意思？

％对应于 0 个或更多字符，_只是 LIKE 语句中的一个字符。

列设置为 AUTO INCREMENT 时， 如果在表中达到最大值， 会发生什么情况？

它会停止递增， 任何进一步的插入都将产生错误， 因为密钥已被使用。

怎样才能找出最后一次插入时分配了哪个自动增量？

LAST_INSERT_ID 将返回由 Auto_increment 分配的最后一个值， 并且不需要指定表名称。

如何在 Unix 和 Mysql 时间戳之间进行转换？

• UNIX_TIMESTAMP 是从 Mysql 时间戳转换为 Unix 时间戳的命令
• FROM_UNIXTIME 是从 Unix 时间戳转换为 Mysql 时间戳的命令

列设置为 AUTO INCREMENT 时，如果在表中达到最大值，会发生什么情况？

它会停止递增，任何进一步的插入都将产生错误，因为密钥已被使用。

索引

MySQL 索引是怎么实现的？

索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据。

具体来说 MySQL 中的索引，不同的数据引擎实现有所不同，但目前主流的数据库引擎的索引都是 B+ 树实现的，B+ 树的搜索效率，可以到达二分法的性能，找到数据区域之后就找到了完整的数据结构了，所有索引的性能也是更好的。

B+树主要是在所有的叶子结点中增加了指向下一个叶子节点的指针， 因此 InnoDB 建议为大部分表使用默认自增的主键作为主索引。

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

1、以“%” 开头的 LIKE 语句， 模糊匹配
2、OR 语句前后没有同时使用索引
3、数据类型出现隐式转化（ 如 varchar 不加单引号的话可能会自动转换为 int 型）

怎么验证 MySQL 的索引是否满足需求？

使用 explain 查看 SQL 是如何执行查询语句的，从而分析你的索引是否满足需求。

explain 语法：

explain select * from table where type=1

可以使用多少列创建索引？

任何标准表最多可以创建 16 个索引列

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

索引是通过以下方式为表格定义的：show index from <tablename>;

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

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

• 普通索引：一个索引只包括一个列，一个表可以有多个列，(由关键字 KEY 或 INDEX 定义的索引)的唯一任务是加快对数据的访问速度。
• 主键索引：只有一个主键索引
• 唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引
• 复合索引：一个索引同时包括多列

普通索引允许被索引的数据列包含重复的值。

如果能确定某个数据列将只包含彼此各不相同的值， 在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一索引。

也就是说， 唯一索引可以保证数据记录的唯一性。

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

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

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

索引的目的是什么？

快速访问数据表中的特定信息， 提高检索速度。

创建唯一性索引， 保证数据库表中每一行数据的唯一性==。加速表和表之间的连接使用分组和排序子句进行数据检索时==， 可以显著减少查询中分组和排序的时间.

索引对数据库系统的负面影响是什么？

负面影响：
创建索引和维护索引需要耗费时间， 这个时间随着数据量的增加而增加； 索引需要占用物理空间， 不光是表需要占用数据空间， 每个索引也需要占用物理空间； 当对表进行增、删、改、的时候索引也要动态维护， 这样就降低了数据的维护速度。

为数据表建立索引的原则有哪些？

• 不过度索引， 在频繁使用的、需要排序的字段上建立索引
• 在最频繁使用的、用以缩小查询范围的字段上建立索引。
• 索引条件列(where 后面最频繁的条件比较适宜索引)
• 索引散列值,过于集中的值不要索引，例如:给性别"男"、"女"加索引，意义不大

什么情况下不宜建立索引？

• 主键自动建立唯一索引；
• 频繁作为查询条件的字段应该创建索引
• 查询中与其它表关联的字段，外键关系建立索引
• 单键/组合索引的选择问题， 组合索引性价比更高
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段

什么情况下不宜建立索引？

• 对于查询中很少涉及的列或者重复值比较多的列， 不宜建立索引。
• 对于一些特殊的数据类型， 不宜建立索引， 比如文本字段（ text） 等
• 表记录太少
• 经常增删改的表或者字段
• Where 条件里用不到的字段不创建索引
• 过滤性不好的不适合建索引

唯一索引比普通索引快吗，为什么？

唯一索引不一定比普通索引快， 还可能慢。

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

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

事务

事务并发问题如何发生？

当多个事务同时操作同一个数据库的相同数据时。

事务的并发问题有哪些？

• 脏读：一个事务读取到了另外一个事务未提交的数据
• 不可重复读：同一个事务中，多次读取到的数据不一致
• 幻读：一个事务读取数据时，另外一个事务进行更新，导致第一个事务读取到了没有更新的数据

如何避免事务的并发问题？

通过设置事务的隔离级别

1. READ UNCOMMITTED
2. READ COMMITTED 可以避免脏读
3. REPEATABLE READ 可以避免脏读、不可重复读和一部分幻读
4. SERIALIZABLE可以避免脏读、不可重复读和幻读

设置隔离级别：
set session|global transaction isolation level 隔离级别名;

查看隔离级别：
select @@tx_isolation;

MySQL事务的隔离级别, 分别有什么特点？

1. 读未提交(RU read uncommited): 最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）。

2. 读提交(RC): 一个事务提交之后， 它做的变更才会被其他事务看到（会造成幻读、不可重复读）。

3. 可重复读(RR repeatable read): 可重复读，默认级别，保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据（会造成幻读）。一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下， 未提交变更对其他事务也是不可见的。

4. 串行化(S serializable): ：序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。对于同一行记录, 读写都会加锁。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成才能继续执行。

脏读 ：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录A。

幻读 ：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了
第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。

不可重复读 ：是指在一个事务内，多次读同一数据。

什么是锁?

数据库的锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性，这样才能保证在高并发的情况下，访问数据库的时候，数据不会出现问题。

MySQL 中有哪几种锁？

MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

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

• 行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

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

说一下乐观锁和悲观锁？

MySQL 的事务隔离是在 MySQL. ini 配置文件里添加的，在文件的最后添加：ransaction-isolation = REPEATABLE-READ
可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。

• 乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。

• 悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。

mySql数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。

死锁？

是指两个或两个以上进程执行过程中，因竞争共享资源造成的相互等待现象。

如何处理死锁?

• 设置超时时间。超时后自动释放。·
• 发起死锁检测，主动回滚其中一条事务，让其他事务继续执行。

MySQL 优化

实践中如何优化 MySQL

最好是按照以下顺序优化：
1、SQL 语句及索引的优化
2、数据库表结构的优化
3、系统配置的优化
4、硬件的优化

如何做 MySQL 的性能优化？

1、选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置 NOTNULL， 例如’ 省份’、’ 性别’ 最好适用 ENUM
2、使用连接(JOIN)来代替子查询
3、适用联合(UNION)来代替手动创建的临时表
4、事务处理
5、锁定表、优化事务处理
6、垂直分割分表，适用外键， 优化锁定表
7、为搜索字段创建索引
8、优化查询语句，避免使用 select *，列出需要查询的字段
9、选择正确的存储引擎

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

1、设计良好的数据库结构， 允许部分数据冗余， 尽量避免 join 查询， 提高效率。（实际开发中尽量少用多表联查，其根本原因就在这里，查询过程中，现在内存中构建一个大大的结果集，然后再进行数据的过滤。那这个构建过程，和所使用的内存资源，包括过滤时的判断，都是既耗费资源，又浪费时间。）

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

3、MySQL 库主从读写分离。

4、找规律分表， 减少单表中的数据量提高查询速度。

5、添加缓存机制， 比如 memcached， apc 等。

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

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

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

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

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

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

• 联合索引将高频字段放在最左边（最左特性）。

锁的优化策略有哪些？

• 读写分离
• 分段加锁
• 减少锁持有的时间
• 多个线程尽量以相同的顺序去获取资源
• 不能将锁的粒度过于细化， 不然可能会出现线程的加锁和释放次数过多， 反而效率不如一次加一把大锁。

MySQL常见问题解决

.

MySQL 问题排查都有哪些手段？

• 使用 show processlist 命令查看当前所有连接信息。
• 使用 explain 命令查询 SQL 语句执行计划。
• 开启慢查询日志，查看慢查询的 SQL。

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

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

防止 SQL 注入的方式：

• 开启配置文件中的 magic_quotes_gpc 和 magic_quotes_runtime 设置
• 执行 sql 语句时使用 addslashes 进行 sql 语句转换Sql 语句书写尽量不要省略双引号和单引号。
• 过滤掉 sql 语句中的一些关键词： update、insert、delete、select、 * 。
• 提高数据库表和字段的命名技巧， 对一些重要的字段根据程序的特点命名， 取不易被猜到的。

MySQL查询缓存有什么弊端，应该什么情况下使用， 8.0版本对查询缓存有什么变更？

• 查询缓存可能会失效非常频繁， 对于一个表， 只要有更新， 该表的全部查询缓存都会被清空。 因此对于频繁更新的表来说， 查询缓存不一定能起到正面效果。

• 对于读远多于写的表可以考虑使用查询缓存。

• 8.0版本的查询缓存功能被删了

Mysql 如何优化 DISTINCT（去重）？

DISTINCT 在所有列上转换为 GROUP BY，并与 ORDER BY 子句结合使用。

SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

一千万条数据的表, 如何分页查询？

数据量过大的情况下，limit offset 分页会由于扫描数据太多而越往后查询越慢。 可以配合当前页最后一条ID进行查询,

SELECT * FROM T WHERE id > #{ID} LIMIT #{LIMIT} .

当然， 这种情况下ID必须是有序的， 这也是有序ID的好处之一。

订单表数据量越来越大导致查询缓慢, 如何处理？

分库分表。由于历史订单使用率并不高，高频的可能只是近期订单。

因此， 将订单表按照时间进行拆分,，根据数据量的大小考虑按月分表或按年分表。 订单ID最好包含时间(如根据雪花算法生成)， 此时既能根据订单ID直接获取到订单记录, 也能按照时间进行查询。

MySQL怎么恢复半个月前的数据

通过整库备份+binlog进行恢复，前提是要有定期整库备份且保存了binlog日志。

MYSQL 数据表在什么情况下容易损坏？

• 服务器突然断电导致数据文件损坏。
• 强制关机，没有先关闭 mysql 服务等。