MySQL常见面试题

最新推荐文章于 2024-04-16 19:31:16 发布

wangci0211

最新推荐文章于 2024-04-16 19:31:16 发布

阅读量5.6k

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/wangci0211/article/details/99292144

版权

mysql 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

https://www.jianshu.com/p/c0bc592e6824

https://blog.csdn.net/u014209205/article/details/83051001

MySQL的优点：

1．它使用的核心线程是完全多线程，支持多处理器。

2．有多种列类型：1、2、3、4、和8字节长度自有符号／无符号整数、FLOAT、DOUBLE、CHAR、VARCHAR、TEXT、BLOB、DATE、TIME、DATETIME、 TIMESTAMP、YEAR、和ENUM类型。

3．它通过一个高度优化的类库实现SQL函数库并像他们能达到的一样快速，通常在查询初始化后不该有任何内存分配。没有内存漏洞。

4．全面支持SQL的GROUP BY和ORDER BY子句，支持聚合函数(COUNT()、COUNT(DISTINCT)、AVG()、STD()、SUM()、MAX()和MIN())。你可以在同一查询中混来自不同数据库的表。

5．支持ANSI SQL的LEFT 0UTER JOIN和ODBC。

6．所有列都有缺省值。你可以用INSERT插入一个表列的子集，那些没用明确给定值的列设置为他们的决省值。

7． MySQL可以工作在不同的平台上。支持C、C＋＋、Java、Perl、PHP、Python和TCL API。

MySQL的缺点：

1、 MySQL最大的缺点是其安全系统，主要是复杂而非标准，另外只有到调用mysql admin来重读用户权限时才发生改变。
2、 MySQL的另一个主要的缺陷之一是缺乏标准的RI（Referential Integrity-RI）机制；Rl限制的缺乏(在给定字段域上的一种固定的范围限制)可以通过大量的数据类型来补偿。

4、 MySQL不支持热备份。

5、 .没办法应用缓存。虽然有全局临时表之类的方法可以做缓存，但同样加重了数据库的负担。如果缓存并发严重，经常要加锁，那效率实在堪忧。

6.无法适应数据库的切割（水平或垂直切割）。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。

MySQL几个引擎区别：https://www.cnblogs.com/daofaziran/p/10978498.html

一 Innodb

支持事务，是事务安全的（事务的介绍移驾http://blog.csdn.net/cool_wayen/article/details/78890949），提供行级锁与外键约束，有缓冲池，用于缓冲数据和索引

适用场景：用于事务处理，具有ACID事物支持，应用于执行大量的insert和update操作的表

二 MyISAM

不支持事务，不支持外键约束，不支持行级锁，操作时需要锁定整张表，不过会保存表的行数，所以当执行select count(*) from tablename时执行特别快

适用场景：用于管理非事务表，提供高速检索及全文检索能力，适用于有大量的select操作的表，如日志表

三 MEMORY

使用存在于内存中的内容创建表，每一个memory只实际对应一个磁盘文件。因为是存在内存中的，所以memory访问速度非常快，而且该引擎使用hash索引，可以一次定位，不需要像B树一样从根节点查找到支节点，所以精确查询时访问速度特别快，但是非精确查找时，比如like，这种范围查找，hash就起不到作用了。另外一旦服务关闭，表中的数据就会丢失，因为没有存到磁盘中。

适用场景：主要用于内容变化不频繁的表，或者作为中间的查找表。对表的更新要谨慎因为数据没有被写入到磁盘中，服务关闭前要考虑好数据的存储

四 MERGE

以下是转载的http://blog.csdn.net/leiyonglin/article/details/7008659

MERGE存储引擎把一组MyISAM数据表当做一个逻辑单元来对待，让我们可以同时对他们进行查询。构成一个MERGE数据表结构的各成员MyISAM数据表必须具有完全一样的结构。每一个成员数据表的数据列必须按照同样的顺序定义同样的名字和类型，索引也必须按照同样的顺序和同样的方式定义。

redis的优缺点

优点：

1 读写性能优异

2 支持数据持久化，支持AOF和RDB两种持久化方式

