mysql面试指北

最新推荐文章于 2024-04-20 17:21:30 发布

yannischen

最新推荐文章于 2024-04-20 17:21:30 发布

阅读量99

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/piggachen/article/details/114667073

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

Mysql

数据库并发带来的问题

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

Mysql存储引擎

InnoDB

InnoDB 事务型数据库的首选引擎，支持事务安全表（ACID）。MySQL 5.5.5 之后，InnoDB 作为默认存储引擎
支持行级锁
支持外键
支持树级索引（B+树）

场景：由于其支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

MyISAM

mysql最早默认的存储引擎
支持树索引
BLOB和TEXT列可以被索引，支持FULLTEXT类型的索引。支持全文索引
主要用来插入和查询记录

场景：在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物和外键。

Memory

支持树索引、hash索引
存储的数据保存在内存中，所以其保存的数据具有不稳定性，比如如果mysqld进程发生异常、重启或计算机关机等等都会造成这些数据的消失，所以这种存储引擎中的表的生命周期很短，一般只使用一次。

数据库事务

数据库四种事务隔离级别（InnoDB）

RU 读未提交（read-uncommitted）
RC 读已提交（read-committed）存在不可重复度和幻读的问题
RR 可重复读（repeatable-read） Mysql默认
串行化（serializable）

Isolation Level（隔离级别)	Dirty Read(脏读)	Unrepeatable Read(不可重复读)	Phantom Read(幻读)
Read UNCOMMITTED(读未提交)	Yes	Yes	Yes
READ COMMITTED(读已提交)	No	Yes	Yes
READ REPEATABLE(可重复读)	No	No	Yes
SERIALIZABLE(序列化)	No	No	No

事务相关问题

1.数据库默认隔离级别:

mysql —repeatable read,oracle,sql server —read commited

2.mysql binlog的格式三种：statement,row,mixed

3.为什么mysql用的是repeatable而不是read committed:

Mysql的主从复制，使用的binlog的方式。在 5.0之前只有statement一种格式，而主从复制存在了大量的不一致，故选用repeatable

4.为什么默认的隔离级别都会选用read commited

repeatable存在间隙锁会使死锁的概率增大，在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行
在RC级用别下，主从复制用什么binlog格式：row格式，是基于行的复制！

一般互联网配置事务隔离级别为RC的原因

不选择RC 是因为不满足基本的业务要求
不选择Seriazable是因为效率太慢
在RR级别下，存在间隙锁，死锁概率变大
在RR级别下，若未命中会锁表，RC下仅会锁行

疑难问题详解：
https://www.cnblogs.com/rjzheng/p/10510174.html

binlog的三种格式

Statement 默认

每条在master上执行的sql均需在slave上同样执行

优点： 历史悠久，技术成熟。日志文件较小，节省存储及IO。仅关心执行的sql及相关的上下文信息。

缺点： 1.对于不确定的Sql执行会不一样，比如sysDate,Now();
2.

Row

日志中会记录每行数据的修改情况，然后对具体行的数据进行修改。不会记录多表关联等信息。

优点： 不关心sql执行上下文的依赖关系，仅关心数据的具体变化。row的日志会记录每行数据的变化细节。

缺点： 记录行的变化细节，日志量会变大。

mix

Mysql锁机制

行级锁

行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。

对于 UPDATE、 DELETE 和 INSERT 语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通 SELECT 语句，InnoDB不会加任何锁。

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