3 支持主从复制，主机会自动将数据同步到从机，可以进行读写分离。

4 数据结构丰富：除了支持string类型的value外还支持string、hash、set、sortedset、list等数据结构。

5 原子 – Redis的所有操作都是原子性的，同时Redis还支持对几个操作全并后的原子性执行。

缺点：

1 Redis不具备自动容错和恢复功能，主机从机的宕机都会导致前端部分读写请求失败，需要等待机器重启或者手动切换前端的IP才能恢复。

2 主机宕机，宕机前有部分数据未能及时同步到从机，切换IP后还会引入数据不一致的问题，降低了系统的可用性。

3 redis的主从复制采用全量复制，复制过程中主机会fork出一个子进程对内存做一份快照，并将子进程的内存快照保存为文件发送给从机，这一过程需要确保主机有足够多的空余内存。若快照文件较大，对集群的服务能力会产生较大的影响，而且复制过程是在从机新加入集群或者从机和主机网络断开重连时都会进行，也就是网络波动都会造成主机和从机间的一次全量的数据复制，这对实际的系统运营造成了不小的麻烦。

4 Redis较难支持在线扩容，在集群容量达到上限时在线扩容会变得很复杂。为避免这一问题，运维人员在系统上线时必须确保有足够的空间，这对资源造成了很大的浪费

5 是数据库容量受到物理内存的限制,

数据库的主从复制

一个服务器作为主服务器，一个或多个服务器作为从服务器，主服务器将更新写到二进制日志，当一个从服务器连接到主服务器时，通知主服务器读取日志，接收从那时起发生的所有更新。解决：数据分布，负载平衡，备份，高可用性和容错性

1、MySQL主从复制的原理。

（1）、主库必须开启二进制日志
（2）、当有增删改的语句时，会记录到主库的binlog中
（3）、主库通过IO线程把binlog里面的内容传给从库的relay binlog（中继日志）（这是msyql复制是异步复制的原因）
（4）、从库的sql线程负责读取它的relay log里的信息并应用到数据库中

2、Seconds_Behind_Master的原理。

表示sql线程和io线程之间的时间差
具体的计算：从库服务器当前的时间戳与二进制日志中的事件的时间戳相对比得到的，所以只有在执行事件时才能报告延迟。
不足：
一些错误（例如主备的max_allowed_packet不匹配，或者网络不稳定）可能中断复制，由于主从复制是异步操作，Seconds_Behind_Master可能显示为0

3、主从延迟的主要原因有哪些？

（1）、慢SQL语句过多
（2）、从库的硬件比主库差
（3）、同一个主库下有过多的从库
（4）、网络延迟
（5）、表分区过多
（还有一些原因，欢迎补充）

4、MySQL常见存储引擎及各自特点。

（1）、InnoDB
支持事务、行级锁、支持外键约束，主要面向OLTP的应用，使用next-key locking 的策略来避免幻读现象的产生.
（2）、MyISAM
不支持事务、表锁设计、支持全文索引、读写互相阻塞、不支持外键约束；主要面向OLAP应用场景；缓存池只缓存索引文件，不缓存数据文件
（3）、Memory
将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。如果数据库重启或者奔溃，数据都将丢失。
（4）、TokuDB
支持事务、高压缩、告诉读写、基于稀疏树索引设计；支持大多数在线修改索引、添加字段。
（5）、Inforbright/infinidb
列式存储、高压缩、单列查询快

5、innodb_flush_log_at_trx_commit参数0、1和2分别代表什么？

innodb_flush_log_at_trx_commit参数可以控制将redo log buffer中的更新记录写入到日志文件以及日志文件刷新到磁盘的操作时机。
0
每秒一次触发log buffer写入log file中，并且log file刷新到磁盘。
（由于进程调度问题，不能保证每秒100%刷新；如果mysql进程崩溃，可能会丢失1s的事务；效率最高，但最不安全）
1
每次事务提交触发log buffer写入log file中，并且log file刷新到磁盘。
2
每次事务提交，log buffer写入log file中；每秒log file刷新到磁盘。
（如果操作系统崩溃或者停电，可能会丢失1s的事务）

6、Mysql中varchar和char的区别

CHAR列的长度固定为创建表时声明的长度，范围（0-255）
VARCHAR列的长度不固定，范围（0-65535）

7、varchar(50)中的50代表的含义、int(20)中20的含义。

varchar(50)中的50代表最多能存放50个字符
int(20)中20的含义表示显示宽度，跟着zerofill一起才有意义

8、MySQL binlog的几种日志录入格式的涵义、适用场景和在复制中的优劣。

（1）、statement level模式
每一条会修改数据的sql都会记录到master的binlog中，slave在复制时sql进程会解析成和原来master端执行过的相同的sql再次执行。
适用场景：对主从数据一致性要求不太高，并且很少用到函数、存储过程、触发器等场景
优点：bin-log日志量少
缺点：部分新功能（函数、存储过程、触发器）同步会有障碍，比如now()
（2）、row level模式
日志中会记录成每一行数据被修改的形式，然后再slave端再对相同的数据进行修改
适用场景：对主从数据一致性要求比较高的场景。
优点：记录的详细
缺点：binlog日志量过大
（3）、mixed模式
MySQL默认采用statement格式进行二进制日志文件的记录，但是在一些情况下会使用row格式，可能的情况有：
1）、表的存储引擎为NDB，此时对表的DML操作都会以ROW格式记录
2）、使用了UUID(),USER(),CURRENT_USER(),FOUND_ROWS(),ROW_count()等不确定函数时
3）、使用了insert delay语句
4）、使用了用户定义函数（UDF）
5）、使用了临时表
适用场景：对主从数据一致性要求不太高，可能会用到函数、存储过程、触发器等场景
优缺点介于statement和row模式之间

9、重做日志和二进制日志的区别（至少三点）

（1）涉及存储引擎不一样：
binlog记录的是所有存储引擎的操作记录
redo log只记录innodb存储引擎的日志
（2）记录内容不一样：
binlog记录的是关于一个事务的具体操作内容。为逻辑日志
而redo log记录的是每个页更改的物理情况
（3）写的时间不一样：
binlog文件仅在事务提交前进行提交，即只写磁盘一次
而在事务进行过程中，却不断有重做日志条目被写入到重做日志文件中。

10、Explain执行计划中要关注哪些要素？

（1）、type:本次查询表联接类型，从这里可以看到本次查询大概的效率
（2）、key:最终选择的索引，如果没有索引的话，本次查询效率通常很差
（3）、key_len:本次查询用于结果过滤的索引实际长度
（4）、rows:预计需要扫描的记录数，预计需要扫描的记录数越小越好
（5）、extra:额外附加信息，主要确认是否出现 Using filesort、Using temporary 类似情况

二、字符集及校对规则

字符集指的是一种从二进制编码到某类字符符号的映射。校对规则则是指某种字符集下的排序规则。Mysql中每一种字符集都会对应一系列的校对规则。

Mysql采用的是类似继承的方式指定字符集的默认值，每个数据库以及每张数据表都有自己的默认值，他们逐层继承。比如：某个库中所有表的默认字符集将是该数据库所指定的字符集（这些表在没有指定字符集的情况下，才会采用默认字符集）

三、索引

Mysql索引使用的数据结构主要有BTree索引 和 哈希索引 。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

Mysql的BTree索引使用的是B数中的B+Tree，但对于主要的两种存储引擎的实现方式是不同的。

MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

InnoDB: 其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

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

同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：

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

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

第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

索引的优点

1.通过创建唯一索引，可以保证数据库每一行数据的唯一性2.可以大大提高查询速度3.可以加速表与表的连接4.可以显著的减少查询中分组和排序的时间。

索引的缺点

1.创建索引和维护索引需要时间，而且数据量越大时间越长2.创建索引需要占据磁盘的空间，如果有大量的索引，可能比数据文件更快达到最大文件尺寸3.当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护速度

索引的设计原则