行级锁(InnoDB）如何显式加锁

用lock in share mode 显式的加共享锁，用 for update 显式的加排他锁。

行级锁(InnoDB）的实现

InnoDB的行锁是通过在MySQL的索引上的索引项加锁实现的，意味着只有通过索引条件检索数据，才会使用行锁，否则使用表锁。
即使是访问不同行的记录，如果使用的是相同的索引键，会发生锁冲突。
如果数据表建有多个索引时，可以通过不同的索引锁定不同的行。

间隙锁

间隙锁产生的条件

数据库存储引擎为InnoDB，事务为RR（可重复读）
当使用唯一索引来搜索唯一行的语句时，不需要间隙锁定

InnoDB支持事务，为了满足隔离级别的要求，InnoDB有个间隙锁，当使用范围查找时，InnoDB会给满足key范围要求，但实际并不存在的记录加锁。例如：select * from user where id > 100 for updata 会给ID>100的记录加排他锁，满足这个范围，但不存在的记录，会加间隙锁，这样可以避免幻读，避免读取的时候插入满足条件的记录。（RR)

行级锁与死锁

MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。

InnoDB发生死锁后一般能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务

产生原因：
在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。
当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。

如何处理：

1.不同的程序并发操纵多个表，一般约定以同样的顺序进行处理；

2.同一个事务，一般一次性的锁住所有需操作的资源；

3.对于非常容易死锁的业务，可以尝试升级锁粒度；

表级锁

对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

特点：对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

页级锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁

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

数据库索引B+优势

参见：文档：树.md
链接：http://note.youdao.com/noteshare?id=633da3d172ab0485d26665a611ef18e8&sub=9D51255774384EFA830290877F852301

InnoDB不使用

聚簇索引与非聚簇索引

聚簇索引

聚集索引（聚簇索引）：以 InnoDB 作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。
因为 InnoDB 是把数据存放在 B+ 树中的，而 B+ 树的键值就是主键，在 B+ 树的叶子节点中，存储了表中所有的数据。

这种以主键作为 B+ 树索引的键值而构建的 B+树索引，我们称之为聚集索引。

非聚簇索引

非聚集索引（非聚簇索引）：以主键以外的列值作为键值构建的 B+ 树索引，我们称之为非聚集索引。

区别

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

Mysql调优

Show查看状态

mysql慢查询日志分析
关键词

long_query_time

slow_query_log：是否开启慢查询日志，1表示开启，0表示关闭

https://blog.csdn.net/qq_40884473/article/details/89455740

查看mysql运行时长
查看mysql运行到现在的查询次数（插入次数com_insert、修改次数com_insert、删除次数com_delete）
查看mysql的连接次数
查看mysql的索引使用情况

mysql> SHOW STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 12    |
+-----------------------+-------+

handler_read_key：这个值越高越好，越高表示使用索引查询到的次数。
handler_read_rnd_next：这个值越高，说明查询低效。

explain分析指定sql

mysql> explain select * from t_device where id = 262;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_device | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

mysql> explain select * from t_device where devicenumber like '%11';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_device | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  117 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

explain查询sql执行计划，各列含义：

id: SELECT识别符。这是SELECT的查询序列号.在单个的SQL执行中，SQL从大到小的执行
select_type： 查询类型
    - SIMPLE(简单SELECT，不使用UNION或子查询等)；
    - PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)；
    - UNION(UNION中的第二个或后面的SELECT语句)；
    - DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)；
    - UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)；
    - SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)；
    - DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)；
    - DERIVED(派生表的SELECT, FROM子句的子查询)；
    - UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)；
table：表名；
type：连接的类型 ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
    - const：主键、索引；
    - eq_reg：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件；
    - ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
    - range：只检索给定范围的行，使用一个索引来选择行；
    - index：Full Index Scan，index与ALL区别为index类型只遍历索引树；
    - all：Full Table Scan， MySQL将遍历全表以找到匹配的行；
possible_keys：可能用到的索引；
key：实际使用的索引；
key_len：索引的长度，越短越好；
ref：索引的哪一列被使用了，常数较好；
rows：mysql认为必须检查的用来返回请求数据的行数；
extra：using filesort、using temporary（常出现在使用order by时）时需要优化。
    -Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

    -Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by。查询需优化
    -Using filesort：当Query中包含 order by操作，而且无法利用索引完成的排序操作称为“文件排序”.查询需优化
    -Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
    -Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
    -Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
    -No tables used：Query语句中使用from dual 或不含任何from子句

SQL优化的关键点在type,extra上

yannischen

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql面试指北

Mysql数据库并发带来的问题1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。小结：不可重复读的和幻读很容易
复制链接

扫一扫

专栏目录