1.不是越多越好2.常更新的表越少越好3.数据量小的表最好不要建立索引4.不同的值比较多的列才需要建立索引5.某种数据本身具备唯一性的时候，建立唯一性索引，可以保证定义的列的数据完整性，以提高查询熟度6.频繁进行排序或分组的列(group by或者是order by)可以建立索引，提高搜索速度7.经常用于查询条件的字段应该建立索引

三、索引种类
普通索引（index）：仅加速查询

唯一索引（unique）：加速查询 + 列值唯一（可以有null）

主键索引（primary key）：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并，在使用组合索引查询的时候需要遵循“最左前缀”规则

全文索引（fulltext）：对文本的内容进行分词，进行搜索。是对于大表的文本域：char，varchar，text列才能创建全文索引，主要用于查找文本中的关键字，并不是直接与索引中的值进行比较。

ps.

索引合并，使用多个单列索引组合搜索
覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

二、索引实现类型

Mysql目前主要有以下几种索引实现类型：FULLTEXT（即为全文索引），HASH，BTREE，RTREE（RTREE在MySQL很少使用，仅支持geometry数据类型，相对于BTREE，RTREE的优势在于范围查找）。

12. 列举创建索引但是无法命中索引的8种情况。

1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

2.对于多列索引，不是使用的第一部分，则不会使用索引

3.like查询是以%开头

4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

6 对小表查询

7 提示不使用索引

8 统计数据不真实

9.单独引用复合索引里非第一位置的索引列.

四、缓存

my.cnf加入以下配置，重启Mysql开启查询缓存

query_cache_type=1
query_cache_size=600000

Mysql执行以下命令也可以开启查询缓存

set global query_cache_type=1;
set global query_cache_size=600000;

如上，开启查询缓存后在同样的查询条件以及数据情况下，会直接在缓存中返回结果。这里的查询条件包括查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息。因此任何两个查询在任何字符上的不同都会导致缓存不命中。此外，如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、Mysql库中的系统表，其查询结果也不会被缓存。

缓存建立之后，Mysql的查询缓存系统会跟踪查询中涉及的每张表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。 因此，开启缓存查询要谨慎，尤其对于写密集的应用来说更是如此。如果开启，要注意合理控制缓存空间大小，一般来说其大小设置为几十MB比较合适。此外，还可以通过sql_cache和sql_no_cache来控制某个查询语句是否需要缓存：

select sql_no_cache count(*) from usr;

七、大表优化

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。；
读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
缓存： 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；

垂直分区：

根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张。

垂直拆分的优点： 可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

5. 水平分区：

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

水品拆分可以支持非常大的数据量。需要注意的一点是:分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL 并发能力没有什么意义，所以 水品拆分最好分库 。

水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨界点Join 性能较差，逻辑复杂。《Java工程师修炼之道》的作者推荐 尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度 ，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络 I/O。

存储过程

我们常用的操作数据库语言SQL语句在执行的时候需要要先编译，然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。

优点：

(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

(2).存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

(3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。

(4).存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。

(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

触发器：触发器是一个特殊的存储过程，它是MySQL在insert、update、delete的时候自动执行的代码块。

Q8：存储过程与触发器的区别

存储过程和触发器都是SQL语句集；触发器不可用CALL调用，而是在用户执行某些语句后自动调用；

4. 简述触发器、函数、视图、存储过程？

触发器：触发器是一个特殊的存储过程，它是Mysql在insert、update、delete、的时候会自动执行代码块。

函数：MySQL中提供了许多内置函数，还可以自定义函数（实现程序员需要sql逻辑处理）。

视图：视图是由查询结果形成的一张虚拟表，是表通过某种运算得到的一个投影。

存储过程：把一段代码封装起来，当要执行这一段代码的时候，可以通过调用该存储过程来实现（经过第一次编译后再次调用不需要再次编译，比一个个执行sql语句效率高）

7. Mysql常见函数？

聚合函数：

　　AVG(col)返回指定列的平均值

　　COUNT(col)返回指定列中非NULL值的个数

　　MIN(col)返回指定列的最小值

　　MAX(col)返回指定列的最大值

　　SUM(col)返回指定列的所有值之和

　　GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

数学函数：

　　ABS(x) 返回x的绝对值

　　BIN(x) 返回x的二进制（OCT返回八进制，HEX返回十六进制）

8. 如何开启慢查询日志？

（1）简介：开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能

（2）参数说明：

slow_query_log 慢查询开启状态
slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
long_query_time 查询超过多少秒才记录

（3）设置步骤：

1.查看慢查询相关参数

mysql> show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name             | Value                            |
+---------------------------+----------------------------------+
| slow_query_log            | OFF                              |
| slow_query_log_file       | /mysql/data/localhost-slow.log   |
+---------------------------+----------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

2.设置方法

配置文件设置
修改配置文件my.cnf，在[mysqld]下的下方加入

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

3.重启MySQL服务

service mysqld restart

4.查看设置后的参数

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /usr/local/mysql/data/slow.log |
+---------------------+--------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

5.测试：

1.执行一条慢查询SQL语句

mysql> select sleep(2);

2.查看是否生成慢查询日志

ls /usr/local/mysql/data/slow.log

如果日志存在，MySQL开启慢查询设置成功！

delete、drop、truncate区别

truncate 和 delete只删除数据，不删除表结构 ,drop删除表结构，并且释放所占的空间。
删除数据的速度，drop> truncate > delete
delete属于DML语言，需要事务管理，commit之后才能生效。drop和truncate属于DDL语言，操作立刻生效，不可回滚。
使用场合：
- 当你不再需要该表时，用 drop;
- 当你仍要保留该表，但要删除所有记录时，用 truncate;
- 当你要删除部分记录时（always with a where clause), 用 delete.

注意： 对于有主外键关系的表，不能使用truncate而应该使用不带where子句的delete语句，由于truncate不记录在日志中，不能够激活触发器

10. 主键和外键的区别？

主键Primary key：数据库实体完整性的一种规则，唯一标示一个实体，取值非空唯一。比如，学生表的学号。

外键 Foreign key：数据库参照完整性的一种规则，将两表或者多张表联系起来，取值必须来自参照表的参照字段的值，可为空也可不为空。比如，选课表里的学号。

15. MySQL数据库优化方案？(https://www.cnblogs.com/li1992/articles/9217026.html)

一、SQL语句优化

（1）使用limit对查询结果的记录进行限定
（2）避免select *，将需要查找的字段列出来
（3）使用连接（join）来代替子查询
（4）拆分大的delete或insert语句

二、选择合适的数据类型

（1）使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob
（2）使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数
（3）使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar
（4）尽可能使用not null定义字段
（5）尽量少用text，非用不可最好分表

三、选择合适的索引列

（1）查询频繁的列，在where，group by，order by，on从句中出现的列
（2）where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列
（3）长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好
（4）离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高：

mysql> SELECT COUNT(DISTINCT column_name) FROM table_name;

四、使用命令分析

explain

Record lock

单条索引记录上加锁，record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

Gap lock

在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。gap lock的机制主要是解决可重复读模式下的幻读问题，关于幻读的演示和gap锁如何解决了幻读。关于这一块，先给出几个定义

快照读：

简单的select操作，没有lock in share mode或for update，快照读不会加任何的锁，而且由于mysql的一致性非锁定读的机制存在，任何快照读也不会被阻塞。但是如果事务的隔离级别是SERIALIZABLE的话，那么快照读也会被加上共享的next-key锁，本文不对SERIALIZABLE隔离级别做叙述。

当前读：

官方文档的术语叫locking read，也就是insert，update，delete,select..in share mode和select..for update,当前读会在所有扫描到的索引记录上加锁，不管它后面的where条件到底有没有命中对应的行记录。当前读可能会引起死锁。

Next-Key Locks

在默认情况下，mysql的事务隔离级别是可重复读，并且innodb_locks_unsafe_for_binlog参数为0，这时默认采用next-key locks。所谓Next-Key Locks，就是Record lock和gap lock的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。

下面我们针对大部分的SQL类型分析是如何加锁的，假设事务隔离级别为可重复读。

select .. from

不加任何类型的锁

select...from lock in share mode

在扫描到的任何索引记录上加共享的（shared）next-key lock，还有主键聚集索引加排它锁

select..from for update

在扫描到的任何索引记录上加排它的next-key lock，还有主键聚集索引加排它锁

update..where delete from..where

在扫描到的任何索引记录上加next-key lock，还有主键聚集索引加排它锁

insert into..

简单的insert会在insert的行对应的索引记录上加一个排它锁，这是一个record lock，并没有gap，所以并不会阻塞其他session在gap间隙里插入记录。不过在insert操作之前，还会加一种锁，官方文档称它为insertion intention gap lock，也就是意向的gap锁。这个意向gap锁的作用就是预示着当多事务并发插入相同的gap空隙时，只要插入的记录不是gap间隙中的相同位置，则无需等待其他session就可完成，这样就使得insert操作无须加真正的gap lock。想象一下，如果一个表有一个索引idx_test，表中有记录1和8，那么每个事务都可以在2和7之间插入任何记录，只会对当前插入的记录加record lock，并不会阻塞其他session插入与自己不同的记录，因为他们并没有任何冲突。

假设发生了一个唯一键冲突错误，那么将会在重复的索引记录上加读锁。当有多个session同时插入相同的行记录时，如果另外一个session已经获得改行的排它锁，那么将会导致死锁。

死锁原因分析：

首先session1插入一条记录，获得该记录的排它锁，这时session2和session3都检测到了主键冲突错误，但是由于session1并没有提交，所以session1并不算插入成功，于是它并不能直接报错吧，于是session2和session3都申请了该记录的共享锁，这时还没获取到共享锁，处于等待队列中。这时session1 rollback了，也就释放了该行记录的排它锁，那么session2和session3都获取了该行上的共享锁。而session2和session3想要插入记录，必须获取排它锁，但由于他们自己都拥有了共享锁，于是永远无法获取到排它锁，于是死锁就发生了。如果这时session1是commit而不是rollback的话，那么session2和session3都直接报错主键冲突错误。查看死锁日志也是一目了然

insert导致的死锁现象2

另外一个类似的死锁是session1删除了id=1的记录并未提交，这时session2和session3插入id=1的记录。这时session1 commit了，session2和session3需要insert的话，就需要获取排它锁，那么死锁也就发生了；session1 rollback，则session2和session3报错主键冲突。这里不再做演示。

INSERT ... ON DUPLICATE KEY UPDATE

这种sql和insert加锁的不同的是，如果检测到键冲突，它直接申请加排它锁，而不是共享锁。

replace

replace操作如果没有检测到键冲突的话，那么它的加锁策略和insert相似；如果检测到键冲突，那么它也是直接再申请加排它锁

INSERT INTO T SELECT ... FROM S WHERE ...

在T表上的加锁策略和普通insert一致，另外还会在S表上的相关记录上加共享的next-key lock。（如果是可重复读模式，则不会加锁）

CREATE TABLE ... SELECT ...

在select的表上加共享的next-key lock

自增id的加锁策略

当一张表的某个字段是自增列时，innodb会在该索引的末位加一个排它锁。为了访问这个自增的数值，需要加一个表级锁，不过这个表级锁的持续时间只有当前sql，而不是整个事务，即当前sql执行完，该表级锁就释放了。其他session无法在这个表级锁持有时插入任何记录。

外键检测的加锁策略

如果存在外键约束，任何的insert，update，delete将会检测约束条件，将会在相应的记录上加共享的record lock，无论是否存在外键冲突。

wangci0211

关注

1
点赞
踩
11

收藏

觉得还不错? 一键收藏
1
评论
MySQL常见面试题

https://www.jianshu.com/p/c0bc592e6824https://blog.csdn.net/u014209205/article/details/83051001MySQL的优点：1．它使用的核心线程是完全多线程，支持多处理器。2．有多种列类型：1、2、3、4、和8字节长度自有符号／无符号整数、FLOAT、DOUBLE、CHAR、VARCHAR...
复制链接

扫一扫